mysql 数据查询练习题

CREATE TABLE department (
id int AUTO_INCREMENT COMMENT ‘ID’ PRIMARY KEY,
name VARCHAR (50) NOT NULL COMMENT ‘部门名称’
) COMMENT ‘部门表’;
INSERT INTO department (name)
VALUES
(‘技术部’),
(‘市场部’),
(‘财务部’),
(‘人力资源部’),
(‘运营部’),
(‘战略部’),
(‘行政部’),
(‘法务部’),
(‘研发部’),
(‘销售部’);

CREATE TABLE employee (
id INT AUTO_INCREMENT COMMENT ‘ID’ PRIMARY KEY,
name VARCHAR (50) NOT NULL COMMENT ‘姓名’,
age INT COMMENT ‘年龄’,
job VARCHAR (20) COMMENT ‘职位’,
salary INT COMMENT ‘薪资’,
entrydate DATE COMMENT ‘入职时间’,
managerid INT COMMENT ‘直属领导 ID’,
dept_id INT COMMENT ‘部门 ID’
) COMMENT ‘员工表’;
INSERT INTO employee (name, age, job, salary, entrydate, managerid, dept_id)
VALUES
(‘张三’, 25, ‘软件工程师’, 12000, ‘2017-05-01’, 2, 1),
(‘李四’, 22, ‘会计’, 10000, ‘2018-01-15’, 5, 2),
(‘王五’, 30, ‘产品经理’, 15000, ‘2015-04-01’, 1, 3),
(‘赵六’, 28, ‘测试工程师’, 12500, ‘2016-11-05’, 3, 1),
(‘钱七’, 32, ‘运维工程师’, 14000, ‘2014-05-25’, 4, 4),
(‘孙八’, 36, ‘项目经理’, 18000, ‘2012-03-19’, 1, 1),
(‘周九’, 29, ‘UI 设计师’, 13500, ‘2015-08-30’, 6, 3),
(‘吴十’, 27, ‘前端工程师’, 12000, ‘2017-02-12’, 7, 1),
(‘郑十一’, 34, ‘Java 开发工程师’, 16000, ‘2013-07-01’, 2, 1),
(‘王十二’, 31, ‘PHP 开发工程师’, 15000, ‘2014-09-15’, 9, 1),
(‘刘十三’, 26, ‘数据库管理员’, 11500, ‘2018-06-24’, 4, 2),
(‘阮十四’, 23, ‘实习生’, 5000, ‘2020-05-18’, 8, 1),
(‘杨十五’, 40, ‘技术总监’, 25000, ‘2010-01-20’, 1, 1),
(‘朱十六’, 38, ‘产品总监’, 22000, ‘2011-11-05’, 10, 2),
(‘黄十七’, 35, ‘运维总监’, 20000, ‘2013-03-11’, 11, 4),
(‘张十八’, 33, ‘研发经理’, 18000, ‘2015-07-25’, 3, 1),
(‘王十九’, 29, ‘销售专员’, 11500, ‘2017-02-05’, 6, 5),
(‘马二十’, 24, ‘文员’, 9500, ‘2019-05-12’, 13, 2);

select * from employee

内连接

– 1. 查询每一个员工的姓名，以及关联部门的名称 (隐式内连接)
select * from department d, employee e where d.id = e.dept_id;
– 2. 查询每一个员工的姓名，以及关联部门的名称 (显示外连接)
select d. name as 部门,e. name as 姓名 from department as d inner JOIN employee as e on d.id = e.dept_id;

外连接

– 1. 查询 emp 表的所有数据，和对应的部门信息（左外连接）
select * from employee e LEFT JOIN department d on e.dept_id = d.id
– 2. 查询 dept 表的所有数据，和对应的员工信息（右外连接)
select * from employee e RIGHT JOIN department d on e.dept_id = d.id order by d.name ;

自连接

– 查询 员工信息，并查询对应的领导信息, 如果没有领导 ，也需要查询出来信息
select * from employee e 1 left join employee e 2 on e 1. managerid = e 2. id

