Mysql 基础-多表查询

博主： Ruiven
发布时间：2017 年 07 月 18 日
114次浏览
暂无评论
5327字数
分类：基础数据库

多表关系

表结构之间存在联系基本上有以下三种

一对多
多对多
一对一

多表查询概述

概述：指从多张表中查询数据

连接查询

内连接

A 表和 B 表的交集

  隐式内连接
  SELECT 字段列表 FROM 表1，表2 WHERE 条件 ;
  显示内里
  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

内连接查询的是两张表交集部分

外连接

左外连接：查询左表所有数据，以及两张表交集部分

SELECT 字段表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

右外连接：查询右边所有数据，以及两张表的交集部分

SELECT 字段表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

自连接：当前表与自身的连接查询，自连接必须使用表别名

 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

例：

 CREATE TABLE emp(
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'id',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  age INT COMMENT '年龄',
  job VARCHAR(20) COMMENT '职位',
  salary INT COMMENT '薪资',
  entrydate DATE COMMENT '入职时间',
  managerid INT COMMENT '直属领导ID',
  dept_id INT COMMENT '部门ID'
) COMMENT '员工表';

INSERT INTO emp (name, age, job, salary, entrydate, managerid, dept_id) VALUES
  ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
  ('张无忌', 23, '开发', 10500, '2004-09-07', 1, 1),
  ('杨潇', 33, '项目经理', 12500, '2005-12-01', 2, 1),
  ('常遇春', 66, '开发', 8400, '2000-11-01', 2, 1),
  ('韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
  ('小昭', 19, '程序员鼓励师', 6600, '2004-10-01', 2, 1);

select a.name, b.name FROM emp a , emp b where a.managerid = b.id;

联合查询 union， union all

对于一个union 查询就是吧多次查询的结果合并起来，行程一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

子查询

允许把一个查询嵌套在另一个查询当中称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

根据子查询结果不通，分为

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询 (子查询结果为一列)
表子查询（子查询结果为多行多列）

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。

标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符： = , <> , > , >= , < , <=
例：

SELECT * FROM emp WHERE  dept_id = (SELECT id FROM dept WHERE name = '销售部');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符： IN, NOT IN , ANY , SOME , ALL

操作符	描述
IN	在指定的集合方位之内，多选1
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用 ANY
ALL	子查询返回列表的所有值都必须满足

例：

1 . 查询 “销售部” 和 市场部 的所有员工信息
    a. 查询销售部和市场部的部门ID
      select id from dept where name='销售部' or name = '市场部';
    b. 根据部门ID,查询员工信息
      select * from emp where dept_id  in (2, 4)
组合：
    select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

2. 查询比财务部所有人工资都高的员工信息
   a. 财务部所有人员工资
     SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部')
   b.所有大于财务工资
    SELECT * FROM emp WHERE salary > all();
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

3. 查询比研发部其中任意一人工资都搞的员工信息
      SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'))

行子查询

子查询返回的结果是一列（可以是多列），这种子查询称为行子查询。
常用的操作符： = ， <>, IN , NOT IN

例：

1.查询与张无忌的薪资及直属领导相同的员工信息；
-- a 查询张无忌的薪资及直属领导

SELECT salary, managerid FROM emp WHERE name='张无忌';

-- b 查询与张无忌的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE salary = '' and managerid = '';
// SELECT * FROM emp WHERE (salary, managerid) = ('', '');
结合后：
  SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary,managerid FROM emp WHERE name='张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询成为表子查询
常用操作符 IN

例：

1. 查询与 长颈鹿 与 韦一笑 的职位和薪资相同的员工信息

-- a 查询 长颈鹿 与 韦一笑 的职位 和薪资
    SELECT job, salary FROM emp WHERE name='长颈鹿' OR name='韦一笑';
-- b 与 长颈鹿 与 韦一笑 的职位和薪资相同的员工信息
    SELECT * FROM emp WHERE (job, salary) in (SELECT job, salary FROM emp WHERE name='长颈鹿' OR name='韦一笑')

2. 查询 入职日期是 2006-01-01 之后的员工信息， 及其部门信息
   -- a 入职时间是 2006-01-01 之后的员工
    SELECT * FROM emp WHERE entrydate > '2006-01-01'
   -- b 查询这部分员工对应的部门信息
    SELECT e.*,d.name FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON  e.dept_id = d.id;

多表查询案例


emp 员工表  dept 部门表 salgrade 薪资等级表

-- 1.查询员工姓名，年龄 职位 部门信息  emp 员工表  dept 部门表

SELECT e.name, e.job, d.name as deptName FROM emp e, dept d Where e.dept_id = d.id;

SELECT e.name, e.job, d.name as deptName FROM emp e LEFT JOIN dept d ON e.dept_id = d.id

-- 2.查询年龄小于30岁的员工姓名，年龄，职位，部门信息

SELECT e.name, e.age, e.job,d.name as deptName  FROM emp e INNER JOIN dept d ON e.dept_id = d.id  WHERE age < 30

SELECT e.name, e.age, e.job,d.name as deptName  FROM emp e LEFT JOIN dept d ON e.dept_id = d.id  WHERE age < 30

-- 3.查询拥有员工的部门Id,部门名称
SELECT distinct d.id, d.name from emp e , dept d where e.dept_id = d.id;

-- 4.查询所有员工的工资等级
-- 表 emp , salgrade 
-- 连接条件： emp.salary > = salgrade.losal and emp.salary <= salgrade.hisal
  SELECT e.name, e.salary, s.grade FROM emp e, salgrade s WHERE e.salary >= s.losal and e.salary <= s.hisal;
 SELECT e.name, e.salary, s.grade FROM emp e, salgrade s WHERE e.salary between s.losal and e.salary and s.hisal;

-- 5. 查询研发部所有员工的信息及工资等级
-- 表 emp, salgrade, dept
-- 连接条件 ： emp.salgrade between salgrade.losal and salgrade.hisal, emp.dept_id =dept.id
-- 查询条件 ： dept.name = '研发部'
   SELECT
      e.*, s.grade, d.name as deptName
   FROM
      emp e, dept d , salgrade s
   WHERE
      e.dept_id = d.id
   AND
      (e.salary BETWEEN s.losal AND s.hisal)
   AND
      d.name='研发部';

-- 6.查询研发部员工的平均工资
-- 表 emp dept
-- 连接条件  emp.dept_id = dept.id

SELECT avg(e.salary) FROM emp e, dept d WHERE e.dept_id = d.id AND d.name = '研发部';

-- 7.查询 比 陈主管 薪资高的员工信息

SELECT salary FROM emp WHERE name = '陈主管';

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '陈主管');

