第三章 SQL语言及oracle数据库操纵(1).

1、 l使用使用SQL语言操纵基本数据库对象语言操纵基本数据库对象l操纵其他重要数据库对象操纵其他重要数据库对象l数据库的启动与关闭数据库的启动与关闭lOracle 数据库完整性约束的实现数据库完整性约束的实现 SQL关键字关键字（以下命令为以下命令为SQL命令，以命令，以“；”结束）结束） Alter、 CommitCreate、Delete Drop、Desc 、 Grant、Insert、Rename Revoke、Rollback、Select 、Truncate 、 Update. 删除表中所有行，并删除表中所有行，并释放分配给该表的空间释放分配给该表的空间 TRUNCATE TABLE

2、 emp; TRUNCATE TABLE emp;1 1 显示表结构显示表结构: : DESC emp;DESC emp;2 2 获取有关函数、过程及包的描述获取有关函数、过程及包的描述（Truncate 与与delete区别见区别见P48）创建基本表创建基本表操纵表中数据（数据的增、删、改操作）操纵表中数据（数据的增、删、改操作）数据查询数据查询 例例 创建学生表创建学生表CREATE TABLE student(sno CHAR(5) PRIMARY KEY,sname CHAR(20) UNIQUE,ssex CHAR(2) CHECK(ssex IN(男男, 女女) ,sage NUM

3、BER(2,0) CHECK(sage between 16 and 20),sdept CHAR(15);OEM中查看所创建表的约束中查看所创建表的约束 创建新表，结构与创建新表，结构与studentstudent相同，并拷贝数据。相同，并拷贝数据。 CREATE TABLE student2 AS SELECT * FROM student WHERE 1=2;CREATE TABLE student1 AS SELECT * FROM student;创建新表，结构与创建新表，结构与STUDENTSTUDENT相同，不拷贝数据。相同，不拷贝数据。？表中数据操纵表中数据操纵向表中插入数据（

4、向表中插入数据（INSERTINSERT）对表中数据进行修改（对表中数据进行修改（UPDATEUPDATE）删除表中数据（删除表中数据（DELETEDELETE）l插入单行插入单行 INSERT INTO (,) VALUES (,); Note：在表定义时说明了在表定义时说明了NOT NULL的属性列不能取空值，否则的属性列不能取空值，否则会出错。会出错。如果如果INTO子句中没有指明任何列名，则新插入的记录必须子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。在每个属性列上均有值。字符型和日期型数据在插入时要加单引号字符型和日期型数据在插入时要加单引号。SQL INSERT

5、INTO DEPT VALUES (60,PRODUCTION,SAN FRANCISCO);SQL INSERT INTO DEPT(DNAME,DEPTNO) VALUES(TEST,70);l插入子查询结果插入子查询结果 INSERT INTO (,)subquery； 例例向表中插入一条与向表中插入一条与SMITH的内容相同的记录，但姓的内容相同的记录，但姓名改为了名改为了FAN,雇员号改为了雇员号改为了8000. INSERT INTO EMP (EMPNO, ENAME, JOB,MGR ,HIREDATE, SAL, COMM, DEPTNO) SELECT 8000, FAN,

6、JOB,MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE ENAME = SMITH;l修改某一个元组的值修改某一个元组的值UPDATE SET =,column1=WHERE conditions; 例例将雇员号为将雇员号为8000的雇员提升为部门的雇员提升为部门20的经理，工资增的经理，工资增加加1000。 SQL UPDATE EMP SET JOB = MANAGER, SAL = SAL+1000, DEPTNO = 20 WHERE EMPNO = 8000;l修改多个元组的值修改多个元组的值 例例将部门号为将部门号为20的所有的雇员的

7、工资加的所有的雇员的工资加10%. SQL UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;l带子查询的修改语句带子查询的修改语句 例例将将20号部门的所有雇员的工资都变为该部门平均工资号部门的所有雇员的工资都变为该部门平均工资加加1000. UPDATE EMP SET SAL = 1000 + (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20) WHERE DEPTNO = 20;l删除某一个元组的值删除某一个元组的值DELETEFROMWHERE condition;例例 删除名字为删除名字为

8、FAN的雇员的雇员.SQL DELETE FROM EMP WHERE ENAME = FAN;l删除多个元组的值删除多个元组的值例例 删除删除EMP表中的所有数据表中的所有数据.SQL DELETE FROM EMP ；l带子查询的删除语句带子查询的删除语句例例 删除所有与雇员删除所有与雇员FAN相同部门的雇员信息。相同部门的雇员信息。SQL DELETE FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = FAN); 查询语句的完整格式如下：查询语句的完整格式如下：SELECTALL|DISTINCTSELECTALL

9、|DISTINCT ，column2column2FROM table1FROM or view1， table2 WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC|DESCASC|DESC；简单查询（略）简单查询（略）复杂查询复杂查询 聚合函数是从一组中返回汇总信息，聚合函数有聚合函数是从一组中返回汇总信息，聚合函数有SUM,COUNT，MAX,MIN,AVG等等计算计算max,min,avg,suma.SQLSELECT MIN(sal),MAX(sal),AVG(sal),SUM(sal) FROM emp

10、;b.找出具有最高工资的员工？找出具有最高工资的员工？ SQLSELECT ename,job,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);COUNT与与COUNT DISTINCTa.求求emp表中总行数？表中总行数？ SQLSELECT COUNT(*) FROM emp;b.求求job的行数？的行数？ SQLSELECT COUNT(job) FROM emp;c.求有多少工种？求有多少工种？SQLSELECT COUNT(DISTINCT job) FROM emp;SELECT 子子句中目标的一致性句中目标的一致性不能简单地把单

