mysql数据库之子查询

一、概念。

sql语句中嵌套select语句，称为嵌套查询，又称子查询。

格式：select * from t1 where column1=（select column1 from t2）;

子查询外部的语句可以是SELECT/INSERT/UPDATE/DELETE任何一个。

二、分类。

根据子查询结果不同，分为标量子查询（子查询结果为单个值）、列子查询（子查询 结果为一列）、行子查询（子查询结果为一行）、表子查询（子查询结果为多行多列）。

根据子查询位置不同，分为WHERE之后、FROM之后、SELECT之后。 

三、标量子查询

1、子查询返回的结果是单个值（数字，字符串 ，日期等），最简单的形式，这种子查询成为标量子查询。

2、常用的操作符：= <>  <  <=  >  >=

3、示例1：查询销售部员工的所有信息。

梳理

a.查询销售部门id。

select id from dept where name=‘销售部’ 

b.根据销售部门员工id查询员工信息。

select * from emp where dept_id=4; 

 优化后的子查询

select * from emp where dept_id=(select id from dept where name=‘销售部’) ; 

 4、示例：查询在“方东白”入职后的员工信息。

梳理

a.查询房东白的入职日期。

select entrydate from emp where name='方启白'；

b.查询指定入职日期之后的员工信息。

select * from emp where entrydate > '2009-02-12';

优化后的子查询

select * from emp where entrydate > (select entrydate from emp where name='方启白'；);

四、列子查询。

1、子查询返回的结果是一列（也可以是多行），这种子查询称为列子查询。

2、常用操作符：IN、NOTIN、ANY、SOME、ALL

3、示例1：查询销售部和市场部的所有员工信息。

梳理

a.先查询销售部和市场部的部门id。

select id from dept where name="销售部" or name="市场部";

b.再根据部门id查询所有员工信息。

select * from emp where dept_id in (2,4);

优化后的子查询

select * from emp where dept_id in (select id from dept where name="销售部" or name="市场部");

4、示例2：查询比财务部所有人工资都高的员工信息。

梳理

a.查询财务部所有人的工资

select id from dept where name='财务部';

selecr salary from emp where dept_id=3;

#优化后
selecr salary from emp where dept_id=(select id from dept where name='财务部');

b. 比财务部所有人工资都高的员工信息

select * from emp where salary > all (select salary from emp where dept_id=(select id from dept where name='财务部'));

5、示例3：查询比研发部任意一人工资都高的员工信息。

梳理

a.查询研发部所有人的工资。

select salary from emp where dept_id=(select id from dept where name='研发部');

b.查询比研发部任意一人工资都高的员工信息。

select * from emp where salary > any (select salary from emp where dept_id=(select id from dept where name='研发部'));

五、行子查询。

1、子查询返回的结果是一行（也可以是多列），这种子查询称为行子查询。

2、常用操作符：=、<>、IN、NOTIN

3、示例：查询与张无忌的薪资及直属领导相同的员工信息。

梳理

a.查询张无忌的薪资和直属领导。

select salary,managerid from emp where name='张无忌';

b.查询与张无忌的薪资及直属领导相同的员工信息。

select * from emp (salary,managerid) = (12500,1);

select * from emp (salary,managerid) = (select salary,managerid from emp where name='张无忌');

六、表子查询。 

1、子查询返回的结果是多行多列，这种查询称为表子查询。

2、常用操作符：IN

3、示例1：查询与鹿杖客、宋远桥职位相同的员工信息。

梳理

a.查询鹿杖客和宋远桥的职位和薪资。

select job,salary from emp name = '鹿杖客' or name = '宋远桥';

b.查询与鹿杖客、宋远桥职位相同的员工信息。

select * from emp where (job,salary) in (select job,salary from emp name = '鹿杖客' or name = '宋远桥');

4、示例2：查询入职日期是“2006-01-01”之后的员工信息及部门信息。

梳理

a.查询入职日期是“2006-01-01”之后的员工信息。

select * from emp where entrydate > '2006-01-01';

b.查询这部分员工对应的部门信息。

select e.*,d* from (select * from emp where antrydate > '2006-01-01') e left join dept d on e.dept_id=d.id;

多表联查总结

1、多表关系

一对多：在多的一方设置外键，关联一的一方的主键。

多对多：建立中间表，中间表包含两个外键，关联两张表的主键。

一对一：用于表结构拆分，在其中任何一方设置外键unique，关联另一方的主键。

2、多表关系

内连接

隐式：SELECT ... FROM 表A，表B WHERE 条件...

显示：SELECT ... FROM 表A INNER JOIN 表B 条件...

外连接

左外：SELECT ... FROM 表A LEFT JOIN 表B ON 条件...

右外：SELECT ... FROM 表A RIGHT JOIN 表B ON 条件...

自连接：SELECT ... FROM 表A 别名1，表A 别名2 WHERE 条件... 

子查询：标量子查询、列子查询、行子查询、表子查询