SQL 语法
1、课程名称： SQL语法
2、知识点
2.1、上次课程的主要知识点
1、DML语法全部讲解完成；
2、多表查询：首先要确定数据显示列所在的表，而后如果有多张表连接查询，肯定会存在各种关联字段，以消除笛卡尔积，如果发现所需要的数据没有显示出来，则可以通过左右连接改变连接的方向；
3、分组统计：分组统计要使用统计函数，统计函数要么单独使用，要么在分组中使用，而且在分组之后的SELECT子句之中只能出现分组字段和统计函数，而且在分组之前的过滤使用WHERE子句，而分组之后的过滤使用的是HAVING子句；
4、子查询：多表查询由于会有笛卡尔积的问题，所以在开发之中可以使用子查询提升查询性能，子查询可以出现在查询语句的任意位置，但是在WHERE和FROM之后出现比较常见；
5、数据库的更新操作：
- 增加：INSERT INTO 表名称(字段1,字段2,… …) VALUES(值1,值2,… …);
- 修改：UPDATE 表名称 SET 字段1=值1,字段2=值2,… …[WHERE 更新条件(s)];
- 删除：DELETE FROM 表名称[WHERE 删除条件(s)];
6、所有的更新操作都受到事务的保护，每一个SESSION（用户）都有各自独立的事务处理能力，主要的操作命令：COMMIT、ROLLBACK；
7、数据表查询的分页显示要使用ROWNUM伪列完成，此种操作在日后的开发中必须会。
2.2、习题讲解
1、列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
- 确定所需要的表
|- dept表：查出部门编号、名称
|- emp表：查出部门编号、雇员数量、统计信息
- 确定已知的关联字段：dept.deptno = emp.deptno
第一步：查出至少有一个员工的部门编号
| SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno) >= 1; |
第二步：加入部门名称
| SELECT d.deptno, d.dname, COUNT(e.empno) FROM emp e, dept d WHERE e.deptno(+) = d.deptno GROUP BY d.deptno, d.dname HAVING COUNT(empno) >= 1; |
第三步：加入统计信息，显示平均工资、最低工资、最高工资
| SELECT d.deptno, d.dname, COUNT(e.empno), AVG(e.sal), MIN(e.sal), MAX(e.sal) FROM emp e, dept d WHERE e.deptno(+) = d.deptno GROUP BY d.deptno, d.dname HAVING COUNT(empno) >= 1; |
2、列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
- 确定所需要的表
|- emp表：列出SMITH和ALLEN的工资
|- emp表（员工）：列出所有员工的编号、姓名
|- emp表（领导）：列出领导姓名
|- dept表：列出部门名称
- 确定已知的关联字段：
|- emp表（员工）与emp表（领导）的关联：emp.mgr = memp.empno
|- emp表与dept表的关联：emp.deptno = dept.deptno
第一步：列出SMITH和ALLEN的工资
| SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN'); |
第二步：列出工资比SMITH或ALLEN多的员工，由以上查询得知返回的结果是多行单列，因此将此子查询写在WHERE后面比较合适
| SELECT empno, ename FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN')); |
第三步：加入部门表，列出部门名称
| SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.sal > ANY (SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN')) AND e.deptno = d.deptno; |
第四步：列出领导姓名，领导表需要emp的自身关联
| SELECT e.empno, e.ename, d.dname, m.ename FROM emp e, dept d, emp m WHERE e.sal > ANY (SELECT sal FROM emp WHERE ename IN('SMITH','ALLEN')) AND e.deptno = d.deptno AND e.mgr = m.empno(+); |
3、列出所有员工的编号、姓名及直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
- 确定所需要的表
|- emp表（员工）：列出所有员工的编号、姓名
|- emp表（领导）：列出领导姓名
- 确定已知的关联字段：emp.mgr = memp.empno
| SELECT e.empno, e.ename, m.empno, m.ename, m.sal * 12 income FROM emp e, emp m WHERE e.mgr = m.empno(+) ORDER BY income DESC; |
4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
- 确定所需要的表
|- emp表（员工）：列出所有员工的编号、姓名、受雇日期
|- emp表（领导）：列出领导的受雇日期
|- dept表（部门）：列出部门名称、部门位置
|- emp表：统计部门人数
- 确定已知的关联字段
|- emp表（员工）与emp表（领导）：emp.mgr = memp.empno
|- emp表（员工）与dept表（部门）：emp.deptno = dept.deptno
|- emp表（员工）与emp表（统计部门人数）：emp.deptno = temp.deptno
第一步：查出员工受雇日期早于领导的记录
| SELECT e.empno, e.ename FROM emp e, emp m WHERE e.mgr = m.empno(+) AND e.hiredate < m.hiredate; |
第二步：加入dept表，查出部门名称、部门位置
| SELECT e.empno, e.ename, d.dname, d.loc FROM emp e, emp m, dept d WHERE e.mgr = m.empno(+) AND e.hiredate < m.hiredate AND e.deptno = d.deptno; |
第三步：加入人数统计，由于以上查询无法统计完整的部门人数，因此使用子查询完成。
| SELECT e.empno, e.ename, d.dname, d.loc, t.count FROM emp e, emp m, dept d, ( SELECT deptno, COUNT(empno) count FROM emp GROUP BY deptno) t WHERE e.mgr = m.empno(+) AND e.hiredate < m.hiredate AND e.deptno = d.deptno AND e.deptno = t.deptno; |
5、列出部门名称和这些部门的员工信息(数量、平均工资)，同时列出那些没有员工的部门
- 确定所需要的表
|- dept表：列出部门名称
|- emp表：统计员工数量、平均工资
- 确定已知的关联字段：dept.deptno = emp.deptno
| SELECT d.deptno, d.dname, COUNT(e.empno), AVG(e.sal) FROM dept d, emp e WHERE d.deptno = e.deptno(+) GROUP BY d.deptno, d.dname; |
6、列出所有“CLERK”(办事员)的姓名及其部门名称，部门人数，工资等级。
- 确定所需要的表
|- emp表：列出所有CLERK的姓名
|- dept表：列出部门名称
|- salgrade表：工资等级
|- emp表：统计部门人数
- 确定已知的关联字段
|- emp表与dept表：emp.deptno = dept.deptno
|- emp表与salgrade表：sal BETWEEN losal AND hisal
|- emp表与emp表（统计人数）：emp.deptno = temp.deptno
第一步：列出所有CLERK的姓名
| SELECT e.ename FROM emp e WHERE e.job = 'CLERK'; |
第二步：加入部门表，列出部门名称
| SELECT e.ename, d.dname FROM emp e, dept d WHERE e.job = 'CLERK' AND e.deptno = d.deptno; |
第三步：加入salgrade表，列出工资等级
| SELECT e.ename, d.dname, s.grade FROM emp e, dept d, salgrade s WHERE e.job = 'CLERK' AND e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal; |
第四步：加入人数统计，由于以上查询无法统计完整的部门人数，因此使用子查询完成。
| SELECT e.ename, d.dname, s.grade, t.count FROM emp e, dept d, salgrade s,( SELECT deptno, COUNT(empno) count FROM emp GROUP BY deptno) t WHERE e.job = 'CLERK' AND e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.deptno = t.deptno; |
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数所在部门名称、位置、平均工资；
- 确定所需要的表
|- emp表：列出所有最低工资大于1500的工作
|- dept表：列出部门名称、位置
|- emp表：求出平均工资
- 确定已知的关联字段：emp.deptno = dept.deptno
第一步：找出最低工资大于1500的工作及从事此工作的雇员人数
| SELECT job, COUNT(empno) FROM emp GROUP BY job HAVING MIN(sal) > 1500; |
第二步：列出雇员所在部门名称、位置；以上的查询只能做子查询，而要列出部门名称和位置，需要再引用一个emp表与dept表及上面的子查询关联
| SELECT e.ename, t.count, d.dname, d.loc FROM emp e, dept d, (SELECT job, COUNT(empno) count FROM emp GROUP BY job HAVING MIN(sal) > 1500) t WHERE e.job = t.job AND e.deptno = d.deptno; |
第三步：以部门分组计算平均工资
| SELECT e.ename, t.count, d.dname, d.loc, a.avg FROM emp e, dept d, (SELECT job, COUNT(empno) count FROM emp GROUP BY job HAVING MIN(sal) > 1500) t, (SELECT deptno, AVG(sal) avg FROM emp GROUP BY deptno) a WHERE e.job = t.job AND e.deptno = d.deptno AND e.deptno = a.deptno; |
8、列出在部门“SALES”(销售部)工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号
- 确定所需要的表
|- emp表：列出员工的姓名、基本工资、雇佣日期
|- dept表：列出部门名称
- 确定已知的关联字段：emp.deptno = dept.deptno
| SELECT e.ename, e.sal, e.hiredate, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES'; |
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
- 确定所需要的表
|- emp表：求出平均工资
|- emp表（员工）：列出员工信息
|- emp表（领导）：列出领导信息
|- dept表：列出部门信息
|- salgrade表：工资等级
- 确定已知的关联字段
|- 员工和部门：emp.deptno = dept.deptno
|- 员工和领导：emp.mgr = memp.empno
|- 员工和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal
第一步：求出平均工资
| SELECT AVG(sal) FROM emp; |
第二步：列出薪金高于公司平均薪金的所有员工，因上面的查询返回结果为单行单列，可以将以上SQL放在WHERE后面的子查询中使用
| SELECT e.empno, e.ename, e.job, e.sal FROM emp e WHERE e.sal > (SELECT AVG(sal) FROM emp); |
第三步：加入部门表，列出部门信息
| SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc FROM emp e, dept d WHERE e.sal > (SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno; |
第四步：列出上级领导
| SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc, m.ename FROM emp e, dept d, emp m WHERE e.sal > (SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno AND e.mgr = m.empno(+); |
第五步：加入工资等级表，列出工资等级
| SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc, m.ename, g.grade FROM emp e, dept d, emp m, salgrade g WHERE e.sal > (SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno AND e.mgr = m.empno(+) AND e.sal BETWEEN g.losal AND g.hisal; |
10、列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
- 确定所需要的表
|- emp表：列出SCOTT从事的工作
|- emp表：列出与SCOTT从事相同工作的员工
|- dept表：列出部门名称
|- emp表：按部门统计人数
- 确定已知的关联字段
|- 员工与部门：emp.deptno = dept.deptno
|- 员部与统计人数：emp.deptno = temp.deptno
第一步：列出SCOTT从事的工作
| SELECT job FROM emp WHERE ename = 'SCOTT'; |
第二步：列出与SCOTT从事相同工作的员工，因以上查询返回单行单列，所以此SQL可以作为WHERE的子查询
| SELECT e.ename, e.ename, e.job FROM emp e WHERE e.job = (SELECT job FROM emp WHERE ename = 'SCOTT'); |
第三步：加入dept表，列出部门名称
| SELECT e.ename, e.ename, e.job, d.dname FROM emp e, dept d WHERE e.job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND e.deptno = d.deptno; |
第四部：加入按部门统计员工人数信息
| SELECT e.ename, e.ename, e.job, d.dname, t.count FROM emp e, dept d, ( SELECT deptno, COUNT(empno) count FROM emp GROUP BY deptno) t WHERE e.job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND e.deptno = d.deptno AND e.deptno = t.deptno; |
11、列出公司各个工资等级雇员的数量、平均工资
- 确定所需要的表
|- emp表：计算工资信息
|- salgrade表：列出工资等级
- 确定已知的关联字段：emp.sal BETWEEN salgrade.losal AND hisal
| SELECT g.grade, g.losal, g.hisal, COUNT(e.empno), AVG(e.sal) FROM emp e, salgrade g WHERE e.sal BETWEEN g.losal AND g.hisal GROUP BY g.grade, g.losal, g.hisal; |
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
- 确定所需要的表
|- emp表：列出30部门所有员工的薪金
|- emp表：列出高于30部门所有员工薪金的员工
|- dept表：列出部门名称
- 确定已知的关联字段：emp.deptno = dept.deptno
第一步：列出30部门所有员工的薪金
| SELECT sal FROM emp WHERE deptno=30; |
第二步：列出高于30部门所有员工薪金的员工的姓名、薪金，以上查义为多行单列，可以作为WHERE子句的子查询使用，因条件是高于30部门所有员工的薪金，所以要使用> ALL运算符
| SELECT e.ename, e.sal FROM emp e WHERE e.sal > ALL(SELECT sal FROM emp WHERE deptno=30); |
第三步：加入部门表，列出部门名称
| SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.sal > ALL(SELECT sal FROM emp WHERE deptno=30) AND e.deptno = d.deptno; |
13、列出每个部门的员工数量、平均工资和平均服务期限
- 确定所需要的表
|- dept表：列出部门信息
|- emp表：统计员工数量、平均工资、平均服务期限
- 确定已知的关联字段：dept.deptno = emp.deptno
按多字段分组统计即可，服务期限需要计算，此处按年计算
| SELECT d.deptno, d.dname, d.loc, COUNT(e.empno), AVG(e.sal), AVG(MONTHS_BETWEEN(SYSDATE, e.hiredate)/12) FROM dept d, emp e WHERE d.deptno = e.deptno(+) GROUP BY d.deptno, d.dname, d.loc; |
14、列出所有员工的姓名、部门名称和工资
- 确定所需要的表
|- emp表：列出所有员工姓名、工资
|- dept表：列出部门名称
- 确定已知的关联字段：emp.deptno = dept.deptno
| SELECT e.ename, d.dname, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno; |
15、列出所有部门的详细信息和部门人数
- 确定所需要的表
|- dept表：列出部门详细信息
|- emp表：统计部门人数
- 确定已知的关联字段：dept.deptno = emp.deptno
| SELECT d.deptno, d.dname, d.loc, COUNT(e.empno) FROM dept d, emp e WHERE d.deptno = e.deptno(+) GROUP BY d.deptno, d.dname, d.loc; |
16、列出各种工作的最低工资及从事此工资的雇员姓名
- 确定所需要的表
|- emp表：列出各种工作的最低工资
|- emp表：列出从事此工资的雇员姓名
- 确定已知的关联字段：emp.job = jemp.job AND emp.sal = jemp.sal
第一步：按工作统计最低工资
| SELECT job, MIN(sal) FROM emp GROUP BY job; |
第二步：列出从事此工资的雇员姓名
| SELECT e.ename, e.job, e.sal FROM emp e, ( SELECT job, MIN(sal) sal FROM emp GROUP BY job) j WHERE e.job = j.job AND e.sal = j.sal; |
17、列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数
- 确定所需要的表
|- emp表：列出各个部门最低薪金的MANAGER
|- emp表：列出姓名
|- dept表：列出部门名称
|- emp表：统计人数
- 确定已知的关联字段
|- 最低薪金的MANAGER与emp姓名：emp.deptno = jemp.deptno AND emp.sal = jemp.sal
|- emp与dept：emp.deptno = dept.deptno
第一步：查出各个部门最低薪金的MANAGER
| SELECT deptno, MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno; |
第二步：查出最低薪金的MANAGER的姓名
| SELECT e.ename, e.sal FROM emp e, ( SELECT deptno, MIN(sal) sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno) j WHERE e.deptno = j.deptno AND e.sal = j.sal; |
第三步：加入dept表，列出部门名称
| SELECT e.ename, e.sal, d.dname FROM emp e, ( SELECT deptno, MIN(sal) sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno) j, dept d WHERE e.deptno = j.deptno AND e.sal = j.sal AND e.deptno = d.deptno; |
第四步：加入统计人数信息
| SELECT e.ename, e.sal, d.dname, t.count FROM emp e, ( SELECT deptno, MIN(sal) sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno) j, dept d, ( SELECT deptno, COUNT(empno) count FROM emp GROUP BY deptno) t WHERE e.deptno = j.deptno AND e.sal = j.sal AND e.deptno = d.deptno AND e.deptno = t.deptno; |
18、列出所有员工的年工资，所在部门名称，按年薪从低到高排序
- 确定所需要的表
|- emp表：列出员工信息、年工资
|- dept表：列出部门名称
- 确定已知的关联字段：emp.deptno = dept.deptno
| SELECT e.empno, e.ename, e.sal*12 income, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY income; |
19、查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
- 确定所需要的表
|- emp表（员工）：查询员工信息
|- emp表（领导）：查询领导信息
|- dept表：查询部门信息
- 确定已知的关联字段
|- emp员工与emp领导：emp.mgr = memp.empno
|- emp与dept：emp.deptno = dept.deptno
| SELECT DISTINCT m.ename, d.dname FROM emp e, emp m, dept d WHERE e.mgr = m.empno AND m.deptno = d.deptno AND m.sal > 3000; |
20、求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数
- 确定所需要的表
|- dept表：查出部门名称中带‘S’的部门
|- emp表：统计部门员工的工资合计、部门人数
- 确定已知的关联字段：dept.deptno = emp.deptno
| SELECT d.deptno, d.dname, d.loc, SUM(e.sal), COUNT(e.ename) FROM dept d, emp e WHERE d.dname LIKE '%S%' AND d.deptno = e.deptno(+) GROUP BY d.deptno, d.dname, d.loc; |
21、给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，以此类推。
| UPDATE emp SET sal = DECODE(deptno, 10, sal*1.1, 20, sal*1.2, 30, sal*1.3, sal) WHERE MONTHS_BETWEEN(SYSDATE, hiredate)/12 > 34 OR TO_CHAR(hiredate, 'yyyy') = '1987'; |
2.3本次预讲解的知识点
1、数据表的创建及管理
2、表约束的创建及管理
3、利用DDL、DML完成一套完整的操作案例
4、序列的使用
5、数据的集合操作
6、PL SQL Developer工具的使用
3、具体内容
3.1 表的创建及管理（重点）
对于数据库而言实际上每一张表都表示的是一个数据库的对象，而在数据库对象指的就是DDL定义的所有操作，例如：表、视图、索引、序列、约束等等，都属于对象的操作，所以表的建立就是对象的建立，而对象的操作分为以下三类语法：
- 创建对象：CREATE 对象名称 …
- 删除对象：DROP 对象名称 …
- 修改对象：ALTER 对象名称 …
3.1.1常用的数据字段
每一张数据表都是由若干个字段所组成，而每一个字段都会有其对应的数据类型，而在Oracle之中，常用的有如下几种：
| NO | 数据类型 | 关键字 | 描述 |
| 1 | 字符串 | VARCHAR2(n) | 其中n表示的是字符串所能保存的最大长度，基本上保存200个左右内容 |
| 2 | 整数 | NUMBER(n) | 表示最多为n位的整数，有时候也可以使用INT代替 |
| 3 | 小数 | NUMBER(n,m) | 其中m为小数位，n-m为整数位，有时候也可以使用FLOAT代替 |
| 4 | 日期 | DATE | 存放日期时间 |
| 5 | 大文本 | CLOB | 可以存储海量文字(4G) |
| 6 | 大对象 | BLOB | 存放二进制数据，例如：电影、MP3、图片、文字 |
一般在开发之中使用最多的：VARCHAR2()、NUMBER、DATE、CLOB，而对于BLOB字段一般使用较少，首先BLOB可以存放4G的二进制数据，但是存放进去之后，一是数据库过于庞大，二是读取不方便。
3.1.2 表的创建
如果现在要想进行表的创建，可以使用如下的操作语法：
| CREATE TABLE 表名称 ( 字段1 数据类型 [DEFAULT 默认值], 字段2 数据类型 [DEFAULT 默认值], ...... 字段n 数据类型 [DEFAULT 默认值] ); |
下面创建一张成员表（member），有如下保存信息：姓名、年龄、生日、个人简介
| CREATE TABLE member ( name VARCHAR2(50) DEFAULT '无名氏', age NUMBER(3), birthday DATE DEFAULT SYSDATE, content CLOB ); |
表创建成功之后，向表中增加数据：
| INSERT INTO member(name,age,birthday,content) VALUES('张三',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'一个好人'); |
| INSERT INTO member(age,content) VALUES(20,'一个好人'); |
一定要记住：表的创建是属于数据库对象的创建，所以使用CREATE语法。
3.1.3表的复制
在之前学习过表的复制的操作，下面给出完整的操作语法：
| CREATE TABLE 复制表名称 AS子查询; |
范例：复制一张只包含20部门雇员信息的表
| CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; |
范例：现在要求将emp表的表结构复制出来，不要数据 —— 写一个永远满足不了的条件即可
| CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2; |
但是以上语法只是Oracle数据库所支持的操作，其它的数据库语法上会有一些区别。
3.1.4为表重命名（了解）
在Oracle数据库之中，所有的数据都是通过数据字典保存的，例如：在之前使用过如下的一种查询：
| SELECT * FROM tab; |
以上就是一个数据字典，而在Oracle之中，提供了三种数据字典，最常用的是：dba_、user_，下面查询一个user_tables数据字典
| SELECT * FROM user_tables; |
也就是说Oracle中的所有数据都是按照文件保存的，那么所有的内容都会在数据字典中注册，既然这样，所谓的修改表名称实际上对于Oracle而言就相当于修改一条数据，而修改表名称的方法如下：
| RENAME 旧的表名称 TO 新的表名称; |
范例：将member表更名为person
| RENAME member TO person; |
但是这种操作由于是Oracle数据库所独有的一种特性，所以了解即可，不用做深入的掌握。
3.1.5截断表（了解）
在之前曾经讲解过一个删除表数据的操作，使用的是DELETE操作，但是这种删除操作本身有一个特点，即：可以进行事物的回滚，也就是说删除之后并不会立刻释放数据的资源。如果现在希望彻底释放掉一张表所占用的全部资源（表空间、索引等等）就可以使用截断表的语法，此语法如下：
| TRUNCATE TABLE 表名称; |
范例：截断person表
| TRUNCATE TABLE person; |
但是这种语法本身只是Oracle所有的，所以只做了解即可。
3.1.6表的删除
表的删除操作指的是数据库对象的删除，既然是删除则使用DROP语句，删除表的语法如下：
| DROP TABLE 表名称; |
范例：删除person表
| DROP TABLE person; |
3.1.7 Oracle 10g的新特性闪回技术（理解）
在Oracle 10g之后，为了预防用户的误删除表操作，专门提供了回收站的功能，用户所删除的表默认情况下会在一个回收站之中保存，而用户也可以通过回收站进行表的恢复，此技术被称为闪回（FLASHBACK）.
范例：查看回收站
| SHOW RECYCLEBIN; |
此时发现所有已删除的表都在回收站中保存，下面就可以使用如下的语法恢复表：
| FLASHBACK TABLE 表名称 TO BEFORE DROP; |
范例：恢复myemp表
| FLASHBACK TABLE myemp TO BEFORE DROP; |
当然，现在也可以直接删除掉回收站中的一些数据表，语法如下：
| PURGE TABLE 表名称; |
范例：删除回收站中的person表
| PURGE TABLE person; |
范例：清空回收站
| PURGE RECYCLEBIN; |
如果现在希望删除一张表，而又不希望其进入到回收站之中，则可以在删除时增加PURGE
| DROP TABLE myemp PURGE; |
这种技术是在Oracle 10g之后才有的，而Oracle 11g也是存在的。
问题：现在回收站中存在了一张tab表，而后又建立了一张tab表，那么如果从回收站中恢复tab，可以吗？
无法恢复，但是一般人也不会出此问题。
3.1.8修改表结构（了解）
如果一张建立好的数据表，发现其初期的结构已经不满足后期的使用要求，则可以进行表的修改操作，而表的修改操作实际上就是数据库对象的修改操作，使用ALTER指令完成，例如：现在有如下一张表：
| CREATE TABLE member( mid NUMBER, Name VARCHAR2(50)); |
现在希望可以向表中增加字段，可以使用如下的语法完成：
| ALTER TABLE 表名称ADD(列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值], …… ); |
范例：为member表增加字段
| ALTER TABLE member ADD (age NUMBER(3), birthday DATE DEFAULT SYSDATE); |
如果增加的数据列没有默认值，则所有已有的数据的列的内容都是null，而如果增加的列指定了DEFAULT默认值，则所有已有的数据列都是设置的默认值。
现在也可以修改已有的表结构，此时的语法如下：
| ALTER TABLE 表名称MODIFY(列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值], ……); |
范例：将name字段的默认值定义为“无名氏”
| ALTER TABLE member MODIFY(name VARCHAR2(100) DEFAULT '无名氏'); |
虽然在SQL语法之中以及Oracle数据库之中，都给出了修改表结构的操作，但是这种操作能不用就不用，从大型数据库来讲，世界上性能最高的数据库是IBM DB2，但是IBM DB2本身有一个平台限制问题，所以如果说是跨平台的数据库则是Oracle数据库的性能最高。
在IBM DB2数据库之中是不允许修改表结构的，即：表建立完成之后就不能修改了，所以以后在开发之中也尽可能的不要去修改表结构。
额外补充：关于软件项目的开发基本流程
- 获取需求 à 软件销售
- 需求分析 à 根据客户方的提示，作出需求分析
- 业务分析，数据库设计及接口设计 à 数据表设计
- 编码实现 à 业务梳理
- 软件测试 à 理论上应该有专门的测试人员
- 软件培训及项目维护
数据表的建立前提 = 软件的业务分析
3.1.9思考题（面试题）
现在要求建立一张nation表，表中有一个name字段，里面保存四条记录：中国、美国、巴西、荷兰，要求通过查询实现如下的操作效果：
- 中国 美国
- 中国 巴西
- 中国 荷兰
- 美国 巴西
- 美国 中国
- 美国 荷兰
剩下的依次类推，现在要求建立新的表并完成此查询的操作。
本题目的主要目的不是在于查询的编写上，而是在于规范化数据库创建脚本的格式，以后只要是碰到了类似的要求，首先必须要编写一个数据库创建脚本，这个脚本的要求如下：
- 本文件的文件名称后缀必须是“*.sql”
- 先删除相应的数据表
- 编写创建表的语句
- 增加测试数据
- 提交事务
| --1、删除表 DROP TABLE nation PURGE; --2、创建表 CREATE TABLE nation ( name VARCHAR2(50) ); --3、测试数据 INSERT INTO nation(name) VALUES('中国'); INSERT INTO nation(name) VALUES('美国'); INSERT INTO nation(name) VALUES('巴西'); INSERT INTO nation(name) VALUES('荷兰'); --4、事务提交 COMMIT; |
本程序如果要想完成依靠笛卡尔积完成，属于表的自身关联
| SELECT a.name, b.name FROM nation a, nation b WHERE a.name <> b.name; |
而且以后如果面试之中，出现了一些复杂的查询，建议把脚本写全了。
3.2约束（重点）
表虽然建立完成了，但是表中的数据是否合法并不能有所检查，而如果要想针对表中的数据做一些过滤，则可以通过约束完成，约束的主要功能是保证表中的数据合法性，按照约束的分类，一共有五种约束：非空约束、唯一约束、主键约束、检查约束、外键约束。
3.2.1非空约束（NOT NULL）：NK
当数据表中的某个字段上的内容不希望设置为null，则可以使用NOT NULL进行指定。
范例：定义一张数据表
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL ); |
因为此时存在了“NOT NULL”约束，所以下面插入两组数据
范例：正确的数据
| INSERT INTO member(mid, name) VALUES(1, '张三'); INSERT INTO member(mid, name) VALUES(null, '李四'); INSERT INTO member(name) VALUES('王五'); |
范例：插入错误的数据
| INSERT INTO member(mid, name) VALUES(9, NULL); INSERT INTO member(mid) VALUES(10); |
此时出现错误提示
| ORA-01400：无法将null插入("scott"."member"."NAME") |
本程序之中，直接表示出了“用户”.“表名称”.“字段”出现了错误。
3.2.2唯一约束（UNIQUE）：UK
唯一约束指的是每一个列上的数据是不允许重复的，例如：email地址每个用户肯定是不重复的，那么就使用唯一约束完成。
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, email VARCHAR2(50) UNIQUE ); |
范例：插入正确的数据
| INSERT INTO member(mid, name, email) VALUES(1, '张三', 'zhangsan@163.com'); INSERT INTO member(mid, name, email) VALUES(2, '李四', null); |
范例：插入错误的数据
| INSERT INTO member(mid, name, email) VALUES(3, '王五', 'zhangsan@163.com'); |
此时会出现如下的错误提示：
| ORA-00001：违反唯一约束条件(SCOTT,SYS_COO5277) |
可是这时候的错误提示与之前的非空约束相比并不完善，因为现在只是给出了一个代号而已，这是因为在定义约束的时候没有为约束指定一个名字，所以由系统默认分配了，而且约束的名字建议的格式“约束类型_字段”，例如：“UK_email”。指定约束名称使用CONSTRAINT完成。
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, email VARCHAR2(50), CONSTRAINT UK_email UNIQUE(email) ); |
以后再次增加错误数据时，提示信息如下：
| ORA-00001：违反唯一约束条件（SCOTT,UK_EMAIL）; |
已经可以很明确的提示用户错误的位置
3.2.3主键约束（Primary Key）：PK
主键约束 = 非空约束 + 唯一约束，在之前设置唯一约束的时候发现可以设置为null，而如果使用了主键约束之后则不能为null，而且主键约束一般作为数据的唯一的一个标记出现，例如人员的ID。
范例：建立主键约束
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL ); |
范例：增加正确的数据
| INSERT INTO member(mid, name) VALUES(1, '张三'); |
范例：错误的数据 —— 主键设置为null
| INSERT INTO member(mid, name) VALUES(null, '张三'); |
错误信息，与之前的非空约束的错误信息提示是一样的
| ORA-01400：无法将null插入("SCOTT"."MEMBER"."MID") |
范例：错误的数据 —— 主键重复
| INSERT INTO member(mid, name) VALUES(1, '张三'); |
错误信息，这个错误信息就是唯一约束的错误信息，但是信息不明确，因为没起名字。
| ORA-00001:违反唯一约束条件(SCOTT,SYS_COO5286) |
为了约束使用方便，为主键约束起一个名字
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, CONSTRAINT PK_mid PRIMARY KEY (mid) ); |
此时重复插入数据，则错误信息如下：
| ORA_00001：违反唯一约束条件(SCOTT,PK_MID) |
从正常的开发角度而言，一张表一般只设置一个主键，但是从SQL语法的规定而言，一张表可以设置多个主键，而此种做法称为复合主键，例如：参考如下代码：
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50), CONSTRAINT PK_mid_name PRIMARY KEY (mid, name) ); |
在复合主键的使用之中，只有两个字段的内容都一样的情况下，才被称为重复数据。
范例：插入正确的数据
| INSERT INTO member(mid, name) VALUES(1, '张三'); INSERT INTO member(mid, name) VALUES(1, '李四'); INSERT INTO member(mid, name) VALUES(2, '张三'); |
范例：插入错误的数据
| INSERT INTO member(mid, name) VALUES(1, '张三'); |
错误信息：
| ORA-00001:违反唯一约束条件(SCOTT.PK_MID_NAME) |
但是从开发的实际角度而言，正常人都不使用复合主键，所以这个知识只是作为其相关内容做了一个介绍，记住了，只要是数据表，永远都只设置一个主键。
3.2.4检查约束（Check）：CK
检查约束指的是为表中的数据增加一些过滤条件，例如：
- 设置年龄的时候范围是：0 – 250
- 设置性别的时候应该是：男、女、中
范例：设置检查约束
| DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, sex VARCHAR2(10) NOT NULL, age NUMBER(3), CONSTRAINT PK_mid_name PRIMARY KEY (mid, name), CONSTRAINT CK_sex CHECK (sex IN ('男', '女', '中')), CONSTRAINT CK_age CHECK (age BETWEEN 0 AND 250) ); |
范例：增加正确的数据
| INSERT INTO member(mid, name, sex, age) VALUES(1, '刘三', '中', 200); |
范例：增加错误的性别 -- ORA-02290:违反检查约束条件(SCOTT.CK_SEX)
| INSERT INTO member(mid, name, sex, age) VALUES(2, '刘四', '人妖', 200); |
范例：增加错误的年龄 -- ORA-02290:违反检查约束条件(SCOTT.CK_AGE)
| INSERT INTO member(mid, name, sex, age) VALUES(1, '刘四', '女', 500); |
检查的操作就是对输入的数据进行过滤。
3.2.5主-外键约束（核心难点）
之前的四种约束都是在单张表中进行的，而主-外键约束是在两张表中进行的，这两张表是存在父子关系的，即：子表中的某个字段的取值范围由父表所决定。
例如，要求表示出一种关系，每一个人有多本书，应该定义两张数据表：member（主）、book（子）。
| DROP TABLE member PURGE; DROP TABLE book PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, CONSTRAINT PK_mid PRIMARY KEY (mid) ); CREATE TABLE book ( bid NUMBER, title VARCHAR2(50) NOT NULL, mid NUMBER, CONSTRAINT PK_bid PRIMARY KEY (bid) ); |
此时只是根据要求建立了两张独立的数据表，下面插入几条数据：
| INSERT INTO member(mid, name) VALUES(1, '张三'); INSERT INTO member(mid, name) VALUES(2, '李四'); INSERT INTO book(bid, title, mid) VALUES(101, 'Java 开发', 1); INSERT INTO book(bid, title, mid) VALUES(102, 'Java Web 开发', 2); INSERT INTO book(bid, title, mid) VALUES(103, 'EJB 开发', 1); INSERT INTO book(bid, title, mid) VALUES(105, 'Android 开发', 1); INSERT INTO book(bid, title, mid) VALUES(107, 'AJAX 开发', 1); |
要想验证这个数据是否有意义，最简单的做法，就是写两个查询。
范例：统计每个人员拥有书的数量
| SELECT m.mid, m.name, COUNT(b.bid) FROM member m, book b WHERE m.mid = b.mid GROUP BY m.mid, m.name; |
范例：查询出每个人的编号，姓名，拥有书的名称
| SELECT m.mid, m.name, b.title FROM member m, book b WHERE m.mid = b.mid; |
即：现在的book.mid字段应该是与member.mid字段相关联的，但是由于本程序没有设置约束，所以现在以下的数据也是可以增加的。
| INSERT INTO book(bid, title, mid) VALUES(108, 'PhotoShop使用手册', 3); INSERT INTO book(bid, title, mid) VALUES(109, 'FLEX开发手册', 8); |
现在增加了两条新的记录，而且记录可以保存在数据表之中，但是这两条记录没有意义，因为member.mid字段的内容没有3和8。而要想解决这个问题就必须依靠外键约束解决。
让book.mid字段的取值由member.mid决定，如果member.mid的数据真实存在，则表示可以更新。
| DROP TABLE member PURGE; DROP TABLE book PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, CONSTRAINT PK_mid PRIMARY KEY (mid) ); CREATE TABLE book ( bid NUMBER, title VARCHAR2(50) NOT NULL, mid NUMBER, CONSTRAINT PK_bid PRIMARY KEY (bid), CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES member(mid) ); |
此时只是增加了一个约束，这样一来如果输入的数据有错误，则会出现如下的提示：
| ORA-02291:违反完整约束条件(SCOTT.FK_MID) - 未找到父项关键字 |
因为member.mid没有指定的数据，所以book.mid如果有错误，则无法进行更新操作。
使用外键最大的好处是控制子表中某些数据的取值范围，但是同样带来了不少的问题。
- 删除数据的时候，如果主表中的数据有对应的子表数据，则无法删除
范例：删除member表中mid为1的数据
| DELETE FROM member WHERE mid = 1; |
错误提示信息：ORA-02292：违反完整约束条件（SCOTT.FK_MID）- 已找到子记录。
此时只能先删除子表记录，之后再删除父表记录：
| DELETE FROM book WHERE mid = 1; DELETE FROM member WHERE mid = 1; |
这种操作明显不方便，如果说现在希望主表数据删除之后，子表中对应的数据也可以删除，则可以在建立外键约束的时候指定一个级联删除的功能，修改数据库的创建脚本。
| DROP TABLE book PURGE; DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, CONSTRAINT PK_mid PRIMARY KEY (mid) ); CREATE TABLE book ( bid NUMBER, title VARCHAR2(50) NOT NULL, mid NUMBER, CONSTRAINT PK_bid PRIMARY KEY (bid), CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE ); |
此时由于存在了级联删除的操作，所以主表中的数据删除之后，对应的子表中的数据也都会被同时删除掉。
- 删除数据的时候，让子表中对应的数据设置为null
当主表中的数据删除之后，对应的子表中的数据相关项也希望将其设置为null，而不是删除，此时可以继续修改数据表的创建脚本。
| DROP TABLE book PURGE; DROP TABLE member PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, CONSTRAINT PK_mid PRIMARY KEY (mid) ); CREATE TABLE book ( bid NUMBER, title VARCHAR2(50) NOT NULL, mid NUMBER, CONSTRAINT PK_bid PRIMARY KEY (bid), CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ); |
3、删除父表之前必须首先删除对应的子表，否则无法删除
| DROP TABLE book PURGE; DROP TABLE member PURGE; |
但是这样做明显很麻烦，因为对于一个未知的数据库，如果要按照此类方式进行，则必须首先知道其父子关系，所以在Oracle之中专门提供一个强制性删除表的操作，即：不再关心约束，在删除的时候写上一句“CASCADE CONSTRAINT”
| DROP TABLE member CASCADE CONSTRAINT PURGE; DROP TABLE book CASCADE CONSTRAINT PURGE; |
此时，不关心子表是否存在，直接强制性的删除主表。
合理做法：在以后进行数据表删除的时候，最好是先删除子表，之后再删除父表。
3.2.6修改约束（了解）
约束本身也属于数据库对象，那么也可以进行修改操作，而且只要是修改都使用ALTER指令，约束的修改主要指的是以下两种操作：
- 为表增加约束
| ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段); |
- 删除表中的约束
| ALTER TABLE 表名称 DROP CONSTRAINT 约束名称; |
可以发现，如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束作为一个特殊的约束无法操作。现在有如下一张数据表：
| DROP TABLE member CASCADE CONSTRAINT PURGE; CREATE TABLE member ( mid NUMBER, name VARCHAR2(50) NOT NULL, age NUMBER(3) ); |
范例：为表中增加主键约束
| ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid); |
增加数据：
| INSERT INTO member(mid,name,age) VALUES(1,'张三',30); INSERT INTO member(mid,name,age) VALUES(2,'李四',300); |
现在member表中已经存在了年龄上的非法数据，所以下面为member增加检查约束
| ALTER TABLE member ADD CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 250); |
这个时候在表中已经存在了违反约束的数据，所以肯定无法增加。
范例：删除member表中的mid上的主键约束
| ALTER TABLE member DROP CONSTRAINT pk_mid; |
可是，跟表结构一样，约束最好也不要修改，而且记住，表建立的同时一定要将约束定义好，以后的使用之中建议就不要去改变了。
3.2.7查询约束（了解）
在Oracle中所有的对象都会在数据字典中保存，而约束也是一样的，所以如果要想知道有哪些约束，可以直接查询“user_constraints”数据字典
| SELECT owner, constraint_name, table_name FROM user_constraints; |
但是这个查询出来的约束只是告诉了名字，而并没有告诉在哪个字段上有此约束，此时可以查看另外一张数据字典表“user_cons_columns”
| COL owner FOR A15; COL constraint_name FOR A15; COL table_name FOR A15; COL column_name FOR A15; SELECT owner, constraint_name, table_name, column_name FROM user_cons_columns; |
但是这些维护的工作大部分应该由DBA负责，而不是作为程序开发人员。
3.3建表、更新、查询综合练习
有某个学生运动会比赛信息的数据库，保存如下的表：
- 运动员sporter(运动员编号sporterid，运动员姓名name,运动员性别sex,所属系名department)
- 项目item(项目编号itemid,项目名称itemname,项目比赛地点location)
- 成绩grade(运动员编号sporterid,项目编号itemid，积分mark)
请用SQL语句完成如下功能：
1、建表，并在相应字段上增加约束；
|-定义各个表的主键和外键约束；
|-运动员的姓名和所属系别不能为空；
|-积分要么为空值，要么为6、4、2、0，分别代表第一、二、三名和其他名次的积分，注意名次可以有并列名次，后面的排名不往前提升，例如，如果有两个并列第一，则没有第二名。
2、向表中插入指定的数据：
| 运动员( 1001,李明,男,计算机系 1002,张三,男,数学系 1003,李四,男,计算机系 1004,李娜,女,物理系 1005,王二,男,心里系 1006,孙俪,女,数学系 ) 项目( X001,男子5千米,一操场 X002,男子标枪,一操场 X003,男子跳远,二操场 X004,女子跳高,二操场 X005,女子3千米,三操场 ) 积分( 1001,x001,6 1002,x001,4 1003,x001,2 1004,x001,0 1001,x003,4 1002,x003,6 1004,x003,2 1005,x004,6 1006,x004,4 1003,x002,6 1005,x002,4 1006,x002,2 1001,x002,0 ) |
3、 完成如下的查询要求：
a) 求出目前总积分最高的系名，及其积分
b) 找出在一操场进行比赛的各项目名称及其冠军的姓名
c) 找出参加了张三所参加的所有项目的其他同学的姓名
d) 经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中做出相应修改
e) 经组委会协商，需要删除女子跳高比赛项目
3.3.1建表
编写数据库创建脚本
| DROP TABLE grade PURGE; DROP TABLE sporter PURGE; DROP TABLE item PURGE; CREATE TABLE sporter ( sporterid NUMBER(4), name VARCHAR2(20) NOT NULL, sex VARCHAR2(10), department VARCHAR2(20) NOT NULL, CONSTRAINT pk_sporterid PRIMARY KEY(sporterid), CONSTRAINT ck_sex CHECK (sex IN('男','女')) ); CREATE TABLE item ( itemid VARCHAR2(4), itemname VARCHAR2(20) NOT NULL, location VARCHAR2(20) NOT NULL, CONSTRAINT pk_itemid PRIMARY KEY(itemid) ); CREATE TABLE grade ( sporterid NUMBER(4) NOT NULL, itemid VARCHAR2(4) NOT NULL, mark NUMBER(1), CONSTRAINT fk_sporterid FOREIGN KEY (sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE, CONSTRAINT fk_itemid FOREIGN KEY (itemid) REFERENCES item(itemid) ON DELETE CASCADE, CONSTRAINT ck_mark CHECK (mark IN(0,2,4,6)) ); |
而且在Oracle中要考虑到回收站的问题。
3.3.2增加数据：
1、增加运动员数据
| INSERT INTO sporter(sporterid, name, sex, department) VALUES(1001,'李明','男','计算机系'); INSERT INTO sporter(sporterid, name, sex, department) VALUES(1002,'张三','男','数学系'); INSERT INTO sporter(sporterid, name, sex, department) VALUES(1003,'李四','男','计算机系'); INSERT INTO sporter(sporterid, name, sex, department) VALUES(1004,'李娜','女','物理系'); INSERT INTO sporter(sporterid, name, sex, department) VALUES(1005,'王二','男','心里系'); INSERT INTO sporter(sporterid, name, sex, department) VALUES(1006,'孙俪','女','数学系'); |
2、增加项目数据
| INSERT INTO item(itemid, itemname, location) VALUES('X001','男子5千米','一操场'); INSERT INTO item(itemid, itemname, location) VALUES('X002','男子标枪','一操场'); INSERT INTO item(itemid, itemname, location) VALUES('X003','男子跳远','二操场'); INSERT INTO item(itemid, itemname, location) VALUES('X004','女子跳高','二操场'); INSERT INTO item(itemid, itemname, location) VALUES('X005','女子3千米','三操场'); |
3、增加成绩数据
| INSERT INTO grade(sporterid, itemid, mark) VALUES(1001,'X001',6); INSERT INTO grade(sporterid, itemid, mark) VALUES(1002,'X001',4); INSERT INTO grade(sporterid, itemid, mark) VALUES(1003,'X001',2); INSERT INTO grade(sporterid, itemid, mark) VALUES(1004,'X001',0); INSERT INTO grade(sporterid, itemid, mark) VALUES(1001,'X003',4); INSERT INTO grade(sporterid, itemid, mark) VALUES(1002,'X003',6); INSERT INTO grade(sporterid, itemid, mark) VALUES(1004,'X003',2); INSERT INTO grade(sporterid, itemid, mark) VALUES(1005,'X004',6); INSERT INTO grade(sporterid, itemid, mark) VALUES(1006,'X004',4); INSERT INTO grade(sporterid, itemid, mark) VALUES(1003,'X002',6); INSERT INTO grade(sporterid, itemid, mark) VALUES(1005,'X002',4); INSERT INTO grade(sporterid, itemid, mark) VALUES(1006,'X002',2); INSERT INTO grade(sporterid, itemid, mark) VALUES(1001,'X002',0); |
3.3.3数据操作
1、求出目前总积分最高的系名，及其积分
- 确定需要的表
|- grade表：统计积分
|- sporter表：列出系名
- 确定已知的关联字段：grade.sporterid = sporter.sporterid
第一步：关联两张表并统计积分
| SELECT s.department, SUM(g.mark) FROM sporter s, grade g WHERE s.sporterid = g.sporterid GROUP BY s.department; |
第二步：列出最高的系的积分
两种做法：
- 用ROWNUM，按积分降序排列，然后取第一条记录（此种做法不考虑相同积分的情况）
| SELECT * FROM ( SELECT s.department, SUM(g.mark) sum FROM sporter s, grade g WHERE s.sporterid = g.sporterid GROUP BY s.department ORDER BY sum DESC) WHERE ROWNUM = 1; |
- 用MAX函数，首先要取出最高的积分，然后统计各系的积分与其对比（此种做法如果存在相同积分则同时列出）
| SELECT s.department, SUM(g.mark) FROM sporter s, grade g WHERE s.sporterid = g.sporterid GROUP BY s.department HAVING SUM(g.mark) = ( SELECT MAX(SUM(g.mark)) FROM sporter s, grade g WHERE s.sporterid = g.sporterid GROUP BY s.department); |
2、找出在一操场进行比赛的各项目名称及其冠军的姓名
- 确定需要的表
|- item表：列出场地和项目编号
|- grade表：求出冠军的成绩及编号
|- sporter表：列出冠军的姓名
- 确定已知的关联字段
|- item表和grade表：item.itemid = grade.itemid
|- grade表和sporter表：grade.sporterid = sporter.sporterid
第一步：列出一操场的比赛项目
| SELECT i.itemid, i.itemname FROM item i WHERE i.location = '一操场'; |
第二步：加入grade表，列出冠军的积分
| SELECT i.itemid, i.itemname, MAX(g.mark) max FROM item i, grade g WHERE i.location = '一操场' AND i.itemid = g.itemid GROUP BY i.itemid, i.itemname; |
第三步：列出冠军的姓名
| SELECT s.name, g.itemid, m.itemname, m.max FROM sporter s, grade g, ( SELECT i.itemid, i.itemname, MAX(mark) max FROM item i, grade g WHERE i.location = '一操场' AND i.itemid = g.itemid GROUP BY i.itemid, i.itemname) m WHERE s.sporterid = g.sporterid AND g.itemid = m.itemid AND g.mark = m.max; |
3、找出参加了张三所参加的所有项目的其他同学的姓名
- 确定所需要的表
|- sporter表：列出张三的编号
|- grade表：列出张三参加的项目
|- sporter表：查出与张三参加相同项目的其他同学的姓名
- 确定已知的关联字段：sporter.sporterid = grade.sporterid
第一步：列出张三参加的所有项目
| SELECT g.itemid FROM sporter s, grade g WHERE s.sporterid = g.sporterid AND s.name = '张三'; |
第二步：查出与张三参加相同项目的其他同学的姓名，由于上一个查询是单行多列，所以可以加在WHERE子句之后
| SELECT DISTINCT s.name FROM sporter s, grade g WHERE g.itemid = ANY( SELECT g.itemid FROM sporter s, grade g WHERE s.sporterid = g.sporterid AND s.name = '张三') AND s.sporterid = g.sporterid AND s.name <> '张三'; |
4、经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中做出相应修改
| UPDATE grade SET mark = 0 WHERE sporterid = ( SELECT sporterid FROM sporter WHERE name = '张三'); |
5、经组委会协商，需要删除女子跳高比赛项目
由于创建表时的外键约束加入了级联删除选项，直接删除item表中的项目即可
| DELETE FROM item WHERE itemname = '女子跳高'; |
3.4集合操作（理解）
在数学的操作中存在交、差、并、补的概念，而在数据的查询中也存在此概念，有如下几个连接符号：
- UNION：连接两个查询，相同的部分不显示
- UNINO ALL：连接两个查询，相同的部分不显示
- INTERSECT：返回两个查询中的相同部分
- MINUS：返回两个查询中的不同部分
为了验证以上的操作，下面创建一个只包含20部门雇员信息的表：
| CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno = 20; |
范例：验证UNION
| SELECT * FROM emp UNION SELECT * FROM emp20; |
范例：验证UNION ALL
| SELECT * FROM emp UNION ALL SELECT * FROM emp20; |
范例：验证INTERSECT
| SELECT * FROM emp INTERSECT SELECT * FROM emp20; |
范例：验证MINUS
| SELECT * FROM emp MINUS SELECT * FROM emp20; |
在之前讲解分组的时候曾经留过一道未完成的题目：所有领取奖金的人求出平均工资，所有不领取奖金的人求出平均工资，当时实现的代码如下：
| SELECT comm, AVG(sal) FROM emp GROUP BY comm; |
这种问题下只能依靠查询的连接操作，准备两个查询：
1、第一个查询负责查询出所有领取奖金的雇员的平均工资
2、第二个查询负责查询出所有不领取奖金的雇员的平均工资
| SELECT 'UNCOMM', AVG(sal) FROM emp WHERE comm IS NULL UNION SELECT 'COMM', AVG(sal) FROM emp WHERE comm IS NOT NULL; |
对于这种连接查询，只需要清楚其概念即可，而以后在实际的工作之中，如果遇到太复杂的SQL，也不是程序员的工作，找DBA帮忙。
3.5序列（核心重点）
在许多的数据表之中都存在一种称为自动增长列的操作，但是在oracle之中，这种自动增长列并不是自动控制的，而是需要用户手工的控制，这样做主要是为了开发方便，创建序列的语法如下：
| CREATE SEQUENCE SEQUENCE --创建序列 [INCREMENT BY n] --递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1 [START WITH n] --开始的值,递增默认是minvalue 递减是maxvalue [{MAXVALUE n | NOMAXVALUE}] --最大值 [{MINVALUE n | NOMINVALUE}] --最小值 [{CYCLE | NOCYCLE}] --循环/不循环 [{CACHE n | NOCACHE}]; --分配并存入到内存中 |
范例：创建序列
| CREATE SEQUENCE myseq; |
当一个序列创建完成之后，可以通过以下两种方式访问序列：
1、序列名称.nextval：让序列增长到下一个内容
2、序列名称.currval：取得当前序列的内容
范例：验证序列操作
| SELECT myseq.CURRVAL FROM dual; |
如下错误提示：“ORA-08002：序列MYSEQ.CURRVAL尚未在此会话中定义”
在oracle之中如果要想操作currval，则首先必须先使用nextval；
| SELECT myseq.NEXTVAL FROM dual; SELECT myseq.CURRVAL FROM dual; |
序列一般都作为主键使用，例如：下面定义一张表：
| CREATE TABLE mytab ( ids NUMBER, names VARCHAR2(20) NOT NULL, CONSTRAINT pk_ids PRIMARY KEY (ids) ); |
现在向mytab表中增加数据：
| INSERT INTO mytab(ids, names) VALUES(myseq.NEXTVAL, '姓名'); |
一定要记住，这个过程都是由用户自己手工进行的，不能自动完成。
在默认情况下，序列从0开始，每次增长1，那么现在也可以修改
范例：创建序列，从10开始，每次增长2
| DROP SEQUENCE myseq; CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10; SELECT myseq.NEXTVAL FROM dual; |
范例：希望定义一个序列，这个序列可以在1、3、5、7、9之间循环出现
| CREATE SEQUENCE myseq INCREMENT BY 2 START 1 MAXVALUE 10 MINVALUE 1 CYCLE NOCACHE; |
关于序列中CACHE的解释：
在oracle数据库之中，由于序列被经常使用到，所以oracle为了提升性能，将序列的操作形式做了如下的处理
首先，如果在用户每次使用序列的时候序列再增加，则肯定会造成一些性能上的损耗，所以在oracle中专门为用户准备了一块空间，这个空间之中，为用户准备好了若干个已经生成好的序列，每次操作的时候都是从这块空间之中取出序列的内容，但是这样做有一个问题，如果现在数据库的实例关闭了，那么保存在这块空间中的内容就有可能消失了，但是虽然消失了，可是数据已经增长好了，这样就会出现跳号的事情，而如果要想取消掉这种问题，则最好的方式是将序列设置为不缓存，使用nocache声明。
3.6前台工具：PLSQL Developer（了解）
从oracle开始，个人是从oracle8i使用的，印象之中出现了这么点事：
1、Oracle8i/orache9i：OEM(Oracle企业管理器)、sqlplusw、sqlplus；
2、oracle10g：EM(WEB)、sqlplusw、sqlplus；
3、oracle11g：EM(WEB)、sqlplus、SQLDeveloper（会JAVA）；
但是在这之外oracle中有一个前台工具——PLSQLDeveloper，此工具是一个第三方的软件，也是在开发之中使用最多的前台工具
4、总结
1、表的创建及约束的创建语法必须精通
2、自动增长列使用序列完成
5、预习任务
索引、同义词、用户管理
6、作业
1、复杂查询必须可以灵活编写
2、表的创建及约束的使用必须灵活
1736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
