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;