数据库原理与应用chpSQL语言.pptx
第3章 SQL语言关系数据库标准语言SQL概述SQL的基本数据类型的基本数据类型SQL的数据定义（的数据定义（create,alter,drop）SQL的数据操纵（的数据操纵（insert,update,delete）SQL的数据查询（的数据查询（select）SQL的数据控制（的数据控制（grant,revoke）第1页/共145页2.1 SQL概述1、SQL语言标准的发展历程语言标准的发展历程 SQL-86SQL-89SQL-92 （SQL1）SQL-99 （SQL2）SQL-2003（SQL3）第2页/共145页2、SQL的特点的特点 一体化（功能强大）一体化（功能强大）数据查询、数据操纵、数据定义、数据控制于数据查询、数据操纵、数据定义、数据控制于一体一体,风格统一风格统一 高度非过程化高度非过程化 只需提出做什么只需提出做什么,无需指明怎么做无需指明怎么做 面向集合的操作方式面向集合的操作方式 能以多种方式使用能以多种方式使用 既可以独立使用既可以独立使用,也可以嵌入到其他高级语也可以嵌入到其他高级语言言简洁（语法简单）：简洁（语法简单）：9个动词个动词,类似英语语句类似英语语句2.1 SQL概述（续）第3页/共145页2.1 SQL概述（续）3、SQL语言基本概念语言基本概念 基本表基本表 独立存在的表独立存在的表,一个关系对应一个基本表。一个关系对应一个基本表。视图视图 虚表。是从一个或几个基本表导出的表。虚表。是从一个或几个基本表导出的表。视图中存放的仅仅是定义视图中存放的仅仅是定义,而不是数据而不是数据,数据仍在基本表中。数据仍在基本表中。索引索引 加速查询的速度而提供的数据结构加速查询的速度而提供的数据结构第4页/共145页第5页/共145页2.2 SQL Server的数据类型1、数值型、数值型int4个字节个字节smallint2个字节个字节tinyint1个字节个字节numeric小数小数decimal小数小数float浮点数浮点数第6页/共145页2.2 SQL Server的数据类型（续）2、字符串型、字符串型char固定长度（固定长度（1-8000）varchar可变长度（可变长度（1-8000）text可存储可存储231-1个字符的文本个字符的文本image可存储多种格式的文件可存储多种格式的文件第7页/共145页2.2 SQL Server的数据类型（续）3、日期时间型、日期时间型datetime8个字节（毫秒）个字节（毫秒）smalldatetime4个字节（分钟）个字节（分钟）4、货币型、货币型money8个字节个字节smallmoney4个字节个字节第8页/共145页学生课程数据库学生课程数据库学生表：学生表：Student(Sno,Sname,Ssex,Sage,Sdept)第9页/共145页学生课程数据库课程表：课程表：Course(Cno,Cname,Cpno,Credit)第10页/共145页学生课程数据库选课表：选课表：SC(Sno,Cno,Grade)第11页/共145页2.3 数据定义操作对象创建删除修改模式模式CREATE SCHEMADROPSCHEMA表表CREATETABLEDROPTABLEALTERTABLE视图视图CREATEVIEWDROPVIEW索引索引CREATEINDEXDROPINDEX数据库数据库CREATEDATABASEDROPDATABASEALTERDATABASE第12页/共145页2.3 数据定义（续）1、数据库的定义与删除、数据库的定义与删除 数据库定义数据库定义 CREATE DATABASE database_name 创建一个数据库及存储该数据库的文件。创建一个数据库及存储该数据库的文件。每个数据库至少包括两个文件：主数据文件和每个数据库至少包括两个文件：主数据文件和事务日志文件。事务日志文件。第13页/共145页2.3 数据定义（续）1、数据库的定义与修改与删除、数据库的定义与修改与删除 数据库修改数据库修改 ALTER DATABASE database_name。修改数据库。修改数据库。数据库删除数据库删除 DROP DATABASE database_name 删除数据库所有文件及磁盘文件。删除数据库所有文件及磁盘文件。第14页/共145页2、模式的定义与删除、模式的定义与删除 模式定义模式定义 CREATE SCHEMA schema_name AUTHORIZATION owner 创建一个模式创建一个模式,并指明模式的所有者。并指明模式的所有者。可以在模式中建立其他数据对象可以在模式中建立其他数据对象,如基本表如基本表,视图视图,索引等。索引等。2.3 数据定义（续）第15页/共145页2、模式的定义与删除、模式的定义与删除 模式删除模式删除 DROP SCHEMA schema_name 删除模式。删除模式。CASCADE表示级联删除表示级联删除,删除模式的同时删除删除模式的同时删除模式下的数据库对象。模式下的数据库对象。RESTRICT拒绝删除已定义了数据库对象的模拒绝删除已定义了数据库对象的模式。式。2.3 数据定义（续）第16页/共145页2.3 数据定义（续）3、基本表的定义、删除及修改、基本表的定义、删除及修改 基本表定义基本表定义 CREATE TABLE（列级约束列级约束 ,列级约束列级约束,表级完整性约束定义表级完整性约束定义）第17页/共145页CREATE TABLE Student(Sno char(7)PRIMARY KEY,Sname char(10)UNIQUE,Ssexchar(2)CHECK(Ssex=男 or Ssex=女),Sagetinyint CHECK(Sage=15 and Sage=45),Sdept char(20)DEFAULT 计算机系）2.3 数据定义（续）第18页/共145页CREATE TABLE Course(Cno char(4)PRIMARY KEY,Cname varchar(40)NOT NULL,Cpnochar(4),Ccredittinyint,FOREIGN KEY(Cpno)REFERENCES Course(Cno)2.3 数据定义（续）表级完整性约束条件表级完整性约束条件,列列Cpno中数据必须参中数据必须参照列照列Cno中的数据。中的数据。第19页/共145页CREATE TABLE SC(Sno char(7),Cno char(4),Grade numeric(5,2),PRIMARY KEY(Sno,Cno),FOREIGN KEY(Cno)REFERENCES Course(Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),)2.3 数据定义（续）表级完整性约束条件表级完整性约束条件,参照完整性规则参照完整性规则第20页/共145页第21页/共145页2.3 数据定义（续）表结构的修改表结构的修改修改列的定义修改列的定义ALTER TABLE 表名表名 ALTER COLUMN 如：修改如：修改Student的的Sdept为为40个字符宽度个字符宽度ALTER TABLE Student ALTER COLUMN Sdept char(40)第22页/共145页 增加新列增加新列ALTER TABLE 表名表名 ADD 约束约束如：在如：在Student中增加中增加Address（家庭住址）列（家庭住址）列ALTER TABLE Student Add Address varchar(30)2.3 数据定义（续）第23页/共145页2.3 数据定义（续）删除列删除列ALTER TABLE 表名表名 DROP COLUMN 如：删除如：删除Student的的Sage列列ALTER TABLE Student DROP COLUMN Sage第24页/共145页 添加约束添加约束ALTER TABLE 表名表名 ADD CONSTRAINT constraint_name 约束类型约束类型()如：在如：在Student中增加中增加Sno为主关键字为主关键字ALTER TABLE Student Add PRIMARY KEY(Sno)如：添加如：添加SC的的Sno列的外码约束为列的外码约束为Student的的SnoALTER TABLE SC ADD FOREIGN KEY(Sno)REFRENCES Student(Sno)2.3 数据定义（续）约束名也可以省略第25页/共145页2.3 数据定义（续）例如：增加课程名必须惟一的约束条件例如：增加课程名必须惟一的约束条件ALTER TABLE CourseADD CONSTRAINT UnqCnameUNIQUE(Cname)例如：增加成绩约束例如：增加成绩约束,成绩在成绩在0100之间之间ALTER TABLE SCADD CONSTRAINT ChkGRADECHECK(Grade=0 AND Grade=100)第26页/共145页2.3 数据定义（续）删除约束删除约束ALTER TABLE 表名表名 DROP 约束名约束名如：如：ALTER TABLE COURSE DROP UnqCname如：如：ALTER TABLE SC DROP ChkGRADE第27页/共145页2.3 数据定义（续）表的删除表的删除 DROP TABLE RESTRICT|CASCADE RESTRICT表示删除表时有限制表示删除表时有限制,若表建立了与其若表建立了与其他表的约束、触发器、视图等关系或对象他表的约束、触发器、视图等关系或对象,则不允许删则不允许删除。除。CASCADE表示删除时将与该表相关的约束、视图等表示删除时将与该表相关的约束、视图等一起删除一起删除 如：删除表如：删除表Student DROP TABLE Student第28页/共145页2.3 数据定义（续）4、索引的建立与删除、索引的建立与删除 索引是加快查询效率的一种手段。索引是加快查询效率的一种手段。索引是按照基本表中某个（某些）属性列上的值索引是按照基本表中某个（某些）属性列上的值进行排序进行排序,以提供多种查找途径。以提供多种查找途径。一个基本表可以建立一个或多个索引。一个基本表可以建立一个或多个索引。第29页/共145页2.3 数据定义（续）4、索引的建立与删除、索引的建立与删除 建立索引建立索引 CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_nameON table|view (column ASC|DESC ,.n )第30页/共145页2.3 数据定义（续）例如：例如：(1)在在Student表上建立按学号升序排列的索引表上建立按学号升序排列的索引 CREATE INDEX Sno_A ON Student(Sno)(2)在在Course表的课程名建立惟一索引表的课程名建立惟一索引 CREATE UNIQUE Cname_U ON Course(Cname)第31页/共145页2.3 数据定义（续）例如：例如：(3)在在SC表上建立按学号升序表上建立按学号升序,课程号降序排列的索引课程号降序排列的索引 CREATE INDEX SC_A ON SC(Sno,Cno DESC)第32页/共145页2.3 数据定义（续）删除索引删除索引 DROP INDEX 表名表名.索引名索引名例如：例如：删除删除SC表上的表上的SC_A索引索引 DROP INDEX SC.SC_A注意：索引由系统进行维护注意：索引由系统进行维护,当基本表中数据发生变化时当基本表中数据发生变化时,系统需要对根据数据重新维护索引系统需要对根据数据重新维护索引,因此过多建立索引也会因此过多建立索引也会降低数据库的使用效率。降低数据库的使用效率。第33页/共145页练习：用SQL语句定义下列基本表第34页/共145页CREATE TABLE class(CLASSNAME CHAR(20)PRIMARY KEY,MAXCREDIT SMALLINT,MINCREDIT SMALLINT)CREATE TABLE student(STUDENTID CHAR(6)PRIMARY KEY,NAME CHAR(8),SEX CHAR(2),BIRTHDAY DATETIME,CLASSNAME,FOREIGN KEY(CLASSNAME)REFERENCES CLASS(CLASSNAME)第35页/共145页练习：用SQL语句修改基本表的结构1、在班级信息表、在班级信息表(class)增加约束：最大学分限制大于增加约束：最大学分限制大于最小学分限制最小学分限制ALTER TABLE classADD CONSTRAINT check_cCHECK(MAXCREDITMINCREDIT)第36页/共145页2、在学生信息表、在学生信息表(student)增加一个属性列：增加一个属性列：列名：列名：SAGE 数据类型：整型数据类型：整型 ALTER TABLE student ADD SAGE INT3、在学生信息表、在学生信息表(student)增加一个约束：增加一个约束：SAGE必须介于必须介于1545之间之间ALTER TABLE student ADD CONSTRAINT CHECK_SCHECK(SAGE=15 AND SAGE=45)第37页/共145页2.4 数据查询功能1、SELECT的基本结构的基本结构SELECT FROMWHERE GROUP BYHAVINGORDER BY第38页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例1 查询全体学生的学号、姓名查询全体学生的学号、姓名SELECT SNO,SNAME FROM STUDENT注意：输出列的顺序可以与表中定义的列顺序不同。注意：输出列的顺序可以与表中定义的列顺序不同。第39页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例2 查询全部列查询全部列SELECT*FROM STUDENTSELECT*FROM COURSESELECT*FROM SC第40页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例3 查询全体学生的姓名和出生年份（需要进行计算的输出列）查询全体学生的姓名和出生年份（需要进行计算的输出列）SELECT SNAME,2009-SAGE FROM STUDENT为输出列起个列名：为输出列起个列名：SELECT SNAME,2009-SAGE AS BIRTH FROM STUDENT计算出的出生年份计算出的出生年份第41页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例4 查询计算机系全体学生的姓名查询计算机系全体学生的姓名例例5 查询年龄小于查询年龄小于20岁的学生的姓名和年龄岁的学生的姓名和年龄SELECT SNAME FROM STUDENT WHERE SDEPT=计算机系计算机系SELECT SNAME,SAGE FROM STUDENTWHERE SAGE20第42页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例6 查询计算机系年龄小于查询计算机系年龄小于20岁学生的姓名岁学生的姓名例例7 查询年龄介于查询年龄介于22岁到岁到24岁的学生姓名和年岁的学生姓名和年龄龄SELECT SNAME FROM STUDENT WHERE SDEPT=计算机系计算机系 AND SAGE20SELECT SNAME,SAGE FROM STUDENTWHERE SAGE=22或：或：Between and 一般用于数值型的比较一般用于数值型的比较SELECT SNAME,SAGE FROM STUDENTWHERE SAGE BETWEEN 22 AND 24第43页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例8 查询查询SC表中有哪些学生选修了课程。只需给出学号（消除重表中有哪些学生选修了课程。只需给出学号（消除重复值）复值）例例9 查询信息系、数学系和计算机系学生的姓名和性别。查询信息系、数学系和计算机系学生的姓名和性别。SELECT DISTINCT SNO FROM SCSELECT SNAME,SSEX FROM STUDENTWHERE SDEPT IN(信息系信息系,数学系数学系,计算计算机系机系)第44页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例10 查询姓李的同学的基本信息。查询姓李的同学的基本信息。例例11 查询姓李、张、刘的同学的信息。查询姓李、张、刘的同学的信息。例例12 查询不姓李、张、刘的同学的信息。查询不姓李、张、刘的同学的信息。SELECT*FROM STUDENTWHERE SNAME LIKE 李李%SELECT*FROM STUDENTWHERE SNAME LIKE 张李刘张李刘%SELECT*FROM STUDENTWHERE SNAME LIKE 张李刘张李刘%第45页/共145页2.4 数据查询功能（续）2、简单查询、简单查询例例13 查询无考试成绩的学生的学号。查询无考试成绩的学生的学号。例例14 查询有考试成绩的学生的学号和成绩。查询有考试成绩的学生的学号和成绩。SELECT SNO FROM SCWHERE GRADE IS NULLSELECT SNO FROM SCWHERE GRADE IS NOT NULL第46页/共145页2.4 数据查询功能（续）3、查询结果排序、查询结果排序例例15 将学生按年龄由小到大排序。将学生按年龄由小到大排序。例例16 输出选修输出选修c02课程学生的成绩课程学生的成绩,按降序排序。按降序排序。SELECT*FROM STUDENTORDER BY SAGESELECT*FROM SCWHERE CNO=C02ORDER BY GRADE DESC第47页/共145页2.4 数据查询功能（续）4、使用集合函数统计查询、使用集合函数统计查询例例17 统计学生的总人数。统计学生的总人数。例例18 统计选修了课程的学生的人数。统计选修了课程的学生的人数。SELECT COUNT(*)FROM STUDENTSELECT COUNT(DISTINCT SNO)FROM SC第48页/共145页2.4 数据查询功能（续）4、使用集合函数统计查询、使用集合函数统计查询例例19 计算学号为计算学号为200215121学生的考试成绩总和。学生的考试成绩总和。例例20 计算计算c01课程的考试平均成绩。课程的考试平均成绩。SELECT SUM(GRADE)FROM SCWHERE SNO=200215121SELECT AVG(GRADE)FROM SCWHERE CNO=C01第49页/共145页2.4 数据查询功能（续）4、使用集合函数统计查询、使用集合函数统计查询例例21 查询最大的学生年龄。查询最大的学生年龄。例例22SELECT MAX(SAGE)FROM STUDENT第50页/共145页2.4 数据查询功能（续）5 5、分组查询、分组查询 分组查询是根据某个列或某些列的值将记录分组分组查询是根据某个列或某些列的值将记录分组,分组后分组后,聚集函聚集函数对每组数据都进行计算数对每组数据都进行计算,从而获得各组的统计结果。从而获得各组的统计结果。第51页/共145页2.4 数据查询功能（续）5、分组查询、分组查询例例23 统计每门课程的选课人数。统计每门课程的选课人数。例例24 查询每名学生的选课门数和平均成绩。查询每名学生的选课门数和平均成绩。SELECT CNO,COUNT(*)FROM SCGROUP BY CNOSELECT SNO,COUNT(*),AVG(GRADE)FROM SC GROUP BY SNO第52页/共145页2.4 数据查询功能（续）5、分组查询（对分组结果进行筛选）、分组查询（对分组结果进行筛选）例例25 查询选修了查询选修了3门以上课程的学生的学号。门以上课程的学生的学号。例例26 查询选课门数大于或等于查询选课门数大于或等于4门的学生的平均成绩和选课门数。门的学生的平均成绩和选课门数。SELECT SNO FROM SCGROUP BY SNOHAVING COUNT(*)3SELECT SNO,COUNT(*),AVG(GRADE)FROM SC GROUP BY SNOHAVING COUNT(*)=4第53页/共145页Colledge数据库介绍：关系图及字段描述数据库介绍：关系图及字段描述第54页/共145页学号 课程代号 成绩 任课教师工号练习：练习：1、查询工号为、查询工号为E0002的教师所承担课程的课程号。的教师所承担课程的课程号。2、查询课程代号为、查询课程代号为1301的成绩不及格的学生学号和成绩。的成绩不及格的学生学号和成绩。cj$SELECT XH,CJ FROM cj$WHERE KCDH=1301 AND CJ=60GROUP BY XH ORDER BY 3 DESC第58页/共145页学号 课程代号 成绩 任课教师工号练习：练习：8、查询选修、查询选修通过通过门数超过门数超过3门门,且平均分在且平均分在70以上的学生的学以上的学生的学号号,选修门数选修门数,平均成绩。（按成绩从高到低排序）。平均成绩。（按成绩从高到低排序）。SELECT XH,COUNT(*),AVG(CJ)FROM cj$WHERE CJ=60GROUP BY XH HAVING COUNT(*)3 AND AVG(CJ)=70ORDER BY 3 DESC第59页/共145页若查询的字符串中本来就含有统配符若查询的字符串中本来就含有统配符%或或_,可以使可以使用用ESCAPE对统配符进行转义。对统配符进行转义。例如：查询课程名例如：查询课程名DB_DESIGN开头的课程号与学开头的课程号与学分分2.4 数据查询功能（续）SELECT CNO,cCREDITFROM COURSEWHERE CNAME LIKE DB_DESIGN%ESCAPE第60页/共145页2.4 数据查询功能（续）6、连接查询、连接查询若在一个查询请求中若在一个查询请求中,涉及到多个表中的数据涉及到多个表中的数据,则需则需要进行连接查询。要进行连接查询。按连接方式分：按连接方式分：内连接内连接 INNER JOIN 左外连接左外连接 LEFT JOIN 右外连接右外连接 RIGHT JOIN 全连接全连接FULL JOIN第61页/共145页2.4 数据查询功能（续）6、连接查询、连接查询 内连接内连接 INNER JOIN 例例27 查询每个学生的基本信息及其选课情况。查询每个学生的基本信息及其选课情况。SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADEFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO第62页/共145页2.4 数据查询功能（续）6、连接查询、连接查询例例27 查询每个学生的基本信息及其选课情况。查询每个学生的基本信息及其选课情况。或写成：或写成：SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADEFROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO第63页/共145页2.4 数据查询功能（续）6、连接查询、连接查询例例28 查询计算机系学生的选课情况查询计算机系学生的选课情况,要求输出姓名要求输出姓名,课程号课程号,成绩。成绩。SELECT SNAME,CNO,GRADEFROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO ANDSDEPT=计算机系计算机系第64页/共145页2.4 数据查询功能（续）6、连接查询、连接查询例例29 查询计算机系学生的选课情况查询计算机系学生的选课情况,要求输出姓名要求输出姓名,课程名课程名,成绩。成绩。(多表连接多表连接)SELECT SNAME,CNAME,GRADEFROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO ANDSC.CNO=COURSE.CNO AND SDEPT=计算机系计算机系第65页/共145页2.4 数据查询功能（续）6、连接查询、连接查询左外连接：左外连接：LEFT JOIN 例例30 查询学生的选课情况查询学生的选课情况,要求输出学号要求输出学号,姓名姓名,课程代号课程代号,成绩。成绩。(包括未选课学生的包括未选课学生的信息信息)SELECT student.SNO,SNAME,CNO,GRADEFROM STUDENT LEFT JOIN SCON STUDENT.SNO=SC.SNO ORDER BY student.SNO第66页/共145页Colledge数据库介绍：关系图及字段描述数据库介绍：关系图及字段描述第67页/共145页第68页/共145页第69页/共145页两个表的左外连接两个表的左外连接,若右表中没有与左表相对应的若右表中没有与左表相对应的记录记录,则填充则填充NULL值。值。第70页/共145页2.4 数据查询功能（续）6、连接查询、连接查询右外连接：右外连接：RIGHT JOIN 例例31 查询教师的工资情况查询教师的工资情况SELECT GZ$.*,JS$.XMFROM GZ$RIGHT JOIN JS$ON GZ$.GH=JS$.GHORDER BY XM第71页/共145页两个表的右外连接两个表的右外连接,若左表中没有与右表相对应的若左表中没有与右表相对应的记录记录,则填充则填充NULL值。值。第72页/共145页2.4 数据查询功能（续）6、连接查询、连接查询全连接：全连接：FULL JOIN 例例32 查询教师的工资情况查询教师的工资情况SELECT GZ$.*,JS$.*FROM GZ$FULL JOIN JS$ON GZ$.GH=JS$.GH第73页/共145页2.4 数据查询功能（续）6、连接查询、连接查询自连接：一个表与其自身进行连接自连接：一个表与其自身进行连接,由于是同一个表由于是同一个表所以连接时必须用别名进行区分所以连接时必须用别名进行区分例例33 查询各门课程的先修课名称情况查询各门课程的先修课名称情况SELECT FIRST.CNO,FIRST.CNAME,SECOND.CNAME AS 先修课先修课FROM COURSE FIRST,COURSE SECONDWHERE FIRST.CPNO=SECOND.CNO第74页/共145页SELECT*FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c2=tab2.c2 RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4 ON tab3.c1=tab4.c1 ON tab2.c3=tab4.c3 多个表多种连接方式的查询多个表多种连接方式的查询tab1c2.tab2c2c3.tab4c1c3.tab3c1.纽带表第75页/共145页SELECT XS$.XM,KC$.KCM,cj$.CJFROM XS$LEFT JOIN cj$ON XS$.XH=cj$.XH RIGHT JOIN KC$ON cj$.KCDH=KC$.KCDHORDER BY 3查询全部课程课程名及全体学生的姓名与课程成绩的信息，并按成绩小到大排序第76页/共145页对于多表连接对于多表连接,若连接方式为内连接若连接方式为内连接,则多采用将连则多采用将连接条件写在接条件写在where子句中的形式子句中的形式,更加简洁。更加简洁。如：如：SELECT SNAME,CNAME,GRADEFROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO ANDSC.CNO=COURSE.CNO AND SDEPT=计算机系计算机系第77页/共145页对于多表连接对于多表连接,若多个表中均有输出列若多个表中均有输出列,则必须在列名则必须在列名指定输出列来自于哪个表。指定输出列来自于哪个表。如：如：SELECT STUDENT.SNO,SNAME,CNAME,GRADE FROM STUDENT,SC,COURSE WHERE STUDENT.SNO=SC.SNO ANDSC.CNO=COURSE.CNO AND SDEPT=计算机系计算机系第78页/共145页练习第79页/共145页SELECT XS$.XH,XM,AVG(CJ),COUNT(cj$.KCDH)FROM XS$,cj$WHERE XS$.XH=cj$.XH GROUP BY XS$.XH,XM order by AVG(CJ)desc2 2、输出学生成绩单、输出学生成绩单,要求输出学号要求输出学号,姓名姓名,平均分平均分,选修门数选修门数,按平均分降序排序。按平均分降序排序。SELECT XS$.XH,XM,AVG(CJ),COUNT(cj$.KCDH)FROM XS$,cj$WHERE XS$.XH=cj$.XHGROUP BY XS$.XH,XM order by XS$.XH1 1、输出学生成绩单、输出学生成绩单,要求输出学号要求输出学号,姓名姓名,平均成绩平均成绩,所学课所学课程数，按学号升序排序。程数，按学号升序排序。第80页/共145页练习第81页/共145页SELECT XH,XM,JGFROM XS$,ZY$WHERE XS$.ZYDH=ZY$.ZYDH AND ZYMC=网络工程 SELECT XIMING,ZYMC,COUNT(*)FROM XS$,XIM$,ZY$WHERE XS$.ZYDH=ZY$.ZYDH AND XS$.XDH=XIM$.XDHGROUP BY XIMING,ZYMCORDER BY 13 3、输出网络工程专业的学生的学号、输出网络工程专业的学生的学号,姓名姓名,籍贯。籍贯。4 4、统计各专业的学生人数、统计各专业的学生人数,要求输出系名要求输出系名,专业名专业名,总人数总人数,按按系名排序。系名排序。第82页/共145页练习第83页/共145页SELECT JS$.GH,XM,XB,ZC,KCM,SKDDFROM JS$,RK$,KC$,ZC$WHERE JS$.GH=RK$.GH AND JS$.ZCDH=ZC$.ZCDH AND RK$.KCDH=KC$.KCDH ORDER BY 1SELECT XIMING,ZC,COUNT(*)FROM JS$,XIM$,ZC$WHERE JS$.XDH=XIM$.XDH AND JS$.ZCDH=ZC$.ZCDHGROUP BY XIMING,ZCORDER BY 1,2 5 5、查询教师的任课情况、查询教师的任课情况,要求输出教师工号要求输出教师工号,姓名姓名,性别性别,职称职称,课程名课程名,上课地点上课地点,按工号排序。按工号排序。6 6、统计各系教师职称情况、统计各系教师职称情况,要求输出系名要求输出系名,职称名职称名,人数人数,按系按系名、职称排序。名、职称排序。第84页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询例例34 查询与刘晨在同一个系的学生。查询与刘晨在同一个系的学生。若子查询结果只有若子查询结果只有一个值一个值,可以用可以用=Select Sno,Sname,Sdeptfrom Studentwhere Sdept in(Select Sdept from StudentWhere Sname=刘晨刘晨)第85页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询例例35 查询有成绩大于查询有成绩大于90分的学生的学号和姓名。分的学生的学号和姓名。Select Sno,Snamefrom Studentwhere Sno in(Select Sno from SCWhere Grade90)第86页/共145页2.4 数据查询功能（续）涉及到多个表的查询涉及到多个表的查询,除了可以使用多表连接查询除了可以使用多表连接查询以外以外,还可以利用嵌套子查询来完成。还可以利用嵌套子查询来完成。由于连接查询比较耗时由于连接查询比较耗时,当表中记录数很大时当表中记录数很大时,嵌套嵌套查询的效率可能会比连接查询快。查询的效率可能会比连接查询快。第87页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询例例36 查询选修了查询选修了c02课程且成绩高于此课程的平均成绩的学生的学号和成绩。课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECT SNO,GRADE FROM SCWHERE CNO=C02 AND GRADE(SELECT AVG(GRADE)FROM SCWHERE CNO=C02)第88页/共145页相关子查询 例例:查询每个学生成绩高于他选修课的平均成绩的学生的学号和课程号及成绩。查询每个学生成绩高于他选修课的平均成绩的学生的学号和课程号及成绩。select sno,cno,grade from sc x where grade=(select avg(grade)from sc y where x.sno=y.sno)第89页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询例例37 查询其他系中比信息系某一学生年龄小查询其他系中比信息系某一学生年龄小的学生姓名和年龄的学生姓名和年龄SELECT Sname,Sage FROM Student WHERE Sdept信息系信息系 ANDSage ANY大于子查询结果中的某个值 (min)ALL大于子查询结果中的所有值(max)ANY小于子查询结果中的某个值 (max)ALL小于子查询结果中的所有值(=ANY大于等于子查询结果中的某个值(=min)=ALL 大于等于子查询结果中的所有值(=max)=ANY小于等于子查询结果中的某个值 (=max)=ALL 小于等于子查询结果中的所有值 (=min)=ANY等于子查询结果中的某个值 (IN)=ALL等于子查询结果中的所有值（通常没有实际意义）(-)!=（或）ANY 不等于子查询结果中的某个值(-)!=（或）ALL 不等于子查询结果中的任何一个值(NOT IN)第91页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询带带EXISTS谓词的子查询谓词的子查询例例38 查询所有选修了查询所有选修了C02课程的学生的姓名课程的学生的姓名SELECT SNAMEFROM STUDENT WHERE EXISTS(SELECT*FROM SC WHERE SNO=STUDENT.SNO ANDCNO=C02)第92页/共145页用in子查询SELECT SNAMEFROM STUDENT WHERE SNO IN(SELECT SNO FROM SC WHERE CNO=C02)或：用连接查询SELECT SNAME FROM STUDENT,SCWHERE STUDENT.SNO=SC.SNO AND CNO=C02第93页/共145页查询所有选修了查询所有选修了数据库数据库课程的学生的姓名课程的学生的姓名SELECT SNAMEFROM STUDENT WHERE SNO IN(SELECT SNO FROM SC WHERE CNO=(select cno from course where cname=数据库)）第94页/共145页2.4 数据查询功能（续）7、嵌套子查询、嵌套子查询例例39 查询所有未选修查询所有未选修C02课程的学生的姓名课程的学生的姓名SELECT SNAMEFROM STUDENT WHERE NOT EXISTS(SELECT*FROM SC WHERE SNO=STUDE
