神奇的 SQL 之团结的力量 → JOIN

  • 时间:
  • 浏览:0
  • 来源:大发uu快3_uu快3娱乐_大发uu快3娱乐

前言

  开心一刻

    闺蜜家暴富,买了一栋大别野,喊我去吃饭,菜挺丰盛的,一次性筷子什么都有有不给力,银一次性筷子,好重,我知道你换个竹子的,闺蜜说,你你是什么银一次性筷子家里总共才五双,可太久再能要能 贵宾要能用~我咬着牙享受着贵宾待遇,终于,在第三次夹虾排滑落盘子时,我爆发了:去它喵的贵宾,我想要要虾排……不会……我想要要竹一次性筷子!

连接

  简单来说,什么都有有 将什么都有有表中的列换成过来,进行"换成列"的运算,如下图所示。

  为哪些地方须要进行"换成列"的操作 了? 肯能一群人在设计数据库的事先,往往须要满足范式(具体满足范式几,无法一概而论,这里不做细究),会意味一群人某个需求的删改列分散在不同的表中,什么都有有为了满足需求,一群人须要将什么都有有表的列进行连接。一群人来看个简单例子,假使 一群人有两张表(t_user,t_login_log):

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(30) NOT NULL COMMENT '用户名',
  sex TINYINT(1) NOT NULL COMMENT '性别, 1:男,0:女',
  age TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄',
  phone_number VARCHAR(11) NOT NULL DEFAULT '' COMMENT '电话号码',
  email VARCHAR(30) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (id)
) COMMENT='用户表';

DROP TABLE IF EXISTS t_login_log;
CREATE TABLE t_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(30) NOT NULL COMMENT '用户名',
  ip VARCHAR(15) NOT NULL COMMENT '登录IP',
  client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) COMMENT='登录日志';

INSERT INTO t_user(user_name, sex, age, phone_number,email,create_time,update_time) VALUES
('Bruce Lee', 1, 32, '15174430987', 'brucelee@126.com', NOW(), NOW()),
('Jackie Chan', 1, 65, '15174481234', 'JackieChan@126.com', NOW(), NOW()),
('Jet Li', 1, 56, '15174481245', 'JetLi@126.com', NOW(), NOW()),
('Jack Ma', 1, 55, '15174481256', 'JackMa@126.com', NOW(), NOW()),
('Pony', 1, 48, '15174481278', 'Pony@126.com', NOW(), NOW()),
('Robin Li', 1, 51, '15174481290', 'RobinLi@126.com', NOW(), NOW());

INSERT INTO t_login_log(user_name, ip, client, create_time) VALUES
('Jackie Chan', '10.53.56.78',2, '2019-10-12 12:23:45'),
('Jackie Chan', '10.53.56.78',2, '2019-10-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2018-08-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2019-10-19 10:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-05-12 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-11-11 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2019-06-18 22:23:45'),
('Robin Li', '220.181.38.148',3, '2019-10-21 09:45:56'),
('Robin Li', '220.181.38.148',3, '2019-10-26 22:23:45'),
('Pony', '104.69.130.30',4, '2019-10-12 10:23:45'),
('Pony', '104.69.130.30',4, '2019-10-15 20:23:45');
View Code

  肯能一群人须要展示如下列表(需求:展示用户列表,并显示其最近登录时间、最近登录 IP),那么就须要 t_user 和 t_login_log 连表查了

  连接的类型有什么都有有种,细分如下图

交叉连接

  讲交叉连接事先了,一群人先来看看笛卡尔积,假设一群人2个 集合,集合A={a, b},集合B={0, 1, 2},则A与B的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)},表示为AxB,也什么都有有 集合A中的任一元素与集合B的每个元素组合后的新集合则为A与B的笛卡尔积(AxB)。数学上的笛卡尔积反映到数据库中什么都有有 交叉连接(CROSS JOIN),结合上述的案同类下:

SELECT * FROM t_user CROSS JOIN t_login_log;

