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;