MySQL子查询与多表查询

一、子查询

查询中的查询即为子查询，一般使用括号将子查询sql括起来。

1、如下SQL语句：

 select * from (select * from user_info) users

2、当在查询过程中需要知道一个已知量的不确定数据时使用子查询。

select * from address where user_id=(select id from user_info where real_name='王晓明') #使用子查询先通过姓名获取id，然后再通过用户id查询地址表

3、如果子查询只返回一个结果，则可以使用=,也可以使用in；但是如果确定子查询永远只返回一个结果，则不建议使用in，例如上面SQL语句可变为如下SQL语句

 select * from address where user_id in(select id from user_info where real_name='王晓明');

二、多表查询

多个表关联查询需要依据多表之间列关系将其连接起来，这种连接方式分为三种：内连接(inner join)、外连接(outer join)及交叉连接(cross join)

以下面一个例子来说明内连接、外连接和交叉连接。

1、创建user_info表和address表，向两个表中添加如下数据

#user_info表
create table user_info(
      id int(2) primary key,
      user_name varchar(12) unique,
      password varchar(15) not null,
      real_name varchar(8) not null,
      age int(3)
); 

#address表
create table address(
      id int(2) primary key,
      user_id int(2) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
); 
  
insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');
 
insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');
 
insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9); #只有用户信息，没有地址信息。
insert into address values(6,6,'刘倩','13159775555','吉林长春'); #只有地址信息，没有用户信息。

2、怎样查询每个用户的住址？

方法一：使用子查询

select * from address where user_id in(select id from user_info)

结果

缺点：缺少了两条不完整的数据（3,‘街角の风铃’,‘27w4921’,‘李晓飞’,9）、（6,6,‘刘倩’,‘13159775555’,‘吉林长春’）

方法二：使用左外连接查询

select ui.*,addr.*
from user_info ui
left join address addr 
on addr.user_id=ui.id

结果

缺点：缺少一条不完整的数据（6,6,‘刘倩’,‘13159775555’,‘吉林长春’）；另外缺少地址信息的那条数据（3,‘街角の风铃’,‘27w4921’,‘李晓飞’,9）地址信息用“null”填充。

方法三：使用右外连接查询

select ui.*,addr.*
from user_info ui
right join address addr 
on addr.user_id=ui.id

结果

不足：缺少了一条不完整的数据（3,‘街角の风铃’,‘27w4921’,‘李晓飞’,9）；另外缺少用户信息的那条数据（6,6,‘刘倩’ ,‘13159775555’,‘吉林长春’）用户信息用null填充。

方法四：使用全外连接查询

select ui.*,addr.* 
from user_info ui 
full join address addr 
on ui.id = addr.user_id;

结果出错!

因此MySQL数据库不支持全外连接。

方法五：使用内连接查询

select ui.*,addr.*
from user_info ui
inner join address addr 
on addr.user_id=ui.id

结果

不足：缺少了两条不完整的数据3,‘街角の风铃’,‘27w4921’,‘李晓飞’,9）、（6,6,‘刘倩’ ,‘13159775555’,‘吉林长春’），地址信息和用户信息不完整的数据都无法查询。

方法六：使用交叉连接查询

select ui.*,addr.* 
from user_info ui 
cross join address addr  
order by ui.id

结果

分析：显示左表（cross join左边的表）user_info的每一行数据与右表（cross join右边的表）address所有行数据的交叉组合结果。