专升本SQL.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,*,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,专升本SQL课件,专升本SQL课件专升本SQL课件3.1 SQL语言的基本概念与特点,3.2 了解SQL Server 2000,3.3 创建与使用数据库,3.4 创建与使用数据表,3.5 创建与使用索引,3.6 数据查询,3.7 数据更新,3.8 视图,3.9 数据控制2,3.1 SQL,语言的基本概念与特点,3.2,了解,SQL Server 2000,3.3,创建与使用数据库,3.4,创建与使用数据表,3.5,创建与使用索引,3.6,数据查询,3.7,数据更新,3.8,视图,3.9,数据控制,2,结构化查询语言,Structured Query Language,数据查询,数据定义,数据操纵,数据控制,3,3.1 SQL,语言的基本概念与特点,3.1.1 SQL,语言的发展及标准化,SQL,语言的发展,Chamberlin,SEQUEL,SQL,大型数据库,Sybase,INFORMIX,SQL Server,Oracle,DB2,INGRES,-,小型数据库,FoxPro,Access,4,3.1.2 SQL,语言的基本概念,基本表（,Base Table,）,一个关系对应一个基本表,一个或多个基本表对应一个存储文件,视图（,View,）,视图是从一个或几个基本表导出的表，是一个虚拟的表,S(SNo,，,SN,，,Sex,，,Age,，,Dept),S_Male(SNo,，,SN,，,Age,，,Dept),无数据，只有定义,Sex=,男,在数据库中只存有,S_Male,的定义，数据仍在,S,表中,5,SQL,语言支持的关系数据库的三级模式结构,6,3.1.3 SQL,语言的主要特点,SQL,语言是类似于英语的自然语言，简洁易用,SQL,语言是一种非过程语言,SQL,语言是一种面向集合的语言,SQL,语言既是自含式语言，又是嵌入式语言,SQL,语言具有数据查询、数据定义、数据操纵和数据控制四种功能,7,3.2,了解,SQL Server 2000,SQL Server,是一个关系数据库管理系统,企业版（,Enterprise Edition,）,标准版（,Standard Edition,）,个人版（,Personal Edition,）,开发者版（,Developer Edition,）,8,3.2.1 SQL Server 2000,的主要组件,组 件,功 能,企业管理器,管理所有的数据库系统工作和服务器工作,查询分析器,执行,Transact-SQL,命令等,SQL,脚本程序,服务管理器,启动、暂停或停止,SQL Server,的四种服务,客户端网络实用工具,配置客户端的连接、测定网络库的版本信息以及设定本地数据库的相关选项,服务器网络实用工具,配置服务器端的连接、测定网络库的版本信,导入和导出数据,在,OLE DB,数据源之间复制数据,在,IIS,中配置,SQL XML,支持,在运行,IIS,的计算机上定义、注册虚拟目录，并在虚拟目录和,SQL Server,实例之间创建关联,事件探查器,监视,SQL Server,数据库系统引擎事件,联机丛书,查询信息,9,3.2.2,企业管理器,由,Enterprise Manager,产生的,SQL,脚本是一个后缀名为,.sql,的文件,企业管理器的管理工作,文本文件,管理数据库,管理数据库对象,管理备份,管理复制,管理登录和许可,管理,SQL Server Agent,管理,SQL Server Mail,10,3.2.3,查询分析器,使用查询分析器的熟练程度是衡量一个,SQL Server,用户水平的标准。,11,3.3,创建与使用数据库,数据文件,1,事务日志文件,数据库,数据文件,n,存放数据库数据和数据库对象的文件,主要数据文件,(.mdf)+,次要数据文件,(.ndf),只有一个,可有多个,记录数据库更新情况，扩展名为,.ldf,当数据库破坏时可以用事务日志还原数据,库内容,12,文件组,文件组（）是将多个数据文件集合起来形成的一个整体,主要文件组,+,次要文件组,一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用,日志文件不分组，它不能属于任何文件组,13,3.3.1 SQL Server,的系统数据库,Model,Msdb,Tempdb,系统默认数据库,系统信息：,磁盘空间；文件分配和使用；系统级的配置参,数；登录账号信息；,SQL Server,初始化信息；,系统中其他系统数据库和用户数据库的相关信息,Model,数据库存储了所有用户数据库和,Tempdb,数,据库的创建模板,通过更改,Model,数据库的设置可以大大简化数据,库及其对象的创建设置工作,存储计划信息以及与备份和还原相关的信息,Tempdb,数据库用作系统的临时存储空间,存储临时表,临时存储过程和全局变量值，创建临,时表，存储用户利用游标说明所筛选出来的数据,Master,14,3.3.2 SQL Server,的实例数据库,重建实例数据库,安装目录,MSSQLInstall,中：,Instpubs.sql,Instnwnd.sql,实例数据库,pubs,Northwind,虚构的图书出版公司的基本情况,包含了一个公司的销售数据,15,3.3.3,创建用户数据库,用,Enterprise Manager,创建数据库,用,SQL,命令创建数据库,CREATE DATABASE database_name,ON,.n ,.n ,LOG ON ,.n ,COLLATE collation_name,FOR LOAD|FOR ATTACH,16,例,3-1,用,SQL,命令创建一个教学数据库,Teach,，数据文件的逻辑名称为,Teach_Data,，数据文件物理地存放在,D,：盘的根目录下，文件名为,TeachData.mdf,，数据文件的初始存储空间大小为,10MB,，最大存储空间为,50MB,，存储空间自动增长量为,5MB,；日志文件的逻辑名称为,Teach_Log,，日志文件物理地存放在,D,：盘的根目录下，文件名为,TeachLog.ldf,，初始存储空间大小为,10MB,，最大存储空间为,25MB,，存储空间自动增长量为,5MB,。,CREATE DATABASE Teach,ON,(NAME=Teach_Data,D:TeachData.mdf,SIZE=10,MAXSIZE=50,),LOG ON,(NAME=Teach_Log,D:TeachLog.ldf,SIZE=5,MAXSIZE=25,),17,3.3.4,修改用户数据库,用,Enterprise Manager,修改数据库,用,SQL,命令修改数据库,ALTER DATABASE database_name,ADD FILE ,.n TO ,|ADD LOG FILE ,.n,|REMOVE FILE logical_ WITH DELETE,|ADD,|REMOVE,|MODIFY FILE,|MODIFY NAME=new_dbname,|MODIFY,|NAME=new_,|SET ,.n WITH ,|COLLATE ,18,例,3-2,修改,Northwind,数据库中的,Northwind,文件增容方式为一次增加,2MB,。,ALTER DATABASE Northwind,MODIFY FILE,(NAME=Northwind,=2mb,),19,3.3.5,删除用户数据库,用,Enterprise Manager,删除数据库,用,SQL,命令删除数据库,DROP DATABASE database_name,.n,例,3-3,删除数据库,Teach,。,DROP DATABASE Teach,20,3.3.6,查看数据库信息,用,Enterprise Manager,查看数据库信息,用系统存储过程显示数据库信息,用系统存储过程显示数据库结构,用系统存储过程显示文件信息,用系统存储过程显示文件组信息,Sp_helpdb dbname=name,Sp_helpfile =name,Sp_help =name,21,EXEC Sp_helpdb Northwind,EXEC Sp_help,EXEC Sp_help,22,3.4,创建与使用数据表,3.4.1,数据类型,整数数据,精确数值,近似浮点数值,日期时间数据,bigint,，,int,，,smallint,，,tinyint,numeric,和,decimal,float,和,real,datetime,与,smalldatetime,23,字符串数据,Unicode,字符串数据,二进制数据,货币数据,char,、,varchar,、,text,nchar,、,nvarchar,与,ntext,binary,、,varbinary,、,image,money,与,smallmoney,标记数据,timestamp,和,uniqueidentifier,24,3.4.2,创建数据表,用,Enterprise Manager,创建数据表,相关属性定义,“字段名”,“数据类型”,字段的“长度”、“精度”和“小数位数”,“允许空”,“,默认值”,同一表中不许有重名字段,系统默认为,NULL,25,用,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 ,缺省值为“男”,26,3.4.3,定义数据表的约束,正确性,有效性,相容性,数据的完整性,约束（,Constraint,）,默认（,Default,）,规则（,Rule,）,触发器（,Trigger,）,存储过程（,Stored Procedure,）,SQL Server,的数据完整性机制,27,完整性约束的基本语法格式,CONSTRAINT ,NULL/NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,28,NULL/NOT NULL,约束,NULL,表示“不知道”、“不确定”或“没有数据”的意思,主键列不允许出现空值,CONSTRAINT NULL|NOT NULL,例,3-5,建立一个,S,表，对,SNo,字段进行,NOT 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,29,UNIQUE,约束（惟一约束）,指明基本表在某一列或多个列的组合上的取值必须惟一,在建立,UNIQUE,约束时，需要考虑以下几个因素：,使用,UNIQUE,约束的字段允许为,NULL,值。,一个表中可以允许有多个,UNIQUE,约束。,可以把,UNIQUE,约束定义在多个字段上。,UNIQUE,约束用于强制在指定字段上创建一个,UNIQUE,索引，缺省为非聚集索引。,UNIQUE,用于定义列约束,CONSTRAINT UNIQUE,UNIQUE,用于定义表约束,CONSTRAINT UNIQUE,（,）,30,例,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)UNIQUE,Sex CHAR(2),Age INT,Dept VARCHAR(20),CONSTRAINT S_UNIQ UNIQUE(SN,Sex),SN_Uniq,可以省略,SN CHAR(8)UNIQUE,31,PRIMARY KEY,约束（主键约束）,用于定义基本表的主键，起惟一标识作用,PRIMARY KEY,与,UNIQUE,的区别：,一个基本表中只能有一个,PRIMARY KEY,，但可多个,UNIQUE,对于指定为,PRIMARY KEY,的一个列或多个列的组合，其中任何一个列都不能出现,NULL,值，而对于,UNIQUE,所约束的惟一键，则允许为,NULL,对于指定为,PRIMARY KEY,的一个列或多个列的组合，其中任何一个列都不能出现,NULL,值，而对于,UNIQUE,所约束的惟一键，则允许为,NULL,不能为,NULL,不能重复,32,PRIMARY 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 INT),33,例,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),34,FOREIGN KEY,约束（外键约束）,CONSTRAINT FOREIGN KEY REFERENCES,(,),外部键,从表,主键,主表,引用,35,例,3-10,建立一个,SC,表，定义,SNo,CNo,为,SC,的外部键。,CREATE TABLE SC,(SNo CHAR(5)NOT NULL CONSTRAINT 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);,36,CHECK,约束,CHECK,约束用来检查字段值所允许的范围,在建立,CHECK,约束时，需要考虑以下几个因素：,一个表中可以定义多个,CHECK,约束。,每个字段只能定义一个,CHECK,约束。,在多个字段上定义的,CHECK,约束必须为表约束。,当执行,INSERT,、,UNDATE,语句时,CHECK,约束将验证数据。,CONSTRAINT CHECK(),37,例,3-11,建立一个,SC,表，定义,Score,的取值范围为,0,100,之间。,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 CHAR(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),38,3.4.4,修改数据表,用,Enterprise Manager,修改数据表的结构,用,SQL,命令修改数据表,ALTER TABLE,ADD|,ALTER TABLE,ALTER COLUMN NULL|NOT NULL,ALTER TABLE,DROP CONSTRAINT,39,例,3-13,在,S,表中增加一个班号列和住址列。,ALTER TABLE S,ADD,Class_No CHAR(6),Address CHAR(40),使用此方式增加的新列自动填充,NULL,值，所以不能为增加的新列指定,NOT NULL,约束。,例,3-14,在,SC,表中增加完整性约束定义，使,Score,在,0,100,之间。,ALTER TABLE SC,ADD,CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100),40,例,3-15,把,S,表中的,SN,列加宽到,10,个字符。,ALTER TABLE S,ALTER COLUMN,SN CHAR(10),不能改变列名；,不能将含有空值的列的定义修改为,NOT NULL,约束；,若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；,只能修改,NULL/NOT NULL,约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。,例,3-16,删除,S,表中的主键。,ALTER TABLE S,DROP CONSTRAINT S_Prim,41,3.4.5,删除基本表,用,Enterprise Manager,删除数据表,用,SQL,命令删除数据表,DROP TABLE,只能删除自己建立的表，不能删除其他用户所建的表,42,3.4.6,查看数据表,查看数据表的属性,属性包括：数据表的名称，所有者，创建日期，文件组，记录的行数，数据表中的字段名称、结构和类型等。,查看数据表中的数据,在,Enterprise Manager,中，用右键单击要查看数据的表，从快捷菜单中选择“打开表”，再选择其子菜单中的“返回所有行”。,43,3.5,创建与使用索引,3.5.1,索引的作用,3.5.2,索引的分类,加快查询速度,保证行的惟一性,聚集索引与非聚集索引,唯一索引,复合索引,聚集索引：查询速度快,非聚集索引：更新速度快,排列的结果存储在表中,只有一个,排列的结果不存储在表中,可以有多个,有,UNIQUE,，自动建立非聚集的惟一索引,有,PRIMARY KEY,，自动建立聚集索引,将两个或多个字段组合起来建立的索引，,单独的字段允许有重复的值,44,3.5.3,创建索引,用,Enterprise Manager,创建索引,用索引创建向导创建索引,直接创建索引,用,SQL,命令创建索引,CREATE UNIQUE CLUSTER INDEX ON (,次序,次序,),建立惟一索引,建立聚集索引,ASC,或,DESC,，默认为,ASC,45,例,3-18,为表,SC,在,SNo,和,CNo,上建立惟一索引。,CREATE UNIQUE INDEX SCI ON SC(SNo,CNo),例,3-19,为教师表,T,在,TN,上建立聚集索引。,CREATE CLUSTER INDEX TI ON T(TN),注意：,（,1,）改变表中的数据（如增加或删除记录）时，索引将自动更新。,（,2,）索引建立后，在查询使用该列时，系统将自动使用索引进行查询。,（,3,）索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。,46,3.5.4,查看与修改索引,用,Enterprise Manager,查看和修改索引,用,Sp_helpindex,存储过程查看索引,Sp_helpindex objname=name,例,3-20,查看表,SC,的索引。,EXEC Sp_helpindex SC,表的名称,47,用,Sp_rename,存储过程更改索引名称,Sp_rename,数据表名,.,原索引名,原索引名,例,3-21,更改,T,表中的索引,TI,名称为,T_Index,。,EXEC Sp_rename T.TI,T_Index,index,48,3.5.5,删除索引,用,Enterprise Manager,删除索引,用,DROP INDEX,命令删除索引,DROP INDEX,数据表名,.,索引名,例,3-22,删除表,SC,的索引,SCI,。,DROP INDEX SC.SCI,不能删除由,CREATE,或,ALTER,命令创建的索引，也不能删除系统表中的索引,49,3.6,数据查询,3.6.1 SELECT,命令的格式与基本使用,SELECT ALL|DISTINCTTOP N PERCENTWITH TIES,列名,AS,别名,1,，,列名,AS,别名,2,INTO,新表名,FROM,表名,1,或视图名,1AS,表,1,别名,，,表名,2,或视图名,2AS,表,2,别名,WHERE,检索条件,GROUP BY HAVING,ORDER BY ASC|DESC,投影,选取,50,例,3-23,查询全体学生的学号、姓名和年龄。,SELECT SNo,SN,Age,FROM S,例,3-24,查询学生的全部信息。,SELECT*,FROM S,例,3-25,查询选修了课程的学生号。,SELECT DISTINCT SNo,FROM SC,例,3-26,查询全体学生的姓名、学号和年龄。,SELECT SN Name,SNo,Age,FROM S,SELECT SN AS Name,SNo,Age,51,3.6.2,条件查询,运算符,含义,=,=,比较大小,AND,OR,NOT,多重条件,BETWEEN AND,确定范围,IN,确定集合,LIKE,字符匹配,IS NULL,空值,常用的比较运算符：,52,比较大小,例,3-27,查询选修课程号为,C1,的学生的学号和成绩,SELECT SNo,Score,FROM SC,WHERE CNo=C1,例,3-28,查询成绩高于,85,分的学生的学号、课程号和成绩。,SELECT SNo,CNo,Score,FROM SC,WHERE Score85,53,多重条件查询,NOT,、,AND,、,OR,用户可以使用括号改变优先级,例,3-29,查询选修,C1,或,C2,且分数大于等于,85,分学生的学号、课程号和成绩。,SELECT SNo,CNo,Score,FROM SC,WHERE(CNo=C1 OR CNo=C2)AND(Score=85),高,低,54,确定范围,例,3-30,查询工资在,1000,至,1500,元之间的教师的教师号、姓名及职称。,SELECT TNo,TN,Prof,FROM T,WHERE Sal BETWEEN 1000 AND 1500,例,3-31,查询工资不在,1000,至,1500,之间的教师的教师号、姓名及职称。,SELECT TNo,TN,Prof,FROM T,WHERE Sal NOT BETWEEN 1000 AND 1500,WHERE Sal=1000 AND Sal=1500,55,确定集合,利用“,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,Score,FROM SC,WHERE CNo NOT IN(C1,，,C2),56,部分匹配查询,当不知道完全精确的值时，用户可以使用,LIKE,或,NOT LIKE,进行部分匹配查询（也称模糊查询）,LIKE,例,3-34,查询所有姓张的教师的教师号和姓名。,SELECT TNo,TN,FROM T,WHERE TN LIKE,张,%,例,3-35,查询姓名中第二个汉字是“力”的教师号和姓名。,SELECT TNo,TN,FROM T,WHERE TN LIKE_,力,%,57,空值查询,某个字段没有值称之为具有空值（,NULL,）,空值不同于零和空格，它不占任何存储空间,例,3-36,查询没有考试成绩的学生的学号和相应的课程号。,SELECT SNo,CNo,FROM SC,WHERE Score IS NULL,58,3.6.3,常用库函数及统计汇总查询,函数名称,功 能,AVG,按列计算平均值,SUM,按列计算值的总和,MAX,求一列中的最大值,MIN,求一列中的最小值,COUNT,按列值计个数,59,例,3-37,求学号为,S1,学生的总分和平均分。,SELECT SUM(Score)AS TotalScore,AVG(Score)AS AveScore,FROM SC,WHERE(SNo=S1),例,3-38,求选修,C1,号课程的最高分、最低分及之间相差的分数。,SELECT MAX(Score)AS MaxScore,MIN(Score)AS MinScore,MAX(Score),MIN(Score)AS Diff,FROM SC,WHERE(CNo=C1),例,3-40,求学校中共有多少个系。,SELECT COUNT(DISTINCT Dept)AS DeptNum,FROM S,DISTINCT,消去重复行,60,例,3-41,统计有成绩同学的人数。,SELECT COUNT(Score),FROM SC,成绩为零的同学他计算在内，没有成绩（即为空值）的不计算。,例,3-42,利用特殊函数,COUNT(*),求计算机系学生的总数。,SELECT COUNT(*)FROM S,WHERE Dept=,计算机,COUNT,（*）用来统计元组的个数，不消除重复行，,不允许使用,DISTINCT,关键字。,61,3.6.4,分组查询,GROUP BY,子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。,例,3-43,查询各个教师的教师号及其任课的门数。,SELECT TNo,COUNT(*)AS C_Num,FROM TC,GROUP BY TNo,GROUP BY,子句按,TNo,的值分组，所有具有相同,TNo,的元,组为一组，对每一组使用函数,COUNT,进行计算，统计出各位教,师任课的门数。,62,若在分组后还要按照一定的条件进行筛选，则需使用,HAVING,子句,例,3-44,查询选修两门以上课程的学生的学号和选课门数。,SELECT SNo,COUNT(*)AS SC_Num,FROM SC,GROUP BY SNo,HAVING(COUNT(*)=2),GROUP BY,子句按,SNo,的值分组，所有具有相同,SNo,的元组为一,组，对每一组使用函数,COUNT,进行计算，统计出每位学生选课的门,数。,HAVING,子句去掉不满足,COUNT,（*）,=2,的组,63,3.3.5,查询的排序,当需要对查询结果排序时，应该使用,ORDER BY,子句，,ORDER BY,子句必须出现在其他子句之后。排序方式可以指定，,DESC,为降序，,ASC,为升序，缺省时为升序。,例,3-45,查询选修,C1,的学生学号和成绩，并按成绩降序排列。,SELECT SNo,Score,FROM SC,WHERE(CNo=C1),ORDER BY Score DESC,64,例,3-46,查询选修,C2,、,C3,、,C4,或,C5,课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。,SELECT SNo,CNo,Score,FROM SC,WHERE(CNo IN(C2,C3,C4,C5),ORDER BY SNo,Score DESC,65,例,3-47,求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。,SELECT SNo,SUM(Score)AS TotalScore,FROM SC,WHERE(Score=60),GROUP BY SNo,HAVING(COUNT(*)=3),ORDER BY SUM(Score)DESC,取出整个,SC,筛选,Score=60,的元组,将选出的元组按,SNo,分组,筛选选课三门以上的分组,将选取结果排序,在剩下的组中提取学号和总成绩,ORDER BY 2 DESC;,“,2”,代表查询结果的第二列,66,3.6.6,数据表连接及连接查询,连接查询：一个查询需要对多个表进行操作,表之间的连接：连接查询的结果集或结果表,连接字段：数据表之间的联系是通过表的字段值来体现的,连接操作的目的：从多个表中查询数据,表的连接方法,:,表之间满足一定条件的行进行连接时，,FROM,子句指明进行连接的表名，,WHERE,子句指明连接的列名及其连接条件,利用关键字,JOIN,进行连接：当将,JOIN,关键词放于,FROM,子句中时，应有关键词,ON,与之对应，以表明连接的条件,67,INNER JOIN,显示符合条件的记录，此为默认值,LEFT,（,OUTER,）,JOIN,为左（外）连接，用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以,NULL,来显示,RIGHT,（,OUTER,）,JOIN,右（外）连接，用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以,NULL,来显示,FULL,（,OUTER,）,JOIN,显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以,NULL,来显示,CROSS JOIN,将一个表的每一个记录和另一表的每个记录匹配成新的数据行,JION,的分类,68,等值连接与非等值连接,例,3-48,查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。,方法,1,：,SELECT T.TNo,TN,CNo,FROM T,TC,WHERE(T.TNo=TC.TNo)AND(TN=,刘伟,),方法,2,：,SELECT T.TNo,TN,CNo,FROM T INNER JOIN TC,ON T.TNo=TC.TNo,WHERE(TN=,刘伟,),连接条件，当比较运算符为“”时，称为等值连接。其他情况为非等值连接。,引用列名,TNo,时要加上表名前缀，这是因为两个表中的列名相同，,必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。,69,例,3-49,查询所有选课学生的学号、姓名、选课名称及成绩。,SELECT S.SNo,SN,CN,Score,FROM S,C,SC,WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo,例,3-50,查询每门课程的课程名、任课教师姓名及其职务、选课人数。,SELECT CN,TN,Prof,COUNT(SC.SNo),FROM C,T,TC,SC,WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo,GROUP BY SC.CNo,70,自身连接,例,3-51,查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。,方法,1,：,SELECT X.TN,X.Sal AS,Sal_a,Y.Sal AS Sal_b,FROM T AS X,T AS Y,WHERE X.SalY.Sal,AND Y.TN=,刘伟,方法,2,：,SELECT X.TN,X.Sal,Y.Sal,FROM T AS X INNER JOIN,T AS Y,ON X.SalY.Sal,AND Y.TN=,刘伟,方法,3,：,SELECT R1.TN,R1.Sal,R2.Sal,FROM,(SELECT TN,Sal FROM S)AS R1,INNER JOIN,(SELECT Sal FROM T,WHERE TN=,刘伟,)AS R2,ON R1.SalR2.Sal,71,例,3-52,检索所有学生姓名，年龄和选课名称。,方法,1,：,SELECT SN,Age,CN,FROM S,C,SC,WHERE S.SNo=SC.SNo,AND SC.CNo=C.CNo,方法,2,：,SELECT R3.SNo,R3.SN,R3.Age,R4.CN,FROM,(SELECT SNo,SN,Age FROM S)AS R3,INNER JOIN,(SELECT R2.SNo,R1.CN,FROM,(SELECT CNo,CN FROM C)AS R1,INNER JOIN,(SELECT SNo,CNo FROM SC)AS R2,ON R1.CNo=R2.CNo)AS R4,ON R3.SNo=R4.SNo,72,外连接,而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。,符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上,NULL,值后再返回到结果集中。,例,3-53,查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。,SELECT S.SNo,SN,CN,Score,FROM S,LEFT OUTER JOIN SC,ON S.SNo=SC.SNo,LEFT OUTER JOIN C,ON C.CNo=SC.CNo,左外部连接右外部连接,73,3.6.7,子查询,在,WHERE,子句中包含一个形如,SELECT-FROM-WHERE,的查询块，此查询块称为子查询或嵌套查询。,返回一个值的子查询,例,3-54,查询与“刘伟”老师职称相同的教师号、姓名,SELECT TNo,TN,FROM T,WHERE Prof=(SELECT Prof,FROM T,WHERE TN=,刘伟,),使用比较运算符,（,=,=,ANY(SELECT Sal,FROM T,WHERE Dept=,计算机,),AND(Dept ,计算机,),SELECT TN,Sal,FROM T,WHERE Sal (SELECT MIN(Sal),FROM T,WHERE Dept=,计算机,),AND Dept ,计算机,76,使用,ALL,例,3-58,查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。,SELECT TN,Sal,FROM T,WHERE(Sal ALL(SELECT Sal FROM T,WHERE Dept=,计算机,),AND(Dept ,计算机,),例,3-59,查询不讲授课程号为,C5,的教师姓名。,SELECT DISTINCT TN,FROM T,WHERE(C5 ALL(SELECT CNo,FROM TC,WHERE TNo=T.TNo),Sal (SELECT MAX(Sal),NOT IN,77,使用,EXISTS,带有,EXISTS,的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。,当子查询的的查询