连接查询
MySQL
|


其中，left join 和 right join统称为 “外连接查询”，inner join 是“内连接查询”

inner join

用法

先按照这个文件里的语句创建好数据。
一共有三个表：

  1. student。uid(P)/name/age/sex
  2. course。cid(P)/cname/credit（绩点）
  3. exame。uid(P)/cid(P)/time/score

现在需求来了：查询 uid=1 且 cid=2 的学生的学生信息和成绩，由于是两个表，且有公共的记录，用到了 inner join

分两次表查是这样的

select a.uid,a.name,a.age,a.sex from student a where a.uid=1;
select c.score from exam c where c.uid=1 and c.cid=2;

现在将其连起来

mysql> select a.uid,a.name,a.age,a.sex,c.score from exame c
    -> inner join student a on a.uid=c.uid where c.uid=1 and c.cid=2;
+-----+-----------+-----+-----+-------+
| uid | name      | age | sex | score |
+-----+-----------+-----+-----+-------+
|   1 | zhang san |  18 | M   |    80 |
+-----+-----------+-----+-----+-------+
1 row in set (0.00 sec)

再加上需求，把课程的信息也输出。这就涉及到了三个表的操作。表是两两相关的。用到两个 inner join

mysql> select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
    -> from exame c
    -> inner join student a on c.uid=a.uid
    -> inner join course b on c.cid=b.cid
    -> where c.uid=1 and c.cid=2;
+-----+-----------+-----+-----+-----+-----------+--------+-------+
| uid | name      | age | sex | cid | cname     | credit | score |
+-----+-----------+-----+-----+-----+-----------+--------+-------+
|   1 | zhang san |  18 | M   |   2 | C++ gaoji |     10 |    80 |
+-----+-----------+-----+-----+-----+-----------+--------+-------+
1 row in set (0.00 sec)

完成课后的两个作业：
1）cid=2这门课程考试成绩的最高分的学生信息和课程信息

mysql> select * from exame where cid=2;
+-----+-----+------------+-------+
| uid | cid | time       | score |
+-----+-----+------------+-------+
|   1 |   2 | 2021-04-10 |    80 |
|   2 |   2 | 2021-04-10 |    90 |
|   3 |   2 | 2021-04-10 |    93 |
|   5 |   2 | 2021-04-10 |    59 |
+-----+-----+------------+-------+
4 rows in set (0.00 sec)

mysql> select c.score,b.cid,b.cname,a.uid,a.name,a.age,a.sex
    -> from exame c
    -> inner join course b on c.cid=b.cid
    -> inner join student a on c.uid=a.uid
    -> where c.cid=2
    -> order by c.score desc limit 1;
+-------+-----+-----------+-----+----------+-----+-----+
| score | cid | cname     | uid | name     | age | sex |
+-------+-----+-----------+-----+----------+-----+-----+
|    93 |   2 | C++ gaoji |   3 | chen wei |  22 | M   |
+-------+-----+-----------+-----+----------+-----+-----+
1 row in set (0.00 sec)

2)cid=2这门课程的考试平均成绩&每门课程考试的平均成绩+课程信息

mysql> select avg(c.score),b.cid,b.cname
    -> from exame c
    -> inner join course b on b.cid=c.cid
    -> where c.cid=2;
+--------------+-----+-----------+
| avg(c.score) | cid | cname     |
+--------------+-----+-----------+
|         80.5 |   2 | C++ gaoji |
+--------------+-----+-----------+
1 row in set (0.01 sec)
mysql> select c.cid,ROUND(avg(c.score),2),b.cname
    -> from exame c
    -> inner join course b on b.cid=c.cid
    -> group by c.cid
    -> order by c.cid;
+-----+-----------------------+---------------+
| cid | ROUND(avg(c.score),2) | cname         |
+-----+-----------------------+---------------+
|   1 |                 77.50 | C++ jichu     |
|   2 |                 80.50 | C++ gaoji     |
|   3 |                 89.33 | C++ xiangmu   |
|   4 |                 98.00 | C++ algorithm |
+-----+-----------------------+---------------+
4 rows in set (0.00 sec)

优化查询

做偏移的效率和select有关

