《数据库原理》课件1第四章 结构化查询语言.ppt

1、 教学内容：教学内容：教学重点：教学重点：SQLSQL的数据查询；的数据查询；嵌入式嵌入式SQLSQL的使用的使用。SQLSQL数据库的体系结构，数据库的体系结构，SQLSQL的组成；的组成；SQLSQL的数据定义：的数据定义：SQLSQL模式、基本表和索引的创建和撤销；模式、基本表和索引的创建和撤销；SQLSQL的数据查询：的数据查询：SELECTSELECT语句的句法和使用；语句的句法和使用；SQLSQL的数据更新：插入、删除和修改语句；的数据更新：插入、删除和修改语句；视图的创建和撤消，对视图更新操作的限制；视图的创建和撤消，对视图更新操作的限制；嵌入式嵌入式SQLSQL：预处理方式：预

2、处理方式,使用规定使用规定,使用技术使用技术,动态动态SQLSQL语句。语句。一、一、SQLSQL数据库的体系结构数据库的体系结构 SQL数据库的体系结构基本上也是三级模式结构。数据库的体系结构基本上也是三级模式结构。在在SQL 中中:外模式外模式对应于对应于视图视图，模式模式对应于对应于基本表基本表，元组称为元组称为“行行”，属性称为属性称为“列列”，内模式内模式对应于对应于存储文件存储文件。用户用户1用户用户2用户用户3用户用户4视图视图1视图视图2基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2存储文件存储文件3存储文件存储文件4 二、二、SQ

3、LSQL的组成的组成 SQLSQL主要分成四个部分：主要分成四个部分：数据定义；数据定义；数据操纵；数据操纵；数据控制；数据控制；嵌入式嵌入式SQLSQL的使用。的使用。三、三、SQLSQL的主要特点的主要特点 1.1.一体化；一体化；2.2.两种使用方式，统一的语法结构；两种使用方式，统一的语法结构；3.3.高度非过程化；高度非过程化；4.4.语言简洁，易学易用。语言简洁，易学易用。一、一、SQLSQL模式的创建和撤消模式的创建和撤消1.SQL1.SQL模式的创建模式的创建 CREATE SCHEMA CREATE SCHEMA AUTHORIZATION AUTHORIZATION 2.S

4、QL2.SQL模式的撤消模式的撤消 DROP SCHEMA DROP SCHEMA CASCADE|RESTRICT CASCADE|RESTRICT CREATE DATABASE CREATE DATABASE DROP DATABASE DROP DATABASE 例：创建工程数据库例：创建工程数据库PROJECTPROJECT CREATE DATABASECREATE DATABASE PROJECT PROJECT 例：撤消工程数据库例：撤消工程数据库PROJECTPROJECT DROP DATABASE PROJECT DROP DATABASE PROJECT 例：创建学生数

5、据库例：创建学生数据库 STUDENTSTUDENT CREATE DATABASECREATE DATABASE STUDENT STUDENT 例：撤消学生数据库例：撤消学生数据库 STUDENTSTUDENT DROP DATABASEDROP DATABASE STUDENT STUDENT 二、基本表的创建、修改和撤消二、基本表的创建、修改和撤消 1 1.基本表的创建基本表的创建 CREATE TABLE SQLCREATE TABLE SQL 模式名模式名.基本表名基本表名 （列名（列名 类型，类型，完整性约束，完整性约束，）完整性规则主要有三种：完整性规则主要有三种：主键子句主键

6、子句（PRIMARY KEYPRIMARY KEY）；）；检查子句检查子句（CHECKCHECK）；）；外键子句外键子句（FOREIGN KEYFOREIGN KEY）。）。SQL SQL提供的基本数据类型提供的基本数据类型:数值型数值型:INTEGER INTEGER 长整数（也可写成长整数（也可写成INTINT）SMALLINT SMALLINT 短整数短整数 REAL REAL 取决于机器精度的浮点数取决于机器精度的浮点数 DOUBLE PRECISION DOUBLE PRECISION 取决于机器精度的双精度取决于机器精度的双精度浮点数浮点数:FLOAT(n)FLOAT(n)浮点数，

