课程名称SQL语句.docx

1、课程名称SQL语句1、课程名称：SQL语句2、知识点2.1、上次课程的主要知识点1、 Oracle数据库的四个主要用户： 超级管理员：sys / change_on_install； 普通管理员：system / manager； 普通用户（解锁）：scott / tiger； 海量数据用户（解锁）：sh / sh；2、 sqlplus的主要命令： 设置每行显示的长度：SET LINESIZE 长度； 设置每页显示的长度：SET PAGESIZE 长度； 编辑与执行指令：ed、； 连接用户：CONN 用户名 / 密码 AS SYSDBA； 调用本机程序：HOST 命令；3、 SQL的基本语法S

2、ELECT DISTINCT * | 列 别名 ,列 别名,列 别名 .FROM 表名称 别名WHERE 条件(s)ORDER BY 排序字段 ASC | DESC ,排序字段 ASC | DESC,. ;4、 在WHERE子句里面可以编写多个条件，条件判断：关系运算、逻辑运算、BETWEEN.AND、IN、LIKE、IS NULL；5、 单行函数：UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、INSTR()、SUBSTR()、ROUND()、TO_CHAR()、TO_DATE()、NVL()、DECODE()、SYSDATE、MONTHS_BETW

3、EEN()、ADD_MONTHS()、LAST_DAY()、NEXT_DAY()；2.2、习题讲解1、 找出各月倒数第3天受雇的所有员工。 首先需要确定的是，每一个雇员的雇佣日期是不一样的，那么每一个雇佣日期所在月的最后一天（LAST_DAY()）也肯定不一样，现在要求计算出各月倒数第三天雇佣（日期 数字）。 公式：雇佣日期 = LAST_DAY(雇佣日期) 2；SELECT * FROM empWHERE hiredate=LAST_DAY(hiredate)-2 ;2、 找出早于12年前受雇的员工。 如果要计算年份，那么最简单的做法就是通过月数（MONTHS_BETWEEN()）除以12；

4、SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/1212 ;3、 找出在（任何年份的）2月受聘的所有员工。 如果要想求出一个日期的月数，那么使用TO_CHAR()函数即可。SELECT * FROM emp WHERE TO_CHAR(hiredate,mm)=02 ;SELECT * FROM emp WHERE TO_CHAR(hiredate,mm)=2 ;4、 显示满10年服务年限的员工的姓名和受雇日期。SELECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(SYSDATE

5、,hiredate)/1210 ;5、 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。SELECT ename,hiredate FROM emp ORDER BY hiredate ;6、 显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。 既然要拆分年和月，则使用TO_CHAR()函数完成。SELECT ename,TO_CHAR(hiredate,yyyy) year,TO_CHAR(hiredate,mm) monthsFROM empORDER BY months,year ; 可以发现，现在在Order BY子

6、句里面使用的是别名，在所有子句之中，只有ORDER BY可以使用别名。7、 显示在一个月为30天的情况所有员工的日薪金，忽略余数SELECT ename,TRUNC(sal/30) FROM emp ;8、 以年月日的方式显示所有员工的服务年限。 本题目的含义如下，例如，现在假设一个雇员是在1981-02-14雇佣，而今天的日期是2012年03月27日，那么这个雇员已经被公司雇佣了：30年、1个月、13天； 步骤一：计算出每一个雇员到今天为止雇佣的年份，通过月份操作；SELECT empno,ename,hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE,hireda

7、te)/12) yearFROM emp ; 步骤二：计算出每一个雇员被雇佣的月数，在之前计算年的时候剩下的无法整除的部分就是月，使用MOD()SELECT empno,ename,hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year , TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12) monthsFROM emp ; 步骤三：计算出日 |- 现在唯一讲解过计算天数的方法就是：日期1 日期2 = 数字； |- 日期1应该是当前日期：SYSDATE； |- 日期2？SELECT empno

8、,ename,hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year , TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12) months , TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) dayFROM emp ; 本程序是一个典型的日期函数的操作过程，在开发之中记住了日期函数最大的好处是帮助用户处理闰年的问题。2.3、本次预计讲解的知识点1、 多表查询的实现及注意事项；2、 统计函数与统计查询的操作；3