-- 8.查询比平均工资高的员工信息

-- 查询平均工资
  SELECT avg(salary) FROM emp;

-- 大于平均工资的人
  SELECT * FROM emp WHERE salary > (SELECT avg(salary) FROM emp);

-- 9.查询低于本部门平均工资的员工信息

-- a部门的平均工资
   SELECT avg(salary) FROM emp WHERE dept_id = 1;

-- b低于平均工资的人
SELECT *, ( SELECT avg(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id) as '平均薪资' FROM emp e2 WHERE e2.salary < ( SELECT avg(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id)

-- 10 查询所有的部门信息 并统计部门的员工人数
-- a 查询所有的部门信息
  SELECT * FROM dept;

-- b 统计部门的员工人数
    SELECT count(*) FROM emp WHERE dept_id = '1'; -- 2 ， 3， 4 这里的1 对应的都是 detp 的 id
-- 合并
   SELECT d.id, d.name, (SELECT count(*) FROM emp e WHERE e.dept_id = d.id ) as '人数' FROM dept d;

小结

多表关系

一对多：在多的一方设置外键，关联的一方的主键
多对多：建立中间表，中间表包含两个外键，关联两种表的主键
一对一：用于表结构拆分，在其中任何一方设置外键（UNIQUE）关联另一方的主键

多表查询

 内连接
     隐式： SELECT ... FROM 表 A, 表 B WHERE ...
     显示： SELECT ... FROM 表 A INNER JOIN  表 B ON 条件 ...
 外连接
     左外： SELECT ... FROM 表A LEFT JOIN 表B ON 条件
     右外： SELECT ... FROM 表A RIGHT JOIN 表B ON  条件
 自连接
     SELECT ... FROM 表A 别名1，表A 别名2 WHERE 条件 ...
 子查询
     标量子查询， 列子查询，行子查询， 表子查询

最后修改：2022 年 04 月 04 日 11 : 39 AM

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Mysql 基础-多表查询

Ruiven • 2017 年 07 月 18 日

多表关系

表结构之间存在联系基本上有以下三种

一对多
多对多
一对一

多表查询概述

概述：指从多张表中查询数据

连接查询

内连接

A 表和 B 表的交集

  隐式内连接
  SELECT 字段列表 FROM 表1，表2 WHERE 条件 ;
  显示内里
  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;

内连接查询的是两张表交集部分

外连接

左外连接：查询左表所有数据，以及两张表交集部分

SELECT 字段表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

右外连接：查询右边所有数据，以及两张表的交集部分

SELECT 字段表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

自连接：当前表与自身的连接查询，自连接必须使用表别名

 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

例：

 CREATE TABLE emp(
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'id',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  age INT COMMENT '年龄',
  job VARCHAR(20) COMMENT '职位',
  salary INT COMMENT '薪资',
  entrydate DATE COMMENT '入职时间',
  managerid INT COMMENT '直属领导ID',
  dept_id INT COMMENT '部门ID'
) COMMENT '员工表';

INSERT INTO emp (name, age, job, salary, entrydate, managerid, dept_id) VALUES
  ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
  ('张无忌', 23, '开发', 10500, '2004-09-07', 1, 1),
  ('杨潇', 33, '项目经理', 12500, '2005-12-01', 2, 1),
  ('常遇春', 66, '开发', 8400, '2000-11-01', 2, 1),
  ('韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
  ('小昭', 19, '程序员鼓励师', 6600, '2004-10-01', 2, 1);

select a.name, b.name FROM emp a , emp b where a.managerid = b.id;

联合查询 union， union all

对于一个union 查询就是吧多次查询的结果合并起来，行程一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

子查询

允许把一个查询嵌套在另一个查询当中称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

根据子查询结果不通，分为

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询 (子查询结果为一列)
表子查询（子查询结果为多行多列）

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。

标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符： = , <> , > , >= , < , <=
例：

SELECT * FROM emp WHERE  dept_id = (SELECT id FROM dept WHERE name = '销售部');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符： IN, NOT IN , ANY , SOME , ALL

操作符	描述
IN	在指定的集合方位之内，多选1
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用 ANY
ALL	子查询返回列表的所有值都必须满足

例：

1 . 查询 “销售部” 和 市场部 的所有员工信息
    a. 查询销售部和市场部的部门ID
      select id from dept where name='销售部' or name = '市场部';
    b. 根据部门ID,查询员工信息
      select * from emp where dept_id  in (2, 4)
组合：
    select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

2. 查询比财务部所有人工资都高的员工信息
   a. 财务部所有人员工资
     SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部')
   b.所有大于财务工资
    SELECT * FROM emp WHERE salary > all();
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '财务部'));

3. 查询比研发部其中任意一人工资都搞的员工信息
      SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'))

行子查询

子查询返回的结果是一列（可以是多列），这种子查询称为行子查询。
常用的操作符： = ， <>, IN , NOT IN

例：

1.查询与张无忌的薪资及直属领导相同的员工信息；
-- a 查询张无忌的薪资及直属领导

SELECT salary, managerid FROM emp WHERE name='张无忌';

-- b 查询与张无忌的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE salary = '' and managerid = '';
// SELECT * FROM emp WHERE (salary, managerid) = ('', '');
结合后：
  SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary,managerid FROM emp WHERE name='张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询成为表子查询
常用操作符 IN

例：

1. 查询与 长颈鹿 与 韦一笑 的职位和薪资相同的员工信息

-- a 查询 长颈鹿 与 韦一笑 的职位 和薪资
    SELECT job, salary FROM emp WHERE name='长颈鹿' OR name='韦一笑';
-- b 与 长颈鹿 与 韦一笑 的职位和薪资相同的员工信息
    SELECT * FROM emp WHERE (job, salary) in (SELECT job, salary FROM emp WHERE name='长颈鹿' OR name='韦一笑')

2. 查询 入职日期是 2006-01-01 之后的员工信息， 及其部门信息
   -- a 入职时间是 2006-01-01 之后的员工
    SELECT * FROM emp WHERE entrydate > '2006-01-01'
   -- b 查询这部分员工对应的部门信息
    SELECT e.*,d.name FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON  e.dept_id = d.id;

多表查询案例


emp 员工表  dept 部门表 salgrade 薪资等级表

-- 1.查询员工姓名，年龄 职位 部门信息  emp 员工表  dept 部门表

SELECT e.name, e.job, d.name as deptName FROM emp e, dept d Where e.dept_id = d.id;

SELECT e.name, e.job, d.name as deptName FROM emp e LEFT JOIN dept d ON e.dept_id = d.id

-- 2.查询年龄小于30岁的员工姓名，年龄，职位，部门信息

SELECT e.name, e.age, e.job,d.name as deptName  FROM emp e INNER JOIN dept d ON e.dept_id = d.id  WHERE age < 30

SELECT e.name, e.age, e.job,d.name as deptName  FROM emp e LEFT JOIN dept d ON e.dept_id = d.id  WHERE age < 30

-- 3.查询拥有员工的部门Id,部门名称
SELECT distinct d.id, d.name from emp e , dept d where e.dept_id = d.id;

-- 4.查询所有员工的工资等级
-- 表 emp , salgrade 
-- 连接条件： emp.salary > = salgrade.losal and emp.salary <= salgrade.hisal
  SELECT e.name, e.salary, s.grade FROM emp e, salgrade s WHERE e.salary >= s.losal and e.salary <= s.hisal;
 SELECT e.name, e.salary, s.grade FROM emp e, salgrade s WHERE e.salary between s.losal and e.salary and s.hisal;

-- 5. 查询研发部所有员工的信息及工资等级
-- 表 emp, salgrade, dept
-- 连接条件 ： emp.salgrade between salgrade.losal and salgrade.hisal, emp.dept_id =dept.id
-- 查询条件 ： dept.name = '研发部'
   SELECT
      e.*, s.grade, d.name as deptName
   FROM
      emp e, dept d , salgrade s
   WHERE
      e.dept_id = d.id
   AND
      (e.salary BETWEEN s.losal AND s.hisal)
   AND
      d.name='研发部';

-- 6.查询研发部员工的平均工资
-- 表 emp dept
-- 连接条件  emp.dept_id = dept.id

SELECT avg(e.salary) FROM emp e, dept d WHERE e.dept_id = d.id AND d.name = '研发部';

-- 7.查询 比 陈主管 薪资高的员工信息

SELECT salary FROM emp WHERE name = '陈主管';

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '陈主管');

