在PL/SQL中使用SQL SQL基本命令分类语句 SQL语句可以分为六类

Presentation on theme: "在PL/SQL中使用SQL SQL基本命令分类语句 SQL语句可以分为六类"— Presentation transcript:

1 在PL/SQL中使用SQL SQL基本命令分类语句 SQL语句可以分为六类
数据操纵语言: select,delete,update,insert, set transaction,explain plan 数据定义语言: drop,create,alter,grant,revoke 事务控制: commit,rollback,savepoint 会话控制:alter session,set role 系统控制: alter system 嵌入式SQL: connect, declare cursor,allocate PL/SQL使用静态联编，所以DDL语句被禁止使用

2 静态联编:是指联编工作是在程序编译连接阶段进行的，这种联编又称为早期联编；因为这种联编是在程序开始运行之前完成的。
动态联编:编译程序在编译阶段并不能确切地知道将要调用的函数，只有在程序执行时才能确定将要调用的函数，为此要确切地知道将要调用的函数，要求联编工作在程序运行时进行，这种在程序运行时进行的联编工作被称为动态联编，或动态束定，又叫晚期联编

3 DML和事务控制语句是唯一的不会修改数据模式对象或对其操作的特权的SQL语句，是唯一可在PL/SQL中运行的SQL语句。使用DDL是非法的。
Begin create table my_table1( id number, name varchar2(8)); Insert into my_table1(id,name) Values(1,’hello!’); End; 该块不能运行。 DML和事务控制语句是唯一的不会修改数据模式对象或对其操作的特权的SQL语句，是唯一可在PL/SQL中运行的SQL语句。使用DDL是非法的。 使用DDL：DBMS_SQL允许运行时刻动态地创建SQL，并分析执行他。

4 5.2 数据定义语言 SQL 命令功能 Alter procedrue 修改存储过程 Alter table
5.2 数据定义语言 SQL 命令功能 Alter procedrue 修改存储过程 Alter table 增加表列、重定义表列、更改存储分配 Analyze 收集数据库对象的性能统计值并送入基于代价的优化器 Alter table add constraint 在已有的表上增加约束 Create procedure 创建存储过程 Create table 创建表 Create index 创建索引 Drop index 删除索引 Drop table 删除表 Grant 授权 Truncate 删除表中的所有行 revoke 收回权限

5 5.3 数据操纵语言DML 在SQL语句中允许在使用表达式的地方使用变量。 例： Declare
v_numcredits classes.num_credit%type; Begin v_numcredits:=3; update classes set num_credits:=v_numcredits where department=‘His’ and course=‘101’; End;

6 SELECT: 并非所有的SQL中的实体都可以用变量替换，只有表达式是可以的，表名和列名必须都要知道。 语法：
Select select_list_item into pl_sql_record/variable from table where where_clause; 这里select只返回一条记录，如果多于一条，则出错，应该用游标。 例： Declare v_studentrecord students%rowtype; v_department classes.department%type; v_course classes.course%type; Begin select * into v_studentrecord from students where id=10000;

7 字符比较： 当两个字符进行比较的时候，oracle可以使用填充格式和非填充格式进行比较。
Select department,course into v_department,v_course from classes where room_id=99997; End; 字符比较： 当两个字符进行比较的时候，oracle可以使用填充格式和非填充格式进行比较。 填充格式使用下面的语法（string1和string2比较） 1)如果string1和string2长度不同，则在较短的字符串上填充空格，使得两个字符串的长度相同。 2)逐个字符比较字符串，从左边开始，假设string1的字符是char1，string2的字符是char2。 3)如果ASCII(CHAR1)<ASCII(CHAR2),则string1<string2。 如果ASCII(CHAR1)>ASCII(CHAR2),则string1>string2。 如果ASCII(CHAR1)＝ASCII(CHAR2),则继续比较下应该字符。 4)如果达到了字符串的末尾，则认为两个字符串是相同的。

8 非填充格式的比较算法如下： 使用填充格式比较语意，则下面的条件都返回TRUE。 ‘abc’=‘abc’ ‘abc ‘=‘abc’
‘ab’<‘abc’ ‘abcd’>’abcc’ 非填充格式的比较算法如下： 1)逐个字符比较字符串，从左边开始，假设string1的字符是char1，string2的字符是char2。 2)如果ASCII(CHAR1)<ASCII(CHAR2),则string1<string2。 如果ASCII(CHAR1)>ASCII(CHAR2),则string1>string2。 如果ASCII(CHAR1)＝ASCII(CHAR2),则继续比较下一个字符。 3)如果达到了string1的末尾，但是没有达到string2的末尾，那么string1<string2。如果达到了string2的末尾，但是没有达到string1的末尾，那么string1>string2。

9 使用非填充格式比较语意，则下面的条件都返回TRUE。
‘abc’=‘abc’ ‘ab’<‘abc’ ‘abcd’>abcc’ 但是，下面的比较将返回FALSE ‘abc ‘=‘abc’ 仅当两个比较的字符串具有固定的长度时，才使用填充格式比较语意，如果其中一个是变长的，就会使用非填充格式比较语意。Char是定长的，varchar2是变长的，字符文字被认为是定长的。 例：下面的块不会删除任何行，因为，department是char类型，而v_department是varchar2类型。 Declare v_department varchar2(3); Begin v_department:=‘CS’; delete from classes where department=v_department; End;

10 表引用 为了确保where子句带来期望的结果，一定要使变量与要比较的数据库列有相同的类型，用％type可以保证这一点。
所有的DML都对指向表的引用进行处理，这个引用通常会采用如下的形式： schema标识该表的拥有者，Dblink标识远程数据库中的一个表。为了建立一个数据库连接，应该给出特定模式的用户名和口令。在会话中其他后继的SQL语句将缺省地指向这个数据模式。如果没有指明数据模式，那么给出的表应该是缺省数据模式下的一个表。缺省地，数据模式是在执行SQL语句以前连接的数据模式。如果该表是在另一个数据模式中，可以指明数据模式来标识他。如： Update example.students set major=‘music’ where id=10005;

