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

实验3.MySQL数据库表数据的查询操作实验(1)

一、实验目的

1. 掌握SELECT 语句的基本语法格式。
2. 掌握SELECT 语句的执行方法。
3. 掌握SELECT 语句的 GROUP BY 和 ORDER BY 子句的作用。

二、验证性实验

create database bumeninfo;

use bumeninfo;

create table bumen(
d_id int(4)not null unique primary key,
d_name varchar(20)not null unique,
functions varchar(20),
address varchar(20)
);

desc bumen;

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),
constraint yuangong_fk foreign key(d_id)
references bumen(d_id)
);

desc yuangong;

bumen表的练习数据：

1001,‘人事部’,‘人事管理’,‘北京’
1002,‘科研部’,‘研发产品’,‘北京’
1003,‘生产部’,‘产品生产’,‘天津’
1004,‘销售部’,‘产品销售’,‘上海’
yuangong表的练习数据：
8001,‘韩鹏’,‘男’,25,1002,4000,‘北京市海淀区’
8002,‘张峰’,‘男’,26,1001,2500,‘北京市昌平区’
8003,‘欧阳’,‘男’,20,1003,1500,‘湖南省永州市’
8004,‘王武’,‘男’,30,1001,3500,‘北京市顺义区’
8005,‘欧阳宝贝’,‘女’,21,1002,3000,‘北京市昌平区’
8006,‘呼延’,‘男’,28,1003,1800,‘天津市南开区’
然后在bumen表和yuangong表查询记录。

insert into bumen values(1001,'人事部','人事管理','北京');
insert into bumen values(1002,'科研部','研发产品','北京');
insert into bumen values(1003,'生产部','产品生产','天津');
insert into bumen values(1004,'销售部','产品销售','上海');

