sql语句2

连接查询（多表查询）

笛卡尔积：表1 有m行 表2 有n行 结果 m*n行

分类：按功能分类

1. 内连接：等值连接、非等值连接、自连接

2. 外连接：左外连接、右外连接、全外连接

3. 交叉连接
   sql92 支持所有的内连接

   等值
   非等值
   自连接
   也支持一部分外连接（用于oracle、sqlserver，mysql不支持）
   

   sql99 （推荐使用）

   内连接
   	等值
   	非等值
   	自连接
   外连接
   	左外
   	右外
   	全外（mysql不支持）
   交叉连接
   

sql92

• 等值连接：select user_name, dep_id from user dep where user_dep = dep_id;
  注：

  1. 多表等值连接的结果为多表的交集部分
  2. n 表连接，至少需要n - 1个连接条件
  3. 多表的顺序没有要求
  4. 一般需要为表取别名
  5. 可以搭配所有的查询字句，eg：排序、分组、筛选

• 非等值连接

  eg：工资等级表（job_grades）
  
  查询员工的工资以及工资等级

  SELECT e.salary, j.grade_level FROM employees e ,job_grades j WHERE e.salary BETWEEN j.lowest_sal AND highest_sal;

  SELECT e.salary, j.grade_level from employees e LEFT JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

• 自连接（表自己连接自己）
  查询每个员工的领导编号及姓名
  
  SELECT e.employee_id 员工编号, e.last_name 员工名称,m.employee_id 领导编号,m.last_name 领导名称 FROM employees e ,employees m WHERE e.manager_id = m.employee_id;
  

sql99

• 内连接

1. 语法：
   select 查询列表 from 表1 别名 【inner】 join 表2 别名 on 连接条件
   where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表
   limit 子句;

2. 特点：
   ①表的顺序可以调换
   ②内连接的结果=多表的交集
   ③n表连接至少需要n-1个连接条件

3. 案例：
   员工表
   
   部门表
   
   工种表
   

   3.1 查询员工名及所在的部门名（inner join 后面可以不需要on，left join 后面必须需要on）

   SELECT e.last_name,d.department_name FROM employees e INNER JOIN departments d WHERE e.department_id = d.department_id;

   3.2 查询名字中包含e的员工名和该员工的工种名

   SELECT e.last_name,j.job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE last_name like “%e%”

   3.3查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

   SELECT COUNT(),d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY e.department_id HAVING(COUNT()>3) ORDER BY COUNT( *) desc;

   3.4查询员工名、部门名、工种名、，并按照部门名降序

   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 e.job_id = j.job_id
   ORDER BY department_name desc;

   总结：

   1. 可以添加排序、分组、筛选
   2. inner可以省略
   3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
   4. inner join 连接和sql92语法中的等值连接效果是一样的，都是查询的多表的交集

• 外连接

1. 语法：
   select 查询列表 from 表1 别名 left|right|full【outer】 join 表2 别名 on 连接条件
   where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表
   limit 子句;

2. 特点：
   ①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
   ②left join 左边的就是主表，right join 右边的就是主表
   full join 两边都是主表
   ③一般用于查询除了交集部分的剩余的不匹配的行

3. 案例
   beauty 表
   
   boys表
   
   3.1 查询没有男朋友的女生信息

   SELECT * FROM beauty b left outer joinboys y ON b.boyfriend_id = y.id WHERE y.id IS NULL;

   3.2 查询哪个部门没有员工

   SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON e.department_id = d.department_id WHERE e.employee_id IS NULL;

   SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL;

   总结：
   1. 外连接的查询结果为主表中的所有记录，如果表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
   外连接查询结果 = 内连接结果+主表中有而从表中没有的记录
   2. 左外连接：left join左边的是主表
   右外连接：right join右边是主表
   3. 左外和右外，交换两个表的顺序，可实现同样的效果
   4. 全外连接 = 内连接的结果+表一中有但是表2中没有的+表二中有但是表一中没有的

• 交叉连接

1. 语法：
   select 查询列表 from 表1 别名 cross join 表2 别名;

2. 特点：
   类似于笛卡尔乘积、

   SELECT * FROM beauty b cross join boys y;

• 子查询

  出现在其他语句中的select语句，称为子查询或者内查询
  出现在外部的查询称为主查询或者外查询

1. 分类

   按子查询出现的位置：

    select 后面  （仅仅支持（标量子查询））
    from后面 （支持表子查询）
    ※where或者having后面 （标量子查询 、列子查询 、行子查询）
    exists后面（相关子查询）
   

   按照结果集的行列数不同

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

2. 特点

   子查询放在小括号内
   子查询一般放在条件的右侧
   标量子查询，一半搭配单行操作符使用 > 、< 、 >= 、 <= 、 <>
   列子查询，一般搭配多行操作符使用 in、any、some 、all

3. 案例

   标量子查询

   3.1查询工资高于Abel的员工

   SELECT *
   FROM employees
   WHERE salary >
   (SELECT salary FROM employees WHERE last_name = “Abel”)

   3.2 返回job_id 与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资（where 后面可以有多个子查询）

   SELECT last_name,job_id 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.3 返回工资最少的员工信息 （分组函数）

   SELECT * FROM employees WHERE salary = (SELECT MIN(salary) from employees);

   行子查询

   3.4 查询每个部门的最低工资且大于50号部门的最低工资 （分组函数+筛选）

   SELECT MIN(salary),department_id FROM employees
   GROUP BY(department_id)
   HAVING
   MIN(salary) > (SELECT min(salary) FROM employees WHERE department_id = ‘50’)

   列子查询（多行子查询）

   3.5 查询location_id 为1400或者1700的员工信息

   SELECT * FROM employees WHERE department_id
   IN
   (SELECT department_id FROM departments WHERE location_id IN(1400,1700));

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

   3.6 查询每个部门的员工个数

   SELECT d. * ,
   ( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) 个数
   FROM departments d;

   3.7 员工编号为102的部门名称

   SELECT ( SELECT department_name FROM departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102) 部门名称;