11 数据链接：数据库链接是指向远程数据库的引用，该数据库可以位于与本地数据库完全不同的系统中。下面的DDL语句会创建一个数据库链接。
Create database link link_name connect to username identified by password using sql_string; Link_name跟在用于数据库标识符的通用规则之后。Sql_string 是一个有关远程数据库的有效连接字符串。 例： Create database link example_bakkup backup connect to example indentified by example using ‘backup_database’; 在进行了前面的连接之后，可以使用以下语句对student表进行远程更新。 Update set major=‘Music’ where id=10005; 当有关数据库连接被用作某个事务的一部分时，该事务被称作分布式事务。

12 替代名：可以为复杂的表引用创建替代名。语法如下：
Create synonym synonym_name for reference; 这里reference可以是一个表、过程、序列或其他的数据库对象。例： Create synonym backup_students for 有了上面的替代名以后，可以重写前面的分布式语句。 Update back_students set major=‘Music’ where id=10005;

13 伪列：是仅能从SQL语句中进行调用的其他函数。是作为SQL语句的一部分进行求值和被执行的。
Currval和nextval：和序列一起用。Currval返回序列的当前值，nextval返回序列的下一个值。 Rowid：返回特定行的标识符 例：select rowid from rooms; Rownum:返回当前行的序号，第一行的序号为1，第二行的序号为2。例： Select * from students where rownum <3;

14 5.3.5 lock table命令 Oracle提供了表锁定和记录锁定。
一些DML语句如INSERT、UPDATE、DELETE以及带有UPDATE的SELECT语句会自动对表进行锁定。在不影响完整性的前提下，以最低模式进行锁定。 Lock table显式锁定,语法如下： Lock table table_name in lockmode MODE{nowait} 例： Lock table tdz,tdzjsjl in exclusive mode nowait;

15 5.4 事务控制 事务的开始位置是在从前一个事务结束以后执行的第一条SQL语句或在连接到数据库以后执行的第一条SQL语句。事务的结束是使用commit或rollback标识的。 set transaction 用于显式地开始一个事务，其语法如下： Set transaction read only write only use rollback segment 回滚段名 Read only：用于标识事务级读一致性，事务处理过程中只能查询，不能进行其它操作。查询只能够看到事务处理前提交的事务。 Read write：用于标识语句级读一致性 例：set transaction read only; Select count(*) from ship;

16 Update department set dept_name=‘软件开发部’ where dept_id=1; Commit;

17 5.4.2Commit与rollback 当向数据库发出commit语句，那么该事务就被终结了。并且 Commit语句的语法是
1.事务完成的所有工作都是永久性的 2.其他的会话可以看到此事务的修改 3.事务所需要的所有加锁处理都被释放了。 Commit语句的语法是 Commit [work]; 可选的work用来提高可读性。直到提交事务以前，只有执行该事务的会话可以看到自己所作的修改。

18 会话A 会话B Insert into rooms (room_id,building, room_number, number_seats,description) Values (99991,’building 7’,310, 50,’discussion room e’); select * from rooms where building=‘building 7’; Commit;

19 当对数据库执行rollback时，该事务就被终结了。并且：
1.事务完成的所有工作都被取消，就好像没有执行这些命令一样。 2.事务所需要的所有加锁处理都被释放了。 Roolback语句的语法是： Rollback [work]; 与commit一样，work关键字也是可选的，是用来提高可读性的。当程序检测出一个错误，而不能继续工作时，通常都使用显式的rollback语句。如果一个会话断开与数据库的链接，而没有使用rollback或commit终结当前的事务，那么该事务会自动被数据库取消。 *当你退出SQL*PLUS时，系统自动发出一条commit语句。Autocommit选项会在每条SQL语句之后发出commit命令。这不会影响PL/SQL块内的SQL语句执行情况，因为，在PL/SQL块结束以前，SQL*PLUS是不会取得控制权的。

20 5.4.3 保存点： Rollback会撤销整个事务，但是，使用了savepoint，只有部分的事务需要被撤销。其语法是：
Savepoint name; 这里name是保存点的名字。保存点没有在声明部分进行声明，因为他们对于事务而言不是全局的，并且事务可以延续到一个块结束以后。一旦定义了保存点，可以使用以下的语句撤销事务到该保存点。 Rollbak [work] to savepoint savepointname; 在使用了以上命令以后，会发生以下事情。 1.从保存点以后所作的所有工作都被撤销。但是，保存点依然有效，如果需要的话，可以再次撤销到该保存点。 2.从该保存点以后，SQL语句所获取的所有锁和资源都被释放。 3.该事务没有完成，因为SQL语句依然被挂起。

21 例： Begin insert into temp_table(char_col) values (‘insert one’); savepoint a; insert into temp_table(char_col) values (‘insert two’); savepoint b; insert into temp_table(char_col) values (‘insert three’); savepoint c; commit; End; 如果在savepoint c之后插入rollback to b；则第三条插入语句被撤销。 Savepoint通常在事务的比较复杂的部分以前被使用，如果该事务的该部分失败，他就被撤销，使得以前的工作结果可以被继续使用。

22 事务和块 事务和快之间是有区别的。在启动一个块时，并不意味着该事务被启动了。类似地，事务的开始，并不需要和块的开始相一致。例：
--ltrans.sql Insert into classes(department,course,description,max_students, current_students,num_credits, room_id) values (‘CS’,101,’computer science 101’,50,10,4,99998); Begin update rooms set room_id=room_id-1000; roolback work; End; Rollback不仅撤销了update语句，而且撤销了insert语句。Insert和该块都是同一个事务中的。