9、、 子查询并且结合多表查询、限定查询、统计查询完成复杂查询；4、 数据的更新操作及事务处理操作；3、具体内容3.1、多表查询（重点）3.1.1、多表查询的基本概念 在之前所学习的所有查询都是在一张数据表上进行的查询操作，但是从开发而言，有时候会遇到从多张数据表查询数据的情况，那么在这种情况下就被称为多表查询。 但是在讲解多表查询的具体操作之前，那么首先先来看一下多表查询的基本语法。SELECT DISTINCT * | 列 别名 ,列 别名,列 别名 .FROM 表名称 别名 ,表名称 别名,.WHERE 条件(s)ORDER BY 排序字段 ASC | DESC ,排序字段 ASC | DE

10、SC,. ; 下面将emp表和dept表进行关联以完成多表查询，而在进行具体的操作之前，先使用一个COUNT()函数统计一下这两张表中的数据量。范例：查询emp表中的数据量 14条记录SELECT COUNT(*) FROM emp ;范例：查询dept表中的数据良 4条记录SELECT COUNT(*) FROM dept ; 那么也就是说这两张表的记录如果加起来一共才18条记录，下面按照之前给出的多表查询的语法实现多表查询。SELECT * FROM emp,dept ; 现在的结果发现有56条的记录，现在雇员表中的一条数据显示的重复次数正好是部门表中的数据个数，所以所谓的56条结果 =

11、雇员表的14条记录 * 部门表的4条记录，而这样的结果在数据库中被称为笛卡尔积。 虽然清楚了笛卡尔积的产生问题，但是现在所产生的积并没有任何实质上的用处，所以需要想办法将其消除掉，而唯一可以消除掉的方式：既然每一个雇员都有一个其所属的部门编号，那么就使用这个部门编号进行匹配。 现在的操作形式：雇员表.部门编号=部门表.部门编号，但是由于两张表都存在deptno的字段，所以要想成功的消除掉笛卡尔积，就必须对这个字段做一个明确的声明，可以采用“表名称.字段”的形式访问。SELECT * FROM emp,deptWHERE emp.deptno=dept.deptno ; 此条件一旦增加，则不再有

12、任何重复的错误数据产生。 需要注意的是，此时的限定条件“WHERE emp.deptno=dept.deptno”实际上只是消除了显示中笛卡尔积，而实际上由于数据库内部的操作机制，这个笛卡尔积依然存在，那么现在就存在了两个问题。问题一：关于老鸟和菜鸟的区别问题 如果现在你接触到了一个新的数据库，老板让你确定里面的数据，你的第一反应是什么？SELECT * FROM 表名称；SELECT * FROM emp ; 按照这种操作模式，下面在sh用户上使用，使用sh.sales表操作。SELECT * FROM sales ; 但是这个时候如果表中的数据量很大的话，则就成刷屏了，什么也看不见，所以一

13、个有经验的人往往在接触到一张新的数据表的时候，会首先使用COUNT()函数确定一下表中的数据量。SELECT COUNT(*) FROM sales ; 如果发现数据量较大的话，那么可以使用一些操作取出第一条记录观察，而如果数据量小，那么在使用第一种方式直接查看全部数据。 在之前讲解NOT IN之中里面不能够有null，那么现在可以分析一下，如果出现null意味着查询全部，那么表中的数据量一大，那么数据库有可能会崩溃。问题二：虽然使用关联字段消除了显示的笛卡尔积，但是由于内部的处理机制所导致，笛卡尔积依然是一种影响数据库性能的操作的杀手，依然使用sh用户进行观察。范例：确定sales表中的数据

14、量 918843SELECT COUNT(*) FROM sales ;范例：确定costs表中的数据量 82112SELECT COUNT(*) FROM costs ;范例：将两张表关联在一起查询，依然消除笛卡尔积SELECT COUNT(*) FROM costs,sales WHERE costs.prod_id=sales.prod_id ; 通过以上的分析，可以得出：多表查询的性能不高，开发之中应该尽量避免使用多表查询，少去使用多表查询的前提是数据量大，而如果数据量小，则没有任何的问题。 以上已经完成了多表查询的基本实现，但同时也发现一个问题，在程序之中是使用了“表名称.字段”的方

