MySQL数据库查询练习

实验要求

创建数据表：employee表

创建部门表 dept

输入记录：
employee表

部门表中的记录

查询练习：

1. 查询所有记录的e_no、e_name和e_salary字段值
2. 查询dept_no等于10和20的所有记录
3. 查询工资范围在800~2500之间的员工信息
4. 查询部门编号为20的部门中的员工信息
5. 查询每个部门最高工资的员工信息
6. 查询员工BLAKE所在部门和部门所在地
7. 查询所有员工的部门和部门信息
8. 计算每个部门各有多少名员工
9. 计算不同类型职工的总工资数
10. 计算不同部门的平均工资
11. 查询工资低于1500的员工信息
12. 将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
13. 查询员工姓名以字母‘A’或‘S’开头的员工的信息
14. 查询到目前为止工龄大于等于18年的员工信息
15. 计算所有女员工（‘F’）的年龄
16. 使用LIMIT查询从第3条记录开始到第6条记录的结果
17. 查询销售人员（SALSEMAN）的最低工资
18. 查询名字以字母N或者S结尾的记录
19. 查询在BeiJing工作的员工的姓名和职务
20. 使用左连接方式查询employee和dept表
21. 查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并
    两个查询结果
22. 使用LIKE查询员工姓名中包含字母a的记录
23. 使用REGEXP查询员工姓名中包含T、C或者M这3个字母中任意1个的记录

---

实验过程

建表：

create database myemployee;
use myemployee;
create table employee(
	e_no int(11) primary key unique,
	e_name varchar(50) not null,
	e_gender char(2),
	dept_no int(11) not null,
	e_job varchar(50) not null,
	e_salary int(11) not null,
	hireDate date not null
);

create table dept(
	d_no int(11) primary key unique auto_increment,
	d_name varchar(50) not null,
	d_location varchar(100)
);

alter table employee add constraint FK_emp_dept foreign key(dept_no) references dept(d_no);

插入数据：

insert into employee values
(1001,'SMITH','m',20,'CLEAR',800,'2005-11-12'),
(1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12'),
(1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12'),
(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18'),
(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12'),
(1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15'),
(1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12'),
(1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12'),
(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01'),
(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12'),
(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05'),
(1012,'JAMES','f',30,'CLERK',950,'2008-06-15');

insert into dept values
(10,'ACCOUNTING','ShangHai'),
(20,'RESEARCH','BeiJing'),
(30,'SALES','ShenZhen'),
(40,'OPERATIONS','FuJian');

查询练习：

-- 1. 查询所有记录的e_no、e_name和e_salary字段值
select e_no,e_name,e_salary from employee;

-- 2. 查询dept_no等于10和20的所有记录
select * from employee where dept_no in (10,20);

-- 3. 查询工资范围在800~2500之间的员工信息
select * from employee where e_salary between 800 and 2500;

-- 4. 查询部门编号为20的部门中的员工信息
select * from employee where dept_no in (select d_no from dept where d_no = 20);

-- 5. 查询每个部门最高工资的员工信息
select * from (select max(e_salary) max_salary,dept_no from employee group by dept_no) a, employee e where a.dept_no = e.dept_no and a.max_salary = e.e_salary;

-- 6. 查询员工BLAKE所在部门和部门所在地
select dept_no from employee where e_name = 'BLAKE';
select * from dept where d_no in (select dept_no from employee where e_name = 'BLAKE');

-- 7. 查询所有员工的部门和部门信息
select * from dept where d_no in (select distinct dept_no from employee);

-- 8. 计算每个部门各有多少名员工
select count(e_name) '总人数',dept_no from employee group by dept_no;

-- 9. 计算不同类型职工的总工资数
select sum(e_salary) '总工资数',e_job from employee group by e_job;

-- 10. 计算不同部门的平均工资
select avg(e_salary) '平均工资',e_job from employee group by e_job;

-- 11. 查询工资低于1500的员工信息
select * from employee where e_salary < 1500;

-- 12. 将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
select * from employee order by dept_no desc,e_salary desc;

-- 13. 查询员工姓名以字母‘A’或‘S’开头的员工的信息
select * from employee where e_name regexp '^[as]';

-- 14. 查询到目前为止工龄大于等于18年的员工信息
select * from employee where NOW() - hireDate >= 18;

-- 15. 计算所有女员工（‘F’）的工龄
select sum(a.workTime) '总工龄' from (select YEAR(NOW()) - YEAR(hireDate) 'workTime' from employee where e_gender = 'F') a;

-- 16. 使用LIMIT查询从第3条记录开始到第6条记录的结果
select * from employee limit 3, 3;

-- 17. 查询销售人员（SALSEMAN）的最低工资
select min(e_salary) '最低工资' from employee where e_job = 'SALESMAN';

-- 18. 查询名字以字母N或者S结尾的记录
select * from employee where e_name regexp '[ns]$';

-- 19. 查询在BeiJing工作的员工的姓名和职务
select e_name,e_job from employee where dept_no = (select d_no from dept where d_location = 'BeiJing');

-- 20. 使用左连接方式查询employee和dept表
select * from employee e left join dept d on e.dept_no = d.d_no;

-- 21. 查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果
select * from employee where hireDate between '2001-01-01' and '2005-12-31' union select * from employee where dept_no in (20,30);

-- 22. 使用LIKE查询员工姓名中包含字母a的记录
select * from employee where e_name like '%a%';

-- 23. 使用REGEXP查询员工姓名中包含T、C或者M这3个字母中任意1个的记录
select * from employee where e_name regexp '[tcm]';