db - MySQL - MySQL表连接

世界上并没有完美的程序，但是我们并不因此而沮丧，因为写程序就是一个不断追求完美的过程。

• MySQL表连接
• • 表之间的关系
  • 表连接
  • 子查询
  • 多个表的查询

• 关系
  • 一对一
    • 1 : 1
  • 一对多
    • 1 : n
  • 多对多
    • m : n
• 使用
  • 1 : 1

    create table `t_1_1` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    create table `t_1_2` (
        `id` int primary key,
        `utext` text,
        foreign key (`id`) references `t_1_1` (`id`)
    ) ENGINE=InnoDB;
    

  • 1 : n

    create table `t_2_1` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    create table `t_2_2` (
        `id` int primary key,
        `name` varchar(255) unique not null,
        `t_2_1_id` int not null,
        foreign key (`t_2_1_id`) references `t_2_1` (`id`)
    ) ENGINE=InnoDB;
    

  • m : n

    create table `t_3_1` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    create table `t_3_2` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    create table `t_3_3` (
        `t_3_1_id` int,
        `t_3_2_id` int,
        primary key (`t_3_1_id`, `t_3_2_id`),
        foreign key (`t_3_1_id`) references `t_3_1` (`id`),
        foreign key (`t_3_2_id`) references `t_3_2` (`id`)
    ) ENGINE=InnoDB;
    

• 合并结果集
  • 关键字
    • union
      • 返回的行的数据不重复
    • union all
      • 返回的行的数据可以重复
    • 注意 ：两个查询语句返回的列数必须相同并且对应
  • 语法
    • union
      • select * from union select * from
    • union all
      • select * from union all select * from
  • 使用

    create table `t_4_1` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    create table `t_4_2` (
        `id` int primary key,
        `name` varchar(255) unique not null
    ) ENGINE=InnoDB;
    
    insert into `t_4_1` (`id`, `name`) values (1, 'hello'), (2, 'good'), (3, 'me');
    
    insert into `t_4_2` (`id`, `name`) values (2, 'good'), (4, 'kkkk'), (5, 'tttt');
    

    • union

      select * from `t_4_1` union select * from `t_4_2`;
      

    • union all

      select * from `t_4_1` union all select * from `t_4_2`;
      

• 笛卡尔积
  • 关键字
    • cross join
  • 语法
    • select * from cross join
  • 使用

    select * from `t_4_1` cross join `t_4_2`;
    

• 内连接
  • 关键字
    • [inner] join
  • 语法
    • select * from join on col_1 = col_2;
  • 使用

    select t1.id id, t1.name name1, t2.name name2 from `t_4_1` t1 join `t_4_2` t2 on t1.id = t2.id;
    

• 外连接
  • 关键字
    • left [outer] join
      • 匹配数据及左表所有数据
    • right [outer] join
      • 匹配数据及右表所有数据
  • 语法
    • select * from left join on col_1 = col_2;
    • select * from right join on col_1 = col_2;
  • 使用

    select t1.id id, t1.name name1, t2.name name2 from `t_4_1` t1 left join `t_4_2` t2 on t1.id = t2.id;
    
    select t1.id id, t1.name name1, t2.name name2 from `t_4_1` t1 right join `t_4_2` t2 on t1.id = t2.id;
    

• 自然连接
  • 关键字
    • natural join
  • 语法
    • select * from natural join ;
    • select * from natural left join ;
    • select * from natural right join ;
  • 使用

    select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from `t_4_1` t1 natural join `t_4_2` t2;
    
    select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from `t_4_1` t1 natural left join `t_4_2` t2;
    
    select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from `t_4_1` t1 natural right join `t_4_2` t2;
    

• 多表连接
  • 语法
    • select * from t1 join t2 on t1.col1 = t2.col2 join t3 on t3.col3 = t2.col3;

• 语法
  • select * from t1 where t1.col in (select col from t2 where t2.col=…);
  • select * from t1, (select col from where col=…) t2;
• 使用

  select t1.id id1, t1.name name1 from `t_4_1` t1 where id in (select id from `t_4_2` where name = 'good');
  
  select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from `t_4_1` t1, (select id, name from `t_4_2` where id=2) t2;
  

• 语法
  • select * from t1, t2;
• 使用

  select t1.id, t1.name, t2.id, t2.name from `t_4_1` t1, `t_4_2` t2 where t1.id = t2.id;