MYSQL（二）

1、升降序

#ORDER BY 按照那一列排序(排序列表)  DESC降序
SELECT * FROM employees ORDER BY salary DESC; 

#ASC升序  (默认升序)
SELECT * FROM employees ORDER BY salary ASC;

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

#按照表达式查询
SELECT * ,salary * 12 * (1 + IFNULL(commission_pct,0)) AS '年薪' FROM employees ORDER BY salary * 12 * (1 + IFNULL(commission_pct,0)) DESC;

#按照名字的长度(AS可省略)
SELECT LENGTH(last_name) AS 字节长度 ,last_name, salary FROM employees ORDER BY LENGTH(last_name) DESC;

#可以放多个字段
SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

SELECT last_name, salary FROM employees WHERE NOT (salary BETWEEN 8000 AND 17000) ORDER BY salary DESC; 

2、函数查询

# SELECT 函数名（实参列表） 【from 表】  
#1、单行函数 CONCAT(str1,str2,...) LENGTH(str) IFNULL(expr1,expr2)
SELECT LENGTH()

#2、分组函数  4
SELECT LENGTH('join');

#一个汉字三个字符  10
SELECT LENGTH('张三haha');

#拼接两个字符串
SELECT CONCAT(last_name,'-',first_name) FROM employees;

SELECT UPPER('join');

SELECT LOWER('JOIN');

3、分组查询

直接原表就可以筛选，直接用where 需要查出来以后才筛选 用having

#分组查询
SELECT COUNT(*) 个数 FROM employees WHERE department_id = 90;

#SELECT 函数 ，列（要求出现在group by的后面） from 表 WHERE  GROUP BY   ORDER BY

#每一个工作 的最高工资
SELECT MAX(salary), job_id FROM employees GROUP BY job_id;

#查询部门信息
SELECT COUNT(*) ,location_id FROM departments GROUP BY location_id;

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

SELECT MAX(salary), manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

#员工大于2部门信息
#SELECT COUNT(*),department_id FROM employees GROUP BY department_id;
SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;

#查询每个工种有奖金的的最高工资
SELECT MAX(salary) job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;

#继续判断是否大于12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary) > 12000;

SELECT MIN(salary), manager_id 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 AVG(salary), department_id,job_id FROM employees GROUP BY department_id, job_id DESC;

4、连接查询

#1、等值连接
SELECT `name`, boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id;

#不可以，必须使用别名，否则 Unknown column 'employees.job_id' in 'field list'
SELECT last_name,employees.job_id,job_title FROM employees e, jobs j  WHERE e.job_id = j.job_id

SELECT last_name, e.job_id, job_title FROM employees e, jobs j WHERE e.job_id = j.job_id;

#查询又奖金的员工名，和部门名
SELECT last_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND e.commission_pct IS NOT NULL;

SELECT department_name, city FROM departments d, locations l WHERE d.location_id = l.location_id AND city LIKE '_o%';

SELECT COUNT(*) 个数，city FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY city;

SELECT department_name, d.department_id, d.manager_id, MIN(salary) FROM departments d, employees e WHERE d.department_id = e.department_id AND commission_pct IS NOT NULL GROUP BY department_name;

SELECT last_name, department_name, city FROM employees e, departments d, locations l WHERE d.department_id = e.department_id AND d.location_id = l.location_id AND city LIKE 's%';

#自链接
SELECT e.last_name, e.employee_id, m.employee_id, m.last_name FROM employees e, employees m WHERE e.manager_id= m.employee_id;