mysql数据库实验查询_MySQL数据库表数据的查询操作实验

实验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