-- 8.查询比平均工资高的员工信息

-- 查询平均工资
  SELECT avg(salary) FROM emp;

-- 大于平均工资的人
  SELECT * FROM emp WHERE salary > (SELECT avg(salary) FROM emp);

-- 9.查询低于本部门平均工资的员工信息

-- a部门的平均工资
   SELECT avg(salary) FROM emp WHERE dept_id = 1;

-- b低于平均工资的人
SELECT *, ( SELECT avg(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id) as '平均薪资' FROM emp e2 WHERE e2.salary < ( SELECT avg(e1.salary) FROM emp e1 WHERE e1.dept_id = e2.dept_id)

-- 10 查询所有的部门信息 并统计部门的员工人数
-- a 查询所有的部门信息
  SELECT * FROM dept;

-- b 统计部门的员工人数
    SELECT count(*) FROM emp WHERE dept_id = '1'; -- 2 ， 3， 4 这里的1 对应的都是 detp 的 id
-- 合并
   SELECT d.id, d.name, (SELECT count(*) FROM emp e WHERE e.dept_id = d.id ) as '人数' FROM dept d;

小结

多表关系

一对多：在多的一方设置外键，关联的一方的主键
多对多：建立中间表，中间表包含两个外键，关联两种表的主键
一对一：用于表结构拆分，在其中任何一方设置外键（UNIQUE）关联另一方的主键

多表查询

 内连接
     隐式： SELECT ... FROM 表 A, 表 B WHERE ...
     显示： SELECT ... FROM 表 A INNER JOIN  表 B ON 条件 ...
 外连接
     左外： SELECT ... FROM 表A LEFT JOIN 表B ON 条件
     右外： SELECT ... FROM 表A RIGHT JOIN 表B ON  条件
 自连接
     SELECT ... FROM 表A 别名1，表A 别名2 WHERE 条件 ...
 子查询
     标量子查询， 列子查询，行子查询， 表子查询

Mysql 基础-多表查询

多表关系

多表查询概述

连接查询

联合查询 union， union all

子查询

根据子查询结果不通，分为

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。

标量子查询

列子查询

行子查询

表子查询

多表查询案例

小结

发表评论取消回复

Mysql 5.7安装实录

新玩意，初入Redis之数据结构

Nginx 安装实录(源码安装)

经典排序-冒泡排序

经典排序-选择排序

经典排序-插入排序

PHP面试宝典1

如何固定Ubuntu网卡IP地址?

新玩意，初入Redis之数据结构

Mysql 基础查询-函数

Mysql 基础-多表查询

多表关系

多表查询概述

连接查询

联合查询 union， union all

子查询

根据子查询结果不通，分为

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。

标量子查询

列子查询

行子查询

表子查询

多表查询案例

小结

多表关系

多表查询概述

连接查询

联合查询 union， union all

子查询

根据子查询结果不通，分为

根据子查询位置，分为 WHERE 之后 ， FROM 之后， SELECT 之后。

标量子查询

列子查询

行子查询

表子查询

多表查询案例

小结

发表评论 取消回复

Mysql 基础-多表查询

多表关系

多表查询概述

连接查询

联合查询 union， union all

子查询

根据子查询结果不通，分为

根据子查询位置，分为 WHERE 之后 ， FROM 之后， SELECT 之后。

标量子查询

列子查询

行子查询

表子查询

多表查询案例

小结

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。

发表评论取消回复

根据子查询位置，分为 WHERE 之后， FROM 之后， SELECT 之后。