三，多表查询

normaling
133 阅读6分钟

一，多表之间的关系

表之间存在如下关系：

  • 一对多(多对一)

    多个员工都属于一个部门的关系

    实现方式：

    • 在员工表里面加上一个部门表的唯一建字段用来关联

  • 多对多

    学生与课程表之间的关系

    实现方式：

    • 在学生表加上课程表的唯一键关联，并且课程表加上学生表的唯一键关联，创建一个中间表记录两者唯一键。

  • 一对一

    用户和用户详细信息的关系

    实现方式：

    • 在用户表或用户详细表里面任意一方加上对方的唯一键关联即可。

二，多表查询介绍

2.1 笛卡儿积

select * from 表1,表2

这种就叫多表查询，但是这样查询到的 内容行数=表1的行数*表二的行数，这种现象叫笛卡尔积

笛卡尔积：俩个集合A和集合B的所有组合的情况（在多表查询时需要消除无效笛卡尔积）

在多表查询中一般查询其中一个表中字段写法为：表1.字段1

2.2 多表查询分类

  1. 内连接查询：查询表A和表B之间交集部分
  2. 外连接查询
    • 左外连接查询：查询左表和左边与右边之间交集部分
    • 右外连接查询：查询右表和右边与左表之间交集部分
  3. 自连接查询：当前只有一张表，但是我们会对这种表自身进行连接查询
  4. 子查询查询：在sql语句中嵌套select语句叫子查询

三，内连接查询

3.1 定义

内连接查询就是查询俩张表之间的交集部分 image

3.2 隐式内连接查询

语法

select 字段列表 from 表A,表B where 连接条件;

举例：

select t1.name,t2.age from t1,t2 where t1.id=t2.id;

3.3 显式内连接查询

语法

select 字段列表 from 表A [inner] join 表B on  连接条件;

举例：

select t1.name,t2.age from t1 join t2 on t1.id=t2.id;

四，外连接查询

4.1 定义

查询表A和表B交集部分+表A部分/表B部分

4.2 左外连接查询

查询表A和表B交集部分+表A部分 image

语法

select 字段列表 from 表A left [outer] join 表B on 连接条件;

4.3 右外连接查询

查询表A和表B交集部分+表B部分
image

语法

select 字段列表 from 表A right [outer] join 表B on 连接条件;

五，自连接查询

自己查询自己的内容，通常要用到别名。比如说查询表内年龄一致的人的信息 语法

Select 字段列表 from 表A [as] 别名a join 表A [as] 别名b on 条件….;

六，联合查询

将多次查询的结果联合起来就叫联合查询 语法

Select 字段列表 from 表A [where]….
Union [all]
Select 字段列表 from 表B [where]….;
  • Union all是联合查询是将结果直接合并！
  • Union是联合查询结果去掉重复的再合并
  • 每次查询返回的列数必须一致，字段类型也必须一致

七，子查询

7.1 定义

在SQL中嵌套select的叫子查询

7.2 语法

Select * from t1 where column1=(select column1 from t2)

外部的select可以为update、insert、delete、select、任意一种

7.3 分类

根据子查询结果不同分为：

  1. 标量子查询(子查询的结果为单个值)
  2. 列子查询(子查询结果为一列)
  3. 行子查询(子查询结果为一行)
  4. 表子查询(子查询结果为多行多列)

即：值，列，行，表四种

根据子查询的位置分为

  1. Where之后
  2. From之后
  3. Select之后

即，作为条件，作为表，作为字段三种

7.4 标量子查询(值)

常用符号

符号描述
=等于
大于
<小于
>=大于等于
<=小于等于
!=或<>不等于

用法举例 假定存在俩张表

  • 一张表为员工信息表
  • 一张表为部门信息表 imageimage

其中员工信息表中的dep字段关联着部门信息的id用来代表员工所属哪个字段.

  • 问：查询研发部所属员工全部信息 问题分解:

    1. 查询出研发部的部门编号(id)

      select id from dep where dept_name='研发部';

    2. 查询出dep=研发部编号的员工信息

      select * from dep where dep=(select id from dep where dept_name='研发部');

7.5 列子查询

常用操作符

IN

  • 用法举例: 字段 > in(a,b,c)
  • 相当于 字段>(a or b or c)，即只要字段大于a,b,c中任意一个即可

NOT IN

  • 用法举例: 字段= not in(a,b,c)
  • 相当于 字段 <> (a or b or c)，即只要字段不等于a,b,c中任意一个才通过

ANY

  • 用法举例: 字段 > any(select语句)
  • 假定select语句返回a,b,c
  • 那么相当于 字段 > (a or b or c)，即只要字段大于a,b,c任意一个才通过

SOME，是ANY的别名

ALL

  • 用法举例: 字段 = all(select语句)
  • 假定select语句返回a,b,c
  • 那么相当于 字段=(a and b and c)，即只要字段等于a,b,c三个全部才通过

用法举例 假定存在俩张表

  • 一张表为员工信息表,

  • 一张表为部门信息表 如图 imageimage

  • 问：查询研发部和后勤部的所有员工信息

    1. 查询研发部和后勤部部门编号

      select id from dept where part='研发部' || part ='后期部';

    2. 查询部门id满足研发部或者后勤部的员工的信息

      select * from emps where dept in (4,6);

    3. 合并

      select * from emps where dept in (select id from dept where part='研发部' || part ='后期部');

7.6 行子查询

常用符号

符号描述
=等于
<>不等于
IN在这这内
Not in不在这之内

特殊写法:

select * from 表 where (id,gender)=(1,'男');

用法举例： 假定存在俩张表

  • 一张表为员工信息表,

  • 一张表为部门信息表 如图 imageimage

  • 问 ：查询和张三一样年纪和部门的人的信息

    1. 查询张三的年纪和部门

      select age,dep from employee where name='张三';

    2. 查询符合条件的信息

      select * from employee where (age,dep)=(select age,dep from employee where name='张三');

7.7 表子查询

常用操作符

  • IN

用法举例 假定存在俩张表

  • 一张表为员工信息表,

  • 一张表为部门信息表 如图 imageimage

  • 问：查询 张三和李四的年龄和部门一样的员工信息

    1. 查询张三和李四的年龄+部门

      select age,dep from employee where name='张三' or name='李四';

    2. 查询符合条件的

      Select * from employee where (age,dep) in (select age,dep from employee where name='张三' or name='李四');
avatar
文章
阅读
粉丝