SQL开发技巧-MySQL

一、SQL语言介绍

SQL-结构化查询语言(Structured Query Language)

1.常见的SQL语句类型

DML：select、insert、update、delete

2.SQL的重要性

	1. 增加数据库处理效率，减少应用响应时间
 	2. 减少数据库服务器负载，增加服务器稳定性
 	3. 减少服务器间通讯的网络流量
1
2
3

3. join从句

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行

二、Join从句

---

1. 测试数据库

CREATE DATABASE IF NOT EXISTS `join_test` CHARACTER SET = utf8mb4;
USE `join_test`;
1
2

2. 测试数据表user1

CREATE TABLE IF NOT EXISTS `user1` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
 `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user1`(`user_name`,`over`) VALUES ('唐僧','旃檀功德佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('猪八戒','净坛使者');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('孙悟空','斗战圣佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('沙僧','金身罗汉');
1
2
3
4
5
6
7
8
9
10

3. 测试数据表user2

CREATE TABLE IF NOT EXISTS `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user2`(`user_name`,`over`) VALUES ('孙悟空','斗战胜佛');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('牛魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('蛟魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('鹏魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('狮驼王','被降服');

1
2
3
4
5
6
7
8
9
10
11
12

---

1.Join操作的类型-- INNER JOIN内连接

• INNER JOIN 关键字在表中存在至少一个匹配时返回行。
• 内连接Inner Join基于连接谓词将两张表(tb_A,tb_B)的列组合在一起，产生新的结果表–新的结果集/表是指两张表的公共部分。
  

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a INNER JOIN `user2` b ON a.`user_name` = b.`user_name`;
1

等价

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a ,`user2` b WHERE a.`user_name` = b.`user_name`;
1

2.Join操作的类型–LEFT OUTER JOIN 左外连接/左连接

• LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
  
• 1.查询的结果包含两张表的字段信息，但是如果不符合的部分，则显示为null-以左表user1为基础

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`;
1

• user1

SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;
1

• user2

SELECT b.`id`,b.`user_name`,b.`over` FROM `user2` b LIMIT 0,5;
1

• left join查询结果–查询所有
  
• 2.查询的结果包含两张表的字段信息，但是如果不符合的部分，则不显示-以左表user1为基础

SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NOT NULL;
1

• left join查询结果–去除null
  

3.Join操作的类型–RIGHT OUTER JOIN右外连接/右连接

• RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
  
• 1.查询的结果包含两张表的字段信息，但是如果不符合的部分，则显示为null-以右表user2为基础

SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
1

• right outer join查询结果–查询所有
  
• 2.查询的结果包含两张表的字段信息，但是如果不符合的部分，则不显示-以右表user2为基础

SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name` WHERE b.`user_name` IS NOT NULL;
1

4.Join操作的类型–FULL OUTER JOIN 全连接-左外连接和右外连接的合集

• FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
• FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
  
  MySQL中本身不支持Full Join
• SQL实现： 使用UNION ALL合集来实现Full Join效果

SELECT a.`user_name` 'a_username',a.`over` 'a_over',b.`user_name` 'b_username',b.`over` 'b_over' FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`
UNION ALL 
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
1
2
3

5.Join操作的类型–CROSS JOIN 交叉连接/笛卡尔集

交叉连接（cross join）,又称笛卡尔集连接（cartesian join）或叉乘（product）；
如果A和B是两个集合，他们的交叉连接记为：A x B；
笛卡尔集中，没有ON从句（没有连接关键词）；
1
2
3

SELECT a.`id`,a.`user_name`,a.`over`,b.`id`,b.`user_name`,b.`over` FROM `user1` a CROSS JOIN `user2` b;
1

三、与JOIN相关的SQL技巧

1. 如何更新使用过滤条件中包含自身的表？

情景：
把同时存在 于user1表和user2表中的记录的人在user1表中的over字段更新为齐天大圣。

UPDATE `user1` SET `over` = '齐天大圣' WHERE  `user1`.`user_name` IN (SELECT b.`user_name` FROM `user1` a  INNER JOIN `user2` b ON a.`user_name` = b.`user_name`);
1

• 执行会报错：You can’t specify target table ‘user1’ for update in FROM clause：您不能在FROM子句中为更新指定目标表’user1’
  

通过查询MySQL手册可知，MySQL不支持这种更新操作

	既然报错提示待更新的表不允许出现在from从句中，那么就不让待更新的表出现在from从句中
1

 UPDATE `user1` a JOIN (
 SELECT b.`user_name` FROM `user1` a INNER JOIN `user2` b ON a.`user_name`=b.`user_name`
 ) b ON a.`user_name` = b.`user_name` SET a.`over` ='齐天大圣';
1
2
3

	先将user1别名为a的表，和括号里边的内连接Inner join查询的结果表别名为b的表，进行JOIN，条件时a和b表的共有字段，然后进行update
1

• 验证结果

 SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;
1

技巧：

更新【自身在from从句中】的表的方式
1

2. 如何使用JOIN优化子查询？

子查询

SELECT a.`user_name`,a.`over`,(SELECT `over` FROM `user2` b WHERE a.`user_name` = b.`user_name`) AS over2 FROM `user1` a;
1

如果查询的两张表的记录非常多，子查询相当于对a表(user1表)中的每一条记录都要进行子查询，查询的效率较低。
1

使用JOIN优化子查询

SELECT a.`user_name`,a.`over`,b.`over` AS over2
FROM `user1` a
LEFT JOIN `user2` b ON
a.`user_name`=b.`user_name`;
1
2
3
4

先查出user1和user2进行左外连接LEFT JOIN查询的结结果，然后再进行最终的查询
1

3. 如何使用JOIN优化聚合子查询？

• 引入新表 ,按照日期记录user1表中的每个人打怪的数量

# CREATE table
CREATE TABLE IF NOT EXISTS `user_kills`(
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
  `timestr`  timestamp NOT NULL DEFAULT now() ON UPDATE CURRENT_TIMESTAMP COMMENT '打怪时间',
  `kills` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '打怪数量',
  FOREIGN KEY (`user_id`) REFERENCES `user1` (`id`),# 给从表做外键约束，映射主表的id
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-01-10 00:00:00',10);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-01 00:00:00',2);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-05 00:00:00',12);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (4,'2013-01-10 00:00:00',3);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-11 00:00:00',5);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-06 00:00:00',1);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (3,'2013-01-11 00:00:00',20);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-12 00:00:00',10);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-07 00:00:00',17);
# SELECT all
SELECT c.`id`,c.`user_id`,c.`timestr`,c.`kills` FROM `user_kills` c LIMIT 0,9;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

情景：
如何查询出四人组中打怪最多的日期？

SELECT 	a.`user_name`,b.`timestr`,b.`kills`
FROM `user1` a JOIN `user_kills` b
ON a.`id` = b.`user_id`
WHERE b.`kills` = (SELECT MAX(c.`kills`) FROM `user_kills` c WHERE c.`user_id` = b.`user_id`);
1
2
3
4

如何查询出四人组中打怪最多的日期？(避免子查询)

优化子查询使用用HAVING从句

SELECT a.`user_name`,b.`timestr`,b.`kills` 
FROM `user1` a
JOIN `user_kills` b ON a.`id` = b.`user_id`
JOIN `user_kills` c ON c.`user_id` = b.`user_id`
GROUP BY a.`user_name`,b.`timestr`,b.`kills`
HAVING b.`kills` = MAX(c.`kills`);
1
2
3
4
5
6

4. 如何实现分组选择？

情景

• 选择出每一个人杀怪最多的前两天头两个日期是？
• 常见查询

 SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '猪八戒'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
1
2
3
4
5
6

SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '孙悟空'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
1
2
3
4
5
6

SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '沙僧'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
1
2
3
4
5
6

问题

1.如果分类或是用户很多的情况下，则需要多次执行同一查询
2.增加应用程序同数据库交互的次数
3.增加了数据库执行查询的次数，不符合批处理的原则
4.增加了网络流量，网路的稳定性得不到稳定
1
2
3
4

优化
**使用ROW_NUMBER()**函数来实现分区排序–MySQL支持的不好，宝宝也没办法

#使用ROW_NUMBER()函数来实现分区排序--mysql没有很好的支持ROW_NUMBER()
WITH tmp AS(
SELECT a.`user_name`,b.`timestr`,b.`kills`,
ROW_NUMBER() OVER(PARTITION BY a.`user_name` 
ORDER BY b.`kills`)cnt
FROM `user1` a JOIN `user_kills` b
ON a.`id` = b.`user_id`)
SELECT * 
FROM tmp WHERE cnt <=2;
1
2
3
4
5
6
7
8
9

优化方式2：
通用优化方式

SELECT d.`user_name`,c.`timestr`,`kills`
FROM (
SELECT `user_id`,`timestr`,`kills`,
(SELECT COUNT(*)FROM `user_kills` b WHERE 
b.`user_id` = a.`user_id` AND a.`kills` <=b.`kills`)AS cnt
FROM `user_kills` as a
GROUP BY `user_id`,`timestr`,`kills`)c 
JOIN `user1` d ON c.`user_id` = d.`id`
WHERE cnt <=2;
1
2
3
4
5
6
7
8
9

	分类聚合的情景下尽量使用通用查询方式进行优化，尽可能多的一次查询到，且SQL通用性要普遍支持
1

5. 使用自连接的方法实现行转列【多行转单列】

情景

• 实现每个人打怪数的统计

SELECT a.`user_name`,SUM(b.`kills`) FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` GROUP BY `user_name`;
1

行转列【多行转单列】：使用CROSS JOIN的方式实现

# 行转列实现：使用cross join
SELECT * FROM(
SELECT SUM(`kills`) as '孙悟空' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '孙悟空'
)  a CROSS JOIN(
SELECT SUM(`kills`) as '猪八戒' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '猪八戒'
)  b CROSS JOIN(
SELECT SUM(`kills`) as '沙僧' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '沙僧'
)  c;
1
2
3
4
5
6
7
8

虽然实现了效果，但是非常的不方便，而且SQL显得过于冗余和繁琐
1

行转列：使用CASE 语句的方式实现

# 使用case语句实现
SELECT  SUM(CASE WHEN `user_name` = '孙悟空' THEN `kills` END)as '孙悟空',
SUM(CASE WHEN `user_name` = '猪八戒' THEN `kills` END)as '猪八戒',
SUM(CASE WHEN `user_name` = '沙僧' THEN `kills` END)as '沙僧'
FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id`;
1
2
3
4
5

6. 实现列转行【单列转多行】

场景1.

• 属性拆分
  
  在左侧未进行列转行的权限表中， 用户的实际权限点permission使用以逗号分隔的形式存在于权限表中，如需对权限进行查询，需要对权限转换成行的形式，把每个权限进行多行的显示，如右表形式。
  场景2.
• ETL 数据处理
  
• 实现单列转多行：1.对user1表中添加一列mobile

# 实现单列转多行
# 1.对user1表中添加一列mobile
ALTER TABLE `user1` ADD COLUMN `mobile` VARCHAR(100) COMMENT '手机号';
UPDATE `user1` SET `mobile`='121123456,141123456,161123456' WHERE `id` = 1;
UPDATE `user1` SET `mobile`='121654321,141654321' WHERE `id` = 2;
UPDATE `user1` SET `mobile`='121666666,141666666,161666666,181666666' WHERE `id` = 3;
UPDATE `user1` SET `mobile`='121987654,141987654' WHERE `id` = 4;
SELECT `id`,`user_name`,`mobile` FROM `user1` LIMIT 0,4;
1
2
3
4
5
6
7
8

• 利用序列表实现列转行的数据
• 1.创建序列表

# 1.创建序列表
CREATE TABLE IF NOT EXISTS `tb_sequence`(
`id` int auto_increment not null,
primary key(`id`)
);
# 初始化序列表的数据 
INSERT INTO `tb_sequence` values(),(),(),(),(),(),(),(),(),(),(),(),();
SELECT * FROM `tb_sequence`;
1
2
3
4
5
6
7
8

• 2.利用序列表实现列转行的数据

# 利用序列表处理列转行的数据
SELECT
	`user_name`,
	REPLACE (
		SUBSTRING(
			SUBSTRING_INDEX(`mobile`, ',', a.`id`),
			CHAR_LENGTH(
				SUBSTRING_INDEX(`mobile`, ',', a.`id` - 1)
			) + 1
		),
		',',
		''
	) AS mobile
FROM
	`tb_sequence` a
CROSS JOIN (
	SELECT
		`user_name`,
		CONCAT(`mobile`, ',') AS mobile,
		lENGTH(mobile) - lENGTH(REPLACE(mobile, ',', '')) + 1 size
	FROM
		`user1` b
) b ON a.`id` <= b.size;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SQL释义

• 1.子查询实现的是保证user1表中的mobile列是以逗号,结尾，然后通过计算mobile列有多少个逗号，用size标识

# 此子查询实现的是保证user1表中的mobile列是以逗号,结尾，然后通过计算mobile列有多少个逗号，用size标识
 SELECT `user_name`,CONCAT(`mobile`,',')AS mobile,lENGTH(mobile)-lENGTH(REPLACE(mobile,',',''))+1 size FROM `user1` b;
1
2

• 执行查询
  

• 2.使用序列表进行交叉连接，生成笛卡尔集，关联条件a.id<=b.size,实现有多少个逗号，也就是有多少个手机号，最终的结果就会显示成多少行

 # 使用序列表进行交叉连接，生成笛卡尔集，关联条件a.id<=b.size,实现有多少个逗号，也就是有多少个手机号，最终的结果就会显示成多少行
FROM `tb_sequence` a
CROSS JOIN (
	SELECT
		`user_name`,
		CONCAT(`mobile`, ',') AS mobile,
		lENGTH(mobile) - lENGTH(REPLACE(mobile, ',', '')) + 1 size
	FROM
		`user1` b
) b ON a.`id` <= b.size;
1
2
3
4
5
6
7
8
9
10

• 执行查询

SELECT * FROM `tb_sequence` a
CROSS JOIN (
	SELECT
		`user_name`,
		CONCAT(`mobile`, ',') AS mobile,
		lENGTH(mobile) - lENGTH(REPLACE(mobile, ',', '')) + 1 size
	FROM
		`user1` b
) b ON a.`id` <= b.size;
1
2
3
4
5
6
7
8
9

• 3.实现了字符串的截取

REPLACE (
		SUBSTRING(
			SUBSTRING_INDEX(`mobile`, ',', a.`id`),
			CHAR_LENGTH(
				SUBSTRING_INDEX(`mobile`, ',', a.`id` - 1)
			) + 1
		),
		',',
		''
	) AS mobile
1
2
3
4
5
6
7
8
9
10

完整SQL【基于MySQL】代码

# CREATE database
CREATE DATABASE IF NOT EXISTS `join_test` CHARACTER SET = utf8mb4;
# SWITCH database
USE `join_test`;
# CREATE Table
CREATE TABLE IF NOT EXISTS `user1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user1`(`user_name`,`over`) VALUES ('唐僧','旃檀功德佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('猪八戒','净坛使者');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('孙悟空','斗战圣佛');
INSERT INTO `user1`(`user_name`,`over`) VALUES ('沙僧','金身罗汉');
# SELECT all
SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;
# CREATE Table
CREATE TABLE IF NOT EXISTS `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `over` varchar(30) NOT NULL DEFAULT '' COMMENT '最终结果',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user2`(`user_name`,`over`) VALUES ('孙悟空','斗战胜佛');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('牛魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('蛟魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('鹏魔王','被降服');
INSERT INTO `user2`(`user_name`,`over`) VALUES ('狮驼王','被降服');
# SELECT all
SELECT b.`id`,b.`user_name`,b.`over` FROM `user2` b LIMIT 0,5;
# Inner Join
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a INNER JOIN `user2` b ON a.`user_name` = b.`user_name`;
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a ,`user2` b WHERE a.`user_name` = b.`user_name`;
# LEFT Outer Join
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`;
SELECT a.`user_name`,a.`over`,b.`user_name`,b.`over` FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name` WHERE b.`user_name` IS NOT NULL;
# RIGTH Outer Join
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name` WHERE b.`user_name` IS NOT NULL;
# Full Join
SELECT a.`user_name` 'a_username',a.`over` 'a_over',b.`user_name` 'b_username',b.`over` 'b_over' FROM `user1` a LEFT JOIN `user2` b ON a.`user_name` = b.`user_name`
UNION ALL 
SELECT b.`user_name` 'b_username',b.`over` 'b_over',a.`user_name` 'a_username',a.`over` 'a_over' FROM `user2` b RIGHT JOIN `user1` a ON b.`user_name` = a.`user_name`;
# Cross JOIN
SELECT a.`id`,a.`user_name`,a.`over`,b.`id`,b.`user_name`,b.`over` FROM `user1` a CROSS JOIN `user2` b;
 
 # 情景：把**同时存在** 于user1表和user2表中的记录的人在user1表中的over字段更新为齐天大圣。
 # 会报错 UPDATE `user1` SET `over` = '齐天大圣' WHERE  `user1`.`user_name` IN (SELECT b.`user_name` FROM `user1` a  INNER JOIN `user2` b ON a.`user_name` = b.`user_name`);
  UPDATE `user1` a JOIN (
 SELECT b.`user_name` FROM `user1` a INNER JOIN `user2` b ON a.`user_name`=b.`user_name`
 ) b ON a.`user_name` = b.`user_name` SET a.`over` ='齐天大圣';
 #验证结果
  SELECT a.`id`,a.`user_name`,a.`over` FROM `user1` a LIMIT 0,4;
 # 子查询 
SELECT a.`user_name`,a.`over`,(SELECT `over` FROM `user2` b WHERE a.`user_name` = b.`user_name`) AS over2 FROM `user1` a;
# 使用JOIN优化子查询
SELECT a.`user_name`,a.`over`,b.`over` AS over2
FROM `user1` a
LEFT JOIN `user2` b ON
a.`user_name`=b.`user_name`;
# 使用JOIN优化聚合子查询
# 引入新表 ,按照日期记录user1表中的每个人打怪的数量
# CREATE table
CREATE TABLE IF NOT EXISTS `user_kills`(
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '用户id',
  `timestr`  timestamp NOT NULL DEFAULT now() ON UPDATE CURRENT_TIMESTAMP COMMENT '打怪时间',
  `kills` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '打怪数量',
  FOREIGN KEY (`user_id`) REFERENCES `user1` (`id`),# 给从表做外键约束，映射主表的id
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-01-10 00:00:00',10);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-01 00:00:00',2);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-05 00:00:00',12);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (4,'2013-01-10 00:00:00',3);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-11 00:00:00',5);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-06 00:00:00',1);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (3,'2013-01-11 00:00:00',20);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-12 00:00:00',10);
INSERT INTO `user_kills`(`user_id`,`timestr`,`kills`) VALUES (2,'2013-02-07 00:00:00',17);
# SELECT all
SELECT c.`id`,c.`user_id`,c.`timestr`,c.`kills` FROM `user_kills` c LIMIT 0,9;
#  如何查询出四人组中打怪最多的日期？
SELECT 	a.`user_name`,b.`timestr`,b.`kills`
FROM `user1` a JOIN `user_kills` b
ON a.`id` = b.`user_id`
WHERE b.`kills` = (SELECT MAX(c.`kills`) FROM `user_kills` c WHERE c.`user_id` = b.`user_id`);
# 优化子查询：使用Having子句
SELECT a.`user_name`,b.`timestr`,b.`kills` 
FROM `user1` a
JOIN `user_kills` b ON a.`id` = b.`user_id`
JOIN `user_kills` c ON c.`user_id` = b.`user_id`
GROUP BY a.`user_name`,b.`timestr`,b.`kills`
HAVING b.`kills` = MAX(c.`kills`);
 # 如何实现分组选择
 # 选择出每一个人杀怪最多的前两天头两个日期是？
  SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '猪八戒'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
  SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '孙悟空'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
 SELECT a.`user_name`,b.`timestr`,b.`kills`
 FROM `user1` a JOIN `user_kills` b
 ON a.`id` = b.`user_id`
 WHERE a.`user_name` = '沙僧'
 ORDER BY b.`kills` DESC 
 LIMIT 0,2;
 
 #优化 
#使用ROW_NUMBER()函数来实现分区排序--mysql没有很好的支持ROW_NUMBER()
WITH tmp AS(
SELECT a.`user_name`,b.`timestr`,b.`kills`,
ROW_NUMBER() OVER(PARTITION BY a.`user_name` 
ORDER BY b.`kills`)cnt
FROM `user1` a JOIN `user_kills` b
ON a.`id` = b.`user_id`)
SELECT * 
FROM tmp WHERE cnt <=2;
# 通用优化方式
SELECT d.`user_name`,c.`timestr`,`kills`
FROM (
SELECT `user_id`,`timestr`,`kills`,
(SELECT COUNT(*)FROM `user_kills` b WHERE 
b.`user_id` = a.`user_id` AND a.`kills` <=b.`kills`)AS cnt
FROM `user_kills` as a
GROUP BY `user_id`,`timestr`,`kills`)c 
JOIN `user1` d ON c.`user_id` = d.`id`
WHERE cnt <=2;

#行转列
SELECT a.`user_name`,SUM(b.`kills`) FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` GROUP BY `user_name`;
# 行转列实现：使用cross join
SELECT * FROM(
SELECT SUM(`kills`) as '孙悟空' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '孙悟空'
)  a CROSS JOIN(
SELECT SUM(`kills`) as '猪八戒' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '猪八戒'
)  b CROSS JOIN(
SELECT SUM(`kills`) as '沙僧' FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id` WHERE a.`user_name` = '沙僧'
)  c;
# 使用case语句实现
SELECT  SUM(CASE WHEN `user_name` = '孙悟空' THEN `kills` END)as '孙悟空',
SUM(CASE WHEN `user_name` = '猪八戒' THEN `kills` END)as '猪八戒',
SUM(CASE WHEN `user_name` = '沙僧' THEN `kills` END)as '沙僧'
FROM `user1` a JOIN `user_kills` b ON a.`id` = b.`user_id`;

