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;