第6章 关系数据库标准语言

1、第第6章章 关系数据库标准语言关系数据库标准语言SQL v6.1关系数据库标准语言关系数据库标准语言v6.2 SQL语句语句 6.1关系数据库标准语言关系数据库标准语言 v在关系数据库中普遍使用一种介于关系代数在关系数据库中普遍使用一种介于关系代数和关系演算之间的数据库操作语言和关系演算之间的数据库操作语言SQL，SQL的含义即结构化查询语言的含义即结构化查询语言（Structured Query Language）。 vSQL最早是最早是1974年年由由Boyce和和Chamberlin提出，是提出，是Structured Query Language的缩的缩写，并作为写，并作为IBM公司研

2、制的关系数据库管理公司研制的关系数据库管理系统原型系统原型System R的一部分付诸实施。的一部分付诸实施。 v自自SQL成为国际标准语言以后，各个数据库成为国际标准语言以后，各个数据库厂家纷纷推出各自的厂家纷纷推出各自的SQL软件或与软件或与SQL能够能够通信的的接口软件通信的的接口软件 6.1关系数据库标准语言关系数据库标准语言 vSQL语言之所以能够为用户和业界所接受，语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通成为国际标准，是因为它是一个综合的、通用的、功能极强的、简学易用的语言。其主用的、功能极强的、简学易用的语言。其主要特点包括：要特点包括： 综合统一

3、综合统一 高度非过程化高度非过程化 用同一种语法结构提供两种使用方式用同一种语法结构提供两种使用方式 语言简洁，易学易用语言简洁，易学易用 6.2 SQL语句语句 v在在ACCESS2003中，可以通过查询对象的中，可以通过查询对象的SQL窗口执行窗口执行SQL语句。语句。 6.2.1 数据定义语言数据定义语言 （1） 表的创建、修改和删除表的创建、修改和删除 v表的创建表的创建 SQL语言使用语言使用CREATE TABLE语句建立基本语句建立基本表。其一般格式为表。其一般格式为:CREATE TABLE （ NOT NULL 索引索引1, NOT NULL 索引索引2,CONSTRAINT

4、 复合字段索引复合字段索引,） 功能：创建一个名为功能：创建一个名为“表名表名”的数据表，该数的数据表，该数据表由若干个字段组成。据表由若干个字段组成。 6.2.1 数据定义语言数据定义语言 （1） 表的创建、修改和删除表的创建、修改和删除 v表的创建表的创建 其中，其中，“”中的内容是必选项，中的内容是必选项，“ ”中的内容是中的内容是可选项。本书以下各章节也遵循这个约定。可选项。本书以下各章节也遵循这个约定。：规定了所定义的表的名字，在一个数据库：规定了所定义的表的名字，在一个数据库中不允许有两个基本表同名。中不允许有两个基本表同名。：规定了该字段的名称。表中可以有若干：规定了该字段的名称

5、。表中可以有若干字段，不能有两个字段同名。字段，不能有两个字段同名。：规定了该字段的数据类型。参见表：规定了该字段的数据类型。参见表6-2。 NOT NULL，UNIQUE是指对某一列设置的约束条件。是指对某一列设置的约束条件。 CONSTRAIN子句用来建立索引。子句用来建立索引。 【例例6-1】 在在“ZHJW”数据库中创建四个表：数据库中创建四个表：解：创建表的语句分别如下。解：创建表的语句分别如下。 CREATE TABLE 学生表（学号学生表（学号 CHAR（6），姓名），姓名 CHAR（8），性别），性别 CHAR（2），所属年级），所属年级 CHAR（4），），专业专业CHAR（

