MySQL查询练习

创建表:员工 employees，工资表 salary，部门 departments

create or replace table employees(
empID INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empName VARCHAR(20) NOT NULL , #姓名
sex VARCHAR(4) , #性别
birth date, #出生日期
deptID INT(10) , #部门编码
jobs VARCHAR(20) , #工作岗位
firJob date, #开始工作的日期
hiredate date,#入职日期
politicalStatus VARCHAR(20) , # 政治面貌
leader INT(10) #领导编码
);

create or replace table salary(
sid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empID INT(10) not null,
salary INT(10) not null,#工资
lastedit date #上次调薪日期
);

create or replace table departments(
deptid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
deptname VARCHAR(20) not null,#部门名称
faterdeptid INT(10) # 上级部门编码
);

插入数据

insert into employees values
(0, "赵欣", "男","1991-08-15",1006,"行政","2019-12-01","2009-12-01","群众",0001112215),
(0, "杨一", "女","2001-03-15",1066,"测试","2019-12-01","2009-12-01","团员",0001112211),
(0, "张天", "男","1999-09-08",1066,"开发","2009-06-08","2009-06-07","党员",0001112229),
(0, "刘赢", "女","1991-08-15",1006,"行政","2019-12-01","2009-12-01","群众",0001112228),
(0, "杨可", "男","2001-03-15",1066,"开发","2019-12-01","2009-12-01","团员",0001112227),
(0, "刘欣", "女","1999-09-08",1066,"开发","2009-06-08","2009-06-07","党员",0001112226),
(0, "刘菲", "女","1991-08-15",1006,"行政","2019-12-01","2009-12-01","群众",0001112225),
(0, "杨阳", "男","2001-03-15",1066,"测试","2019-12-01","2009-12-01","团员",0001112224),
(0, "张美", "女","1999-09-08",1066,"开发","2009-06-08","2009-06-07","党员",0001112223),
(0, "刘玲", "女","1991-08-15",1006,"行政","2019-12-01","2009-12-01","群众",0001112222),
(0, "杨紫", "女","2001-03-15",1006,"行政","2019-12-01","2009-12-01","团员",0001112221),
(0, "张涵", "女","1999-09-08",1005,"人事","2009-06-08","2009-06-07","党员",0001112220);

drop table if exists salary;
insert into salary values
(1, 1, 11231,"2021-08-15"),
(2, 2, 11231,"2021-03-15"),
(3, 3, 23152,"2019-09-08"),
(4, 4, 25412,"2018-08-15"),
(5, 5, 45231,"2022-01-15"),
(6, 6, 9000,"2015-09-08"),
(7, 7, 5200,"2016-08-15"),
(8, 8, 12000,"2016-03-15"),
(9, 9, 15000,"2018-09-08"),
(10, 10, 15600,"2019-08-15"),
(11, 11, 18500,"2021-03-15"),
(12, 12, 21000,"2022-01-08");

drop table if exists departments;
insert into departments values
(2122212301, '总裁部', 2122212301),
(2122212346, "行政部", 2122212301),
(2122212666, "技术部", 2222212345),
(2222212345, "人事部", 2122212346);

SQL数据库查询练习

一、单表查询

1、显示所有职工的基本信息。
select * from employees
2、查询所有职工所属部门的部门号，不显示重复的部门号。
select distinct e.deptID from employees e
3、求出所有职工的人数。
select count(1) from employees
4、列出最高工和最低工资。
select max(s.salary) as '最高工资',min(s.salary) as '最低工资' from salary s
5、列出职工的平均工资和总工资。
select AVG(s.salary) as '最高工资',SUM(s.salary) as '最低工资' from salary s
6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

Create table emp2 (select e.empID,e.empName,e.firJob from employees e);
-- 或者
create table hiredate(
empID INT(10) NOT NULL UNIQUE PRIMARY KEY,
empName VARCHAR(20) NOT NULL,#姓名
hiredate date#入职日期
);
insert into hiredate(empID,empName,hiredate) select empID,empName,hiredate from employees;

7、显示所有女职工的年龄

