Java --- MySql多表

Mysql多表

分组查询定义

根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表

分组查询语法

SELECT * FROM result;

-- 分组查询平均分

SELECT subjectNo,AVG(studentResult) FROM result GROUP BY subjectNo;

 分组实战（分组+排序）

一定是group在前面，order在后面（默认升序）

SELECT * FROM result;

-- 分组查询平均分

SELECT subjectNo,AVG(studentResult) FROM result

GROUP BY subjectNo

ORDER BY AVG(studentResult);

 分组实战（group by + Having + order）

核心要点：Having是分组之后过滤（筛选）

案例：

求result表中每个科目的平均成绩，且成绩大于等于60

-- 分组

SELECT subjectNo,AVG(studentResult) FROM result

GROUP BY subjectNo

-- 筛选

HAVING AVG(studentResult) >= 60

-- 降序

ORDER BY AVG(studentResult) DESC;

 多表关联查询种类

内连接       (INNER JOIN)

左外连接   (LEFT JOIN)

右外连接   (RIGHT JOIN)

自连接       (SELF JOIN)

 

 内连接 INNER JOIN

定义：使用比较运算符根据每个表通用列中的值匹配两个表中的行

SELECT * FROM student a

INNER JOIN

result b

ON a.studentNo = b.studentNo;

 

 左（外）连接 LEFT JOIN（在公司用的多）

核心要点：根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配，如果找不到与左表匹配的，用null表示

SELECT * FROM student a

LEFT JOIN

result b

ON a.studentNo = b.studentNo;

SELECT a.studentNo,a.studentName,b.studentResult FROM student a

LEFT JOIN

result b

ON a.studentNo = b.studentNo;

 右外连接 RIGHT JOIN

核心要点：右外连接的原理与左外连接相同，右表逐条去匹配记录；否则NULL填充

SELECT a.studentNo,a.studentName,b.studentResult FROM student a

RIGHT JOIN

result b

ON a.studentNo = b.studentNo;

拓展：左外连接和右外连接等价的场景

SELECT a.studentNo,a.studentName,b.studentResult FROM student a

RIGHT JOIN

result b

ON a.studentNo = b.studentNo;

SELECT a.studentNo,a.studentName,b.studentResult FROM result b

LEFT JOIN

student a

ON a.studentNo = b.studentNo;

 自连接 SELF JOIN

pid代表父类

核心要点：两张表连接查询（自连接）

SELECT a.categoryName AS '父节点',b.categoryName AS '子节点'

FROM category AS a,category AS b

WHERE a.categoryid = b.pid;

 物理主外键

RBAC

 

特点：

（1）删除数据麻烦【必须先删除t_user，t_role，t_permission中的数据】

（2）添加数据麻烦【必须先添加t_permission，t_role，t_user中的数据】

逻辑主外键

把物理主外键删除，达到业务上是主外键即可

码表关联【中间表关联】