-- 与 CROSS JOIN 得到的结果相同
-- 过时的写法,不符合 SQL标准,能学会英语就好,不推荐使用
SELECT * FROM t_user, t_login_log;

  t_user 中含 6 条记录, t_login_log 中含 11 条记录,t_user CROSS JOIN t_login_log 的结果是 66( 6 乘以 11) 条记录

  交叉连接什么都有有 对两张表中的删改记录进行交叉组合,而且其结果是两张表的乘积,这也是为哪些地方交叉连接无法使用内连接或外连接中所使用的 ON 子句的意味。交叉连接基本太久再应用到实际业务之中,意味有2个 ,一是其结果那么实用价值,二是结果行数太久,需要花费一定量的运算时间和硬件资源。虽说交叉连接的实际使用场景几乎那么,但还是有它的理论价值的,交叉连接是什么都有有所有连接运算的基础,内连接是交叉连接的一累积,其结果是交叉连接的一累积(子集),外连接一阵一阵特殊,其结果中含 交叉连接之外的内容;更多详情,一群人接着往下看。

内连接

  只返回两张表匹配的记录,就叫内连接,直观的表现什么都有有 关键字:INNER JOIN ... ON,ON 表示两张表连接所使用的列(连接键);而内连接中又属等值连接最常用

  等值连接

    简单点来说,什么都有有 连接键相等

-- 等值连接
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- INNER JOIN 可太久再能简写成 JOIN
SELECT * FROM t_user tu JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- 不加连接键, 结果与 CROSS JOIN 一样
SELECT * FROM t_user tu INNER JOIN t_login_log ttl

    等值连接的结果中,每一条记录的连接键的列的值是想等的,如上图中的 user_name 和 user_name1(为了区别于第2个 user_name,数据库系统自动取的别名,一群人可太久再能显示的指定)

  不等值连接

    连接键的比较谓词除了 = 之外的所有情况汇报,比如 >、<、<>(!=);不等值连接使用场景比较少,反正我在实际工作中几乎没用到过

SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name <> ttl.user_name;
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name > ttl.user_name;

  自然连接

    不须要指定连接条件,数据库系统会自动用相同的字段作为连接键,直观的表现什么都有有 关键字:NATURAL JOIN,NATURAL LEFT JOIN、NATURAL RIGHT JOIN;

    连接键不直观,须要去看两张表中相同的字段哪些地方地方;对于自然连接,了解即可,不推荐使用,反正我工作那么久,一次都没用过。

外连接

  外连接的使用方法 与内连接一样,也是通过 ON 使用连接键将两张表连接,从结果中获取一群人我想要的数据,而且返回的结果与内连接有区别,具体一群人往下看

  左连接

    返回匹配的记录,以及左表多余的记录,关键字:LEFT JOIN(LEFT OUTER JOIN 的简写)

SELECT * FROM t_user tu LEFT OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- LEFT JOIN 是 LEFT OUTER JOIN 的简写
SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

    上图中,前 11 条记录是匹配的记录,而第 12 条是不匹配、左表的记录

  右连接

    返回匹配的记录,以及表 B 多余的记录,关键字:RIGHT JOIN(RIGHT OUTER JOIN 的简写)

SELECT * FROM t_login_log ttl RIGHT OUTER JOIN t_user tu ON tu.user_name = ttl.user_name;
-- RIGHT JOIN 是 RIGHT OUTER JOIN 的简写
SELECT * FROM t_login_log ttl RIGHT JOIN t_user tu ON tu.user_name = ttl.user_name;

    肯能一群人习惯了从左往右(阅读方法 、写作方法 ),而且在实际项目中,基本上用的不会左连接

  全连接

    返回匹配的记录,以及左表和右表各自 的多余记录,关键字:FULL JOIN (FULL OUTER JOIN 的简写)

SELECT * FROM t_user tu FULL OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- FULL JOIN 是 FULL OUTER JOIN 的简写
SELECT * FROM t_user tu FULL JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

    注意:MySQL 不支持 全连接,一群人可太久再能通过 左连接、右连接事先,再 UNION 来实现全连接

自连接

  一张表,此人 连接此人 ,简单点来理解什么都有有 ,左表、右表是同一张表;连接方法 可太久再能是内连接、也可太久再能是外连接

  更多详情一群人可太久再能去看:项目上线后,谈一下感触比较深的什么都有有:查询优化

