实验5
SQL查询



一、 实验目的
(1) 掌握SQL的数据查询功能。
(2) 掌握SQL的数据定义功能。
(3) 掌握SQL的数据操纵功能。
二、 实验内容
1. 数据定义和数据操纵

(1) 用CREATE TABLE命令建立“职工”数据表。
(2) 用CREATE TABLE命令建立“工资”数据表。
(3) 用CREATE INDEX命令建立索引。
(4) 建立一个多字段索引XBCSRQ。
(5) 删除“工资备份”表。
(6) 为“职工”表增加一个电话号码字段。
(7) 为“职工”表删除一个“电话号码”字段。
(8) 在“职工”表尾部添加新记录。
(9) 更新数据，计算“工资”表中的实发数。
(10) 删除数据，将“职工”表中女职工的记录删除。
2.  数据查询
(1) 在SQL视图中修改已建查询中的准则。
(2) SQL的单表查询。
(3) SQL的计算查询。
(4) SQL的多表查询。
(5) SQL的参数查询。
(6) SQL的联合查询。
(7) SQL的嵌套查询。
三、 实验步骤
(一) 数据定义和数据操纵
1. 用CREATE TABLE命令建立“职工”数据表
建立“职工管理”数据库，并在库中建立一个“职工”数据表，表由“职工号”“姓名”“性别”“出生日期”“婚否”字段组成，其中，设置“职工号”为主键。
操作步骤如下。
(1) 启动Access，创建“职工管理”数据库。
(2) 选择“创建”选项卡中的“查询”选项组。
(3) 单击“查询设计”按钮，弹出“显示表”对话框。
(4) 关闭弹出的“显示表”对话框，打开查询“设计视图”窗口。
(5) 选择“设计”选项卡→“查询类型”→“数据定义”命令，打开“查询”窗口。
(6) 在“查询”窗口中输入SQL语句。
CREATE TABLE 职工(职工号 TEXT(4) PRIMARY KEY,姓名 TEXT(4),性别 TEXT(1),出生日期 DATE,婚否 LOGICAL)
(7) 保存查询为“职工数据表定义”，查询建立完毕。
(8) 单击功能区上的“运行”按钮，执行SQL语句，完成“职工”表的创建操作。
(9) 在导航窗格中选定“表”对象，可以看到在表列表框中多了一个“职工”表。
在“设计视图”窗口中打开职工表，显示的表结构如图5．1所示。


图5．1职工表结构



实
验
5

SQL查询


Access数据库应用技术实验教程（第4版）
2. 用CREATE TABLE命令建立“工资”数据表，并通过“职工号”字段建立与
“职工”表的关系
操作步骤如下。
(1) 重复前面(2)~(6)的操作步骤，输入如下SQL语句。
CREATE TABLE 工资(职工号 TEXT(4) PRIMARY KEY REFERENCES 职工,工资 single,应扣 single,实发 single)
(2) 保存查询为“工资数据表定义”，查询建立完毕。
(3) 运行“工资数据表定义”查询，完成“工资”表的创建操作。
单击“数据库工具”选项卡中“关系”选项组中的“关系”按钮
，在打开的“关系”窗口中可以看到两个表的结构及两个表之间已经建立的关系，如图5．2所示。


图5．2“职工”表与“工资”表


(4) 双击关系的联线，弹出如图5．3所示的对话框，将3个复选框都选中，则两表之间的关系为一对一。


图5．3编辑关系


3. 用CREATE INDEX命令建立索引为“工资”的表并按“工资”字段建立一个降序索引
操作步骤如下。
(1) 选择“创建”选项卡中的“查询”选项组。
(2) 单击“查询设计”按钮，弹出“显示表”对话框。
(3) 关闭弹出的“显示表”对话框，打开查询设计视图窗口。
(4) 单击“设计”选项卡→“查询类型”→“数据定义”按钮，打开“查询”窗口。
(5) 在“查询”窗口中输入SQL语句如下： 
CREATE INDEXGZ ON 工资(工资 DESC)
(6) 单击功能区上的“运行”按钮，执行SQL语句。
4. 为“职工”表按“性别”和“出生日期”字段建立一个多字段索引XBCSRQ
其SQL语句如下。

CREATE INDEX XBCSRQ ON 职工(性别,出生日期 ASC)

在设计视图中打开“职工”表，单击功能区上的“索引”按钮，打开“索引”对话框，如图5．4所示。


图5．4“索引”对话框


5. 删除“工资备份”表
操作步骤如下。
(1) 为“工资”表建立一个备份，命名为“工资备份”表。
(2) 打开“数据定义查询”窗口。
(3) 输入以下删除表的SQL语句。

DROP TABLE 工资备份

(4) 单击功能区上的“运行”按钮，执行SQL语句，完成删除表的操作，则“工资备份”表将从“职工管理”数据库窗口消失。
6. 为“职工”表增加一个“电话号码”字段
操作步骤如下。
(1) 打开“数据定义查询”窗口。
(2) 输入以下SQL语句。

