进阶6、7 连接查询与子查询

进阶六、连接查询

---

SQL92和SQL99的区别

SQL99：使用join关键字代替了SQL92中的逗号，并将连接条件和筛选条件进行了分离，提高了阅读性。

SQL92语法

一、内连接

1.等值连接

语法：

select 查询列表

from 表名1 别名1,表名2 别名2,…

where 等值连接的条件

特点

1. 为了解决多个表中的字段名重名问题，往往要为表起别名，提高语义性。
2. 表的顺序无要求

简单的两表连接

查询员工名和部门名

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

查询部门编号大于100的部门名和所在的城市名

SELECT department_name,city
FROM departments d ,locations l
WHERE d.location_id = l.location_id
AND d.department_id > 100;

查询有奖金的员工名，部门名

SELECT last_name,department_name
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL;

查询城市名中第二个字符为 o的部门名和城市名

SELECT city,department_name
FROM departments d ,locations l
WHERE d.location_id=l.location_id
AND city LIKE '_o%';

添加分组+筛选

#查询每个城市的部门个数
SELECT COUNT(*),l.city
FROM departments d ,locations l
WHERE d.location_id=l.location_id
GROUP BY l.city;

2.非等值连接

1 查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e ,job_grades g
WHERE e.salary BETWEEN  g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A'; # 可继续添加筛选

3.自连接

查询员工名和上级名

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e ,employees m
WHERE m.employee_id=e.manager_id;

SQL99语法

一、内连接

语法

select 查询列表

from 表名1 别名

【inner】 join 表名2 别名

on 连接条件

where 筛选条件

group by 分组列表

having 分组后筛选

order by 排序列表;

1.等值连接

1 简单连接

# 查询员工名和部门名
SELECT last_name,department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id;

2添加筛选条件

#查询部门编号大于100的部门名和所在城市名
SELECT department_name,city
FROM departments d JOIN locations l
ON d.location_id = l.location_id
WHERE department_id>100;

3 添加分组加筛选

#查询每个城市的部门个数
SELECT COUNT(*) 部门个数,city
FROM departments d 
JOIN locations l 
ON d.location_id = l.location_id
GROUP BY city;

4 添加分组加筛选加排序

#查询部门中员工个数大于10的部门名，并按员工个数降序
SELECT d.department_name,COUNT(*)
FROM departments d 
JOIN employees e 
ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING count(*)>10
ORDER BY COUNT(*) DESC ;

2.非等值连接

查询部门编号在10-90之间 的员工的工资级别，并按级别进行分组

SELECT COUNT(*) 个数,grade_level
FROM employees e 
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
WHERE department_id BETWEEN 10 AND 90
GROUP BY grade_level;

3.自连接

查询员工名和对应领导名

SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e 
JOIN employees m
ON e.manager_id=m.employee_id;

查询员工姓名，入职日期并按入职日期升序

SELECT last_name,hiredate
FROM employees
ORDER BY YEAR(hiredate);

日期函数拓展

SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(hiredate) FROM employees;
SELECT DAY(hiredate) FROM employees;
SELECT HOUR(hiredate) FROM employees;
SELECT MINUTE(hiredate) FROM employees;
#将当前日期显示成  XXX年XXX月XXX日
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

二、外连接查询（只SQL99语法）

外连接

说明

表中所有的记录**，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null。

应用场景

一般用于查询主表中有但是从表中没有的记录。

特点

1. 外连接分为主从表，两表的顺序不能调换。

2. 如果为左连接，left join左边为主表；

   如果为右连接，left join右边为主表。

3. 外连接的查询结果=内连接的查询结果+主表有但从表没有的记录

语法

select 查询列表

from 表1 别名

left/right 【outer】 join 表2 别名

on 连接条件

where 筛选条件;

左连接

有对象显示对象，无显示null

SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo 
ON b.boyfriend_id=bo.id;

哪个没有对象

SELECT b.name
FROM beauty b
LEFT JOIN boys bo 
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

右连接

哪个没有对象

SELECT d.department_id,d.department_name
FROM employees eRIGHT 
JOIN departments d 
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

