关系数据库标准语言-SQL.pptx

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

1、1,数 据 库,主讲：许福,联系方式电话：62336516手机：邮件：地址：信息学院软件教研室 主楼411,2,第3章 关系数据库标准语言SQL,3,结构化查询语言Structured Query Language,数据查询数据定义数据操纵 数据控制,4,本章主要介绍SQL的使用和SQL Server 2000 DBMS的主要功能及使用方法掌握SQL的四大功能及使用，重点掌握数据查询功能熟悉SQL Server 2000的使用,5,3.1 SQL语言的基本概念与特点3.2 了解SQL Server 20003.3 创建与使用数据库3.4 创建与使用数据表3.5 创建与使用索引3.6 数据查询3

2、.7 数据更新3.8 视图3.9 数据控制,6,3.1 SQL语言的基本概念与特点,3.1.1 SQL语言的发展及标准化 SQL语言的发展,1974,Chamberlin,SEQUEL,SQL,大型数据库 SybaseINFORMIXSQL ServerOracleDB2INGRES-小型数据库 FoxProAccess,7,3.1.2 SQL语言的基本概念,基本表（Base Table）一个关系对应一个基本表 一个或多个基本表对应一个存储文件 视图（View）视图是从一个或几个基本表导出的表，是一个虚拟的表 S(SNo，SN，Sex，Age，Dept)S_Male(SNo，SN，Age，De

3、pt),无数据，只有定义,Sex=男,在数据库中只存有S_Male的定义，数据仍在S表中,8,SQL语言支持的关系数据库的三级模式结构,9,3.1.3 SQL语言的主要特点,SQL语言是类似于英语的自然语言，简洁易用 SQL语言是一种非过程语言 SQL语言是一种面向集合的语言 SQL语言既是自含式语言，又是嵌入式语言SQL语言具有数据查询、数据定义、数据操纵和数据控制四种功能,10,3.2 了解SQL Server 2000,SQL Server是一个关系数据库管理系统,企业版（Enterprise Edition）标准版（Standard Edition）个人版（Personal Editi

4、on）开发者版（Developer Edition）,11,3.2.1 SQL Server 2000的主要组件,12,3.2.2 企业管理器,由Enterprise Manager产生的SQL脚本是一个后缀名为.sql的文件企业管理器的管理工作,文本文件,管理数据库,管理数据库对象,管理备份,管理复制,管理登录和许可,管理SQL Server Agent,管理SQL Server Mail,13,3.2.3 查询分析器,使用查询分析器的熟练程度是衡量一个SQL Server用户水平的标准。,14,3.3 创建与使用数据库,数据文件1,事务日志文件,数据库,数据文件n,存放数据库数据和数据库对

5、象的文件 主要数据文件(.mdf)+次要数据文件(.ndf),只有一个,可有多个,记录数据库更新情况，扩展名为.ldf 当数据库破坏时可以用事务日志还原数据库内容,15,文件组文件组（File Group）是将多个数据文件集合起来形成的一个整体 主要文件组+次要文件组 一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用 日志文件不分组，它不能属于任何文件组,16,3.3.1 SQL Server的系统数据库,Model,Msdb,Tempdb,系统默认数据库,系统信息：磁盘空间；文件分配和使用；系统级的配置参数；登录账号信息；SQL Server初始化信息；系统中其他系统数据

6、库和用户数据库的相关信息,Model数据库存储了所有用户数据库和Tempdb数据库的创建模板 通过更改Model数据库的设置可以大大简化数据库及其对象的创建设置工作,存储计划信息以及与备份和还原相关的信息,Tempdb数据库用作系统的临时存储空间 存储临时表，临时存储过程和全局变量值；为排序创建临时表；存储用户利用游标说明所筛选出来的数据,Master,17,3.3.2 SQL Server的实例数据库,重建实例数据库安装目录MSSQLInstall中：Instpubs.sqlInstnwnd.sql,实例数据库,pubs,Northwind,虚构的图书出版公司的基本情况,包含了一个公司的销售

