关系数据库标准语言SQL学习培训模板课件.ppt

1、2022-11-81第三讲 关系数据库标准语言SQLSQL(Structured Query Language)3.1 SQL概述3.2 学生课程数据库 3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结2022-11-823.1 SQL概述pSQL的特点n1.综合统一n2.高度非过程化n3.面向集合的操作方式n4.以同一种语法结构提供两种使用方法n5.语言简洁，易学易用2022-11-835.语言简捷，易学易用2022-11-84 SQL语言支持的关系数据库的三级模式结构 基本关系（基本关系（Base Table）SQL语言支持数据库的三级模式结构语言支持数据库的三级模

2、式结构3.1.2 SQL语言的基本概念存储文件关系模式1关系模式2关系模式3关系模式4外模式1外模式2外模式3模式外模式内模式SQL应用1-SQL应用2-SQL应用3-SQL2022-11-85第三讲 关系数据库标准语言SQLp3.1 SQL概述p3.2 学生课程数据库p3.3 数据定义p3.4 数据查询p3.5 数据更新p3.6 视图p3.8 小结2022-11-863.2 学生课程数据库-学生学学 号号Sno姓姓 名名Sname性性 别别Ssex年年 龄龄Sage所所 在在 系系Sdept200215121李勇李勇男男20CS200215122刘晨刘晨女女19IS200215123王敏王敏

3、女女18MA200215125张立张立男男19IS(a)Student2022-11-873.2 学生课程数据库-课程(b)Course课程号课程号课程名课程名先行课先行课学分学分CnoCnameCpnoCcredit1数据库数据库542数学数学 23信息系统信息系统144操作系统操作系统635数据结构数据结构746数据处理数据处理 27PASCAL语言语言642022-11-883.2 学生课程数据库-选课(c)SC学学 号号课课 程程 号号成成 绩绩SnoCnoGrade200215121192200215121285200215121388200215122290200215122380

4、2022-11-89第三讲 关系数据库标准语言SQLp3.1 SQL概述p3.2 学生课程数据库 p3.3 数据定义p3.4 数据查询p3.5 数据更新p3.6 视图p3.8 小结2022-11-8103.3 数 据 定 义2022-11-8113.3.1 基本表的定义、删除与修改一、定义基本表一、定义基本表CREATE TABLE （，）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件 2022-11-812定义基本关系（续）p常用完整性约束n主码约束：PRIMARY KEYn唯一性约束：UNIQUEn非空值约

