关系数据库标准语言SQL.ppt

《关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL.ppt（93页珍藏版）》请在三一办公上搜索。

1、2023/4/25,1,第三讲 关系数据库标准语言SQL,SQL(Structured Query Language)3.1 SQL概述3.2 学生课程数据库 3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结,2023/4/25,2,3.1 SQL概述,SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用,2023/4/25,3,5.语言简捷，易学易用,2023/4/25,4,SQL语言支持的关系数据库的三级模式结构,基本关系（Base Table）SQL语言支持数据库的三级模式结构,3.1.2 SQL

2、语言的基本概念,存储文件,关系模式1,关系模式2,关系模式3,关系模式4,外模式1,外模式2,外模式3,模式,外模式,内模式,SQL,应用1-SQL,应用2-SQL,应用3-SQL,2023/4/25,5,第三讲 关系数据库标准语言SQL,3.1 SQL概述3.2 学生课程数据库3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结,2023/4/25,6,3.2 学生课程数据库-学生,(a),Student,2023/4/25,7,3.2 学生课程数据库-课程,(b),Course,2023/4/25,8,3.2 学生课程数据库-选课,(c),SC,2023/4/25,9,

3、第三讲 关系数据库标准语言SQL,3.1 SQL概述3.2 学生课程数据库 3.3 数据定义3.4 数据查询3.5 数据更新3.6 视图3.8 小结,2023/4/25,10,3.3 数 据 定 义,2023/4/25,11,3.3.1 基本表的定义、删除与修改,一、定义基本表CREATE TABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,2023/4/25,12,定义基本关系（续）,常用完整性约束主码约束：PRIMARY KEY唯一性约束：UNIQUE非空值约束：NOT NULL默认值：DEFAULT

4、参照完整性约束PRIMARY KEY与 UNIQUE的区别？,2023/4/25,13,例题,例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);,2023/4/25,14,例题,例6 建立一个“课程”表Course。C