6、10） CREATE TABLE 课程表（课号课程表（课号 CHAR（3） NOT NULL,课名课名 CHAR（20）,开课教师开课教师 CHAR（8）,总学时总学时 INTEGER,学分学分 INTEGER） CREATE TABLE 必修课表（课号必修课表（课号 CHAR（3） NOT NULL,必修专业必修专业 CHAR（10） CREATE TABLE 必修课成绩表（学号必修课成绩表（学号 CHAR（6） NOT NULL,课号课号 CHAR（3） NOT NULL,成绩成绩 INTEGER） （1） 表的创建、修改和删除表的创建、修改和删除v修改表的结构修改表的结构 添加字段添加字

7、段 语句格式：语句格式：ALTER TABLE ADD COLUMN 长度长度NOT NULL 功能：为功能：为“表名表名”指定的表添加一个名为指定的表添加一个名为“字段名字段名”的字的字段，字段的数据类型由段，字段的数据类型由“类型类型”指定。指定。NOT NULL 与长度与长度的含义同的含义同CREATE TABLE语句。语句。 【例例6-2】在已定义的课程表中增加开课学期字段。在已定义的课程表中增加开课学期字段。 解：解：ALTER TABLE 课程表课程表 ADD COLUMN 开课学开课学期期 INTEGER （1） 表的创建、修改和删除表的创建、修改和删除v删除字段删除字段 语句格

8、式：语句格式：ALTER TABLE 表名表名 DROP COLUMN 字段名字段名 功能：为功能：为“表名表名”指定的表删除一个名为指定的表删除一个名为“字段名字段名”的字段。的字段。 【例例6-3】在已定义的课程表中删除开课日期字段。在已定义的课程表中删除开课日期字段。解：解：ALTER TABLE 课程表课程表 DROP COLUMN 开课学开课学期期 （1） 表的创建、修改和删除表的创建、修改和删除v 删除表删除表语句格式：语句格式：DROP TABEL 表名表名功能：删除名为功能：删除名为“表名表名”的表。的表。【例例6-4】删除删除“ZHJW”数据库中的数据库中的“课程课程”表。表

9、。解：语句如下。解：语句如下。DROP TABLE 课程表课程表（2）索引的建立与删除）索引的建立与删除 v建立索引建立索引 创建索引可使用创建索引可使用SQL的的CREATE INDEX 语句，该语语句，该语句的格式和功能如下：句的格式和功能如下： 语句格式：语句格式：CREATE UNIQUEINDEX 索引名索引名ON 表表名（字段名名（字段名1 DESC|ASC,字段名字段名2 DESC|ASC,） WITH PRIMARY|DISALLOW NULL|IGNORE NULL 功能：为功能：为“表名表名”指定的表创建一个名为指定的表创建一个名为“索引名索引名”的索引。的索引。 【例例6

10、-5】 v为为“学生学生”表创建一个名为表创建一个名为“XH”的索引，该的索引，该索引按照索引按照“学号学号”字段的升序排列，为主键。字段的升序排列，为主键。v为为“课程课程”表创建一个名为表创建一个名为“KH”的索引，该的索引，该索引按照索引按照“课号课号”字段的降序排列，为主键。字段的降序排列，为主键。v为为“必修课必修课”表创建一个名为表创建一个名为“KHZY”的索的索引，该索引按照引，该索引按照“课号课号”的升序和的升序和“专业专业”的降序排列，索引字段中不允许使用空值。的降序排列，索引字段中不允许使用空值。v为为“必修课成绩必修课成绩”表创建一个名为表创建一个名为“XHKH”的索引，

11、该索引按照的索引，该索引按照“学号学号”的升序和的升序和“课课号号”的升序排列。的升序排列。 （2）索引的建立与删除）索引的建立与删除 v删除索引删除索引 语句格式：语句格式：DROP INDEX 索引名索引名 ON 表名表名 功能：删除功能：删除“表名表名”指定的表中由指定的表中由“索引名索引名”指定指定的索引。的索引。 【例例6-6】删除必修课成绩表中的删除必修课成绩表中的XH索引。索引。解：使用的解：使用的SQL语句如下。语句如下。DROP INDEX XH ON 必修课成绩必修课成绩 6.2.2 数据查询功能数据查询功能 （1）SELECT语句格式及功能说明语句格式及功能说明 v数据查

