交通信息系统第八章结构化查询语言SQL1

1、1 主要内容主要内容vSQLSQL概述概述 vSQLSQL的数据定义的数据定义vSQLSQL的数据查询的数据查询 v数据更新数据更新 v嵌入式嵌入式SQL SQL vTransact SQLTransact SQL语言概述语言概述 2本章概述本章概述v 结构化查询语言结构化查询语言SQLSQL（Structured Query LanguageStructured Query Language）是关系数据库）是关系数据库的标准语言，对关系模型的发展和商用的标准语言，对关系模型的发展和商用DBMSDBMS的研制起着重要的作用。的研制起着重要的作用。SQLSQL语语言是介乎于关系代数和元组演算之间

2、的一种语言。本章详细介绍言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQLSQL的核心的核心部分内容：部分内容：数据定义数据定义、数据查询数据查询、数据更新数据更新和和嵌入式嵌入式SQLSQL。 34.1 SQL概述概述 主要内容主要内容vSQL的产生与发展的产生与发展vSQL的组成及特点的组成及特点44.1.1 SQL4.1.1 SQL的产生与发展的产生与发展 v19721972年，年，IBMIBM公司开始研制实验型关系数据库管理系统公司开始研制实验型关系数据库管理系统SYSTEM RSYSTEM R，其配，其配备的查询语言称为备的查询语言称为SQUARESQUARE (Spec

3、ifying Queries As Relational (Specifying Queries As Relational Expression ) Expression )语言，语言中使用了较多的数学符号。语言，语言中使用了较多的数学符号。v19741974年，年，BoyceBoyce和和ChamberlinChamberlin把把SQUARESQUARE修改为修改为SEQUELSEQUEL (Structured (Structured English Query Language )English Query Language )语言。后来语言。后来SEQUELSEQUEL简称为简称为

4、SQLSQL (Structured (Structured Query Language )Query Language )，即，即“结构式查询语言结构式查询语言”，SQLSQL的发音仍的发音仍“sequelsequel”。现在现在SQLSQL已经成为一个标准已经成为一个标准 。v SQLSQL有两个标准：有两个标准：ANSI SQLANSI SQL和和19921992年通过的修改版本年通过的修改版本SQL-92SQL-92（简称（简称 SQL2SQL2）。还有一个新的标准）。还有一个新的标准SQL3SQL3，它扩充了，它扩充了SQL2SQL2，引入了递归、触发，引入了递归、触发 器和对象等

5、概念和机制。器和对象等概念和机制。SQL简介简介SQL语言是应用于数据库的语言，本身是不能独立存在的。它是一种非过程性(non-procedural)语言，与一般的高级语言(如C、JAVA)不同。一般的高级语言在存取数据库时，需要依照每一行程序的顺序处理许多的动作。但是使用SQL时，只需告诉数据库需要什么数据，怎么显示就可以了。具体的内部操作则由数据库系统来完成。64.1.2 SQL4.1.2 SQL的组成及特点的组成及特点 SQLSQL语言从功能上可以分为四部分：数据查询（语言从功能上可以分为四部分：数据查询（Data QueryData Query）、数据）、数据操纵（操纵（Data Ma

6、nipulationData Manipulation）、数据定义（）、数据定义（Data DefinitionData Definition）和数据控制）和数据控制（Data ControlData Control）。）。 数据定义语言，即数据定义语言，即SQL DDLSQL DDL，用于定义，用于定义SQLSQL模式、基本表、视图、索引等模式、基本表、视图、索引等结构。结构。 数据操纵语言，即数据操纵语言，即SQL DMLSQL DML。数据操纵分成数据查询和数据更新两类。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。其中数据更新又分成插入、删除和修改三种

