Day.22 SQL应用 - 表与表之间的连结 (JOIN)
今天来介绍关连式资料库查询方便的地方-JOIN
- 多表之间的连结(JOIN 用法)
用以下3个表的纪录内容来解释，使用不同JOIN方法在资料表之中的资料显示差异～
mysql> select * from act;
+------------+--------+------------+------------+----------------+------------+------------+
| actives_id | status | start_time | end_time | platform_id | updated_at | created_at |
+------------+--------+------------+------------+----------------+------------+------------+
| 1 | 1 | 1611504000 | 1632931200 | 10868213102191 | 1632499200 | 1632499200 |
+------------+--------+------------+------------+----------------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from act_game;
+------------+-----------------+-------------------------------+------+------------+
| actives_id | game_name | support_rules | type | created_at |
+------------+-----------------+-------------------------------+------+------------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | 7 | 1632499200 |
| 2 | INVEST-ITHOME99 | ["z920"] | 5 | 1632499200 |
+------------+-----------------+-------------------------------+------+------------+
2 rows in set (0.00 sec)
mysql> select * from act_name;
+------------+--------------+----------+------------+
| actives_id | actives_name | language | created_at |
+------------+--------------+----------+------------+
| 1 | test2 | ch | 1632499205 |
| 1 | test1 | en | 1632499200 |
| 1 | test3 | jp | 1632499209 |
| 3 | test01 | ch | 1632499220 |
| 3 | test02 | en | 1632499225 |
| 3 | test03 | jp | 1632499230 |
+------------+--------------+----------+------------+
6 rows in set (0.00 sec)
- INNER JOIN(等值连接) -> 查询结果只会返回符合2边资料表连接条件的资料
使用: 连结act_game&act_name取得游戏内容包含使用语言资讯。
语法:
SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
INNER JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
+------------+-----------------+-------------------------------+----------+
3 rows in set (0.00 sec)
- LEFT JOIN -> 返回左侧资料表中所有资料列(就算没有符合连接的条件)，右侧资料表中如果没有能匹配的资料值会显示为NULL。
使用: 连结act_game&act_name取得游戏内容包含使用语言资讯。
语法:
SELECT column1, column2...
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
LEFT JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | NULL |
+------------+-----------------+-------------------------------+----------+
4 rows in set (0.00 sec)
- RIGHT JOIN -> 返回右侧资料表中所有资料列(就算没有符合连接的条件)，左侧资料表中如果没有匹配的资料值会显示为NULL。
使用: 连结act_game&act_name取得游戏内容包含使用语言资讯。
语法:
SELECT column1, column2...
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
RIGHT JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| NULL | NULL | NULL | ch |
| NULL | NULL | NULL | en |
| NULL | NULL | NULL | jp |
+------------+-----------------+-------------------------------+----------+
6 rows in set (0.00 sec)
- CROSS JOIN -> 不指定搜寻条件，返回两个资料表中所有可能的排列组合。
使用: 连结act_game&act_name取得游戏内容包含使用语言资讯。
范例中: act_game (2笔记录) * act_name (6笔记录) = 返回12笔结果。
语法:
SELECT table_column1, table_column2...
FROM table_name1
CROSS JOIN table_name2;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
CROSS JOIN act_name;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 2 | INVEST-ITHOME99 | ["z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 2 | INVEST-ITHOME99 | ["z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | jp |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 2 | INVEST-ITHOME99 | ["z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 2 | INVEST-ITHOME99 | ["z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | jp |
+------------+-----------------+-------------------------------+----------+
12 rows in set (0.00 sec)
-
UNION -> 将两个(以上)SQL查询结果合并起来，查询的各别SQL语句使用栏位要是相同的资料型别及顺序。
ps.MYSQL没有FULL JOIN，可以用UNION来达到类似作用。
差异点:
-
JOIN: 横向结合 (合并多个资料表的各栏位)。
-
UNION: 垂直结合 (合并多个资料表中的纪录)。
-
使用: 查询act_game&act_name的纪录。
1. UNION 会显示2表含几个不同的actives_id。
2. UNION ALL 会列出所有的资料，不论资料是否重复。
语法:
SELECT column_name... FROM table_name1
UNION
SELECT column_name... FROM table_name2;
mysql> SELECT actives_id FROM act_game
UNION
SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
3 rows in set (0.00 sec)
mysql> SELECT actives_id FROM act_game UNION ALL SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
| 1 |
| 2 |
| 1 |
| 1 |
| 1 |
| 3 |
| 3 |
| 3 |
+------------+
8 rows in set (0.00 sec)
介绍完以上资料表连结的方式後，最後来个3表之间的连结查询当作结尾
使用: 连结 act & act_game & act_name 取得活动ID=1的完整内容。
3张表的全部栏位资讯:
SELECT act.* , act_game.* , act_name.*
FROM `act`
left join act_game on act.actives_id = act_game.actives_id
left join act_name on act.actives_id = act_name.actives_id
WHERE (act.actives_id = 1);
过滤其他不需要的栏位後:
SELECT act.actives_id, act.status, act.start_time, act.end_time, act.platform_id, act_game.game_name, act_game.support_rules, act_game.type, act_name.actives_name, act_name.language FROM `act` left join act_game on act.actives_id = act_game.actives_id left join act_name on act.actives_id = act_name.actives_id WHERE (act.actives_id = 1);
放弃实作 AES CBC 加密/解密
是的，如题 因为网路上找到的范例，几乎都是具备密码学知识基础才看得懂的 … 我完全无法使用 pyth...
[DAY11] Data Access Layer 设计概念
前言 这篇将介绍 boxenn 与 DAL 层的依赖关系和介面。 简易 Class Diagram ...
GMail 挡信，DNS Server 需要新增 spf dmarc dkim 该怎麽设定
GMail 挡信，DNS Server 需要新增 spf dmarc dkim 该怎麽设定 原文出处...
[Day9] Reactstrap = Bootstrap in React，你看离 React 越来越近了吧
前言 昨天的文章带到 Reactstrap 的 Grid 写法， 不过当然不只有 <Conta...
让团队把事情做好：提升动力
昨天谈到前 Intel 执行长，企业管理「教父」Andy Grove 认为，员工不能达成任务只有两个...