MySql多表联查

多表联查

使用多表联查的场景，有些时候数据在不同的表中，这个时候我们就需要用到mysql中的多表联查。

多表联查概念

将两个或两个以上的表按某个条件连接起来，从而选取需要的数据。多表联查是同时查询两个或两个以上的表时使用的。

多表联查分类

1. 内连接查询

   内连接查询使用关键字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. 外连接查询

   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());

1. 查询出用户姓名，城市，性别

   //实现方式一,这种方式等价于内连接

   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;
   

   !!这几种方式都是一样的!!

2. 查询出用户的姓名，城市。并且性别为1的用户

   select name,city,sex from user inner join user_detail on user.id=user_detail.uid where sex=1;
   

3. 按照城市对用户分组，得到每个城市的所有用户名字

   select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city;
   

4. 按照城市对用户分组，得到每个城市的所有用户名字，并且只要组人数超过1个人的

   select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city having count(*) > 1;
   

5. 查询出订单表中的用户的姓名，性别，城市，用户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;

• 外连接的实例

  1. 列出所有用户的所有信息(包括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等

下图为子查询说明图：

• 注意

  1. 子查询先执行里面的SQL语句，再执行外面SQL语句。
  2. 子查询的效率比较低，一般建议使用join替换子查询
  3. 子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再