Access 数据库与程序设计

Access 数据库与程序设计 四川大学计算机学院主讲教师曾新

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

第5章结构化查询语言SQL • 结构化查询语言SQL（Structured Query Language），是关系数据库的标准语言，它具有数据定义、数据操纵、数据查询和数据控制等功能，并且其语言简洁、功能强大，目前广泛用于各大、中、小型关系数据库，已成为世界上最流行的数据库语言之一。

结构化查询语言SQL SQL语言的产生和发展 • 1974年由Boyce和Chamberlin提出，并作为IBM公司研制的关系数据库管理系统原型SYSTEM R的查询语言文本。 • 1982年，ANSI（美国国家标准局）成立SQL标准委员会。 • 1986年10月，ANSI SQL标准被批准作为关系数据库语言的美国标准。公布了SQL标准文本（SQL-86）。 • 1987年6月，ISO（国际标准化组织）将其采纳为国际标准。 • SQL-89、SQL92和SQL99等。 • 目前SQL已被许多DBMS产品如，DB2、INGRES、SYSBASE、SQLServer、VFP和ORACLE等所采用，它已成为关系数据库领域中一个主流语言。

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

结构化查询语言SQL • 简洁化 • SQL语言功能强大并且只用几条命令来完成，其语法也非常简单，很接近自然语言模式。SQL的命令有：数据查询命令（SELECT）、数据定义命令（CREATE、DROP、ALTER）、数据操纵命令（INSERT、UPDATE、DELETE）和数据控制命令（GRANT、REVOKE）。

5.2 SQL语言的数据定义功能 • 打开数据库。 • 在“设计视图” 新建查询，在弹出的“显示表”对话框中直接点击“关闭”。

5.2 SQL语言的数据定义功能 • “查询”－“SQL特定查询”- “数据定义查询”

建立数据表结构 定义数据表的结构。 • CREATE TABLE <表名> (<字段名1> <数据类型1> [(<大小>)] [NOT NULL] [PRIMARY KEY | UNIQUE|REFERENCES 外部表 [(外部关键字)]] [，<字段名2> <数据类型2> [(<大小>)] [NOT NULL] [PRIMARY KEY | UNIQUE|REFERENCES 外部表 [(外部关键字)]] [，...]])

SQL-建立表结构 • 建立一个“学生名册”表结构（CREATE TABLE命令） • CREATE TABLE学生名册 (学号 TEXT(10) PRIMARY KEY, • 姓名 TEXT(8),性别 TEXT(2), • 出生日期 DATE,院系 TEXT(12), • 总分 INTEGER, 奖学金 LOGICAL, • 简历 MEMO, 照片 OLEOBJECT)

选择“数据定义查询”类型 • 写入SQL语句 • 运行查询

使用SQL创建的表结构

CREATE TABLE 课程表 • ( 课程号 TEXT(6) PRIMARY KEY, • 　课程名 TEXT(20), • 　　开课院系 TEXT(12), • 　　学分 INTEGER, • 　　学时 TEXT(2) )

SQL-建立表结构 • 建立“成绩表”,并与“学生名册”建立关系 • CREATE TABLE 成绩 • (学号 TEXT(10) REFERENCES 学生名册(学号), • 课程号 TEXT(6),课序号 TEXT(2), 成绩 INTEGER)

SQL-修改表结构 2、修改基本表 • 增加字段 ALTER TABLE 表名 ADD字段名数据类型 • 修改字段 ALTER TABLE 表名 ALTER字段名数据类型 • 删除字段 ALTER TABLE 表名 DROP 字段名

SQL-修改表结构 • 例：为“学生名册”增加字段一个“电子邮件”字段 ALTER TABLE 学生名册 ADD电子邮件TEXT(20)

增加字段SQL查询