7、精度至少为浮点数，精度至少为n n位数字位数字;NUMERICNUMERIC（p,dp,d）定点数，由定点数，由p p位数字（不包括符号、小数点）位数字（不包括符号、小数点）组成，小数点后面有组成，小数点后面有d d位数字位数字;也可写成也可写成:DECIMAL:DECIMAL（P P，d d）或）或DECDEC（P P，d d）SQL SQL提供的基本数据类型提供的基本数据类型 字符串型字符串型:CHAR CHAR（n n）长度为长度为n n的定长字符串的定长字符串 VARCHAR(n)VARCHAR(n)具有最大长度为具有最大长度为n n的变长字符串的变长字符串位串型位串型:BITBIT（

8、n n）长度为长度为n n的二进制位串的二进制位串 BIT VARYINGBIT VARYING（n n）最大长度为最大长度为 n n的变长二进制位的变长二进制位时间型时间型:DATE DATE 日期，包含年、月、日日期，包含年、月、日,YYYY,YYYYMMMMDDDD TIME TIME 时间，包含时、分、秒，时间，包含时、分、秒，HH:MM:SSHH:MM:SS【例例4.14.1】在有关工程项目的数据库中，有四个关系，其结构如下：供应商关系：S(SNO，SNAME，SADDR)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO，JNAME，JCITY，B

