第三章结构化查询语言SQL补充ppt课件

1、第第5 5章章 结构化查询语言结构化查询语言SQLSQLoSQLSQL语言简介语言简介 oSQLSQL语言的数据定义功能语言的数据定义功能 o建立表结构、修改表结构、删除数据表建立表结构、修改表结构、删除数据表 oSQLSQL语言的数据操纵功能语言的数据操纵功能 o记录的插入、记录的删除、记录的更新记录的插入、记录的删除、记录的更新oSQLSQL语言的查询功能语言的查询功能 o简单查询简单查询o条件查询条件查询o连接查询连接查询o嵌套查询嵌套查询o统计查询统计查询o分组查询分组查询o查询排序查询排序o合并查询合并查询o查询的输出查询的输出 第第5 5章章 结构化查询语言结构化查询语言SQL S

2、QL o结构化查询语言结构化查询语言SQLSQLStructured Query Structured Query LanguageLanguage），是关系数据库的标准语言，它），是关系数据库的标准语言，它具有数据定义、数据操纵、数据查询和数据具有数据定义、数据操纵、数据查询和数据控制等功能，并且其语言简洁、功能强大，控制等功能，并且其语言简洁、功能强大，目前广泛用于各大、中、小型关系数据库，目前广泛用于各大、中、小型关系数据库，已成为世界上最流行的数据库语言之一。已成为世界上最流行的数据库语言之一。 结构化查询语言结构化查询语言SQLSQLSQLSQL语言的产生和发展语言的产生和发展197

3、41974年由年由BoyceBoyce和和ChamberlinChamberlin提出，并作为提出，并作为IBMIBM公公司研制的关系数据库管理系统原型司研制的关系数据库管理系统原型SYSTEM RSYSTEM R的查询语言文本。的查询语言文本。19821982年，年，ANSIANSI美国国家标准局成立美国国家标准局成立SQLSQL标准标准委员会。委员会。19861986年年1010月，月，ANSI SQLANSI SQL标准被批准作为关系数据标准被批准作为关系数据库语言的美国标准。公布了库语言的美国标准。公布了SQLSQL标准文本标准文本SQL-86SQL-86）。）。19871987年年6

4、 6月，月，ISOISO国际标准化组织将其采纳为国际标准化组织将其采纳为国际标准。国际标准。SQL-89SQL-89、SQL92SQL92和和SQL99SQL99等。等。目前目前SQLSQL已被许多已被许多DBMSDBMS产品如，产品如，DB2DB2、INGRESINGRES、SYSBASESYSBASE、SQLServerSQLServer、VFPVFP和和ORACLEORACLE等所采用，等所采用，它已成为关系数据库领域中一个主流语言。它已成为关系数据库领域中一个主流语言。 结构化查询语言结构化查询语言SQLSQLoSQLSQL语言的特点语言的特点o一体化一体化 o将所有的对数据库的定义、

5、查询、更新、控制、将所有的对数据库的定义、查询、更新、控制、维护、恢复、安全等一系列操作要求统一为一种维护、恢复、安全等一系列操作要求统一为一种语言。语言。o非过程化非过程化 o交互式交互式SQLSQL是一种自含式语言，可独立使用，用是一种自含式语言，可独立使用，用于一般用户建立中、小型管理信息系统于一般用户建立中、小型管理信息系统 o嵌入式嵌入式SQLSQL是将是将SQLSQL语言嵌入到高级语言中，用户语言嵌入到高级语言中，用户可利用高级语言灵活地表达计算功能和可利用高级语言灵活地表达计算功能和SQLSQL数据数据处理功能，以便完成各种复杂的事务处理。处理功能，以便完成各种复杂的事务处理。

6、结构化查询语言结构化查询语言SQLSQLo简洁化简洁化 oSQLSQL语言功能强大并且只用几条命令来完成，语言功能强大并且只用几条命令来完成，其语法也非常简单，很接近自然语言模式。其语法也非常简单，很接近自然语言模式。SQLSQL的命令有：数据查询命令的命令有：数据查询命令SELECTSELECT）、）、数据定义命令数据定义命令CREATECREATE、DROPDROP、ALTERALTER）、数据操纵命令）、数据操纵命令INSERTINSERT、UPDATEUPDATE、DELETEDELETE和数据控制命令和数据控制命令GRANTGRANT、REVOKEREVOKE）。）。 5.2 SQL

