SQL连接查询案例

红太狼 2023-06-25 15:28 12阅读 0赞

# （一）等值连接（sql92语法） #

## 1.简单的两表连接 ##

### 案例1：查询员工名和对应的部门名 ###

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

为表起别名：  
① 提高语句的简洁度  
② 区分多个重名的字段

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

## 2.添加筛选条件 ##

### 案例1：查询部门编号>100的部门名和所在的城市名 ###

SELECT department_name,city
    from departments d, locations l
    WHERE d.location_id = l.location_id
    and d.department_id>100

### 案例2： 查询有奖金的员工名、部门名 ###

SELECT last_name,department_name
    from employees e,departments d
    WHERE e.commission_pct is not null
    and e.department_id=d.department_id

### 案例3：查询城市命中第二个字符为o的部门名和城市名 ###

SELECT department_name,city
    from departments d,locations l
    where d.location_id = l.location_id
    and city like "_o%"

## 3. 添加分组+筛选 ##

### 案例1：查询每个城市的部门个数 ###

SELECT city,COUNT(*)
    from departments d, locations l
    WHERE d.location_id=l.location_id
    GROUP BY l.city

### 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 ###

select d.department_name,d.manager_id,MIN(e.salary)
    from employees e,departments d
    where e.department_id=d.department_id
    and e.commission_pct is not null
    GROUP BY d.department_id

### 案例3：查询部门中员工个数>10的部门名 ###

SELECT department_name,COUNT(*)
    from departments d,employees e
    where e.department_id=d.department_id
    GROUP BY d.department_id
    HAVING COUNT(*)>10

## 4. 添加分组、删选、排序 ##

### 案例：查询哪个部门的员工个数>5，并按员工个数进行降序 ###

SELECT department_name,COUNT(*)
    from departments d,employees e
    where d.department_id=e.department_id
    GROUP BY d.department_id
    HAVING COUNT(*)>5
    ORDER BY COUNT(*) DESC

## 5.三表查询 ##

### 案例：查询员工名、部门名和所在的城市 ###

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;

## SQL99语法内连接 ##

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

### sql92和sql99的区别 ###

sql99，使用join关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性

# （二）非等值连接 #

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

SELECT j.grade_level,count(*)
    from employees e
    join job_grades j
    on e.salary BETWEEN j.lowest_sal and j.highest_sal
    where e.department_id BETWEEN 10 and 90
    GROUP BY j.grade_level

# （三）自连接 #

### 案例：查询员工名和对应的领导名 ###

select e1.last_name as 员工,e2.last_name as 领导
    from employees e1
    join employees e2
    on e1.manager_id = e2.employee_id

# （四）外连接（sql99语法） #

## 应用场景 ##

用于查询一个表中有，另一个表没有的记录

## 特点 ##

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

## 语法 ##

select 查询列表  
from 表1 别名  
left | right outer join 表2 别名  
on 连接条件  
where 筛选条件

## 表结构 ##

数据：[girls.sql][]

### beauty表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]

### boys表 ###

![在这里插入图片描述][20191229232605767.png]

### 案例1： 查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null ###

#### 左连接： ####

SELECT b.*,bo.*
    from beauty b
    LEFT JOIN boys bo
    on b.boyfriend_id=bo.id

### 案例2： 哪个女神没有男朋友 ###

#### 左连接 ####

SELECT b.name
    from beauty b
    LEFT JOIN boys bo
    on b.boyfriend_id=bo.id
    WHERE bo.id is null

### 案例3：查询哪个部门没有员工，并显示其部门编号和部门名 ###

SELECT d.department_id,d.department_name
    from departments d
    LEFT JOIN employees e
    on d.department_id=e.department_id
    where  e.employee_id is null

[girls.sql]: https://blog.csdn.net/GongmissYan/article/details/102937816
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191229232550654.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg==,size_16,color_FFFFFF,t_70
[20191229232605767.png]: https://img-blog.csdnimg.cn/20191229232605767.png