ALTER TABLE 职工 ADD 电话号码 Char(8)

(3) 单击功能区上的“运行”按钮，执行SQL语句。
7. 删除“职工”的“电话号码”字段
操作步骤如下。
(1) 打开“数据定义查询”窗口。
(2) 输入以下SQL语句。

ALTER TABLE 职工 DROP 电话号码

(3) 单击功能区上的“运行”按钮，执行SQL语句。
8.  在“职工”表尾部添加一条新记录
操作步骤如下。
(1) 在“职工管理”数据库窗口中，打开数据定义“查询”窗口。
(2) 在数据定义查询窗口中，输入以下插入数据的SQL语句。

INSERT INTO 职工(职工号,姓名,性别,出生日期,婚否)

VALUES("1001","张明","男",#1975-03-09#,yes)

(3) 单击功能区中的“运行”按钮，执行SQL语句，完成插入数据的操作。
(4) 在“数据表视图”中打开“职工”表，查看显示结果。
9. 在“职工”表尾部添加第二条新记录
其SQL语句如下。

INSERT INTO 职工 VALUES("1002","王芳","女",#1992-07-21#,no)

在“数据表视图”中打开“职工”表，查看结果。
10. 更新数据，计算“工资”表中的实发数
操作步骤如下。
(1) 在“职工管理”数据库窗口中，打开数据定义“查询”窗口。
(2) 在数据定义“查询”窗口中，输入更新数据的SQL语句。

UPDATE 工资 SET 实发=工资-应扣

(3) 单击功能区上的“运行”按钮，执行SQL语句，完成更新数据的操作。
(4) 在“数据表视图”中打开“工资”表，查看更新结果。
11. 删除数据，将“职工”表中女职工的记录删除
操作步骤如下。
(1) 在“职工管理”数据库窗口中，打开数据定义查询窗口。
(2) 在数据定义查询窗口中，输入以下删除数据的SQL语句。

DELETE FROM 职工 WHERE 性别="女"

(3) 单击功能区上的“运行”按钮，执行SQL语句，完成删除数据的操作。
(4) 在“数据表视图”中打开“职工”表，查看显示结果。
(5) 关闭“职工管理”数据库。
(二) 数据查询
1. 在SQL视图中修改已建查询中的准则
在SQL视图中将“教学管理系统”数据库已经建立的“获得奖励的女生”查询中的准则改为“获得奖励的男生”。
操作步骤如下。
(1) 打开“教学管理系统”数据库。
(2) 在“设计视图”中打开已建立的查询“获得奖励的女生”，如图5．5所示。


图5．5“通过奖励的女生”的设计视图


(3) 单击功能区上的“视图”按钮下侧的向下箭头按钮，从下拉列表中选择“SQL视图”选项，打开SQL视图窗口，如图5．6所示。


图5．6“获得奖励的女生”的SQL视图


(4) 在如图5．6所示窗口中选中要进行修改的部分，将条件="女"改为="男"。修改结果如下。

SELECT 学生.学号, 学生.姓名, 学生.性别, 学生.奖励否

FROM 学生

WHERE (((学生.性别)="男") AND ((学生.奖励否)=True));

(5) 单击“视图”按钮，在数据表视图中预览查询的结果。
(6) 单击“文件”菜单下的“另存为”命令，保存本次查询为“获得奖励的男生”。
2. 查询学生表的全部字段
操作步骤如下。
(1) 选择“创建”选项卡中的“查询”选项组。
(2) 单击“查询设计”按钮，弹出“显示表”对话框。
(3) 关闭弹出的“显示表”对话框，打开查询“设计视图”窗口。
(4) 单击“设计”选项卡的“结果”组中的“视图”按钮，选择“SQL视图”命令，打开“查询”窗口。
(5) 在“查询”窗口中输入以下SQL语句： 

SELECT * FROM 学生

(6) 在数据表视图中查看查询结果，然后保存查询，查询建立完毕。
(7) 在设计视图中，单击功能区上的“运行”按钮，显示运行查询的结果。
下面列举一些与前面操作类似的查询，它们的操作步骤与前面的操作步骤基本一致，只是SQL语句不同。注意观察运行结果。
3.  在学生表中查询学号、姓名字段
操作步骤与上例相同，其中SQL语句如下。

SELECT 学号,姓名 FROM 学生

4.  查询学生数据表的全部学生的姓名和年龄，去掉重名


SELECT DISTINCT 姓名,YEAR(DATE())-YEAR(出生日期) AS 年龄 FROM 学生

5.  查询学生数据表学号为17020002和17030005的记录

SELECT * FROM 学生 WHERE 学号 IN("17020002","17030005")

或： 

SELECT * FROM 学生 WHERE学号="17020002" OR 学号="17030005"
6.  查询成绩表中成绩为70~90分的学生记录

SELECT * FROM 成绩 WHERE 成绩 BETWEEN 70 AND 90

或： 

SELECT * FROM 成绩 WHERE 成绩>=70 AND 成绩<=90

7.  查询学生表中姓“王”的男学生的记录

SELECT * FROM 学生 WHERE 姓名 LIKE "王*" AND 性别="男" 

8.  计算查询，在学生表中统计学生人数

SELECT COUNT(*) AS 学生人数 FROM 学生

9.  查询会计专业学生人数

SELECT COUNT(*) AS 会计专业学生人数 FROM 学生 WHERE 专业="会计"

10.  统计成绩表中不同课程的成绩字段的最大值和最小值

SELECT 课程号, MAX(成绩) AS 成绩最大值, MIN(成绩) AS 成绩最小值

FROM 成绩

GROUP BY 课程号

11.  分别统计男女学生的人数

SELECT 性别, COUNT(性别) AS 人数 FROM 学生 GROUP BY 性别

12.  按学号升序查询学生表中的记录

SELECT * FROM 学生 ORDER BY 学号 ASC

13.  在成绩表中统计有4个以上学生选修的课程

SELECT 课程号,COUNT(*) AS 选课人数

FROM 成绩

GROUP BY 课程号 HAVING COUNT(*)>=4

14.  创建多表查询
在“学生”表、“成绩”表和“课程”表中，查询“学号”“姓名”“课程名称”“成绩”字段，并将查询结果按“学号”排序。

SELECT 学生.学号, 学生.姓名, 课程.课程名称, 成绩.成绩

FROM 学生,课程,成绩

WHERE 课程.课程号 = 成绩.课程号 AND 学生.学号 = 成绩.学号

ORDER BY 学生.学号

15. 查询教师的编号，姓名和课程名称
在“教师”表、“开课教师”表和“课程”表中，查询开课教师的“教师编号”“姓名”“课程名称”字段。

SELECT 教师.教师编号, 教师.姓名, 课程.课程名称

FROM 课程 INNER JOIN (教师 INNER JOIN 开课教师 ON 教师.教师编号 = 开课教师.教师编号) ON 课程.课程号 = 开课教师.课程号

16.  查询课程考试成绩在前3名的学生的学号，姓名，课程名称，成绩信息

SELECT TOP 3 学生.学号, 学生.姓名, 课程.课程名称, 成绩.成绩

FROM 学生 INNER JOIN (课程 INNER JOIN 成绩 ON 课程.课程号 = 成绩.课程号) ON 学生.学号 = 成绩.学号

ORDER BY 成绩.成绩 DESC

17. 按输入的学号和课程名称查询学生成绩信息

SELECT 学生.学号, 学生.姓名, 课程.课程名称, 成绩.成绩

FROM 学生 INNER JOIN (课程 INNER JOIN 成绩 

ON 课程.课程号 = 成绩.课程号) ON 学生.学号 = 成绩.学号

WHERE (学生.学号=［请输入学号:］) AND (课程.课程名称=［请输入课程名称:］)

18. 创建联合查询，查询学生成绩大于80或小于60的学生记录
操作步骤如下。
(1) 选择“创建”选项卡中的“查询”选项组。
(2) 单击“查询设计”按钮，弹出“显示表”对话框。
(3) 关闭弹出的“显示表”对话框，打开查询设计视图窗口。
(4) 选择“设计”选项卡的“查询类型”组中的“联合”命令，打开“查询”窗口。
(5) 在“查询”窗口中输入以下SQL语句。

SELECT * FROM 成绩 WHERE 成绩>=80

UNION

SELECT * FROM 成绩 WHERE 成绩<60

(6) 保存查询，并在数据表视图中查看查询结果。
(7) 在设计视图中，单击功能区上的“运行”按钮，显示运行查询的结果。
19.  嵌套查询，查询选修了课程名称为“大学英语”的学生的学号
在SQL视图窗口中，输入以下命令： 

SELECT 学号 FROM 成绩 WHERE 课程号 IN

(SELECT 课程号 FROM 课程 WHERE 课程名称="大学英语")

20. 查询选修“大学英语”或“高等数学”的所有学生的学号

SELECT 学号 FROM 成绩 WHERE 课程号=ANY

(SELECT 课程号 FROM 课程

WHERE 课程名称="大学英语" OR 课程名称="高等数学")

21.  查询没有选修课程的学生信息

SELECT 学生.学号, 学生.姓名, 学生.专业

FROM 学生 LEFT JOIN 成绩 ON 学生.［学号］ = 成绩.［学号］

WHERE (((成绩.学号) IS NULL))

或者： 

SELECT 学生.学号, 学生.姓名, 学生.专业

FROM 学生

WHERE (学生.学号 NON IN (SELECT 成绩.学号 FROM 成绩))