MySQL数据操作管理

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

一、实验目的

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

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

二、验证性实验

题目要求：

在公司的部门员工管理数据库的department表和employee表上进行信息查询。

department表和employee表的定义如表所示。

表department表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
d_id	部门号	INT(4)	是	否	是	是	否
d_name	部门名称	VARCHAR(20)	否	否	是	是	否
function	部门职能	VARCHAR(20)	否	否	否	否	否
address	工作地点	VARCHAR(30)	否	否	否	否	否

表employee表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
id	员工号	INT(4)	是	否	是	是	否
name	姓名	VARCHAR(20)	否	否	是	否	否
gender	性别	VARCHAR(4)	否	否	是	否	否
birthday	年龄	INT(4)	否	否	否	否	否
d_id	部门号	VARCHAR(20)	否	否	是	否	否
salary	工资	Float	否	否	否	否	否
address	家庭住址	VARCHAR(50)	否	否	否	否	否

创建表的SQL语句如下：

（1）

create table department(

  d_id int(4) not null primary key unique,

  d_name varchar(20) not null unique,

  `function` varchar(20),

  address varchar(30)

) engine = myisam character set = utf8mb4 collate = utf8mb4_general_ci;

（2）

create table employee (

  id int(4) not null primary key unique not null,

  name varchar(20) not null,

  gender varchar(4) not null,

  birthday int(4),

  d_id varchar(20) not null,

  salary float,

   address varchar(50)

) engine = myisam character set = utf8mb4 collate = utf8mb4_general_ci;

department表的练习数据：

1001,'人事部','人事管理','北京'

1002,'科研部','研发产品','北京'

1003,'生产部','产品生产','天津'

1004,'销售部','产品销售','上海'

employee表的练习数据：

8001,'韩鹏','男',25,1002,4000,'北京市海淀区'

8002,'张峰','男',26,1001,2500,'北京市昌平区'

8003,'欧阳','男',20,1003,1500,'湖南省永州市'

8004,'王武','男',30,1001,3500,'北京市顺义区'

8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'

8006,'呼延','男',28,1003,1800,'天津市南开区'

insert into department values(1001,'人事部','人事管理','北京');

insert into department values(1002,'科研部','研发产品','北京');

insert into department values(1003,'生产部','产品生产','天津');

insert into department values(1004,'销售部','产品销售','上海');

insert into employee values(8001,'韩鹏','男',25,1002,4000,'北京市海淀区');

insert into employee values(8002,'张峰','男',26,1001,2500,'北京市昌平区');

insert into employee values(8003,'欧阳','男',20,1003,1500,'湖南省永州市');

insert into employee values(8004,'王武','男',30,1001,3500,'北京市顺义区');

insert into employee values(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区');

insert into employee values(8006,'呼延','男',28,1003,1800,'天津市南开区');

然后在department表和employee表查询记录。查询的要求如下：

1. 查询employee表的所有记录。

select * from employee;

1. 查询employee表的第四条到第五条记录。

select id,name,gender,birthday,d_id,salary,address from employee limit 3,2;

1. 查询部门号（d_id）、部门名称（d_name）和部门职能（function）。

select d_id,d_name,`function` from department;

1. 查询人事部和科研部的员工的信息。

select *from employee

     where d_id in(

    select d_id from department

    where d_name='人事部' or d_name='科研部');

1. 查询年龄在25到30之间的员工的信息。

select * from employee where birthday between 25 and 30;

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

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

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

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

（8）用左连接的方式查询department表和employee表。

select department.d_id,d_name,‘function’,department.address,id,name,birthday,gender,salary,employee.address

from department left join employee on employee.d_id=employee.d_id;

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

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

（10）查询employee表，按照工资从高到低的顺序排列。

select * from employee order by salary desc;

1. 从department表和employee表中查询出部门号，然后使用UNION合并查询结果。

Select d_id from employee union select d_id from department;

1. 查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。

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

三、设计性试验

将在student表和score表上进行查询。Student表和score表的定义如表所示：

Student表的内容

score表的内容

表创建成功后，查看两个表的结构。

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;

1. 查询student表的第二条到第四条记录。

select num,name,sex,birthday,bumen,address from student limit 1,3;

1. 从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 year(now())-birthday between 18 and 22;

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

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

1. student表中查询每个院系有多少人，为统计的人数列取别名sum_of_department。

select bumen,count(num) as sum_of_bumen from student group by bumen;

1. 从score表中查询每个科目的最高分。

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

1. 查询李五一的考试科目（c_name）和考试成绩（grade）。

select c_name,grade from score

where stu_id in(

select num from student

where name='李五一');

                  

1. 用连接查询的方式查询所有学生的信息和考试信息。

select student.num,name,sex,birthday,bumen,address,score.c_name,score.grade

from student left join score on student.num=score.stu_id;

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

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

1. 计算每个考试科目的平均成绩。

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

1. 查询计算机成绩低于95的学生的信息。

select * from student

where num in(

select stu_id from score

where c_name='计算机' and grade<95);

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

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='英语');

1. 将计算机成绩按从高到低进行排序。

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

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

select num from student union select stu_id from score;

1. 查询姓张或者姓王的同学的姓名、院系、考试科目和成绩

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

select name,birthday,bumen,c_name,grade from student,score

where (address like '湖南%')

and student.num=score.stu_id;