7、语言的数据定义功能 o打开数据库。打开数据库。o在在“设计视图设计视图” ” 新建查询，在弹出的新建查询，在弹出的“显示表对话框中直接点击显示表对话框中直接点击“关闭关闭”。 “查询查询”“SQL特定查询特定查询”- “数据定义查询数据定义查询” 5.2 SQL语言的数据定义功能 建立数据表结构 定义数据表的结构。定义数据表的结构。CREATE TABLE CREATE TABLE ( ( ( () ) NOT NULL NOT NULL PRIMARY KEY | UNIQUE|REFERENCES PRIMARY KEY | UNIQUE|REFERENCES 外部外部表表 (外部关键字外

8、部关键字) ) ， ( () NOT NULL NOT NULL PRIMARY KEY | UNIQUE|REFERENCES PRIMARY KEY | UNIQUE|REFERENCES 外部外部表表 (外部关键字外部关键字) ) ，.).)SQL-SQL-建立表结构建立表结构o建立一个建立一个“学生名册表结构学生名册表结构o（CREATE TABLE命令）命令） oCREATE TABLE 学生名册学生名册 (学号学号 TEXT(10) PRIMARY KEY,o 姓名姓名 TEXT(8),性别性别 TEXT(2),o 出生日期出生日期 DATE,院系院系 TEXT(12),o 总分总

9、分 INTEGER, 奖学金奖学金 LOGICAL,o 简历简历 MEMO, 照片照片 OLEOBJECT)o 选择选择“数据定义查询数据定义查询类型类型写入写入SQLSQL语句语句运行查询运行查询使用使用SQL创建的表结构创建的表结构oCREATE TABLE 课程表o ( 课程号 TEXT(6) PRIMARY KEY,o 课程名 TEXT(20), o开课院系 TEXT(12),o学分 INTEGER,o学时 TEXT(2) ) SQL-SQL-建立表结构建立表结构o建立建立“成绩表成绩表”, ,并与并与“学生名册学生名册建立关建立关系系oCREATE TABLE CREATE TABL

10、E 成果成果o ( (学号学号 TEXT(10) REFERENCES TEXT(10) REFERENCES 学生名册学生名册( (学号学号), ), o 课程号课程号 TEXT(6),TEXT(6),课序号课序号 TEXT(2), TEXT(2), 成成果果 INTEGER)INTEGER)2 2、修改基本表、修改基本表增加字段增加字段 ALTER TABLE ALTER TABLE 表名表名 ADD ADD 字段名字段名 数据类型数据类型 修改字段修改字段 ALTER TABLE ALTER TABLE 表名表名 ALTER ALTER 字段名字段名 数据类型数据类型删除字段删除字段 A

