第七章-关系数据库标准语言SQL

第七章关系数据库标准语言SQLSQL语言，即结构化查询语言(StructuredQueryLanguage)。该语言的功能十分强大，所有表的建立、查询、索引等功能都可以用几条SQL语句完成。本章简要介绍SQL语言的常用语句。只要求掌握这些语句的基本语法及使用，重点掌握SELECT查询语句的一般使用。

7.1SQL概述SQL是一个功能强大且十分庞大的语言。其标准文档就有600多页。我们只需要掌握能够在Access环境中使用简单的SQL语句完成想要执行的操作。SQL语言是一个非过程化语言，其大多数语句都可独立执行并完成一个特定操作、与上下文无关。在Access中使用SQL查询，由用户输入SQL语句，能够实现各种的查询的功能，甚至有些功能只能通过SQL查询完成。

SQL语言的特点：1.高度综合SQL语言可用于表示用户对数据库的所有操作，而所有对数据库的操作是由几个命令来完成的。2.非过程化用户只需要描述要做什么，而不必告诉系统如何去做。3.采用面向集合的操作方式该语言的操作对象和操作结果都是元组的集合，即表。现在很多数据库应用开发工具都将SQL直接融入自身中，Access也不例外。

SQL语言的功能：SQL语言提供数据定义、数据查询与数据控制三大类功能。下表为Access所支持的功能。功能支持的SQL语句详细功能数据定义CREATETABLE/INDEXALTERTABLE/INDEXDROPTABLE/INDEX定义、修改与删除基本表和索引。数据更新INSERT、UPDATE、DELETE插入数据；修改数据；删除数据数据查询SELECT数据库各种查询

7.2SQL语言的数据定义

SQL语言的数据类型：

注意：不同数据库管理系统所提供的SQL数据类型不完全相同。(教材表7.2中TINYINT、DECIMAL、UNIQUEIDENTIFIER、CHARACTER在Access中不能使用。另外，表中TEXT与CHAR等同)掌握以下数据类型：数据类型Access中数据类型说明CHAR/TEXT字符熟悉掌握DATETIME/DATE日期/时间熟悉掌握SMALLINT短整型熟悉掌握REAL单精度型熟悉掌握INTEGER/INT长整型熟悉掌握FLOAT双精度型BIT/LOGICAL

逻辑型MEMO备注MONEY货币IMAGEOLE对象

数据定义：

SQL语言的数据定义主要包括表和索引的创建、修改和删除操作。

1.建立数据表

CREATETABLE<表名>(<列名1><数据类型1>[<长度>][<列级完整性约束1>][,<列名2><数据类型2>[(长度)][<列级完整性约束2>]][,…][,<表级完整性约束1>]][,…])；

功能：创建一个表，指定列属性定义的表结构。说明：(1)[]可选项；<>名词不可拆分项；[,…]前面的项可重复多次。(2)表的完整性约束一般分列级和表级两种，定义格式如下：①NOTNULL列级约束。输入数据时不允许字段值为空。系统默认值为NULL。其作用与Access表的设计视图中“必填字段”相同。

例如，CREATETABLE系(系编号 CHAR(5)NOTNULL);

②PRIMARYKEY表或列级约束。该约束可在列级或表级定义。如果单个字段做主健，可在“<列名><数据类型名><长度>”的后面写“PRIMARYKEY。如果多个字段做主键，则需在所有字段定义完成后写“PRIMARYKEY(<字段名1>,<字段名2>,…)”。其作用与Access表的设计视图中“主键”相同。例如，CREATETABLE系(系编号CHAR(5)NOTNULLPRIMARYKEY,系名称CHAR(20),系主任CHAR(20),学院编号CHAR(5));或CREATETABLE系(系编号CHAR(5)NOTNULL,系名称CHAR(20),系主任CHAR(20),学院编号CHAR(5),PRIMARYKEY(系编号));③FOREIGNKEY外键约束。该约束可在列级或表级定义。如果外键只有一列，可定义为列级约束。如果外键包含一到多个列，可定义为表级约束。例如，CREATETABLE系(系编号CHAR(5)NOTNULLPRIMARYKEY,系名称CHAR(20),系主任CHAR(20),学院编号CHAR(5)REFERENCES学院(学院编号));或CREATETABLE系(系编号CHAR(5)NOTNULL,系名称CHAR(20),系主任CHAR(20),学院编号CHAR(5),PRIMARYKEY(系编号)，FOREIGNKEY(学院编号)REFERENCES学院(学院编号));

说明：系编号是主键，NOTNULL可省略。注意在参照表（表名由REFERENCES后面的<表名>定义）中，这些对应字段必须是已建立了唯一性索引的，否则该建表语句无法执行。④CHECK是域完整性约束，用于输入列值时对输入数据进行有效性检查。其作用与Access表的设计视图中“有效性规则”相同。⑤UNIQUE唯一性约束，要求不同记录在此字段上取值不能相等。