5.3 SQL语言的数据操纵功能 • 1． SQL-插入数据 INSERT INTO 表名（列名1，列名2……） VALUES (表达式1，表达式2……) 【例】向“学生情况”表中插入一个新的学生记录 INSERT INTO 学生情况2 VALUES ("112", "王汉", "男", #10/21/1980#, "586", TRUE, "足球", "1")

5.3 SQL语言的数据操纵功能 • 【例】使用SQL命令向“课程”表中追加插入一条包含部分信息的新记录。 • INSERT INTO 课程 (课程号,课程名称,学分) VALUES("201003","CAD设计",2) • 如果仅为表中的部分字段赋值，应注意字段名称与字段值要一一对应。

5.3 SQL语言的数据操纵功能 • 2．SQL-修改数据 UPDATE 表名 SET 列名1＝表达式1，列名2＝表达式2…… WHERE 条件 UPDATE 学生情况2 SET 性别＝“女” WHERE 姓名＝“王汉”

数据操纵 • 3．SQL-删除数据 DELETE FROM 表名 WHERE 条件

5.4 SQL语言的查询功能 • SELECT [ALL|DISTINCT] [表别名.] 字段名[AS 列名],…. FROM [数据库名!]表名 [WHERE 条件] [GROUP BY 分组名] [HAVING 条件] [UNION[ALL]SELECT语句] [ORDER BY 排序项] 分组中的条件

【命令】 • SELECT [ALL | DISTINCT] [TOP <数值表达式> [PERCENT]] • <目标列选项> [AS <列标题名>][ INTO <新表名>] • FROM <表名1> [[AS] <别名1>],<表名2> [[AS] <别名2>] • [WHERE <连接条件1> [AND <连接条件2> ...] • [AND | OR <过滤条件1> [AND | OR <过滤条件2> ...]]] • [GROUP BY <分组列名1> [, <分组列名2> ...]] • [HAVING <过滤条件>] • [UNION [ALL] <SELECT 命令>] • [ORDER BY <排序项1>[ASC | DESC] [, <排序项2> [ASC | DESC] ...]]

查询实例 • 相关表： • 学生信息表 • 学生成绩表 • 课程信息表

SELECT-SQL简单查询 • 单表查询 • 例1：例出全部学生信息 SELECT * FROM 学生信息表 “*”–全部数据

【例5-11】查询“学生信息表”中所有学生的姓名、性别、出生日期。【例5-11】查询“学生信息表”中所有学生的姓名、性别、出生日期。 SELECT 姓名 AS 学生姓名,性别, 　　　出生日期　AS 生日 FROM 学生信息表

SELECT-SQL简单查询 • 单表查询 • 例2：例出所有女生的学号及姓名 SELECT 学号,姓名 FROM 学生信息表 WHERE 性别=“女”

SELECT-SQL简单查询 • 单表查询 • 例3：例出所有学生的性别 SELECT DISTINCT 性别 FROM 学生信息表 * 列出性别为“男”的学生 SELECT 姓名，性别 FROM 学生情况 WHERE 性别=“男”

SELECT-SQL简单查询 • 单表查询 • 例4：例出课程号为”303001”且成绩大于80分的学生学号及成绩，并按成绩由高到低排序。 SELECT 学号，成绩 FROM 学生成绩表 WHERE 课号=“303001” AND 成绩>=80 ORDER BY 成绩 DESC

SELECT-SQL简单查询 • 单表查询 • 例5：查询84年以后出生的学生。 SELECT 姓名，出生日期 FROM 学生信息表 WHERE 出生日期>=#1984-01-01# 注意日期的写法

SELECT-SQL简单查询 • 单表查询 • 例6：查询不是“艺术学院”的学生。 SELECT 姓名，院系名称 FROM 学生信息表 WHERE 院系名称 <>“艺术学院” • 院系名称 !=“艺术学院”

【例5-13】查询“学生信息表”中所有艺术学院和软件学院学生的学号、姓名和所在院系名称。【例5-13】查询“学生信息表”中所有艺术学院和软件学院学生的学号、姓名和所在院系名称。 • SELECT 学号,姓名,院系名称　FROM 学生信息表 • WHERE 院系名称 IN (”艺术学院”,”软件学院”) • WHERE 院系名称=”软件学院”OR 院系名称=”艺术学院” • WHERE 院系名称 NOT IN (”艺术学院”,”软件学院”)

SELECT-SQL简单查询 • 单表查询 • 例7：查询成绩在70到80之间的学生选课得分情况。 SELECT * FROM 学生成绩表 WHERE 成绩 BETWEEN 70 AND 80 • WHERE 成绩>=70 AND 成绩<=80 • WHERE 成绩 NOT BETWEEN 70 AND 80 • WHERE 成绩<70 or 成绩>80

WHERE 成绩 BETWEEN 70 AND 80 • WHERE 成绩 NOT BETWEEN 70 AND 80

SELECT-SQL简单查询 • 例9：列出所有姓“张”的学生的情况。 SELECT * FROM 学生信息表 WHERE 姓名 LIKE “张??“ • LIKE “张??", “？”代表任意一个字符 • LIKE “张*", “*”代表任意多个字符 • WHERE 姓名 NOT LIKE “张??"

SELECT-SQL统计查询 • AVG-----按列求平均 • SUM -----按列求和 • COUNT -----按列统计个数 • MAX-----求一列中的最大值 • MIN-----求一列中的最小值

统计入学总分大于600分的男生有多少人 • SELECT COUNT (姓名) from 学生信息表 WHERE 入学总分>=600 AND 性别='男' • SELECT COUNT (姓名) AS 合格人数from 学生信息表 WHERE 入学总分>=600 AND 性别='男'

SELECT-SQL统计查询 例10：求全体学生的入校总分、最高分、最低分和平均分 SELECT SUM(入学总分) AS 新生入校总分, MAX(入学总分) AS 新生最高分, MIN(入学总分) AS 新生最低分, AVG(入学总分) AS 新生平均分 FROM 学生信息表

SELECT-SQL统计查询 例11：求入校总分在580分以上的学生人数 SELECT COUNT(*) AS 总分大于580的人数 FROM 学生信息表 WHERE 入学总分> 580

SELECT-SQL统计查询 例13：查出学生成绩表中选修“101002”课程的最高分、最低分和相差分数 SELECT MAX(成绩) AS 最高分, MIN(成绩) AS 最低分, MAX(成绩)- MIN(成绩) AS 相差分数 FROM 学生成绩表 WHERE 学生成绩表.课程号 = "101002";

SELECT-SQL连接查询 • 例14.查询成绩在90分以上的学生记录，并显示该生的学号、姓名、课程号和成绩相关表：学生情况、选课表 • Where 连接: 只连接相等的记录 • 连接条件： WHERE学生信息表.学号=学生成绩表.学号 AND 成绩>=90 SELECT 学生信息表.学号,学生信息表.姓名,学生成绩表.课程号,学生成绩表.成绩 from 学生信息表, 学生成绩表 where 学生信息表.学号=学生成绩表.学号 AND 成绩>=90 SELECT a.学号,姓名,课程号,成绩 from 学生信息表 a, 学生成绩表 b where a.学号=b.学号 AND 成绩>=90 临时定义别名

INNER JOIN 连接 • SELECT 学生信息表2.姓名, 学生成绩表2.成绩 • FROM 学生信息表2 INNER JOIN学生成绩表2 ON学生信息表2.学号 = 学生成绩表2.学号 • WHERE (((学生成绩表2.成绩)>=90));

SELECT 学生信息表2.姓名, 学生成绩表2.成绩FROM 学生信息表2 LEFT JOIN学生成绩表2 ON 学生信息表2.学号 = 学生成绩表2.学号WHERE (((学生成绩表2.成绩)>=90)); LEFT JOIN 连接

RIGHT JOIN 连接 SELECT 学生信息表2.姓名, 学生成绩表2.成绩 FROM 学生信息表2 RIGHT JOIN学生成绩表2 ON 学生信息表2.学号 = 学生成绩表2.学号 WHERE (((学生成绩表2.成绩)>=90));

SELECT-SQL连接查询 • 例15. 列出选修1号课的学生姓名及成绩。 • 相关表：学生情况 • 选课表 • 连接条件： • WHERE 学生名册.学号=选课表.学号 AND 课号=“1” • SELECT * • FROM 学生情况 AS S, 选课表 AS C • WHERE S.学号=C.学号 AND 课号="1"; • 临时定义表的别名S和C可以减少书写长的表名

分组查询:GROUP BY • 例18. 列出各门课的平均成绩、最高成绩、最低成绩和选课人数。 • SELECT 学生成绩表2.课程号, Avg(学生成绩表2.成绩) AS 成绩之平均值, Max(学生成绩表2.成绩) AS 成绩之最大值, Min(学生成绩表2.成绩) AS 成绩之最小值, Count(学生成绩表2.学号) AS 学号之计数 • FROM 学生成绩表2 • GROUP BY 学生成绩表2.课程号;

SELECT 学生成绩表2.课程号, ROUND(Avg(学生成绩表2.成绩),2)AS 平均分, Max(学生成绩表2.成绩) AS 最高分, Min(学生成绩表2.成绩) AS 最低分, Count(学生成绩表2.学号) AS 选课计数 • FROM 学生成绩表2 • GROUP BY 学生成绩表2.课程号; ROUND(X,N) :N给出小数位数

HAVING 子句(函数) • HAVING函数与GROUP BY子句联合使用,可以对分组后的结果作进一步的限制. • 例18a. 列出选课人数超过3人的各门课的平均成绩、最高成绩、最低成绩和选课人数。 • SELECT 学生成绩表2.课程号, Round(Avg(学生成绩表2.成绩),2) AS 平均分, Max(学生成绩表2.成绩) AS 最高分, Min(学生成绩表2.成绩) AS 最低分, Count(学生成绩表2.学号) AS 选课计数 • FROM 学生成绩表2 • GROUP BY 学生成绩表2.课程号 • HAVING Count(学生成绩表2.学号)>3

Access 数据库与程序设计