计算机数据库(经济会计类)七讲关系数据库标准语言SQL随堂讲义

1、第七章第七章 关系数据库标准语言关系数据库标准语言SQL SQL结构化查询语言结构化查询语言(Structured Query Language) 的功能十的功能十分强大，所有表的建立、查询、索引等功能都可以用几条语句完分强大，所有表的建立、查询、索引等功能都可以用几条语句完成。成。 本章简要介绍本章简要介绍SQL语言的常用语句。只要求掌握这些语句的语言的常用语句。只要求掌握这些语句的基本语法及使用，重点掌握基本语法及使用，重点掌握SELECT查询语句的一般使用。查询语句的一般使用。 SQL是一个功能强大且十分庞大的语言，其标是一个功能强大且十分庞大的语言，其标准文档就有准文档就有600多页。

2、多页。 在在Access中由用户输入中由用户输入SQL语句，能够实现各种的查询的功能，甚至有些功能语句，能够实现各种的查询的功能，甚至有些功能只能通过只能通过SQL查询完成。查询完成。 SQL语言的特点语言的特点： 1. 高度综合高度综合 SQL语言可用于表示用户对数据库的所有操作，语言可用于表示用户对数据库的所有操作，而所有对数据库的操作是由几个命令来完成的。而所有对数据库的操作是由几个命令来完成的。 2. 非过程化非过程化 用户只需要描述要做什么，而不必告诉系统如用户只需要描述要做什么，而不必告诉系统如何去做。何去做。 3. 采用面向集合的操作方式采用面向集合的操作方式 该语言的操作对象和

3、操作结果都是元组的集合，该语言的操作对象和操作结果都是元组的集合，也就是表。也就是表。 现在几乎所有数据库应用开发工具都将现在几乎所有数据库应用开发工具都将SQL直直接融入自身中，接融入自身中，Access也不例外。也不例外。 SQL语言的语言的功能：功能： SQL语言提供数据定义、语言提供数据定义、数据查询与数据控制数据查询与数据控制三大类功能三大类功能。下表为下表为Access所支持的功能。所支持的功能。功能功能支持的支持的SQL语句语句详细功能详细功能数据定义数据定义CREATE TABLE / INDEXALTER TABLE / INDEXDROP TABLE / INDEX定义、修

