Mysql多表设计

前言

多表查询中要给每一表起别名

• tableA as 别名1 , tableB as 别名2 ; （111111推荐）

• tableA 别名1 , tableB 别名2 ;

例子：

select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;

一对一

在任意一方加入对方表的主键

（一对多的一中）

一对多

连接查询

• 在(t1)多的一方加入 一(t2)的主键 

  例：select * from t1,t2 where t1.t2_id=t2.id;

• 内连接 查询A,B交集部分数据

  • 隐式内连接

    • select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
      from tb_emp , tb_dept -- 关联两张表
      where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积
      

  • 显示内连接

    • select tb_emp.name , tb_dept.name
      from tb_emp inner join tb_dept
      on tb_emp.dept_id = tb_dept.id;
      

• 外连接（左右连接可以切换）

  • 左外连接（推荐）

    • 左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。即使表2中没有与之对应的数据该数据也会被查询出来
    • select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

    • -- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，
      以及和主表匹配的右边表中的数据
      select emp.name , dept.name
      from tb_emp AS emp left join tb_dept AS dept 
           on emp.dept_id = dept.id;
      

  • 右外连接

    • select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

• 子查询

  • SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

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

    • 标量子查询

      • 子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

      • 常用的操作符： = <> > >= < <

      • ​
        -- 1.查询"教研部"部门ID
        select id from tb_dept where name = '教研部';    #查询结果：2
        -- 2.根据"教研部"部门ID, 查询员工信息
        select * from tb_emp where dept_id = 2;
         
        -- 合并出上两条SQL语句
        select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
         
        ​
        

    • 列子查询

      • （子查询结果为一列，但可以是多行）

      • 操作符	描述
        IN	在指定的集合范围之内，多选一
        NOT IN	不在指定的集合范围之内

      • -- 1.查询"销售部"和"市场部"的部门ID
        select id from tb_dept where name = '教研部' or name = '咨询部';    #查询结果：3,2
        -- 2.根据部门ID, 查询员工信息
        select * from tb_emp where dept_id in (3,2);
         
        -- 合并以上两条SQL语句
        select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
        

    • 行子查询（子查询结果为一行，但可以是多列）

    • 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

      • -- 1.查询"方东白"的入职日期
        select entrydate from tb_emp where name = '方东白';     #查询结果：2012-11-01
        -- 2.查询指定入职日期之后入职的员工信息
        select * from tb_emp where entrydate > '2012-11-01';
         
        -- 合并以上两条SQL语句
        select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
        

  • 子查询可以书写的位置

    • where之后

    • from之后

    • select之后