# 实现单列转多行
# 1.对user1表中添加一列mobile
ALTER TABLE `user1` ADD COLUMN `mobile` VARCHAR(100) COMMENT '手机号';
UPDATE `user1` SET `mobile`='121123456,141123456,161123456' WHERE `id` = 1;
UPDATE `user1` SET `mobile`='121654321,141654321' WHERE `id` = 2;
UPDATE `user1` SET `mobile`='121666666,141666666,161666666,181666666' WHERE `id` = 3;
UPDATE `user1` SET `mobile`='121987654,141987654' WHERE `id` = 4;
SELECT `id`,`user_name`,`mobile` FROM `user1` LIMIT 0,4;
# 利用序列表实现列转行的数据 
# 1.创建序列表
CREATE TABLE IF NOT EXISTS `tb_sequence`(
`id` int auto_increment not null,
primary key(`id`)
);
# 初始化序列表的数据 
INSERT INTO `tb_sequence` values(),(),(),(),(),(),(),(),(),(),(),(),();
SELECT * FROM `tb_sequence`;
# 利用序列表处理列转行的数据
SELECT
	`user_name`,
	REPLACE (
		SUBSTRING(
			SUBSTRING_INDEX(`mobile`, ',', a.`id`),
			CHAR_LENGTH(
				SUBSTRING_INDEX(`mobile`, ',', a.`id` - 1)
			) + 1
		),
		',',
		''
	) AS mobile