例如，CREATETABLE系(系编号 CHAR(5)NOTNULL,系名称CHAR(20)NOTNULLUNIQUE,系主任CHAR(20),学院编号CHAR(5),PRIMARYKEY(系编号),FOREIGNKEY(学院编号)REFERENCES学院(学院编号));

说明：UNIQUE唯一性约束则要求姓名不能有重名。另外，完整性约束主要掌握前2种。

2.修改数据表

ALTERTABLE<表名>[ADD<列名><数据类型>[<完整性约束>][,…]][DROP[[[CONSTRAINT]<约束名>]|[COLUMN<列名>]][,…]][ALTER<列名><数据类型>[,…]];

功能：修改表的结构。说明：(1)<表名>是要修改结构的表的名字(2)ADD用于增加新列和新的完整性约束条件(3)DROP用于删除指定列或完整性约束条件(4)ALTER用于修改表的列宽度、数据类型等。注:参数CONSTRAINT无效，参数COLUMN可省略。例7.2在学生表上，给出完成下列操作的SQL语句。(1)增加“入学时间”列，其数据类型为日期类型(2)将学号字段由字符类型改为整数类型，(3)增加学号字段不能为空的完整性约束条件(4)删除“班级编号”字段。完成上述操作的SQL语句依次为：ALTERTABLE学生ADD入学时间DATE;ALTERTABLE学生ALTERCOLUMN学号INT;

ALTERTABLE学生ADDCONSTRAINTNOTNULL(学号);ALTERTABLE学生DROP班级编号;

说明，该语句主要掌握增加一个字段、删除一个字段的简单命令。

3.删除数据表格式：DROPTABLE<表名>功能：删除表。说明：表一旦被删除，表中的数据、此表上建立的索引和查询等结构都将自动被删除，并且无法恢复，因此一定要格外小心。

例7.3删除学生和班级两张表。DROPTABLE班级;DROPTABLE学生;

4.索引操作语句(1)建立索引CREATE[UNIQUE][CLUSTERED]INDEX<索引名>ON<表名>(<列名1>[ASC∣DESC][,<列名2>[ASC∣DESC]][,……])功能：为表创建索引。

例7.4(1)为系表建立系编号字段上的唯一索引(2)为系表建立学院编号字段上的普通索引SQL语句如下：CREATEUNIQUEINDEX系表的系编号ON系(系编号);CREATEINDEX系表的学院编号ON系(学院编号);(2)删除索引格式：DROPINDEX索引名功能：删除索引。

例7.5给出删除例7.4建立的两个索引的SQL语句。DROPINDEX系表的系编号;DROPINDEX系表的学院编号;7.3SQL的数据查询

SELECT语句：(该命令掌握程度以教材例题为准)SELECT[ALL|DISTINCT]<列名>|<目标列表达式>|<函数>[,……]FROM<表名或视图名>[,……][WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC][DESC]]功能：创建一个由指定范围内、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集。(1)WHERE<条件表达式>：符合条件的记录集。(2)<函数>：查询计算函数。(3)GROUPBY<分组字段名>：查询结果按指定字段分组。(4)HAVING<条件表达式>：只对满足条件的查询结果分组。(5)ORDERBY<排序字段>[ASC][DESC]：按指定字段排序(6)ALL|DISTINCT的含义：ALL是指显示所有满足条件的记录，DISTINCT则指只显示不完全相同结果的记录。简单查询语句：是指数据来源是一个表或一个视图的查询操作，它是最简单的查询操作。1.检索表中所有的行和列

例7.6假设学院表包含的数据如表所示，编写查询语句查询所有学院的各项信息。学院编号学院名称院长姓名电话地址a信息学院刘万85285555嵩山b地理学院宽带里65433213东山…

SELECT学院编号,学院名称,院长姓名,电话,地址FROM学院；SELECT*FROM学院；

注：如果查询包括所有列，可用*号代替。

2.检索表中指定的列

例7.7查询所有学院的名称和联系方式。

SELECT学院名称,电话,地址FROM学院；3.检索表中指定的列和产生新列

