实验4：MySQL数据库表数据的查询操作
一、实验目的
1．掌握SELECT 语句的基本语法格式。
2．掌握SELECT 语句的执行方法。
3．掌握SELECT 语句的 GROUP BY 和 ORDER BY 子句的作用。
二、实验内容
- 验证性实验：在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。
- 设计性试验：将在student表和score表上进行查询。
三、实验步骤与实验结果
（一）验证性实验
在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。Bumen表和yuangong表的定义如表所示。
表bumen表的定义
|
字段名 |
字段描述 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
d_id |
部门号 |
INT(4) |
是 |
否 |
是 |
是 |
否 |
|
d_name |
部门名称 |
VARCHAR(20) |
否 |
否 |
是 |
是 |
否 |
|
function |
部门职能 |
VARCHAR(20) |
否 |
否 |
否 |
否 |
否 |
|
address |
工作地点 |
VARCHAR(30) |
否 |
否 |
否 |
否 |
否 |
表yuangong表的定义
|
字段名 |
字段描述 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
id |
员工号 |
INT(4) |
是 |
否 |
是 |
是 |
否 |
|
name |
姓名 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
|
sex |
性别 |
VARCHAR(4) |
否 |
否 |
是 |
否 |
否 |
|
birthday |
年龄 |
INT(4) |
否 |
否 |
否 |
否 |
否 |
|
d_id |
部门号 |
INT(4) |
否 |
是 |
是 |
否 |
否 |
|
salary |
工资 |
Float |
否 |
否 |
否 |
否 |
否 |
|
address |
家庭住址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
1.创建bumen表
CREATE TABLE bumen(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCHAR(20) ,
address VARCHAR(30)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
2.向bumen表中插入记录。
INSERT INTO bumen
VALUES
(1001,'人事部','人事管理','北京'),
(1002,'科研部','研发产品','北京'),
(1003,'生产部','产品生产','天津'),
(1004,'销售部','产品销售','上海');
3.操作后查看表
SELECT * FROM bumen;
4.创建yuangong表
CREATE TABLE yuangong(
id int(4) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday INT(4),
d_id INT(4) NOT NULL,
salary Float,
address VARCHAR(50),
FOREIGN KEY(d_id)REFERENCES bumen(d_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.向yuangong表中插入记录
INSERT INTO yuangong
VALUES
(8001,'韩鹏','男',25,1002,4000,'北京市海淀区'),
(8002,'张峰','男',26,1001,2500,'北京市昌平区'),
(8003,'欧阳','男',20,1003,1500,'湖南省永州市'),
(8004,'王武','男',30,1001,3500,'北京市顺义区'),
(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'),
(8006,'呼延','男',28,1003,1800,'天津市南开区');
6.操作后查看表
SELECT * FROM yuangong;
7.然后在bumen表和yuangong表查询记录。查询的要求如下：
（1）查询yuangong表的所有记录。
SELECT * FROM yuangong;
或者列出yuangong表的所有字段名称。
SELECT id,name,sex,birthday,d_id,salary,address FROM yuangong;
（2）查询yuangong表的第四条到第五条记录。
SELECT id,name,sex,birthday,d_id,salary,address
FROM
yuangong
ORDER BY id LIMIT 3,2;
（3）从bumen表查询部门号（d_id）、部门名称（d_name）和部门职能（function）。
SELECT d_id,d_name,function FROM bumen;
（4）从yuangong表中查询人事部和科研部的员工的信息。先从bumen表查询出人事部和科研部的部门号。然后到yuangong表中去查询员工的信息。
SELECT * FROM yuangong
WHERE
d_id=ANY(
SELECT d_id FROM bumen
WHERE
d_name IN('人事部','科研部'));
或者使用下面的代码。
SELECT * FROM yuangong
WHERE
d_id IN(
SELECT d_id FROM bumen
WHERE
d_name='人事部' OR d_name='科研部');
（5）从yuangong表中查询年龄在25到30之间的员工的信息。可以通过两种方式来查询。
第一种方式:
SELECT * FROM yuangong
WHERE
birthday BETWEEN 25 AND 30;
第二种方式:
SELECT * FROM yuangong
WHERE
birthday>=25 AND birthday
（6）查询每个部门有多少员工。先按部门号进行分组，然后用COUNT()函数来计算每组的人数。
SELECT d_id,COUNT(id)
FROM
yuangong GROUP BY d_id;
或者给COUNT(id)取名为sum。
SELECT d_id,COUNT(id) AS sum
FROM yuangong
GROUP BY d_id;
（7）查询每个部门的最高工资。先按部门号进行分组，然后用MAX()函数来计算最大值。
SELECT d_id,MAX(salary)
FROM yuangong
GROUP BY d_id;
（8）用左连接的方式查询bumen表和yuangong表。
使用 LEFT JOINON 来实现左连接。
SELECT bumen.d_id,d_name,function,bumen.address,id,name,birthday,sex,salary,yuangong.address
FROM bumen
LEFT JOIN yuangong ON yuangong.d_id=bumen.d_id;
（9）计算每个部门的总工资。先按部门号进行分组，然后用SUM()函数来求和。
SELECT d_id,SUM(salary)
FROM yuangong
GROUP BY d_id;
（10）查询yuangong表，按照工资从高到低的顺序排列。
SELECT * FROM yuangong
ORDER BY salary DESC;
（11）从bumen表和yuangong表中查询出部门号，然后使用UNION合并查询结果。
SELECT d_id FROM yuangong
UNION
SELECT d_id FROM bumen;
（12）查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。
SELECT name,birthday,address
FROM yuangong
WHERE
address LIKE '北京%';
（二）将在student表和score表上进行查询。Student表和score表的定义如表所示：
Student表的内容
|
字段名 |
字段描述 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
Num |
学号 |
INT(10) |
是 |
否 |
是 |
是 |
否 |
|
name |
姓名 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
|
Sex |
性别 |
VARCHAR(4) |
否 |
否 |
是 |
否 |
否 |
|
birthday |
出生年份 |
DATETIME |
否 |
否 |
否 |
否 |
否 |
|
bumen |
院系 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
|
address |
家庭住址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
score表的内容
|
字段名 |
字段描述 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
|
Id |
编号 |
INT(10) |
是 |
否 |
是 |
是 |
否 |
|
C_name |
课程名 |
VARCHAR(20) |
否 |
否 |
否 |
否 |
否 |
|
Stu_id |
学号 |
INT(10) |
否 |
是 |
是 |
否 |
否 |
|
grade |
成绩 |
INT(10) |
否 |
否 |
否 |
否 |
否 |
1.创建student表
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday INT(4),
bumen VARCHAR(20) NOT NULL,
address VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.创建score表
CREATE TABLE score(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
c_name VARCHAR(20),
stu_id INT(10) NOT NULL,
grade INT(10),
FOREIGN KEY(stu_id)REFERENCES student(num)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.向student表中插入记录
INSERT INTO student
VALUES
(901,'张军','男',1985,'计算机系','北京市海淀区'),
(902,'张超','男',1986,'中文系','北京市昌平区'),
(903,'张美','女',1990,'中文系','湖南省永州市'),
(904,'李五一','男',1990,'英语系','辽宁省阜新市'),
(905,'王芳','女',1991,'英语系','福建省厦门市'),
(906,'王桂','男',1988,'计算机系','湖南省衡阳市');
4.向score表中插入记录
INSERT INTO score
(stu_id,c_name,grade,id)
VALUES
(901,'计算机',98,1),
(901,'英语',80,2),
(902,'计算机',65,3),
(902,'中文',88,4),
(903,'中文',95,5),
(904,'计算机',70,6),
(904,'英语',92,7),
(905,'英语',94,8),
(906,'计算机',90,9),
(906,'英语',85,10);
5.按照下列要求进行表操作
（1）查询student表的所有记录。
方法一：用”*“.
SELECT * FROM student;
方法二：列出所有的列名。
SELECT num,name,sex,birthday,bumen,address
FROM student;
（2）查询student表的第二条到第四条记录。
SELECT *
FROM student
ORDER BY num LIMIT 1,3; 
（3）从student表查询所有学生的学号、姓名和院系的信息。
SELECT num,name,bumen
FROM student; 
（4）查询计算机系和英语系的学生的信息。
方法一：使用IN关键字
SELECT *
FROM student
WHERE
bumen in ('计算机系','英语系');
方法二：使用OR关键字
SELECT *
FROM student
WHERE
bumen ='计算机系' OR
bumen = '英语系';
（5）从student表中查询年龄为18到22岁的学生的信息。
方法一：使用BETWEEN AND 关键字来查询
SELECT *
FROM student
WHERE (2022-birthday)
BETWEEN 18 AND 22;
方式二：使用 AND 关键字和比较运算符。
SELECT *
FROM student
WHERE
(2022-birthday)>=18 AND (2022-birthday)
（6）student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。
SELECT bumen,COUNT(bumen) AS sum_of_bumen
FROM student
GROUP BY bumen;
（7）从score表中查询每个科目的最高分。
SELECT c_name,MAX(grade)
FROM score
GROUP BY c_name;
（8）查询李五一的考试科目（c_name）和考试成绩（grade）。
SELECT c_name,grade
FROM score,student
WHERE
score.stu_id=student.num AND name='李五一';
（9）用连接查询的方式查询所有学生的信息和考试信息。
SELECT *
FROM score,student
WHERE
score.stu_id=student.num ;
（10）计算每个学生的总成绩（需显示学生姓名）。
SELECT name,SUM(grade)
FROM score,student
WHERE
score.stu_id=student.num
GROUP BY name;
（11）计算每个考试科目的平均成绩。
SELECT c_name,avg(grade)
FROM score
GROUP BY c_name;
（12）查询计算机成绩低于95的学生的信息。
SELECT *
FROM student,score
WHERE
student.num=score.stu_id
AND c_name='计算机'
AND grade
（13）查询同时参加计算机和英语考试的学生的信息。
SELECT num,name,sex,birthday,bumen,address
FROM student,score
WHERE
student.num=score.stu_id
AND c_name='英语'
AND stu_id IN
(SELECT stu_id
FROM score
WHERE
c_name='计算机');
（14）将计算机成绩按从高到低进行排序。
SELECT grade
FROM score
WHERE
c_name='计算机' ORDER BY grade DESC; 
（15）从student表和score表中查询出学生的学号，然后合并查询结果。
SELECT stu_id
FROM score
UNION
SELECT num
FROM student;
（16）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。
SELECT name,bumen,c_name,grade
FROM student,score
WHERE
student.num=score.stu_id
AND (name LIKE'张%' OR name LIKE'王%');
（17）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。
SELECT name,(2020-birthday) AS age,bumen,c_name,grade
FROM student, score
WHERE
student.num=score.stu_id
AND address LIKE '湖南%';
四、观察与思考
1、LIKE的通配符有哪些？分别代表什么含义？
|
通配符 |
含义 |
|
% |
有零个或更多个字符组成的任意字符串 |
|
_ |
任意单个字符 |
|
[ ] |
用于指定范围，例如[A ~ F]，表示A ~ F范围内的任何单个字符 |
|
[ ^ ] |
表示指定范围之外的，例如[ ^A ~ F ]，表示A ~ F 范围以外的任何单个字符 |
2、知道学生的出生日期，如何求出其年龄？
答：一般可以使用year函数来计算已知出生日期求年龄的问题
year(getdate())-year( )
3.IS能用“=”来代替吗？如何周全地考虑“空数据”的情况？
答：不可以的，is是用来判断null的，=是用来直接比较值的；= 用在2种情况下：一是判断值是否相等，二是用于赋值用 is null去判断是否真的是空。
4.关键字ALL和DISTINCT有什么不同的含义？关键字ALL是否可以省略不写？
答：ALL表示所有的字段DISTINCT表示去掉重复项，只取一条，All关键字是可以省略，但是在select子句和union子句中不能省略all关键字
5.聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？
答：可用于 select和having中，where是在先筛选后计算，having是先计算后筛选。
6.WHERE子句与HAVING子句有何不同？
答：where子句作用于表或视图中得行,而having子句作用于形成的组；where子句限定查找的行，having子句限定查找的组；若查询语句中同时有where子句和Having子句，执行时先去掉不满足where条件的行，然后分组，分组后再去掉不满足having条件的组。where自居不能直接使用聚合函数，having子句条件中可以包含聚合函数
7.count(*)、count(列名)、count(distinct 列名)三者的区别是什么？通过一个实例说明。
count（*）：是统计所有的个数不管是否重复，是否为空值；
count(列名)：不统计为空值的行；
count（distinct 列名）：统计不重复的行数为多少；
例：
8.内连接与外连接有什么区别？
答：内连接查询是最常见的一种查询，也称为同等查询，就是在表关系的笛卡尔积数据记录中，保留表关系中所有相匹配的数据，而弃舍不匹配的数据
外连接可以查询两个或两个以上的表，外连接查询和内连接查询非常相似，也需要通过指定的字段进行连接，当该字符取值相等时，可以查询出该表的记录。而且，该字段取值不相等的记录也可以才查出来。
9.“=”与IN在什么情况下作用相同？
答：当in的候选值只有一个的时候
五、实验要求
按要求完成，详细记录操作步骤，书写实验报告。所有实验环节均由每位学 生独立完成，认真记录操作过程，严禁抄袭他人实验结果。
六、实验总结
通过本次实验学习了select语法，它是数据库中使用频率最高的SQL语句，实验中涉及到了select的各种查询，例如，分组查询、条件查询、限制查询，还有多表查询的连接。在实验中出现了连接查询后的笛卡尔积现象，后查看在查询语句中缺少括号来维持条件的整体性，修正后笛卡尔积现象得以解决顺利的完成了所有实验。
