MYSQL学习之路——数据处理练习题（一）

#时间函数

SELECT now() AS 时间;

#查询工资>12000的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	salary > 12000;

#查询所有职员的全名用并且用“_”隔开

SELECT
	CONCAT( first_name, '_', last_name ) AS 全名 
FROM
	employees;

#查询部分编号不等于90号的员工名和部门编号

SELECT
	first_name,
	department_id 
FROM
	employees 
WHERE
	department_id != 90;

#查询工资(不)在10000-20000之间的员工名、工资

SELECT
	first_name,
	salary 
FROM
	employees 
WHERE
	NOT ( salary >= 10000 AND salary <= 20000 );

#查询员工的部门编号是60、90的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	department_id = 60 
	OR department_id = 90;

#查询员工名中包含字符a的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	first_name LIKE '%a';

#第一个字符是S的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	first_name LIKE 'S%';

#查询员工名中第三个字符为e，第五个字符为a的员工名和工资

SELECT
	first_name,
	salary 
FROM
	employees 
WHERE
	first_name LIKE '__e_a%';

#查询员工中第二个字符为_的员工名

SELECT
	first_name 
FROM
	employees 
WHERE
	first_name LIKE '_\_%';
## \转义符，_不为通配符

#查询员工编号在100到200之间的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	employee_id >= 100 
	AND employee_id <= 200;

SELECT
	* 
FROM
	employees 
WHERE
	employee_id BETWEEN 100 
	AND 200;

#查询员工的部分编号是60，90的员工信息：

SELECT
	* 
FROM
	employees 
WHERE
	department_id IN ( 60, 90 );

#查询(没)有奖金的员工名和奖金率：

SELECT
	first_name,
	commission_pct 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;

#查询员工信息，要求工资从高到低排序 desc降序 ，asc升序（若不写则默认）：

SELECT
	* 
FROM
	employees 
ORDER BY
	salary DESC;

#查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序

SELECT
	* 
FROM
	employees 
WHERE
	department_id >= 90 
ORDER BY
	hire_date ASC;

#查询salary 显示结果为out put

SELECT
	salary AS 'out put' 
FROM
	employees;

#去重,查询员工表中涉及到的所有的部门编号

SELECT DISTINCT
	department_id 
FROM
	employees;

#查询employees中值的类型

DESCRIBE employees;

#查询工资在2500-3500间的员工姓名

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary BETWEEN 2500 
	AND 3500;

#查找manager_id=100、101、201的员工的薪水、employee_id和last_name

SELECT
	salary,
	last_name,
	employee_id,
	manager_id 
FROM
	employees 
WHERE
	manager_id IN ( 100, 101, 201 );

#查找所有S开头的fist_name的员工

SELECT
	first_name 
FROM
	employees 
WHERE
	first_name LIKE 'S%';

#查找所有带有MAN的员工的id、姓名、job_id，并且salary>=6000

SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary >= 6000 
	AND job_id LIKE '%MAN';

#查找所有员工的姓名以及id，计算其年薪，并按升序排名

SELECT
	employee_id,
	last_name,
	salary * 12 AS 年薪 
FROM
	employees 
ORDER BY
	年薪;

#查询每个工种的最高工资

SELECT
	max( salary ),
	job_id 
FROM
	employees 
GROUP BY

#查询邮箱中包含A字符的，每个部门的平均工资

SELECT
	avg( salary ),
	department_id,
	email 
FROM
	employees 
WHERE
	email LIKE '%A%' 
GROUP BY
	department_id;

#查询哪个部门的员工个数>2

SELECT
	count( * ),
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	count( * ) > 2;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资

SELECT
	max( salary ),
	job_id 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	max( salary ) > 12000;

    #按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有几个
 

SELECT
	count( * ),
	length( first_name ) 
FROM
	employees 
GROUP BY
	length( first_name ) 
HAVING
	count( * ) > 5;

 #查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资是多少

SELECT
	manager_id,
	min( salary ) 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id 
HAVING
	min( salary ) > 5000;

#按多个字段分组：查询每个部门每个工种的员工的平均工资

SELECT
	avg( salary ),
	department_id,
	job_id 
FROM
	employees 
GROUP BY
	department_id,
	job_id;

#查询有奖金的员工和入职日期

SELECT
	first_name,
	commission_pct,
	DATE_FORMAT( hire_date, '%m月/%d日%y年' ) 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;

#查询员工名，奖金率，有奖金的显示有奖金，没有的显示没有奖金

SELECT
	first_name,
	commission_pct,
IF
	( commission_pct IS NULL, '无', '有' ) AS 有无奖金 
FROM
	employees;

#查询员工的工资，要求
-- 部门号=30.显示的工资为1.1倍
-- 部门号=40，显示的工资为1.2倍
-- 部门号=50，显示的工资为1.3倍
-- 其他部门显示的工资为原工资

SELECT
	first_name AS 姓名,
	department_id AS 部门编号,
	salary AS 原始工资,
CASE
	department_id 
	WHEN 30 THEN
	salary * 1.1 
	WHEN 40 THEN
	salary * 1.2 
	WHEN 50 THEN
	salary * 1.3 ELSE salary 
	END AS 新工资 
FROM
	employees;

#查询工资等级

-- 如果工资>20000，显示A级别
-- 如果工资>15000，显示B级别
-- 如果工资>10000，显示C级别
-- 否则显示D级别

SELECT
	first_name AS 姓名,
	salary AS 工资,
CASE
	
	WHEN salary > 20000 THEN
	'A级别' 
	WHEN salary > 15000 THEN
	'B级别' 
	WHEN salary > 10000 THEN
	'C级别' ELSE 'D级别' 
	END AS 工资等级 
FROM
	employees;