转载自: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
... join ... group by p.name order by r.id ASC ,p.id ASC;
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:执行情况的说明和描述。