FROM
	`tb_sequence` a
CROSS JOIN (
	SELECT
		`user_name`,
		CONCAT(`mobile`, ',') AS mobile,
		lENGTH(mobile) - lENGTH(REPLACE(mobile, ',', '')) + 1 size
	FROM
		`user1` b
) b ON a.`id` <= b.size;
# 此子查询实现的是保证user1表中的mobile列是以逗号,结尾，然后通过计算mobile列有多少个逗号，用size标识
 SELECT `user_name`,CONCAT(`mobile`,',')AS mobile,lENGTH(mobile)-lENGTH(REPLACE(mobile,',',''))+1 size FROM `user1` b;
 # 使用序列表进行交叉连接，生成笛卡尔集，关联条件a.id<=b.size,实现有多少个逗号，也就是有多少个手机号，最终的结果就会显示成多少行
SELECT * FROM `tb_sequence` a
CROSS JOIN (
	SELECT
		`user_name`,
		CONCAT(`mobile`, ',') AS mobile,
		lENGTH(mobile) - lENGTH(REPLACE(mobile, ',', '')) + 1 size
	FROM
		`user1` b
) b ON a.`id` <= b.size;
# 实现了字符串的截取 
#REPLACE (
#		SUBSTRING(
#			SUBSTRING_INDEX(`mobile`, ',', a.`id`),
#			CHAR_LENGTH(
#				SUBSTRING_INDEX(`mobile`, ',', a.`id` - 1)
#			) + 1
#		),
#		',',
#		''
#	) AS mobile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216