关于mysql的练习习题 24道

结果都可输出，可自行优化，本人所写较为繁琐

create database test;
use test;

create table if not exists employee(
	e_no int(11) primary key,-- 员工编号
	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 if not exists dept(
	d_no int(11) primary key,-- 部门编号
	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 dept where d_no in (10,20);
-- 3.查询工资范围在800~2500之间的员工信息
select * from employee where e_salary between 800 and 2500 order by e_salary;
-- 4.查询部门编号为20的部门中的员工信息
select * from employee where dept_no=20;
select * from employee where dept_no in (select d_no from dept where d_no = 20);-- 答案
-- 5.查询每个部门最高工资的员工信息
select max(e_salary) bigsal from employee group by dept_no;-- 写少了

select * from employee where e_salary in(select max(e_salary) bigsal from employee group by dept_no);-- 自己写的更改后的

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 d_no,d_name,d_location from employee a join dept b on a.dept_no = b.d_no and a.e_name = '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 e_name,d_no,d_name,d_location from employee a join dept b on a.dept_no = b.d_no;

select * from dept where d_no in (select distinct dept_no from employee);-- 答案只查了出现的部门信息没有查询每个员工所对应的部门信息

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

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

-- 10.计算不同部门的平均工资
select dept_no,avg(e_salary) from employee group by dept_no;
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;
select * from employee where dept_no in (select dept_no from employee group by dept_no)order by dept_no desc,e_salary desc;-- 复杂了
-- 	13.查询员工姓名以字母‘A’或‘S’开头的员工的信息
select * from employee where e_name like 'A%' or e_name like 'S%';-- 自己用的like字句
select * from employee where e_name regexp '^[as]';-- 答案是正则表达式写的

-- 14.查询到目前为止工龄大于等于18年的员工信息
select *,(year(NOW())-year(hireDate)) as nian from employee where (year(NOW())-year(hireDate)) >18;
-- 15.计算所有女员工（‘F’）的年龄
select year(hireDate) from employee;
select *,year(NOW())-year(hireDate) '工龄' from employee where e_gender='F';
-- 16.使用LIMIT查询从第3条记录开始到第6条记录的结果
select * from employee limit 3,3;
-- 17.查询销售人员（SALSEMAN）的最低工资
select min(e_salary) from employee group by e_job = 'SALSEMAN';
-- 18.查询名字以字母N或者S结尾的记录
select * from employee where e_name like '%N' or e_name like '%S';-- 自己写的是like
select * from employee where e_name regexp '[NS]$';-- 答案用的正则表达式

-- 19.查询在BeiJing工作的员工的姓名和职务
select e_name,e_job,d_no,d_name,d_location from employee a join dept b on a.dept_no=b.d_no and b.d_location = '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 a left join dept b on a.dept_no=b.d_no;
-- 21.使用右连接方式查询employee和dept表
select * from employee a right join dept b on a.dept_no=b.d_no;
-- 22.查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果
select * from employee a join dept b on a.dept_no=b.d_no and a.dept_no = 20 and year(hireDate) between 2001 and 2005 
union
select * from employee a join dept b on a.dept_no=b.d_no and a.dept_no = 30 and year(hireDate) between 2001 and 2005;-- 理解成了吧两个查询合在一起查两种结果了

select * from employee where hireDate between '2001-01-01' and '2005-12-31' union select * from employee where dept_no in (20,30);

select * from employee where hireDate between '2001-01-01' and '2005-12-31' union select * from employee where dept_no in (20,30) order by e_no;-- 加了排序后可以让数据看上去更整齐清晰

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