mysql> select * from t_user limit 1500000,10;
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1500001 | 1500001@qq.com | 1500001  |
| 1500002 | 1500002@qq.com | 1500002  |
| 1500003 | 1500003@qq.com | 1500003  |
| 1500004 | 1500004@qq.com | 1500004  |
| 1500005 | 1500005@qq.com | 1500005  |
| 1500006 | 1500006@qq.com | 1500006  |
| 1500007 | 1500007@qq.com | 1500007  |
| 1500008 | 1500008@qq.com | 1500008  |
| 1500009 | 1500009@qq.com | 1500009  |
| 1500010 | 1500010@qq.com | 1500010  |
+---------+----------------+----------+
10 rows in set (0.30 sec)

mysql> select id from t_user limit 1500000,10;
+---------+
| id      |
+---------+
| 1500001 |
| 1500002 |
| 1500003 |
| 1500004 |
| 1500005 |
| 1500006 |
| 1500007 |
| 1500008 |
| 1500009 |
| 1500010 |
+---------+
10 rows in set (0.23 sec)

必须要select *，不能用 where 怎么优化？
采用临时表

mysql> select a.id,a.email,a.password from t_user a
    -> inner join (select id from t_user limit 1500000,10) b
    -> on a.id=b.id;
+---------+----------------+----------+
| id      | email          | password |
+---------+----------------+----------+
| 1500001 | 1500001@qq.com | 1500001  |
| 1500002 | 1500002@qq.com | 1500002  |
| 1500003 | 1500003@qq.com | 1500003  |
| 1500004 | 1500004@qq.com | 1500004  |
| 1500005 | 1500005@qq.com | 1500005  |
| 1500006 | 1500006@qq.com | 1500006  |
| 1500007 | 1500007@qq.com | 1500007  |
| 1500008 | 1500008@qq.com | 1500008  |
| 1500009 | 1500009@qq.com | 1500009  |
| 1500010 | 1500010@qq.com | 1500010  |
+---------+----------------+----------+
10 rows in set (0.26 sec)

大表用索引。因为使用on时区分大表和小表，按照数据量区分的，小表永远是整表扫描，然后去大表扫描。

mysql> mysql> explain select a.*, b.* from student a inner join exame b on a.uid = b.uid;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL         |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | school.a.uid |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可以看出实际用到了索引的是大表

对于inner join内连接，过滤条件写在where的后面和on连接条件里面，效果是一样的。

mysql> explain select a.*,b.* from student a inner join exame b on a.uid=b.uid and b.cid=3;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |   12 |    10.00 | Using where |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.b.uid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> explain select a.*,b.* from student a inner join exame b on a.uid=b.uid where b.cid=3;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |   12 |    10.00 | Using where |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.b.uid |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

外连接

左连接：把左边表的所有数据显示出来，如果在右表中不存在相应数据，显示NULL
右连接：把右边表的所有数据显示出来，如果在左表中不存在相应数据，显示NULL
比如我们现在有一个学生没有考试记录。

mysql> select a.*,b.* from student a left join exame b on a.uid=b.uid;
+-----+-----------+-----+-----+------+------+------------+-------+
| uid | name      | age | sex | uid  | cid  | time       | score |
+-----+-----------+-----+-----+------+------+------------+-------+
|   1 | zhang san |  18 | M   |    1 |    1 | 2021-04-09 |    99 |
|   1 | zhang san |  18 | M   |    1 |    2 | 2021-04-10 |    80 |
|   2 | gao yang  |  20 | W   |    2 |    2 | 2021-04-10 |    90 |
|   2 | gao yang  |  20 | W   |    2 |    3 | 2021-04-12 |    85 |
|   3 | chen wei  |  22 | M   |    3 |    1 | 2021-04-09 |    56 |
|   3 | chen wei  |  22 | M   |    3 |    2 | 2021-04-10 |    93 |
|   3 | chen wei  |  22 | M   |    3 |    3 | 2021-04-12 |    89 |
|   3 | chen wei  |  22 | M   |    3 |    4 | 2021-04-11 |   100 |
|   4 | lin feng  |  21 | W   |    4 |    4 | 2021-04-11 |    99 |
|   5 | liu xiang |  19 | W   |    5 |    2 | 2021-04-10 |    59 |
|   5 | liu xiang |  19 | W   |    5 |    3 | 2021-04-12 |    94 |
|   5 | liu xiang |  19 | W   |    5 |    4 | 2021-04-11 |    95 |
|   6 | wei wei   |  20 | M   | NULL | NULL | NULL       |  NULL |
+-----+-----------+-----+-----+------+------+------------+-------+
13 rows in set (0.00 sec)

