进阶5:分组查询
语法:
select 查询列表 ⑤
from 表 ①
【where 筛选条件】②
group by 分组的字段 ③
having 分组后筛选 ④
【order by 排序的字段】; ⑥
特点:
1、查询列表比较特殊要求是分组函数和groupby后面出现的字段
2、分组查询中筛选条件分为两类:分组前筛选和分组后筛选
针对的表 | 位置 | 连接的关键字 | |
分组前筛选 | 原始表 | group by前 | where |
分组后筛选 | group by后的结果集 | group by后 | having |
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的,优先考虑使用分组前筛选
3、groupby子句支持单个字段和多个字段分组(多个字段之间用逗号隔开,没有顺序要求),也支持表达式或函数(用的较少)
多个字段分组
#查询每个专业的男生人数和女生人数分别是多少
SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;
4、也可以添加排序(排序放在整个分组查询的最后)
5、在mysql中group by后面支持别名的使用,having也支持,但是在oracle中不支持,所以在MySQL中尽量别用,order by后面可以使用
进阶6:连接查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔成积现象:表1有m行,表2有n行,结果m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
sql语言对于连接的分类:
按年代分类:
sql92标准:仅仅支持内连接(在MySQL中)
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99标准【推荐】:支持内连接+外连接(只支持左外和右外)+交叉连接(在MySQL中)
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
sql92标准
等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
原理:把一张表所有的行都和另一张表进行匹配,连接条件相等则留下,否则过滤
1.一般需要为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
SELECT e.last_name,e.job_id,j.job_title # 起别名后就不能用employees.last_name,注意区分job_id属于哪个表
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
2.多表等值连接的结果为多表的交集部分
3.n表连接,至少需要n-1个连接条件
4.多表的顺序没有要求
5.可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
不是等值连接的连接,也是属于多个表之间
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
自连接
语法:
select 查询列表
from 表 别名1,表 别名2 (两个表都是一个表)
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
相当于等值连接,与等值连接的不同之处在于,自连接的原始表是一个表,而不是多张表
#案例:查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m # e代表员工表,m代表上级表,充分体现起别名的好处
WHERE e.`manager_id`=m.`employee_id`;
sql92 多表连接案例
#.查询员工名、部门名、工种名,并按部门名降序(添加三表连接)
SELECT last_name,department_name,job_title
FROM employees e, departments d, jobs j
where e.`department_id`= d.`department_id`
and e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
sql99标准
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接(★):inner
外连接
左外(★):left 【outer】
右外(★):right 【outer】
全外:full【outer】
交叉连接:cross
内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类:
等值
非等值
自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
多表连接案例
# 等值连接案例
SELECT e.last_name,e.job_id,j.job_title
FROM employees e
inner join jobs j on e.`job_id`=j.`job_id`;
# 非等值连接案例
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e
inner join job_grades g
on salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例:查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e
inner join employees m # e代表员工表,m代表上级表,充分体现起别名的好处
on e.`manager_id`=m.`employee_id`;
#.查询员工名、部门名、工种名,并按部门名降序(添加三表连接)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
外连接
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,left join左边的是主表
右外连接,right join右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
sql92和 sql99pk
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高
join连接总结