7、数据,18,3.3.3 创建用户数据库,用Enterprise Manager 创建数据库 用SQL命令创建数据库 CREATE DATABASE database_name ON,.n,.n LOG ON,.n COLLATE collation_name FOR LOAD|FOR ATTACH,PRIMARY(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),.n,19,例3-1 用SQL命令创建一个教学数据库Teach

8、，数据文件的逻辑名称为Teach_Data，数据文件物理地存放在C：盘的根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在C：盘的根目录下，文件名为TeachLog.ldf，初始存储空间大小为10MB，最大存储空间为25MB，存储空间自动增长量为5MB。,CREATE DATABASE TeachON(NAME=Teach_Data,FILENAME=C:TeachData.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=5)LO

9、G ON(NAME=Teach_Log,FILENAME=C:TeachLog.ldf,SIZE=5,MAXSIZE=25,FILEGROWTH=5),20,3.3.4 修改用户数据库,用Enterprise Manager修改数据库 用SQL命令修改数据库 ALTER DATABASE database_name ADD FILE,.n TO FILEGROUP filegroup_name|ADD LOG FILE,.n|REMOVE FILE logical_file_name WITH DELETE|ADD FILEGROUP filegroup_name|REMOVE FILEGRO

10、UP filegroup_name|MODIFY FILE|MODIFY NAME=new_dbname|MODIFY FILEGROUP filegroup_namefilegroup_property|NAME=new_filegroup_name|SET,.n WITH|COLLATE,21,例3-2 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。,ALTER DATABASE NorthwindMODIFY FILE(NAME=Northwind,FILEGROWTH=2mb),22,3.3.5 删除用户数据库,用Enterprise Manager删

11、除数据库 用SQL命令删除数据库 DROP DATABASE database_name,.n 例3-3 删除数据库Teach。DROP DATABASE Teach,23,3.3.6 查看数据库信息,用Enterprise Manager查看数据库信息用系统存储过程显示数据库信息 用系统存储过程显示数据库结构 用系统存储过程显示文件信息 用系统存储过程显示文件组信息,Sp_helpdb dbname=name,Sp_helpfile filename=name,Sp_helpfilegroup filegroupname=name,24,EXEC Sp_helpdb NorthwindEXE

12、C Sp_helpfile Northwind Use NorthwindEXEC Sp_helpfilegroup,25,3.4 创建与使用数据表,3.4.1 数据类型,整数数据,精确数值,近似浮点数值,日期时间数据,bigint，int，smallint，tinyint,numeric和decimal,float和real,datetime与smalldatetime,26,字符串数据,Unicode字符串数据,二进制数据,货币数据,char、varchar、text,nchar、nvarchar与ntext,binary、varbinary、image,money与smallmoney,

