此SQL笔记为听网易云课堂李兴华老师的课程后整理的笔记，感谢李兴华老师，讲的很详细很系统。
格式化指令：
|- SET LINESIZE 300; //设置每行显示的数据长度，需右击命令框-属性-布局-两个宽度设置为120
|- SET PAGESIZE 30; //设置表格数据每页显示的最大长度
|- COL列标题 FOR A参数; //设置某列显示长度为10
SQL下启动记事本程序：
|- ed 文件名 //输入命令保存入记事本（文件后缀缺省值为.sql）
|- @文件名 //执行文本中的命令（文件后缀缺省值为.sql）
切换账号登录：
|- CONN用户名/密码 [AS SYSDBA] //如果是使用sys账号登录，必须写上AS SYSDBA
|- showuser //显示当前用户名
调用本地pc命令：
|- HOST 本地命令参数 //例：HOST copy d:\temp.txtd:\1.txt
scott用户表结构：
|- SELECT * FROM tab； //查询当前用户下的所有数据表
|- DESC 表名； //查询一个数据表的结构
----------------------------------------------------------------------------------------------------------------
复杂查询=简单查询+限定查询+多表查询+分组统计查询+子查询。
简单查询：
|- SELECT [DISTINCT] * FROM 表名称[别名]； //DISTINCT：不显示重复项
|- SELECT[DISTINCT] 列名称 [别名]，列名称 [别名]，...FROM表名称[别名]；以上结构，首先执行FROM字句，再执行SELECT字句。DISTINST去除重复项。
| 例1：SELECT * FORM EMP; //查询EMP表中全部数据 |
| 例2：SELECT ENAME,JOB,SAL FROM EMP; //查询EMP表中的指定数据（姓名，工作，工资） |
| 例3：SELECT ENAME,JOB,SAL*12 SALYEAR FROM EMP; //工资数据乘12，并设置为别名SALYEAR |
|- SELECT列名称||列名称 [别名] FROM表名称; //将多个列的内容合并为一个类显示（插入的字符串用“ ‘ ”声明）
列：SELECT 'NAME:'||ENAME||',JOB:'||JOB||',SAL:'||SAL INFORMATION FROM EMP; //合并输出 ”AME: 姓名,JOB:工作,SAL:工资” 的格式
---------------------------------------------------------------------------------------------------------------
限定查询：
|- SELECT [DISTINCT] *|列名称 [别名]，列名称 [别名]，... //③控制需要显示的列数据
FROM 表名称 [别名] //①确定数据来源
[WHERE 过滤条件]； //②筛选满足条件的数据行
|- 关系运算符：>、<、>=、<=、<>（!=）；
|- 逻辑运算符：AND、OR、NOT；
|- 范围运算符：BETWEEN...AND；
|- 谓词范围： IN、NOTIN； //NOT IN的查询范围内不允许出现NULL，否则返回查询结果NULL
|- 空判断： IS NULL、ISNOT NULL；
|- 模糊查询： LIKE。
|- “_”：匹配任意的一位字符
|- “%”：匹配任意的零位、一位、多位字符
| 例1：SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB!='SALESMAN' AND JOB!='CLERK'; |
| 例2：SELECT * FROM EMP WHERE JOB='SALESMAN' OR SAL>=2000; |
| 例3：SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-1月-81' AND '31-12月-1981'; |
| 例4：SELECT *FROM EMP WHERE EMPNO IN(7499,7782,7900,9999); |
| 例5：SELECT *FROM EMP WHERE COMM IS NOT NULL; |
| 例6：SELECT * FROM EMP WHERE ENAME LIKE '_A%'; //姓名第二位为A |
---------------------------------------------------------------------------------------------------------------
排序查询：
|- SELECT [DISTINCT] *|列名称 [别名]，列名称 [别名]，... //③控制需要显示的列数据
FROM 表名称 [别名] //①确定数据来源
[WHERE 过滤条件] //②筛选满足条件的数据行
[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC], ... ]； //④排序，ASC（缺省默认）：升序排列，DESC：降序排列。SQL语句中只有ORDER BY可以调用SELECT字句中的别名
| 例1：SELECT *FROM EMP WHERE JOB='SALESMAN' ORDER BY SAL DESC,HIREDATE ASC; |
| 例2：SELECT EMPNO,ENAME,SAL*12 INCOME FROM EMP ORDER BY INCOME; |
-----------------------------------------------------------------------------------------------------------------
单行函数： //完成某一功能的操作函数，例：转大小写、日期格式转换等
|- 字符串函数
| 函数 | 返回值 | 功能 |
| UPPER（列|字符串） | String | 将传入的字符串变为大写 |
| LOWER（列|字符串） | String | 将传入的字符串变为小写 |
| INITCAP（列|字符串） | String | 开头首字母大写，其余字母小写 |
| LENGTH（列|字符串） | Int | 取得指定字符串的长度 |
| SUBSTR（列|字符串，开始索引[，长度]） | String | 进行字符串的截取，字符串首字母的索引为1 |
| REPLACE（列|字符串，旧内容，新内容） | String | 将指定字符串的指定部分字符替换 |
| 例1：SELECT UPPER(‘hellO’) FROM DUAL; //DUAL为虚拟表，供实验用 |
| 例2：SELECT LOWER(ENAME) ENAME FROM EMP; |
| 例3：SELECT * FROM EMP WHERE ENAME=UPPER(‘&input’); //实现数据输入操作，&input为用户输入 |
| 例4：SELECT ENAME,SUBSTR(ENAME,-3) FROM EMP; //截取最后三位字符，Oracle独有 |
|- 数值函数：
| 函数 | 功能 |
| ROUND（列|数值[，小数位数]） | 实现数值四舍五入，小数位数缺省值为0，负数则整数部分四舍五入 |
| TRUNC（列|数值[，小数位数]） | 实现数值的截取（不四舍五入），小数位数缺省值为0，负数则整数部分截取 |
| MOD（列|数值，列|数值） | 求模（计算余数） |
|- 日期函数：
| 函数 | 返回值 | 功能 |
| SYSDATE | 当前日期
| |-日期+数字=日期（若干天后的日期） |-日期-数字=日期（若干天前的日期） |-日期-日期=数字（天数） |
| SYSTIMESTAMP | 时间戳 | 返回当前日期和时间 |
| ADD_MONTHS（列|日期，月数） | 日期 | 在指定日期上增加若干个月之后的日期 |
| MONTHS_BETWEEN（列|日期，列|日期） | Int | 返回两个日期之间所经历的月数 |
| LAST_DAY（列|日期） | 日期 | 返回指定日期所在月的最后一天 |
| NEXT_DAY（列|日期） | 日期 | 返回下一个指定的一周时间数对应的日期 |
| 例1：SELECT * FROM EMP WHERE HIREDATE=(LAST_DAY(HIREDATE)-2); |
| 例2：SELECT NEXT_DAY(SYSDATE,'星期天') FROM DUAL; //下一个星期天日期 |
| 例3： SELECT ENAME,HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) YRAE, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12)) MONTH, TRUNC(SYSDATE-ADD_MONTHS(HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE))) DAY FROM EMP; //HIREDATE与现在时间的年-月-日的差 |
|- 转换函数：
| 函数 | 返回值 | 功能 |
| TO_CHAR（列|日期|数字，转换格式） | String | 将日期或数值格式化为指定结构的字符串 |
| TO_DATE（列|字符串，转换格式） | 日期 | 按照指定的转换格式编写字符串后将其变为日期型数据 |
| TO_NUMBER（列|字符串） | 数字 | 将字符串转换为数值 |
|- 转换格式
|- 日期：年（yyyy）、月（mm）、日（dd）；
|- 时间：时（hh|hh24）、分（mi）、秒（ss）；
|- 数字：任意数字（9）、本地货币符号（L）。
| 例1：SELECT TO_CHAR(SYSDATE,'YYYY/mm/dd hh24:mi:ss') FROM DUAL; |
| 例2： SELECT TO_CHAR(SYSDATE,'yyyy'),TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM DUAL; //拆分日期的年月日 |
| 例3：SELECT *FROM EMP WHERE TO_CHAR(HIREDATE,'mm')='02'; //或者用=2 |
| 例4：SELECT TO_CHAR(123872181,'L999,999,999,999') NAME FROM DUAL; |
| 例5：SELECT TO_DATE('1989-12-1','yyyy-mm-dd') FROM DUAL; |
|- 通用函数
| 函数 | 返回值 | 功能 |
| NVL（列|NULL，默认值） | 数字 | 如果传入内容是NULL，则使用默认数值处理，如果不是NULL则使用原始数据处理 //NULL参与的计算结果均为NULL |
| DECODE（列|字符串|数值，比较内容1，显示内容1，比较内容2，显示内容2...[，默认显示内容]） | 数据类型 | 设置的内容会与每一个比较内容进行比较，如果内容相同，则会使用显示内容进行输出，如果都不相同，则使用最后的默认信息输出 |
| 例1：SELECT ENAME,SAL,COMM,(SAL+NVL(COMM,0))*12 INCOME FROM EMP; |
| 例2：SELECT ENAME,JOB,DECODE(JOB,'CLERK','办事员','SALESMAN','销售员') 替换 FROM EMP; //没有匹配项且无默认显示内容的项，显示NULL |
| 例3：SELECT ENAME,JOB,DECODE(JOB,'CLERK','办事员','SALESMAN','销售员',JOB) 替换 FROM EMP; |
--------------------------------------------------------------------------------------------------------------
多表查询： //性能很差!多表查询必须消除笛卡尔积
|- SELECT [DISTINCT] *|列名称 [别名]，列名称 [别名]，... //③控制需要显示的列数据
FROM 表名称 [别名]，表名称 [别名] //①确定数据来源
[WHERE 过滤条件] //②筛选满足条件的数据行
[ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC], ... ]； //④排序
| 例1：SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; //笛卡尔积 |
| 例2：SELECT * FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO; //使用表名 |
| 例3：SELECT e.ENAME,e.JOB,e.SAL,d.LOC FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO; |
| 例4：SELECT e.ENAME,e.JOB,e.SAL,,d.DNAME,d.LOC,g.GRADE FROM EMP e,DEPT d,SALGRADE g WHERE (e.DEPTNO=d.DEPTNO) AND (e.SAL BETWEEN g.LOSAL AND HISAL); |
|- 表的连接：
|- 内连接（等值连接）：所有满足条件的数据都会显示；
|- 外连接（左外连接、右外连接、全外连接）：控制左表与右表的数据是否全部显示；
//（+）Oracle独有语法
|- 左外连接：字段 = 字段（+）；//让等号左的字段数据全部显示
|- 右外连接：字段（+） = 字段；//让等号右的字段数据全部显示
//SQL1999通用语法
|- SELECT [DISTINCT] *|列 [别名]
FROM 表名称1
[CROSS JOIN表名称2]
[NATURALJOIN表名称2]
[JOIN表名称 ON（条件）|USING（字段）]//USING，指定关联字段；没有关联项，用ON设置关联条件
[LEFT|RIGHT |FULL OUTER JOIN表名称2]；
| 例1：SELECT e.ENAME,e.JOB,e.DEPTNO,d.DEPTNO,d.DNAME FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO; //内连接 |
| 例2：SELECT e.ENAME,e.JOB,e.DEPTNO,d.DEPTNO,d.DNAME FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO(+); //左连接，左字段表数据全部显示 |
| 例3：SELECT e.ENAME,e.JOB,e.DEPTNO,d.DEPTNO,d.DNAME FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO; //右链接，右字段表数据全部显示 |
| 例4：SELECT e.EMPNO,e.ENAME,e.JOB,e.MGR,d.ENAME FROM EMP e,EMP d WHERE e.MGR=d.EMPNO(+); //e的表格数据全部显示 |
| 例5：SELECT * FROM EMP CROSS JOIN DEPT; //交叉连接，产生笛卡尔积 |
| 例6：SELECT * FROM EMP NATURAL JOIN DEPT; //自动找到关联项，并内连接 |
| 例7：SELECT *FROM EMP JOIN DEPT USING(DEPTNO); //指定关联项进行内连接 |
| 例8：SELECT *FROM EMP e JOIN SALGRADE s ON (e.SAL BETWEEN s.LOSAL AND s.HISAL); |
| 例9：SELECT * FROM EMP LEFT OUTER JOIN DEPT USING(DEPTNO); //左连接 |
数据集合操作： //将多个处理的表合并显示，列一定要相同
|- SELECT [DISTINCT] *| 列名称 [别名]，列名称 [别名]，...
FROM 表名称 [别名]，表名称 [别名]
[WHERE 过滤条件]
[ORDERBY 字段 [ASC|DESC],字段 [ASC|DESC], ... ]
UNION|UNION ALL|INTERSECT|MINUS
SELECT [DISTINCT] *|列名称 [别名]，列名称 [别名]，...
FROM 表名称 [别名]，表名称 [别名]
[WHERE 过滤条件]
[ORDERBY 字段 [ASC|DESC],字段 [ASC|DESC], ... ]
...
//UNION：如果遇见相同的内容，不会重复显示;
//UNION ALL:如果遇见相同的内容，重复显示;
//INTERSECT：返回相同的数据部分，交集操作;
//MINUS：第一个查询结果减去后者的查询结果，差集操作
------------------------------------------------------------------------------------------------------------
基础统计函数：
|- 统计个数：COUNT（*|[DISTINCT]字段）
|- 最大、最小：MAX（字段）、MIN（字段）
|- 合、平均值：SUN（字段）、AVG（字段）
分组统计：
|- SELECT [DISTINCT] 分组字段 [别名]，...|统计函数 //⑤
FROM 表名称 [别名]，表名称 [别名] //①
[WHERE 过滤条件] //②WHERE不允许使用分组函数
[GROUPBY 分组字段，分组字段，...] //③SELECT出现的字段（非统计函数），必须出现在GROUP BY中，GROUP BY的字段，只能多，不能少
[HAVING 分组后的过滤条件] //④对分组后的数据筛选,即可以使用分组函数进行数据筛选
[ORDER BY字段 [ASC|DESC], 字段 [ASC|DESC], ... ]； //⑥
| 例1: SELECT JOB,COUNT(EMPNO),AVG(SAL) FROM EMP GROUP BY JOB; |
| 例2：SELECT DEPTNO,COUNT(*),MAX(SAL),MIN(SAL) FROM EMP GROUP BY DEPTNO; |
| 例3：SELECT d.DNAME,COUNT(e.ENAME),AVG(e.SAL) FROM EMP e,DEPT d WHERE d.DEPTNO=e.DEPTNO(+) GROUP BY d.DNAME; |
| 例4：SELECT d.DEPTNO,d.DNAME,d.LOC,COUNT(e.ENAME), AVG(MONTHS_BETWEEN(SYSDATE,e.HIREDATE)/12) FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO GROUP BY d.DEPTNO,d.DNAME,d.LOC; |
| 例5：SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL)>2000; |
显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按月工资的合计升序排序
| SELECT JOB,SUM(SAL) FROM EMP WHERE JOB!='SALESMAN' GROUP BY JOB HAVING SUM(SAL)>5000 ORDER BY SUM(SAL); |
统计公司所有领取佣金与不领取佣金的雇员人数、平均工资
| SELECT '领取' title,COUNT(ENAME),AVG(SAL) FROM EMP WHERE COMM IS NOT NULL UNION SELECT '不领取' title,COUNT(ENAME),AVG(SAL) FROM EMP WHERE COMM IS NULL; |
子查询：//相对于多表查询，性能更高
|- SELECT [DISTINCT] 分组字段 [别名]，...|统计函数（子查询）
FROM 表名称 [别名]，表名称 [别名]（子查询）
[WHERE 过滤条件（子查询）]
[GROUPBY 分组字段，分组字段，...]
[HAVING分组后的过滤条件（子查询）]
[ORDER BY字段 [ASC|DESC], 字段 [ASC|DESC], ... ]；
WHERE子查询：子查询一般会返回单行单列、单行多列、多行单列；
HAVING子查询：子查询会返回单行单列，且使用统计函数；
FROM子查询：子查询返回多行多列（表结构），子查询的统计函数必须用别名；
SELECT子查询：返回单行单列，一般不使用。
|- ANY
=ANY（单列数据），相当于IN；
>ANY（单列数据），大于单列数据的最小值；
<ANY（单列数据），小于单列数据的最大值。
|- ALL
>ALL（单列数据），大于单列数据的最大值；
<ALL（单列数据），小于单列数据的最小值。
| 例1: SELECT * FROM EMP WHERE SAL<( SELECT AVG(SAL) FROM EMP ); //子查询返回单行单列的平均工资数据 |
| 例2：SELECT * FROM EMP WHERE (JOB,SAL)=( SELECT JOB,SAL FROM EMP WHERE ENAME=’SCOTT’) AND (ENAME!=’SCOTT’); //返回单行多列 |
| 例3：SELECT * FROM EMP WHERE SAL NOT IN ( SELECT SAL FROM EMP WHERE JOB=’MANAGER’); //返回多行单列 |
| 例4：SELECT * FROM EMP WHERE MGR NOT IN ( SELECT MGR FROM EMP); //返回的多行单列包含NULL，NOT IN返回NULL |
| 例5：SELECT * FROM EMP WHERE MGR =ANY ( SELECT MGR FROM EMP); |
| 例6：SELECT JOB,COUNT(ENAME),AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL)>( SELECT AVG(SAL) FROM EMP); |
| 例7：SELECT e.EMPNO,e.ENAME,e.JOB,( SELECT d.DNAME FROM DEPT d WHERE e.DEPTNO=d.DEPTNO) FROM EMP e; //1+N次查询，不推荐使用 |
| 例8：SELECT d.DNAME,d.LOC,COUNT(e.ENAME) FROM EMP e,DEPT d WHERE e.DEPTNO(+)=d.DEPTNO GROUP BY d.DNAME,d.LOC; //多表查询效率低 |
| 例9：SELECT d.DEPTNO,d.DNAME,d.LOC,e.COUNT FROM DEPT d,( SELECT DEPTNO,COUNT(ENAME) COUNT FROM EMP GROUP BY DEPTNO) e WHERE d.DEPTNO=e.DEPTNO(+); //相对于例8，子查询性能高！！！ |
综合：
列出薪金高于在部门30工作的所有员工的姓名和薪金、部门名称、部门人数。
| SELECT e.ENAME,e.SAL,d.DNAME,c.COUNT FROM EMP e,DEPT d,( SELECT DEPTNO,COUNT(ENAME) COUNT FROM EMP GROUP BY DEPTNO ) c WHERE e.DEPTNO=d.DEPTNO AND d.DEPTNO=c.DEPTNO AND e.SAL>ALL( SELECT SAL FROM EMP WHERE DEPTNO=30 ); |
列出与‘SCOTT’从事相同工作的所有员工及部门名称、部门人数和领导姓名。
| SELECT e.ENAME,d.DNAME,c.COUNT,t.ENAME FROM EMP e,DEPT d,( SELECT DEPTNO,COUNT(ENAME) COUNT FROM EMP GROUP BY DEPTNO ) c,EMP t WHERE e.JOB=( SELECT JOB FROM EMP WHERE ENAME='SCOTT' ) AND e.DEPTNO=d.DEPTNO AND d.DEPTNO=c.DEPTNO AND e.MGR=t.EMPNO AND e.ENAME!='SCOTT'; |
列出薪金比’SMITH’或’ALLEN’多的所有员工的编号、姓名、部门名称、领导姓名，部门人数、平均工资、最高最低工资。
| SELECT e.EMPNO,e.ENAME,d.DNAME,e2.ENAME,c.COUNT,c.AVG,c.MAX,c.MIN FROM EMP e,DEPT d,( SELECT EMPNO,ENAME FROM EMP ) e2,( SELECT DEPTNO,COUNT(ENAME) COUNT,AVG(SAL) AVG,MAX(SAL) MAX,MIN(SAL) MIN FROM EMP GROUP BY DEPTNO ) c WHERE e.SAL>ANY( SELECT SAL FROM EMP WHERE ENAME IN ('SMITH','ALLEN') ) AND e.DEPTNO=d.DEPTNO AND d.DEPTNO=c.DEPTNO AND e.MGR=e2.EMPNO(+) AND e.ENAME NOT IN ('SMITH','ALLEN'); |
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