11、LTER TABLE ALTER TABLE 表名表名 DROP DROP 字段名字段名SQL-SQL-修改表结构修改表结构SQL-SQL-修改表结构修改表结构o例：为例：为“学生名册增加字段一个学生名册增加字段一个“电子邮件字段电子邮件字段o o ALTER TABLE 学生名册学生名册 o ADD 电子邮件电子邮件 TEXT(20) 增加字段SQL查询5.3 SQL5.3 SQL语言的数据操纵功能语言的数据操纵功能 o1 1 SQL-SQL-插入数据插入数据o INSERT INTO INSERT INTO 表名表名 （列名（列名1 1，列名，列名22）o VALUES (VALUES (

12、表达式表达式1 1，表达式，表达式2) 2) 【例】向【例】向“学生情况表中插入一个新的学生记录学生情况表中插入一个新的学生记录INSERT INTO 学生情况学生情况2VALUES (112, 王汉王汉, 男男, #10/21/1980#, 586, TRUE, 足球足球, 1)5.3 SQL5.3 SQL语言的数据操纵功能语言的数据操纵功能o【例】【例】 使用使用SQLSQL命令向命令向“课程课程表中追加插入一条包表中追加插入一条包含部分信息的新记录。含部分信息的新记录。oINSERT INTO INSERT INTO 课程课程 ( (课程号课程号, ,课程名称课程名称, ,学分学分) )

13、o VALUES(201903,CAD VALUES(201903,CAD设计设计,2),2)o如果仅为表中的部分字段赋值，应注意字段名称与字段如果仅为表中的部分字段赋值，应注意字段名称与字段值要一一对应。值要一一对应。 5.3 SQL5.3 SQL语言的数据操纵功能语言的数据操纵功能o2 2SQL-SQL-修改数据修改数据o UPDATE UPDATE 表名表名o SET SET 列名列名1 1表达式表达式1 1，列名，列名2 2表达式表达式22o WHERE WHERE 条件条件 UPDATE 学生情况学生情况2 SET 性别性别“女女” WHERE 姓名姓名“王汉王汉” 数据操纵数据操纵

14、o3 3SQL-SQL-删除数据删除数据o DELETE FROM DELETE FROM 表名表名o WHERE WHERE 条件条件 5.4 SQL语言的查询功能oSELECT ALL|DISTINCTo 表别名. 字段名AS 列名,.o FROM 数据库名!表名o WHERE 条件o GROUP BY 分组名o HAVING 条件o UNIONALLSELECT语句o ORDER BY 排序项分组中的条件分组中的条件o【命令】【命令】o SELECT ALL | DISTINCT TOP SELECT ALL | DISTINCT TOP PERCENT PERCENTo AS AS I

15、NTO INTO o FROM FROM AS AS , AS AS 2o WHERE WHERE AND AND .2 .o AND | OR AND | OR AND | OR AND | OR .2 .o GROUP BY GROUP BY , , .2 .oHAVING HAVING o UNION ALL SELECT UNION ALL o ORDER BY ORDER BY ASC | DESC , ASC | DESC , ASC | 2 ASC | DESC . DESC . 查询实例o相关表：相关表：o学生信息表学生信息表o学生成绩表学生成绩表o课程信息表课程信息表o单表查

16、询单表查询o例例1：例出全部学生信息：例出全部学生信息o SELECT * FROM 学生信息表学生信息表o “*” 全部数据全部数据o o SELECT-SQLSELECT-SQL简单查询简单查询【例【例5-11】查询】查询“学生信息表中所有学生的姓学生信息表中所有学生的姓名、性别、出生日期。名、性别、出生日期。SELECT 姓名 AS 学生姓名,性别,出生日期AS 生日 FROM 学生信息表 SELECT-SQLSELECT-SQL简单查询简单查询o单表查询单表查询o例例2：例出所有女生的学号及姓名：例出所有女生的学号及姓名o SELECT 学号学号,姓名姓名 o FROM 学生信息表学生

17、信息表o WHERE 性别性别=“女女”o o单表查询单表查询o例例3 3：例出所有学生的性别：例出所有学生的性别o SELECT DISTINCT SELECT DISTINCT 性别性别 o FROM FROM 学生信息表学生信息表o o* * 列出性别为列出性别为“男的学生男的学生o SELECT SELECT 姓名，性别姓名，性别 o FROM FROM 学生情况学生情况o WHERE WHERE 性别性别=“=“男男”SELECT-SQL简单查询o单表查询单表查询o例例4 4：例出课程号为：例出课程号为303001303001所有成绩大于所有成绩大于8080分的学生学号及分的学生学号

18、及成绩，并按成绩由高到低排序。成绩，并按成绩由高到低排序。o SELECT SELECT 学号，成果学号，成果 o FROM FROM 学生成绩表学生成绩表o WHERE WHERE 课号课号=“303001” AND =“303001” AND 成果成果=80=80o ORDER BY ORDER BY 成果成果 DESCDESCo SELECT-SQLSELECT-SQL简单查询简单查询SELECT-SQL简单查询o单表查询单表查询o例例5 5：查询：查询8484年以后出生的学生。年以后出生的学生。o SELECT SELECT 姓名，出生日期姓名，出生日期 o FROM FROM 学生信

19、息表学生信息表o WHERE WHERE 出生日期出生日期=#1984-01-01#=#1984-01-01#o 注意日期的写法注意日期的写法SELECT-SQLSELECT-SQL简单查询简单查询o单表查询单表查询o例例6 6：查询不是：查询不是“艺术学院的学生。艺术学院的学生。o SELECT SELECT 姓名，院系名称姓名，院系名称 o FROM FROM 学生信息表学生信息表o WHERE WHERE 院系名称院系名称 “艺术学院艺术学院”o o院系名称院系名称 !=“!=“艺术学院艺术学院”【例5-13】查询“学生信息表中所有艺术学院和软件学院学生的学号、姓名和所在院系名称。oSE

20、LECT 学号学号,姓名姓名,院系名称院系名称FROM 学生信息表学生信息表oWHERE 院系名称院系名称 IN (”艺术学院艺术学院”,”软件学院软件学院”)oWHERE 院系名称院系名称=”软件学院软件学院” OR 院系名称院系名称=”艺术学院艺术学院”oWHERE 院系名称院系名称 NOT IN (”艺术学院艺术学院”,”软件学软件学院院”)SELECT-SQL简单查询o单表查询单表查询o例例7：查询成绩在：查询成绩在70到到80之间的学生选课得分情况。之间的学生选课得分情况。o SELECT * o FROM 学生成绩表学生成绩表o WHERE 成果成果 BETWEEN 70 AND

21、80oWHERE 成果成果=70 AND 成果成果=600 AND 性别=男 oSELECT COUNT (姓名) AS 合格人数 from 学生信息表 WHERE 入学总分=600 AND 性别=男SELECT-SQLSELECT-SQL统计查询统计查询例例1010：求全体学生的入校总分、最高分、最低分和平均分：求全体学生的入校总分、最高分、最低分和平均分SELECT SUM(SELECT SUM(入学总分入学总分) AS ) AS 新生入校总分新生入校总分, MAX(, MAX(入学总分入学总分) AS ) AS 新新生最高分生最高分, MIN(, MIN(入学总分入学总分) AS ) A

22、S 新生最低分新生最低分, , AVG( AVG(入学总分入学总分) AS ) AS 新生平均分新生平均分 FROM FROM 学生信息表学生信息表SELECT-SQLSELECT-SQL统计查询统计查询例例1111：求入校总分在：求入校总分在580580分以上的学生人数分以上的学生人数SELECT COUNT(SELECT COUNT(* *) AS ) AS 总分大于总分大于580580的人数的人数 FROM FROM 学生信息表学生信息表 WHERE WHERE 入学总分入学总分 580 580SELECT-SQLSELECT-SQL统计查询统计查询例例1313：查出学生成绩表中选修：查

23、出学生成绩表中选修“101002101002课程的最高分、最低分和相差分数课程的最高分、最低分和相差分数SELECT MAX(SELECT MAX(成果成果) AS ) AS 最高分最高分, MIN(, MIN(成果成果) AS ) AS 最低分最低分, , MAX( MAX(成果成果)- MIN()- MIN(成果成果) AS ) AS 相差分数相差分数 FROM FROM 学生成绩表学生成绩表 WHERE WHERE 学生成绩表学生成绩表. .课程号课程号 = 101002;= 101002;SELECT-SQLSELECT-SQL连接查询连接查询o例例14.14.查询成绩在查询成绩在90

24、90分以上的学生记录，并显示该生的学号、分以上的学生记录，并显示该生的学号、姓名、课程号和成绩相关表：学生情况、选课表姓名、课程号和成绩相关表：学生情况、选课表o Where Where 连接连接: : 只连接相等的记录只连接相等的记录o 连接条件：连接条件：o WHERE WHERE 学生信息表学生信息表. .学号学号= =学生成绩表学生成绩表. .学号学号 AND AND 成成果果=90=90o SELECT SELECT 学生信息表学生信息表. .学号学号, ,学生信息表学生信息表. .姓名姓名, ,学生成绩表学生成绩表. .课课程号程号, ,学生成绩表学生成绩表. .成果成果o fro

25、m from 学生信息表学生信息表, , 学生成绩表学生成绩表o where where 学生信息表学生信息表. .学号学号= =学生成绩表学生成绩表. .学号学号 AND AND 成果成果=90 =90 SELECT a.学号学号,姓名姓名,课程号课程号,成果成果 from 学生信息表学生信息表 a, 学生成绩表学生成绩表 b where a.学号学号=b.学号学号 AND 成果成果=90 临时定义别名临时定义别名o INNER JOIN 连接oSELECT 学生信息表2.姓名, 学生成绩表2.成果oFROM 学生信息表2 INNER JOIN 学生成绩表2 ON 学生信息表2.学号 = 学

26、生成绩表2.学号oWHERE (学生成绩表2.成果)=90);SELECT SELECT 学生信息表学生信息表2.2.姓名姓名, , 学生成绩表学生成绩表2.2.成果成果FROM FROM 学生信息表学生信息表2 LEFT JOIN 2 LEFT JOIN 学生成绩表学生成绩表2 ON 2 ON 学生信息表学生信息表2.2.学号学号 = = 学生成绩表学生成绩表2.2.学号学号WHERE (WHERE (学生成绩表学生成绩表2.2.成果成果)=90);)=90);LEFT JOIN LEFT JOIN 连接连接RIGHT JOIN 连接连接SELECT SELECT 学生信息表学生信息表2.2

