SQL 基础--SELECT 查询
一、SQL 结构化查询语言
包括DDL(数据定义语言)、DCL(数据控制语言)、
DQL(数据查询语言)、DML(数据操纵语言)
二、SQL的特点
SQL 语句不区分大小写
SQL 语句能输入一行或多行
关键字不能整行缩写或分离
子句通常被放置在分开的行上
缩进可提高可读性
在SQL 开发工具,SQL 语句能选择分号结束(;) .当你运行多个SQL 语句的时候,需要分号
在SQL*Plus中, 你要用一个分号结束每个SQL 语句.(;)
三、SQL*PLUS特征:
字符日期左对齐
数字右对对齐
列名默认大写
SQL PLUS 自己的命令不需以分号“;”结束
四、SQL查询时,数字和日期类型的数据可用算术运算符
+ 加
- 减
* 乘
/ 除
( ) 用于改变运算符的优先级
五、空值
空值一般用NULL表示
一般表示未知的、不确定的值,也不是空格
一般运算符与其进行运算时,都会为空
空不与任何值相等
表示某个列为空用:IS NULL 不能使用COMM=NULL这种形式
某个列不为空:IS NOT NULL 不能使用COMM != NULL 这种形式
空值在作升序排列时,空值会放到最后。
相反作降序排列时,空值会放在最前。
空值作逻辑运算时:
AND运算:
F AND F =F F AND T =F F AND NULL =F
T AND F =F T AND T =T T AND NULL IS NULL
NULL AND F =F NULL AND T IS NULL NULL AND NULL IS NULL
就是说AND的优先级是:F ->NULL ->T
OR运算:
T OR T =T T OR F =T T OR NULL =T
F OR T =T F OR F =F F OR NULL IS NULL
NULL OR T =T NULL OR F IS NULL NULL OR NULL IS NULL
OR运算优先级:T ->NULL ->F
NOT运算:
NOT T =F
NOT F =T
NOT NULL IS NULL
与空值相关的函数:
NVL 函数
格式:NVL(表达式1,表达式2)
作用:测试表达式的值,如果表达式1为空,则返回表达式2的值;不为空,返回表达式1的值。
NVL2 函数
格式:NVL2(表达式1,表达式2,表达式3)
作用:测试表达式的值,表达式1不为空,返回表达式2的值,如果为空,则返回表达式3的值。
NULLIF 相等为空
格式:NULLIF (表达式1,表达式2)
作用:比较表达式1和表达式2的值,如果两个相等则返回为空,否则返回表达式1的值。
COALESCE 找非空
格式:COALESCE (表达式1,表达式2,表达式3,...,表达式n)
作用:返回第一个不为空的值,如果所有的都为空,则返回NULL。
六、SELECT语句的用法
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
七、演示 */
--选择所有字段
SQL> SET LINESIZE 200
SQL> SELECT * FROM SCOTT.EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
--选择部分字段
SQL> SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
--算术加减运算
SQL> SELECT EMPNO,ENAME,SAL + 300 FROM SCOTT.EMP;
EMPNO ENAME SAL+300
---------- ---------- ----------
7369 SMITH 1100
7499 ALLEN 1900
7521 WARD 1550
--优先级
SQL> SELECT EMPNO,ENAME,12 * (SAL + 300) FROM SCOTT.EMP;
EMPNO ENAME 12*(SAL+300)
---------- ---------- ------------
7369 SMITH 13200
7499 ALLEN 22800
7521 WARD 18600
SQL> SELECT EMPNO,ENAME,12 * SAL + 300 FROM SCOTT.EMP;
EMPNO ENAME 12*SAL+300
---------- ---------- ----------
7369 SMITH 9900
7499 ALLEN 19500
7521 WARD 15300
--NULL,记录中COMM存在为NULL的情况
SQL> SELECT EMPNO,ENAME,SAL,COMM FROM SCOTT.EMP;
EMPNO ENAME SAL COMM
---------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
--与NULL运算,结果为NULL
SQL> SELECT EMPNO,ENAME,SAL,COMM + 300 FROM SCOTT.EMP;
EMPNO ENAME SAL COMM+300
---------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600 600
7521 WARD 1250 800
7566 JONES 2975
--将COMM不为NULL的记录的COMM乘以
SQL> SELECT EMPNO,ENAME,SAL,COMM * 12 FROM SCOTT.EMP WHERE COMM IS NOT NULL
EMPNO ENAME SAL COMM*12
---------- ---------- ---------- ----------
7499 ALLEN 1600 3600
7521 WARD 1250 6000
7654 MARTIN 1250 16800
7844 TURNER 1500 0
--字段别名,字段后用AS 别名,AS可以省略
SQL> SELECT EMPNO,ENAME AS EmpName,SAL Salary FROM SCOTT.EMP;
EMPNO EMPNAME SALARY
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
--DISTINCT,过滤重复行
SQL> SELECT DISTINCT EMPNO,ENAME FROM SCOTT.EMP;
--连接操作符,通过二个垂直的条描述(||),注意,日期和文字数值一定嵌入在单引号里面
SQL> SELECT EMPNO,ENAME || ' IS A ' ||JOB AS POSITION FROM SCOTT.EMP;
EMPNO POSITION
---------- -------------------------
7369 SMITH IS A CLERK
7499 ALLEN IS A SALESMAN
7521 WARD IS A SALESMAN
--DESC table_name,显示表结构信息
SQL> DESC SCOTT.EMP
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--NVL的用法
SQL> SELECT EMPNO,ENAME,NVL(TO_CHAR(COMM),'Not Applicable') FROM SCOTT.EMP;
EMPNO ENAME NVL(TO_CHAR(COMM),'NOTAPPLICABLE')
---------- ---------- ----------------------------------------
7369 SMITH Not Applicable
7499 ALLEN 300
7521 WARD 500
7566 JONES Not Applicable
--NVL2的用法
SQL> SELECT empno,ename,sal,NVL2(TO_CHAR(comm),12 * (sal + comm),sal) AS Income FROM scott.emp;
EMPNO ENAME SAL INCOME
---------- ---------- ---------- ----------
7369 SMITH 800 800
7499 ALLEN 1600 22800
7521 WARD 1250 21000
7566 JONES 2975 2975
--NULLIF的用法
--等价于CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
SQL> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
2 FROM hr.employees e, hr.job_history j
3 WHERE e.employee_id = j.employee_id
4 ORDER BY last_name, "Old Job ID";
LAST_NAME Old Job ID
------------------------- ----------
De Haan AD_VP
Hartstein MK_MAN
Kaufling ST_MAN
Kochhar AD_VP
Kochhar AD_VP
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen AD_ASST
Whalen
--下面是使用CASE WHEN的等价用法
SQL> SELECT e.last_name, CASE WHEN e.job_id = j.job_id THEN NULL ELSE e.job_id END AS "Old Job ID"
2 FROM hr.employees e, hr.job_history j
3 WHERE e.employee_id = j.employee_id
4 ORDER BY last_name, "Old Job ID";
LAST_NAME Old Job ID
------------------------- ----------
De Haan AD_VP
Hartstein MK_MAN
Kaufling ST_MAN
Kochhar AD_VP
Kochhar AD_VP
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen AD_ASST
Whalen
--COALESCE的用法
--当COALESCE(exp1,exp2)包含两个表达式时,等价于CASE WHEN exp1 IS NOT NULL THEN exp1 ELSE exp2 END
--COALESCE (expr1, expr2, ..., exprn), for n>=3
--当n >= 3时,等价于
--CASE WHEN expr1 IS NOT NULL THEN expr1
-- ELSE COALESCE (expr2, ..., exprn) END
SQL> SELECT product_id, list_price, min_price,
2 COALESCE(0.9*list_price, min_price, 5) "Sale"
3 FROM oe.product_information
4 WHERE supplier_id = 102050
5 ORDER BY product_id, list_price, min_price, "Sale"
PRODUCT_ID LIST_PRICE MIN_PRICE Sale
---------- ---------- ---------- ----------
1769 48 43.2
1770 73 73
2378 305 247 274.5
2382 850 731 765
3355 5
八、更多
300多个js网页特效 js+Css特效源码例子
t("小游戏")
"五子棋(js)
"五子棋(applet)"
"扫雷"
"超级马力"
"俄罗期方块"
"乒乓球"
"推箱子"
"小猫"
"模拟抽奖"
("applet")
"礼花"
"人体模特"
"java时钟"
"applet菜单"
"魔鬼文字"
("链接类")
"顺序链接"
"随机连接"
"链接注释"
"滚动链接注释"
"左右键一样"
"禁止Shift"
"导航栏"
"链接确认"
"强调连接"
"连接说明脚本"
"友情连接脚本"
"About:连接"
"本地连接"
"闪烁的连接"
"连接提示框显示"
"连接提示框显示Ⅱ"
"链接确认警告"
"酷的导航条"
"导航条之六"
("色彩类")
"调色板1"
"颜色代码"
"调色板2"
"渐变"
"颜色板"
"表格调色板"
"应用色彩学"
"渐变色条"
"色彩调节器"
("样式表")
"HTML对联"
"HTML旗帜"
"HTMl图标"
"CSS立体层"
"调用CSS文件"
"闪动的文字"
"不错的文字效果"
"改变字体大小"
"着重号"
"属性列表"
"字体效果1"
"字体效果2"
"不同颜色交替"
"改变样式"
"变色文字"
"荧光灯效果"
"变色文本"
"字体变化效果(大小)"
"闪闪烁烁的按钮"
"彩虹文字"
"带颜色变化的连接按钮"
"荧光文字Ⅱ"
"掉下的文字"
"闪烁效果字幕"
"文字转换效果"
"效果之二"
"环绕转动的字体"
"波浪文字"
"飞舞文字"
"掉下的文字Ⅱ"
"渐隐渐现字体效果"
"动态文字之一"
"检测系统颜色字体"
("表格类")
"会动的表格"
"极酷的表格"
"加一个表格"
"几类表格"
"插入表格"
"动态插入文本"
"拖动单元格"
"变色表格单元"
"变色的表格框"
("数学类")
"装机价格计算器"
"面积计算器"
"进制转化"
("窗口类")
"桌面(win2000)"
"iframe颜色"
"层窗口"
"转圈的窗口"
"资源管理器"
"改变层的大小"
"active桌面"
"移动的窗口"
"tip对话框"
"从天而降的浏览器"
"居中的窗口"
"搞笑的窗口"
"窗口分块显示"
"Threedface"
"左右上下"
"最大化与最小化"
"打开你的窗口来"
"拖动窗口"
"自动关闭"
"页面效果六"
"页面效果五"
"页面效果四"
"页面效果三(从中)"
"页面效果二(四周)"
"页面效果一(一块块)"
"按钮打开全屏窗口"
"全屏打开页面"
"自动滚屏"
"动画显示打开新窗口"
"震动的窗口"
"SplashWindow应用"
"页面效果生成器"
"ChWindowsv"
"ChWindows应用指南"
"IE5.5的新窗口."
"自动跳出框架"
"放大镜效果IE5.5"
("菜单类")
"多数菜单"
"简单的目录"
"酷菜单一"
"下拉菜单1"
"下拉菜单2"
"多数菜单"
"右键菜单"
"右键菜单2"
"移动的菜单"
"菜单之一"
"导航栏"
"QQ菜单生成器"
"QQ菜单"
"菜单之二"
fivemenu.htm", "五角形"
dmenu.htm", "下拉菜单2"
mlmenu.htm", "多级右键菜单"
twomenu.htm", "同时打开两个菜单"
cmenu.htm", "效果之一"
coolmenu.htm", "很酷的导航栏"
CoolMenus3.htm", "多功能菜单"
coolleftmenu.htm", "菜单"
regmenu.htm", "加入IE右键项目"
baoku/0008.htm", "滑动导航条一(左)"
baoku/0009.htm", "滑动导航条二(键)"
baoku/0010.htm", "滑动导航条三(格)"
baoku/0033.htm", "导航条之七"
baoku/0034.htm", "导航条之八"
baoku/0035.htm", "树型菜单"
baoku/0036.htm", "双选择关联菜单"
baoku/0037.htm", "三级关联菜单"
baoku/0038.htm", "带子菜单的导航条"
baoku/0141.htm", "右键菜单-IE5.5+"
baoku/0148.htm", "滑动导航条"
baoku/0151.htm", "多项目选择脚本"
baoku/0155.htm", "模范IE的收藏夹"
baoku/0134.htm", "模仿微软按钮的连接"
baoku/0118.htm", "右键菜单"
baoku/0114.htm", "Cool的右键菜单"
baoku/0152.htm", "带有图片的下拉选择"
("滚动条")
scroll/dblscroll.htm", "双击滚屏"
scroll/dragpage.htm", "拖动页面"
scroll/autoscroll.htm","自动滚屏1"
scroll/autoscroll1.htm", "自动滚屏2"
scroll/ascroll.htm", "局部滚动"
scroll/scrollimg.htm", "滚动的图片"
scroll/ontop.htm", "滚动条在上面"
baoku/0147.htm", "滚动条颜色生成器IE5.5+"
baoku/0153.htm", "滚动条颜色生成器IE5.5+"
baoku/0087.htm", "横向移动显示图片"
baoku/0062.htm", "从外部读入内容"
baoku/0015.htm", "滑动导航条四(条)"
baoku/0016.htm", "滑动导航条五(两端)"
baoku/0017.htm", "自动滚动页面"
baoku/0057.htm", "不错的字幕滚动例子"
baoku/0126.htm", "Marquee效果生成器"
baoku/0053.htm", "简单的轮显效果"
("图片类")
img/drawc.htm", "画圆饼"
img/netpet/netpet.htm", "一个游泳的鱼"
img/imgleft.htm", "禁止对图片用右键"
img/shakeimage.htm", "震动的图片"
img/orgsize.htm", "图片原始大小"
img/flag.htm", "旗帜效果"
img/leida.htm", "雷达效果"
img/img.htm", "浏览图片"
img/newimg.htm", "弹出窗口与图片相同"
img/comeimg.htm", "图片过来"
img//acd/acdsee.htm", "ACDsee游览器"
img/imgsize.htm", "图片大小"
img/color.htm", "淡入淡出效果"
img/picalpha.htm", "犹抱琵琶半遮面"
img/livebg.htm", "改变图像背景"
baoku/0004.htm", "图形淡出淡隐"
baoku/0005.htm", "跟随页面移动的图片"
baoku/0006.htm", "动态图形转换"
baoku/0007.htm", "随机广告图片"
baoku/0065.htm", "飘浮的图片"
baoku/0070.htm", "图片浏览"
baoku/0072.htm", "图片从右飞向左"
baoku/0076.htm", "从左飞向右的图片"
baoku/0077.htm", "上下滚动显示图片"
baoku/0078.htm", "左右滚动显示图片"
baoku/0084.htm", "图片轮显效果"
baoku/0081.htm", "水中的倒影"
baoku/0082.htm", "灯照效果"
baoku/0138.htm", "图片效果生成器"
baoku/0100.htm", "飞舞的图片IE5.5"
baoku/0018.htm", "向上飘的白云"
baoku/0083.htm", "震动的图片"
baoku/0086.htm", "滚动的小球"
baoku/0104.htm", "新技术测试--有美女"
baoku/0095.htm", "背景效果:渐变"
baoku/0073.htm", "渐渐显示图片"
("日期类")
time/date1.htm", "日历一"
time/date2.htm", "日历二"
time/date3.htm", "日历三"
time/cndate.htm", "农历日历"
time/ymdw.htm", "年月日周"
time/lastm.htm", "最后更新时间"
time/titletime.htm","标题时间"
time/titledate.htm","标题日期"
time/goodclock.htm", "很不错的时钟"
time/picclock/picclock.htm", "图片时钟"
time/ltime.htm", "倒计时"
time/dates.htm", "下拉时间."
time/20minite.htm", "倒计时20分钟"
time/setatime.htm", "定时"
time/backtime.htm", "背景时钟"
time/statustime.htm", "状态栏数字钟"
time/staytime.htm","驻留时间"
time/buttontime.htm", "按钮时间"
time/month1.htm", "月份图片"
baoku/0045.htm", "带背景与时间停顿"
baoku/0069.htm", "**年**月**日"
baoku/0064.htm", "COOL数字钟"
baoku/0131.htm", "显示日历"
baoku/0075.htm", "文本时间显示"
baoku/0079.htm", "标题栏显示时间"
baoku/0080.htm", "停留时间警告"
baoku/0085.htm", "时间倒数"
("鼠标类")
mouse/nocon.htm", "真正的右键屏蔽"
mouse/mouselogo.htm", "围绕鼠标的文字"
mouse/shi.htm", "十字线"
mouse/mouse1.htm", "鼠标跟随字幕"
mouse/mouse2.htm", "录制鼠标"
mouse/mouse3.htm", "拖动图像"
mouse/mouse4.htm", "模仿flash效果"
mouse/mouse5.htm","拖动层"
mouse/mouse6.htm", "坐标"
mouse/mouse7.htm", "鼠标效果"
mouse/showalt1.htm", "注释效果1"
mouse/show3.htm", "注释效果2"
mouse/rightopen.htm", "右键打开窗口"
mouse/rightcode.htm", "右键打开源文件"
baoku/0012.htm", "右键启动收藏夹"
baoku/0013.htm", "禁止点击鼠标右键"
baoku/0014.htm", "禁止对图片使用右键"
baoku/0099.htm", "模拟系统鼠标效果"
baoku/0101.htm", "跟随鼠标的星星"
baoku/0102.htm", "跟随鼠标的图形"
baoku/0103.htm", "飞鼠标跟随之一"
baoku/0108.htm", "鼠标跟随文字二"
baoku/0110.htm", "模拟鼠标移动效果"
baoku/0111.htm", "鼠标跟随之三"
baoku/0112.htm", "鼠标跟随之二"
baoku/0105.htm", "跟随鼠标的文字一"
baoku/0106.htm", "鼠标跟随之四(十字)"
baoku/0107.htm", "事件追加函数IE5.5"
baoku/0071.htm", "跟随鼠标的双眼"
baoku/0149.htm", "很Cool鼠标跟随"
baoku/0092.htm", "模仿Flash效果"
baoku/0093.htm", "模仿Flash效果二"
baoku/0050.htm", "鼠标跟随字幕"
baoku/0039.htm", "花炮效果"
baoku/0040.htm", "花炮效果Ⅱ"
baoku/0041.htm", "飘荡的枯叶"
("键盘类")
key/showkey.htm", "ASCII代码"
baoku/0090.htm", "给页面定义热键"
baoku/0063.htm", "打字效果"
key/Ctrl+Enter.htm", "Ctrl+Enter提交"
key/typer.htm", "打字机"
key/testspeed.htm", "打字速度测试"
key/keya.htm", "键盘字母"
("按钮类")
baoku/0001.htm", "前进后退按钮"
baoku/0089.htm", "查看源代码"
baoku/0094.htm", "只提交一次"
button/td1.htm", "效果1"
button/all.htm", "各种功能按钮"
("状态栏")
baoku/0066.htm", "状态栏文字从右到左"
baoku/0067.htm", "状态栏文字从右弹出"
baoku/0068.htm", "闪烁状态栏"
baoku/0074.htm", "状态栏数字钟"
("属性类")
baoku/0002.htm", "离开启动收藏夹"
baoku/0032.htm", "记录浏览次数"
baoku/0042.htm", "记录来宾姓名"
baoku/0043.htm", "检测屏幕分辨率"
baoku/0044.htm", "系统信息检测"
baoku/0049.htm", "文本在窗口顶端"
baoku/0109.htm", "复制到的剪贴板"
baoku/0113.htm", "每日提示"
baoku/0116.htm", "查找页面关键字"
baoku/0119.htm", "ASCII字形生成器"
baoku/0120.htm", "HTML的tooltips"
baoku/0122.htm", "HTML在线编辑器"
baoku/0127.htm", "带背景图片的按钮"
baoku/0139.htm", "数据重排1.0"
baoku/0140.htm", "InernetAmp播放器"
baoku/0142.htm", "对象属性显示器"
baoku/0143.htm", "加密页面代码生成器"
baoku/0144.htm", "密码登录生成器"
sys/leavealt.htm", "离开提示"
sys/stxt.htm", "搜索文本"
sys/searchsource.htm", "利用源文件搜索"
sys/sysinfo.htm", "系统信息"
sys/sys_language.htm", "系统语言检测"
sys/finish.htm", "下载完成"
sys/password.htm", "密码"
sys/saveas.htm", "另存为"
sys/help1.htm", "帮助效果"
sys/online.htm", "是否上网"
sys/systest.htm", "信息检测"
sys/mplayer.htm", "音乐播放"
sys/clickcopy.htm", "点击复制"
sys/txtpass.htm", "解密效果"
sys/w_11.htm", "Web对话框"
sys/view.htm", "调试源文件"
sys/indexof.htm", "IndexOf"
sys/comefrom.htm", "访问来源"
("loading")
loading/load1.htm", "效果之一"
loading/load2.htm", "效果之二"
loading/load3.htm", "效果之三"
loading/load4.htm", "效果之四"
baoku/0091.htm", "模拟进度条"
("资料夹")
js.htm", "属性列表"
function.htm", "常用js例子"
keycode.htm", "KeyCode常数用法"
FileSystemObject.htm", "文件系统对象"
("控 制")
"首页"
"我要加入"