12、询是数据库中最常见的操作，数据查询是数据库中最常见的操作，SQL中实现查询的中实现查询的语句是语句是SELECT语句，该语句有一系列的子句，通过这语句，该语句有一系列的子句，通过这些子句可以实现对数据库的任何查询。些子句可以实现对数据库的任何查询。 v该语句的格式和功能如下：该语句的格式和功能如下：vSELECT ALL | DISTINCT |TOP N|TOP N PERCENT *|列名列名1或表达式或表达式1 AS 列标题列标题1 ,列名列名2或表达式或表达式2 AS 列标题列标题2 INTO 表名表名 FROM 表名表名1 ,表名表名2WHERE 条件条件 GROUP BY 列名列名

13、1 , 列名列名2HAVING 条件条件ORDER BY 列名列名1 ASC | DESC , 列名列名2 ASC | DESC 说明说明 vALL|DISTINCE|TOP N|TOP N PERCENT：选：选“ALL”时表示值相同的记录也包含在结果中，这是时表示值相同的记录也包含在结果中，这是默认设置；选默认设置；选“DISTINCT”时表示对于相同的记录只时表示对于相同的记录只包含第一条记录；选包含第一条记录；选“TOP N”时只返回查询结果的时只返回查询结果的前前N条记录，条记录，“N”为可变参数；选为可变参数；选“TOP N PERCENT”时表示只返回查询结果中的前百分之时表示只

14、返回查询结果中的前百分之N条条记录，记录，N为可变参数。为可变参数。v*|列名列名1或表达式或表达式1 AS 列标题列标题1，列名，列名2或表达式或表达式2 AS 列标题列标题2：用于指定查询结果中包含的选定：用于指定查询结果中包含的选定项，可以是列名或表达式，项，可以是列名或表达式，AS后面的后面的“列标题列标题”用用来定义对应列或表达式的标题。在来定义对应列或表达式的标题。在“列名列名1”、“列名列名2”等别名的前面可能有包含该列的表名。若选等别名的前面可能有包含该列的表名。若选“*”表示输出列是由表示输出列是由FROM子句中指定的表中的所有字段。子句中指定的表中的所有字段。vINTO子句

15、子句：用于查询生成新表，：用于查询生成新表，为新表的名为新表的名称。称。说明说明 vFROM表名表名1IN 数据库名数据库名1别名别名1，表名，表名2IN 数据库名数据库名2 别名别名2：用于指定查询操作从哪些表中获取数据，每个：用于指定查询操作从哪些表中获取数据，每个表可以取一个表可以取一个“别名别名”，这样就可以在选定项中用，这样就可以在选定项中用“别名别名”来引用相应表中的字段。来引用相应表中的字段。vWHERE条件条件：用于设定查询条件或多个表之间的连接条：用于设定查询条件或多个表之间的连接条件。件。vGROUP BY 列名列名1，：用于指定按照哪些列：用于指定按照哪些列进行分组，以便

16、进行统计。进行分组，以便进行统计。vHAVING条件条件：只有在指定分组的查询中才有效，只有满：只有在指定分组的查询中才有效，只有满足条件的组的信息才包含在查询结果中。足条件的组的信息才包含在查询结果中。vGROUP BY列名列名1ASC|DESC，列名，列名ASC|DESC：用于指定按照哪些列对查询结果进行排序，用于指定按照哪些列对查询结果进行排序，ASC代表升序，代表升序，DESC代表降序，默认为升序。代表降序，默认为升序。v格式中的所有条件均可以是逻辑表达式或关系表达式，使格式中的所有条件均可以是逻辑表达式或关系表达式，使用的运算符有：用的运算符有：AND、OR、NOT、=、=、BETW

