mysql常用sql

概述

本文章抄袭 MySQL综合实操练习题
该文适合部分傻逼招聘需要准备sql笔试的训练，以及工作中常用sql查询。

创建数据库与表，并添加数据

-- 创建数据库
CREATE DATABASE IF NOT EXISTS study CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE study;

CREATE TABLE department (
	id INT(2) NOT NULL COMMENT '部门编号',
	`name` VARCHAR (15) COMMENT '部门名称',
	location VARCHAR (20) COMMENT '部门位置' 
);
 
-- 添加主键
ALTER TABLE department ADD PRIMARY KEY (id);
 
-- 添加数据
INSERT INTO department (id,`name`,location)VALUES (10,'财务部','高新四路');
INSERT INTO department (id,`name`,location)VALUES (20,'人事部','科技二路');
INSERT INTO department (id,`name`,location)VALUES (30,'销售部','长安区');
INSERT INTO department (id,`name`,location)VALUES (40,'运输部','雁塔区');


-- -------------------------------------------------------------------------------------------------
CREATE TABLE employee(
	id INT(4) NOT NULL COMMENT '员工编号',
	`name` VARCHAR(10) COMMENT '员工名字',
	job VARCHAR(10) COMMENT '职位',
	superior INT(4) COMMENT '上司',
	hiredate DATE COMMENT '入职时间',
	salary INT(7) COMMENT '基本工资',
	allowance INT(7) COMMENT '补贴',
	department_id INT(2) COMMENT '所属部门编号'
);
 
-- 添加主键
ALTER TABLE employee ADD PRIMARY KEY (id);
 
-- 添加外键约束
ALTER TABLE employee ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id) 
REFERENCES department(id);
 
INSERT INTO employee VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');
INSERT INTO employee VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');
INSERT INTO employee VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');
INSERT INTO employee VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');
INSERT INTO employee VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');
INSERT INTO employee VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');
INSERT INTO employee VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');
INSERT INTO employee VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');
INSERT INTO employee VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');

INSERT INTO employee(id, `name`, job, superior, hiredate, salary, allowance, department_id) 
	VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30'),
    ('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20'),
    ('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30'),
    ('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20'),
    ('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');



-- -------------------------------------------------------------------------------------------------
CREATE TABLE salary_grade(
grade INT (10) COMMENT '工资等级',
min INT (10) COMMENT '最低限额',
max INT (10) COMMENT '最高限额'
);
 
INSERT INTO salary_grade (grade, min, max)VALUES (1, 700, 1200);
INSERT INTO salary_grade (grade, min, max)VALUES (2, 1201, 1400);
INSERT INTO salary_grade (grade, min, max)VALUES (3, 1401, 2000);
INSERT INTO salary_grade (grade, min, max)VALUES (4, 2001, 3000);
INSERT INTO salary_grade (grade, min, max)VALUES (5, 3001, 9999);

单表查询

1.选择部门30中的所有员工

select * from employee where department_id = 30;

2.找出补贴多余基本工资的员工

select * from employee where allowance > salary;

3.找出不需要补贴的员工

select * from employee where allowance is null;

4.找出部门10中所有人事部长和部门20中所有办事员的详细资料

select * from employee where (department_id=10 and job='人事部长') 
or (department_id=20 and job='办事员');

select * from employee where department_id=10 and job='人事部长'
union
select * from employee where department_id=20 and job='办事员';

5.找出拥有补贴的人

select * from employee where allowance is not null;

6.找出姓张的员工

select * from employee where name like '张%';

7.找出不姓张的员工

select * from employee where name not like '张%';

8.查询所有员工按工资降序，入职时间升序

select * from employee order by salary desc, hiredate asc;

多表查询

1.查询员工id, name, job, salary, allowance, total_salary（salary + allowance）, 部门，并按照工资排序。

ifnull(e.allowance, 0)如果值为null则用0替代。

select e.id, e.name, e.job, e.salary, e.allowance, e.salary + ifnull(e.allowance, 0) as total_salary, d.name
from department as d
left join employee as e
on d.id=e.department_id
order by total_salary desc;

2.查出每位员工的直接领导（上司）

select e.id, e.name, e.superior, e1.name
from employee as e
left join employee as e1
on e.superior = e1.id;

3.查询销售部所有员工

select d.name, e.id as employee_id, e.name
from department as d 
left join employee as e
on d.id=e.department_id
where d.name='销售部';

4.假设名字唯一，查询与“李兴”同一部门的所有员工

select e.department_id, e.id, e.name
from employee as e
where e.department_id in (
	select e1.department_id from employee as e1 where e1.name='李兴'
)

5.查询工资高于部门30的所有员工

select e.id, e.name, e.salary
from employee as e
where e.salary > (select max(e1.salary) from employee as e1 where e1.department_id=30);

6.查询员工入职时间早于上司的员工

select e.id, e.name, e.hiredate, e.superior as superior_id, e1.name as superior_name, e1.hiredate as superior_hiredate
from employee as e
left join employee as e1
on e.superior=e1.id
where e.hiredate < e1.hiredate;

分组查询

1.查询部门总人数以及各部门的平均工资

select e.department_id, d.name, count(e.id) as number_of_people, avg(e.salary) as avg_salary
from employee as e
left join department as d
on e.department_id=d.id
group by e.department_id
order by d.id asc;

2.按职位分组查询 当前职位人数，最小工资，最大工资，平均工作

select job, count(job) as number_of_people, min(salary), max(salary), avg(salary)
from employee
group by job

3.查询部门平均工资大于1700的部门

select d.id, d.name, count(e.id) as number_of_people, avg(e.salary)
from department as d
left join employee as e
on d.id=e.department_id
group by d.id
having avg(e.salary) > 1700;

4.查询工资多余等于华仔的所有员工（包括华仔信息）

select *
from employee
where salary>=(select salary from employee where name="华仔")

5.查询工作多余所有员工平均工资的员工

select *
from employee
where salary > (select avg(salary) from employee)

6.查询不同工作等级的人数

select s.grade, count(e.id) as number_of_people
from salary_grade as s
left join employee as e
on e.salary between s.min and s.max
group by s.grade;

7.查询不同部门的平均工资对应的工资等级

select *
from (
	select d.id, d.name, avg(salary) as avg_salary
	from department as d
	left join employee as e
	on d.id=e.department_id
	group by d.id
) as de
left join salary_grade as s
on de.avg_salary between s.min and s.max;