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、表的创建及约束的使用必须灵活