MySQL基础（⼆）：多表查询--分组排序
多表查询：连接查询-⼦查询
mysql语句多表查询； ⼯具： SQLyog
MySQL语法顺序：
1. select[distinct]
2. from
3. join（left join/right join）
4. on
5. where
6. group by
7. having
8. union
9. order by
10. 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
f5硬件负载均衡器
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
字段类型描述objectiveness翻译
id int(11) NOT NULL
主键，⾃增
name varchar(255) 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.lename 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.lename 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='查询'
);
内连接
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的⾓⾊信息
handlemessageSELECT * , lename) FROM role r
JOIN role_permission rp ON r.id = rp.rid
JOIN permission p ON p.id = rp.pid
GROUP lename 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 ），且只适⽤于查询某个字段；若需要查询多个字段（如 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：表⽰表的连接类型，性能由好到差的连接类型为
fpga是哈佛结构吗
1. system（表中仅有⼀⾏，即常量表）、
2. const（单表中最多有⼀个匹配⾏，例如PRIMARY KEY或者UNIQUE INDEX）、
3. eq_ref（对于前⾯的每⼀⾏，在此表中只查询⼀条记录，简单来说，就是多表连接中使⽤PRIMARYKEY或者UNIQUE
INDEX）、
4. ref（与eq_ref类似，区别在于不使⽤PRIMARYKEY或者UNIQUE INDEX，⽽是使⽤普通的索引）、
5. ref_of_null（与ref类似，区别在于条件中包含对NULL的查询）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。