5、束：NOT NULLn默认值：DEFAULT n参照完整性约束PRIMARY KEY与 UNIQUE的区别？2022-11-813例题 例5 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATE TABLE Student (Sno CHAR(12)Primary key,Sname VARCHAR2(20)UNIQUE,Ssex CHAR(3)DEFAULT(男),Sage NUMBER(3),Sdept VARCHAR(20);2022-11-814例题 例

6、6 建立一个“课程”表Course。CREATE TABLE Course(Cno varchar2(6)Primary KEY,Cname varchar2(20),Cpno varchar2(6),Ccredit number(2）,FOREIGN KEY(Cpno)REFERENCES Course(Cno);2022-11-815例题（续）p例7 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATE TABLE SC(Sno CHAR(12),Cno varchar2(6),Grade number(3）DEFAU

7、LT(0),Primary key(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno);2022-11-816二、数据类型二、数据类型 p当用SQL语句定义表时，需要为表中的每一个字段设置一个数据类型，用来指定字段所存放的数据是数值、字符串、日期或是其它类型的数据。pORACLE 的数据类型有很多种，以下常用类型：nchar 字符型，最大长度2000B，缺省长度为1B nnchar 基于NLS国家字符集的字符型，最大长度2000B，缺省为1字符 nvarchar2 变长字

8、符型，最大长度4000B nnvarchar2 基于NLS国家字符集的字符型，其余同carchar2 varchar 同varchar2 2022-11-817pnumber(m,n)数值型。m为总位数，n为小数位数。总长度最大为38位 pdate 日期型。有效表示范围公元前4712年1月1日到公元4712年12月31日 plong 变长字符型，最大长度2GB，不支持对字符串内容进行搜索pblob 二进制大对象类型，最大长度4GBpclob 字符大对象类型，最大长度4GB 2022-11-818三、修改基本表三、修改基本表ALTER TABLE ADD 完整性约束 DROP ALTER COL

9、UMN ；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nALTER COLUMN子句：用于修改列名和数据类型2022-11-819例题 例8 向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Sentrance DATE;n不论基本表中原来是否已有数据，新增加的列一律为空值。ALTER TABLE Student Drop（Sentrance）2022-11-820 例9 将年龄的数据类型改为2位整数。ALTER TABLE Student modify Sage number(2)

10、n注：修改原有的列定义有可能会破坏已有数据2022-11-821例题 例10 增加课程名称必须取惟一值的约束条件 ALTER TABLE Course ADD UNIQUE(Cname)2022-11-822五、删除基本表五、删除基本表 DROP TABLE 基本表删除时，数据、表上的索引都删除，表上的视图往往仍然保留，但无法引用2022-11-823例题例11 删除Student表 DROP TABLEStudent;2022-11-8243.3.2 建立与删除索引 p建立索引是加快查询速度的有效手段p建立索引nDBA或表的属主（即建立表的人）根据需要建立n有些DBMS自动建立以下列上的索引

11、p PRIMARY KEYp UNIQUEp维护索引n DBMS自动完成 p使用索引n DBMS自动选择是否使用索引以及使用哪些索引2022-11-825一、建立索引 p语句格式CREATE UNIQUE CLUSTERED INDEX ON(,)；n用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTERED表示要建立的索引是聚簇索引2022-11-826例题 例14 为学生-课程数据库中的Student，Course二个表

12、建立索引。其中Student表按姓名升序建索引，Course表按课程名降序建唯一索引。CREATE INDEX Stusname ON Student(Sname ASC)CREATE UNIQUE INDEX Coursename ON Course(Cname desc)2022-11-827建立索引（续）p唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束2022-11-828二、删除索引 DROP INDEX；n删除索引时，系统会从数据字典中删去有关该索

13、引的描述。例15 删除Student表的Stusname索引。DROP INDEX Stusname；2022-11-8293.4 查 询 3.4.1 单表查询3.4.2 连接查询3.4.3 嵌套查询3.4.4 集合查询3.4.5 SELECT语句的一般格式2022-11-830概述 p语句格式SELECT ALL|DISTINCT ，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC ；2022-11-831语句格式nSELECT子句：指定要显示的属性列或表达式nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件n GROUP BY

14、子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：筛选出只有满足指定条件的组nORDER BY子句：对查询结果表按指定列值的升序或降序排序 2022-11-8323.4.1 单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组 2022-11-833一、选择表中的若干列p1.查询指定列p2.查询全部列p3.查询经过计算的值2022-11-8341.查询指定列例1 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student

15、例2 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student2022-11-8352.查询全部列例3 查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student 或SELECT *FROM Student 2022-11-8363.查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等 2022-11-8373.查询经过计算的值例4 查全体学生的姓名及其出生年份。SELECT Sname,2008-SageFROM Student 输出结果：Sname 200

16、8-Sage -李勇 1988 刘晨 1989 王敏 1990 张立 1989 2022-11-8383.查询经过计算的值例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname,Year of Birth:,2004-Sage,LOWER(Sdept)FROM Student2022-11-839例题（续）输出结果：Sname YearofBirth:2004-Sage ISLOWER(Sdept)-李勇 Year of Birth:1984 cs 刘晨 Year of Birth:1985 is 王名 Year of Birth:1986 ma 张立

17、 Year of Birth:1985 is 2022-11-840例5.1 使用列别名改变查询结果的列标题SELECT Sname NAME,Year of Birth:BIRTH,2000-Sage BIRTHDAY,LOWER(Sdept)DEPARTMENTFROM Student；输出结果：NAME BIRTH BIRTHDAY DEPARTMENT -李勇 Year of Birth:1984 cs 刘晨 Year of Birth:1985 is 王名 Year of Birth:1986 ma 张立 Year of Birth:1984 is2022-11-841练习-参考第二

18、章习题5中的4个表 1.查询所有零件的名称、颜色和重量。2.查询所有供应商名称、所在城市 3.查询所有工程名称和所在城市2022-11-842SELECT Pname,Color,WeightFROM PSELECT Pname 名称,Color 颜色,Weight 重量FROM PSELECT Pname as 名称,Color as 颜色,Weight as 重量FROM P2022-11-843二、选择表中的若干元组 p1.消除取值重复的行p2.查询满足条件的元组 2022-11-8441.消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cn

19、o Grade -200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 802022-11-845ALL 与 DISTINCT 例6 查询选修了课程的学生学号。(1)SELECT(ALL)Sno FROM SC 结果：Sno -200215121 200215121 200215121 200215122 2002151222022-11-846例题（续）(2)SELECT DISTINCT Sno FROM SC;结果：Sno -200215121 2002151222022-11-847例题（续）p注

20、意 DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法:SELECT DISTINCT Cno,DISTINCT GradeFROM SC;正确的写法:SELECT DISTINCT Cno,Grade FROM SC;2022-11-8482.查询满足条件的元组表表3.3 常常用用的的查查询询条条 件件查查 询询 条条 件件谓谓 词词比比 较较=，=，=，!=，!，!；NOT+上上述述比比较较运运算算符符确确定定范范围围BETWEEN AND，NOT BETW EEN AND确确定定集集合合IN，NOT IN字字符符匹匹配配LIKE，NOT LIKE空空 值值IS

21、 NULL，IS NOT NULL多多重重条条件件AND，ORWHERE子句常用的查询条件子句常用的查询条件2022-11-849(1)比较大小p比较运算符包括：=,=,=,!=,!,!2022-11-850(1)比较大小 例7 查询计算机科学系全体学生的名单SELECT Sno,Sname,Ssex FROM Student WHERE Sdept=CS2022-11-851(1)比较大小（续）例8 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,Sage FROM Student WHERE Sage 202022-11-852(1)比较大小（续）例9 查询考试成绩有

22、不及格的学生的学号SELECT Sno FROM SC WHERE Grade60 2022-11-853(2)确定范围p使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23 2022-11-854例题（续）例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN

23、 20 AND 23 2022-11-855(3)确定集合使用谓词 IN,NOT IN ：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept IN(IS,MA,CS)2022-11-856(3)确定集合(续)例13查询既不是信息系、数学系，也不是计算 机科学系的学生的姓名和性别。SELECT Sname,SsexFROM Student WHERE Sdept NOT IN(IS,MA,CS);2022-11-857(4)字符串匹配p NOT LIKE ESCAP

24、E ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时，可以用=运算符取代 LIKE 谓词 用!=或 运算符取代 NOT LIKE 谓词2022-11-858通配符w%(百分号)代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串_(下横线)代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串2022-11-859ESCAPE 短语：n当用户要查询的字符串本身就含有%或 _ 时，要使用ESCAPE 短语对通配符进行转义。2022-

25、11-860例题1)匹配模板为固定字符串 例14 查询学号为200215121的学生的详细情况。SELECT*FROM Student WHERE Sno LIKE 200215121；等价于：SELECT *FROM Student WHERE Sno=200215121；2022-11-861例题（续）2)匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%2022-11-862例题（续）匹配模板为含通配符的字符串（续）例16 查询姓“刘”且全名为二个汉字的学生的姓

26、名。SELECT Sname FROM Student WHERE Sname LIKE 刘_2022-11-863例题（续）匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为阳字的学生的姓名和学号。SELECT Sname,Sno FROM Student WHERE Sname LIKE _阳%2022-11-864例题（续）匹配模板为含通配符的字符串（续）例18 查询所有不姓刘的学生姓名。SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE 刘%2022-11-865例题（续）3)使用换码字符将通配符转义为普通字符 例1

27、9 查询DB_Design课程的课程号和学分。SELECT Cno,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 2022-11-866例题（续）使用换码字符将通配符转义为普通字符(续)例20 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SELECT *FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE 2022-11-867(5)涉及空值的查询n 使用谓词 IS NULL 或 IS NOT NULLn“IS NULL”不能用“=NULL”代替例21 某些学生选修课程后没有参加考试

28、，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL2022-11-868例题(续)例22 查所有有成绩的学生学号和课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL2022-11-869(6)多重条件查询用逻辑运算符AND和 OR来联结多个查询条件p AND的优先级高于ORp 可以用括号改变优先级可用来实现多种其他谓词p NOT INp NOT BETWEEN AND 2022-11-870例题例23 查询计算机系年龄在20岁以下的学生姓

29、名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage=20 AND Sage=232022-11-873练习-参考第二章习题5中的4个表p查询使用S1供应商所供应零件的工程号p查询使用S1或者S2供应商所供应零件的工程号p名称中包含“螺”字的零件编号和颜色2022-11-874SELECT distinct Jno FROM SPJ WHERE Sno=S1SELECT distinct Jno FROM SPJ WHERE Sno=S1 or Sno=S2 SELECT Pno,color FROM P WHERE Pname like%螺%

30、2022-11-875三、对查询结果排序 使用ORDER BY子句p 可以按一个或多个属性列排序p 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时pASC：排序列为空值的元组最后显示pDESC：排序列为空值的元组最先显示 2022-11-876对查询结果排序（续）例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno,Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC 2022-11-877对查询结果排序（续）例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELE

31、CT *FROM Student ORDER BY Sdept,Sage DESC 2022-11-878四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL*）统计元组的个数COUNT（DISTINCT|ALL）统计某列值的个数n计算总和SUM（DISTINCT|ALL）求某列值的总和n 计算平均值AVG（DISTINCT|ALL）求某列值的均值2022-11-879使用集函数（续）求最大值nMAX（DISTINCT|ALL）求某列值的最大值 求最小值nMIN（DISTINCT|ALL）求某列值的最小值DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消

32、重复值ALL为缺省值2022-11-880使用集函数（续）例26 查询学生总人数。SELECT COUNT(*)FROM Student 例27 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC注：用DISTINCT以避免重复计算学生人数2022-11-881使用集函数（续）例28 计算1号课程的学生平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=1；例29 查询选修1号课程的学生最高分数。SELECT MAX(Grade)FROM SC WHERE Cno=1；2022-11-882使用集函数（续）例30 查询学生2

33、00215012选修课程的总分 SELECT SUM(grade)FROM SC WHERE Sno=200215012 2022-11-883使用集函数（续）在集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值。2022-11-884参考S、P、J、SPJ关系模式p查询S1供应商供应的工程数量p查询S1供应商供应了几种零件p查询S1供应商供应P1零件数量p查询供应商平均信誉（status）值p查询零件最大重量p查询给J2工程供应零件的供应商数量p查询供应P3零件的供应商数量2022-11-885SELECT COUNT(distinct Jno)FROM SPJ WHERE S

34、no=S12022-11-886五、对查询结果分组 使用GROUP BY子句分组 细化集函数的作用对象n 未对查询结果分组，集函数将作用于整个查询结果n 对查询结果分组后，集函数将分别作用于每个组 2022-11-887使用GROUP BY子句分组例31 求各个课程号及相应的选课人数。SELECT Cno,COUNT(Sno)FROM SC GROUP BY Cno 结果 Cno COUNT(Sno)1 1 2 2 3 2 2022-11-888对查询结果分组（续）pGROUP BY子句的作用对象是查询的中间结果表p分组方法：按指定的一列或多列值分组，值相等的为一组p使用GROUP BY子句后

35、，SELECT子句的列名列表中只能出现分组属性和集函数 2022-11-889使用HAVING短语筛选最终输出结果例32 查询选修了2门以上课程的学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)2 2022-11-890例题例 查询有2门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno,COUNT(*)FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=2;2022-11-891使用HAVING短语筛选最终输出结果p只有满足HAVING短语指定条件的组才输

36、出pHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。2022-11-892练习p查询各供应商的编号及其供应工程的数量。p查询各工程使用了几种零件p查询各种零件的供应数量p查询各工程使用各种零件的数量p查询各工程使用零件的总数 2022-11-893练习(连接、分组)p统计各系的选课人数、课程门数。p统计每个学生学号、选课门数、平均成绩。p只显示上题中平均成绩大于80分的。p统计每个学生学号、姓名、选课门数、平均成绩。p统计各种颜色零件的使用数量。p统计各供应商编号、名称和供应工程数量。