实验4：MySQL数据库表数据的查询操作

一、实验目的

1．掌握SELECT 语句的基本语法格式。

2．掌握SELECT 语句的执行方法。

3．掌握SELECT 语句的 GROUP BY 和 ORDER BY 子句的作用。

二、实验内容

1. 验证性实验：在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。
2. 设计性试验：将在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<=30;

（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)<=22;

（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<95;

（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的各种查询，例如，分组查询、条件查询、限制查询，还有多表查询的连接。在实验中出现了连接查询后的笛卡尔积现象，后查看在查询语句中缺少括号来维持条件的整体性，修正后笛卡尔积现象得以解决顺利的完成了所有实验。