SQL连接查询案例

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

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

1.简单的两表连接

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

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

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

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

2.添加筛选条件

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

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

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

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

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

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

3. 添加分组+筛选

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

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

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

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

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

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

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

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

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

5.三表查询

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

  1. SELECT last_name,department_name,city
  2. FROM employees e,departments d,locations l
  3. WHERE e.`department_id`=d.`department_id`
  4. AND d.`location_id`=l.`location_id`
  5. AND city LIKE 's%'
  6. 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之间的员工的工资级别，并按级别进行分组

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

（三）自连接

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

  1. select e1.last_name as 员工,e2.last_name as 领导
  2. from employees e1
  3. join employees e2
  4. 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表

在这里插入图片描述

boys表

在这里插入图片描述

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

左连接：

  1. SELECT b.*,bo.*
  2. from beauty b
  3. LEFT JOIN boys bo
  4. on b.boyfriend_id=bo.id

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

左连接

  1. SELECT b.name
  2. from beauty b
  3. LEFT JOIN boys bo
  4. on b.boyfriend_id=bo.id
  5. WHERE bo.id is null

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

  1. SELECT d.department_id,d.department_name
  2. from departments d
  3. LEFT JOIN employees e
  4. on d.department_id=e.department_id
  5. where  e.employee_id is null

发表评论

表情：
评论列表 （有 0 条评论，100人围观）

还没有评论，来说两句吧...

相关阅读

    相关 sql连接查询

    按功能分类： > 内连接： > > 等值连接、非等值连接、自连接 > > 外连接： > > 左外链接、左链接 > > 右外链接、右链接 > > 完全外链接 >

    柔情只为你懂柔情只为你懂/ 0 赞/ 137 阅读

    相关 sql的子连接查询

            一直没有注重于sql这一块，现在的项目有大量的统计，报表，以前都是多表连接或者用mybatis的延迟加载之类的，这几天心血来潮自己查了一下，发现了sql子查询这

    以你之姓@以你之姓@/ 0 赞/ 337 阅读