23 类似地，单个块，可以包含多个事务。例： Declare v_numiterations number; Begin
for v_loopcounter in loop insert into temp_table(num_col) values (v_loopcounter); v_numiterations number:= v_numiterations number+1; if v_numiterations number=50 then commit; v_numiterations number:=0; end if; end loop; End;

24 5.5 会话控制命令 5.5.1 alter session命令 是否使用SQL跟踪功能 改变NLS参数 改变会话的缺省标号格式
5.5 会话控制命令 alter session命令 用该命令可以按下列方式更改当前会话 是否使用SQL跟踪功能 改变NLS参数 改变会话的缺省标号格式 关闭一个数据库链 发送一个忠告到远程数据库，对一个悬而未决的分布式事务进行处理 禁止或允许在过程或存储函数中发出COMMIT和ROLLBACK语句 修改基于开销的优化方法的目标 语法如下： Alter session set paraname=value

25 例：修改NLS参数，设置语言为法语，日期格式为‘YYYY MM DD HH24:MI:SS’
例：启用SQL跟踪功能 　alter session set sql_trace=true; 例：修改NLS参数，设置语言为法语，日期格式为‘YYYY MM DD HH24:MI:SS’ Alter session set nls_language=French; Alter session set nls_data_format=‘YYYY MM DD HH24:MI:SS’; 例：改变优化方法和目标来优化SQL语句，这些优化方法有： First_row：最快响应时间 All_row：最大吞吐量 Rule：基于规则 Alter session set optimizer_goal=first_row

26 5.5.2 set role 命令 该命令的作用是设置角色对当前会话能否使用。 语法如下：
Set ROLE role [identified by password][role [identified by password],…]|all [except role]|none 例：使当前用户可以使用其被授予的但不是缺省的角色role1，其口令为tiger Set ROLE role1 identified by tiger; 例：使当前用户可以使用除了BANKER角色以外的所有角色特权。 Set ROLE EXCEPT BANKER;

27 5.6 系统控制命令 使资源限制是否起作用 为共享服务器结构管理共享服务器进程或调度进程 显式地转换日志文件组 显式地执行一个检查点
5.6 系统控制命令 该命令的作用可以在下列方面动态地更改Oracle数据库实例： 使资源限制是否起作用 为共享服务器结构管理共享服务器进程或调度进程 显式地转换日志文件组 显式地执行一个检查点 检验数据文件的存取 限制用户对Oracle登录，仅使具有RESTRICTED SESSION系统特权的用户登录 在单进程环境下使分布式恢复使能 手工地归档日志文件组或使自动归档不能用 清除SGA共享池中的全部数据 终止一个会话

28 其语法如下： Alter system Set paraname=value |{enable|disable} restricted session |flush shared_pool |switch logfile |archive log archive_log_clause |kill session ‘integer1,integer2’ 例：清除SGA共享池 Alter system flush shared_pool; 例：设置共享服务器进程数量最小为25。 Alter system set MTS_servers=25

29 例：动态调整进程数，对TCP/IP协议为５对DECNET量为10。
Alter system set mts_dispatchers=‘TCP,5’ MTS_Dispachters=‘DECNET,10’ 例：动态设置使用许可限制 Alter system set license_max_sessions=64; Alter system set license_sessions_warnning=54; 例：终止会话，首先查找会话的SID以及序号，然后再终止会话。 Select sid,serial#,username from v$session; Alter system kill session ’13,8’;--sid=13,serial=8

30 SQL基本命令(2) 综合统一 高度非过程化 面向集合的操作方式 SQL概述 SQL的特点 非关系模型的数据定义语言分为：
1.模式数据定义语言 2.外模式数据定义语言 3.与数据存储有关的描述语言 SQL集DDL、DML、DCL于一体，语言风格统一 关系模型中对实体即实体的联系都用关系表示，因此操作符统一 非关系模型中对实体的操作符和对实体间的联系不一样 高度非过程化 面向集合的操作方式

31 SQL易用的基本概念 以同一种语言提供两种使用方式 语言简洁、易学、易用 视图－－外模式 基本表－－模式 存储文件－－内模式 自含式和嵌入式
基本表是存在的表，视图是导出表，只存定义，不存数据

32 语法 Oracle中的数据类型 数据定义 定义基本表 Create table 表名 (列名 数据类型 〔列约束〕）
Char(size) 存放定长字符，最多2000个 Nchar(size) 与char类似，最大长度由所用的字符集决定 Varchar2(size)----存放变长字符，最多4000个 Nvarchar(size)----类似与varchar2 Varchar 等同于char Number(L,d) 存放数值型数据，L代表总位数，d代表小数点后位数 Blob 二进制大对象，最长2GB Raw(size) 纯二进制数据，最长2000字节 Date 存放日期 Long 存放可变长字符数据，最大2GB

33 例： 空值 描述表结构：describe 表名 Create table customer(
last_name varchar2(30) not null, State_cd varchar(2), Sales number) Tablespace cust_spac Storage(initial 25k next 25k minextents 1); Storage用于定义表空间，tablespace将表放到指定的表空间 Create table state( state_cd varchar(2) not null, state_name varchar2(30)); 空值 1+null=null 描述表结构：describe 表名

34 修改基本表 语法： 例1: 例2： 例3： 例4: Alter table 表名 〔Add （列名）（数据类型）〕
〔modify （列名）（数据类型）〕 〔not null〕 〔drop 〔primary key〕〔unique （列名）〕constraint（约束名）〕〕 例1: Alter table student add scome date; 例2： Alter table modify sname char; 例3： Alter table student drop primary key; 例4: Alter table student add constraint student_sno primary key(sno);

35 删除基本表 例5: 例6: 语法： 例1:删除reservesheet 及与该表相关的完整性约束 例2:
Alter table student modify(sname varchar(8) unique); 例6: Alter table student drop unique sname; 删除基本表 语法： Drop table 表名 〔cascade constraints]; 例1:删除reservesheet 及与该表相关的完整性约束 Drop table reservesheet casecade constraints; 例2: Drop table sc;

