MySQL学习笔记（四）

表联结

参考链接：https://blog.csdn.net/qq_34899040/article/details/89036270#3LEFT_JOIN_449

1 MySQL别名

通过使用SQL语言，可以为表名称或列名称指定别名。使用AS关键字。
创建别名是为了让列名称的可读性更强。

//列的SQL别名语法：
SELECT column_name AS alias_name//提示：如果列名称包含空格，要求使用双引号或方括号
FROM table_name;

//表的 SQL 别名语法:
SELECT column_name(s)
FROM table_name AS alias_name;


//列的别名示例1：指定了两个别名，一个是 name 列的别名，一个是 country 列的别名。
SELECT name AS n, country AS c
FROM Websites;

//列的别名示例2：把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名。
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;


//表的别名实例:使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"（通过使用别名让 SQL 更简短）：
SELECT w.name, w.url, a.count, a.date 
FROM Websites AS w, access_log AS a 
WHERE a.site_id=w.id and w.name="教程";

//如果不使用别名:(与上面SQL语句效果相同)
SELECT Websites.name, Websites.url, access_log.count, access_log.date 
FROM Websites, access_log 
WHERE Websites.id=access_log.site_id and Websites.name="教程";

在以下情况中应使用别名：

在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

2 INNER JOIN

//在RUNOOB数据库中存在这样两张表：
mysql> use RUNOOB;
Database changed
mysql> show tables;
+------------------+
| Tables_in_runoob |
+------------------+
| runoob_tbl       |
| tcount_tbl       |
+------------------+
2 rows in set (0.01 sec)

mysql> select * from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.00 sec)

mysql> select * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         1 | 学习 PHP      | 菜鸟教程       | 2017-04-12      |
|         2 | 学习 MySQL    | 菜鸟教程       | 2017-04-12      |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C        | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.00 sec)

//使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
mysql> select a.runoob_id,a.runoob_author,b.runoob_count
    -> from runoob_tbl a
    -> inner join tcount_tbl b
    -> on a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程       |           10 |
|         2 | 菜鸟教程       |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.01 sec)

3 LEFT JOIN

左连接，获取左表中所有记录，即使右表没有对应匹配的记录。

//以 runoob_tbl 为左表，tcount_tbl 为右表
mysql> select a.runoob_id,a.runoob_author,b.runoob_count
    -> from runoob_tbl a
    -> left join tcount_tbl b
    -> on a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程       |           10 |
|         2 | 菜鸟教程       |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

4 CROSS JOIN

CROSS JOIN将返回第一个表中的每一行与第二个表中的每一行组合在一起的所有记录。这也意味着CROSS JOIN返回连接表中行集的笛卡尔积。
CROSS JOIN使用：可以以两种方式来指定JOIN或语法通过列出在表FROM由逗号分隔的条款，而无需使用WHERE子句供给联接标准。

SQL CROSS JOIN语法：
SELECT * FROM [表1] CROSS JOIN [表2]
或
SELECT * FROM [表1]，[表2]

mysql> select * from runoob_tbl cross join tcount_tbl;
+-----------+---------------+---------------+-----------------+---------------+--------------+
| runoob_id | runoob_title  | runoob_author | submission_date | runoob_author | runoob_count |
+-----------+---------------+---------------+-----------------+---------------+--------------+
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      | 菜鸟教程      |           10 |
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      | RUNOOB.COM    |           20 |
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      | Google        |           22 |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      | 菜鸟教程      |           10 |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      | RUNOOB.COM    |           20 |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      | Google        |           22 |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      | 菜鸟教程      |           10 |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      | RUNOOB.COM    |           20 |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      | Google        |           22 |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      | 菜鸟教程      |           10 |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      | RUNOOB.COM    |           20 |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      | Google        |           22 |
|         5 | 学习 C        | FK            | 2017-04-05      | 菜鸟教程      |           10 |
|         5 | 学习 C        | FK            | 2017-04-05      | RUNOOB.COM    |           20 |
|         5 | 学习 C        | FK            | 2017-04-05      | Google        |           22 |
+-----------+---------------+---------------+-----------------+---------------+--------------+
15 rows in set (0.00 sec)

5 自连接

参考链接：https://blog.csdn.net/lu0422/article/details/78892497
PS：如果对内外联结，全联结等概念需要了解的，也可以打开上面的链接

自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表

SELECT e.empName,b.empName
     from t_employee e
     LEFT JOIN t_employee b
     ON e.bossId = b.id;

查询结果：

在这里，b表是虚拟化出的表，我们可以通过查询了解b表的记录：

SELECT e.empName,b.empName,b.*
     from t_employee e
     LEFT JOIN t_employee b
     ON e.bossId = b.id;

查询结果：

后面的四个字段就是虚拟化出的b表的所有记录，但看这四个字段其实就是记录所有是上司的员工的信息

所以，自连接查询一般用作表中的某个字段的值是引用另一个字段的值，比如权限表中，父权限也属于权限。

6 UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

//MySQL UNION 操作符语法格式：
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数：

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

//从"runoob_tbl"表和"tcount_tbl"表中选取所有不同的runoob_author（只有不同的值）：
mysql> select runoob_author from runoob_tbl
    -> union
    -> select runoob_author from tcount_tbl
    -> order by runoob_author;
+---------------+
| runoob_author |
+---------------+
| FK            |
| Google        |
| RUNOOB.COM    |
| 菜鸟教程      |
+---------------+
4 rows in set (0.01 sec)

//使用union all从"runoob_tbl"表和"tcount_tbl"表中选取所有的runoob_author（也有重复的值）：
mysql> select runoob_author from runoob_tbl
    -> union all
    -> select runoob_author from tcount_tbl
    -> order by runoob_author;
+---------------+
| runoob_author |
+---------------+
| FK            |
| Google        |
| RUNOOB.COM    |
| RUNOOB.COM    |
| RUNOOB.COM    |
| 菜鸟教程      |
| 菜鸟教程      |
| 菜鸟教程      |
+---------------+
8 rows in set (0.00 sec)

7、以上几种方式的区别和联系

INNER JOIN：内连接或等值连接，获取两个表中字段匹配关系的记录。
LEFT JOIN：左连接，获取左表中所有记录，即使右表没有对应匹配的记录。
CROSS JOIN：将返回第一个表中的每一行与第二个表中的每一行组合在一起的所有记录。笛卡尔积
自连接：不同表的不同列连接在一起。
UNION ：用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。