17、EEN、LIKE、IN等。等。 （2）简单查询）简单查询 v【例例6-7】列出全部学生表信息。列出全部学生表信息。v解：命令如下：解：命令如下：SELECT * FROM 学生表学生表 【例例6-8】 v列出列出“工商管理工商管理”专业的全部学生的学号、专业的全部学生的学号、姓名和所属年级。姓名和所属年级。v解：解：Select 学号学号,姓名姓名,所属年级所属年级 from 学生学生 Where 专业专业=“工商管理工商管理” 【例例6-9】 v求求“101”号课成绩在号课成绩在60分以上的学生的姓分以上的学生的姓名和成绩。并按成绩由高到低排序。名和成绩。并按成绩由高到低排序。v解：解：Se

18、lect 学号，成绩学号，成绩 From 必修课成绩必修课成绩表表 WHERE 课号课号=101 and 成绩成绩=60 ORDER BY 成绩成绩 DESC 【例例6-10】 v在必修课成绩表中找出所有没有成绩的学在必修课成绩表中找出所有没有成绩的学号和课号。号和课号。vSELECT 学号，课号学号，课号 FROM 必修课成绩表必修课成绩表 WHERE 成绩成绩 IS NULL 三个专用于查询的谓词三个专用于查询的谓词 vBETWEENAND：当要求某个字段值在某：当要求某个字段值在某个区间内时，可使用该谓词，否定的形式是个区间内时，可使用该谓词，否定的形式是NOT BETWEENAND。v

19、IN：当要求字段值是几个值中的一个时，可：当要求字段值是几个值中的一个时，可使用该谓词，否定的形式是使用该谓词，否定的形式是NOT IN。vLIKE：主要用于在查询条件中对字符串进行：主要用于在查询条件中对字符串进行比较。比较。LIKE提供两种字符串匹配方式，一种提供两种字符串匹配方式，一种是使用是使用“？”匹配任意一个字符，另一种是匹配任意一个字符，另一种是使用使用“*”匹配匹配0个或多个字符的字符串。它的个或多个字符的字符串。它的否定形式为否定形式为NOT LIKE。 【例例6-11】 v查找成绩在查找成绩在85和和95之间的学生的选课得分之间的学生的选课得分情况。情况。vSELECT *

20、 FROM 必修课成绩表必修课成绩表 WHERE 成绩成绩 BETWEEN 85 AND 95【例例6-12】 v查找出查找出“2002”和和“2003”级学生的学号和级学生的学号和姓名。姓名。vSELECT 学号学号, 姓名姓名FROM 学生表学生表 WHERE 所属年级所属年级 IN（2002,2003） 【例例6-13】 v查询查询“02”和和“03”级学生的学习情况。已级学生的学习情况。已知学号的前知学号的前2位是学生的年级。位是学生的年级。vSELECT * FROM 必修课成绩表必修课成绩表 WHERE 学号学号 Like 02* Or 学号学号 Like 03* （3）联接查询）

21、联接查询 v多个表之间的联接运算特别是多个表之间的联接运算特别是自然联结运算自然联结运算是关系数据库中经常用到的一种操作。是关系数据库中经常用到的一种操作。vSQL语言提供了语言提供了查询多个表查询多个表中信息的操作，中信息的操作，这种多个表之间的查询功能称之为联接查询。这种多个表之间的查询功能称之为联接查询。v若按对应字段相等将一个表中的行与另一个若按对应字段相等将一个表中的行与另一个表中的行联接起来，称为等值查询，若两个表中的行联接起来，称为等值查询，若两个表对应字段同名就相当于内联结。表对应字段同名就相当于内联结。 【例例6-14】 v查询出所有学生的必修课的学习情况，查询查询出所有学生

