MySQL打卡4-表联结@Datawhale

1. 设置mysql别名

表别名

查询数据时，如果表名很长，使用起来不方便，此时，就可以为表取一个别名，用这个别名来代替表的名称
SELECT * FROM 表名 [AS] 别名；
注意，为表指定别名，AS关键字可以省略不写

mysql> select * from salary;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  2 | B    | m   |   1500 |
|  3 | C    | f   |   5500 |
|  4 | D    | m   |    500 |
+----+------+-----+--------+
4 rows in set (0.00 sec)

mysql> select * from salary as ss where ss.salary > 2000;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  3 | C    | f   |   5500 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

列别名

在查询数据时，为了使显示的查询结果更加直观，可以为字段取一个别名
SELECT 字段名 [AS] 别名 [，字段名 [AS] 别名，……] FROM 表名；
注意，为字段指定别名，AS关键字可以省略不写。

mysql> select id as user_id, name as user_name, sex as user_sex, salary as user_salary from salary;
+---------+-----------+----------+-------------+
| user_id | user_name | user_sex | user_salary |
+---------+-----------+----------+-------------+
|       1 | A         | f        |        2500 |
|       2 | B         | m        |        1500 |
|       3 | C         | f        |        5500 |
|       4 | D         | m        |         500 |
+---------+-----------+----------+-------------+
4 rows in set (0.00 sec)

2. INNER JOIN、LEFT JOIN、CROSS JOIN、自连接、UNION几种方式区别与联系

inner join:

左右两张表连接字段完全一致
SELECT XXX FROM XXX INNER JOIN XXX ON XXX; 这里 INNER 可以省略

left join:

以左表为全部，去连接右表

cross join:

交叉连接，得到的结果是两个表的乘积，即笛卡尔积.
笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
笛卡尔积算法针对的是表之间数据行的匹配次数，跟内连接还是外连接无关，至于查询结果与你的查询条件有关系。

自连接：

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。
mysql自连接

union:

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

{A, B} UNION {B, C} = {A, B, C}

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；

union all:

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

{A, B} UNION ALL {B, C} = {A, B, B, C}

SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

作业：

项目五：组合两张表 （难度：简单）
在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下
信息：FirstName, LastName, City, State

--创建表
create table person (
personid int not null primary key,
firstname varchar(20) not null,
lastname varchar(20) not null
);
--插入数据
insert into person values (101, 'ming', 'Yao');
insert into person values (102, 'lei', 'Wu');
insert into person values (103, 'zhi', 'Zheng');
--查询结果
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
|      102 | lei       | Wu       |
|      103 | zhi       | Zheng    |
+----------+-----------+----------+
3 rows in set (0.00 sec)
--创建表
create table address (
addressid int not null primary key,
personid int not null,
city varchar(20) not null,
state varchar(20) not null
);
--插入数据
insert into address values (8000, 9999, 'xian', 'UA');
insert into address values (8008, 102, 'beijing', 'AB');
insert into address values (9009, 103, 'hangzhou', 'AC');
--查询结果
mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city     | state |
+-----------+----------+----------+-------+
|      8000 |     9999 | xian     | UA    |
|      8008 |      102 | beijing  | AB    |
|      9009 |      103 | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select firstname, lastname, a.city, a.state
    -> from person
    -> left join address as a on person.personid = a.personid;
+-----------+----------+----------+-------+
| firstname | lastname | city     | state |
+-----------+----------+----------+-------+
| ming      | Yao      | NULL     | NULL  |
| lei       | Wu       | beijing  | AB    |
| zhi       | Zheng    | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

项目六：删除重复的邮箱（难度：简单）
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 email表应返回以下几行:
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | a@b.com |
| 2  | c@d.com  |
+----+------------------+

#问题分析
a. 删除重复的电子邮箱
b. 需要保留ID最小的那个（删除的ID比留下的那一条ID要大）
c. 考察自连接知识，把一张表当两张表来用。

--code--
mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
|  3 | a@b.com |
+----+---------+
3 rows in set (0.00 sec)

mysql> delete e1
    -> from email as e1, email as e2
    -> where e1.Email = e2.Email
    -> and e1.ID > e2.ID;
Query OK, 1 row affected (0.01 sec)

mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
+----+---------+
2 rows in set (0.00 sec)

--此处补充delete删除单表及多表的用法，之前不知道这个知识点
# 单表删除内容
mysql> select * from students2;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | 555   | 1      |       2 |
|   2 | 555   | 2      |       2 |
|   3 | 555   | 3      |       2 |
|   4 | 555   | 3      |       1 |
|   5 | 555   | 1      |       1 |
+-----+-------+--------+---------+
5 rows in set (0.00 sec)

mysql> delete from students2 where sid=4;
Query OK, 1 row affected (0.05 sec)

mysql> select * from students2;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | 555   | 1      |       2 |
|   2 | 555   | 2      |       2 |
|   3 | 555   | 3      |       2 |
|   5 | 555   | 1      |       1 |
+-----+-------+--------+---------+
4 rows in set (0.00 sec)

# 多表删除内容
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
|      102 | lei       | Wu       |
|      103 | zhi       | Zheng    |
+----------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city     | state |
+-----------+----------+----------+-------+
|      8000 |     9999 | xian     | UA    |
|      8008 |      102 | beijing  | AB    |
|      9009 |      103 | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> delete p1, a1
    -> from person p1 inner join address a1
    -> where p1.personid = a1.personid;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
+----------+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from address;
+-----------+----------+------+-------+
| addressid | personid | city | state |
+-----------+----------+------+-------+
|      8000 |     9999 | xian | UA    |
+-----------+----------+------+-------+
1 row in set (0.00 sec)

总结

• 熟悉inner join / left join / cross join / 自连接 、union / union all 几种不同的连接方式
• 主要学习自连接用法，一张表当两张表用
• 深入学习delete语句用法

参考文章：
mysql自连接的一些用法

mysql union 操作符 | 菜鸟教程

mysql（3）-- 内连接、外连接的区别

MySQL-5.7 DELETE语句详解