7、操作。 数据控制语言，即数据控制语言，即SQL DCLSQL DCL，这一部分包括对基本表和视图的授权、完，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。整性规则的描述、事务控制等内容。 SQLSQL的核心部分相当于关系代数，同时又具有关系代数所没有的许多的核心部分相当于关系代数，同时又具有关系代数所没有的许多特点，如聚集、数据库更新等。特点，如聚集、数据库更新等。7v综合统一综合统一v高度非过程化高度非过程化nSQL是一种第四代语言（是一种第四代语言（4GL），用户只需提出），用户只需提出“干什干什么么”，无需具体指明，无需具体指明“怎么干怎么干”，像存取路径选择和具，

8、像存取路径选择和具体处理操作等，均有系统自动完成。体处理操作等，均有系统自动完成。v统一的语法结构统一的语法结构nSQL有两种使用方式：一是联机交互使用，另一种是嵌有两种使用方式：一是联机交互使用，另一种是嵌入到某种高级语言程序设计语言中去使用。入到某种高级语言程序设计语言中去使用。v语言简洁语言简洁8SQLSQL的核心动词的核心动词 功功 能能动动 词词数据库查询数据库查询SELECTSELECT数据定义数据定义CREATECREATE，DROPDROP数据操纵数据操纵INSERTINSERT，UPDATEUPDATE，DELECTDELECT数据控制数据控制GRANTGRANT，REVOK

9、EREVOKE9操作对操作对象象操操 作作 方方 式式创创 建建删删 除除修修 改改数据库数据库CREATE DATABASECREATE DATABASEDROP DATABASEDROP DATABASE表表CREATE TABLECREATE TABLEDROP TABLEDROP TABLEALTER TABLEALTER TABLE索索 引引CREATE INDEXCREATE INDEXDROP INDEXDROP INDEX视视 图图CREATE VIEWCREATE VIEWDROP VIEWDROP VIEWSQLSQL的数据定义功能包括数据库定义、表定义、视图和索引的定义。

10、的数据定义功能包括数据库定义、表定义、视图和索引的定义。 10学号学号SNO姓名姓名SNAME性别性别SEX年龄年龄AGE所在系所在系SDEPT200915121200915122200915123200915125李小勇李小勇刘刘 晨晨王洪敏王洪敏张张 力力MFFM20191819CSCSMAIS学生表学生表：S(S(SNOSNO,SNAME,SEX,AGE,SDEPT) ,SNAME,SEX,AGE,SDEPT) 课程表：课程表：C(C(CNOCNO,CNAME,CPNO,CCREDIT) ,CNAME,CPNO,CCREDIT) 学生选课表学生选课表：SC(SC(SNO,CNOSNO,C

11、NO,GRADE) ,GRADE) 例例4.14.1 定义一个定义一个教务管理数据库教务管理数据库JWGLJWGL，它包含，它包含3 3个表：个表：11课程号课程号CNO课程名课程名CNAME先修课先修课CPNO学分学分CCREDIT1234567数据库数据库数学数学信息系统信息系统操作系统操作系统数据结构数据结构数据处理数据处理C语言语言516764243424课程表实例课程表实例学号学号SNO课程号课程号CNO成绩成绩GRADE200915121200915121200915121200915122200915122123239285889080124.3 SQL的数据查询的数据查询 主要

