转载自：https://blog.csdn.net/StarryaSky/article/details/82802252
多表查询 ： 连接查询-子查询
MySQL基础操作链接 ； 工具： SQLyog
MySQL语法顺序：
- select[distinct]
- from
- join（left join/right join）
- on
- where
- group by
- having
- union
- order by
- limit
1. 表结构
用户，角色，权限三张表（主表）及三者之间的关系通过两张 “第三张外键表”维护。“外键表”中的两个字段分别使用外键指向主表的主键。（一个用户可以有多个角色，一个角色可以有多个权限；正常来看是是一对多的关系，但是反过来 某个权限可以有多个角色拥有。 所以三者关系必须理解为多对多的关系，所以需要 “第三张外键表”去维护两张表之间的关系，同时保证实体表与实体表之间互相独立）。
表名：
| 用户信息表 user | 用户角色表 user_role | |||||
| 字段 | 类型 | 描述 | 字段 | 类型 | 描述 | |
| id | int(11) NOT NULL | 主键，自增 | uid | int(11) NOT NULL | 外键：user id | |
| name | varchar(255) NOT NULL | 名称 | rid | int(11) NOT NULL | 外键：role id | |
| password | varchar(255) NOT NULL | 密码 | ||||
| age | int(11) NOT NULL | 年龄 | ||||
| gender | char(1) NOT NULL | 性别 | ||||
| 角色信息表 role | 角色权限表 role_permisson | |||||
| 字段 | 类型 | 描述 | 字段 | 类型 | 描述 | |
| id | int(11) NOT NULL | 主键，自增 | rid | int(11) NOT NULL | 外键：role id | |
| rolename | varchar(255) NOT NULL | 名称 | pid | int(11) NOT NULL | 外键：permission id | |
| 权限信息表 permission | ||||||
| 字段 | 类型 | 描述 | ||||
| id | int(11) NOT NULL |
| ||||
创建用户表：
-
CREATE
TABLE
`user` (
-
`id`
int(
11)
NOT
NULL AUTO_INCREMENT
COMMENT
'主键，自增',
-
`name`
varchar(
255)
NOT
NULL
COMMENT
'名称',
-
`password`
varchar(
255)
NOT
NULL
COMMENT
'密码',
-
`age`
int(
11)
NOT
NULL
COMMENT
'年龄',
-
`gender`
char(
1)
NOT
NULL
COMMENT
'性别',
-
PRIMARY
KEY (
`id`)
-
)
ENGINE=
InnoDB AUTO_INCREMENT=
7
DEFAULT
CHARSET=utf8
创建角色表：
-
CREATE
TABLE
`role` (
-
`id`
int(
11)
NOT
NULL AUTO_INCREMENT
COMMENT
'主键，自增',
-
`rolename`
varchar(
255)
NOT
NULL
COMMENT
'名称',
-
PRIMARY
KEY (
`id`)
-
)
ENGINE=
InnoDB AUTO_INCREMENT=
7
DEFAULT
CHARSET=utf8
创建权限表：
-
CREATE
TABLE
`permission` (
-
`id`
int(
11)
NOT
NULL AUTO_INCREMENT,
-
`name`
varchar(
255)
NOT
NULL,
-
PRIMARY
KEY (
`id`)
-
)
ENGINE=
InnoDB AUTO_INCREMENT=
6
DEFAULT
CHARSET=utf8
创建用户角色表：
-
CREATE
TABLE
`user_role` (
-
`uid`
int(
11)
DEFAULT
NULL
COMMENT
'外键-userId',
-
`rid`
int(
11)
DEFAULT
NULL
COMMENT
'外键-roleId',
-
KEY
`fk_ur_role_id` (
`rid`),
-
KEY
`fk_ur_user_id` (
`uid`),
-
CONSTRAINT
`fk_ur_user_id` FOREIGN
KEY (
`uid`)
REFERENCES
`user` (
`id`),
-
CONSTRAINT
`fk_ur_role_id` FOREIGN
KEY (
`rid`)
REFERENCES
`role` (
`id`)
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8
创建角色权限表：
-
CREATE
TABLE
`role_permission` (
-
`rid`
int(
11)
DEFAULT
NULL
COMMENT
'外键-roleId',
-
`pid`
int(
11)
DEFAULT
NULL
COMMENT
'外键-permissionId',
-
KEY
`fk_rp_role_id` (
`rid`),
-
KEY
`fk_rp_permission_id` (
`pid`),
-
CONSTRAINT
`fk_rp_role_id` FOREIGN
KEY (
`rid`)
REFERENCES
`role` (
`id`),
-
CONSTRAINT
`fk_rp_permission_id` FOREIGN
KEY (
`pid`)
REFERENCES
`permission` (
`id`)
-
)
ENGINE=
InnoDB
DEFAULT
CHARSET=utf8
2. 查询操作
1. （三表）哪些角色具有‘查询’的权限？
-
普通查询
-
SELECT r.id,r.rolename
FROM permission p ,role_permission rp,
role r
-
WHERE r.
`id`=rp.
`rid`
AND rp.
`pid`=p.
`id`
AND p.name=
'查询' ;
-
子查询:
注：通过子查询在role_permission表中查询出的rid有多个值（1，2，3），则需要在括号前面添加any字段。Any关键字 表示 where r.id = rp.rid1 or r.id = rp.rid2 or ... 。(扩展：all关键字用and替换or；some关键字和any关键字相同)。
-
SELECT r.id,r.rolename
FROM
role r
WHERE r.id=
ANY(
-
SELECT rp.rid
FROM role_permission rp
WHERE rp.pid = (
SELECT p.id
FROM permission p
WHERE p.name=
'查询')
-
);
子查询-内连接：
-
SELECT *
FROM
role r
WHERE r.id=
ANY(
-
SELECT rp.rid
FROM role_permission rp
JOIN permission p
ON rp.pid=p.id
AND p.name=
'查询'
-
);
-
内连接Join...on...
-
SELECT *
FROM role_permission rp
-
JOIN permission p
ON rp.pid=p.id
AND p.name=
'查询'
-
JOIN
role r
ON rp.rid=r.id
order
by rp.id
desc;
2.（五表）查询用户“管理员A” 有哪些角色和哪些权限？（分组排序）
- 排序：先按 角色id 升序，然后在按 权限id升序
-
SELECT *
FROM
USER u
-
JOIN user_role ur
ON u.id=ur.uid
AND u.name=
'管理员A'
-
JOIN
role r
ON ur.rid=r.id
-
JOIN role_permission rp
ON r.id=rp.rid
-
JOIN permission p
ON p.id=rp.pid
-
order
by r.id
ASC ，p.id
ASC ;
查询后的数据：
- 分组：在上述的基础上：
1. 查询 管理员A拥有的权限？; 在 order by 的前面加上 group by p.name
3.综合-统计
- 查询出 至少有2中权限的角色？
- 首先：查询出角色和权限信息；（拥有2种权限以上的只有 管理员和教师）
-
SELECT *
FROM
role r
-
JOIN role_permission rp
ON r.id = rp.rid
-
JOIN permission p
ON p.id = rp.pid
-
ORDER
BY r.id
ASC;
- 其次 使用 count 函数统计出权限大于2的角色信息
-
SELECT * ,
COUNT(r.rolename)
FROM
role r
-
JOIN role_permission rp
ON r.id = rp.rid
-
JOIN permission p
ON p.id = rp.pid
-
-
GROUP
BY r.rolename
HAVING
COUNT(p.name)>=
2
-
ORDER
BY r.id
ASC ,p.id
ASC;
查询 角色信息，按照角色名称或id进行分组查询。条件可以选择统计权限的个数count(p.name),也可以统计角色的个数，方法不是唯一。
注： 去重可以使用distinct 和group by 。 显然关联查询中不能使用distinct。原因：distinct位置固定只能跟在select 后面如（select distinct name from...）,且只适用于查询某个字段；若需要查询多个字段（如 select distinct name ,age from ...）,则mysql会过滤（name字段并且age字段）也相同的数据。
后续...
附：查看mysql查询效率--explain的用法
explain语句用于查看一条SQL语句的查询执行计划，直接把explain放到要执行的SQL语句的前面即可。
例：explain select * from ...
explain extended和explain的输出结果一样，只是用explain extended语句后可以通过show warnings查看一条SQL语句的反编译的结果，让我们知道我们输入的一条SQL语句真正是怎么执行的。
对输入结果简单解释一下：
- select_type：表示select类型，常见的取值有SIMPLE（不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的或者后面的查询语句）、SUBQUERY（子查询中的第一个select）等。
- table：输出结果集的表。
- type：表示表的连接类型，性能由好到差的连接类型为
- system（表中仅有一行，即常量表）、
- const（单表中最多有一个匹配行，例如PRIMARY KEY或者UNIQUE INDEX）、
- eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用PRIMARYKEY或者UNIQUE INDEX）、
- ref（与eq_ref类似，区别在于不使用PRIMARYKEY或者UNIQUE INDEX，而是使用普通的索引）、
- ref_of_null（与ref类似，区别在于条件中包含对NULL的查询）
- index_merge（索引合并化）、
- unique_subquery（in的后面是一个查询主键字段的子查询）、
- index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）、
- range（单表中的范围查询）、
- index（对于前面的每一行都通过查询索引来得到数据）、
- all（对于前面的每一行的都通过全表扫描来获得数据）。
结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null >index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
- possible_keys：表示查询时，可能使用到的索引。
- key：表示实际使用的索引
- key_len：索引字段的长度
- rows：扫描行的数量
- extra：执行情况的说明和描述。
1333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