联合查询

– 查询 员工薪资高与 5000 和 年龄>60 岁的人
SELECT * from employee as e where salary>12000 union select * from employee as e where age>=35

标量子查询

– 查询部门为 技术部的所有员工信息
select * from employee as e where e.dept_id = (select id from department d where d. name =’技术部’)
– 查询吴十 入职之后的 员工信息
select * from employee e where e.entrydate > (select e.entrydate from employee e where e.name=’吴十’)

列子查询

– 1. 查询“销售部”和“市场部”的所有员工信息

– a.查询“销售部”和“市场部”的部门 ID
select id from department where name=’财务部’ OR name=’市场部’;
– b. 根据部门 id，查询员工信息
select * from employee where dept_id in (2,3)
– c. 结合 ab
select * from employee where dept_id in (select id from department where name=’财务部’ OR name=’市场部’)

– 2. 查询比财务部所有人工资都高的工信息
select * from employee where salary> all (select salary from employee where dept_id=(select id from department where name=’财务部’))

– 其他方式
select * from employee where salary>(select salary from employee where dept_id=(select id from department where name=’财务部’) ORDER BY salary desc LIMIT 1)
select * from employee where salary>(select max (salary) from employee where dept_id=(select id from department where name=’财务部’))

– 3. 查询比 研发部 其中任意一人工资高的员工信息
select * from employee where salary< any (select salary from employee where dept_id=(select id from department where name=’技术部’))

行子查询

– 1. 查询与”张无忌” 的薪资 及 直属领导相同的员工信息
– 拆分 a： 查询”张无忌” 的薪资 及 直属领导
select salary, managerid from employee where name=’孙八’;
– 拆分 b： 查询与”张无忌” 的薪资 及 直属领导相同的 员工信息;
select * from employee where (salary, managerid) = (18000,1)
– 结合 c： 结合 a、b
select * from employee where (salary, managerid) = (select salary, managerid from employee where name=’孙八’);

select e 2.* from employee e 1 join employee e 2 on e 1. salary=e 2. salary and e 1. managerid=e 2. managerid and e 1. name=’孙八’

表子查询

– 1. 查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
– 拆分 a：查询 “鹿杖客” 或者 “宋远桥” 的信息
select salary, job from employee where name in (‘钱七’,’王五’);
– 拆分 b: 查询 薪资=19000 and 职位=’研发经理’ || 薪资=18000 and 职位=项目经理 的员工信息
select * from employee where salary=19000 and job=’研发经理’ or salary=18000 AND job=’项目经理’;
select * from employee where (salary, job) in ((19000,’研发经理’), (18000,’项目经理’))
– 组合 c， 组合 a，b
select * from employee where (salary, job) in (select salary, job from employee where name in (‘钱七’,’王五’))

– 2. 查询入职日期是“2006-81-81”之后的员工信息，及其部门信息

select * from employee e left join department d on (e.dept_id=d.id and e.entrydate>’2017-05-01’)
select * from employee e left join department d on e.dept_id=d.id group by e.id having e.entrydate>’2017-05-01’
select * from employee e where e.entrydate>’2017-05-01’

– 拆分 a ： 查询入职日期在 2017-05-01 后面的员工信息
select * from employee e where e.entrydate>’2017-05-01’
– 拆分 b ： 查询 部门 为 1,2 的部门信息
– 查询 这部分员工 的 所属部门信息
select * from department d where d.id in (1,2)
– 合并 C：
SELECT * from (select * from employee e where e.entrydate>’2017-05-01’) e left join department d on e.dept_id = d.id;

CREATE TABLE salgrade (
grade INT,
losal INT,
hisal INT
) COMMENT ‘薪资等级表’;
INSERT INTO salgrade VALUES
(1, 8000, 12000),
(2, 12001, 18000),
(3, 18001, 25000),
(4, 25001, 38000),
(5, 38001, 50000);