唐宇迪机器学习实战课程笔记
https://blog.csdn.net/weixin_54338498/article/details/128818007?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ECtr-1-128818007-blog-127750710.235%5Ev43%5Econtrol&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ECtr-1-128818007-blog-127750710.235%5Ev43%5Econtrol&utm_relevant_index=1
https://blog.csdn.net/qq_40890544?type=blog&year=2022&month=03
第12章：使用多重表
12.0 引言
12.1 在表中找到与另一个表中的行相匹配的行
mysql> select * from artist
-> ;
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 4 | Renoir |
| 3 | Van Gogh |
+------+----------+
4 rows in set (0.03 sec)
mysql> select * from painting;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+
5 rows in set (0.01 sec)
mysql> insert into painting(a_id, p_id, title, state, price)
-> values('3','6','The Rocks', 'IA','33' ) ;
Query OK, 1 row affected (0.02 sec)
mysql> select * from painting;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | 6 | The Rocks | IA | 33 |
+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from artist, painting;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 1 | 2 | Mona Lisa | MI | 87 |
| 2 | Monet | 1 | 2 | Mona Lisa | MI | 87 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 4 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 1 | Da Vinci | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | 6 | The Rocks | IA | 33 |
| 4 | Renoir | 3 | 6 | The Rocks | IA | 33 |
| 2 | Monet | 3 | 6 | The Rocks | IA | 33 |
| 1 | Da Vinci | 3 | 6 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+
24 rows in set (0.00 sec)
mysql> select * from artist, painting
-> where artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 6 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from artist inner join painting
-> on artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 6 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from artist inner join painting
-> using(a_id);
+------+----------+------+-------------------+-------+-------+
| a_id | name | p_id | title | state | price |
+------+----------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 6 | The Rocks | IA | 33 |
+------+----------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
On, using, or where 中的任何一个都可以包含比较操作。依据经验规则，通常使用on或using 来指定如何连接表，而用where 子句限制选择哪些已经连接的行。
根据a_id列连接表，只选择（kentucky）购买的画作，使用on (or using)子句匹配两个表中的行，使用where子句来检验state列：
mysql> select * from artist inner join painting
-> on artist.a_id = painting.a_id
-> where painting.state = 'KY';
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
+------+----------+------+------+-------------------+-------+-------+
2 rows in set (0.01 sec)
mysql> select artist.name, painting.title, painting.state, painting.price
-> from artist inner join painting
-> on artist.a_id = painting.a_id
-> where painting.state = 'KY';
+----------+-------------------+-------+-------+
| name | title | state | price |
+----------+-------------------+-------+-------+
| Van Gogh | Starry Night | KY | 48 |
| Van Gogh | The Potato Eaters | KY | 67 |
+----------+-------------------+-------+-------+
2 rows in set (0.00 sec)
mysql> select artist.name, painting.title, states.name, painting.price
-> from artist inner join painting inner join states
-> on artist.a_id = painting.a_id and painting.state =states.abbrev;
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
| Van Gogh | The Rocks | Iowa | 33 |
+----------+-------------------+----------+-------+
6 rows in set (0.00 sec)
可以给每一个表一个别名，并使用此别名指向它的列
mysql> select a.name, p.title, s.name, p.price
-> from artist as a inner join painting as p inner join states as s
-> on a.a_id = p.a_id and p.state = s.abbrev;
+----------+-------------------+----------+-------+
| name | title | name | price |
+----------+-------------------+----------+-------+
| Da Vinci | The Last Supper | Indiana | 34 |
| Da Vinci | Mona Lisa | Michigan | 87 |
| Van Gogh | Starry Night | Kentucky | 48 |
| Van Gogh | The Potato Eaters | Kentucky | 67 |
| Renoir | Les Deux Soeurs | Nebraska | 64 |
| Van Gogh | The Rocks | Iowa | 33 |
+----------+-------------------+----------+-------+
6 rows in set (0.00 sec)
mysql> select artist.name, painting.title, states.name, painting.price, states.statehood
-> from artist inner join painting inner join states
-> on artist.a_id = painting.a_id and painting.state =states.abbrev;
+----------+-------------------+----------+-------+------------+
| name | title | name | price | statehood |
+----------+-------------------+----------+-------+------------+
| Da Vinci | The Last Supper | Indiana | 34 | 1816-12-11 |
| Da Vinci | Mona Lisa | Michigan | 87 | 1837-01-26 |
| Van Gogh | Starry Night | Kentucky | 48 | 1792-06-01 |
| Van Gogh | The Potato Eaters | Kentucky | 67 | 1792-06-01 |
| Renoir | Les Deux Soeurs | Nebraska | 64 | 1867-03-01 |
| Van Gogh | The Rocks | Iowa | 33 | 1846-12-28 |
+----------+-------------------+----------+-------+------------+
6 rows in set (0.00 sec)
mysql> select painting.title
-> from artist inner join painting on artist.a_id = painting.a_id
-> where artist.name = 'Van Gogh';
+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+-------------------+
3 rows in set (0.00 sec)
mysql> select artist.name
-> from artist inner join painting on artist.a_id = painting.a_id
-> where painting.title = 'Mona Lisa';
+----------+
| name |
+----------+
| Da Vinci |
+----------+
1 row in set (0.00 sec)
mysql> select distinct artist.name
-> from artist inner join painting on artist.a_id = painting.a_id
-> where painting.state in ('KY', 'IN');
+----------+
| name |
+----------+
| Da Vinci |
| Van Gogh |
+----------+
2 rows in set (0.00 sec)
Distinct 以每个画家的名字只显示一次。
高性能 MySQL 第四版（GPT 重译）（一）
mysql_绝不原创的飞龙的博客-CSDN博客
https://www.bilibili.com/opus/777022148865687593
MySQL8.0以及数据库可视化软件navicat 15 Premium版本超详细安装教程
mysql> select artist.name, count(*) as 'number of paintings'
-> from artist inner join painting on artist.a_id = painting.a_id
-> group by artist.name;
+----------+---------------------+
| name | number of paintings |
+----------+---------------------+
| Da Vinci | 2 |
| Van Gogh | 3 |
| Renoir | 1 |
+----------+---------------------+
3 rows in set (0.00 sec)
mysql> select artist.name,
-> count(*) as 'number of paintings',
-> sum(painting.price) as 'total price',
-> avg(painting.price) as 'average price'
-> from artist inner join painting on artist.a_id = painting.a_id
-> group by artist.name;
+----------+---------------------+-------------+---------------+
| name | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Van Gogh | 3 | 148 | 49.3333 |
| Renoir | 1 | 64 | 64.0000 |
+----------+---------------------+-------------+---------------+
3 rows in set (0.00 sec)
12.2 查找与其他表不匹配的行
mysql> select * from artist order by a_id;
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Renoir |
+------+----------+
4 rows in set (0.01 sec)
mysql> select * from painting order by a_id, p_id;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | 6 | The Rocks | IA | 33 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from artist inner join painting
-> on artist.a_id != painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 1 | 2 | Mona Lisa | MI | 87 |
| 2 | Monet | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 4 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 1 | Da Vinci | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 4 | Renoir | 3 | 6 | The Rocks | IA | 33 |
| 2 | Monet | 3 | 6 | The Rocks | IA | 33 |
| 1 | Da Vinci | 3 | 6 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+
18 rows in set (0.00 sec)
mysql> select * from artist inner join painting
-> on artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | 6 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from artist left join painting
-> on artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 3 | 6 | The Rocks | IA | 33 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
+------+----------+------+------+-------------------+-------+-------+
7 rows in set (0.00 sec)
mysql> select * from artist left join painting
-> on artist.a_id = painting.a_id
-> where painting.a_id is null;
+------+-------+------+------+-------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+-------+------+------+-------+-------+-------+
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
+------+-------+------+------+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select artist.* from artist left join painting
-> on artist.a_id = painting.a_id
-> where painting.a_id is null;
+------+-------+
| a_id | name |
+------+-------+
| 2 | Monet |
+------+-------+
1 row in set (0.00 sec)
首先，在select子句中，if函数前缺少逗号，导致与前一个字段artist.name粘连在一起。
其次，在 MySQL 中，字符串字面量需要用单引号包围，但别名建议用反引号而非单引号，避免与字符串字面量混淆。
mysql> select artist.name,
-> if(count(painting.a_id) > 0, 'yes', 'no') as `in collection`
-> from artist left join painting on artist.a_id = painting.a_id
-> group by artist.name;
+----------+---------------+
| name | in collection |
+----------+---------------+
| Da Vinci | yes |
| Monet | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+---------------+
4 rows in set (0.00 sec)
mysql> select artist.name,
-> if(count(painting.a_id) > 0, 'yes', 'no') as `in collection`
-> from painting right join artist on artist.a_id = painting.a_id
-> group by artist.name;
+----------+---------------+
| name | in collection |
+----------+---------------+
| Da Vinci | yes |
| Monet | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+---------------+
4 rows in set (0.00 sec)
12.3 将表与自身进行比较
mysql> select a_id, title from painting order by a_id;
+------+-------------------+
| a_id | title |
+------+-------------------+
| 1 | The Last Supper |
| 1 | Mona Lisa |
| 3 | Starry Night |
| 3 | The Potato Eaters |
| 3 | The Rocks |
| 4 | Les Deux Soeurs |
+------+-------------------+
6 rows in set (0.00 sec)
mysql> select @id := a_id from painting where title='The Potato Eaters';
+-------------+
| @id := a_id |
+-------------+
| 3 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select title from painting where a_id = @id;
+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+-------------------+
3 rows in set (0.00 sec)
mysql> select p2.title
-> from painting as p1 inner join painting as p2
-> on p1.a_id = p2.a_id;
+-------------------+
| title |
+-------------------+
| The Last Supper |
| The Last Supper |
| Mona Lisa |
| Mona Lisa |
| Starry Night |
| Starry Night |
| Starry Night |
| The Potato Eaters |
| The Potato Eaters |
| The Potato Eaters |
| Les Deux Soeurs |
| The Rocks |
| The Rocks |
| The Rocks |
+-------------------+
14 rows in set (0.00 sec)
mysql> select p2.title
-> from painting as p1 inner join painting as p2
-> on p1.a_id = p2.a_id
-> where p1.title = 'The Potato Eaters';
+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+-------------------+
3 rows in set (0.00 sec)
mysql> select p2.title
-> from painting as p1 inner join painting as p2
-> on p1.a_id = p2.a_id
-> where p1.title = 'The Potato Eaters' and p2.title != 'The Potato Eaters';
+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+
2 rows in set (0.00 sec)
mysql> select p2.title
-> from painting as p1 inner join painting as p2
-> on p1.a_id = p2.a_id
-> where p1.title = 'The Potato Eaters' and p2.title != p1.title;
+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+
2 rows in set (0.00 sec)
mysql> select s2.name, s2.statehood
-> from states as s1 inner join states as s2
-> on year(s1.statehood) = year(s2.statehood)
-> where s1.name = 'New York'
-> order by s2.name;
+----------------+------------+
| name | statehood |
+----------------+------------+
| Connecticut | 1788-01-09 |
| Georgia | 1788-01-02 |
| Maryland | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| New York | 1788-07-26 |
| South Carolina | 1788-05-23 |
| Virginia | 1788-06-25 |
+----------------+------------+
8 rows in set (0.01 sec)
mysql> select s1.name , s1.statehood
-> from states as s1 left join states as s2
-> on year(s1.statehood) = year(s2.statehood) and s1.name != s2.name
-> where s2.name is null
-> order by s1.name;
+----------------+------------+
| name | statehood |
+----------------+------------+
| Alabama | 1819-12-14 |
| Arkansas | 1836-06-15 |
| California | 1850-09-09 |
| Colorado | 1876-08-01 |
| Illinois | 1818-12-03 |
| Indiana | 1816-12-11 |
| Iowa | 1846-12-28 |
| Kansas | 1861-01-29 |
| Kentucky | 1792-06-01 |
| Louisiana | 1812-04-30 |
| Maine | 1820-03-15 |
| Michigan | 1837-01-26 |
| Minnesota | 1858-05-11 |
| Mississippi | 1817-12-10 |
| Missouri | 1821-08-10 |
| Nebraska | 1867-03-01 |
| Nevada | 1864-10-31 |
| North Carolina | 1789-11-21 |
| Ohio | 1803-03-01 |
| Oklahoma | 1907-11-16 |
| Oregon | 1859-02-14 |
| Rhode Island | 1790-05-29 |
| Tennessee | 1796-06-01 |
| Utah | 1896-01-04 |
| Vermont | 1791-03-04 |
| West Virginia | 1863-06-20 |
| Wisconsin | 1848-05-29 |
+----------------+------------+
27 rows in set (0.00 sec)