SELECT last_name FROM employees WHERE last_name LIKE '_o%';
NULL
使用 IS (NOT) NULL 判断空值
SELECT last_name , manager_id FROM employees WHERE manager id IS NULL;
逻辑运算
AND
AND要求并的关系为真
SELECT employee_id , last_name , job_id , salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%MAN%'
OR
OR要求或关系为真
SELECT employee_id , last_name , job_id , salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%'
NOT
SELECT last_name , job_id FROM employees WHERE job_id NOT IN( 'IT_PROG','ST_CLERK','SA_REP' )
ORDER BY子句
使用ORDER BY子句排序
（1）ASC：升序
（2）DESC：降序
ORDER BY子句在SELECT语句的结尾
SELECT last_name , job_id , department_id , hire_date FROM employees ORDER BY hire_date;
降序排序
SELECT last_name , job_id , department_id , hire_date FROM employees ORDER BY hire_date DESC;
按别名排序
SELECT employee_id , last_name , salary * 12 annsal FROM employees ORDER BY annsal;
多个列排序
按照ORDER BY列表的顺序排序
SELECT last_name , department_id , salary FROM employees ORDER BY department_id , salary desc;
可以使用不在SELECT列表中的列排序
分组函数
分组函数作用于一组数据，并对一组数据返回一个值
组函数语法
SELECT [column,]group function(column), ... FROM table [where condition] [GROUP BY column] [ORDER BY column]
AVG和SUM函数
可以对数值型数据使用AVG和SUM函数
SELECT AVG（salary）,MAX(salary) MIN(salary),SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
MIN和MAX
可以对任意数据类型的数据使用MIN和MAX函数
SELECT MIN(hire_date),MAX(hire_date) FROM employees;
COUNT函数
COUNT(*)返回表中记录欧洲那个数，适用于任意数据类型
COUNT返回expr不为空的记录总数
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;
分组数据
GROUP BY 子句
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column , group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]
WHERE一定要放在FROM后面
在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
SELECT department_id , AVG(salary) FROM employees GROUP BY department_id;
包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT AVG(salary) FROM employees GROUP BY department_id ;
在GROUP BY子句中包含多个列
SELECT department_id , dept_id , job_id ,SUM(salary) FROM employees GROUP BY department_id , job_id ;
注意事项
- 不能在WHERE子句中使用组函数
- 可以在HAVING子句中使用组函数
HAVING 子句
使用HAVING过滤分组
- 行已经被分组
- 使用了组函数
- 满足HAVING子句中条件的分组将会被显示
SELECT column , group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]
SELECT department_id , MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) >10000 ;