没有员工的部门个数

SELECT COUNT(*) 部门个数
FROM employees eRIGHT 
JOIN departments d 
ON d.department_id = e.department_id 
WHERE e.department_id IS NULL;

进阶七、子查询

概念

说明

当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或者内查询，外面的select语句称为主查询或外查询。

分类

按子查询出现的位置进行分类：

1. 在select 后面

   要求：子查询的结果为单行单列（标量子查询）

2. from 后面

   要求：子查询的结果可以为多行多列

3. ！主要： where或having后面

   要求：子查询的结果必须为单列（可以为多行）

   • 单行子查询
   • 多行子查询

4. exists 后面

   要求：子查询结果必须为单列（相关子查询）

特点

1. 子查询放在where或having 的条件中时，要求必须在条件的右侧。

2. 子查询一般放在小括号中。

3. 子查询的执行优先于主查询。

4. 单行子查询对应了 单行操作符：> < >= <= <>

   多行子查询对应了 多行操作符： any/some all in

单行子查询

案例1 查询和Oliver 相同部门 的员工姓名和工资

SELECT department_id
FROM employees e
WHERE first_name='Oliver';

SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id
       FROM employees e
       WHERE first_name='Oliver');

案例2 查询工资比公司平均工资高的员工的员工号、姓名和工资

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(SELECT AVG(salary)
    FROM employees
    );

多行子查询

• in： 判断某字段是否在指定列表内

  x in （10,30,40）

• any/some : 判断某字段的值是否满足其中任意一个

  x > any(10,30,40) ===> x > min(10,30,40)

  x = any(10,30,40) ===> x in (10,30,40)

• all : 判断某字段的值是否满足所有

  x > all(10,30,40) ===> x > max()

案例1 查询location_id是1700或1400的部门中的所有员工姓名

SELECT department_id
FROM departments
WHERE location_id=1700 OR location_id=1400;

SELECT last_name
FROM employees
WHERE department_id IN(
        SELECT department_id
        FROM departments
        WHERE location_id=1700 OR location_id=1400);

案例2 查询其他部门中比job_id 为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(SELECT DISTINCT salary
              FROM employees
              WHERE job_id='IT_PROG');

案例3 查询其他部门中比job_id 为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salary

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(SELECT DISTINCT salary
                 FROM employees
                 WHERE job_id='IT_PROG');
#等价于
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(SELECT DISTINCT MIN(salary)
                 FROM employees
                 WHERE job_id='IT_PROG');

其他位置子查询。。

子查询案例

# 查询各部门中 工资比本部门平均工资高 的员工的员工号，姓名，工资
#1 查询各部门平均工资
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id;
#2 将1的结果和employees 表连接查询
SELECT employee_id,last_name,salary
FROM employees e
JOIN (SELECT department_id,AVG(salary) ag
      FROM employees
      GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary>d.ag;

查询和姓名中包含字母 u的员工在相同部门的员工的员工号和姓名

#1
SELECT  DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
GROUP BY department_id;
#2
SELECT e.employee_id,e.last_name
FROM employees e
JOIN (SELECT DISTINCT department_id
      FROM employees
      WHERE last_name LIKE '%u%'
      GROUP BY department_id) dep
ON  e.department_id=dep.department_id;

查询管理者是 K_ing的员工的姓名，工资

#1查询管理者是K_ing的编号
SELECT employee_id
FROM employees
WHERE last_name='K_ing';
#2
SELECT last_name,salary
FROM employees 
WHERE manager_id IN(SELECT employee_id
FROM employees
WHERE last_name='K_ing');

查询平均工资最低的部门信息和该部门的平均工资

#查询各部门平均工资，排序
#1
SELECT department_id,AVG(salary) avgs
FROM employees e
GROUP BY department_id
ORDER BY avgs
LIMIT 1;
#2
SELECT d.*,av.avgs
FROM departments d
JOIN (SELECT department_id,AVG(salary) avgs
	FROM employees e
	GROUP BY department_id
	ORDER BY avgs
	LIMIT 1) av
ON d.department_id=av.department_id;