5、REATE TABLE Course(Cno varchar2(6)Primary KEY,Cname varchar2(20),Cpno varchar2(6),Ccredit number(2）,FOREIGN KEY(Cpno)REFERENCES Course(Cno);,2023/4/25,15,例题（续）,例7 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATE TABLE SC(Sno CHAR(12),Cno varchar2(6),Grade number(3）DEFAULT(0),Primary ke

6、y(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno);,2023/4/25,16,二、数据类型,当用SQL语句定义表时，需要为表中的每一个字段设置一个数据类型，用来指定字段所存放的数据是数值、字符串、日期或是其它类型的数据。ORACLE 的数据类型有很多种，以下常用类型：char字符型，最大长度2000B，缺省长度为1Bnchar基于NLS国家字符集的字符型，最大长度2000B，缺省为1字符varchar2变长字符型，最大长度4000Bnvarchar2基于NLS国家字

7、符集的字符型，其余同carchar2varchar同varchar2,2023/4/25,17,number(m,n)数值型。m为总位数，n为小数位数。总长度最大为38位date日期型。有效表示范围公元前4712年1月1日到公元4712年12月31日long变长字符型，最大长度2GB，不支持对字符串内容进行搜索blob二进制大对象类型，最大长度4GBclob字符大对象类型，最大长度4GB,2023/4/25,18,三、修改基本表,ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性

8、约束条件ALTER COLUMN子句：用于修改列名和数据类型,2023/4/25,19,例题,例8 向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Sentrance DATE;不论基本表中原来是否已有数据，新增加的列一律为空值。ALTER TABLE Student Drop（Sentrance）,2023/4/25,20,例9 将年龄的数据类型改为2位整数。ALTER TABLE Student modify Sage number(2)注：修改原有的列定义有可能会破坏已有数据,2023/4/25,21,例题,例10 增加课程名称必

9、须取惟一值的约束条件 ALTER TABLE Course ADD UNIQUE(Cname),2023/4/25,22,五、删除基本表,DROP TABLE 基本表删除时，数据、表上的索引都删除，表上的视图往往仍然保留，但无法引用,2023/4/25,23,例题,例11 删除Student表 DROP TABLEStudent;,2023/4/25,24,3.3.2 建立与删除索引,建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE维护索引 DBMS自动完成使用索引 DBMS自动选择是否

10、使用索引以及使用哪些索引,2023/4/25,25,一、建立索引,语句格式CREATE UNIQUE CLUSTERED INDEX ON(,)；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTERED表示要建立的索引是聚簇索引,2023/4/25,26,例题,例14 为学生-课程数据库中的Student，Course二个表建立索引。其中Student表按姓名升序建索引，Course表按课程名降序建唯一索引。CREATE INDE

11、X Stusname ON Student(Sname ASC)CREATE UNIQUE INDEX Coursename ON Course(Cname desc),2023/4/25,27,建立索引（续）,唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,2023/4/25,28,二、删除索引,DROP INDEX；删除索引时，系统会从数据字典中删去有关该索引的描述。例15 删除Student表的Stusname索引。DROP INDEX Stusname；

12、,2023/4/25,29,3.4 查 询,3.4.1 单表查询3.4.2 连接查询3.4.3 嵌套查询3.4.4 集合查询3.4.5 SELECT语句的一般格式,2023/4/25,30,概述,语句格式SELECT ALL|DISTINCT，FROM，WHERE GROUP BY HAVING ORDER BY ASC|DESC；,2023/4/25,31,语句格式,SELECT子句：指定要显示的属性列或表达式FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAV

13、ING短语：筛选出只有满足指定条件的组ORDER BY子句：对查询结果表按指定列值的升序或降序排序,2023/4/25,32,3.4.1 单表查询,查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组,2023/4/25,33,一、选择表中的若干列,1.查询指定列2.查询全部列3.查询经过计算的值,2023/4/25,34,1.查询指定列,例1 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student例2 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFRO

14、M Student,2023/4/25,35,2.查询全部列,例3 查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student 或SELECT*FROM Student,2023/4/25,36,3.查询经过计算的值,SELECT子句的为表达式算术表达式字符串常量函数列别名等,2023/4/25,37,3.查询经过计算的值,例4 查全体学生的姓名及其出生年份。SELECT Sname,2008-SageFROM Student输出结果：Sname 2008-Sage-李勇 1988 刘晨 1989 王敏 1990 张立 1989,2023

15、/4/25,38,3.查询经过计算的值,例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname,Year of Birth:,2004-Sage,LOWER(Sdept)FROM Student,2023/4/25,39,例题（续）,输出结果：Sname YearofBirth:2004-Sage ISLOWER(Sdept)-李勇 Year of Birth:1984 cs 刘晨 Year of Birth:1985 is 王名 Year of Birth:1986 ma 张立 Year of Birth:1985 is,2023/4/25,40,例

16、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 is,2023/4/25,41,练习-参考第二章习题5中的4个表,1.查询所有零件的名称、颜色和重量。2.查询

17、所有供应商名称、所在城市 3.查询所有工程名称和所在城市,2023/4/25,42,SELECT Pname,Color,WeightFROM PSELECT Pname 名称,Color 颜色,Weight 重量FROM PSELECT Pname as 名称,Color as 颜色,Weight as 重量FROM P,2023/4/25,43,二、选择表中的若干元组,1.消除取值重复的行2.查询满足条件的元组,2023/4/25,44,1.消除取值重复的行,在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 Sno Cno Grade-200215121 1 92 200

18、215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80,2023/4/25,45,ALL 与 DISTINCT,例6 查询选修了课程的学生学号。(1)SELECT(ALL)Sno FROM SC结果：Sno-200215121 200215121 200215121 200215122 200215122,2023/4/25,46,例题（续）,(2)SELECT DISTINCT Sno FROM SC;结果：Sno-200215121 200215122,2023/4/25,47,例题（续）,注意 DISTINCT短语的作用范围是所有

19、目标列例：查询选修课程的各种成绩错误的写法:SELECT DISTINCT Cno,DISTINCT GradeFROM SC;正确的写法:SELECT DISTINCT Cno,Grade FROM SC;,2023/4/25,48,2.查询满足条件的元组,WHERE子句常用的查询条件,2023/4/25,49,(1)比较大小,比较运算符包括：=,=,!,!,2023/4/25,50,(1)比较大小,例7 查询计算机科学系全体学生的名单SELECT Sno,Sname,Ssex FROM Student WHERE Sdept=CS,2023/4/25,51,(1)比较大小（续）,例8 查询

20、所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,Sage FROM Student WHERE Sage 20,2023/4/25,52,(1)比较大小（续）,例9 查询考试成绩有不及格的学生的学号SELECT Sno FROM SC WHERE Grade60,2023/4/25,53,(2)确定范围,使用谓词 BETWEEN AND NOT BETWEEN AND 例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23,2

21、023/4/25,54,例题（续）,例11 查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23,2023/4/25,55,(3)确定集合,使用谓词 IN,NOT IN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计 算机科学系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdept IN(IS,MA,CS),2023/4/25,56,(3)确定集合(续),例13查询既不是信息系、数学系，也不是计

22、算 机科学系的学生的姓名和性别。SELECT Sname,SsexFROM Student WHERE Sdept NOT IN(IS,MA,CS);,2023/4/25,57,(4)字符串匹配,NOT LIKE ESCAPE：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 当匹配模板为固定字符串时，可以用=运算符取代 LIKE 谓词 用!=或 运算符取代 NOT LIKE 谓词,2023/4/25,58,通配符,%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串_(下横线)代表任意单个字符例：

23、a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,2023/4/25,59,ESCAPE 短语：,当用户要查询的字符串本身就含有%或 _ 时，要使用ESCAPE 短语对通配符进行转义。,2023/4/25,60,例题,1)匹配模板为固定字符串 例14 查询学号为200215121的学生的详细情况。SELECT*FROM Student WHERE Sno LIKE 200215121；等价于：SELECT*FROM Student WHERE Sno=200215121；,2023/4/25,61,例题（续）,2)匹配模板为含通配符的字符串例15 查询所有姓

24、刘学生的姓名、学号和性别。SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%,2023/4/25,62,例题（续）,匹配模板为含通配符的字符串（续）例16 查询姓“刘”且全名为二个汉字的学生的姓名。SELECT Sname FROM Student WHERE Sname LIKE 刘_,2023/4/25,63,例题（续）,匹配模板为含通配符的字符串（续）例17 查询名字中第2个字为阳字的学生的姓名和学号。SELECT Sname,Sno FROM Student WHERE Sname LIKE _阳%,2023/4/25,64,例

25、题（续）,匹配模板为含通配符的字符串（续）例18 查询所有不姓刘的学生姓名。SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE 刘%,2023/4/25,65,例题（续）,3)使用换码字符将通配符转义为普通字符 例19 查询DB_Design课程的课程号和学分。SELECT Cno,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE,2023/4/25,66,例题（续）,使用换码字符将通配符转义为普通字符(续)例20 查询以DB_开头，且倒数第3个字符为 i的课程的详细情况。SE

26、LECT*FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE,2023/4/25,67,(5)涉及空值的查询,使用谓词 IS NULL 或 IS NOT NULL“IS NULL”不能用“=NULL”代替例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL,2023/4/25,68,例题(续),例22 查所有有成绩的学生学号和课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NOT

27、 NULL,2023/4/25,69,(6)多重条件查询,用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND,2023/4/25,70,例题,例23 查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage20,2023/4/25,71,改写例12,例12 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECT Sname,SsexFROM StudentWHERE Sdep

28、t IN(IS,MA,CS)可改写为：SELECT Sname,SsexFROM StudentWHERE Sdept=IS OR Sdept=MA OR Sdept=CS,2023/4/25,72,改写例10,例10 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23 可改写为：SELECT Sname,Sdept,Sage FROM Student WHERE Sage=20 AND Sage=23,2023/4/25,73,练习-参考第二章习

29、题5中的4个表,查询使用S1供应商所供应零件的工程号查询使用S1或者S2供应商所供应零件的工程号名称中包含“螺”字的零件编号和颜色,2023/4/25,74,SELECT 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%螺%,2023/4/25,75,三、对查询结果排序,使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排

30、序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,2023/4/25,76,对查询结果排序（续）,例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECT Sno,Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC,2023/4/25,77,对查询结果排序（续）,例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROM Student ORDER BY Sdept,Sage DESC,2023/4/25,78,四、使用集函数,5类主要集函数计数COUNT（DIS

31、TINCT|ALL*）统计元组的个数COUNT（DISTINCT|ALL）统计某列值的个数计算总和SUM（DISTINCT|ALL）求某列值的总和 计算平均值AVG（DISTINCT|ALL）求某列值的均值,2023/4/25,79,使用集函数（续）,求最大值MAX（DISTINCT|ALL）求某列值的最大值 求最小值MIN（DISTINCT|ALL）求某列值的最小值DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值,2023/4/25,80,使用集函数（续）,例26 查询学生总人数。SELECT COUNT(*)FROM Student例27 查询选修

32、了课程的学生人数。SELECT COUNT(DISTINCT Sno)FROM SC注：用DISTINCT以避免重复计算学生人数,2023/4/25,81,使用集函数（续）,例28 计算1号课程的学生平均成绩。SELECT AVG(Grade)FROM SC WHERE Cno=1；例29 查询选修1号课程的学生最高分数。SELECT MAX(Grade)FROM SC WHERE Cno=1；,2023/4/25,82,使用集函数（续）,例30 查询学生200215012选修课程的总分 SELECT SUM(grade)FROM SC WHERE Sno=200215012,2023/4/2

33、5,83,使用集函数（续）,在集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值。,2023/4/25,84,参考S、P、J、SPJ关系模式,查询S1供应商供应的工程数量查询S1供应商供应了几种零件查询S1供应商供应P1零件数量查询供应商平均信誉（status）值查询零件最大重量查询给J2工程供应零件的供应商数量查询供应P3零件的供应商数量,2023/4/25,85,SELECT COUNT(distinct Jno)FROM SPJ WHERE Sno=S1,2023/4/25,86,五、对查询结果分组,使用GROUP BY子句分组 细化集函数的作用对象 未对查询结果分组，集函

34、数将作用于整个查询结果 对查询结果分组后，集函数将分别作用于每个组,2023/4/25,87,使用GROUP BY子句分组,例31 求各个课程号及相应的选课人数。SELECT Cno,COUNT(Sno)FROM SC GROUP BY Cno 结果 Cno COUNT(Sno)1 1 2 2 3 2,2023/4/25,88,对查询结果分组（续）,GROUP BY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数,2023/4/25,89,使用HAVING短语筛选最终输出结果,例3

35、2 查询选修了2门以上课程的学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)2,2023/4/25,90,例题,例 查询有2门以上课程是90分以上的 学生的学号及（90分以上的）课程数 SELECT Sno,COUNT(*)FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=2;,2023/4/25,91,使用HAVING短语筛选最终输出结果,只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。,2023/4/25,92,练习,查询各供应商的编号及其供应工程的数量。查询各工程使用了几种零件查询各种零件的供应数量查询各工程使用各种零件的数量查询各工程使用零件的总数,练习(连接、分组),统计各系的选课人数、课程门数。统计每个学生学号、选课门数、平均成绩。只显示上题中平均成绩大于80分的。统计每个学生学号、姓名、选课门数、平均成绩。统计各种颜色零件的使用数量。统计各供应商编号、名称和供应工程数量。,2023/4/25,93,