11、行函数与聚合函数混在一起使用，如不能简单地把单行函数与聚合函数混在一起使用，如 SQLSELECT ename,SUM(sal) FROM emp; 单行目标单行目标 聚合函数聚合函数使用使用GROUP BY 分组分组a.查询从事每一个工种的员工的工资总和？查询从事每一个工种的员工的工资总和？ SQLSELECT job,SUM(sal) FROM emp GROUP BY job;b.求出从事每一个工种的员工总数？求出从事每一个工种的员工总数？ SQLSELECT job,COUNT(*) FROM emp GROUP BY job;c.查询每个部门的最高工资及工资总和？查询每个部门的最高工

12、资及工资总和？ SQLSELECT deptno, MAX(sal),SUM(sal) FROM emp GROUP by deptno;按多个条件分组按多个条件分组SQLSELECT deptno,job,COUNT(*) FROM emp GROUP BY deptno,job;使用使用Having,选择满足条件的组选择满足条件的组SQLSELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(SAL)8000;求求,除去除去CLERK职业职业,那些部门工资总和超过那些部门工资总和超过$3000的的部门号及其工资总和部门号及其工资

13、总和(按工资总和降序排列）按工资总和降序排列）SQLselect deptno,sum(sal) from emp where job != CLERK group by deptno having sum(sal)3000 order by sum(sal) DESC;UNION操作是将多个基表的查询结果作并运算操作是将多个基表的查询结果作并运算. 例子例子:有两个基表有两个基表Development,Information 要求查询出两个表中所有年龄超过要求查询出两个表中所有年龄超过 35 周岁的员工周岁的员工? SQLselect employee_name,age from devel

14、opment where age35 UNION select employee_name,age from information where age35;DevelopmentInformationUNION？UNIONUNION与与UNION ALLUNION ALL的区别。的区别。( (参见参见P75)P75)INTERSECT操作是将多个基表的查询结果作交运算。操作是将多个基表的查询结果作交运算。例子例子:有两个基表有两个基表Development,Information 要求查询出两个表中都有的工种要求查询出两个表中都有的工种? SQLselect job from develop

15、ment INTERSECT select job from information ;DevelopmentInformationINTERSECT另见另见P75P75例子例子MINUS操作是将多个基表的查询结果作差运算操作是将多个基表的查询结果作差运算.例子：例子：有两个基表有两个基表Development,Information要求查询出表要求查询出表 Development中有中有,而表而表Information中没有的职业中没有的职业? SQLselect job from development MINUS select job from information ;Developm

16、entInformationMINUS另见另见P76P76例子例子l现有学生成绩表现有学生成绩表grade(sno,cno,gmark),写出下列查询写出下列查询:SELECT Sno FROM Grade WHERE Cno=1 UNION SELECT Sno FROM Grade WHERE Cno=2; 1.选修了选修了1号或号或2号课程的学生学号号课程的学生学号SELECT Sno FROM Grade WHERE Cno=1 INTERSECTSELECT Sno FROM Grade WHERE Cno=2; 2.选修了选修了1号和号和2号课程的学生学号号课程的学生学号SELEC

17、T Sno FROM Grade WHERE Cno=1 MINUS SELECT Sno FROM Grade WHERE Cno=2; 3.选了选了1号课程而没选号课程而没选2号课程的学生学号号课程的学生学号在在SQL Server中如何实现后两个查询中如何实现后两个查询？单个子查询单个子查询 查询在查询在BOSTON工作的员工姓名工作的员工姓名? SQLSELECT ename,deptno FROM emp WHERE deptno IN (select deptno from dept where loc=BOSTON);多个子查询语句多个子查询语句 哪些员工的工作与哪些员工的工作与

18、CLARK相同相同,并且工资比他高并且工资比他高? SQLSELECT ename,job,sal FROM emp WHERE job=(select job FROM emp where ename=CLARK) AND sal(select sal from emp where ename=CLARK); 子查询中有多个基表子查询中有多个基表在在NEW YORK 工作的雇员中工作的雇员中,有哪些员工的工资比有哪些员工的工资比SCOTT高高?SQL SELECT ename,job,sal FROM emp,dept WHERE loc=NEW WORk AND emp.deptno=de

19、pt.deptno AND sal(SELECT sal FROM emp WHERE ename=SCOTT);子查询中使用聚合函数子查询中使用聚合函数 哪个雇员最早受雇哪个雇员最早受雇? SQLSELECT ename,hiredate FROM emp WHERE hiredate=(SELECT MIN(hiredate) FROM emp); 当子查询返回值不止一个时当子查询返回值不止一个时,可以使用可以使用ALL,ANY,IN,NOT IN 查询其他部门中比部门查询其他部门中比部门30中的某一个雇员工资高的员工中的某一个雇员工资高的员工?SQLSELECT ename,sal FR

20、OM emp WHERE salANY(SELECT sal FROM emp WHERE deptno=30) AND deptno !=30;当子查询返回值多于一个当子查询返回值多于一个列列时时,主查询主查询WHERE子句中的列名子句中的列名 用括号括起来用括号括起来. 求出与求出与 FORD 有相同的职务与工资的员工有相同的职务与工资的员工? SQLSELECT ename,job,sal FROM emp WHERE (job,sal)= (SELECT job,sal FROM emp WHERE ename=FORD)内连接内连接（Inner Join）外连接（外连接（Outer

21、Join）自身连接（自身连接（Self Join） P66内连接内连接SQLSELECT dept.deptno,dname,SUM(sal) “MONTHLY SALARY FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dept.deptno,dname ORDER BY dept.deptno;外连接外连接(Oracle Outer Join)SQLSELECT dept.deptno,dname,SUM(sal) “MONTHLY SALARY FROM emp,dept WHERE emp.deptno(+)=dept.dept

22、no GROUP BY dept.deptno,dname ORDER BY dept.deptno;尽管尽管EMPEMP表中没有员工表中没有员工工作在此部门，但其工作在此部门，但其部门信息仍显示于此部门信息仍显示于此, ,只不过其工资为空。只不过其工资为空。（P61）以以dept中的中的deptno为基准，为基准， emp中无此中无此deptno的返回空值。的返回空值。外连接外连接(ANSI Outer Join)SQLSELECT dept.deptno,dname,SUM(sal) FROM emp right outer join dept ON emp.deptno=dept.dep

23、tno GROUP BY dept.deptno,dname ORDER BY dept.deptno;ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3select R.*,S.*from R inner join S on R.C = S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2RSselect R.*,S.*from R ,Swhere R.C = S.C内连接内连接select R.*,S.*from R left outer join S on R.C = S.CABR.CS.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnu

24、llselect R.A,R.B,S.*from R right outer join Son R.C = S.CABCDa1b1c1d1a2b2c2d2nullnullc4d3左外连接左外连接右外连接右外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3R RS Sselect R.*,S.*from R ,S where R.C = S.C(+)(+)select R.A,R.B,S.* from R ,S where R.C (+)(+)= S.Cselect R.*,S.*from R full outer join S on R.C = S.CAB R.C S

25、.CDa1b1c1c1d1a2b2c2c2d2a3b3c3nullnullnullnullnullc4d3全外连接全外连接ABCa1b1c1a2b2c2a3b3c3CDc1d1c2d2c4d3R RS Sselect R.*,S.*from R ,S where R.C (+)(+)= S.C(+)?见P63 序列是由序号生成器自动生成的能够对表中的行唯一标识序列是由序号生成器自动生成的能够对表中的行唯一标识的顺序数（或理解为流水号），在多用户环境下的顺序数（或理解为流水号），在多用户环境下，可以此可以此生成各返回序列号生成各返回序列号值作为主键来保证唯一性。值作为主键来保证唯一性。2. in

26、sert into orders (orderno, custno)values (order_sequence.NEXTVAL, 100001);insert into orders (orderno, custno)values (order_sequence.NEXTVAL, 100002);序列可以使用的两个参数：序列可以使用的两个参数： NEXTVAL,CURRVALSQLALTER SEQUENCE order_sequence MAXVALUE 100000 CYCLE；SQLDROP SEQUENCE order_sequence;l为什么要引入同义词为什么要引入同义词Lily

27、用户查询用户查询scott用户下的用户下的emp表：表：SQLSelect * from scott.emp;为为scott用户下的用户下的emp表创建一同义词表创建一同义词emp:SQLCreate synonym emp for scott.emp;同义词同义词l为什么要引入同义词（续）为什么要引入同义词（续）l私有私有( (private)private)同义词同义词l公用公用( (public)public)同义词同义词前面创建的同义词前面创建的同义词Create synonym emp for scott.emp只能为创建它的那个用户使用，称为私有同义词只能为创建它的那个用户使用，称

28、为私有同义词。所有的所有的OracleOracle用户都可使用的同义词称为公用同义词。用户都可使用的同义词称为公用同义词。用以下语句创建用以下语句创建公用同义词公用同义词： Create public synonym emp for scott.emp说明：要创建公共同义词，必须要有说明：要创建公共同义词，必须要有create public synonym系统权限；要创建一个自己用户下使用的同义词，必须要有系统权限；要创建一个自己用户下使用的同义词，必须要有create synonym系统权限。系统权限。l同义词创建好后，就可以在任何地方引用它，同义词创建好后，就可以在任何地方引用它，以此来代

29、替基表的引用，以此来代替基表的引用，若基表的名字改变了，若基表的名字改变了，只需重新创建同义词即可只需重新创建同义词即可。例如。例如scott用户下的用户下的emp表更名为表更名为emp_information ，则重新定义，则重新定义同义词如下：同义词如下：Create synonym emp for scott.emp_information，这样所有引用同义这样所有引用同义词的地方不必做任何改动。词的地方不必做任何改动。l删除同义词删除同义词 Oracle Assessment Tests What happens when you update a table without a WHERE clause?A. The statement will not execute.B. Only the rows specified will be updated.C. All of the rows in the table will be updated.D. The statement will execute,but no change will be made. You alter the database with this command: ALTER TABLE employee ADD end_date DATE;Which task is ac