15、式进行了关联字段的匹配，那么如果说现在假设表名称很长呢？例如：yuzhou_yinhexi_diqiu_yazhou_zhongguo_beijing_xicheng_ren，这样的话根本就不方便使用，所以在进行多表查询时，更多的话会使用别名的方式来访问。SELECT * FROM emp e,dept dWHERE e.deptno=d.deptno ;范例：要求查询出每个雇员的编号、姓名、职位、部门的名称、位置 确定所需要的数据表： |- emp表：雇员的编号、姓名、职位； |- dept表：部门名称、位置； 确定已知的关联字段：emp.deptno=dept.deptno；雇员表.dep

16、tno=部门表.deptno；第一步：查询每个雇员的编号、姓名、职位SELECT e.empno,e.ename,e.jobFROM emp e ;第二步：引入部门表，引入的同时还需要增加一个消除笛卡尔积的关联条件SELECT e.empno,e.ename,e.job,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptno ;范例：要求查询出每个雇员的编号、姓名、职位、基本工资，工资等级； 确定所需要的数据表： |- emp表：雇员的编号、姓名、职位、基本工资； |- salgrade表：工资等级； 确定已知的关联字段：emp.sal BE

17、TWEEN salgrade.losal AND salgrade.hisal；SELECT e.empno,e.ename,e.job,e.sal,s.gradeFROM emp e,salgrade sWHERE e.sal BETWEEN s.losal AND s.hisal ;范例：要求查询出每个雇员的编号、姓名、职位、雇佣日期、基本工资、工资等级、部门名称及位置 确定所需要的数据表： |- emp表：雇员的编号、姓名、职位、基本工资、雇佣日期；|- dept表：部门名称、位置； |- salgrade表：工资等级； 确定已知的关联字段： |- 雇员和部门：emp.deptno=de

18、pt.deptno； |- 雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal；第一步：查询雇员的编号、姓名、职位、基本工资、雇佣日期SELECT e.empno,e.ename,e.job,e.sal,e.hiredateFROM emp e ;第二步：引入dept表，查询部门的名称及位置SELECT e.empno,e.ename,e.job,e.sal,e.hiredate,d.dname,d.locFROM emp e ,dept dWHERE e.deptno=d.deptno ;第三步：引入salgrade表，查询工资

19、等级SELECT e.empno,e.ename,e.job,e.sal,e.hiredate,d.dname,d.loc,s.gradeFROM emp e , dept d, salgrade sWHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal ; 如果在多张表查询时，只要是消除笛卡尔积的条件中间都使用AND连接。3.1.2、表的连接 在进行多表查询时，表的连接形式一共有四种：内连接、左（外）连接、右（外）连接、全连接。在之前所讲解的操作严格来讲都属于内连接，因为它必须判断条件，为了更好的说明问题，下面先在emp表中

20、增加一条新的记录。INSERT INTO emp(empno,ename,job,hiredate,sal,comm) VALUES (8888,张三,CLERK,SYSDATE,800,300) ; 这个增加的数据本身并没有其所在的部门（deptno=null），下面依靠此操作来验证内连接的概念。SELECT *FROM emp e,dept dWHERE e.deptno=d.deptno ; 内连接的操作就是将所有满足于条件的数据进行显示，而如果说现在一个没有任何雇员的部门，或者是一个没有部门的雇员，这样的信息都无法显示。 所谓的左（外）连接指的就是可以改变其连接的方向，让左表的数据显示

21、。SELECT *FROM emp e,dept dWHERE e.deptno=d.deptno(+) ; 下面再演示右连接。SELECT *FROM emp e,dept dWHERE e.deptno(+)=d.deptno ; 发现一旦增加了左右连接之后，对应的表中的全部数据就会显示，而没有匹配条件的部分，都将其设置为了null，而“(+)”的标记实际上就是控制左右连接的操作，而且是在Oracle中专用的，其操作如下： 字段1=字段2(+)：“(+)”放在了等号的右边，表示的是左连接； 字段1(+)=字段2：“(+)”放在了等号的左边，表示的是右连接； 在emp表中有一个mgr的字段，

22、表示的是一个雇员所对应的领导的编号，但是领导由于其本身也属于雇员，所以可以根据这个领导编号查询其对应的完整信息。范例：要求显示出每个雇员的姓名、职位、领导姓名 确定所需要的数据表： |- emp表：雇员的姓名、职位； |- emp表：领导姓名； 确定已知的关联字段：emp.mgr=memp.empno；第一步：将emp表进行自身的关联SELECT e.ename,e.job,m.enameFROM emp e,emp mWHERE e.mgr=m.empno ; 一共有14位雇员，但是现在查询完成之后只剩下13位雇员，缺少了一个KING，因为KING没有领导，则内连接的条件不满足。第二步：加入