需求:展示用户列表,并显示最近登录时间、最近登录 IP

  对于此需求,一群人会怎样来写你你是什么 SQL ? 我知道你一群人很容易想到左连接,如下所示

SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

  可结果如下:

  显示的是每个用户的所有登录日志,不会一群人我想要的结果;意味是 t_user 中的一条记录在 t_login_log 对应的记录有多种情况汇报:0 条对应、1 条对应、多条对应,那你你是什么 SQL 要为什写呢,方法 有多种,不局限于如下实现

-- 1、连接配合子查询,注意 Bruce Lee 从未登陆过
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN t_login_log tll ON tu.user_name = tll.user_name
WHERE tll.id = (SELECT MAX(id) FROM t_login_log WHERE user_name = tu.user_name) OR tll.user_name IS NULL;

-- 2、t_login_log分组统计出各个用户的最近一次登录信息后,再与 t_user 联表
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN (
    SELECT tb.* FROM(
        SELECT user_name, MAX(id) id FROM t_login_log GROUP BY user_name
    ) ta LEFT JOIN t_login_log tb ON ta.id = tb.id
) tll ON tu.user_name = tll.user_name;

  具体的实现还得结合具体的业务和需求来实现,那样要能写出高效的 SQL;另外结合执行计划来建立要花费的索引。总之,那么一成不变的、通用的高效 SQL,结合具体的业务要能写出最要花费的 SQL。

总结

  1、连接的描述方法

    常用的维恩图,描述如下

    维恩图描述有他的优势,但它不好表示交叉连接,并肩容易我想要误解成 SQL 中的集合操作;这里推荐另外一种描述方法 ,我虽然描述的更准确

    CROSS JOIN

     常用 JOIN

     上图中,颜色表示匹配关系,颜色相同表示匹配。返回结果中,肯能另一张表那么匹配的记录,则用 null 填充, 在上图中则表示为空白。

  2、连接中 ON 指定连接键,连接键可太久再能指定多个,而 WHERE  还是平时的作用,用来指定过滤条件;不推荐将连接键放于 WHERE 后;

  3、实际工作中,用的最多的是 左连接 和 等值连接,什么都有有的用的一阵一阵少

参考

  《SQL基础教程》

  《SQL进阶教程》

猜你喜欢

PDD希望小学完工是怎么回事 PDD小学具体信息一览

想必关注英雄联盟圈子的玩家都知道,主播PDD在2017年就现在开始了了英文接触需用捐助的希望小学,有刚刚决定捐资500万元给内蒙多伦县,就在最近,有媒体爆料PDD希望小学正式完

2020-01-18

《飘流幻境》手机版甜蜜七夕即将上线结婚系统

【斗蟹资讯】喂,瞧一瞧,看一看嘞~~~热乎乎的情报,嗯,刚出炉的,据说和臊子面一样酸甜苦是咸emmm...啊呀,我怕是吃到了假的臊子面~~~心塞塞,不过,萝卡情人关系提示,情报

2020-01-18

小米悦米机械键盘Pro静音版开售:599元、TTC静音红轴

IT之家4月25日消息 今天小米商城上线了一款全新的机械键盘——悦米机械键盘Pro静音版。悦米机械键盘Pro静音版采用了CNC全铝机身设计,内置TTC静音红轴,支持背光,共拥有

2020-01-18

中國長城文化學術研討會 助推新時代長城文化事業大發展

探討長城文旅融合創新共繪長城公園建設藍圖大公網訊共議長城保護利用發展大計,共繪長城國家文化公園建設藍圖。10月29日至31日,中國長城文化學術研討會在北京市八達嶺長城腳下——延

2020-01-18

负暄集/秋声裏/赵阳

前天早上醒来,推开窗,一阵秋风带着清新的草丛气息扑面而来,不要探头张望,就知道是小区在进行绿化作业。风裏面带着丝丝凉意,终於同类秋的意思了。我着实我知道距离气象意义上的秋天,还

2020-01-18