Kristal667

导航

MySQL数据库(四)多表查询

两张假设有两张表格A和B,把表格当作一个集合,那么表格中的记录就是集合中的一个元素。

两张表格如下:

TableA:TableB:

2.1 内连接(只有一种场景)

inner join 或者join(等同于inner join)

 

  1.  
    select a.*, b.* from tablea a
  2.  
    inner join tableb b
  3.  
    on a.id = b.id

 

 

  1.  
    select a.*, b.* from tablea a
  2.  
    join tableb b
  3.  
    on a.id = b.id

 

结果如下:

应用场景:

这种场景下得到的是满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。

2.2 外连接(六种场景)

2.2.1 left join 或者left outer join(等同于left join)

  1.  
    select a.*, b.* from tablea a
  2.  
    left join tableb b
  3.  
    on a.id = b.id

 

或者

 

  1.  
    select a.*, b.* from tablea a
  2.  
    left outer join tableb b
  3.  
    on a.id = b.id

结果如下,TableB中更不存在的记录填充Null:

应用场景:


这种场景下得到的是A的所有数据,和满足某一条件的B的数据;

2.2.2  [left   join 或者left outer join(等同于left join)]  +  [where B.column is null]

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    left join tableb b
  3.  
    on a.id = b.id
  4.  
    Where b.id is null

结果如下:


应用场景:

这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据",然后得到的A剩余数据;

2.2.3  right join 或者fight outer join(等同于right join)

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    right join tableb b
  3.  
    on a.id = b.id

结果如下,TableB中更不存在的记录填充Null:

 

应用场景:

这种场景下得到的是B的所有数据,和满足某一条件的A的数据;

2.2.4 [left   join 或者left outer join(等同于left join)]  +  [where A.column is null]

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    right join tableb b
  3.  
    on a.id = b.id
  4.  
    where a.id is null

结果如下:

 


应用场景:

这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“,然后得到的B剩余数据;

2.2.5 full join (mysql不支持,但是可以用 left join  union right join代替)

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    left join tableb b
  3.  
    on a.id = b.id
  4.  
    union
  5.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  6.  
    right join tableb b
  7.  
    on a.id = b.id

union过后,重复的记录会合并(id为2,3,4的三条记录),所以结果如下:

 

应用场景:

 

这种场景下得到的是满足某一条件的公共记录,和独有的记录

2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+isnull代替)

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    left join tableb b
  3.  
    on a.id = b.id
  4.  
    where b.id is null
  5.  
    union
  6.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  7.  
    right join tableb b
  8.  
    on a.id = b.id
  9.  
    where a.id is null

结果如下:

 

应用场景:

这种场景下得到的是A,B中不满足某一条件的记录之和

注:上面共有其中七(2^3-1)种应用场景,还有一种是全空白,那就是什么都不查,七种情形包含了实际应用所有可能的场景

2.3 交叉连接 (cross join)

2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join:

 

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    cross join tableb b

 

2.3.2 还可以为cross  join指定条件 (where):

  1.  
    select a.id aid,a.age,b.id bid,b.name from tablea a
  2.  
    cross join tableb b
  3.  
    where a.id = b.id

结果如下;

注:这种情况下实际上实现了内连接的效果

三 注意事项

上面仍然存在遗漏,那就是mysql对sql语句的容错问题,即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能地解释它:

3.1 一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;

3.2 一般内连接都需要加上on限定条件,如上面场景2.1;如果不加会被解释为交叉连接;

3.3 如果连接表格使用的是逗号,会被解释为交叉连接;

注:sql标准中还有union join和natural  inner join,mysql不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到

 

外键约束:

创建新闻分类表:news_cate

CREATE TABLE news_cate(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

cate_name  VARCHAR(50) NOT NULL UNIQUE,

cate_desc VARCHAR(100)  NOT NULL DEFAULT ' '

);

创建新闻表:news

CREATE TABLE news(

id INT UNSIGNED AUTO_INCREMENT KEY,

title VARCHAR(100) NOT NULL UNIQUE,

content VARCHAR(100) NOT NULL,

cate_id TINYINT UNSIGNED NOT NULL

);  

创建成功以后,我们插入一些记录.

INSERT INTO news_cate(cate_name) values('chineses news'),('glocal news'),('sports news'),('entertainment news');

INSERT INTO news(title,content,cate_id) VALUES('a1','aaa1',1),('a2','aaa2',2),('a3','aaa3',3),('a4','aaa4',4),('a5','aaa5',15);   

查询news表中的id title content,news_cate表中的cate_name   

(用到我们刚刚学到的多表联查)

SELECT n.id,n.title,n.content,c.cate_name from news AS n

JOIN news_cate AS c

ON n.cate_id = c.id;

这时候news_cate新闻分类中,去掉了国际新闻

DELETE FROM news_cate where id = 2;

成功删除了,但是我们再看看新闻表news,表中的cate_id还有2,显然这是不合适的

INSERT INTO news(title,content,cate_id) VALUES('a6','aaa6',45);

这条数据也插进去了,这两个记录的cate_id,都不在新闻分类里面,这就是所谓的脏数据,此时我们就要用外键,来保持数据的一致性和完整性.

创建外键的两种方法:

  1 建表时指定外键盘:FOREIGN KEY(字段名) REFERENCES 主表(字段名)

    注意:子表关联的必须是父表的主键

  2动态添加外键

    ALTER TABLE 表名 drop foreign key 外键名

      ALTER TABLE 表名 ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);

    **注意,动态添加外键时,不能有脏数据,否则不能成功添加外键

所以为了自动去掉脏数据,我们把新闻表news删掉,重新建有外键的news表

CREATE TABLE news(

id INT UNSIGNED AUTO_INCREMENT KEY,

title VARCHAR(100) NOT NULL UNIQUE,

content VARCHAR(100) NOT NULL,

cate_id TINYINT UNSIGNED NOT NULL,

FOREIGN KEY(cate_id) REFERENCES news_cate(id)

);  

重新插入一下数据,现在非法记录就不可以插入了,这就是有外键和无外键的区别和约束

INSERT INTO news(title,content,cate_id) VALUES('a1','aaa1',1),('a2','aaa2',1),('a3','aaa3',3),('a4','aaa4',4),('a5','aaa5',3);

 

 

 

 

 

 

 

参考连接:https://blog.csdn.net/jintao_ma/article/details/51260458

posted on 2018-07-10 02:42  我的后花园  阅读(19481)  评论(0编辑  收藏  举报