23、左右连接，使KING显示SELECT e.ename,e.job,m.enameFROM emp e,emp mWHERE e.mgr=m.empno(+) ; 如果在进行数据多表查询时，发现某些数据没有显示出来，就加入左右连接的操作，让数据显示。3.1.3、SQL:1999语法 以上的所有的表连接操作，严格来讲只是一些简单的非标准语法，其中“(+)”更是只有Oracle数据库一种才可以使用，那么既然SQL是一种操作的标准，那么它也提供了一个属于所有数据库所共同支持的表连接语法，语法如下：SELECT table1.column,table2.columnFROM table1 CROSS J

24、OIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name); 以上是SQL:1999给出的完整语法，而这套语法之中，也包含了许多小的方面。1、 交叉连接：CROSS JOIN，语法：SELECT table1.column,table2.columnFROM table1 C

25、ROSS JOIN table2； 所谓的交叉连接实际上指的就是产生笛卡尔积。SELECT * FROM emp CROSS JOIN dept ;2、 自然连接：NATURAL JOIN：SELECT table1.column,table2.columnFROM table1 NATURAL JOIN table2； 自然连接表示的是自动使用一个关联字段，来消除笛卡尔积，关联字段是以后通过约束指定的，现在暂时记住，在emp和dept表中的关联字段是deptno。SELECT * FROM emp NATURAL JOIN dept ;3、 USING子句：SELECT table1.col

26、umn,table2.columnFROM table1 JOIN table2 USING(column_name)| 如果现在在表建立的时候没有设置关联字段，那么就可以通过USING子句手工指定一个关联字段。SELECT * FROM emp JOIN dept USING (deptno);4、 ON子句：SELECT table1.column,table2.columnFROM table1 JOIN table2 ON(table1.column_name=table2.column_name)| ON子句表示由用户自己设置一个消除笛卡尔积的关联条件。SELECT * FROM e

27、mp e JOIN dept d ON (e.deptno=d.deptno);5、 改变连接方向：SELECT table1.column,table2.columnFROM table1 LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name); 实际上这个就表示的是左连接、右连接、全连接。SELECT * FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno);SELECT * FROM emp e RIGHT OUTER JOIN de

28、pt d ON (e.deptno=d.deptno);SELECT * FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno); 在其他数据库之中，这些是肯定要使的数据操作标准。3.1.4、查询的集合操作 在之前所接触都属于两张数据表之间的关联操作，而如果现在有若干个查询，则也可以将查询结果进行连接，而要想实现这种连接操作的话，那么就必须保证若干个查询结果返回的列的格式是一的，集合操作有如下四种：UNION、UNION ALL、INTESECT、MINUS。范例：使用UNION操作SELECT * FROM emp WHERE dep

29、tno=30 UNIONSELECT * FROM emp ; 由于第二个查询里面返回的内容，第一个查询已经存在了，所以重复的记录没有显示，所以UNION就表示将两个查询结果连接在一起，但是重复的记录不显示。范例：使用UNION ALL操作SELECT * FROM emp WHERE deptno=30 UNION ALLSELECT * FROM emp ; UNION ALL就是将所有重复的记录都进行显示。范例：使用INTESECT操作SELECT * FROM emp WHERE deptno=30 INTERSECTSELECT * FROM emp ; 此时返回的是两个查询结果中的

30、相同部分，所以属于交集的操作。范例：验证MINUSSELECT * FROM emp MINUSSELECT * FROM emp WHERE deptno=30 ; 返回的结果是差集。 但是以上的这些符号，个人一般使用较少，只在需要的地方使用，而且保证的前提，是返回的数据列的个数一样。3.1.5、思考题1、 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 确定所需要的数据表： |- emp表：员工的编号、姓名； |- emp表：上级的编号、姓名、年薪； 确定已知的关联字段：emp.mgr=emp.empno；SELECT e.empno,e.ename,m.empno,m.ename,NVL(m.sal*12,0) incomeFROM emp e,emp mWHERE e.mgr=m.empno(+)ORDER BY income DESC ;2、 列出在部门“SALES”（销售部）工作的员工姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 确定所需要的数据表：