13、标记数据,timestamp和uniqueidentifier,27,3.4.2 创建数据表,用Enterprise Manager创建数据表 相关属性定义“字段名”“数据类型”字段的“长度”、“精度”和“小数位数”“允许空”“默认值”,同一表中不许有重名字段,系统默认为NULL,28,用SQL命令创建数据表 CREATE TABLE(，|)例3-4 用SQL命令建立一个学生表S。CREATE TABLE S(SNo CHAR(6),SN VARCHAR(8),Sex CHAR(2)DEFAULT 男,Age INT,Dept VARCHAR(20),DEFAULT,缺省值为“男”,29,3.

14、4.3 定义数据表的约束,正确性,有效性,相容性,数据的完整性,约束（Constraint）默认（Default）规则（Rule）触发器（Trigger）存储过程（Stored Procedure）,SQL Server的数据完整性机制,30,完整性约束的基本语法格式 CONSTRAINT,NULL/NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,31,NULL/NOT NULL约束 NULL表示“不知道”、“不确定”或“没有数据”的意思 主键列不允许出现空值 CONSTRAINT NULL|NOT NULL例3-5 建立一个S表，对SNo字段进行N

15、OT NULL约束。CREATE TABLE S(SNo CHAR(6)CONSTRAINT S_Cons NOT NULL,SN VARCHAR(8),Sex CHAR(2),Age INT,Dept VARCHAR(20),可省略约束名称:SNo CHAR(6)NOT NULL,32,UNIQUE约束（惟一约束）指明基本表在某一列或多个列的组合上的取值必须惟一在建立UNIQUE约束时，需要考虑以下几个因素：使用UNIQUE约束的字段允许为NULL值。一个表中可以允许有多个UNIQUE约束。可以把UNIQUE约束定义在多个字段上。UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，

16、缺省为非聚集索引。UNIQUE用于定义列约束 CONSTRAINT UNIQUE UNIQUE用于定义表约束 CONSTRAINT UNIQUE（,）,33,例3-6 建立一个S表，定义SN为惟一键。CREATE TABLE S(SNo CHAR(6),SN CHAR(8)CONSTRAINT SN_Uniq UNIQUE,Sex CHAR(2),Age INT,Dept VARCHAR(20)例3-7 建立一个S表，定义SN+SEX为惟一键，此约束为表约束。CREATE TABLE S(SNo CHAR(6),SN CHAR(8),Sex CHAR(2),Age INT,Dept VARCH

17、AR(20),CONSTRAINT S_UNIQ UNIQUE(SN,Sex),SN_Uniq可以省略 SN CHAR(8)UNIQUE,34,PRIMARY KEY约束（主键约束）用于定义基本表的主键，起惟一标识作用PRIMARY KEY与UNIQUE 的区别：一个基本表中只能有一个PRIMARY KEY，但可多个UNIQUE对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL 不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束,不能为NULL,不能重复,35,PRIMARY

18、 KEY用于定义列约束 CONSTRAINT PRIMARY KEY PRIMARY KEY用于定义表约束 CONSTRAINT PRIMARY KEY(,)例3-8 建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键。CREATE TABLE S(SNo CHAR(6)CONSTRAINT S_Prim PRIMARY KEY,SN CHAR(8),Sex CHAR(2),Age INT,Dept VARCHAR(20)CREATE TABLE C(CNo CHAR(5)CONSTRAINT C_Prim PRIMARY KEY,CN CHAR(20),CT IN

19、T),36,例3-9 建立一个SC表，定义SNo+CNo为SC的主键。CREATE TABLE SC(SNo CHAR(5)NOT NULL,CNo CHAR(5)NOT NULL,Score NUMERIC(4,1),CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo),37,FOREIGN KEY约束（外键约束）CONSTRAINT FOREIGN KEY REFERENCES(,),外部键,从表,主键,主表,引用,38,例3-10 建立一个SC表，定义SNo,CNo为SC的外部键。CREATE TABLE SC(SNo CHAR(5)NOT NULL CONST

20、RAINT S_Fore FOREIGN KEY REFERENCES S(SNo),CNo CHAR(5)NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo),Score NUMERIC(4,1),CONSTRAINT S_C_Prim PRIMARY KEY(SNo,CNo);,39,CHECK约束 CHECK约束用来检查字段值所允许的范围 在建立CHECK约束时，需要考虑以下几个因素：一个表中可以定义多个CHECK约束。每个字段只能定义一个CHECK约束。在多个字段上定义的CHECK约束必须为表约束。当执行INSERT、UPDA

21、TE语句时CHECK约束将验证数据。CONSTRAINT CHECK(),40,例3-11 建立一个SC表，定义Score的取值范围为0100之间。CREATE TABLE SC(SNo CHAR(5),CNo CHAR(5),Score NUMERIC(4,1)CONSTRAINT Score_Chk CHECK(Score=0 AND Score=100)例3-12 建立包含完整性定义的学生表。CREATE TABLE S(SNo CHAR(6)CONSTRAINT S_Prim PRIMARY KEY,SN CHAR(8)CONSTRAINT SN_Cons NOT NULL,Sex C

22、HAR(2)DEFAULT 男,Age INT CONSTRAINT Age_Cons NOT NULL CONSTRAINT Age_Chk CHECK(Age BETWEEN 15 AND 50),Dept CHAR(10)CONSTRAINT Dept_Cons NOT NULL),41,3.4.4 修改数据表,用Enterprise Manager 修改数据表的结构 用SQL命令修改数据表,ALTER TABLE ADD|,ALTER TABLE ALTER COLUMN NULL|NOT NULL,ALTER TABLEDROP CONSTRAINT,42,例3-13 在S表中增加一

23、个班号列和住址列。ALTER TABLE S ADDClass_No CHAR(6),Address CHAR(40)使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。例3-14 在SC表中增加完整性约束定义，使Score在0100之间。ALTER TABLE SC ADD CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100),43,例3-15 把S表中的SN列加宽到10个字符。ALTER TABLE SALTER COLUMNSN CHAR(10)不能改变列名；不能将含有空值的列的定义修改为NOT NULL

24、约束；若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；只能修改NULL/NOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。例3-16 删除S表中的主键。ALTER TABLE SDROP CONSTRAINT S_Prim,44,3.4.5 删除基本表,用Enterprise Manager删除数据表 用SQL命令删除数据表 DROP TABLE 只能删除自己建立的表，不能删除其他用户所建的表,45,3.4.6 查看数据表,查看数据表的属性属性包括：数据表的名称，所有者，创建日期，文件组，记录的行数，数据表中的字段名称、结构和类型等。查

25、看数据表中的数据 在Enterprise Manager中，用右键单击要查看数据的表，从快捷菜单中选择“打开表”，再选择其子菜单中的“返回所有行”。,46,3.5 创建与使用索引,3.5.1 索引的作用 3.5.2 索引的分类,加快查询速度 保证行的惟一性,聚集索引与非聚集索引,唯一索引,复合索引,聚集索引：查询速度快非聚集索引：更新速度快,排列的结果存储在表中 只有一个,排列的结果不存储在表中 可以有多个,有UNIQUE，自动建立非聚集的惟一索引有PRIMARY KEY，自动建立聚集索引,将两个或多个字段组合起来建立的索引，单独的字段允许有重复的值,47,3.5.3 创建索引,用Enterp

26、rise Manager创建索引 用索引创建向导创建索引 直接创建索引 用SQL命令创建索引 CREATE UNIQUE CLUSTERED INDEX ON(次序,次序),建立惟一索引,建立聚集索引,ASC或DESC，默认为ASC,48,例3-18 为表SC在SNo和CNo上建立惟一索引。CREATE UNIQUE INDEX SCI ON SC(SNo,CNo)例3-19 为教师表T在TN上建立聚集索引。CREATE CLUSTERED INDEX TI ON T(TN)注意：（1）改变表中的数据（如增加或删除记录）时，索引将自动更新。（2）索引建立后，在查询使用该列时，系统将自动使用索引

27、进行查询。（3）索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。,49,3.5.4 查看与修改索引,用Enterprise Manager查看和修改索引 用Sp_helpindex存储过程查看索引 Sp_helpindex objname=name 例3-20 查看表SC的索引。EXEC Sp_helpindex SC,表的名称,50,用Sp_rename存储过程更改索引名称 Sp_rename 数据表名.原索引名,原索引名 例3-21 更改T表中的索引TI名称为T_Index。EXEC Sp_rename T.TI,T_Inde

28、x,index,51,3.5.5 删除索引,用Enterprise Manager删除索引 用DROP INDEX命令删除索引 DROP INDEX数据表名.索引名 例3-22 删除表SC的索引SCI。DROP INDEX SC.SCI,不能删除由CREATE 或ALTER命令创建的索引，也不能删除系统表中的索引,52,3.6 数据查询,3.6.1 SELECT命令的格式与基本使用SELECT ALL|DISTINCTTOP N PERCENTWITH TIES列名AS 别名1，列名 AS 别名2INTO 新表名FROM表名1或视图名1AS 表1别名，表名2或视图名2AS 表2别名WHERE检

29、索条件GROUP BY HAVING ORDER BY ASC|DESC,投影,选取,53,WITH TIES,select top(3)*from tb order by score/*1 603 702 85*/select top(3)with ties*from tb order by score/*1 603 702 855 85*/,54,例3-23 查询全体学生的学号、姓名和年龄。SELECT SNo,SN,AgeFROM S 例3-24 查询学生的全部信息。SELECT*FROM S 例3-25 查询选修了课程的学生号。SELECT DISTINCT SNo FROM SC例3

30、-26 查询全体学生的姓名、学号和年龄。SELECT SN Name,SNo,Age FROM S,SELECT SN AS Name,SNo,Age,55,3.6.2 条件查询,常用的比较运算符：,56,比较大小 例3-27 查询选修课程号为C1的学生的学号和成绩SELECT SNo,Score FROM SC WHERE CNo=C1例3-28 查询成绩高于85分的学生的学号、课程号和成绩。SELECT SNo,CNo,Score FROM SC WHERE Score85,57,多重条件查询 NOT、AND、OR 用户可以使用括号改变优先级例3-29 查询选修C1或C2且分数大于等于85

31、分学生的学号、课程号和成绩。SELECT SNo,CNo,ScoreFROM SCWHERE(CNo=C1 OR CNo=C2)AND(Score=85),高,低,58,确定范围 例3-30 查询工资在1000至1500元之间的教师的教师号、姓名及职称。SELECT TNo,TN,ProfFROM TWHERE Sal BETWEEN 1000 AND 1500 例3-31 查询工资不在1000至1500之间的教师的教师号、姓名及职称。SELECT TNo,TN,ProfFROM TWHERE Sal NOT BETWEEN 1000 AND 1500,WHERE Sal=1000 AND S

32、al=1500,59,确定集合 利用“IN”操作可以查询属性值属于指定集合的元组。例3-32 查询选修C1或C2的学生的学号、课程号和成绩。SELECT SNo,CNo,Score FROM SC WHERE CNo IN(C1，C2)利用“NOT IN”可以查询指定集合外的元组。例3-33 查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。SELECT SNo,CNo,ScoreFROM SC WHERE CNo NOT IN(C1，C2),60,部分匹配查询 当不知道完全精确的值时，用户可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）LIKE 例3-34 查询所

33、有姓张的教师的教师号和姓名。SELECT TNo,TN FROM TWHERE TN LIKE 张%例3-35 查询姓名中第二个汉字是“力”的教师号和姓名。SELECT TNo,TN FROM TWHERE TN LIKE_力%,61,空值查询某个字段没有值称之为具有空值（NULL）空值不同于零和空格，它不占任何存储空间 例3-36 查询没有考试成绩的学生的学号和相应的课程号。SELECT SNo,CNoFROM SCWHERE Score IS NULL,62,3.6.3 常用库函数及统计汇总查询,63,例3-37 求学号为S1学生的总分和平均分。SELECT SUM(Score)AS To

34、talScore,AVG(Score)AS AveScoreFROM SCWHERE(SNo=S1)例3-38 求选修C1号课程的最高分、最低分及之间相差的分数。SELECT MAX(Score)AS MaxScore,MIN(Score)AS MinScore,MAX(Score)MIN(Score)AS DiffFROM SCWHERE(CNo=C1)例3-40 求学校中共有多少个系。SELECT COUNT(DISTINCT Dept)AS DeptNumFROM S,DISTINCT消去重复行,64,例3-41 统计有成绩同学的人数。SELECT COUNT(Score)FROM SC

35、成绩为零的同学他计算在内，没有成绩（即为空值）的不计算。例3-42 利用特殊函数COUNT(*)求计算机系学生的总数。SELECT COUNT(*)FROM SWHERE Dept=计算机,COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字。,65,3.6.4 分组查询,GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。例3-43 查询各个教师的教师号及其任课的门数。SELECT TNo,COUNT(*)AS C_NumFROM TCGROUP BY TNo,GROUP BY子句按TNo的值分组，

36、所有具有相同TNo的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。,66,若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句 例3-44 查询选修两门以上课程的学生的学号和选课门数。SELECT SNo,COUNT(*)AS SC_NumFROM SCGROUP BY SNoHAVING(COUNT(*)=2),GROUP BY子句按SNo的值分组，所有具有相同SNo的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。HAVING子句去掉不满足COUNT（*）=2的组,67,3.3.5 查询的排序,当需要对查询结果排序时，应该使用

37、ORDER BY子句，ORDER BY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。例3-45 查询选修C1 的学生学号和成绩，并按成绩降序排列。SELECT SNo,ScoreFROM SCWHERE(CNo=C1)ORDER BY Score DESC,68,例3-46 查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。SELECT SNo,CNo,ScoreFROM SCWHERE(CNo IN(C2,C3,C4,C5)ORDER BY SNo,Score DESC,69,例3-47 求选

38、课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。SELECT SNo,SUM(Score)AS TotalScoreFROM SCWHERE(Score=60)GROUP BY SNoHAVING(COUNT(*)=3)ORDER BY SUM(Score)DESC,取出整个SC,筛选Score=60的元组,将选出的元组按SNo分组,筛选选课三门以上的分组,将选取结果排序,在剩下的组中提取学号和总成绩,ORDER BY 2 DESC;“2”代表查询结果的第二列,70,3.6.6 数据表连接及连接查询,连接查询：一个查询需要对多个表进行操作连接字段：数据表之间的联系

39、是通过表的字段值来体现的连接操作的目的：从多个表中查询数据 表的连接方法:表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件 利用关键字JOIN进行连接：当将JOIN 关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件,71,JOIN的分类,72,等值连接与非等值连接 例3-48 查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。方法1：SELECT T.TNo,TN,CNoFROM T,TCWHERE(T.TNo=TC.TNo)AND(TN=刘伟)方法2：SELECT T.TNo,TN,CNoFROM T

40、 INNER JOIN TCON T.TNo=TC.TNoWHERE(TN=刘伟),连接条件，当比较运算符为“”时，称为等值连接。其他情况为非等值连接。,引用列名TNo时要加上表名前缀，这是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。,73,例3-49 查询所有选课学生的学号、姓名、选课名称及成绩。SELECT S.SNo,SN,CN,ScoreFROM S,C,SCWHERE S.SNo=SC.SNo AND SC.CNo=C.CNo 例3-50 查询每门课程的课程名、任课教师姓名及其职务、选课人数。SELECT CN,TN,Prof,COUNT(SC.S

41、No)FROM C,T,TC,SCWHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNoGROUP BY SC.CNo,74,自身连接 例3-51 查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。,方法1：SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_bFROM T AS X,T AS Y WHERE X.SalY.SalAND Y.TN=刘伟,方法2：SELECT X.TN,X.Sal,Y.Sal FROM T AS X INNER JOIN T AS Y ON X.SalY.SalAND Y.TN=刘

42、伟,方法3：SELECT R1.TN,R1.Sal,R2.SalFROM(SELECT TN,Sal FROM T)AS R1INNER JOIN(SELECT Sal FROM TWHERE TN=刘伟)AS R2ON R1.SalR2.Sal,75,例3-52 检索所有学生姓名，年龄和选课名称。,方法1：SELECT SN,Age,CNFROM S,C,SCWHERE S.SNo=SC.SNo AND SC.CNo=C.CNo,方法2：SELECT R3.SNo,R3.SN,R3.Age,R4.CNFROM(SELECT SNo,SN,Age FROM S)AS R3INNER JOIN(

43、SELECT R2.SNo,R1.CNFROM(SELECT CNo,CN FROM C)AS R1INNER JOIN(SELECT SNo,CNo FROM SC)AS R2ON R1.CNo=R2.CNo)AS R4ON R3.SNo=R4.SNo,76,外连接 而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。例3-53 查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。SELECT S.SNo,SN,CN,ScoreFROM S

44、LEFT OUTER JOIN SCON S.SNo=SC.SNoLEFT OUTER JOIN CON C.CNo=SC.CNo,左外部连接右外部连接,77,3.6.7 子查询,在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询。返回一个值的子查询 例3-54 查询与“刘伟”老师职称相同的教师号、姓名SELECT TNo,TNFROM TWHERE Prof=(SELECT Prof FROM T WHERE TN=刘伟),使用比较运算符（=,=,=,!=）,78,返回一组值的子查询 使用ANY例3-55 查询讲授课程号为C5的教师姓名。S

45、ELECT TNFROM TWHERE(TNo=ANY(SELECT TNo FROM TC WHERE CNo=C5),使用ANY或ALL,SELECT TNFROM T,TCWHERE T.TNo=TC.TNoAND TC.CNo=C5,IN,79,例3-56 查询其他系中比计算机系某一教师工资高的教师的姓名和工资。SELECT TN,SalFROM TWHERE(Sal ANY(SELECT Sal FROM T WHERE Dept=计算机)AND(Dept 计算机)SELECT TN,SalFROM TWHERE Sal(SELECT MIN(Sal)FROM T WHERE Dep

46、t=计算机)AND Dept 计算机,80,使用ALL 例3-58 查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。SELECT TN,SalFROM TWHERE(Sal ALL(SELECT SalFROM T WHERE Dept=计算机)AND(Dept 计算机)例3-59 查询不讲授课程号为C5的教师姓名。SELECT DISTINCT TNFROM TWHERE(C5 ALL(SELECT CNo FROM TC WHERE TNo=T.TNo),Sal(SELECT MAX(Sal),NOT IN,81,二者的执行方式不同：普通子查询的执行顺序是：首先执行子查询，然后把

47、子查询的结果作为父查询的查询条件的值。普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。相关子查询的执行顺序是：首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。相关子查询的执行次数是由父查询表的行数决定的。,82,使用EXISTS 带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则

48、返回假值。NOT EXISTS与此相反。含有IN的查询通常可用EXISTS表示，但反过来不一定。例3-60 用含有EXISTS的语句完成例3-55的查询。查询讲授课程号为C5的教师姓名。SELECT TNFROM TWHERE EXISTS(SELECT*FROM TC WHERE TNo=T.TNo AND CNo=C5),83,例3-61 查询没有讲授课程号为C5的教师姓名。SELECT TNFROM TWHERE(NOT EXISTS(SELECT*FROM TC WHERE TNo=T.TNo AND CNo=C5)例3-62 查询选修所有课程的学生姓名。SELECT SNFROM S

49、WHERE(NOT EXISTS(SELECT*FROM C WHERE NOT EXISTS(SELECT*FROM SC WHERE SNo=S.SNo AND CNo=C.CNo),84,3.6.8 合并查询,合并查询就是使用UNION 操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。参加合并查询的各子查询的使用的表结构应该相同。例3-63 从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。SELECT SNo AS 学号,SUM(Score)AS 总分FROM SCWHER

50、E(SNo=S1)GROUP BY SNoUNIONSELECT SNo AS 学号,SUM(Score)AS 总分FROM SCWHERE(SNo=S5)GROUP BY SNo,85,3.6.9 存储查询结果到表中,使用SELECTINTO 语句可以将查询结果存储到一个新建的数据库表或临时表中。例3-64 从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表cal_table中。SELECT SNo AS 学号,SUM(Score)AS 总分INTO Cal_TableFROM SCGROUP BY SNo,86,3.7 数据操纵,3.7.1 添加数据用Enterpr