实验3.MySQL数据库表数据的查询操作实验(1)
一、实验目的
- 掌握SELECT 语句的基本语法格式。
- 掌握SELECT 语句的执行方法。
- 掌握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