数据库系统原理及MySQL应用教程实验四MySQL数据库表数据的查询操作

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

一、实验目的

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

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

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

二、实验内容

1. 验证性实验：在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。
2. 设计性试验：将在student表和score表上进行查询。

三、实验步骤与实验结果

（一）验证性实验

在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。Bumen表和yuangong表的定义如表所示。

表bumen表的定义

表yuangong表的定义

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表的内容

score表的内容

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的通配符有哪些？分别代表什么含义？

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的各种查询，例如，分组查询、条件查询、限制查询，还有多表查询的连接。在实验中出现了连接查询后的笛卡尔积现象，后查看在查询语句中缺少括号来维持条件的整体性，修正后笛卡尔积现象得以解决顺利的完成了所有实验。文章来源地址https://www.toymoban.com/news/detail-753695.html

到了这里，关于数据库系统原理及MySQL应用教程实验四MySQL数据库表数据的查询操作的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！