22、的必修课的学习情况，查询结果中包含学号、姓名、课程名称和成绩。结果中包含学号、姓名、课程名称和成绩。v解：解：SELECT 学生表学生表.学号学号, 学生表学生表.姓名姓名, 必必修课成绩表修课成绩表.课号课号, 必修课成绩表必修课成绩表.成绩成绩 FROM 学生学生, 必修课成绩表必修课成绩表 WHERE 学生表学生表.学号学号=必修课成绩表必修课成绩表.学号学号 AND 课程表课程表.课号课号=必修必修课成绩表课成绩表.课号课号 【例例6-14】 【例例6-15】 v列出修列出修“101”号课的学生姓名及成绩。号课的学生姓名及成绩。vSELECT 学生表学生表.姓名姓名, 必修课成绩表必修

23、课成绩表.成绩成绩 FROM 学生表学生表, 必修课成绩表必修课成绩表 WHERE 学生表学生表.学号学号=必修必修课成绩表课成绩表.学号学号 AND 必修课成绩表必修课成绩表.课号课号=101 （4）嵌套查询）嵌套查询 v嵌套查询是指在嵌套查询是指在一个外层查询中包含有另一一个外层查询中包含有另一个内层查询个内层查询。其中外层查询称为主查询，内。其中外层查询称为主查询，内层查询称为子查询。层查询称为子查询。vSQL允许多层嵌套允许多层嵌套，由内而外地进行分析，由内而外地进行分析，子查询的结果作为主查询的查询条件。子查询的结果作为主查询的查询条件。v根据子查询嵌套的层数，可以分成根据子查询嵌套

24、的层数，可以分成单层单层嵌套嵌套查询和查询和多层多层嵌套查询。把仅嵌套一层子查询嵌套查询。把仅嵌套一层子查询的的SELECT命令称为单层嵌套查询，把嵌套的命令称为单层嵌套查询，把嵌套的子查询多于一层的查询称为多层嵌套查询。子查询多于一层的查询称为多层嵌套查询。 返回单值的子查询返回单值的子查询 v【例例6-16】列出所有选修了列出所有选修了“微观经济学微观经济学”的学生的学生的学号。的学号。v解：解：SELECT 学号学号 FROM 必修课成绩表必修课成绩表 WHERE 课号课号=（Select DISTINCT 课号课号 FROM 课程表课程表 WHERE 课名课名=微观经济学微观经济学）

25、返回一组值的子查询返回一组值的子查询 v当子查询返回的值不止一个，则必须在当子查询返回的值不止一个，则必须在WHERE子句中子句中说明怎样使用这些返回值。使用方式有如下三种。说明怎样使用这些返回值。使用方式有如下三种。 字段名字段名|表达式表达式 比较运算符比较运算符 ANY|SOME|ALL （子查询结果（子查询结果集）；集）； 字段名字段名|表达式表达式 NOT IN （子查询结果集）；（子查询结果集）； 字段名字段名|表达式表达式 NOT EXISTS （子查询结果集）（子查询结果集）v各谓词的含义如下。各谓词的含义如下。 ANY或或SOME：这两个谓词的功能完成一样，只要子查询结这两个

26、谓词的功能完成一样，只要子查询结果集中任一个值满足给定的条件，结果就是真。果集中任一个值满足给定的条件，结果就是真。 ALL：只有在子查询集中的所有值均满足条件时结果才为真。只有在子查询集中的所有值均满足条件时结果才为真。 IN：如果表达式或字段的值包含在子查询中则表达式值为真，如果表达式或字段的值包含在子查询中则表达式值为真，而而NOT IN正相反。正相反。 EXISTS：若子查询中有返回记录，则结果为真。若子查询中有返回记录，则结果为真。 【例例6-17】 ANY和和SOME的用法的用法 v求选修求选修“101”号课的学生的学号和成绩，这些号课的学生的学号和成绩，这些学生的成绩比选修学生的