9、ALANCE)供应情况关系：SPJ(SNO，PNO，JNO，PRICE，QTY)例例:工程项目数据库工程项目数据库PROJECTPROJECT中有四个关系，其结构如下：中有四个关系，其结构如下：供应商关系：供应商关系：S(S(SNOSNO，SNAMESNAME，SADDR)SADDR)零件关系：零件关系：P(P(PNOPNO，PNAMEPNAME，COLORCOLOR，WEIGHT)WEIGHT)工程项目关系：工程项目关系：J(J(JNOJNO，JNAMEJNAME，JCITYJCITY，BALANCE)BALANCE)供应情况关系：供应情况关系：SPJ(SPJ(SNOSNO，PNOPNO，J

10、NOJNO，PRICEPRICE，QTY)QTY)可用下列语句创建表可用下列语句创建表S:S:CREATE TABLE SCREATE TABLE S(SNO CHAR(4)NOT NULL,(SNO CHAR(4)NOT NULL,SNAME CHAR(20)NOT NULL,SNAME CHAR(20)NOT NULL,SADDR CHAR(20)SADDR CHAR(20)，PRIMARY KEY(SNO);PRIMARY KEY(SNO);可用下列语句创建表可用下列语句创建表P:P:CREATE TABLE PCREATE TABLE P (PNO CHAR(4)NOT NULL,(P

11、NO CHAR(4)NOT NULL,PNAME CHAR(20)NOT NULL,PNAME CHAR(20)NOT NULL,COLOR CHAR(8),COLOR CHAR(8),WEIGHT SMALLINT,WEIGHT SMALLINT,PRIMARY KEY(PNO);PRIMARY KEY(PNO);可用下列语句创建表可用下列语句创建表S:S:CREATE TABLE J CREATE TABLE J (JNO CHAR(4)NOT NULL,(JNO CHAR(4)NOT NULL,JNAME CHAR(20),JNAME CHAR(20),JCITY CHAR(20),JC

12、ITY CHAR(20),BALANCE NUMERIC(7,2),BALANCE NUMERIC(7,2),PRIMARY KEY(JNO);PRIMARY KEY(JNO);可用下列语句创建表可用下列语句创建表SPJ:SPJ:CREATE TABLE SPJCREATE TABLE SPJ(SNO CHAR(4)NOT NULL,(SNO CHAR(4)NOT NULL,PNO CHAR(4)NOT NULL,PNO CHAR(4)NOT NULL,JNO CHAR(4)NOT NULL,JNO CHAR(4)NOT NULL,PRICE NUMERIC(7,2),PRICE NUMERI

13、C(7,2),QTY SMALLINTQTY SMALLINT，PRIMARY KEY(SNO,PNO,JNO),PRIMARY KEY(SNO,PNO,JNO),FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(PNO)REFERENCES P(PNO),FOREIGN KEY(PNO)REFERENCES P(PNO),FOREIGN KEY(JNO)REFERENCES J(JNO),FOREIGN KEY(JNO)REFERENCES J(JNO),CONSTRAINT C_Q

14、TY CHECK(QTY BETWEEN 0 AND 10000)CONSTRAINT C_QTY CHECK(QTY BETWEEN 0 AND 10000)【例例4.14.1】在有关工程项目的数据库中，有四个关系，其结构如下：供应商关系：S(SNO，SNAME，SADDR)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO，JNAME，JCITY，BALANCE)供应情况关系：SPJ(SNO，PNO，JNO，PRICE，QTY)例例:学生数据库学生数据库STUDENTSTUDENT中有三个关系，其结构如下：中有三个关系，其结构如下：学生关系：学生关系：S(

15、S(SNOSNO，SNAMESNAME，AGEAGE，SEXSEX，SDEPT)SDEPT)课程关系：课程关系：C(C(CNOCNO，CNAMECNAME，CDEPTCDEPT，TNAME)TNAME)学习关系：学习关系：S SC(C(SNOSNO，CNOCNO，GRADE)GRADE)可用下列语句创建表可用下列语句创建表S:S:CREATE TABLECREATE TABLE S S(SNO CHAR(4)NOT NULL,(SNO CHAR(4)NOT NULL,SNAME CHAR(20)NOT NULL,SNAME CHAR(20)NOT NULL,AGE CHAR(2),AGE CH

16、AR(2),SEX CHAR(2),SEX CHAR(2),SDEPT CHAR(10),SDEPT CHAR(10),PRIMARY KEYPRIMARY KEY(SNO(SNO););可用下列语句创建表可用下列语句创建表C:C:CREATE TABLE CREATE TABLE C C (CNO CHAR(4)NOT NULL,(CNO CHAR(4)NOT NULL,CNAME CHAR(20)NOT NULL,CNAME CHAR(20)NOT NULL,CDEPT CHAR(10),CDEPT CHAR(10),TNAME CHAR(8),TNAME CHAR(8),PRIMARY

17、KEY PRIMARY KEY(CNO);(CNO);主键子句主键子句主键子句主键子句可用下列语句创建表可用下列语句创建表SC:SC:CREATE TABLECREATE TABLE SC SC(SNO CHAR(4)NOT NULL,(SNO CHAR(4)NOT NULL,CNO CHAR(4)NOT NULL,CNO CHAR(4)NOT NULL,GRADE NUMERIC(7,2),GRADE NUMERIC(7,2),PRIMARY KEY(SNO,CNO),PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN

18、 KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(CNO)REFERENCES P(CNO),FOREIGN KEY(CNO)REFERENCES P(CNO),CONSTRAINT SC_GRADE CONSTRAINT SC_GRADE CHECK(GRADE BETWEEN 0 AND 100);CHECK(GRADE BETWEEN 0 AND 100);主键子句主键子句外键子句外键子句检查子句检查子句2.2.基本表结构的修改基本表结构的修改 增加新的属性：增加新的属性：删除原有的属性：删除原有的属性：ALTER TABLE ALTER TABLE 基本表名

19、基本表名 ADD ADD 新属性名新属性名 新属性类型新属性类型 例例:在基本表在基本表S S中增加一个电话号码（中增加一个电话号码（TELETELE）属性语句如下：）属性语句如下：ALTER TABLE S ADD TELE CHAR(12);ALTER TABLE S ADD TELE CHAR(12);ALTER TABLE ALTER TABLE 基本表名基本表名 DROP DROP 属性名属性名 CASCADE|RESTRICTCASCADE|RESTRICT例例:在表在表S S中删除电话号码（中删除电话号码（TELETELE）属性，并）属性，并且将引用该属性的所有视图和约束也一起删

20、且将引用该属性的所有视图和约束也一起删除，可用下列语句：除，可用下列语句：ALTER TABLE S DROP TELE CASCADEALTER TABLE S DROP TELE CASCADE；删除指定的完整性约束条件，其句法如下：删除指定的完整性约束条件，其句法如下：ALTER TABLE ALTER TABLE 基本表名基本表名 DROP DROP 约束名；约束名；例例:在基本表在基本表SCSC中删除对属性中删除对属性GRADEGRADE的约束，可用下列语句：的约束，可用下列语句：ALTER TABLE SC DROP SC_ ALTER TABLE SC DROP SC_GRADE

21、 GRADE；另外，可以用下列语句增加（即恢复）对属性另外，可以用下列语句增加（即恢复）对属性GRADEGRADE的约束：的约束：ALTER TABLE SPJ ADD CONSTRAINT SC_GRADEALTER TABLE SPJ ADD CONSTRAINT SC_GRADE CHECK(GRADE BETWEEN 0 AND 100)CHECK(GRADE BETWEEN 0 AND 100)3.3.删除基本表删除基本表 删除基本表可以用删除基本表可以用“DROP TABLE”DROP TABLE”语句删除基本表。其句语句删除基本表。其句法如下：法如下：DROP TABLE DRO

22、P TABLE 基本表基本表CASCADE|RESTRICTCASCADE|RESTRICT此处的此处的CASCADECASCADE和和RESTRICTRESTRICT的语义同前面句法中的语义一样。的语义同前面句法中的语义一样。在一个基本表删除后，其所有数据也就丢失了，使用时在一个基本表删除后，其所有数据也就丢失了，使用时要谨慎。要谨慎。SQLSQL的数据查询的数据查询(SELECT(SELECT语句语句)是是SQLSQL的核心内容。的核心内容。一、一、SELECTSELECT语句的来历语句的来历 在关系代数中最常用的式子是下列表达式：在关系代数中最常用的式子是下列表达式：A1,A1,An,A

23、n(F F(R(R1 1R Rm m)这里这里R1R1、RmRm为关系，为关系，F F是公式，是公式，A1A1、AnAn为属性。为属性。为此为此SQLSQL设计成设计成 SELECT SELECT FROM-WHERE FROM-WHERE句型：句型：SELECT ASELECT A1 1，A An nFROM RFROM R1 1，R Rm mWHERE FWHERE F 二、二、SELECTSELECT语句格式：语句格式：SELECTSELECT DISTINCT DISTINCT 目标表的列名目标表的列名(或或)列表达式序列列表达式序列 FROM FROM 基本表名基本表名(或或)视图名

24、序列视图名序列|表引用表引用 WHERE WHERE 行条件表达式行条件表达式 GROUP BYGROUP BY 列名列名1 1序列序列 HAVING HAVING 组条件表达式组条件表达式 ORDER BYORDER BY 列名列名2 ASC|DESC2 ASC|DESC序列；序列；整个语句的执行过程如下：整个语句的执行过程如下：2.2.选取满足选取满足WHEREWHERE子句中给出的条件表达式的元组。子句中给出的条件表达式的元组。3.3.按按GROUPGROUP子句中指定列的值分组，同时提取满足子句中指定列的值分组，同时提取满足HAVINGHAVING子句中子句中 组条件表达式的那些组。组

25、条件表达式的那些组。4.4.按按SELECTSELECT子句中给出的列名或列表达式求值输出。子句中给出的列名或列表达式求值输出。5.ORDER5.ORDER子句对输出的目标表进行排序子句对输出的目标表进行排序,按附加说明按附加说明ASCASC升序排列，升序排列，或按或按DESCDESC降序排列。降序排列。1.1.读取读取FROMFROM子句中基本表、视图的数据，执行笛卡尔积操作。子句中基本表、视图的数据，执行笛卡尔积操作。SELECT SELECT语句中语句中:WHEREWHERE子句子句称为称为“行条件子句行条件子句”，GROUPGROUP子句子句称为称为“分组子句分组子句”，HAVINGH

26、AVING子句子句称为称为“组条件子句组条件子句”，ORDERORDER子句子句称为称为“排序子句排序子句”。在在WHEREWHERE子句的行条件表达式中可使用下列运算符：子句的行条件表达式中可使用下列运算符：算术比较运算符：算术比较运算符：，或！；，或！；逻辑运算符逻辑运算符：ANDAND，OROR，NOTNOT；集合成员资格运算符：集合成员资格运算符：ININ，NOT INNOT IN；谓词：谓词：EXISTSEXISTS，ALLALL，SOMESOME，UNIQUEUNIQUE；聚合函数：聚合函数：AVGAVG，MINMIN，MAXMAX，SUMSUM，COUNTCOUNT；集合运算符：

27、集合运算符：UNIONUNION，INTERSECTINTERSECT，EXCEPTEXCEPT。3.3.举例举例 （板书举例（板书举例 ）举例：举例：假设学生数据库中的关系模式如下：假设学生数据库中的关系模式如下：S（SNO,SNAME,AGE,SEX,SDEPT）C（CNO,CNAME,CDEPT,TNAME）SC（SNO,CNO,GRADE）试用试用SQLSQL表达下列每个查询语句。表达下列每个查询语句。.检索选修检索选修课程号为课程号为C2C2的学生的的学生的学号和成绩。学号和成绩。SELECT SELECT SNO,GRADE SNO,GRADE FROM SCFROM SC WHE

28、RE WHERE CNOCNO=C2C2;2.2.检索检索计算机软件计算机软件、姓、姓“李李”的全体的全体男男同学的同学的学号、姓名和出生年份。学号、姓名和出生年份。SELECT SELECT SNO,SNAMESNO,SNAME,2020-AGE,2020-AGE ASAS BIRTH_YEARBIRTH_YEAR FROM SFROM S WHERE WHERE SNAME SNAME LIKELIKE 李李%ANDAND SDEPT=SDEPT=计算机软件计算机软件 AND AND SEX=SEX=男男;列名列名 LIKE LIKE 字符串常数字符串常数其中：列名的类型必须是字符串其中：

29、列名的类型必须是字符串 或可变字符串。或可变字符串。字符串常数中通配符的含义如下：字符串常数中通配符的含义如下：%(%(百分号百分号):):表示可以与任意长度表示可以与任意长度 的字符串匹配。的字符串匹配。_(_(下划线下划线):):表示可以与任意单个表示可以与任意单个 字符匹配。字符匹配。所有其他的字符只代表自己。所有其他的字符只代表自己。别名别名3.3.检索检索选修课程号为选修课程号为C4C4的学生的的学生的学号和姓名学号和姓名。第一种方法：连接查询第一种方法：连接查询 SELECT SELECT S.SNO,SNAMES.SNO,SNAME FROM FROM S,SCS,SC WHER

30、E WHERE S.SNO=SC.SNOS.SNO=SC.SNO AND CNO=AND CNO=C4C4;第二种方法：嵌套查询第二种方法：嵌套查询 SELECT S.SNO,SNAMESELECT S.SNO,SNAME FROM S FROM S WHERE WHERE ININ (SELECT SNO(SELECT SNO FROM SC FROM SC WHERE CNO=C4 WHERE CNO=C4););第二种方法：嵌套查询第二种方法：嵌套查询 相关子查询相关子查询)SELECT S.SNO,SNAMESELECT S.SNO,SNAME FROM FROM S S WHERE

31、WHERE C4C4 IN IN (SELECT (SELECT CNOCNO FROM FROM SCSC WHERE WHERE S.SNO=SC.SNOS.SNO=SC.SNO););第三种方法：使用存在量词的嵌套第三种方法：使用存在量词的嵌套 SELECT S.SNO,SNAMESELECT S.SNO,SNAME FROM FROM S S WHERE WHERE (SELECT (SELECT*FROM FROM SCSC WHERE WHERE S.SNO=SC.SNOS.SNO=SC.SNO AND SC.CNO=AND SC.CNO=C4C4););-外层查询外层查询-内层查

32、询内层查询(即即:子查询子查询)子查询依子查询依赖于外层赖于外层查询查询子查询依赖子查询依赖于外层查询于外层查询SELECT SELECT S.SNO,SNAMES.SNO,SNAME FROM S FROM S WHERE WHERE ININ (SELECT (SELECT SNOSNO FROM SC FROM SC WHERE WHERE CNOCNO IN IN (SELECT (SELECT CNOCNO FROM C FROM C WHERE WHERE CNAME=CNAME=数据库原理数据库原理););4.4.检索选修课程名为检索选修课程名为“数据库原理数据库原理”的学生的的

33、学生的学号和姓名。学号和姓名。-首先在首先在C C表中找出数据库原理课程的课程号表中找出数据库原理课程的课程号-然后在然后在SCSC表中找出选了该课程的学生的学号表中找出选了该课程的学生的学号-最后在最后在S S表中找出这些学生的姓名表中找出这些学生的姓名SELECT SELECT X.SNOX.SNOFROM SC X,SC YFROM SC X,SC YWHERE X.SNO=Y.SNOWHERE X.SNO=Y.SNO AND AND X.CNO=X.CNO=C2C2 AND Y.CNO=AND Y.CNO=C4C4;5.5.检索选修课程号为检索选修课程号为C2C2和和C4C4的的学生的

34、学号学生的学号。或：或：SELECT SELECT X.SNOX.SNO FROM(SELECT SNO FROM SC WHERE FROM(SELECT SNO FROM SC WHERE CNO=CNO=C2C2)X X,(SELECT SNO FROM SC WHERE (SELECT SNO FROM SC WHERE CNO=CNO=C4C4)Y Y WHERE X.SNO=Y.SNO WHERE X.SNO=Y.SNO;派生表派生表自身联接自身联接6.6.检索没有选修检索没有选修C2C2课程的学生的课程的学生的姓名和年龄。姓名和年龄。SELECT SELECT SNAME,AGE

35、SNAME,AGE FROM FROM S S WHERE WHERE (SELECT (SELECT*FROM SC FROM SC WHERE SC.SNO=WHERE SC.SNO=S.SNOS.SNO AND SC.CNO=AND SC.CNO=C2C2););-找这样的学生找这样的学生-不存在不存在-他他（即（即找的这个学生找的这个学生）-选了选了C2C2课程课程 假设：假设：cnocno:表示课程；表示课程；p(p(cnocno):):表示表示“选修了课程选修了课程cnocno”；p(p(cnocno):):表示表示“没有选修课程没有选修课程cnocno”形式化表示：形式化表示：(

36、cnocno)p()p(cnocno)(cnocno)()(p(p(cnocno)变换后的语义：变换后的语义：找这样的学生找这样的学生SNOSNO，7.7.检索检索选修了全部课程选修了全部课程的的学生的姓名学生的姓名。-查询查询变换依据：变换依据：SELECT SELECT SNAMESNAME FROM FROM S S WHERE WHERE (SELECT (SELECT*FROM FROM C C WHERE WHERE (SELECT (SELECT*FROM FROM SCSC WHERE SC.SNO=WHERE SC.SNO=S.SNOS.SNO AND AND SC.CNOS

37、C.CNO=C=C.CNO.CNO););-找这样的学生找这样的学生-不存在一门不存在一门课程课程他他没有没有-选修选修8.8.检索至少选修了检索至少选修了学生学生S2S2所选所选修的全部课程修的全部课程的学生的学生的学号。的学号。变换表达形式：变换表达形式：找这样的学生找这样的学生SNOSNO，形式化表示：形式化表示：(cnocno)p()p(cnocno)q(q(cnocno)其中：其中：cnocno:表示课程；表示课程；p(p(cnocno):):表示表示“学生学生S2S2选修了课程选修了课程cnocno”；q(q(cnocno):):表示表示“学生学生SNOSNO选修了课程选修了课程c

38、nocno”(cnocno)()(p(p(cnocno)q(q(cnocno)(cnocno)()(p(p(cnocno)q()q(cnocno)(cnocno)(p()(p(cnocno)q(q(cnocno)变换后的语义：变换后的语义：找这样的学生找这样的学生SNOSNO，SELECT SELECT DISTINCT SNODISTINCT SNO FROM FROM SC XSC X WHERE WHERE (SELECT (SELECT*FROM FROM SC YSC Y WHERE WHERE Y.SNO=S2Y.SNO=S2 AND AND (SELECT (SELECT*FRO

39、M FROM SC ZSC Z WHERE WHERE Z Z.SNO=X.SNO.SNO=X.SNO AND AND Z Z.CNO=Y.CNOCNO=Y.CNO););8.8.检索至少选修了检索至少选修了学生学生S2S2所选所选修的全部课程修的全部课程的学生的学生的学号。的学号。-找这样的学生找这样的学生-不存在一门课程不存在一门课程-学生学生S2S2选了选了 而这个学生而这个学生没选没选SELECT SELECT DISTINCT SNODISTINCT SNO FROM FROM SC XSC X WHERE WHERE (SELECT (SELECT*FROM FROM SC YSC

40、 Y WHERE WHERE Y.SNO=S2Y.SNO=S2 AND AND (SELECT (SELECT*FROM FROM SC ZSC Z WHERE WHERE Z Z.SNO=X.SNO.SNO=X.SNO AND AND Z Z.CNO=Y.CNOCNO=Y.CNO););8.8.检索至少选修了检索至少选修了学生学生S2S2所选所选修的全部课程修的全部课程的学生的学生的学号。的学号。-找这样的学生找这样的学生-不存在一门课程不存在一门课程-学生学生S2S2选了选了 而这个学生而这个学生没选没选如要在如要在结果集中不包含学生结果集中不包含学生S2S2的的SQLSQL语句如下：语句

41、如下：SELECT DISTINCT SNOSELECT DISTINCT SNO FROMFROM (SELECT(SELECT*FROM SC WHERE SNOS2)X FROM SC WHERE SNOS2)X WHERE NOT EXISTS WHERE NOT EXISTS (SELECT (SELECT*FROM SC Y FROM SC Y WHERE Y.SNO=S2 WHERE Y.SNO=S2 AND NOT EXISTS AND NOT EXISTS (SELECT (SELECT*FROM SC Z FROM SC Z WHERE Z.SNO=X.SNO WHERE

42、Z.SNO=X.SNO AND Z.CNO=Y.CNO);AND Z.CNO=Y.CNO);四、聚合函数四、聚合函数 SQL SQL 提供了下列聚合函数：提供了下列聚合函数：COUNTCOUNT（*）计算元组的个数计算元组的个数 COUNTCOUNT（列名）（列名）对一列中的值计算个数对一列中的值计算个数 SUMSUM（列名）（列名）求某一列值的总和（求某一列值的总和（此列的值必须是数值此列的值必须是数值）AVGAVG（列名）（列名）求某一列值的平均值（求某一列值的平均值（此列的值必须是数值此列的值必须是数值）MAXMAX（列名）（列名）求某一列值的最大值求某一列值的最大值 MINMIN（列名

43、）（列名）求某一列值的最小值求某一列值的最小值 举例：举例：1.1.检索检索年龄最大年龄最大的学生的的学生的姓名和性别。姓名和性别。SELECT SELECT SNAME,SEXSNAME,SEXFROM SFROM SWHERE AGE=(WHERE AGE=(SELECT MAX(AGE)SELECT MAX(AGE)FROM S FROM S););2.2.检索选修检索选修两门以上课程两门以上课程的学生的学号。的学生的学号。SELECT SNO,COUNT(SELECT SNO,COUNT(*)COUNT)COUNTFROM SCFROM SCGROUP BY SNO GROUP BY

44、SNO HAVING COUNT(HAVING COUNT(*)2)2;SELECT SELECT S.SNO S.SNO 学号学号,SNAME,SNAME 姓名姓名,COUNT(CNO)COUNT(CNO)课程门数课程门数FROM SC,SFROM SC,S WHERE S.SNO=SC.SNO WHERE S.SNO=SC.SNO GROUP BY GROUP BY S.SNO,SNAMES.SNO,SNAMEHAVING COUNT(HAVING COUNT(*)2)2 ORDER BY 3 DESC,1;ORDER BY 3 DESC,1;3.3.统计每个学生选修课程的门数（超过统计每

45、个学生选修课程的门数（超过2 2门的学生才统计）。门的学生才统计）。要求要求显示学生的学号显示学生的学号、姓名、姓名和选修课程门数，和选修课程门数，查询结果按门数降查询结果按门数降序排列，若门数相同，按学号升序排列序排列，若门数相同，按学号升序排列:SELECTSELECT语句的语义语句的语义通常通常有三种情况（有三种情况（SQLSQL标准）标准）:以学生表以学生表S S（SNOSNO，SNAMESNAME，AGEAGE，SEXSEX，SDEPTSDEPT）为例说明。）为例说明。第一种情况：第一种情况：SELECTSELECT语句中未使用分组子句语句中未使用分组子句,也未使用聚合操作，也未使用

46、聚合操作，那么那么SELECTSELECT子句的语义是对查询的结果执行投影操作。子句的语义是对查询的结果执行投影操作。如：如：SELECT SNO,SNAMESELECT SNO,SNAMEFROM SFROM SWHERE SEX=WHERE SEX=男男;第二种情况：第二种情况：SELECTSELECT语句中未使用分组子句，但在语句中未使用分组子句，但在SELECTSELECT子句中使用了聚合操作，此时子句中使用了聚合操作，此时SELECTSELECT子句的语义是对查询子句的语义是对查询结果执行聚合操作。结果执行聚合操作。如：如：SELECT COUNT(*)count_男,AVG(CAS

47、T(S.AGE AS INT)avg_age FROM S WHERE SEX=男;该语句是求男同学的人数和平均年龄（假设该语句是求男同学的人数和平均年龄（假设AVGAVG为字符型为字符型）第三种情况：第三种情况：SELECTSELECT语句使用了分组子句和聚合操作语句使用了分组子句和聚合操作，此时，此时SELECTSELECT子句的语义是对查询结果的每一分组去子句的语义是对查询结果的每一分组去做聚合操作。做聚合操作。如：如：SELECT AGE,COUNT(SELECT AGE,COUNT(*)count)count FROM S FROM S WHERE SEX=WHERE SEX=男男

48、GROUP BY AGE;GROUP BY AGE;该语句是求男同学每一年龄的人数。该语句是求男同学每一年龄的人数。五五.联接操作联接操作 联接条件可在联接条件可在WHEREWHERE中指定中指定,也可以在也可以在 FROMFROM子句中指定。子句中指定。在在 FROM FROM 子句中指定联接条件时，子句中指定联接条件时，SQL2SQL2开始将开始将联接操作符分成联接操作符分成:联接类型、联接条件。联接类型、联接条件。联接类型：联接类型：决定了如何处理联接条件中不匹配的元组。决定了如何处理联接条件中不匹配的元组。联接条件：联接条件：决定了两个关系中哪些元组应该匹配。决定了两个关系中哪些元组应

49、该匹配。联接类型中的联接类型中的OUTEROUTER字样可不写。字样可不写。联接联接类型类型联接联接类型说明类型说明INNER JOININNER JOIN内联接：内联接：LEFT OUTER JOINLEFT OUTER JOIN左外联接：左外联接：RIGHT OUTER JOINRIGHT OUTER JOIN 右外联接：右外联接：FULL OUTER JOINFULL OUTER JOIN完全外联接：完全外联接：CROSS JOINCROSS JOIN交叉联接：交叉联接：结果为两个联接表中匹配行的联接。结果为两个联接表中匹配行的联接。结果包括结果包括“左左”表（出现在表（出现在 JOIN

50、 JOIN 子句的最左边）中的所有行。不包括右表中的子句的最左边）中的所有行。不包括右表中的不匹配行。不匹配行。结果包括结果包括“右右”表（出现在表（出现在JOIN JOIN 子句的最右边）中的所有行子句的最右边）中的所有行。不包括左表中。不包括左表中的不匹配行。的不匹配行。结果包括所有联接表中的所有行，结果包括所有联接表中的所有行，不论它们是否匹配不论它们是否匹配 结果包括两个联接表中所有结果包括两个联接表中所有可能的行组合。交叉联接返回的是两个表的笛可能的行组合。交叉联接返回的是两个表的笛卡儿积卡儿积A AB BC Ca ab bc cb bb bf fc ca ad dB BC CD D