MySQL 练习之查询

昨天又重温了MySQL的相关查询，写了一些单行查询、分组查询、排序查询的作业，以便日后温习其用法。

++++++++++++++++++++++++++++++++

#作业1：单行查询

#1. 显示系统时间(注：日期+时间)
SELECT NOW();
#2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,first_name,last_name,salary,salary*1.2 AS "new salary" FROM employees; 

#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT first_name 名,last_name 姓,LENGTH(first_name) 长度, SUBSTR(first_name,1,1) 首字母 FROM employees ORDER BY 首字母;

#4. 做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees; 

#5. 使用case-when，按照下面的条件：
job                  grade
AD_PRES            A
ST_MAN             B
IT_PROG             C
SA_REP              D
ST_CLERK           E
产生下面的结果
Last_name    Job_id    Grade
king    AD_PRES    A

SELECT * FROM employees;
SELECT last_name AS Last_name,job_id AS Job_id,
CASE  
WHEN job_id='AD_PRES' THEN 'A' 
WHEN job_id='ST_MAN ' THEN 'B'
WHEN job_id='IT_PROG'  THEN 'C'
WHEN job_id='SA_REP' THEN 'D'
WHEN job_id='ST_CLERK' THEN 'E'
END AS Grade
FROM employees WHERE job_id='AD_PRES';

或
SELECT last_name AS Last_name,job_id AS Job_id,
CASE  job_id
WHEN 'AD_PRES' THEN 'A' 
WHEN 'ST_MA ' THEN 'B'
WHEN 'IT_PROG'  THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees WHERE job_id='AD_PRES';

#作业2：分组查询

#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 综合 FROM employees GROUP BY job_id ORDER BY job_id;

#2.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) 最高工资,MIN(salary) 最低工资, (MAX(salary)- MIN(salary)) DIFFERENCE FROM employees;

#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id 管理者编号,MIN(salary) 最低工资 FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING 最低工资>=6000;

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id 部门编号,COUNT(*) 员工数量,AVG(salary) 平均工资 FROM employees GROUP BY department_id ORDER BY 平均工资 DESC;

#5.选择具有各个job_id的员工人数
SELECT job_id 工种号, COUNT(*) 员工人数 FROM employees GROUP BY job_id;

#作业3：分组函数

#1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 总和 FROM employees;
 
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
SELECT MAX(hiredate) 最大入职时间,MIN(hiredate) 最小入职时间, TO_DAYS(DATE_FORMAT(MAX(hiredate),'%Y%m%d')) - TO_DAYS(DATE_FORMAT(MIN(hiredate),'%Y%m%d')) AS DIFFERENCE  FROM employees;
或
SELECT MAX(hiredate) 最大入职时间,MIN(hiredate) 最小入职时间,DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFERENCE FROM employees;

#3.查询部门编号为90的员工个数
SELECT COUNT(*) 员工个数 FROM employees WHERE department_id=90;

#作业4：排序查询

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name 姓氏, department_id 部门号,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC;

#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name 姓氏,salary 工资 FROM employees WHERE NOT salary BETWEEN 8000 AND 17000 ORDER BY salary DESC;

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;
SELECT *,LENGTH(email) email_len FROM employees WHERE email LIKE '%e%' ORDER BY email_len DESC,department_id ASC;