MySQL连接查询，子查询语句

语法：

       select   查询列表

       from    表1   别名 [连接类型]

       jion     表2  别名

       on       连接条件

       [where   筛选条件]

       [group by  分组]

       [having    筛选条件]

       [order by   排序列表]

分类：

内连接：inner

外连接：

              左外：left [outer]

              右外：right[outer]

              全外：full[outer]

交叉连接：cross

 

 

#############（一）、内连接########################################

语法：

       select 查询列表

       from 表1 别名

       ineer join 表2 别名

       on 连接条件;(后可外加其他如where、group by等子句)

 

特点：

①      可添加排序、分组、筛选

②      inner可以省略

③      筛选条件放在where 后面，连接条件放在on后面，提高分离性，便于阅读（对比与sql92,它的筛选条件和连接条件都是放在where后面）

分类：

###等值连接

 

#案例1：查询员工名、部门名

select

       last_name,

       department_name

from

       employees e

inner join departments d on e.department_id = d.department_id;

 

#案例2：查询名字中包含e的员工名和工种名【筛选】

select

       last_name,

       job_title

from

       employees  e

inner join jobs  j  on  e.job_id = j.job_id

where

       e.last_name like '%e%';

 

#案例3：查询那个部门个数大于3的部门名和员工个数，并按个数降序【排序】

select

       department_name,

       count(*)

from

       employees  e

inner join departments  d on e.department_id = d.department_id

group by

       department_name

having

       count(*) > 3

order by

       count(*) desc;

 

#案例4：查询部门个数大于3的城市名和部门个数【分组+筛选】

select

       city,

       count(*)

from

       locations l

inner join departments d on l.location_id = d.location_id

group by

       city

having

       count(*) > 3;

 

#案例5：查询员工名、部门名、工种名、并按部门名降序

select

       last_name,

       department_name,

       job_title

from

       employees e

inner join departments d on e.department_id = d.department_id

inner join jobs j on j.job_id = e.job_id

order by

       department_name desc;

 

###非等值连接

 

#查询员工的工资级别

select

       *

from

       job_grades;

 

select

       grade_level,

       last_name,

       salary

from

       employees e

join job_grades j on e.salary between lowest_sal

and highest_sal;

 

#查询工资级别的个数大于20，并且按工资级别降序【排序+分组+筛选】

select

       count(*) 个数,

       grade_level

from

       employees e

join job_grades j on e.salary between lowest_sal

and highest_sal

group by

       grade_level

having

       count(*) > 20

order by

       grade_level desc;

 

 

###自连接

#案例：查询员工的名字中包含'k'的员工名、上级的名字

select

       e.last_name 员工名,

       m.last_name 上级领导名

from

       employees e

join employees m on e.manager_id = m.employee_id

where

       e.last_name like '%k%';

 

###（二）、外连接#########################################

特点：

1. 外连接的查询结果为主表中的所有记录

a)        如果从表中有和它匹配的，则显示为匹配的值

b)       如果从表中没有和它匹配的，则线束null

c)        外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

1. 左外连接，left join左边的是主表（右外连接，right join右边的为主表）
2. 左外和右外交换两个表的顺序，可以实现同样的效果
3. 全外连接=内连接查询结果+1表中有而表2中没有的记录+表2有而表1没有的记录

引入：

#查询没有男朋友的女神名

use girls;

 

select

       *

from

       beauty;

 

select

       name,

       bo.id

from

       beauty bea

left join boys bo on bea.boyfriend_id = bo.id

where

       bo.id is null;

###左（右）外连接

#查询哪个部门没有员工

 

#左外

select

       department_name,

       employee_id

from

       departments d

left join employees e on d.department_id = e.department_id

where

       employee_id is null;

-----------------------------------------------------------

#右外

select

       department_name,

       employee_id,

from

       employees e

right join departments d on d.department_id = e.department_id

where

       employee_id is null;

 

###全外连接

#查询女神表和男朋友表的全外连接

select b.*,bo.*

from beauty b

full outer join boys bo

on b.boyfriend_id = bo.id;

 

###交叉连接（两个表进行笛卡尔乘积）

 

select b.*,bo.*

from beauty b

cross join boys bo;

 

总结（sql92  pk sql99）

功能：sql99支持的较多

可读性：sql99实现连接查询的条件和筛选选条件的分离，可读性较高

 

 

 

十八、子查询

含义：出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句 ，称为主查询或外查询

分类：

按子查询出现的位置：

              select 后面（仅仅支持标量子查询）

              from 后面（支持表子查询）

              where 或having后面（标量子查询、列子查询、行子查询）-》重要

              exists后面（相关子查询）（表子查询）

按结果集的行列数不同：

              标量子查询（结果集只有一行一列）

              列子查询（结果集只有一列多行）

              行子查询（结果集有一行多列）

              表子查询（结果一般为多行多列）

 

