《工学数据库系统概论第四版第3章关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《工学数据库系统概论第四版第3章关系数据库标准语言SQL（82页珍藏版）》请在金锄头文库上搜索。
1、An Introduction to Database System洛阳理工学院计算机与信息工程系洛阳理工学院计算机与信息工程系数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL2024/9/17An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述3.2 学生-课程数据库3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.6 小结2024/9/17An Introduction to D
2、atabase System3.1 SQL概述概述nSQL的特点n1. 综合统一n2. 高度非过程化n3. 面向集合的操作方式n4. 以同一种语法结构提供两种使用方法n5. 语言简洁，易学易用2024/9/17An Introduction to Database System5. 语言简捷，易学易用语言简捷，易学易用2024/9/173.2 学生学生-课程数据库课程数据库n由三个表构成：n1 学生表Student（Sno,Sname,Ssex,Sage,Sdept)n2 课程表Course(Cno,Cname,Cpno,Ccredit)n3 学生选课表SC(Sno,Cno,Grade)202
3、4/9/17An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述3.2 学生-课程数据库3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.6 小结2024/9/17An Introduction to Database System3.3 数数 据据 定定 义义 2024/9/17nCreate database data_namenOn(filename=“”nsize=n)nLog on(nFilename=“”nsize=)An Introduction to Database Sys
4、tem2024/9/17An Introduction to Database System3.3.1 定义定义语句格式语句格式CREATE TABLE （ ， ， ）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件 2024/9/17An Introduction to Database System例题例题 例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABL
5、E Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15)； 2024/9/17An Introduction to Database System定义基本表（续）定义基本表（续）n常用完整性约束n主码约束： PRIMARY KEYn唯一性约束：UNIQUEn非空值约束：NOT NULLn参照完整性约束PRIMARY KEY与 UNIQUE的区别？2024/9/17An Introduction to Database System例题例题 （续）（
6、续）n例2 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。nCREATE TABLE SC(n Sno CHAR(5) ,n Cno CHAR(3) , n Grade int,n Primary key (Sno, Cno);2024/9/17An Introduction to Database System例题例题 （续）（续）2024/9/17An Introduction to Database System三、删除基本表三、删除基本表 DROP TABLE ; 基本表删除 数据、表上的索引都删除 表上的视图往往仍然
7、保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述 （标准中没有，认为表建立后就永久存在）2024/9/17An Introduction to Database System例题例题例5 删除Student表 DROP TABLEStudent ;2024/9/17An Introduction to Database System二、修改基本表二、修改基本表ALTER TABLE ADD 完整性约束 DROP MODIFY ；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nMODIFY子句：用于修改列名和数据
8、类型2024/9/17An Introduction to Database System例题例题 例2 向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE；n不论基本表中原来是否已有数据，新增加的列一律为空值。2024/9/17An Introduction to Database System语句格式（续）语句格式（续）n删除属性列 直接/间接删除n把表中要保留的列及其内容复制到一个新表中n删除原表n再将新表重命名为原表名直接删除属性列:(新)例：ALTER TABLE Student Drop Scome；20
9、24/9/17An Introduction to Database System 例3 将年龄的数据类型改为半字长整数。 ALTER TABLE Student MODIFY Sage SMALLINT；n注：修改原有的列定义有可能会破坏已有数据2024/9/17An Introduction to Database System例题例题 例4 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname)； 2024/9/17An Introduction to Database System3.3.2 建立与删除索引建立与删除索引 n建立索引
10、是加快查询速度的有效手段n建立索引nDBA或表的属主（即建立表的人）根据需要建立n有些DBMS自动建立以下列上的索引n PRIMARY KEYn UNIQUEn维护索引n DBMS自动完成n使用索引n DBMS自动选择是否使用索引以及使用哪些索引2024/9/17An Introduction to Database System一、建立索引一、建立索引 n语句格式CREATE UNIQUE CLUSTER INDEX ON (, )；n用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUN
11、IQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引2024/9/17An Introduction to Database System例题例题 例6 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno)；CREATE UNIQUE INDEX Coucno ON Course(Cno)；CREATE UNIQUE INDEX S
12、Cno ON SC(Sno ASC，Cno DESC)； 2024/9/17An Introduction to Database System建立索引建立索引 （续）（续）n唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束2024/9/17An Introduction to Database System建立索引建立索引 （续）（续）n聚簇索引n建建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇簇属属性性值值的的升升序序或
13、或降降序序存存放放。也也即即聚聚簇簇索索引引的的索索引引项项顺顺序序与与表表中中记记录录的的物物理理顺序一致顺序一致例：CREATE CLUSTER INDEX Stusname ON Student(Sname)；在在Student表的表的Sname（姓名）列上建立一个聚簇索引，而（姓名）列上建立一个聚簇索引，而且且Student表中的记录将按照表中的记录将按照Sname值的升序存放值的升序存放 2024/9/17An Introduction to Database System建立索引建立索引 （续）（续）n在一个基本表上最多只能建立一个聚簇索引n聚簇索引的用途：对于某些类型的查询，可以
14、提高查询效率n聚簇索引的适用范围n 很少对基表进行增删操作n 很少对其中的变长列进行修改操作 2024/9/17An Introduction to Database System二、删除索引二、删除索引 DROP INDEX ；n删除索引时，系统会从数据字典中删去有关该索引的描述。例7 删除Student表的Stusname索引。DROP INDEX Stusname；2024/9/17An Introduction to Database System3.4 查查 询询 3.4.1 概述3.4.2 单表查询3.4.3 连接查询3.4.4 嵌套查询3.4.5 集合查询3.4.6 小结 202
15、4/9/17An Introduction to Database System3.4.1 概述概述 n语句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；2024/9/17An Introduction to Database System语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件n GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：筛选
16、出只有满足指定条件的组nORDER BY子句：对查询结果表按指定列值的升序或降序排序 2024/9/17An Introduction to Database System示例数据库示例数据库 学生-课程数据库n学生表：Student(Sno，Sname，Ssex，Sage，Sdept)n课程表：Course(Cno，Cname，Cpno，Ccredit)n 学生选课表：SC(Sno，Cno，Grade) 2024/9/17An Introduction to Database System3.4 查查 询询 3.4.1 概述3.4.2 单表查询3.4.3 连接查询3.4.4 嵌套查询3.4.
17、5 集合查询3.4.6 小结 2024/9/17An Introduction to Database System3.4.2 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组 2024/9/17An Introduction to Database System查询指定列查询指定列例1 查询全体学生的学号与姓名。SELECT Sno，SnameFROM Student；例2 查询全体学生的姓名、学号、所在系。SELECT Sname，Sno，SdeptFROM Student；2024/9/
18、17An Introduction to Database System查询全部列查询全部列例3 查询全体学生的详细记录。SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或SELECT *FROM Student； 2024/9/17An Introduction to Database System3. 查询经过计算的值查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等 2024/9/17An Introduction to Database System3. 查询经过计算的值查询经过计算的值例4 查全体学生的
19、姓名及其出生年份。SELECT Sname，2000-SageFROM Student；输出结果： Sname 2000-Sage - - 李勇 1976 刘晨 1977 王名 1978 张立 1978 2024/9/17An Introduction to Database System3. 查询经过计算的值查询经过计算的值例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname，Year of Birth: ，2000-Sage， ISLOWER(Sdept)FROM Student； 2024/9/17An Introduction to Data
20、base System例题（续）例题（续）输出结果： Sname Year of Birth: 2000-Sage ISLOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is 2024/9/17An Introduction to Database System例例 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题SELECT Sname NAME，Year of Birth: BIRTH，
21、 2000-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENTFROM Student；输出结果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇 Year of Birth: 1976 cs 刘晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is2024/9/17An Introduction to Database System二、选择表中的若干元组二、选择表中的若干元组 n消除取值重复的行n查询满足条件的元组 2024/9/1
22、7An Introduction to Database System1. 消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 802024/9/17An Introduction to Database SystemALL 与与 DISTINCT 例6 查询选修了课程的学生学号。(1) SELECT Sno FROM SC;或(默认 ALL) SELECT ALL Sno FROM SC;结果： Sn
23、o - 95001 95001 95001 95002 95002 2024/9/17An Introduction to Database System例题（续）例题（续）(2) SELECT DISTINCT Sno FROM SC;结果： Sno - 95001 95002 2024/9/17An Introduction to Database System例题（续）例题（续）n注意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECT DISTINCT Cno，DISTINCT GradeFROM SC;正确的写法 SELECT DISTINCT
24、 Cno，Grade FROM SC;2024/9/17An Introduction to Database System2.查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件2024/9/17An Introduction to Database System(1) 比较大小比较大小在WHERE子句的中使用比较运算符n=，=，=，!= 或 ，!，!， n逻辑运算符NOT + 比较运算符例8 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage = 20； 2024/9/17An In
25、troduction to Database System(2) 确定范围确定范围n使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 2024/9/17An Introduction to Database System例题（续）例题（续）例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname，Sdept，SageFROM StudentWH
26、ERE Sage NOT BETWEEN 20 AND 23； 2024/9/17An Introduction to Database System(3) 确定集合确定集合使用谓词 IN , NOT IN ：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );2024/9/17An Introduction to Database System(3) 确定集合确定集合例13查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名
27、和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );2024/9/17An Introduction to Database System(4) 字符串匹配字符串匹配n NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时， 可以用 = 运算符取代 LIKE 谓词 用 != 或 运算符取代 NOT LIKE 谓词2024/9/17An Introduction to Database System通配符通配符w% (百分号) 代表任意长度（长度可以为0）的
28、字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串_ (下横线) 代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串2024/9/17An Introduction to Database SystemESCAPE 短语：短语：n当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。2024/9/17An Introduction to Database System例题例题1) 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况。
29、SELECT * FROM Student WHERE Sno LIKE 95001；等价于： SELECT * FROM Student WHERE Sno = 95001；2024/9/17An Introduction to Database System例题（续）例题（续）2) 匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；2024/9/17An Introduction to Database System例题（续）例题（续）匹配模板为含通配符的字符
30、串（续）例16 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_；2024/9/17An Introduction to Database System例题（续）例题（续）匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为阳字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%；2024/9/17An Introduction to Database System例题（续）例题（续）匹配模板为含通配符的字符串（续）例18 查询所有
31、不姓刘的学生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 刘%；2024/9/17An Introduction to Database System例题（续）例题（续）3) 使用换码字符将通配符转义为普通字符 例19 查询DB_Design课程的课程号和学分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 2024/9/17An Introduction to Database System例题（续）例题（续）使用换码字符将通配符转义为
32、普通字符(续)例20 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；2024/9/17An Introduction to Database System(5) 涉及空值的查询涉及空值的查询n 使用谓词 IS NULL 或 IS NOT NULLn “IS NULL” 不能用 “= NULL” 代替例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Gr
33、ade IS NULL；2024/9/17An Introduction to Database System例题例题(续续) 例22 查所有有成绩的学生学号和课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；2024/9/17An Introduction to Database System(6) 多重条件查询多重条件查询用逻辑运算符AND和 OR来联结多个查询条件n AND的优先级高于ORn 可以用括号改变优先级可用来实现多种其他谓词n NOT INn NOT BETWEEN AND 2024/9/17An Introduction
34、to Database System例题例题例23 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=20 AND Sage=23；2024/9/17An Introduction to Database System三、对查询结果排序三、对查询结果排序 使用ORDER BY子句n 可以按一个或多个属性列排序n 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示 2024/9/17An Introduction to D
35、atabase System对查询结果排序（续）对查询结果排序（续） 例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 2024/9/17An Introduction to Database System查询结果查询结果 Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 552024/9/17An Introduction to Database S
36、ystem对查询结果排序（续）对查询结果排序（续） 例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC； 2024/9/17An Introduction to Database System四、使用集函数四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n计算总和SUM（DISTINCT|ALL ）n 计算平均值AVG（DISTINCT|ALL ）2024/9/17An Introduction
37、 to Database System使用集函数（续）使用集函数（续） 求最大值MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值2024/9/17An Introduction to Database System使用集函数使用集函数 （续）（续）例26 查询学生总人数。 SELECT COUNT(*) FROM Student；例27 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC；注：用DISTINCT以避免重复计算学
38、生人数2024/9/17An Introduction to Database System使用集函数使用集函数 （续）（续）例28 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ；例29 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHER Cno= 1 ； 2024/9/17An Introduction to Database System五、对查询结果分组五、对查询结果分组 使用GROUP BY子句分组 细化集函数的作用对象n 未对查询结果分组，集函数将作用于整个查询结果n 对查询结
39、果分组后，集函数将分别作用于每个组 2024/9/17An Introduction to Database System使用使用GROUP BY子句分组子句分组例30 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno； 结果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 482024/9/17An Introduction to Database System对查询结果分组对查询结果分组 （续）（续）nGROUP BY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为
40、一组n使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数 2024/9/17An Introduction to Database System使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； 2024/9/17An Introduction to Database System例题例题例32 查询有3门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(*) FRO
41、M SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3; 2024/9/17An Introduction to Database System使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。 2024/9/17An Introduction to Database System 下课了。下课了。休息一会儿。休息一会儿。2024/9/17