36 建立索引 删除索引 Create index 索引名 on 表名 (列名 〔ASC|DESC]) 例1： 例2: 语法： 例：
Create index on_sheetno on reservesheet (sheetno); 例2: Create index sno_cno on sc (sno,cno); 删除索引 语法： Drop index 索引名 例： Drop index sno_cno;

37 查询 单表查询 查询指定列：SELECT 列名, 列名 Select sno,sname from student;
Select * from student; 查询经过计算的值 Select sname,1996-sage from student; Select sname,’year of birth:’,1996-sage,islower(sdept) from student 消除取值重复的行 Select distinct sno from sc;

38 查询满足条件的行：where子句 (1)比较大小：=,>,<,>=,<=,!=,<> 例1:
Select sname from student where sdept=‘cs’ 例2: select distinct sno from sc where grade<60; (2) 确定范围：between … and ,not between … and select sname,sdept,sage from student where sage between 20 and 23; select sname, sdept,sage from student where sage not between 20 and 23;

39 (3)确定集合：in, not in 例1: select sname,ssex from student where sdept in (‘is,’ma’,’cs’); 例2: where sdept not in (‘is,’ma’,’cs’) (4)字符匹配：[not] like ‘匹配串’ [escape]’换码符’ 通配符： ％（任意长度），_(任意单个字符） select sname,sno,ssex from student where sname like ‘刘%’; where sname not like ‘刘%’;

40 例3: select sname,sno from student where sname like ‘_ _阳%’; 例4:查询DB_Design课程的课程号和学分 select cno,ccredit from course where cname like ‘DB\_Design’ ESCAPE ‘\’; 例5:查询以“DB_”开头，倒数地3个字符为 i的课程的详细情况 select * from course Where cname like ‘DB_%I_ _’ ESCAPE ‘\’; (5)涉及空值的查询 例1:查询缺少成绩的学生的学号和课程号 select sno,cno from sc where grade is null; 例2:查询所有有成绩的学生的学号和课程号 select sno,cno from sc where grade is not null;

41 对查询结果进行排序：ORDER BY 列名 ASC[DESC]
(6)多重条件查询：用and ，or 联结 例1: select sname from sc where sdept=‘CS’ AND Sage<20; 例2: select sname,ssex from student where sdept=‘IS’ OR sdept=‘MA’ OR sdept=‘CS’; 对查询结果进行排序：ORDER BY 列名 ASC[DESC] Select sno,grade from sc where cno=‘3’ order by grade DESC; select * from student order by sdept,sage desc;

42 使用集函数： (1)count(*)：:统计表中所有满足条件的行的行数 count(列名)：条件表中指定列非NULL值并满足条件的行数
例1:查询学生总人数 Select count(*) from student; 例2:查询选修了课程的学生人数 Select count(distinct sno) from sc; (2)max(distinct/all 列名)： 例：计算选修1号课程的学生最高分数 select max(grade) from sc where cno=‘1’; (3)min(distinct/all 列名)： 例：计算选修1号课程的学生最低分数 select min(grade) from sc where cno=‘1’; (4)avg(distinct/all 列名)： 例：计算选修1号课程的学生平均分数

43 对查询结果分组 (5)sum distinct/all( 列名)： 例：计算emp中工资salary的总和
Select sum(salary) from emp; 对查询结果分组 语法：select 列名或表达式列表 from 表名 where 条件 group by 列名表1 having 分组中的条件 order by 列名表2 例1:求各个课程号及相应的选课人数 Select cno,count(sno) from sc group by cno; 例2:查询选修了3门以上课程的学生学号 Select sno from sc group by sno having count(*)>3; **where 作用于基本表或视图 having作用于组，从中选择满足条件的组

44 连接查询 等值连接与非等值连接 [表名1].<列名1> <比较运算符> [表名2.]<列名2> 或
[表名1].<列名1> between [表名2.]<列名2> and [表名3.]<列名3> 当连接运算符为＝时，成为等值连接 例1： Select student.*,sc.* from student,sc where student.sno=sc.sno; 例2:在上例的等值连接中，除去重复列，为自然连接 Select student.sno,sname,ssex,sdept,cno,grade from student,sc Where student.sno=sc.sno;

45 自身连接：一个表与自己连接 外连接 例：查询每一门课的间接先修课 Select first.cno,second.cpno
from course first,course second Where first.cpno=second.cno; 外连接 例：列出student表每个学生的基本情况及其选课情况，若某个学生没有选课，只输出其基本情况信息，其选课信息为空值。 Select student.sno,sname,ssex,sage,sdept,cno,grade From student,sc Where student.sno(+)=sc.sno; 此例为左连接 ＋号出现在右边为右连接

46 复合条件连接：where子句中有多个连接条件
例1:查询选修2号课程且成绩在90分以上的所有学生 Select student.sno,sname from student,sc Where student.sno=sc.sno and sc.cno=‘2’ and sc.grade>90; 例2:查询每个学生的学号、姓名、选修的课程名及成绩 Select student.sno,sname,cname,grade from student,sc,course Where student.sno=sc.sno and sc.sno=course.cno;

47 嵌套查询 在SQL语言中一个SELECT –FROM –WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询 例：select sname from student －－外查询 where sno in select sno from sc where cno=‘2’;－－内查询 **内查询的select语句中不能使用order by子句。 嵌套查询的求解方法为由里向外 带有IN谓词的子查询 例1:查询与刘晨在同一个系学习的学生 Select sdept from student Where sdept in (select sdept from student where sname=‘刘晨’);

48 本例也可用自身连接来完成： Select s1.sno,s1.sname,s1.sdept from student s1,student s2 Where s1.sdept=s2.sdept and s2.sname=‘刘晨’; 也可将内外查询的表用不同的别名区分 Select sno,sname,sdept from student s1 Where s1.sdept in (select sdept from student s2 where s2.sname=‘刘晨’); 例2:查询选修了课程名为信息系统的学生学号和姓名 Select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname=‘信息系统’));