12、内容主要内容 SELECTSELECT语句格式语句格式 单表查询单表查询 聚集函数聚集函数 数据分组数据分组 多表查询多表查询 集合操作集合操作13 4.3.1 SELECT 4.3.1 SELECT语句语句格式格式1 1、SELECTSELECTFROMFROMWHEREWHERE句型句型在关系代数中最常用的式子是下列表达式：在关系代数中最常用的式子是下列表达式： A1,An(F(R1Rm) 这里这里R1、Rm为关系，为关系，F是公式，是公式，A1、An为属性。为属性。针对上述表达式，针对上述表达式，SQL为此设计了为此设计了SELECTFROMWHERE句型：句型：SELECT A1，An

13、FROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达子句中的条件表达式式F要比关系代数中公式更灵活要比关系代数中公式更灵活。14v在在WHERE子句的条件表达式子句的条件表达式F中可使用下列运算符中可使用下列运算符n算术比较运算符：算术比较运算符：、 、 = 、 、 !=n逻辑运算符：逻辑运算符：AND、OR、NOTn集合成员资格运算符：集合成员资格运算符：IN、NOT INn谓词：谓词：EXISTS、ALL、SOME、UNIQUEn聚合函数：聚合函数：AVG、MIN、MAX、SUM、COUNTnF中运算对象

14、还可以是另一个中运算对象还可以是另一个SELECT语句，即语句，即SELECT句型可以嵌套句型可以嵌套2 2、SELECTSELECT语句完整的句法如下：语句完整的句法如下： SELECTSELECT 目标表的列名或列表达式序列目标表的列名或列表达式序列 FROMFROM 基本表名和（或）视图序列基本表名和（或）视图序列 WHEREWHERE 行条件表达式行条件表达式 GROUP BYGROUP BY 列名序列列名序列 HAVING HAVING 组条件表达式组条件表达式 ORDER BYORDER BY 列名列名 ASC|DESC ASC|DESC ， 主语句主语句SELECT-FROM-W

15、HERE的含义是：的含义是： 根据根据WHERE子句的条件表达式，从子句的条件表达式，从FROM子句指定的基本表子句指定的基本表或视图中找出满足条件的元组，再按或视图中找出满足条件的元组，再按SELECT子句中的目标列表达子句中的目标列表达式，选出元组中的属性值形成结果表。式，选出元组中的属性值形成结果表。 16整个语句的执行过程如下：整个语句的执行过程如下：(1) (1) 读取读取FROMFROM子句中基本表、视图的数据，执行笛卡尔积操作。子句中基本表、视图的数据，执行笛卡尔积操作。(2) (2) 选取满足选取满足WHEREWHERE子句中给出的条件表达式的元组。子句中给出的条件表达式的元组

16、。(3) (3) 按按GROUPGROUP子句中指定列的值分组，同时提取满足子句中指定列的值分组，同时提取满足HAVINGHAVING子句中子句中组条件表达式的那些组。组条件表达式的那些组。(4) (4) 按按SELECTSELECT子句中给出的列名或列表达式求值输出。子句中给出的列名或列表达式求值输出。(5) ORDER(5) ORDER子句对输出的目标表进行排序，按附加说明子句对输出的目标表进行排序，按附加说明ASCASC升序排列，升序排列，或按或按DESCDESC降序排列。降序排列。17根据查询条件不同，可分为：根据查询条件不同，可分为：v单表查询单表查询v连接查询连接查询v嵌套查询嵌套

17、查询v集合查询集合查询184.3.2 单表查询单表查询查询仅涉及一个表，是一种最简单的查询操作。查询仅涉及一个表，是一种最简单的查询操作。一、选择表中的若干列一、选择表中的若干列二、选择表中的若干元组二、选择表中的若干元组三、对查询结果排序三、对查询结果排序四、使用聚合函数四、使用聚合函数五、对查询结果分组五、对查询结果分组 191、查询表中的若干列、查询表中的若干列 查询指定列查询指定列 在很多情况下，用户只对表中的一部分属性列感兴趣，在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在这时可以通过在SELECT子句的子句的中指定中指定要查询的属性列。要查询的属性列。 例例4.21

18、 查询全体学生的学号与姓名。查询全体学生的学号与姓名。 SELECT SNO,SNAME FROM S; 执行过程：从执行过程：从S表中取出一个元组，再取出该元组在属表中取出一个元组，再取出该元组在属性性SNO和和SNAME上的值，形成一个新的元组作为输出。上的值，形成一个新的元组作为输出。对对S表中的所有元组做相同的处理，最后形成一个结果关表中的所有元组做相同的处理，最后形成一个结果关系作为输出。系作为输出。20 查询全部列查询全部列 将表中的所有属性列都选出来，可以有两种方法。一将表中的所有属性列都选出来，可以有两种方法。一种方法就是在种方法就是在SELECTSELECT关键字后面列出所有

19、列名；另一种方关键字后面列出所有列名；另一种方法是如果列的显示顺序与其在基表中的顺序相同，也可以法是如果列的显示顺序与其在基表中的顺序相同，也可以简单地将简单地将 指定为指定为“* *”。 例例4.23 4.23 查询全体学生的详细记录。查询全体学生的详细记录。 SELECT SELECT * * FROM S; FROM S; 等价于：等价于： SELECT SNO,SNAME,SEX,AGE,SDEPT SELECT SNO,SNAME,SEX,AGE,SDEPT FROM S; FROM S; 21 (3) 查询经过计算的值查询经过计算的值 SELECTSELECT子句的子句的 不仅可以

20、是表中的属性列，也可不仅可以是表中的属性列，也可以是表达式。以是表达式。 例例4.24 4.24 查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。 SELECT SNAME,2009-AGESELECT SNAME,2009-AGE FROM S; FROM S; 输出结果：输出结果： Sname Sname _ _ 李小勇李小勇 19891989 刘晨刘晨 19901990 王名王名 19911991 张立张立 1990 1990 222、选择表中的若干元组、选择表中的若干元组 消除取值重复的行消除取值重复的行 两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成两个

21、本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了，可以用相同的行了，可以用DISTINCT取消它们。取消它们。 例例4.26 查询选修了课程的学生学号。查询选修了课程的学生学号。 SELECT SNO FROM SC; 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定必须指定DISTINCT关键词：关键词： SELECT DISTINCT SNO FROM SC; 23(1) SELECT Sno(1) SELECT Sno FROM SC; FROM SC;或或( (默认默认 ALL)ALL)

22、SELECT ALL Sno SELECT ALL Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 95001 95001 95001 95001 95001 95002 95002 95002 95002 (2) SELECT DISTINCT Sno(2) SELECT DISTINCT Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 95001 95002 95002 24 查询满足条件的元组查询满足条件的元组查询满足指定条件的元组可以通过查询满足指定条件的元组可以通过WHERE子句实现。子句实现

23、。查查 询询 条条 件件谓谓 词词比较比较确定范围确定范围确定集合确定集合字符匹配字符匹配空空 值值多重条件（逻辑运算）多重条件（逻辑运算）= =， ， =，=，!=!=，!，!=20 AND AGE =23；27确定集合确定集合 谓词谓词IN可以用来查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。 例例4.32 查询计算机科学系（查询计算机科学系（CS）、数学系（）、数学系（MA）和信息系（）和信息系（IS）学）学生的姓名和性别。生的姓名和性别。 SELECT SNAME,SEX FROM S WHERE SDEPT IN(CS,MA,IS); 与与IN相对的谓词是相对的

24、谓词是NOT IN，用于查找属性值不属于指定集合的元组。，用于查找属性值不属于指定集合的元组。 例例4.33 查询既不是计算机科学系、数学系，也不是信息系的学生的姓查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。名和性别。 SELECT SNAME,SEX FROM S WHERE SDEPT NOT IN(CS,MA,IS); 28字符匹配谓词字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：可以用来进行字符串的匹配。其一般语法格式如下： NOT LIKE ESCAPE其含义是查找指定的属性列值与其含义是查找指定的属性列值与相匹配的元组。相匹配的元组。可以是一

25、个完整的字符串，也可以含有通配符可以是一个完整的字符串，也可以含有通配符%和和_。其中：。其中： %（百分号）（百分号）代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串。）的字符串。例如例如a%b表示以表示以a开头，以开头，以b结尾的任意长度的字符串。如结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。等都满足该匹配串。_（下划线）（下划线）代表任意单个字符。例如代表任意单个字符。例如a_b表示以表示以a开头，以开头，以b结尾的长结尾的长度为度为3的任意字符串。如的任意字符串。如acb，afb等都满足该匹配串。等都满足该匹配串。29例例4.34 查询学号为查询学号

26、为200915121的学生的详细情况。的学生的详细情况。 SELECT * FROM S WHERE SNO LIKE 200915121; 等价于：等价于： SELECT * FROM S WHERE SNO=200915121;如果如果LIKE后面的匹配串中不含通配符，则可以用后面的匹配串中不含通配符，则可以用=（等于）运算符取代（等于）运算符取代LIKE谓词，用谓词，用!=或或（不等于）运算符取代（不等于）运算符取代NOT LIKE谓词。谓词。例例4.35 查询所有姓刘的学生的姓名、学号和性别。查询所有姓刘的学生的姓名、学号和性别。 SELECT SNAME,SNO,SEX FROM S

27、 WHERE SNAME LIKE 刘刘%; 30 涉及空值的查询例涉及空值的查询例例例4.41 某些学生选修课程后没有参加考试，所以有选课记录，但没有某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT SNO,CNO FROM SC WHERE GRADE IS NULL; /* 分数分数GRADE是空值是空值 */ 多重条件查询多重条件查询可用逻辑运算符可用逻辑运算符AND和和OR来联结多个查询条件。来联结多个查询条件。AND的优先级高于的优先级高于OR，但可以用括号改变优

28、先级。，但可以用括号改变优先级。例例4.43 查询计算机科学系年龄在查询计算机科学系年龄在20岁以下的学生姓名。岁以下的学生姓名。 SELECT SNAME FROM S WHERE SDEPT=CS AND AGE20; 313、ORDER BY子句（子句（对查询结果排序对查询结果排序）使用使用ORDER BY子句子句v 可以按一个或多个属性列排序可以按一个或多个属性列排序v 升序：升序：ASC；降序：；降序：DESC；缺省值为升序；缺省值为升序当排序列含空值时当排序列含空值时vASC：排序列为空值的元组最后显示：排序列为空值的元组最后显示vDESC：排序列为空值的元组最先显示：排序列为空值

29、的元组最先显示32 例例4.44 查询选修了查询选修了3号课程的学生的学号及其成绩，查询结号课程的学生的学号及其成绩，查询结果按分数的降序排列。果按分数的降序排列。 SELECT SNO,GRADE FROM SC WHERE CNO=3 ORDER BY GRADE DESC; Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55查询结果查询结果33 聚合函数是涉及整个关系的另一类运算操作聚合函数是涉及整个关系的另一类运算操作, ,通过聚合函通过聚合函数，可以把某一列中的值形成单个值。数

30、，可以把某一列中的值形成单个值。 5类主要聚合函数：类主要聚合函数：n计数计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n计算总和计算总和SUM（DISTINCT|ALL ）n 计算平均值计算平均值AVG（DISTINCT|ALL ）4.3.3 聚合函数聚合函数34n求最大值求最大值MAX（DISTINCT|ALL ）n求最小值求最小值 MIN（DISTINCT|ALL ）DISTINCT短语：在计算时要取消指定列中的重复值短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值短语：不取消重复值ALL为缺省值为缺省值35例例4.47 查询选修了课程

31、的学生人数。查询选修了课程的学生人数。 SELECT COUNT(DISTINCT SNO) FROM SC;学生每选修一门课，在学生每选修一门课，在SC中都有一条相应的记录。一个学生要选修多中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在门课程，为避免重复计算学生人数，必须在COUNT函数中用函数中用DISTINCT短短语。语。例例4.48 计算选修计算选修l号课程的学生平均成绩。号课程的学生平均成绩。 SELECT AVG(GRADE) FROM SC WHERE CNO=1;例例4.49 查询选修查询选修l号课程的学生最高分数。号课程的学生最高分数。 SELECT MAX(GRADE) FROM SC WHER CNO=1; 36使用使用GROUP BY子句分组子句分组 细化集函数的作用对象细化集函数的作用对象n 未对查询结果分组，聚合函数将作用于整个查询结果未对查询结果分组，聚合函数将作用于整个查询结果n 对查询结果分组后，聚合函数将分别作用于每个组对查询结果分组后，聚合函数将分别作用于每个组 4.3.4 对查询结果分组对查询结果分组 37