多表联查
使用多表联查的场景,有些时候数据在不同的表中,这个时候我们就需要用到mysql中的多表联查。
多表联查概念
将两个或两个以上的表按某个条件连接起来,从而选取需要的数据。多表联查是同时查询两个或两个以上的表时使用的。
多表联查分类
-
内连接查询
内连接查询使用关键字join或cross join 或 inner join,
然后通过on连接表与表之间的条件注意: 内连接查询只能查询出两个表符合条件的数据
语法:
SELECT 字段,字段1,.. FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
-
外连接查询
2.1 左外连接
左外连接使用关键字left join, 然后通过on连接表与表之间的条件 注意:left join 会查询出left join左边的表所有的数据,即使右表没有匹配 语法: SELECT 字段,字段1,... FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
2.2 右外连接
左外连接使用关键字right join, 然后通过on连接表与表之间的条件 注意: 即使左表中没有匹配,也从右表返回所有的行 语法: SELECT 字段,字段1,.... FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
多表联查例子
//用户表
create table user(
id int primary key auto_increment,
name varchar(150) unique not null,
pass varchar(255) not null
)engine=innodb default charset=utf8;
//用户详情表
create table user_detail(
id int primary key auto_increment,
uid int not null comment '关联用户表的id',
city varchar(100) not null,
sex tinyint not null comment '1男 0女'
)engine=innodb default charset=utf8;
//订单表
create table orders(
id int primary key auto_increment,
uid int not null comment '关联用户表的id',
orderno int not null comment '订单号,关联订单详情表id',
create_time timestamp not null comment '订单下单时间'
)engine=innodb default charset=utf8;
//插入测试数据
insert into user(name,pass) values('jack',md5('123456'));
insert into user(name,pass) values('mary',md5('123456'));
insert into user(name,pass) values('rose',md5('123456'));
insert into user(name,pass) values('test',md5('123456'));
insert into user(name,pass) values('mark',md5('123456'));
insert into user_detail(uid,city,sex) values(1,'广州',1);
insert into user_detail(uid,city,sex) values(2,'上海',0);
insert into user_detail(uid,city,sex) values(3,'北京',0);
insert into user_detail(uid,city,sex) values(4,'北京',1);
insert into orders(uid,orderno,create_time) values(2,3,now());
insert into orders(uid,orderno,create_time) values(4,5,now());
-
查询出用户姓名,城市,性别
//实现方式一,这种方式等价于内连接
select user.id,name,city,sex from user,user_detail where user.id = user_detail.uid;
//实现方式二
select name,city,sex from user inner join user_detail on user.id=user_detail.uid;
//实现方式三
select name,city,sex from user join user_detail on user.id=user_detail.uid; select name,city,sex from user cross join user_detail on user.id=user_detail.uid;
!!这几种方式都是一样的!!
-
查询出用户的姓名,城市。并且性别为1的用户
select name,city,sex from user inner join user_detail on user.id=user_detail.uid where sex=1;
-
按照城市对用户分组,得到每个城市的所有用户名字
select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city;
-
按照城市对用户分组,得到每个城市的所有用户名字,并且只要组人数超过1个人的
select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city having count(*) > 1;
-
查询出订单表中的用户的姓名,性别,城市,用户id,以及用户的下单时间(三表联查)
已有什么,求什么?
已有uid,求姓名,性别,用户下单时间
user表,user_detail,orders
select name,sex,city,u.id,create_time from orders as o inner join user as u on o.uid=u.id inner join user_detail d on u.id = d.uid;
-
外连接的实例
- 列出所有用户的所有信息(包括name,pass,sex,city)
//使用左连接实现
select u.id,name,pass,sex,city from user as u left join user_detail as d on u.id = d.uid;
//使用右连接实现
select u.id,name,pass,sex,city from user_detail as d right join user as u on u.id = d.uid;
MySQL子查询
- 子查询定义
子查询就是把一个查询嵌套在另一个查询中。
子查询可以包含普通select可以包括任何子句,比如:distinct,group by, order by,limit,join等
下图为子查询说明图:
-
注意
- 子查询先执行里面的SQL语句,再执行外面SQL语句。
- 子查询的效率比较低,一般建议使用join替换子查询
- 子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再