第三章 SQL

null第三章 关系数据库标准语言 SQL语言第三章 关系数据库标准语言 SQL语言主要内容主要内容§3.1 SQL概述§3.2 查询语句§3.3 更新语句§3.4 SQL DDL§3.5 SQL DCL§3.6 视图§3.7 嵌入式SQL■■§3.1 SQL概述 §3.1 SQL概述 一、 SQL的发展及现状 二、SQL数据库的体系结构 三、SQL的功能 四、SQL的形式 五、SQL的特点 §3.1 SQL概述 §3.1 SQL概述 一、 SQL的发展及现状 1974年，由Boyce和Chamberlin提出 1975~1979，IBM San Jose Research Lab的关系 数据库管理系统原型System R实施了这种语言 SQL-86是第一个SQL标准 SQL-89、SQL-92(SQL2)、SQL-99(SQL3) 大部分DBMS产品都支持SQL，成为操作数据库的 标准语言二、SQL数据库的体系结构 二、SQL数据库的体系结构 SQL用户Base Table B1View V1View V2Base Table B2Base Table B3Base Table B4Stored File S1Stored File S1Stored File S1Stored File S1外模式模式内模式SQL语言支持的关系数据库的三级模式结构二、SQL数据库的体系结构 二、SQL数据库的体系结构 基本概念： 1、用户可以用SQL语言对视图(View)和基本(Base Table)进行查询等操作，在用户观点里，视图和表一样，都是关系。 2、视图是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。 3、基本表是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。三、SQL的功能三、SQL的功能 数据定义（DDL） 定义、删除、修改关系模式（基本表） 定义、删除视图（View） 定义、删除索引（Index） 数据操纵（DML） 数据查询 数据增、删、改 数据控制（DCL） 用户访问权限的授予、收回四、SQL的组成(分类) 四、SQL的组成(分类) DATABASE TABLE CREATE VIEW INDEX SQL DDL： ALTER DROP SQL QUERY：SELECT … FROM … INSERT … SQL SQL DML： UPDATE … DELETE … GRANT SQL DCL： REVOKE AUDIT 嵌入式SQL： 五、SQL的形式五、SQL的形式 交互式SQL 一般DBMS都提供联机交互工具 用户可直接键入SQL命令对 数据库进行操作 由DBMS来进行解释 嵌入式SQL 能将SQL语句嵌入到高级语言（宿主语言） 使应用程序充分利用SQL访问数据库的能力、宿主 语言的过程处理能力 一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句六、SQL的特点 六、SQL的特点 4. 具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。高度非过程化的语言：用户只需提出“干什么”，至于 “怎么干”由DBMS解决；用户只需要早查询语句中提出需 要什么，DBMS即可按路径存取，并把结果返回给用户。2. 面向集合的语言：每一个SQL的操作对象是一个或多个关 系，操作的结果也是一个关系。3.一种语法结构，两种使用方式：即可独立使用，又可嵌入到 宿主语言中使用，具有自主型和宿主型两种特点。五、SQL的特点 五、SQL的特点 5. 语言简洁、易学易用：核心功能只有9个动词，语法简单， 接近英语。 SQL功能 动词 数据库查询 SELECT 数据定义 CREATE，DROP ，ALTER 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE(一) 数据类型 (一) 数据类型 1、字符型：CHAR(n)，VAR CHAR 2、数字型：INT, SMALLINT,REAL… 3、日期型：DATE,TIME(二) 函数 (二) 函数 数字函数: ABS(X),SQRT(X),RAND(X),LOG(X), … 字符函数： LENGTH(X$),LOWER(X$),UPPER(X$), SUBSTRING(expression, start, length)… 分组函数： COUNT(＊),MAX(X),MIN(X),AVG(X), SUM(X)(三) 运算符(三) 运算符句法操作符：& 数值操作符：+,－,＊,／, … 逻辑操作符：=,>,<,≤,≥,≠,IN,ANY,ALL, … 查询表达式操作符： UNION,… 其它操作符 ：＊, (+) ,…§3.2 查询语句 §3.2 查询语句 基本结构 Select A1, A2, ..., An From R1, R2, ..., Rm Where P数据查询是数据库应用的核心功能 πA1, A2, ..., An(p(R1×R2×...×Rm))§3.2 查询语句 §3.2 查询语句 ■ 语句 (P91) SELECT [ALL|DISTINCT] [表名.]{*|列名|表达式[AS 新列名]} [INTO :主变量1[,:主变量2]…] FROM 表名[表别名], … [WHERE 条件表达式|子查询] [GROUP BY 列名1, … [HAVING 分组表达式]] [{UNION|INTERSECT |EXCEPT}SELECT…FROM…] [ORDER BY 列名|列序号[ASC|DESC], …]；nullSELECT…FROM常用语句执行过程 SELECT… ⑤ 投影 FROM… ① TABLE→内存 WHERE… ② 选取元组 GROUP… ③ 分组 HAVING… ④ 选择分组 [{UNION|… } ⑥ 查徇结果的集 合运算 SELECT… ] ①～⑤ ORDER BY…… ⑦ 排序输出nullπA1, ..., An(p(R1×...×Rm)) Select A1, A2, ..., An From R1, R2, ..., Rm Where P一、单表查询 1. 选择表中的列 2. 选择表中的行 3. 分组与组函数 4. 排 序一、单表查询一、单表查询选择表中的列 例1.求学生所在系及姓名 SELECT SD, SN FROM S； 例2.求学生的全部信息 SELECT S#,SN,SA,SD FROM S； (SELECT *)nullSELECT Sname NAME, 'Year of Birth:'BIRTH, 2003-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student; 例3.查全体学生的姓名、出生年份和所有系，要求用 小写字母表示所有系名 SELECT Sname , 'Year of Birth:', 2003-Sage , LOWER(Sdept) FROM Student; nullnull选择表中的行 1) 消除重复行: DISTINCT (缺省为ALL)  例: 求选修了课程的学生号 SELECT DISTINCT S# FROM S；Where 子句——运算符Where 子句——运算符比较：<、<=、>、>=、=、<>、not + ~ 确定范围： Between A and B、Not Between A and B 确定集合：IN、NOT IN 字符匹配：LIKE，NOT LIKE 空值：IS NULL、IS NOT NULL 多重条件：AND、OR、NOT用WHERE子句实现复杂查询条件null例: 求年龄在18～22 (含18, 22)之间的学生名及年龄（或不在18～22之间) SELECT SN, SA FROM S WHERE SA BETWEEN 18 AND 22； (WHERE SA>=18 AND SA<=22)； (WHERE SA NOT BETWEEN 18 AND 22)； (WHERE SA<18 OR SA>22)；例：求计算机系年龄小于19的姓名及年龄 SELECT SN, SA FROM S WHERE SD=’CS’ AND SA<19；2) 选满足一定条件的行： null3)查询条件来自集合： 用IN or NOT IN 例: 求信息工程系、计算机系、数学系的系名、学生名 (或不是这些系的学生) SELECT SD DEPT, SN NAME FROM S WHERE SD IN (‘IE’, ‘CS’, ‘MA’)； (WHERE SD=‘IE’ OR SD=‘CS’ OR SD=‘MA’) (WHERE SD NOT IN (‘IE’, ‘CS’, ‘MA’)； (WHERE SD!=‘IE’ AND SD!=‘CS’ AND SD!=‘MA’)Where 子句——LikeWhere 子句——Like 格式: [NOT] LIKE ‘匹配串’ [ESCAPE ‘换码字符’] %: 表示任意长度(≥0的任意字符 __: 表示单个的任意字符 ESCAPE ‘换码字符’ : 匹配串中‘换码字符’(转义符)之后的字(%,__)，被定义为普通字符(不作通配符用)4) 字符匹配: null例：列出课程名称中带有‘_’的课号及课名。 Select cno，cname From Course Where cname LIKE ‘%\_%’ escape ‘\’ null例1：求课程名中有’数据库’的课程记录 SELECT * FROM C WHERE CN LIKE ‘%数据库%’； 例2：求倒数第三、四个汉字为’系统’的课程名 SELECT CN FROM C WHERE CN LIKE ‘%系统_ _ _ _’；null 例3：求以‘DATA_BASE’开头且倒数第五个字符为’S’的课程名 SELECT CN FROM C WHERE CN LIKE ‘DATA\_BASE%S_ _ _ _’ ESCAPE’\’；5) 涉及空值的查询: 5) 涉及空值的查询: 例：查缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno 　 FROM SC WHERE Grade IS NULL; Group By子句Group By子句 将查询结果集按某一列或多列的值分组，值相等的为一组，一个分组以一个元组的形式出现 只有出现在Group By子句中的属性，才可出现在Select子句中 例：统计各系学生的人数。 Select sdept，count(*) as stu_count From Student Group By sdept 3、分组与组函数null组函数: 组函数的使用格式： COUNT([DISTINCT|ALL] *|列名) SUM([DISTINCT|ALL] 列名) AVG([DISTINCT|ALL] 列名) MAX([DISTINCT|ALL] 列名) MIN([DISTINCT|ALL] 列名) null组函数可用于SELECT子句中的目标列表中，或在HAVING子句的分组表达式中用作条件。 对分出的每一组用HAVING进行筛选,筛选条件要用到组函数。 null 例1: 查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 例2: 查男女生人数 SELECT sex , COUNT(SNO) COUNTSNO FROM stu GROUP BY sex; null例3：求选修了课程的学生人数 SELECT COUNT(DISTINCT S#) FROM SC；   例4：求选修各门课的人数及平均成绩 SELECT C#, COUNT(S#), AVG(ALL GR) FROM SC GROUP BY C#； null例5：求选修课程在5门以上且都及格的学生号及总平均分   SELECT S#, AVG(ALL GR) FROM SC GROUP BY S# HAVING COUNT(C#)>5 AND MIN(GR)≥60； nullHaving 与 Where的区别Having 与 Where的区别Where 哪些元组被选择参加运算，作用于关系中的元组 Having 决定哪些分组符合要求，作用于分组null4、排序 用ORDER BY子句对查询结果按照一个或多个列的值进行升/降排列输出 升序为ASC；降序为DESC 空值将作为最大值排序 null例1：对选修’C5’课程的学生按成绩降序排列，同分数者按学号升序排列 SELECT S#, GR FROM SC WHERE C#=’C5’ ORDER BY GR DESC，S# ASC；   null例2：求每个学生的总分并按总分降序排列,学号升序 SELECT S#，SUM(ALL GR) FROM SC GROUP BY S# ORDER BY 2 DESC，S# ASC； null求95级学生 中选修课程 在 5 门以上 且都及格的 学生号及总 平均分，并 按平均成绩 排序。 nullπA1, ..., An(p(R1×...×Rm)) Select A1, A2, ..., An From R1, R2, ..., Rm Where P一、单表查询 1. 选择表中的列 2. 选择表中的行 3. 分组与组函数 4. 排 序主要内容主要内容二、连接查询多表连接查询、单表连接查询、 外连接查询、复合条件连接查询.三、嵌套查询一、单表查询返回单个值的子查询 返回一组值的子查询 多重子查询二、连接查询二、连接查询多表连接查询、单表连接查询、 外连接查询、复合条件连接查询.* 示范表 EMP(EMPNO,ENAME,MGR,JOB,SAL,DEPTNO) DEPT(DEPTNO,DNAME,CITY) SALGRADE(GRADE,LOSAL,HISAL)1、多表连接：1、多表连接：连接条件一： [表名1.] 列名1 比较运算符 [表名2.]列名2 连接条件二： [表名1.]列名1 BETWEEN [表名2.]列名2 AND [表名2.]列名3■ 连接条件中的列名称为连接字段，其各 字段应是可比的。执行过程：执行过程： 在表1中找到第一个元组，然后从头开始扫描表2，查找到 满足条件的元组即进行串接并存入结果表中；再继续扫描表2， 依次类推，直到表2末尾。再从表1中取第二个元组，重复上述 的操作，直到表1中的元组全部处理完毕。例题例题SELECT ENAME, SAL, GRADE FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL；SELECT S.S#，SN，CN，G FROM S，C，SC WHERE S.S#=SC.S# AND C.C#=SC.C#；例2：求每个职工的工资级别例1：求选课情况，要求输出学号、姓名、课 程名与成绩nullnull2、单表连接 ：2、单表连接 ： 用表别名把一个表定义为两个不同的表进行连接。SELECT W.ENAME，W.SAL，M.ENAME，M.SAL FROM EMP W ，EMP M WHERE W.MGR = M.EMPNO AND W.SAL > M.SAL；例1：求比本部门职工工资低的部门负责人例题例题SELECT FIRST.C#，THIRD.CNAME FROM C FIRST， C SECOND , C THIRD WHERE FIRST.PC# = SECOND.C# AND SECOND.PC# = THIRD.C#例2：求每门课的间接先修课名（即先修课的 先修课）3、外连接 ：3、外连接 ： 在连接条件的某侧加上(＊) 或(+)，表示该侧所对应的表中可形成一个 各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条 件的元组进行连接。外连接符(＊)或（+）出现在左侧称为右外连接、出现 在右侧称为左连接、两侧都出现的称为全外连接。SELECT DNAME，ENAME，JOB FROM DEPT，EMP WHERE DEPT.DEPTNO = EMP.DEPTNO(*)； 例1：求各部门名及职工名、工种，要求输出 无职工的部门（职工以空值出现）null例题例题SELECT DNAME，ENAME，JOB FROM DEPT，EMP WHERE DEPT.DEPTNO(*) = EMP.DEPTNO；例2：求各部门名及职工名、工种，要求输出 未分配部门的职工（部门以空值出现）null例题例题SELECT DNAME，ENAME，JOB FROM DEPT，EMP WHERE DEPT.DEPTNO(*)=EMP.DEPTNO(*)；例3：求各部门名及职工名、工种，要求输出 无职工的部门和未分配部门的职工null4、复合条件连接 ：4、复合条件连接 ： WHERE子句中除了连接条件，还有其它限制条件。SELECT SN, GR FROM S, SC WHERE S.S#=SC.S# AND SC.C#=’C6’ AND SC.GR>90；例1：求选修’C6’课程且成绩超过90分的学生名 与成绩 连接条件限制条件三、嵌套查询 三、嵌套查询 在SELECT … FROM … WHERE语句结构的 WHERE子句中可嵌入一个SELECT语句块 其上层查询称为外层查询或父查询 其下层查询称为内层查询或子查询 SQL语言允许使用多重嵌套查询 在子查询中不允许使用ORDER BY子句 嵌套查询的实现一般是从里到外，即先进行 子查询，再把其结果用于父查询作为条件1、返回单个值的子查询 ：1、返回单个值的子查询 ：方法一： SELECT SN, SA FROM S WHERE SD = ( SELECT SD FROM S WHERE SN = ‘刘力’）；例1：求与‘刘力’同一个系的学生名，年龄1、返回单个值的子查询 ：1、返回单个值的子查询 ：方法二： SELECT FIRST.SN, FIRST.SA FROM S FIRST , S SECOND WHERE FIRST.SD = SECOND.SD AND SECOND.SN = ‘刘力’；例1：求与‘刘力’同一个系的学生名，年龄2、返回一组值的子查询：2、返回一组值的子查询：SELECT * FROM S WHERE S# IN ( SELECT S# FROM SC WHERE C#=‘C6’ AND GR＞90 )；例1：求选修‘C6‘课程且成绩超过90分的学生方法一：例题例题SELECT S.* FROM S，SC WHERE S.S#=SC.S# AND GR＞90 AND C#=’C6’；例1：求选修‘C6‘课程且成绩超过90分的学生方法二（ 连接查询 ）：例题例题方法一： SELECT * FROM S WHERE SD!=’CS’ AND SA ＜ ANY ( SELECT SA FROM S WHERE SD=‘CS’)；例2：求比计算机系中某一学生年龄小的其他 系的学生例题例题方法二： SELECT * FROM S WHERE SD!=’CS’ AND SA ＜ ( SELECT MAX(SA) FROM S WHERE SD=‘CS’)；例2：求比计算机系中某一学生年龄小的其他 系的学生3、多重子查询 ：3、多重子查询 ：例1：求10#部门中工种与‘SALES’部门中任一工 种相同的职工例题例题 SELECT ENAME，JOB，SAL FROM EMP WHERE DEPTNO = ‘10’ AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = （SELECT DEPTNO FROM DEPT WHERE DNAME=‘SALES’))； 例题例题 SELECT * FROM EMP WHERE SAL ＞= (SELECT MIN(SAL) FROM EMP WHERE ENAME IN (‘CLARK’，‘MILLER’)) AND SAL ＜= (SELECT MAX(SAL) FROM EMP WHERE ENAME IN（‘CLARK’,‘MILLER’)； 例2：求工资介于‘CLARK’与‘MILLER’两个之间的职工例题例题 SELECT DEPTNO,ENAME,JOB,SAL FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE CITY = ’NEW YORK’))； 例3：求工种与在纽约的职工的工种相同的职工总 结总 结连接查询多表连接查询、单表连接查询、 外连接查询、复合条件连接查询嵌套查询返回单个值的子查询 返回一组值的子查询 多重子查询四、带有EXISTS的相关子查询 四、带有EXISTS的相关子查询 不相关子查询：子查询的查询条件不依赖于 父查询的称为不相关子查询。 相关子查询：子查询的查询条件依赖于外层父 查询的某个属性值的称为相关子查询 (Correlated Subquery),带EXISTS 的子查询就 是相关子查询 EXISTS表示存在量词 带有EXISTS的子查询不返回任何记录的数据， 只返回逻辑值 ‘ True ’ 或 ‘ False ’例题例题不相关子查询： SELECT SN FROM S WHERE S# IN ( SELECT S# FROM SC WHERE C# = ‘C1’ )； 例1：求所有选修了‘C1’课程的学生名。例题例题相关子查询： SELECT SN FROM S WHERE EXISTS ( SELECT * FROM SC WHERE S.S#=SC.S# AND C#=‘C1’ )； 例1：求所有选修了‘C1’课程的学生名。执行过程：执行过程： 先在外层查询中取S表的第一个元组（记录），用该记录的 相关的属性值（在内层WHERE子句中给定的）处理内层查询，若 外层的WHERE子句返回‘TRUE’值，则此元组送入结果的表中。然 后再取下一个元组；重复上述过程直到外层表的记录全部遍历 一次为止。 说明： 说明： 不关心子查询的具体内容，因此用 SELECT * Exists + 子查询用来判断该子查询是否返回元组 当子查询的结果集非空时，Exists 为 ‘True’ 当子查询的结果集为空时，Exists 为 ‘False’ NOT EXISTS ：若子查询结果为空，返回‘TRUE’值， 否则返回 ‘FALSE’例题例题 SELECT SN FROM S WHERE NOT EXISTS ( SELECT * FROM C WHERE S.S#=SC.S# AND C#=‘C1’)；例2：列出没有选C1课程的学生的学号、姓名例题例题 SELECT SN FROM S WHERE NOT EXISTS ( SELECT * FROM C WHERE NOT EXISTS ( SELECT * FROM SC WHERE S.S#=SC.S# AND C.C#=SC.C#))；例3：查询选修了所有课程的学生的姓名这门课他没选这样的课是不存在的例题例题例4：查询至少选修了‘S1’所选的全部课程的学生名P: S1选修了课程CX Q: 学生SX选修了课程CX 则: ∀CX(P→Q)表示学生SX选修了‘S1’所选的全部课程 (P→Q)== ⌉P ∨ Q ∀X(P→Q)== ⌉(∃X (⌉(P→Q))) == ⌉(∃X (⌉(⌉ P ∨ Q))) == ⌉(∃X (P ∧ ⌉ Q)) 即： ∀X(P→Q)== ⌉(∃X (P ∧ ⌉ Q))nullSELECT SN FROM S WHERE NOT EXISTS ⌉(∃X ( SELECT * FROM SC SCX S1选课 WHERE SCX.S#=‘S1’ P AND NOT EXISTS ∧ ⌉ ( SELECT * FROM SC SCY WHERE S.S#=SCY.S# AND SCX.C#=SCY.C#))； QFROM语句中使用子查询(SQL2)FROM语句中使用子查询(SQL2)在FROM语句中使用子查询，对查询结果定义表名及列名 例：求平均成绩超过80分的学号及平均成绩 SELECT S#，AVG_G FROM (SELECT S#,AVG(ALL G) FROM SC GROUP BY S# ) AS RA(S#,AVG_G) WHERE AVG_G>80;SELECT S#,AVG(ALL G) FROM SC GROUP BY S# HAVING AVG(ALL G)>80 五、SQL的集合操作 五、SQL的集合操作 属性个数必须一致 对应的类型必须一致 属性名无关 最终结果集采用第一个结果的属性名 缺省为自动去除重复元组 除非显式说明ALL Order By放在整个语句的最后SQL的集合操作——并SQL的集合操作——并例1：查询计算机系的学生或者年龄不大于19岁的学生， 并按年龄倒排序。SELECT * FROM S WHERE SD=‘CS’ UNION SELECT * FROM S WHERE AGE<=19 ORDER BY AGE DESCSQL的集合操作——交SQL的集合操作——交例2：查询计算机系的学生并且年龄不大于19岁的学生， 并按年龄倒排序。(SELECT * FROM S WHERE SD=‘CS’) INTERSEC (SELECT * FROM S WHERE AGE<=19) ORDER BY AGE DESCSQL的集合操作——差SQL的集合操作——差例3：查询选修课程1但没有选修课程2的学生。SELECT SN,SD FROM S WHERE S# IN (( SELECT S# FROM SC WHERE C#='1‘) EXCEPT ( SELECT S# FROM SC WHERE C#='2')) §3.3 更新语句§3.3 更新语句一、插入操作 INSERT二、删除操作 DELETE 三、修改操作 UPDATE 一、插入操作一、插入操作1、插入单个元组：格式：INSERT INTO 表名[(列名1,…)] VALUES (列值1,…)； 插入一已知元组的全部列值 插入一已知元组的部分列值 插入一已知元组的全部列值 插入一已知元组的全部列值 INSERT INTO S VALUES(‘990021’,‘陈冬’,18,‘CS’,‘男’)；例1：新增一个学生信息 INSERT INTO SC（S#，C#） VALUES (‘9807121’,‘C175’)；2、插入子查询的结果 ：2、插入子查询的结果 ：格式：INSERT INTO 表名 [(列名1,…)] (子查询)；例1：设关系S_G(S#,AVG_G),把平均成绩大于 80的男生的学号及平均成绩存入S_G中。 一、插入操作例题例题INSERT INTO S-G(S#，AVG-G) (SELECT S#， AVG(ALL GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=‘男’) GROUP BY S# HAVING AVG(ALL GRADE)>80)；二、删除操作二、删除操作格式： DELETE FROM 表名 [WHERE 条件]； 只能对整个元组操作，不能只删除某些属性上 的值 只能对一个关系起作用，若要从多个关系中删 除元组，则必须对每个关系分别执行删除命令 从关系 r 中删除满足P的元组，只是删除数据， 而不是定义1、删除单个元组 ：1、删除单个元组 ：例1：删除学号为‘95019’的学生。 二、删除操作DELETE FROM SC WHERE S# = '95019';DELETE FROM S WHERE S# = '95019';2、删除多个元组 ：2、删除多个元组 ：例2：删除选课但无成绩的学生的选课信息二、删除操作DELETE FROM SC WHERE GRADE IS NULL;DELETE FROM SC 清空SC表3、带子查询的删除语句：3、带子查询的删除语句：例3：删除选修‘C4’且成绩小于该课程的平均成绩的记录二、删除操作 DELETE FROM SC WHERE C#=‘C4’ AND G < (SELECT AVG(ALL GRADE) FROM SC WHERE C#=‘C4’）；三、修改操作三、修改操作格式1：UPDATE 表名 [别名] SET 列名 = 表达式，… [WHERE 条件]； 格式2：UPDATE 表名 [别名] SET (列名,…) = (子查询) [WHERE 条件]；1、修改单个元组的值 ：1、修改单个元组的值 ：例1：名为MARTIN的职工的工种改为MANAGER， 工资增加收入20%，三、修改操作 UPDATE EMP SET JOB=‘MANAGER’，SAL= SAL*1.2 WHERE NAME = ‘MARTIN’； 2、修改多个元组的值 ：2、修改多个元组的值 ：例2：将所有学生的年龄增加1岁三、修改操作UPDATE S SET SA=SA+1; 3、带子查询的修改语句：3、带子查询的修改语句：例3：工种为SALESMEN的职工的工资改为工种平均 工资的110%，佣金为最低值。三、修改操作UPDATE EMP SET (SAL，COMM)= (SELECT 1.1*AVG(ALL SAL) , MIN(COMM) FROM EMP WHERE JOB=‘SALESMEN’) WHERE JOB=‘SALESMEN’； 3、带子查询的修改语句：3、带子查询的修改语句：例4：计算机系全体学生成绩上浮15%三、修改操作UPDATE SC SET GRADE=GRADE*1.15， WHERE S# IN (SELECT S# FROM S WHERE SD=‘CS’）；数据库上机实验操作说明 数据库上机实验操作说明 1、 win2000 登陆 2、在开始菜单/程序-----Microsoft SQL 7.0 / Query Analyzer SQL SERVER : f3-svr Login Name : USER2 Password : USER2 connection information 选择第二个选项------SQL SERVER 然后按OK 3、STU1 、WORK1数据库中， S , C , SC ; DEPT , EMP, SALGRADE nullnull选择数据库null 数据库查询 SELECT 数据定义 CREATE，DROP ，ALTER 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKESQL功能 动词√√§3.4 数据定义§3.4 数据定义一、定义基本表 CREATE TABLE二、删除基本表 DROP TABLE三、修改基本表 ALTER TABLE 四、定义索引 CREATE INDEX五、删除索引 DROP INDEX一、定义基本表一、定义基本表格式1： CREATE TABLE 表名 (列名 类型(长度) [NOT NULL] [DEFAULT {常量|系统变量|NULL}] [列约束],… …) [PRIMARY KEY (列名,…)] [FOREIGN KEY (列名,…) REFERENCES 表名(列名,…)] [CHECK 条件]； 建立一个新表，表中无记录一、定义基本表一、定义基本表格式2： CREATE TABLE 表名 [(列名 [NOT NULL],…)] [PRIMARY KEY (列名,…)] [FOREIGN KEY (列名,…) REFERENCES 表名1(列名,…)] AS 子查询 建立一个带有子查询结果记录的新表.例题例题 CREATE TABLE S ( S# CHAR(6) NOT NULL, SNAME CHAR(8) NOT NULL, SAGE SMALLINT, SD CHAR(10), SEX CHAR(2) DEFAULT ‘男’ CHECK((SEX='男') OR (SEX='女'))) PRIMARY KEY (S#)；例：建立学生S、课程C、选课SC三个表S表：例题例题 CREATE TABLE C (C# CHAR(6) NOT NULL, CNAME CHAR(30) NOT NULL, TNAME CHAR(8), PC# CHAR(6)) PRIMARY KEY (C#)；例：建立学生S、课程C、选课SC三个表C表：例题例题CREATE TABLE SC (S# CHAR(6) NOT NULL, C# CHAR(6) NOT NULL, GR SMALLINT DEFAULT NULL) PRIMARY KEY (S#,C#) FOREIGN KEY (S#) REFERENCES S(S#) FOREIGN KEY (C#) REFERENCES C(C#) CHECK (GR IS NULL) OR (GR BETWEEN O AND 100)；例：建立学生S、课程C、选课SC三个表SC表：例题例题例：设关系S_G(S#,AVG_G),把平均成绩大于 80的男生的学号及平均成绩存入S_G中。 nullCREATE TABLE S_G (S# CHAR(6) NOT NULL, AVG_G SMALLINT DEFAULT NULL) AS (SELECT S#， AVG(ALL GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=‘男’) GROUP BY S# HAVING AVG(ALL GRADE)>80)二、删除基本表二、删除基本表格式： DROP TABLE 表名 [CASCADE|RESTRICT]； 删除一个表，及与该表相关的索引、视图、码 和外部码。 CASCADE 连同引用该表的视图、完整 性约束一起自动撤消 RESTRICT无引用时，才可撤消三、修改基本表三、修改基本表 改变表名 增加列 改变列的数据类型 删除列的约束 删除列 改变列名 基本表的修改操作：三、修改基本表三、修改基本表格式：ALTER TABLE 表名 [ADD 新列名 类型(长度)[NOT NULL] [列约束],…] [MODIFY 旧列名 类型(长度)] [DROP 列约束] [RENAME 旧表名 TO 新表名] 增加新列，修改列，删除列的完整性约束； 改变表名及列名。 例题例题例1：在S表中增加一个入学时间，为日期型 ALETR TABLE S ADD SCOME DATE； 例2：把SA列的类型改为半字长整数 ALETR TABLE S MODIFY SA SMALLINT； 例3：删除对SN列的唯一约束 ALETR TABLE S DROP UNIQUE(SN)；删除列、改变列名删除列、改变列名 对删除列、改变列名的操作，可采取如下 步骤实现： CREATE TABLE … AS (子查询)建一新表 删除旧表 改变表名四、定义索引四、定义索引格式： CREATE[UNIQUE][CLUSTER] INDEX 索引名 ON 表名 (列名[ASC|DESC],…)； 对指定的表的列建立索引. UNIQUE 表示索引值唯一 CLUSTER 表示索引是聚簇索引 索引一旦建立，交由系统使用和维护例题例题 CREATE UNIQUE INDEX SIDX ON S(S# ASC)； CREATE CLUSTER INDEX SNCDX ON S(SNAME ASC)； CREATE UNIQUE INDEX CIDX ON C(C# ASC)； CREATE UNIQUE INDEX SCIDX ON SC(S# ASC,C# DESC)； 五、删除索引五、删除索引格式： DROP INDEX 索引名； 例： DROP INDEX CIDX；§3.5 数据控制§3.5 数据控制一、授 权 GRANT二、收回权限 REVOKE一、授 权一、授 权1、DATABASE：1、DATABASE： GRANT CREATETAB ON DATABASE 数据库名 TO {用户名,…|PUBLIC} [WITH GRANT OPTION]； 授予用户建立表的权限 由DBA授权，只有CREATETAB一种权限 WITH GRANT OPTION允许用户转授权(权限传播) PUBLIC表示所有用户格式： 2、TABLE级的基本表2、TABLE级的基本表GRANT[{SELECT|INSERT|UPDATE|DELETE |ALTER|INDEX|ALL PRIVILEGES}] ON TABLE 表名 TO {用户名,…|PUBLIC} [WITH GRANT OPTION]； 授予用户对表的操作权限 由拥有该表的用户授权格式： 3、TABLE级的视图(VIEW)3、TABLE级的视图(VIEW)GRANT{SELECT|INSERT|UPDATE| DELETE|ALL PRIVILEGES} ON TABLE 视图名 TO {用户名,…|PUBLIC} [WITH GRANT OPTION]； 授予用户对视图的操作权限 由拥有该视图的用户授权格式： 4、TABLE级的列4、TABLE级的列GRANT{SELECT|INSERT|UPDATE|DELETE| ALL PRIVILEGES } (列名,…) ON TABLE {表名|视图名} TO {用户名,…|PUBLIC} [WITH GRANT OPTION]； 授予用户对列的操作权限 由拥有该表或视图的用户授权格式： 例题例题例1：GRANT CREATETAB ON DATABASE 教学系统 TO USER1； 例2：GRANT SELECT,INSERT ON TABLE SC TO USER1,USER2,USER5 WITH GARNT OPTION； 例3：GRANT ALL PRIVILEGES ON TABLE SC TO USER3,USER4,USER7；例题例题例4：把查询Stu表和修改学生学号的 权限授给用户U4GRANT UPDATE(Sno), SELECT ON TABLE Stu TO U4; 二、ORACLE授权机制二、ORACLE授权机制在ORACLE 中对用户的授权分为 系统级、表级、视图级 null 1、系统级： 格式：GRANT [CONNECT[,RESOURCE[,DBA]]] TO {用户名,…|PUBLIC} [IDENTIFIED BY 口令，…]； 作用：授予用户对ORACLE系统的操作权限 CONNECT：允许用户登录数据库，在获得表级或视图级授权后可作相应操作，但不能建立表(CREATE TABLE) RESOURCE：允许建表 DBA：超级用户 null 2、表级：格式与标准SQL类似 3、表级：格式与标准SQL类似 例： GRANT CONNECT,RESOURCE TO XUNS； GRANT TO USER6 IDENTIFIED BY QWE；三、收回授权三、收回授权 由DBA或授于DBA权限的用户对其他用户进行权限回收。 1、格式：REVOKE CREATETAB ON DATABASE 数据库名 FROM {用户名,…|PUBLIC}；   2、格式：REVOKE {权限列表} ON TABLE {表名|视图名} FROM {用户名,…|PUBLIC}；null3、格式： REVORE {权限列表} (列名,…) ON TABLE {表名|视图名} FROM {用户名,…|PUBLIC}；   4、格式： REVOKE (DBA[,RESOURCE [,CONNECT]]) FROM {用户名,…|PUBLIC}； 例题例题例1 把用户U4修改