板凳-------Mysql cookbook学习 (十一--------4)

发布于:2025-07-10 ⋅ 阅读:(24) ⋅ 点赞:(0)

唐宇迪机器学习实战课程笔记
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 中的任何一个都可以包含比较操作。依据经验规则,通常使用onusing 来指定如何连接表,而用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)

网站公告

今日签到

点亮在社区的每一天
去签到