insert into yuangong values(8001,'韩鹏','男',25,1002,4000,'北京市海淀区');
insert into yuangong values(8002,'张峰','男',26,1001,2500,'北京市昌平区');
insert into yuangong values(8003,'欧阳','男',20,1003,1500,'湖南省永州市');
insert into yuangong values(8004,'王武','男',30,1001,3500,'北京市顺义区');
insert into yuangong values(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区');
insert into yuangong values(8006,'呼延','man',28,1003,1800,'天津市南开区');

（1）查询yuangong表的所有记录。SQL代码：

select * from yuangong;

或者列出yuangong表的所有字段名称。SQL代码：

select id,name,sex,birthday,d_id,salary,address from yuangong;

（2）查询yuangong表的第四条到第五条记录。

select id,name,sex,birthday,d_id,salary,address from yuangong limit 3,2;

（3）从bumen表查询部门号（d_id）、部门名称（d_name）和部门职能（functions）。SQL代码：

select d_id,d_name,functions from bumen;

（4）从yuangong表中查询人事部和科研部的员工的信息。先从bumen表查询出人事部和科研部的部门号。然后到yuangong表中去查询员工的信息。SQL代码：

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之间的员工的信息。可以通过两种方式来查询。
第一种方式的SQL代码：

select * from yuangong where birthday between 25 and 30;

第二种方式的SQL代码：

select * from yuangong where birthday>=25 and birthday<=30;

（6）查询每个部门有多少员工。先按部门号进行分组，然后用COUNT()函数来计算每组的人数。SQL代码：

select d_id,count(id) from yuangong group by d_id;

或者给COUNT(id)取名为sum。其SQL代码为：

select d_id,count(id) as sum from yuangong group by d_id;

（7）查询每个部门的最高工资。先按部门号进行分组，然后用MAX()函数来计算最大值。SQL代码：

select d_id,max(salary) from yuangong group by d_id;

（8）用左连接的方式查询bumen表和yuangong表。
使用 LEFT JOIN ON 来实现左连接。SQL代码：

select bumen.d_id,d_name,functions,bumen.address,id,name,birthday,sex,salary,yuangong.address
from bumen left join yuangong on yuangong.d_id=bumen.d_id;

（9）计算每个部门的总工资。先按部门号进行分组，然后用SUM()函数来求和。SQL代码：

select d_id,sum(salary) from yuangong group by d_id;

（10）查询yuangong表，按照工资从高到低的顺序排列。SQL代码：

select * from yuangong order by salary desc;

（11）从bumen表和yuangong表中查询出部门号，然后使用UNION合并查询结果。SQL代码：

select d_id from yuangong union select d_id from bumen;

（12）查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。SQL代码：

select name,birthday,address from yuangong where address like '北京%';

二、设计性试验

表创建成功后，查看两个表的结构。
Student练习数据如下：
901,‘张军’,‘男’,1985,‘计算机系’,‘北京市海淀区’
902,‘张超’,‘男’,1986,‘中文系’,‘北京市昌平区’
903,‘张美’,‘女’,1990,‘中文系’,‘湖南省永州市’
904,‘李五一’,‘男’,1990,‘英语系’,‘辽宁省阜新市’
905,‘王芳’,‘女’,1991,‘英语系’,‘福建省厦门市’
906,‘王桂’,‘男’,1988,‘计算机系’,‘湖南省衡阳市’
score表练习数据如下：
901,‘计算机’,98
901,‘英语’,80
902,‘计算机’,65
902,‘中文’,88
903,‘中文’,95
904,‘计算机’,70
904,‘英语’,92
905,‘英语’,94
906,‘计算机’,90
906,‘英语’,85

create database schoolinfo;
use schoolinfo;

create table student(
num int(10) primary key not null unique,
name varchar(20) not null,
sex varchar(4) not null,
birthday year,
bumen varchar(20) not null,
address varchar(50)
);
desc student;

create table score(
id int(10) primary key not null unique,
c_name varchar(20),
stu_id int(10) not null,
grade int(10),
constraint score_fk foreign key(stu_id)
references student(num)
);
desc score;

insert into student(num,name,sex,birthday,bumen,address)
values
(901,'张军','男','1985','计算机系','北京市海淀区'),
(902,'张超','男','1986','中文系','北京市昌平区'),
(903,'张美','女','1990','中文系','湖南省永州市'),
(904,'李五一','男','1990','英语系','辽宁省阜新市'),
(905,'王芳','女','1991','英语系','福建省厦门市'),
(906,'王桂','男','1988','计算机系','湖南省衡阳市');

select * from student;

insert into score(id,stu_id,c_name,grade)
values
(1,901,'计算机',98),
(2,901,'英语',80),
(3,902,'计算机',65),
(4,902,'中文',88),
(5,903,'中文',95),
(6,904,'计算机',70),
(7,904,'英语',92),
(8,905,'英语',94),
(9,906,'计算机',90),
(10,906,'英语',85);

select * from score;

然后按照下列要求进行表操作：
（1）查询student表的所有记录。
方法一：用”*“。

select * from student;

方法二：列出所有的列名。

select num,name,sex,birthday,bumen,address from student;

（2）查询student表的第二条到第四条记录。

select num,name,sex,birthday,bumen,address from student 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表中查询年龄为28到32岁的学生的信息。
方法一：使用BETWEEN AND 关键字来查询

select * from student where year(now())-birthday between 28 and 32;

方式二：使用 AND 关键字和比较运算符。

select * from student where year(now())-birthday >=28 and year(now())-birthday <=32;

（6）student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。

select bumen,count(num) 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
where stu_id in(
select num from student
where name='李五一');

（9）用连接查询的方式查询所有学生的信息和考试信息。

select student.num,name,sex,birthday,bumen,address,score.c_name,score.grade
from student left join score on student.num=score.stu_id;

（10）计算每个学生的总成绩（需显示学生姓名）。

select name,sum(grade) from student s left join score sc on s.num=sc.stu_id group by num;

（12）计算每个考试科目的平均成绩。

select c_name,avg(grade) from score group by c_name;

（13）查询计算机成绩低于95的学生的信息。

select * from student
where num in(
select stu_id from score
where c_name='计算机' and grade<95);

（14）查询同时参加计算机和英语考试的学生的信息。

select * from student
where num=any(
select stu_id from score
where stu_id in(
select stu_id from score
where c_name='计算机') and c_name='英语');

（15）将计算机成绩按从高到低进行排序。

select c_name,grade from score where c_name='计算机' order by grade desc;

（16）从student表和score表中查询出学生的学号，然后合并查询结果。

select num from student union select stu_id from score;

（17）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。

select student.name,bumen,c_name,grade from student,score
where (name like '张%' or name like '王%')
and student.num=score.stu_id;

（18）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

select name,birthday,bumen,c_name,grade from student,score
where (address like '湖南%')
and student.num=score.stu_id;

三、 观察与思考

1、LIKE的通配符有哪些？分别代表什么含义？

%（百分号）：代表任意长度（长度可以为0）的字符串。
_（下横线）：代表任意单个字符。

2、知道学生的出生日期，如何求出其年龄？

float(datadiff(year,birthday,getdata())/365.25)
birthday=’xxxx-xx-xx’
getdata()=’xxxx-xx-xx’

3、IS能用“=”来代替吗？如何周全地考虑“空数据”的情况？

不能。该属性应该有一个值，但不知道具体值；该属性不应该有值；由于某种原因不便于填写。

4、关键字ALL和DISTINCT有什么不同的含义？关键字ALL是否可以省略不写？

all：返回所有的记录；distinct：去掉查询返回的记录中重复的记录。
all在select子句里省略，对结果无影响，在union子句里省略则表示剔除有重复的行，反之则保留所有记录行。

5、聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？

聚集函数只能用于select子句，group by中的having子句，where子句是一个条件语句，在where后面跟的是条件。

6、WHERE子句与HAVING子句有何不同？

作用对象不同：where子句作用于基本表或视图，从中选择满足条件的元祖，having子句作用于组，从中选择满足条件的组。

7、 count(*)、count(列名)、count(distinct 列名)三者的区别是什么？通过一个实例说明。

count(*):明确的返回数据表中的元组数据个数，不会忽略值为null的字段
count(列名):返回数据表中的某列数据个数，不统计值为null的字段
count(disinct 列名):返回数据表中某列不重复的数据个数，不统计值为null的字段
select count(distinct d_id) from yuangong;
select count(d_id) from yuangong;
select count(*) from yuangong;

8、 内连接与外连接有什么区别？

内连接：比较运算符根据每个表共有的列的值匹配两个表中的行（使用像 = 或 <> 之类的比较运算符）
外连接：当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值（使用left/right/full join或left/right/full outer join关键字）

9、“=”与IN在什么情况下作用相同？

当in的候选值只有一个时。

感谢无意间遇到有同样实验报告的小伙伴：

https://blog.csdn.net/qq_27991659/article/details/51473829#commentBox