###where或having 后面

1. 标量子查询（单行子查询）

2. 列子查询（多行子查询）

3. 行子查询（多行多列）

特点:

①     子查询放在小括号内

②     子查询一般放在条件的右侧

③     标量子查询，一般搭配着单行操作符使用（>  <  <>  <=  >=  = ）

④     列子查询，一般搭配着多行操作符使用（in 、 any|some 、 all）

⑤     子查询的执行优先于主查询的执行，主查询的结果用到了子查询的结果

 

###标量子查询

 

#案例1：谁的工资比abel高？

 

select  *

from employees

where salary > (

              select salary from employees where last_name = 'abel'  #先查出abel的工资

);

 

#案例2：返回job_id与141号相同，salary比143号员工多的员工、姓名、job_id和工资

 

select last_name,job_id,salary

from employees

where job_id = (

       select job_id from employees where employee_id = 141

) and salary>(

       select salary from employees where employee_id = 143

);

 

#案例3:返回工资最少的员工的last_name  job_id  salary

 

select last_name,job_id,salary

from employees

where salary =(

       select min(salary) from employees

);

 

 

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

 

select department_id,min(salary)

from employees

group by department_id

having min(salary)>(

       select min(salary) from employees where department_id = 50

);

 

###列子查询

 

多行操作符：

 

 

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

select

       last_name,

       department_id

from

       employees e

where

       department_id in (

              select

                     department_id

              from

                     departments

              where

                     location_id in (1400, 1700)

       );

 

#案例2：返回其它部门中比job_id为‘it_prog’部门任一工资低的员工的员 工号、姓名、job_id 以及salary

select

       employee_id,

       last_name,

       job_id,

       salary

from

       employees e

where

       salary < any (

              select distinct

                     salary

              from

                     employees

              where

                     job_id = 'it_prog'

       )

and job_id <> 'it_prog';

-------------------两种方式等价------------------------

select

       employee_id,

       last_name,

       job_id,

       salary

from

       employees e

where

       salary < (

              select

                     max(distinct salary)

              from

                     employees

              where

                     job_id = 'it_prog'

       )

and job_id <> 'it_prog';

 

#案例3：返回其它部门中比job_id为‘it_prog’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary

select

       employee_id,

       last_name,

       job_id,

       salary

from

       employees e

where

       salary < all (

              select distinct

                     salary

              from

                     employees

              where

                     job_id = 'it_prog'

       )

and job_id <> 'it_prog';

-------------------两种方式等价------------------------

 

select

       employee_id,

       last_name,

       job_id,

       salary

from

       employees e

where

       salary < (

              select

                     min(distinct salary)

              from

                     employees

              where

                     job_id = 'it_prog'

       )

and job_id <> 'it_prog';

 

注意：因为列子查询中的all和any|some通能能被max或min分组查询后的标量子查询替换，因此用的较少

 

#行子查询（结果集一行多列或多行多列）

 

#案例：查询员工编号最小并且工资最高的员工信息

 

select

       *

from

       employees

where

       employee_id = (

              select

                     min(employee_id)

              from

                     employees

       )

and salary = (

       select

              max(salary)

       from

              employees

);#用标量子查询写的方式

-------------------两种方式等价------------------------

 

select

       *

from

       employees

where

       (employee_id, salary) = (

              select

                     min(employee_id),

                     max(salary)

              from

                     employees

       );#用行子查询写得方式

 

###select 后面

注意：仅仅支持标量子查询

#案例：查询每个部门的员工个数

select

       d.*, (

              select

                     count(*)

              from

                     employees e

              where

                     e.department_id = d.department_id

       )

from

       departments d;

 

#案例2：查询员工号=102的部门名

select

       (

              select

                     department_name

              from

                     departments d

              inner join employees e on e.department_id = d.department_id

              where

                     e.employee_id = 102

       );

 

###from后面

注意：将子查询结果充当一个表，要求必须取别名

 

#案例：查询每个部门的平均工资的工资等级

 

 

 

select

       ag_dep.*, g.grade_level

from

       (

              select

                     avg(salary) ag,

                     department_id

              from

                     employees

              group by

                     department_id

       ) ag_dep

inner join job_grades g on ag_dep.ag between lowest_sal

and highest_sal;

 

###exists后面（相关子查询）

语法:exists(完整的查询语句)

结果：1或0

select exists(select employee_id from employees);->1

select exists(select employee_id from employees where salary = 30000);->0

存在结果就返回1，不存在就返回0

 

#案例：查询有员工的部门名

 

select department_name

from departments d

where exists(

       select *

       from employees e

       where d.department_id = e.department_id

);

 原文：https://www.cnblogs.com/jane315/p/12846550.html