4、改与删定义、修改与删除基本表和索引。除基本表和索引。数据更新数据更新INSERT、UPDATE、DELETE插入数据；修改插入数据；修改数据；删除数据数据；删除数据数据查询数据查询 SELECT数据库各种查询数据库各种查询定定义义查查询询 SQL语言的数据类型语言的数据类型： 注意：不同数据库管理系统所提供的注意：不同数据库管理系统所提供的SQL数据类型不完全相同。数据类型不完全相同。(教材表教材表7.2中有些类型在中有些类型在Access中不能使用。中不能使用。 掌握以下掌握以下数据类型数据类型：数据类型数据类型Access中数据类型中数据类型说明说明TEXT/VARCHAR文本文本常用常用

5、, ,掌握掌握CHAR定长文本定长文本常用常用, ,掌握掌握DATETIME / DATE日期日期/ /时间时间常用常用, ,掌握掌握SMALLINT短整型短整型常用常用, ,掌握掌握REAL单精度型单精度型常用常用, ,掌握掌握INTEGER / INT长整型长整型常用常用, ,掌握掌握FLOAT双精度型双精度型常用常用, ,了解了解BIT / LOGICAL 逻辑型逻辑型常用常用, ,了解了解MEMO备注备注常用常用, ,了解了解MONEY货币货币常用常用, ,了解了解 数据定义数据定义： SQL语言的数据定义语言的数据定义主要包括表和索引的创建、修改主要包括表和索引的创建、修改和删除操作

6、。和删除操作。(针对表结构的操作针对表结构的操作) 1. 建立数据表建立数据表 CREATE TABLE ( , (长度长度) , , ,)； 功能：创建一个表，指定列属性定义的表结构。功能：创建一个表，指定列属性定义的表结构。 说明：说明： (1) 可选项；可选项； 名词不可拆分项；名词不可拆分项； , 前面的项可重复多次。前面的项可重复多次。 (2) 表的完整性约束一般分列级和表级两种，定义格式如表的完整性约束一般分列级和表级两种，定义格式如下：下： NOT NULL 列级约束。输入数据时不允许字段值为列级约束。输入数据时不允许字段值为空。系统默认值为空。系统默认值为NULL。其作用与。其

7、作用与Access表的设计视图中表的设计视图中“必填字段必填字段”相同。相同。 例如，例如，CREATE TABLE 职工职工 (工号工号 CHAR(6),姓名姓名 CHAR(6) NOT NULL, 工作时间工作时间 DATE,年龄年龄 SMALLINT,工资工资 REAL); PRIMARY KEY 表或列级约束。如果单个字段做主表或列级约束。如果单个字段做主健，可在健，可在“ ”的后面写的后面写“PRIMARY KEY。如果多个字段做主键，则需在所有字。如果多个字段做主键，则需在所有字段定义完成后写段定义完成后写 “PRIMARY KEY (,)”。其作用与。其作用与Access表的设计

8、视图中表的设计视图中“主键主键”相同。相同。 例如，例如，CREATE TABLE 系系 (系编号系编号 CHAR(6) NOT NULL PRIMARY KEY, 系名称系名称 CHAR(40),系主任系主任 CHAR(8), 学院编号学院编号 CHAR(1); 或或 CREATE TABLE 系系 (系编号系编号 CHAR(6) NOT NULL, 系名称系名称 CHAR(40), 系主任系主任 CHAR(8), 学院编号学院编号 CHAR(1), PRIMARY KEY(系编号系编号); FOREIGN KEY 外键约束。该约束可在列级或表级定外键约束。该约束可在列级或表级定义。义。 如

9、果外键只有一列，可定义为列级约束。如果外键包含如果外键只有一列，可定义为列级约束。如果外键包含一到多个列，可定义为表级约束。一到多个列，可定义为表级约束。 例如，例如，CREATE TABLE 系系 (系编号系编号 CHAR(6) NOT NULL PRIMARY KEY, 系名称系名称 CHAR(40),系主任系主任 CHAR(8),学院编号学院编号 CHAR(1) REFERENCES 学院学院(学院编号学院编号); 或或 CREATE TABLE 系系 (系编号系编号 CHAR(6) NOT NULL,系名称系名称 CHAR(40), 系主任系主任 CHAR(8),学院编号学院编号 CH

10、AR(1), PRIMARY KEY(系编号系编号), FOREIGN KEY(学院编号学院编号) REFERENCES 学院学院(学院编号学院编号); 说明：系编号是主键，说明：系编号是主键， NOT NULL可省略。可省略。 注意在参照表（表名由注意在参照表（表名由REFERENCES后面的后面的 定义）中，这定义）中，这些对应字段必须是已建立了唯一性索引的，否则该建表语句无法执行。些对应字段必须是已建立了唯一性索引的，否则该建表语句无法执行。 CHECK是域完整性约束，用于输入列值时对输入数据进行有效是域完整性约束，用于输入列值时对输入数据进行有效性检查。其作用与性检查。其作用与Acce

11、ss表的设计视图中表的设计视图中“有效性规则有效性规则”相同。相同。(无效无效) UNIQUE唯一性约束，要求不同记录在此字段上取值不能相等。唯一性约束，要求不同记录在此字段上取值不能相等。其作用与其作用与Access表的设计视图中表的设计视图中“索引（无重复）索引（无重复）”相同。相同。 例如，例如，CREATE TABLE 系系 (系编号系编号 CHAR(6) NOT NULL, 系名称系名称 CHAR(40) NOT NULL UNIQUE, 系主任系主任 CHAR(8), 学院编号学院编号 CHAR(1), PRIMARY KEY(系编号系编号), FOREIGN KEY(学院编号学院

12、编号) REFERENCES 学院学院(学院编号学院编号); 说明：说明：UNIQUE 唯一性约束则要求姓名不能有重名。唯一性约束则要求姓名不能有重名。 另外，完整性约束主要掌握前另外，完整性约束主要掌握前2种。种。 2. 修改数据表修改数据表 ALTER TABLE ADD , DROP CONSTRAINT |COLUMN , ALTER , ; 功能：修改表的结构。功能：修改表的结构。 说明：说明： (1) 是要修改结构的表的名字是要修改结构的表的名字 (2) ADD用于增加新列和新的完整性约束条件用于增加新列和新的完整性约束条件 (3) DROP用于删除指定列或完整性约束条件用于删除指

13、定列或完整性约束条件 (4) ALTER用于修改表的列宽度、数据类型等。用于修改表的列宽度、数据类型等。 注注: 参数参数CONSTRAINT无效，参数无效，参数COLUMN可省略。可省略。 例例7.2 在学生表上，给出完成下列操作的在学生表上，给出完成下列操作的SQL语句。语句。 (1) 增加增加“入学时间入学时间”列，其数据类型为日期类型列，其数据类型为日期类型 (2) 将将 “入学时间入学时间”字段改为整数类型，字段改为整数类型， (3) 删除删除“入学时间入学时间”字段。字段。 完成上述操作的完成上述操作的SQL语句依次为：语句依次为： ALTER TABLE 学生学生 ADD 入学时

14、间入学时间 DATE; ALTER TABLE 学生学生 ALTER 入学时间入学时间 INT; ALTER TABLE 学生学生 DROP 入学时间入学时间 ; 说明，该语句主要掌握增加一个字段、删除一个字段的简说明，该语句主要掌握增加一个字段、删除一个字段的简单命令。单命令。(注：不能对主键操作注：不能对主键操作) 3. 删除数据表删除数据表 格式：格式：DROP TABLE 功能：删除表功能：删除表。 说明：表一旦被删除，表中的数据、此表上建立的索引说明：表一旦被删除，表中的数据、此表上建立的索引和查询等结构都将自动被删除，并且无法恢复，因此一定要和查询等结构都将自动被删除，并且无法恢复

15、，因此一定要格外小心。格外小心。 例例7.3 删除学生和班级两张表删除学生和班级两张表。 DROP TABLE 班级班级; DROP TABLE 学生学生; 注：如果学生表与班级表有关联，需选删除学生表，再注：如果学生表与班级表有关联，需选删除学生表，再删除班级表。删除班级表。 4. 索引操作语句索引操作语句 (1) 建立索引建立索引 CREATE UNIQUE CLUSTERED INDEX ON ( ASC DESC , ASC DESC ,) 功能：为表创建索引功能：为表创建索引。 例例7.4 (1) 为系表建立系编号字段上的唯一索引为系表建立系编号字段上的唯一索引 (2) 为系表建立学

16、院编号字段上的为系表建立学院编号字段上的一般一般索引索引 SQL语句如下：语句如下： CREATE UNIQUE INDEX 系表的系编号系表的系编号 ON 系系(系编号系编号); CREATE INDEX 系表的学院编号系表的学院编号 ON 系系(学院编号学院编号); 注注:按设计视图打开表按设计视图打开表,点击视图点击视图/索引索引,可看到已创建的索引。可看到已创建的索引。 (2) 删除索引删除索引 格式：格式：DROP INDEX 索引名索引名 ON 功能：删除索引功能：删除索引。 例例7.5 给出删除例给出删除例7.4建立的两个索引的建立的两个索引的SQL语句语句。 DROP INDE

17、X 系表的系编号系表的系编号 ON 系系; DROP INDEX 系表的学院编号系表的学院编号 ON 系系; SELECT语句语句：(该命令掌握程度以教材例题为准该命令掌握程度以教材例题为准) SELECT ALL|DISTINCT | , FROM , WHERE GROUP BY HAVING ORDER BY ASC DESC 功能：创建一个由指定范围内、满足条件、按某字段分组、按某字段功能：创建一个由指定范围内、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集。排序的指定字段组成的新记录集。 (1) WHERE ：符合条件的记录集。：符合条件的记录集。 (2) ：查询计算函

18、数。：查询计算函数。 (3) GROUP BY ：查询结果按指定字段分组。：查询结果按指定字段分组。 (4) HAVING ：只对满足条件的查询结果分组。：只对满足条件的查询结果分组。 (5) ORDER BY ASCDESC：按指定字段排序：按指定字段排序 (6) ALL|DISTINCT的含义：的含义：ALL是指显示所有满足条件的记录是指显示所有满足条件的记录(默默认认)，DISTINCT则指只显示不完全相同结果的记录，可用于过滤重复则指只显示不完全相同结果的记录，可用于过滤重复 。 简单查询语句：简单查询语句： 是指数据来源是一个表或一个视图的查询操作，它是最是指数据来源是一个表或一个视

19、图的查询操作，它是最简单的查询操作。简单的查询操作。 1. 检索表中所有的行和列检索表中所有的行和列 例例7.6 查询所有学院的各项信息。查询所有学院的各项信息。 SELECT 学院编号学院编号,学院名称学院名称,院长姓名院长姓名, 电话电话,地址地址 FROM 学院学院; 或或 SELECT * FROM 学院学院; 注：如果查询包括所有列，可用注：如果查询包括所有列，可用*号代替。号代替。 2. 检索表中指定的列检索表中指定的列 例例7.7 查询所有学院的名称和联系方式。查询所有学院的名称和联系方式。 SELECT 学院名称学院名称,电话电话, 地址地址 FROM 学院学院; 3. 检索表

20、中指定的列和产生新列检索表中指定的列和产生新列 例例7.8 查询员工工资表，输出员工编号、姓名、时间、查询员工工资表，输出员工编号、姓名、时间、扣款、税款和实发工资。假设员工工资表结构为：工资扣款、税款和实发工资。假设员工工资表结构为：工资(员工员工编号编号CHAR(10), 姓名姓名 CHAR(10), 时间时间 DATE, 应发工资应发工资 REAL, 扣款扣款 REAL, 税款税款 REAL)。 SELECT 员工编号员工编号, 姓名姓名, 时间时间, 扣款扣款, 税款税款, 应发工资应发工资-扣款扣款-税款税款 AS 实发工资实发工资 FROM 工资工资; 例例7.9 在学生表中，显示

21、学号、姓名和年龄三个字段，在学生表中，显示学号、姓名和年龄三个字段，且将学号字段的标题显示为且将学号字段的标题显示为“学生号码学生号码”。 SELECT 学号学号 AS 学生号码学生号码, 姓名姓名, year(now()-year(出生年月出生年月) AS 年龄年龄 FROM 学生学生; 4. 检索表中指定的列和指定的行检索表中指定的列和指定的行 查询满足条件的记录可以通过查询满足条件的记录可以通过WHERE子句来实现。子句来实现。例例7.10 查询信息学院的名称和联系方式。查询信息学院的名称和联系方式。 例例7.11 查询有不及格成绩的学生的学号、课程编号和成绩。查询有不及格成绩的学生的学

22、号、课程编号和成绩。 SELECT 学院名称学院名称, 电话电话, 地址地址 FROM 学院学院 WHERE 学院名称学院名称 = 信息学院信息学院; SELECT 学号学号, 课程编号课程编号, 成绩成绩 FROM 选课选课 WHERE 成绩成绩= 20) AND (year(now()-year(出生年月出生年月) = 23); 5. 字符串匹配字符串匹配 关键词关键词“LIKE” 用以进行字符串的匹配，其格式如下：用以进行字符串的匹配，其格式如下： NOTLIKE ESCAPE 表示查找指定属性列的值与表示查找指定属性列的值与相匹配的元组。相匹配的元组。通常是含有通配符的字符串。通常是含

23、有通配符的字符串。Access中通配符目前只支中通配符目前只支持持“*”和和“?” 。 例例7.13 查询叫张玲玲的学生的详细信息。查询叫张玲玲的学生的详细信息。 SELECT * FROM 学生学生 WHERE 姓名姓名=张玲玲张玲玲; 例例7.14 查询姓张且姓名只有两个字的同学的姓名、学号、查询姓张且姓名只有两个字的同学的姓名、学号、出生年月、籍贯信息。出生年月、籍贯信息。 SELECT 姓名姓名, 学号学号, 出生年月出生年月, 籍贯籍贯 FROM 学生学生 WHERE 姓名姓名 LIKE 张张?; 例例7.15 查询姓名中包含查询姓名中包含“丽丽”字的学生的姓名、学号、字的学生的姓名

24、、学号、出生年月、籍贯信息出生年月、籍贯信息。 SELECT 姓名姓名, 学号学号, 出生年月出生年月, 籍贯籍贯 FROM 学生学生 WHERE 姓名姓名 LIKE *丽丽*; 例例7.16 统计每门课程的平均分统计每门课程的平均分。 6. 检索表中分组统计结果检索表中分组统计结果 SQL中常用的统计函数与中常用的统计函数与Access中使用的函数相同，利中使用的函数相同，利用这些函数可完成简单的数据汇总工作。用这些函数可完成简单的数据汇总工作。 例例7.17 按性别统计学生人数按性别统计学生人数。 SELECT 课程编号课程编号, AVG(成绩成绩) AS 平均分平均分 FROM 选课选课

25、 GROUP BY 课程编号课程编号; SELECT 性别性别, COUNT(学号学号) AS 人数人数 FROM 学生学生 GROUP BY 性别性别; 例例7.19 计算课程编号为计算课程编号为03001的课程的平均成绩的课程的平均成绩。 例例7.18 统计选课表中各门课程的最高分统计选课表中各门课程的最高分。 SELECT 课程编号课程编号, MAX(成绩成绩) AS 最高分最高分 FROM 选课选课 GROUP BY 课程编号课程编号; SELECT AVG(成绩成绩) AS 平均分平均分 FROM 选课选课 WHERE 课程编号课程编号=03001; SELECT * FROM 学生

26、学生 ORDER BY 出生年月出生年月; 7. 检索表中排序结果检索表中排序结果 利用利用ORDER BY子句可以对查询的结果进行排序。子句可以对查询的结果进行排序。 如果按由大到小进行排序，则：如果按由大到小进行排序，则： SELECT * FROM 学生学生 ORDER BY 出生年月出生年月 DESC; 例例7.20 将学生按出生年月由小到大进行排序将学生按出生年月由小到大进行排序。 对例对例7.16中的查询结果，按平均分由大到小进行排序中的查询结果，按平均分由大到小进行排序。 8. 空值问题空值问题 例例7.21 查询课程中还没有给出所有学生成绩的课程编号查询课程中还没有给出所有学生

27、成绩的课程编号。 SELECT 课程编号课程编号, AVG(成绩成绩) AS 平均分平均分 FROM 选课选课 GROUP BY 课程编号课程编号 ORDER BY 平均分平均分 DESC; SELECT 课程编号课程编号 FROM 选课选课 WHERE 成绩成绩 IS NULL; 连接查询：连接查询： 把多个表的信息集中在一起输出，就要用到把多个表的信息集中在一起输出，就要用到“连接连接”操作，可通过在操作，可通过在FROM子句中使用多个表子句中使用多个表名来实现。多个表名在名来实现。多个表名在FROM子句中通过逗号隔开。子句中通过逗号隔开。另外还可以用另外还可以用WHERE子句给定表的连接

28、条件。子句给定表的连接条件。 1. 两表连接两表连接 例例7.22 查询每个学生的姓名、所在班级名称和专业。查询每个学生的姓名、所在班级名称和专业。本本例中，连接条件为例中，连接条件为“学生学生.班级编号班级编号=班级班级.班级编号班级编号”。 SELECT 学号学号, 姓名姓名, 班级名称班级名称, 专业专业 FROM 学生学生, 班级班级 WHERE 学生学生.班级编号班级编号=班级班级.班级编号班级编号; 2. 多表连接多表连接 连接操作可以是两个以上表之间进行的，此时连接条件连接操作可以是两个以上表之间进行的，此时连接条件要求必须是两两之间给出。要求必须是两两之间给出。 例例7.24

29、查询学生的学号和姓名，及所在学院、系和班级的名称。查询学生的学号和姓名，及所在学院、系和班级的名称。 SELECT 学院学院.学院名称学院名称, 系系.系名称系名称, 班级班级.班级名称班级名称, 学生学生.学号学号, 学生学生.姓名姓名 FROM 学院学院, 系系, 班级班级, 学生学生 WHERE (学院学院.学院编号学院编号=系系.学院编号学院编号) AND (系系.系编号系编号=班级班级.系编号系编号) AND (班级班级.班级编号班级编号=学生学生.班级编号班级编号); 注：学院注：学院.学院名称可直接写成学院名称，因为只有一个表有此字学院名称可直接写成学院名称，因为只有一个表有此字

30、段，其它项也如此。段，其它项也如此。 其它查询：其它查询： 1. 嵌套查询嵌套查询 在在SQL语言中，一个语言中，一个SELECT FROM WHERE 语句产生一个新的数据集，一个查询语句完全嵌套到另一个语句产生一个新的数据集，一个查询语句完全嵌套到另一个查询语句中的查询语句中的WHERE或或HAVING的的“条件条件”短语中，这种查短语中，这种查询称为嵌套查询。询称为嵌套查询。 例如：例如： SELECT * FROM 学生学生 WHERE 学号学号 IN (SELECT 学号学号 FROM 选课选课 WHERE 课程编号课程编号=03001) 该例中内层查询的含义是查找所有选修了该例中内

31、层查询的含义是查找所有选修了“03001”号课程的学生的号课程的学生的学号，这些学号形成一个集合，在此称该集合为学号，这些学号形成一个集合，在此称该集合为S。外层循环的含义是。外层循环的含义是查找学号在查找学号在S中的学生的详细信息。因此中的学生的详细信息。因此,此嵌套查询的含义是查询选修此嵌套查询的含义是查询选修了了“03001”号课程的学生的详细信息。号课程的学生的详细信息。 例例7.25 查询计算机系、地理系和外语系的所有查询计算机系、地理系和外语系的所有学生的姓名和专业。学生的姓名和专业。 SELECT 姓名姓名, 专业专业 FROM 学生学生,班级班级 WHERE 学生学生.班级编号

32、班级编号=班级班级.班级编号班级编号 AND 班级班级.系编号系编号 IN(SELECT 系编号系编号 FROM 系系 WHERE 系名称系名称 IN(计算机系计算机系,地理系地理系,外语系外语系); 2. 集合查询集合查询 SQL提供的集合操作主要包括并、交和差三类，提供的集合操作主要包括并、交和差三类，分别用分别用UNION，INTERSECT和和EXCEPT表示，其含表示，其含义与集合操作相同。义与集合操作相同。 注：注：Access目前不支持目前不支持INTERSECT和和EXCEPT操作。操作。 例例7.26 输出所有教师和学生的姓名和性别字段。输出所有教师和学生的姓名和性别字段。 SELECT 姓名姓名,性别性别 FROM 教师教师 UNION SELECT 姓名姓名,性别性别 FROM 学生学生; SQL数据更新操作有三类，向表中插入、修改数据更新操作有