例7.8查询员工工资表，输出员工编号、姓名、时间、扣款、税款和实发工资。假设员工工资表结构为：工资(员工编号CHAR(10),姓名CHAR(10),时间DATE,应发工资REAL,扣款REAL,税款REAL)。SELECT员工编号,姓名,时间,扣款,税款,应发工资-扣款-税款AS实发工资FROM工资;例7.9在学生表中，显示学号、姓名和年龄三个字段，且将学号字段的标题显示为“学生号码”。SELECT学号AS学生号码,姓名，year(now())-year(出生年月)AS年龄FROM学生;4.检索表中指定的列和指定的行查询满足条件的记录可以通过WHERE子句来实现。例7.10查询信息学院的名称和联系方式。SELECT学院名称,院长姓名,电话,地址FROM学院WHERE学院名称='信息学院';例7.11查询有不及格成绩的学生的学号、课程编号和成绩。SELECT学号,课程编号,成绩FROM选课WHERE成绩<60;例7.12查询年龄在20-23岁的学生的学号、姓名和出生年月。SELECT学号,姓名,出生年月FROM学生WHEREyear(now())-year(出生年月)BETWEEN20AND23;或SELECT学号,姓名,出生年月FROM学生WHERE(year(now())-year(出生年月)>=20)AND(year(now())-year(出生年月)<=23);

5.字符串匹配关键词“LIKE”用以进行字符串的匹配，其格式如下：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]表示查找指定属性列的值与<匹配串>相匹配的元组。<匹配串>通常是含有通配符的字符串。Access中通配符目前只支持“*”和“?”。例7.13查询叫张玲玲的学生的详细信息。SELECT*FROM学生WHERE姓名='张玲玲';例7.14查询姓张且姓名只有两个字的同学的姓名、学号、出生年月、籍贯信息。SELECT姓名,学号,出生年月,籍贯FROM学生WHERE姓名LIKE'张?';

例7.16统计每门课程的平均分。SELECT课程编号,AVG(成绩)AS平均分FROM选课GROUPBY课程编号;

例7.15查询姓名中包含“丽”字的学生的姓名、学号、出生年月、籍贯信息。SELECT姓名,学号,出生年月,籍贯FROM学生WHERE姓名LIKE'*丽*';6.检索表中分组统计结果SQL中常用的合计函数与Access中使用的函数相同，利用这些函数可完成简单的数据汇总工作。

例7.17按性别统计学生人数。SELECT性别,COUNT(学号)AS人数FROM学生GROUPBY性别;

例7.19计算课程编号为03001的课程的平均成绩。SELECTAVG(成绩)FROM选课WHERE课程编号='03001';

例7.18统计选课表中各门课程的最高分。SELECT课程编号,MAX(成绩)FROM选课GROUPBY课程编号;

例7.20将学生按出生年月由小到大进行排序。SELECT*FROM学生ORDERBY出生年月;7.检索表中排序结果利用ORDERBY子句可以对查询的结果进行排序。

如果按由大到小进行排序，则：SELECT*FROM学生ORDERBY出生年月DESC;

对例7.16中的查询结果，按平均分由大到小进行排序。SELECT课程编号,AVG(成绩)AS平均分FROM选课GROUPBY课程编号ORDERBY平均分DESC;

8.空值问题

例7.21查询课程中还没有给出所有学生成绩的课程编号。SELECT课程编号FROM选课WHERE成绩ISNULL;连接查询：把多个表的信息集中在一起输出，就要用到“连接”操作，可通过在FROM子句中使用多个表名来实现。多个表名在FROM子句中通过逗号隔开。另外还可以用WHERE子句给定表的连接条件。

1.两表连接

例7.22查询每个学生的姓名、所在班级名称和专业。本例中，连接条件为“学生.班级编号=班级.班级编号”。SELECT学号,姓名,班级名称,专业FROM学生,班级WHERE学生.班级编号=班级.班级编号;

2.多表连接连接操作可以是两个以上表之间进行的，此时连接条件要求必须是两两之间给出。例7.24查询学生的学号和姓名，及所在学院、系和班级的名称。SELECT学院.学院名称,系.系名称,班级.班级名称,学生.学号,学生.姓名FROM学院,系,班级,学生WHERE(学院.学院编号=系.学院编号)AND(系.系编号=班级.系编号)AND(班级.班级编号=学生.班级编号);注：学院.学院名称可直接写成学院名称，因为只有一个表有此字段，其它项也如此。其它查询：

1.嵌套查询在SQL语言中，一个SELECT…FROM…WHERE…语句产生一个新的数据集，一个查询语句完全嵌套到另一个查询语句中的WHERE或HAVING的“条件”短语中，这种查询称为嵌套查询。

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

2.集合查询可以把多个SELECT产生的数据集进行合并。SQL提供的集合操作主要包括并、交和差三类，分别用UNION，INTERSECT和EXCEPT表示，其含义与集合操作相同。注：Access目前不支持INTERSECT和EXCEPT操作。例7.26输出所有教师和学生的姓名和性别字段。SELECT姓名,性别FROM教师UNIONSELECT姓名,性别FROM学生;7.4SQL的数据更新SQL数据更新操作有三类，向表中插入、修改或删