27、.姓名姓名, , 学生成绩表学生成绩表2.2.成果成果FROM FROM 学生信息表学生信息表2 RIGHT JOIN 2 RIGHT JOIN 学生成绩表学生成绩表2 ON 2 ON 学生信息表学生信息表2.2.学号学号 = = 学生成绩表学生成绩表2.2.学号学号WHERE (WHERE (学生成绩表学生成绩表2.2.成果成果)=90);)=90);SELECT-SQLSELECT-SQL连接查询连接查询o例例15. 列出选修列出选修1号课的学生姓名及成绩。号课的学生姓名及成绩。o相关表：学生情况相关表：学生情况o 选课表选课表o 连接条件：连接条件：oWHERE 学生名册学生名册.学号学

28、号=选课表选课表.学号学号 AND 课号课号=“1”o SELECT *o FROM 学生情况学生情况 AS S, 选课表选课表 AS Co WHERE S.学号学号=C.学号学号 AND 课号课号=1;o临时定义表的别名临时定义表的别名S和和C可以减少书写长的表名可以减少书写长的表名分组查询分组查询:GROUP BY:GROUP BYo例例18. 列出各门课的平均成绩、最高成绩、最低成绩和列出各门课的平均成绩、最高成绩、最低成绩和选课人数。选课人数。oSELECT 学生成绩表学生成绩表2.课程号课程号, Avg(学生成绩表学生成绩表2.成果成果) AS 成绩之平均值成绩之平均值, Max(学

29、生成绩表学生成绩表2.成果成果) AS 成绩成绩之最大值之最大值, Min(学生成绩表学生成绩表2.成果成果) AS 成绩之最小值成绩之最小值, Count(学生成绩表学生成绩表2.学号学号) AS 学号之计数学号之计数oFROM 学生成绩表学生成绩表2oGROUP BY 学生成绩表学生成绩表2.课程号课程号;oSELECT SELECT 学生成绩表学生成绩表2.2.课程号课程号, ROUND(Avg(, ROUND(Avg(学生成绩表学生成绩表2.2.成果成果),2) AS ),2) AS 平平均分均分, Max(, Max(学生成绩表学生成绩表2.2.成果成果) AS ) AS 最高分最高