49 带有比较运算符的子查询：父查询和子查询之间用比较运算符进行连接
本查询也可以用连接查询实现： Select sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname=‘信息系统’; *以上两例中的子查询都只执行一次，其结果用于父查询。子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。 带有比较运算符的子查询：父查询和子查询之间用比较运算符进行连接 例：查询与刘晨在同一个系学习的学生 子查询的结果是一个值时，可以用＝代替IN Select sno,sname,sdept from student where sdept= (select sdept from student where sname=‘刘晨’);

50 带有ANY或ALL完成的子查询 **子查询一定要跟在比较运算符之后，下列写法是错误的：
Select sno,sname,sdept from student where (select sdept from student where sname=‘刘晨’)=sdept 带有ANY或ALL完成的子查询 例1:查询其它系中比信息系某一学生年龄小的学生姓名和年龄 Select sname,sage from student where sage<any (select sage from student where sdept=‘IS’) and sdept<>’IS’; 本例也可以用集函数实现 select sname,sage from student where sage<(select max(sage) from student

51 例2:查询其它系中比信息系所有学生年龄小的学生姓名和年龄
Select sname,sage from student where sage<ALL (select sage from student where sdept=‘IS’) and sdept<>’IS’; 本例也可以用集函数实现 select sname,sage from student where sage<(select min(sage) from student **<all <====> <min <any <====> <max >all <====> >max >any <====> >min

52 带有EXISTS谓词的子查询 EXISTS代表存在。带有EXISTS的子查询不返回任何数据，只产生“true” 或“false”
例1:查询所有选修了1号课程的学生姓名 Select sname from student where exists (select * from sc where sno=student.sno and cno=‘1’); 此子查询为相关子查询，求解时，依赖于父查询。首先取外层查询表中的一个元组，根据他与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表中；然后再取父查询表的下一个元组；继续这一过程，直到父查询表结束。 例2:查询没有选修1号课程的学生姓名 where not exists

53 带有EXISTS的子查询不能被其它形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
例3:查询与刘晨在同一个系学习的学生 Select sno,sname,sdept from student s1 where exists (select * from student s2 where s2.sdept=s1.sdept and s2.sname=‘刘晨’); **用存在量词代替全称量词 （对于任意x）P＝＝not （exists x （not P）） 例4: 查询选修了全部课程的学生姓名 select sname from student where not exists (select * from course (select * from sc where sno=student.sno and cno=course.cno));

54 **P－>Q===not P or Q 例5:查询至少选修了学生95002选修的全部课程的学生号码。 本题可表达为：查询学号为x的学生，对于所有的课程y,只要95002学生选修了课程y，则x也选修了y。 P：表示学生95002选修了课程y Q：表示学生X选修了课程y 因此上述查询可转换为： Not exists y(P and not Q) 用SQL语言表达如下： Select distinct sno from sc scx where not exists (select * from sc scy where scy.sno=‘95002’ and not exists (select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));

55 集合查询 UNION并运算 语法：select 子句1 UNION select 子句2 〔order by 子句〕 注意：
(1)两个子句都不能包含order by子句，order by子句是用来指定全部UNION操作结果的顺序的 (2)两个子查询的列数和列的类型应相同 (3)order by子句中指定的排序必须用select列表中列的位置序号 例1:查询计算机科学系的学生及年龄不大于19岁的学生 Select * from student where sdept=‘CS’ UNION select * from student where sage<=19;

56 INTERSECE(交）运算符 例2:查询所有订单的信息，并按订单号进行降序排序。
Select serial,dinroom,meal,descp from reservesheet UNION Select serial,dinroom,meal,descp from hisreservesheet ORDER BY 1 DESC; INTERSECE(交）运算符 语法：select 子句1 INTERSECE select 子句2 〔order by子句〕 注意： (1)两个子句都不能包含order by子句，order by子句是用来指定全部UNION操作结果的顺序的 (2)两个子查询的列数和列的类型应相同 (3)order by子句中指定的排序必须用select列表中列的位置序号

57 例1：查询即在订单表中又在历史订单表中的订单信息。
Select serial,dinroom,real from reservesheet INTERSECE SELECT SERIAL,DINROOM,REAL FROM HISRESERVESHEET; 例2:查询计算机科学系的学生与年龄不大于19岁的学生的交集。 Select * from student where sdept=‘CS’ and sage<=19; 例3:查询选修课程1的学生集合与选修课程2的学生集合的交集 Select sno from sc where cno=‘1’ intersece Select sno from sc where cno=‘2’;

58 MINUS(差运算符） 语法：select 子句1 MINUS select 子句2 [order by 子句]
例1：查询在订单表中而不在历史订单表中的订单信息 Select serial,dinroom,meal from reservesheet MINUS Select serial,dinroom,meal from hisreservesheet; 例2:查询计算机科学系的学生与年龄大于19岁的学生的差集。 Select * from student where sdept=‘CS’ nad sage>19;

59 数据更新 插入数据 插入单个元组 语法：insert into 表名 [属性1,属性2,…] values (常量1,常量2，..);
注意：在表定义时说明了NOT NULL的属性列不能去空值。 例1:将一个新学生记录插入到student表中 Insert into student values (‘95020’,’陈冬‘,’男‘,’IS’,18); 例2:插入一条选课记录（‘95020’,‘1’） Insert into sc (sno,cno) Values (‘95020’,’1’); **新插入的记录在grade列上为空值。

60 修改数据 插入子查询结果 修改某一个元组的值 例：对每一个系，求学生的平均年龄，并把结果存入数据库。
Create table deptage (sage char(15),avgage number(2)); Insert into deptage(sdept,avgage) select sdept,avg(sage) from student group by sdept; 修改数据 修改某一个元组的值 语法：update 表名 set 列名＝<表达式>,<列名>=<表达式>,… [where 条件]; 例：将学生95001的年龄改为22岁 Update student set sage=22 where sno=‘95001’;

61 修改多个元组的值 带子查询的修改语句 例：将所有学生的年龄增加1岁 Update student set sage=sage+1;
语法：update 表名 set(列名1,列名2,…)= (select 列名A，列名B，…from 表名2 where 条件) Where update条件; **括号的位置非常重要 例： Update monthly_sales set (sales_amount)= (select sum(sales_amount) from daily_sales where sale_date between to_date(’01-JAN-99’) and to_date(’31-JAN-99’)) Where sales_month=to_date(‘JAN-99’,’MON-YY’);

62 使用相关子查询 语法：update 表名1 set (列名1,列名2,…)= (select 列名a,列名b,… from 表名2
where 表名2.列x=表名1.列I and 条件 ) Where 条件 例： Update type_of_seaweed set per_pound=2*per_pound,last_change_date=sysdate where edible=‘edible’; Update seaweed_sample ss set (selling_price)= (select ts.per_pound*20 from type_of_seaweed ts where ts.type_id=ss.type_id and edible=‘edible’) Where ss.type_id in (select ts1.type_id from type_of_seaweed ts1 where ts1.edible=‘edible’);

63 删除数据 删除某一个元组的值 删除多个元组的值 带子查询的删除语句 语法：delete from 表名 [where 条件];
例：删除学号为95019的学生的记录 Delete from student where sno=‘95019’; 删除多个元组的值 例：删除所有学生选课记录 Delete from sc; 带子查询的删除语句 例： Delete from seaweed_sample where type_id in (select type_id from type_of_seaweed where edible=‘inedible’);

64 视图 定义视图 建立视图 语法：create view 视图名 [列名,列名…] as 子查询 [with check option];
With check option 表示对视图进行update、insert、delete时： 要保证满足视图定义的条件 注意：在以下情况下必须指明组成视图的所有列名 (1)某个目标列不是单纯的属性名，而是集函数或表达式 (2)多表连接时选出了几个同名的列作为视图的字段 (3)需要在视图中为列启用其它的名字 例1:建立信息系学生的视图 Create view is _student as select sno,sname,sage from student where sdept=‘IS’;

65 例2:例1:建立信息系学生的视图，并要求进行修改和插入操作时仍需保证视图只有信息系的学生
Create view is _student as select sno,sname,sage from student where sdept=‘IS’ WITH CHECK OPTION; *只从单个基本表导出的视图为行列子集视图。 例3：建立信息系选修了1号课程的学生的视图 Create view is_s1(sno,sname,grade) as select studen.sno,sname,grade from student,sc where sdept=‘IS’ and student.sno=sc.sno and sc.cno=‘1’; 例4：建立信息系选修了1号课程且成绩在90分以上的学生的视图 Create view IS_S2 as select no,sname,grade from is_s1 where grade>=90;

66 删除视图 例5:定义一个反映学生出生年份的视图 Create view bt_s (sno,sname,sbirth) as
select sno,sname,2002-sage from student; 例6: 将学生的学号及他的平均成绩定义为一个视图 Create view s_g (sno,gavg) as select sno,avg(grade) from sc group by sno; 例7:将student表中的所有女生记录定义为视图 Create view f_student(stdnum,name,sex,age,dept) as select * from student where ssex=‘女’; 删除视图 语法：drop view 视图名 例：删除视图is_s1 Drop view is_s1;

67 查询视图 视图的查询和基本表的查询一样 例1:在信息系学生的视图中找出年龄小于20岁的学生
Select sno,sage from is_student where sage<20; **对视图的查询要转换成对基本表的查询，这一过程称为视图消解。 例2:查询信息选修了1号课程的学生 select sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno=‘1’;

68 例3:在s_g视图中查询平均成绩在90分以上的学生学号和平均成绩
Select * from s_g where gavg=90; S_g视图的所有为： Select sno,avg(grade) from sc group by sno; 经过视图消解后的查询语句为： Select sno,avg(grade) from sc where avg(grade)>=90 group by sno; 而where子句中是不能用集函数作为条件表达式的。 正确的语句为： group by sno having avg(grade)>=90;

69 更新视图 视图的更新最终要转换为基本表的更新 在视图定义时加上with check optin可防止对视图进行非法修改。
例1:将信息系学生视图is_student中学号为95002的学生姓名改为刘晨 Update is_student set sname=‘刘晨’ where sno=‘95002’; 例2:向信息系学生视图is_student中插入一个新的学生记录，其中学号为92029，姓名为赵新，年龄为20岁。 Insert into is_student values (‘95029’,’赵新’,20); 例3:删除学生视图is_student中学号为95029的记录 Delete from is_student where sno=‘95029’;

70 **在更新数据库中，并不是所有的视图都是可更新的，有些视图的更新不能转换成对基本表的更新。
例：update s_g set gavg=90 where sno=‘95001’; 一般地，行列子集视图是可更新的。 DB2规定： (1)若视图是由两个以上基本表导出的，则此视图不有些更新 (2)若视图的字段来自字段表达式或常数，则不有些对此视图执行insert和update，但允许执行delete (3)若视图的字段来自集函数，则此 视图不允许更新 (4)若视图定义中含有group by，则此视图不允许更新 (5)若视图定义中含有distinct，则此视图不允许更新 (6)若视图定义中有嵌套查询，并且子查询的from表也是导出该视图的基本表，则此视图不允许更新。 (7)一个不允许更新的视图上定义的视图也不允许更新。

71 视图的作用 (1)能够简化用户的操作 (2)视图使用户能以多种角度看待同一数据 (3)视图对充够数据库提供了一定程度的逻辑独立性
(4)视图能够对机密数据提供安全保护

72 为表名、字段名取别名 表的别名的定义是通过在表名后加一个名字实现的，两者用空格分开，在下面的这段查询中，H是human的别名。
Select h.human_id,h,first_name,p.land_desc from human h,property_list p where h.human_id=p.human_id; 可定义字段的别名，执行查询时，他会作为字段出现在显式结果中。例： Select human_id “human id”,property_id “prop#”, last_desc description from property_list where property_id>’B’; 注意：1.只有当要使用一些特殊符号，如#，％，逗号，空格以及要在表头中保留小写字母时，才需要引号。 2.where子句中不能用别名，必须使用准确的全名。

73 用户、角色和权限控制 用户和角色的创建 用户的创建 角色的创建 语法：create user 用户名 [identified by 口令]
例：创建一个新用户JUN,口令为WANG。 Create user JUN identified by WANG; 角色的创建 角色定义了一组相关的权限。 语法：create role 角色名 [identified by 口令] 例：建立一个角色DEPT,口令为MDB. Create role DEPT identified by MDB;

74 用户和角色的删除 删除用户的语法：drop 用户名 删除角色的语法：drop 角色名 预定义用户和角色 例：删除用户JUN
例：删除DEPT角色 Drop DEPT; 预定义用户和角色 预定义用户 某个Oracle数据库都有两个预定义的用户：SYS和SYSTEM。SYS用户拥有Oracle数据库字典表和相关的数据库对象。SYSTEM用户拥有Oracle应用开发工具所使用的表。

75 预定义角色 1.CONNECT连接权利角色 (1)访问Oracle数据库
(2)在其它用户建立的表上执行select、insert、update、delete，前提是表的拥有者已授予他相应的权限 2.RESOURCE资源权利角色 除了具有CONNECT的权限外，还具有以下权限 (1)建立表、索引和聚集 (2)对其建立的表、索引和聚集使用GRANT授予别的用户或角色，使用REVOKE收回这些权利 (3)使用AUDIT控制是否对所拥有的表、索引、聚集进行审计

76 3.DBA数据库管理员权利角色 除了拥有RESOURCE的权限外，还具有以下权限 (1)访问任何用户的数据，并对其执行任何SQL语句 (2)授予或收回用户对数据库的存取权利 (3)建立公共同义名并可为所有Oracle用户使用 (4)建立和修改分区 (5)控制系统访问的审计确省值以及表一级的审计确省值 (6)这些整个数据库的卸载 4.IMP_FULL_DATABASE：数据库输入的所有系统权利 5.EXP_FULL_DATABASE：数据库输出的所有系统权利

77 权限控制 权限的授予 语法：GRANT 权限[,权限]…/ALL [ON <对象名>]
TO <用户>[,<用户>]… /PUBLIC [WITH GRANT OPTION]; 例1：为用户JUN授予DROP ANY TABLE的权限 Grant drop any table to JUN; 例2：为用户JUN授予在表reserveguest上的insert、update、delete权限。 Grant insert,update,delete on reserveguest to jun; 例3：将用户JUN的口令改为LAN Grant CONNECT TO JUN indentified by LAN;

78 例5：把修改student表和修改学生学号的权限授予用户U4
例4：将对表SC的查询权限授予所有用户 Grant select on sc to PUBLIC; 例5：把修改student表和修改学生学号的权限授予用户U4 Grant update(sno),select on table student to u4; 例6：把对表SC的insert权限授予U5用户，并允许将此权限再授予其它用户 Grant insert on sc to u5 with grant option;

79 权限的取消 语法：REVOKE 〔权限/ALL] ON 对象名 例1：取消用户JUN的drop any table的权限
From 〔用户名/角色名/PUBLIC〕 例1：取消用户JUN的drop any table的权限 Revoke drop any table from JUN; 例2：取消用户JUN在表reserveguest上的insert、update、delete权限。 Revoke insert,update,delete on reserveguest from JUN;

80 Oracle权限列表 系统权限列表 Alter: Alter any cluster Alter any index
Alter any procedure Alter any role Alter any sequence Alter any table Alter any trigger Alter database Alter profile Alter resource cost Alter rollback segment

81 Analyze any:分析任何模式中的任何表、聚集、索引 Audit any:使用audit审计任何模式中的任何对象
Alter session Alter system Alter tablespace Alter user Analyze any:分析任何模式中的任何表、聚集、索引 Audit any:使用audit审计任何模式中的任何对象 Audit system:使用audit语句 Backup any table:使用EXPORT输出其它模式中的对象 Comment any table:在任何模式的任何表、索引、列上加注释 Cerate: Create any cluster:在任何模式中建立聚集 Create any index：在和模式中建立索引 Create procedure：在任何模式中建立存储过程、函数、包

82 Create any sequence Create any synonym：在任何模式中建立专用同义词 Create any table:在任何模式中建立表 Create any trigger Create any view Create cluster Create database link Create procedure:在自己的模式中建立存储过程、函数、包 Create profile Create public synonym Create role Create sequence Create snapshot Create table:在自己的模式中建立表

83 Create tablespace:建立表空间
Create trigger:在自己的模式中建立触发器 Create user:建立用户 Create view:在自己的模式中建立视图 Delete any table:输出任何模式中的表和视图中的行 Drop: Execute any procedure:执行过程或函数或任何模式中的公用包变量 Grant any privilege:享有系统任何特权 Grant any role:担任数据库中任何角色 Insert any table:将记录插入到任何模式中的表或视图 Lock any table:锁任何模式中的表或视图 Select any sequence:引用任何模式中的序列 Select any table:查询任何模式中的表、视图或快照 Update any table:修改任何模式的表和视图的记录

84 对象权限列表 表权限列表 视图权限列表 序列权限列表 过程、函数和报的权限列表
Alter、delete、index、insert、references、select、update 视图权限列表 Delete、insert、select、update 序列权限列表 Alter、select 过程、函数和报的权限列表 execute

85 数据库完整性(4) 完整性约束条件 完整性约束条件作用的对象分为：关系、元组、列三种。
列约束主要是列的类型、取值范围、精度、排序等约束条件。 元组的约束是元组中各个字段间的联系的约束。 关系的约束是若干元组间、关系集合上以及关系之间的联系的约束。

86 综合以上两个方面，完整性约束条件分为六类：
完整性约束的状态分为：静态、动态。 静态约束指数据库每一确定状态时的数据对象应满足的约束条件。 动态约束指数据库从一种状态转变为另一种状态时，新、旧值之间所应满足的约束条件。 综合以上两个方面，完整性约束条件分为六类： 静态列级约束 静态元组约束 静态关系约束 动态列级约束 动态元组约束 动态关系约束

87 一、静态列级约束 二、静态元组约束 对数据类型的约束（包括类型、长度、单位、精度等） 对数据格式的约束 对取值范围的约束 对空值的约束
例：规定学号的前两位表示年份，中间两位表示系号，后三位为顺序号 对取值范围的约束 例：性别取值为男、女 对空值的约束 其它约束 如：关于列的排序说明 二、静态元组约束 如：规定订货关系中发货量不得超过订货量 教师关系中教授的工资不能低于1000元

88 三、静态关系约束 动态列级约束 五、动态元组约束 六、动态关系约束 实体完整性约束 参照完整性约束 函数依赖约束（在关系模式中定义）
统计约束（字段与关系中多个元组的统计值之间的约束关系） 如：规定部门经理的工资不得高于本部门平均工资的5倍 动态列级约束 修改列定义时的约束 例将允许为空的列改为not null，如果该列目前已存在空值，则拒绝这种修改。 修改列值时的约束 如：职工工资调整不得低于原来的工资 五、动态元组约束 如：职工工资调整时新工资不得低于原工资+1.5*工龄 六、动态关系约束 如：事务一致性、原子性

89 完整性控制 DBMS的完整性控制机制具有三个方面的功能： 检查时机： 定义功能，提供定义完整性约束条件的机制
检查功能，检查用户发出的操作请求是否违背类完整性约束条件 如果发现用户的操作请求是数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性 检查时机： 立即执行约束：一条语句执行完后，立即执行 延迟执行约束：整个事务结束后再进行，检查正确方可提交 如果发现用户操作请求违背类完整性约束提交，系统将拒绝该操作，但对于延迟执行的约束，系统将拒绝整个事务。

90 一条完整性工资可以用一个五元组 (D,O,A,C,P) 来表示
C选择A作用的数据对象值的谓词 P违反完整性规则时触发的过程 例一：在“学号不能为空”的约束中 D 约束作用的对象为sno属性 O 插入或修改student元组时 A sno不能为空 C 无（A可作用于所有记录的sno属性） P 拒绝执行该操作

91 外码能否接受空值问题（参照应用环境的语意）
例二：在“教授规则不得低于1000元”的约束中 D 约束作用的对象为工资sal属性 O 插入或修改职工元组时 A sal不能小于1000 C 职称＝‘教授’ P 拒绝执行该操作 外码能否接受空值问题（参照应用环境的语意） 例一：emp中的deptno参照dept中的deptno，emp中deptno允许为空值，表示此职工尚未分配具体的部门工作。 例二：sc中的sno参照student中的sno，sc中的sno不许为空

92 在被参照关系中删除元组的问题 在参照关系中插入元组的问题 级联删除 受限删除 置空值删除 受限插入 递归插入 参照关系中的相应元组一起删除
被参照关系相应元组不许删除 置空值删除 删除被参照关系，同时将参照关系的相应元组的外码置空值 在参照关系中插入元组的问题 受限插入 递归插入

93 修改关系中主码的问题 不允许修改主码 允许修改主码
如要修改应先删除此记录，再插入 允许修改主码 但修改的主码必须保证主码的唯一性和非空 当修改的关系是被参照关系时，要检查参照关系，是否存在这样的元组，其外码值等于被存在关系中的主码值，如果有，可选择的策略有：级联修改、拒绝修改、置空值。 当修改的关系是参照关系时，要检查被参照关系，是否存在这样的元组，其主码值等于被存在关系中的外码值，如果有，可选择的策略有：受限插入、递归插入

94 Oracle的完整性 Oracle中的实体完整性 例1： 例2： 提供primary key定义建表时的主码
Create table student (sno number(8), Sname varchar(20), Sage number(20), Constraint pk_sno primary key (sno)); 例2： Create table sc cno number(2), grade number(2), Constraint pk_sc primary key(sno,cno));

95 Oracle中的参照完整性 在创建表时用foreign key定义外码，reference指明被参照关系的相应主码，用on deleted cascade指明在删除被参照关系的元组时，同时删除参照关系中的相应的元组 例1： Create table sc (sno number(8), cno number(2), grade number(2), constraint fk_sno foreign key(sno) references student(sno), constraint fk_cno foreign key(cno) references course(cno));

96 Oracle中用户定义的完整性 列值非空 列值唯一 列值是否满足一个布尔表达式 例1： Create table dept
(deptno number, Dname varchar2(9) constraint u1 unique, Loc varchar(10), constraint pk_dept primary ley(deptno));

97 例2： 例3： Create table student (sno number(5)
constraint c1 check(sno between and 99999), sname varchar2(20) constraint c2 not null, sage number(3) constraint c3 check(sage<29), ssex varchar2(2) constraint c4 check(ssex in(‘男’,’女’); 例3： Create table emp (eno number(4), Ename varchar2(10), Job varchar2(8), Sal number(7,2), deduct number(7,2), Deptno number(2), Constraints c1 check(sal+deduct<=3000));

98 例4:为teacher表定义触发器 Create trigger update_sal
before insert or update of sal, pos on teacher for each row when (:new.pos =‘教授’) begin if :new.sal<1000 then :new.sal:=1000; end if; end;