第五章 分组函数 -- 分组函数会对结果集中的一组记录计算一次，每一组只返回一个结果1. MAX(列|表达式) MIN(列|表达式)练习：查询emp中员工工资的最大值SELECT MAX(sal), MIN(sal)FROM emp;2. SUM -- 求和 AVG -- 取平均值练习：查询emp表中20部门员工的工资总和SELECT SUM(sal)FROM empWHERE deptno = 20;-- 所有的分组函数都是忽略空值练习：查询emp表中员工平均补助（comm）SELECT AVG(nvl(comm, 0))FROM emp;SELECT 550 *14 FROM dual;3. COUNT -- 计数练习：查询emp表中30部门的员工数SELECT COUNT(*)FROM empWHERE deptno = 30;练习：查询emp表中员工所在的部门有几个SELECT COUNT(DISTINCT deptno)FROM emp;练习：查询emp表中各个部门的工资的最大值SELECT deptno, MAX(sal)FROM empGROUP BY deptnoORDER BY deptno;练习：查询emp表中各个职位员工工资的平均值SELECT job, AVG(sal)FROM empGROUP BY job;-- 必须要使用子查询练习：查询emp表中各个部门的工资最高的员工的姓名以及工资SELECT ename, deptno, MAX(sal)FROM empGROUP BY ename, deptnoORDER BY deptno;-- 两个原则 -- 1. group by 子句中出现的列，可以不在select子句中出现； -- 2. select 子句出现的非分组函数列，一定要在group by子句中出现； 练习：查询emp表中平均工资在2000以上的部门编号和平均工资SELECT deptno, AVG(sal)FROM emp--WHERE AVG(sal) > 2000GROUP BY deptnoHAVING AVG(sal) > 2000;-- 在where子句中不能出现对分组函数结果的判断-- 对分组函数结果的筛选需要在having子句中-- 是SQL语句的执行顺序决定的练习：查询emp表中员工数多于5人的部门编号以及员工数SELECT deptno, COUNT(*)FROM empGROUP BY deptnoHAVING COUNT(*) > 5;-- 分组函数的嵌套(最多嵌套两层)练习：查询emp表中各部门的平均人数SELECT AVG(COUNT(*))FROM empGROUP BY deptno;
第六章 多表查询-- 笛卡尔积-- A = {a, b}-- B = {1,2,3}-- A × B = {(a, 1), (a, 2),(a, 3),(b, 1),(b, 2),(b, 3)}-- 1. 笛卡尔积元素数量非常庞大，非常耗费系统资源-- 2. 有意义的数据只占一小部分-- 结论：在多表查询中一定要避免产生笛卡尔积-- 方法：一定要为连接的表设定连接条件，两个表连接，至少需要1个连接条件。-- 连接条件：一般是两个表的公共字段。-- 公共字段：（狭义）两个表中列名和列的数据类型都相同的那些列；-- （广义）两个表中意义相同的那些列1. 等值连接(连接条件用=来判断的称之为等值连接)练习：查询emp表中员工姓名，部门编号以及部门名称SELECT emp.ename, emp.deptno, dept.deptno, dept.dnameFROM emp, deptWHERE emp.deptno = dept.deptnoORDER BY emp.ename; -- 表别名 -- 表名 表别名 -- 表别名中不能包含特殊字符 -- 如果在SQL中给表起了别名了，那么在当前的SQL的任何地方都不能再使用表名，只能 -- 引用表别名SELECT e.ename, e.deptno, d.deptno, d.dnameFROM emp e, dept dWHERE e.deptno = d.deptnoORDER BY e.ename;2. 不等值连接(连接条件不是用=进行判断)练习：查询emp表中员工姓名，工资以及工资等级SELECT e.ename, e.sal, s.gradeFROM emp e, salgrade sWHERE e.sal BETWEEN s.losal AND s.hisalORDER BY e.ename;练习：查询emp表中20部门员工的姓名，工资，部门名称以及工资等级SELECT E.ENAME, E.SAL, D.DNAME, S.GRADE FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO = 20;3. 外连接-- 外连接的符号（+）永远是在连接条件的缺乏表的一端，要查询哪个表的全部记录，另一张表就是缺乏表练习：列出所有部门名称和员工的姓名，即使这个部门没有员工SELECT d.dname, e.enameFROM emp e, dept dWHERE e.deptno(+) = d.deptnoORDER BY d.dname;练习：查询emp表中员工的姓名和部门编号，即使这个员工没有部门SELECT d.dname, e.enameFROM emp e, dept dWHERE e.deptno = d.deptno(+)ORDER BY d.dname;练习：查询没有员工部门的名称SELECT D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO AND ENAME IS NULL ORDER BY D.DNAME; 作业：查询没有部门员工的姓名4. 自连接-- 查询结果和条件都需要从同一张表中查询的问题-- 用自连接-- 语法晦涩，不常用。-- 日常工作中我们通常用子查询来代替自连接练习：查询emp表中和SMITH同部门的员工信息，不包括SMITHSELECT A.* FROM EMP A, EMP B WHERE A.DEPTNO = B.DEPTNO AND B.ENAME = 'SMITH' AND A.ENAME <> 'SMITH';第七章 子查询-- 查询结果和条件都需要从同一个表中查出来的问题练习：查询emp表中和SMITH同部门的员工信息，不包括SMITHSELECT * FROM EMP WHERE ENAME <> 'SMITH' AND DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');1. 子查询的分类 a. 单行子查询 - 子查询的结果集是一行一列，可以使用单行比较运算符进行比较练习：查询emp表中和SCOTT部门相同，且同领导的员工信息，不包括SMITH；SELECT *FROM empWHERE ename <> 'SCOTT'AND deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT')AND mgr = (SELECT mgr FROM emp WHERE ename = 'SCOTT');练习：查询emp表中部门人数多于平均部门人数的部门编号和人数SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNOHAVING COUNT(*) > (SELECT AVG(COUNT(*)) FROM EMP GROUP BY DEPTNO); b. 多行子查询 - 子查询的结果集是多行一列的，不能使用单行比较运算符，要使用in练习：查询emp表中哪些员工是领导，列出他们的信息SELECT *FROM empWHERE empno IN (SELECT mgr FROM emp);练习：查询emp表中哪些员工不是领导，列出他们的信息SELECT *FROM empWHERE empno NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);-- 1. 子查询结果集中出现了null-- 2. not in不忽略空值，在子查询中不能出现空值 a IN (1, 2, NULL) ==> a = 1 OR a = 2 OR a = NULL a NOT IN (1, 2, NULL) ==> a <> 1 AND a <>2 AND a <> NULL c. 多列子查询 - 查询结果集是多行多列形式练习：查询emp表中各个部门的工资最高的员工的姓名以及工资SELECT deptno, ename, salFROM empWHERE (deptno, sal) IN (SELECT deptno, MAX(sal) FROM emp GROUP BY deptno)ORDER BY deptno;(a,b) IN ((1,1), (2,2),(3,3));==> a = 1 AND b = 1 OR a = 2 AND b = 2 OR a = 3 AND b = 3; 2. FROM 子句中的子查询 -- 内联视图 - 视图：是一个虚表。视图也是表，所以和表的操作一样。但是视图没有实体数据，只是表的另一种表现方式 -- From子句中的子查询一定要给它起一个别名，作为视图名 -- 如果内联视图中出现了函数列或表达式列，一定要给这样的列起一个别名，就作为对应列的列名SELECT e.deptno, e.ename, e.salFROM emp e, (SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) aWHERE e.deptno = a.deptno AND e.sal = a.maxsalORDER BY e.deptno;练习：查询emp表中各个部门的工资最高的员工的姓名，工资以及部门名称SELECT e.deptno, e.ename, e.sal, d.dnameFROM emp e, (SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) a, dept dWHERE e.deptno = a.deptno AND e.sal = a.maxsal AND e.deptno = d.deptnoORDER BY e.deptno;SELECT B.ENAME, B.SAL, B.DNAME FROM (SELECT E.ENAME, E.SAL, D.DNAME, E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO) B, (SELECT DEPTNO, MAX(SAL) MAXSAL FROM EMP GROUP BY DEPTNO) A WHERE A.DEPTNO = B.DEPTNO AND A.MAXSAL = B.SAL; -- Top-N问题-- 伪列 rownum -- 所有的查询结果集中都有这样一个伪列 -- 作用是给结果集的记录进行编号 SELECT ROWNUM, dname FROM dept; -- 1. rownum编号是在排序之前已经编好的。 练习：查询emp表中工资由高到低排在前三位的员工的信息SELECT rownum, a.ename, a.salFROM (SELECT ename, sal FROM emp ORDER BY sal DESC) aWHERE ROWNUM <= 3; -- 2. rownum 只能用 < 或<=来判断，不能用>或>=来判断，如果用=判断，只能=1 -- rownum 会动态重新编号 练习：查询emp表中工资由高到低排在4~6位的员工的信息 SELECT b.* FROM (SELECT ROWNUM r,a.ename bname,a.sal bsal FROM (SELECT ename,sal FROM emp ORDER BY sal DESC ) a WHERE ROWNUM <=6 ) b WHERE b.r>=4 SELECT B.* FROM (SELECT ROWNUM R, A.ENAME, A.SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) A WHERE ROWNUM <= 6) B WHERE B.R >= 4; -- 分页问题 -- CREATE TABLE TEST(N NUMBER); 表中的数据都是正整数，而且不重复 SELECT * FROM TEST ORDER BY N; 写一个SQL语句查询所有连续号码段的起始和结束号码 minnum maxnum 3 5 7 10 12 13 1. 方法1 SELECT AA.N MINNUM, BB.N MAXNUM FROM (SELECT ROWNUM R, A.N FROM (SELECT N FROM TEST WHERE (N - 1) NOT IN (SELECT N FROM TEST) ORDER BY N) A) AA, (SELECT ROWNUM R, B.N FROM (SELECT N FROM TEST WHERE (N + 1) NOT IN (SELECT N FROM TEST) ORDER BY N) B) BB WHERE AA.R = BB.R; 2. 方法2 数据 - 数列 = 结果 n m n-m n+1 m+1 n-m n+2 m+2 n-m n+4 m+3 n-m+1 n+5 m+4 n-m+1 n+6 m+5 n-m+1 n+9 m+6 n-m+3 n+10 m+7 n-m+3 SELECT MIN(B.N) MINNUM, MAX(B.N) MAXNUM FROM (SELECT A.N - ROWNUM R, A.N FROM (SELECT N FROM TEST ORDER BY N) A) B GROUP BY B.R ORDER BY MINNUM; SELECT MIN(A.N) MINNUM, MAX(A.N) MAXNUM FROM (SELECT N FROM TEST ORDER BY N) A GROUP BY A.N - ROWNUM ORDER BY MINNUM;
第八章 数据操作与事务控制一. 数据插入 练习：向DEPT表中插入记录，编号 50， 部门名称 '实训', 部门所在地 '沈阳' INSERT INTO dept (dname, deptno, loc) VALUES ('实训', 50, '沈阳'); INSERT INTO dept VALUES (51, '实训', '沈阳'); 练习：向DEPT表中插入记录，编号 60， 部门名称 '商用', 部门所在地待定 -- 1. 直接用null关键字 INSERT INTO dept VALUES (60, '商用', NULL); -- 2. 可以用两个连续的单引号来代表空值 INSERT INTO dept VALUES (61, '商用', ''); -- 3. 可以在指定列表的时候忽略空值列，被忽略的列如果没有设定默认值的话，就会插入空值 INSERT INTO dept (deptno, dname) VALUES (62, '商用'); 练习：将自己的信息插入到emp表中(2012-3-5)。INSERT INTO emp VALUES (9999, 'Liu', 'CLERK', 7788, to_date('2012-3-5', 'YYYY-MM-DD'), 5001, NULL, 10);- 插入多条数据INSERT INTO emp1 SELECT * FROM emp WHERE deptno = 30;二. 更新数据练习：把emp表中scott的工资更新成3100UPDATE emp SET sal = 3100 WHERE ename = 'SCOTT';练习：给emp表中SMITH涨工资100UPDATE emp SET sal = sal + 100 WHERE ename = 'SMITH';练习：将SMITH的领导,部门更新跟SCOTT一样UPDATE emp SET mgr = (SELECT mgr FROM emp WHERE ename = 'SCOTT') , deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT')WHERE ename = 'SMITH';练习：给30部门工资最低的员工涨100UPDATE emp SET sal =sal + 100WHERE deptno = 30 AND sal = (SELECT MIN(sal) FROM emp WHERE deptno = 30);SELECT * FROM emp;练习：将自己的部门调到20部门，工资更新成20部门的平均工资 + 100，并四舍五入到个位三. 删除数据DELETE [FROM] 表名[WHERE 条件];DELETE FROM emp1; -- 删除emp1所有数据练习：将本部门工资比自己高的员工记录删除DELETE FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP E, (SELECT DEPTNO, SAL FROM EMP WHERE EMPNO = 9999) A WHERE E.DEPTNO = A.DEPTNO AND E.SAL > A.SAL);第九章 表和约束(DDL)一. CREATE-- 作用：创建数据库对象-- create DDL语句，隐式提交，不能回滚CREATE TABLE [SCHEMA.]表名 -- schema方案名（用户名）( 列名1 数据类型1 [DEFAULT 表达式], 列名2 数据类型2 [DEFAULT 表达式], ... 列名N 数据类型N [DEFAULT 表达式]); - 数据类型 -1. 数字型 - NUMBER[(p[, s])] - - p 定义了数字的总长度 - - s 定义了小数位的长度 - 例子 NUMBER(7,2) - NUMBER(4) - NUMBER - 2. 字符型 - a. VARCHAR2(n) - 可变长度的字符类型 - - n代表字符串的长度，n一定要设定，不能省略 - - n <= 4000 - b. CHAR[(n)] - 固定长度的字符型 - - n可以省略，n默认是1 - - n <= 2000 - 3. LOB 型 -- 容纳4GB的数据 - a. 内部lob - CLOB -- 4GB字符型数据 - BLOB -- 4GB二进制数据 - b. 外部lob - BFILE -- 4GB的外部文件 - 4. DATE - 日期型 练习：创建一个 dossier表 dID 数字型 dname 可变长度字符型 30 dheight 数字型 3 dbirthday 日期型 dcountrycode 固定长度字符型，默认值是‘01’ CREATE TABLE dossier( did NUMBER, dname VARCHAR2(30), dheight NUMBER(3), dbirthday DATE, dcountrycode CHAR(2) DEFAULT '01');DROP TABLE dossier INSERT INTO dossier VALUES (1, '姚明', 226, to_date('1980-10-6', 'YYYY-MM-DD'), DEFAULT);COMMIT;INSERT INTO dossier (did, dname, dheight, dbirthday) VALUES (1, '姚明', 226, to_date('1980-10-6', 'YYYY-MM-DD'));SELECT * FROM dossier; - 子查询建表 CREATE TABLE 表名 AS 子查询; CREATE TABLE emp2 AS SELECT * FROM emp WHERE deptno =10; SELECT * FROM emp2; 练习：用20部门员工的姓名和年薪 创建一个新表 annemp20CREATE TABLE annemp20ASSELECT ename, (sal + NVL(comm, 0)) * 12 annsalFROM empWHERE deptno = 20;SELECT * FROM User_Tables;SELECT ROWID, dnameFROM dossier;二. ALTER -- 属于DDL语句，自动提交，不能回滚 -- 修改对象的属性 1. 添加列 ALTER TABLE 表名 ADD (列名 数据类型[,...]); 练习：在dossier表中添加一列 sex 列，1位长度固定长度字符型ALTER TABLE dossier ADD (sex CHAR); 2. 修改列 ALTER TABLE 表名 MODIFY (列名 属性值); -- 1. 列名一定是在表中存在的列 -- 2. 修改那些属性，就把哪些属性放在列名后面就可以了 练习：为sex列添加默认值，默认为‘1’ALTER TABLE dossier MODIFY (sex DEFAULT '1'); -- 默认值的修改对表中现有列数据没有任何影响 -- 只会影响以后插入的数据。 -- 将某列更新成其默认值 UPDATE dossier SET sex = DEFAULT WHERE did = 1; 3. 删除列 1> ALTER TABLE 表名 DROP COLUMN 列名; 2> ALTER TABLE 表名 DROP (列名列表); 练习：删除表中sex列ALTER TABLE dossier DROP (sex); -- 注意：删除列会将列的定义和数据一并删除，不能回滚，在使用之前一定要慎重 -- 必要的时候要现将表中数据备份后再删除。 -- 表中必须至少要含有一列。 三. DROP -- DDL语句，隐式提交不能回滚 -- 删除数据库对象 DROP TABLE 表名; DROP TABLE emp1; 四. 重命名表RENAME oldname TO NEWname;RENAME dossier TO ouxiang; SELECT *FROM dossier;五. TRUNCATETRUNCATE TABLE 表名;-- 截断：删除表中所有的数据；TRUNCATE TABLE emp2;DELETE FROM emp2;-- 两种语句的区别-- 1. truncate是DDL语句，自动提交，不能回滚，而Delete是DML语句，能回滚；-- 2. truncate在删除数据的同时，会释放存储空间，delete不会做这样的事；-- 3. truncate只能删除所有数据，delete是可以有选择的删除；-- 4. truncate的效率要远远的高于delete，表越大越明显。回滚段五. 约束 定义在表中某列上的规则，防止错误数据进入表中； 1. 五种约束 1> NOT NULL ：非空约束，插入到该列的数据不能为null； 2> UNIQUE ： 唯一键 约束， 该列数据在表中是唯一的，即不能重复； 3> PRIMARY KEY: 主键约束。表中的列或列的组合，其值在表中 可以唯一的确定一条记录； 一个表只能有一个主键。 主键的值必须是非空且唯一的。 在表设计的时候，所有的表必须有主键。 4> FOREIGN KEY : 外键约束。表A和表B的公共字段如果是表A的主键或唯一键的话，那么 就是表B的外键。表A通常成为父表，表B称为子表。 外键是定义在子表上的。 i. 子表的公共字段中不能出现父表中没有的值 UPDATE emp SET deptno = 90 WHERE empno = 7788; ii. 如果父表中的某一个值被子表所引用的话，这条数据不能被删除； DELETE FROM dept WHERE deptno = 10; 5> CHECK ：检查约束。在某列上定义的一个boolean表达式，要求插入到该列的值必须要保证 boolean表达式为真，否则无法插入。 2. 约束定义 1> 列级约束定义 CREATE TABLE dossier( did NUMBER PRIMARY KEY, dname VARCHAR2(30) NOT NULL, dheight NUMBER(3) UNIQUE, dbirthday DATE, dcountrycode CHAR(2) DEFAULT '01' REFERENCES country(countrycode)); 2> 表级约束定义 CONSTRAINT 约束名 约束类型(列名) CREATE TABLE dossier( did NUMBER, dname VARCHAR2(30), dheight NUMBER(3), dbirthday DATE NOT NULL, dcountrycode CHAR(2) DEFAULT '01', CONSTRAINT dossier_did_pk PRIMARY KEY(did), CONSTRAINT dossier_dname_uk UNIQUE(dname), CONSTRAINT dossier_dheight_ck CHECK(dheight > 0), CONSTRAINT dossier_countycode_fk FOREIGN KEY(dcountrycode) REFERENCES county(countrycode) );-- not null只能做列级的约束定义，不能做表级的约束定义练习：创建一个country表 countrycode 两个字符长度固定字符类型 主键 countryname 非空 CREATE TABLE country ( countrycode CHAR(2), countryname VARCHAR2(30) NOT NULL, CONSTRAINT county_code_pk PRIMARY KEY(countrycode) ); INSERT INTO country VALUES('01', '中国'); SELECT * FROM country; 3> 给现有的表添加约束 ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(列名);练习：给dossier表添加主键约束ALTER TABLE dossier ADD CONSTRAINT dossier_did_pk PRIMARY KEY(did); 练习：给dossier表的dname列，添加唯一键约束ALTER TABLE dossier ADD CONSTRAINT dossier_dname_uk UNIQUE(dname); 练习：给dossier表添加check约束，身高不能小于0ALTER TABLE dossier ADD CONSTRAINT dossier_dheight_ck CHECK(dheight > 0);练习：给dossier表的生日列添加非空约束-- ALTER TABLE dossier ADD CONSTRAINT dossier_dbirthday_nn NOT NULL(dbirthday);ALTER TABLE dossier MODIFY (dbirthday NOT NULL); ALTER TABLE dossier ADD CONSTRAINT dossier_dbirthday_ck CHECK (dbirthday IS NO NULL);练习：给dossier表添加外键约束ALTER TABLE dossier ADD CONSTRAINT dossier_dcountrycode_fk FOREIGN KEY(dcountrycode) REFERENCES country(countrycode); 4> 删除约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名; 练习：删除dossier表的唯一键约束ALTER TABLE dossier DROP CONSTRAINT dossier_dname_uk;练习：删除country表的主键约束ALTER TABLE country DROP CONSTRAINT county_code_pk CASCADE;