select e.empID, e.empName, (DATEDIFF(CURDATE(),e.birth) DIV 365) as '年龄' from employees e 
where e.sex = '女';
-- 或者
SELECT empID, empName, (YEAR (curdate())- YEAR ( birth)- 1) age FROM employees 
where sex = "女";
-- 或者
SELECT(YEAR (curdate())- YEAR ( birth )- 1+ 
(DATE_FORMAT( birth, '%m%d' )<=(DATE_FORMAT( curdate(), '%m%d' )))) age 
FROM employees 
where sex = "女";

8、列出所有姓刘的职工的职工号、姓名和出生日期。

select e.empID,e.empName,e.birth from employees e where e.empName like '张%'

select empID, empName, birth from employees where left(empName, 1) = "张";

9、列出1990年以前出生的职工的姓名、参加工作日期。

select e.empName,e.firJob from employees e where YEAR(e.firJob)<1990

select empName, firJob from employees where left(birth, 4) < 1990;

10、列出总人数大于4的部门号和总人数。
select e.deptID, count(1) from employees e group by e.deptID having count(1)>4
11、列出所有陈姓和李姓的职工姓名。

select e.empName from employees e where e.empName like'张%' or e.empName like'李%'

select empName from employees where left(empName, 1) = "陈" or left(empName, 1) = "李";

12、列出所有部门号为1005和1006的职工号、姓名。

select e.empID, e.empName from employees e where e.deptID in (1005,1006);

select empID, empName from employees where deptID = 1005 or deptID = 1006;

13、将职工表worker中的职工按出生的先后顺序排序。
select * from employees e order by e.birth asc
14、求出各部门党员的人数。

select e.deptID,count(1) from employees e where e.politicalStatus = '党员' 
GROUP BY e.deptID

二、多表查询

1、列出每名职工的职工号、姓名和部门名。

select e.empID, e.empName, d.deptname from employees e,departments d 
where e.deptID = d.deptid

select e.empID, e.empName, d.deptname from employees e left join departments d 
on e.deptID = d.deptid;

2、列出市场部的所有女职工的姓名和政治面貌。

select e.empName,e.politicalStatus,d.deptname from employees e,departments d 
where e.deptID = d.deptid and e.sex= '女' and d.deptname = '市场部'

select empName, politicalStatus, deptname from employees e left join departments d 
on e.deptID =d.deptid where e.sex= '女' and d.deptname = "市场部";

3、显示所有职工的姓名、部门名和工资数。

select e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d 
on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID

4、显示所有职工的职工号、姓名、部门名和工资，并按部门名顺序排列。

select e.empid, e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d 
on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID ORDER BY e.deptID

5、显示各部门名和该部门的所有职工平均工资。

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e 
on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname

select deptname, round(avg(salary), 1) from employees e left join departments d 
on e.deptID =d.deptid left join salary s on e.empID = s.empID group by e.deptID;

6、显示所有平均工资高于12000的部门名和对应的平均工资。

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e 
on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname 
having AVG(s.salary)>12000

7、查询刘欣所在的部门。

select d.deptname from employees e, departments d where e.deptID = d.deptid and 
e.empName = '刘欣'

select d.deptname from departments d where d.deptid =(select deptid from employees e 
where e.empName ='刘欣')

select deptname from employees e left join departments d on e.deptID =d.deptid 
where empName = "刘欣";

8、查询部门名为技术处的职工的情况。

select * from employees e,departments d where e.deptID = d.deptid and d.deptname ='技术处'

select * from employees e where e.deptid =(select deptid from departments d 
where d.deptname ='技术处')

select e.*, d.deptname from employees e left join departments d on e.deptID =d.deptid 
where deptname = "财务处";

9、列出工资在10000－20000之间的所有职工姓名。

select e.empName,s.salary from salary s,employees e where s.salary between 10000 
and 20000 and s.empID = e.empID

select empName, s.salary from employees e left join salary s on e.empID = s.empID 
where s.salary between 10000 and 20000;

10、显示工资最高的前3名职工的职工号和姓名。

select e.empID, e.empName from salary s, employees e where s.empID = e.empID 
ORDER BY s.salary desc LIMIT 3

select e.empID,  empName, s.salary from employees e left join salary s 
on e.empID = s.empID  
order by salary desc limit 3;