数据库课程笔记--数据查询（1）连接查询

一、连接查询-笛卡尔乘积

连接查询：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1m行，表2n行，结果有m*n行（所有表实现完全连接）

产生原因：没有有效的连接条件

二、连接查询的分类

分类：
按年代分类：

• SQL92标准：仅支持内连接
• SQL99标准【推荐】：支持内连接+外连接（不支持全外连接）+交叉连接

按功能分类：

• 内连接：
  等值连接
  非等值连接
  自连接
• 外连接：
  左外连接
  右外连接
  全外连接
• 交叉连接

三、内连接（sql92）

3.1等值连接（sql92）

eg1：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

eg2:查询工种号、工种名、员工名（起别名）
#此处的job_id有歧义，需要表名限制
select last_name,employees.job_id,job_title
#起别名，提高语句的简洁度，区分多个重名的字段
FROM employees AS e,jobs AS j
WHERE e.job_id=jobs.job_id;

备注：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

eg3：查询城市名中第二个字符为o的部门名和城市名（增加筛选）
SELECT department_name,city
#起别名可省略AS
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE ‘_o%’;

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

eg5查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资（加分组)
SELECT department_name,d.‘manager_id’,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.‘manager_id’;

eg6查询员工名、部门名和所在的城市（三表连接）
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE ‘s%’
#降序排列
ORDER BY department_name DESC;

3.2非等值连接（sql92）

eg查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.‘lowest_sal’ AND g.‘highest_sal’
AND g.grade_level=A;

3.3自连接（sql92）

eg查询员工名和上级的名称
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语法

语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：

• 内连接（★）：inner

• 外连接

  左外(★):left 【outer】
  右外(★)：right 【outer】
  全外：full【outer】

• 交叉连接：cross

五、内连接（sql99）

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：

• 等值
• 非等值
• 自连接

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

5.1等值连接

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

eg2查询名字中包含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%’

eg3查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city,COUNT() 部门个数
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT()>3;

eg4查询员工名、部门名、工种名，并按部门名降序（三表连接）
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;

5.2非等值连接

eg1查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.'salaryBETWEEN g.lowest_salAND g.highest_sal`;

eg2查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(),grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT()>20
ORDER BY grade_level DESC;

5.3自连接

eg1查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id= m.employee_id;

eg2查询姓名中包含字符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%’;

六、外连接（sql99）

应用场景：用于查询一个表中有，另一个表没有的记录

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

eg查询哪个部门没有员工

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

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

#全外
USE girls;
SELECT b.,bo.
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

七、交叉连接（sql99）

交叉连接又叫’笛卡尔积’，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积（例如：T_student和T_class，返回4*4=16条记录），如果带where，返回或显示的是匹配的行数。

eg1不带WHERE
(相当与笛卡尔积，左表和右表组合)
SELECT *
#cross join 可以省略不写
FROM T_student cross join T_class

等于
SELECT *
FROM T_student, T_class

eg2带WHERE
(往往会先生成两个表行数乘积的数据表，然后才根据where条件从中选择, 查询结果跟等值连接的查询结果是一样。)
select * from T_student s cross join T_class c where s.classId = c.classId
#cross join后加条件只能用where,不能用on)

八、参考文档链接：

https://blog.youkuaiyun.com/weixin_39241397/article/details/79772379
https://blog.youkuaiyun.com/JavaCoder_juejue/article/details/83037521
《MySQL 基础》，尚硅谷