27、成绩比选修“102”号课的最低成绩要高。号课的最低成绩要高。v解：解：SELECT 学号学号,成绩成绩 FROM 必修课成绩表必修课成绩表 WHERE 课号课号=101 and 成绩成绩any（select 成成绩绩 FROM 必修课成绩表必修课成绩表 WHERE 课号课号=102） 图图6-13 例例6-17的执行结果的执行结果【例例6-18】 ALL的用法的用法 v求选修了求选修了“101”号课的学生的学号和成绩，这些学号课的学生的学号和成绩，这些学生的成绩比选修了生的成绩比选修了“102”课的任何学生的成绩都要课的任何学生的成绩都要高。高。v解：解：SELECT 学号学号,成绩成绩 FR

28、OM 必修课成绩表必修课成绩表 WHERE 课号课号=101 and 成绩成绩ALL（SELECT 成成绩绩 FROM 必修课成绩表必修课成绩表 WHERE 课号课号=102） 图图6-14 例例6-18的执行结果的执行结果【例例6-19】 IN的用法的用法 v查找查找“经济管理经济管理”专业的所有必修课的设置情况。专业的所有必修课的设置情况。v解：解：SELECT * FROM 课程表课程表 WHERE 课号课号 IN （SELECT 课号课号 FROM 必修课表必修课表 WHERE 必修必修专业专业=经济管理经济管理） 【例例6-20】 EXISTS的用法的用法 v使用使用EXISTS谓词

29、从谓词从“必修课成绩必修课成绩”表中进行查询，表中进行查询，查询条件是：如果查询条件是：如果“必修课必修课”表中存在课号为表中存在课号为“101”的课程，则从的课程，则从“必修课成绩必修课成绩”表中选出所有选修表中选出所有选修“101”号课程的记录。号课程的记录。vSELECT * FROM 必修课成绩表必修课成绩表 WHERE 课号课号=101 AND EXISTS（SELECT * FROM 必修课表必修课表 WHERE 课号课号=101） 组函数查询组函数查询 vSELECT语句不仅可以通过语句不仅可以通过WHERE子句查找满足条件子句查找满足条件的数据，还可以通过组函数对满足条件的数据

30、进行示最的数据，还可以通过组函数对满足条件的数据进行示最大值、最小值、平均值、求和、计数等统计运算。大值、最小值、平均值、求和、计数等统计运算。 AVG（）（）：求数值字段的平均值。：求数值字段的平均值。 COUNT（）（）：计算所选数据的行数，在分组统计中，用来计：计算所选数据的行数，在分组统计中，用来计算每组数据的行数；其特殊形式是算每组数据的行数；其特殊形式是COUNT（*），用来统计），用来统计满足满足WHERE子句中逻辑表达式的元组个数。子句中逻辑表达式的元组个数。 MIN（）（）：求字符型、日期型或数值型字段的最小值。：求字符型、日期型或数值型字段的最小值。 MAX（）（）：求字符

31、型、日期型或数值型字段的最大值。：求字符型、日期型或数值型字段的最大值。 SUM（）（）：计算数值列的总和。：计算数值列的总和。 v这些组函数一般从一组值中计算出一个汇总信息，这些组函数一般从一组值中计算出一个汇总信息，可使用可使用GROUP BY 子句把字段按照值分为多个子句把字段按照值分为多个组，对每个组进行统计。组，对每个组进行统计。 【例例6-21】 列出各门课的平均成绩、最高成绩、最低成绩和选课人数。列出各门课的平均成绩、最高成绩、最低成绩和选课人数。v 解：解：SELECT 课号课号,AVG（成绩）（成绩） AS 平均成绩平均成绩,MAX（成绩）（成绩） AS 最高成绩最高成绩,MIN（成绩）（成绩） AS 最低成绩最低成绩,COUNT（学号）（学号） AS 人数人数 FROM 必修课成绩表必修课成绩表 GROUP BY 课号课号 【例例6-22】 列出至少选修了三门课的学生姓名。列出至少选修了三门课的学生姓名。 v解：解：SELECT 姓名姓名 FROM 学生表学生表 WHERE 学号学号 IN （SELECT 学号学号 FROM 必修课成绩表必修课成绩表 GROUP BY 学号学号