30、分, Min(, Min(学生成绩表学生成绩表2.2.成果成果) AS ) AS 最低分最低分, Count(, Count(学生成绩表学生成绩表2.2.学号学号) AS ) AS 选课计数选课计数oFROM FROM 学生成绩表学生成绩表2 2oGROUP BY GROUP BY 学生成绩表学生成绩表2.2.课程号课程号; ;ROUND(X,N) :N给出小数位数给出小数位数HAVING 子句(函数)oHAVINGHAVING函数与函数与GROUP BYGROUP BY子句联合使用子句联合使用, ,可以对分组后的可以对分组后的结果作进一步的限制结果作进一步的限制. .o例例18a. 18a.

31、 列出选课人数超过列出选课人数超过3 3人的各门课的平均成绩、人的各门课的平均成绩、最高成绩、最低成绩和选课人数。最高成绩、最低成绩和选课人数。oSELECT SELECT 学生成绩表学生成绩表2.2.课程号课程号, Round(Avg(, Round(Avg(学生成绩表学生成绩表2.2.成果成果),2) AS ),2) AS 平均分平均分, Max(, Max(学生成绩表学生成绩表2.2.成果成果) AS ) AS 最高分最高分, Min(, Min(学生成绩表学生成绩表2.2.成果成果) AS ) AS 最低分最低分, , Count(Count(学生成绩表学生成绩表2.2.学号学号) A

32、S ) AS 选课计数选课计数oFROM FROM 学生成绩表学生成绩表2 2oGROUP BY GROUP BY 学生成绩表学生成绩表2.2.课程号课程号oHAVING Count(HAVING Count(学生成绩表学生成绩表2.2.学号学号)3)3o计算：oSELECT 学生信息表2.姓名, 学生信息表2.性别, Year(Date()-Year(学生信息表2!出生日期) AS 年龄oFROM 学生信息表2;WHERE Year(Date()-Year(学生信息表学生信息表2!出生日期出生日期)=20SQLSQL嵌套查询嵌套查询o一个一个SELECT-FROM-WHERESELECT-F

33、ROM-WHERE语句中嵌套进另外一个语句中嵌套进另外一个SELECT-FROM-WHERESELECT-FROM-WHERE称为嵌套查询。上层查询称为外称为嵌套查询。上层查询称为外层查询或父查询，下层查询被称为内层查询或子查层查询或父查询，下层查询被称为内层查询或子查询。询。o子查询中不能使用子查询中不能使用ORDER BYORDER BY语句，因为该语句是对语句，因为该语句是对最终结果进行排序。最终结果进行排序。o嵌套查询的运行方式是由里向外，也就是说，每个嵌套查询的运行方式是由里向外，也就是说，每个子查询都先于它的父查询执行，而子查询的结果作子查询都先于它的父查询执行，而子查询的结果作为

34、其父查询的条件。为其父查询的条件。 嵌套查询嵌套查询如果查询所要求的结果出自一个关系，而相关的条件却涉及多如果查询所要求的结果出自一个关系，而相关的条件却涉及多个关系，则可以利用嵌套查询来完成。个关系，则可以利用嵌套查询来完成。嵌套查询一般有嵌套查询一般有2 2种形式：种形式： ANY|ALL|SOME ( ANY|ALL|SOME ()NOT EXISTS (NOT EXISTS ()说明：说明：（1 1其中比较运算符可以为其中比较运算符可以为 NOTIN/NOTBETWEENAND/NOTLIKENOTIN/NOTBETWEENAND/NOTLIKE（2 2ANYANY和和SOMESOME

35、是同义词，在进行比较运算时，只要子查询中是同义词，在进行比较运算时，只要子查询中有一条为真，则结果为真；而有一条为真，则结果为真；而ALLALL则要求子查询中全部记录则要求子查询中全部记录都为真，结果才为真。都为真，结果才为真。（3 3EXISTSEXISTS是谓词，检查子查询中是否有结果返回。是谓词，检查子查询中是否有结果返回。 o【例】查询哪些专业至少有一学生成绩等于【例】查询哪些专业至少有一学生成绩等于100100分分oSELECT DISTINCT SELECT DISTINCT 专业代号专业代号 FROM FROM 学生学生 o WHERE WHERE 学生学生. .学号学号 IN

36、IN o （SELECT SELECT 学号学号 FROM FROM 成绩表成绩表 WHERE WHERE 成绩表成绩表. .成果成果=100=100） o注意：注意：o(1)(1)这里所要查询的结果是这里所要查询的结果是“学生表的专业信息，而相关的条件却是学生表的专业信息，而相关的条件却是“成绩表的成绩字段的值，因此使用嵌套查询；成绩表的成绩字段的值，因此使用嵌套查询；o(2) “SELECT (2) “SELECT 学号学号 FROM FROM 成绩表成绩表 WHERE WHERE 成绩表成绩表. .成果成果=100=100） ” ”是子是子查询，且用括号包含。查询，且用括号包含。 【例】查询与【例】查询与“张三专业相同的学生姓名张三专业相同的学生姓名SELECT 姓名姓名 FROM 学生学生; WHERE 学生学生