优化

not in对于索引的命中率并不高。可以采用外连接实现相同功能。

mysql> select * from student where uid not in (select distinct uid from exame);
+-----+---------+-----+-----+
| uid | name    | age | sex |
+-----+---------+-----+-----+
|   6 | wei wei |  20 | M   |
+-----+---------+-----+-----+
1 row in set (0.00 sec)

mysql> select a.* from student a left join exame b on a.uid=b.uid where b.cid is null;
+-----+---------+-----+-----+
| uid | name    | age | sex |
+-----+---------+-----+-----+
|   6 | wei wei |  20 | M   |
+-----+---------+-----+-----+
1 row in set (0.00 sec)

没考过试的话，cid自然是null

与inner join的区别

先给出一段匪夷所思的代码

mysql> mysql> select a.*,b.* from student a left join exame b on a.uid=b.uid and b.cid=3 where b.cid is null;
+-----+-----------+-----+-----+------+------+------+-------+
| uid | name      | age | sex | uid  | cid  | time | score |
+-----+-----------+-----+-----+------+------+------+-------+
|   1 | zhang san |  18 | M   | NULL | NULL | NULL |  NULL |
|   4 | lin feng  |  21 | W   | NULL | NULL | NULL |  NULL |
|   6 | wei wei   |  20 | M   | NULL | NULL | NULL |  NULL |
+-----+-----------+-----+-----+------+------+------+-------+
3 rows in set (0.00 sec)

on里面写的b.cib=3，where里面写的b.cib is null。原因是on A and B需要满足A和B同时成立。

当条件在where后面没有区别

mysql> explain select a.* from student a inner join exame b on a.uid=b.uid where b.cid=3;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 8       | NULL         |   12 |    10.00 | Using where; Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.b.uid |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select a.* from student a left join exame b on a.uid=b.uid where b.cid=3;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | PRIMARY | 8       | NULL         |   12 |    10.00 | Using where; Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.b.uid |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

所以外连接时把条件写到on里面，免得一上来就过滤 b 表，我们想让它直接扫描一遍 a 表。

mysql> select a.*,b.* from student a left join exame b on a.uid=b.uid and b.cid=3;
+-----+-----------+-----+-----+------+------+------------+-------+
| uid | name      | age | sex | uid  | cid  | time       | score |
+-----+-----------+-----+-----+------+------+------------+-------+
|   1 | zhang san |  18 | M   | NULL | NULL | NULL       |  NULL |
|   2 | gao yang  |  20 | W   |    2 |    3 | 2021-04-12 |    85 |
|   3 | chen wei  |  22 | M   |    3 |    3 | 2021-04-12 |    89 |
|   4 | lin feng  |  21 | W   | NULL | NULL | NULL       |  NULL |
|   5 | liu xiang |  19 | W   |    5 |    3 | 2021-04-12 |    94 |
|   6 | wei wei   |  20 | M   | NULL | NULL | NULL       |  NULL |
+-----+-----------+-----+-----+------+------+------------+-------+
6 rows in set (0.00 sec)

mysql> select a.*,b.* from student a left join exame b on a.uid=b.uid and b.cid=3 where b.cid is null;
+-----+-----------+-----+-----+------+------+------+-------+
| uid | name      | age | sex | uid  | cid  | time | score |
+-----+-----------+-----+-----+------+------+------+-------+
|   1 | zhang san |  18 | M   | NULL | NULL | NULL |  NULL |
|   4 | lin feng  |  21 | W   | NULL | NULL | NULL |  NULL |
|   6 | wei wei   |  20 | M   | NULL | NULL | NULL |  NULL |
+-----+-----------+-----+-----+------+------+------+-------+
3 rows in set (0.00 sec)

on后面跟着的看作一个bool值。由于是外连接，左边的所有都要被扫一遍，这样没有cid=3的人将被计null，就被查出来了。

Post Views: 125
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