MYSQL-SQL开发总结（三）-开发技巧2

0. 目标

通过本文可以了解在使用mysql数据库开发时，一些开发技巧。本文比较基础，大神请绕道。

通过本人能够掌握：

• MySQL 行列转换
• MySQL 列转行
• MySQL 行转列
• MySQL 生成唯一序列号
• MySQL 删除重复数据

使用数据及导入方法见MYSQL-SQL开发总结（一）-SQL基础

1. 测试表数据

mysql> SELECT * FROM websites;
+----+----------+---------------------------+-------+---------+
| id | name     | url                       | alexa | country |
+----+----------+---------------------------+-------+---------+
|  1 | Google   | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博     | http://weibo.com/         |    20 | CN      |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |
+----+----------+---------------------------+-------+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

mysql>

mysql> SELECT w.name ,a.count FROM websites w JOIN access_log a ON w.id = a.site_id;
+----------+-------+
| name     | count |
+----------+-------+
| Google   |    45 |
| Google   |   230 |
| 淘宝     |    10 |
| 菜鸟教程 |   100 |
| 菜鸟教程 |   220 |
| 菜鸟教程 |   201 |
| 微博     |    13 |
| Facebook |   205 |
| Facebook |   545 |
+----------+-------+
9 rows in set (0.01 sec)

2.行转列场景

欲实现效果，将各个公司网站访问总数统一在一行显示。实现效果如下：

2.1 使用自连接方法实现行转列

思路 我们可以通过单独查询其中一种应用总的访问次数，然后在对几种结果通过cross join 聚合。

• Google 公司网站访问次数总计

mysql> SELECT SUM(COUNT) AS 'Google' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = 'Google';
+--------+
| Google |
+--------+
|    275 |
+--------+
1 row in set (0.00 sec)

mysql>

• Facebook公司网站访问次数总计

mysql> SELECT SUM(COUNT) AS 'Facebook' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = 'Facebook';
+----------+
| Facebook |
+----------+
|      750 |
+----------+
1 row in set (0.00 sec)

mysql>

• 菜鸟教程公司网站访问次数总计

mysql> SELECT SUM(COUNT) AS '菜鸟教程' FROM access_log a JOIN websites w ON w.id = a.site_id AND w.name = '菜鸟教程';
+----------+
| 菜鸟教程 |
+----------+
|      521 |
+----------+
1 row in set (0.00 sec)

mysql>

• 通过cross join 结果聚合

SELECT *
FROM (SELECT
        SUM(COUNT) AS 'Google'
      FROM access_log a
        JOIN websites w
          ON w.id = a.site_id
            AND w.name = 'Google') G
  CROSS JOIN (SELECT
                SUM(COUNT) AS 'Facebook'
              FROM access_log a
                JOIN websites w
                  ON w.id = a.site_id
                    AND w.name = 'Facebook') F
  CROSS JOIN (SELECT
                SUM(COUNT) AS '菜鸟教程'
              FROM access_log a
                JOIN websites w
                  ON w.id = a.site_id
                    AND w.name = '菜鸟教程') C;

mysql>
+--------+----------+----------+
| Google | Facebook | 菜鸟教程 |
+--------+----------+----------+
|    275 |      750 |      521 |
+--------+----------+----------+
1 row in set (0.01 sec)

mysql>

缺点： 是将原来查询的结果每一行单独查询出来，再进行拼接。因此每增加一个同学就增加一个SELECT语句。并且是通过交叉连接，要保证每个查询的结果只能是一个，不然没办法通过交叉连接实现转换。

2.2 使用CASE实现行转列

SELECT
  SUM(CASE WHEN NAME = 'Google' THEN COUNT END ) AS 'Google',
  SUM(CASE WHEN NAME = 'Facebook' THEN COUNT END ) AS 'Facebook',
  SUM(CASE WHEN NAME = '菜鸟教程' THEN COUNT END ) AS '菜鸟教程'
FROM websites w
  JOIN access_log a
    ON w.id = a.site_id;

3 列转行场景

3.1 单列转多行场景

3.1.1 单列转多行场景实际用途

• （1）属性拆分
• （2）ETL数据处理

3.1.2 单列转多行场景演示-权限拆分

原始数据建表语句及数据

Create Table: CREATE TABLE `user_roles` (
  `user_roles_id` int(11) DEFAULT NULL,
  `role_name` varchar(100) DEFAULT NULL,
  `major_roles` varchar(100) DEFAULT NULL,
  `creation_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql>

INSERT INTO USER_ROLES
            (USER_ROLES_ID,
             ROLE_NAME,
             MAJOR_ROLES,
             CREATION_TIME)
VALUES (1,
        'middleware',
        '2,21,22,23,24',
        CURTIME());
INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
VALUES (2, 'db','9,10', CURTIME());
INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
VALUES (3, 'SYSTEM',  '4,6,7,8', CURTIME());
INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
VALUES (4, 'bcy109', '21,22,25,26,27,28,23,24,16',CURTIME());
INSERT INTO USER_ROLES (USER_ROLES_ID, ROLE_NAME, MAJOR_ROLES, CREATION_TIME)
VALUES (5, 'bcytest', '17,18,23,24',CURTIME());

表数据

预期实现目标

3.1.3 使用序列化表的方法实现行转列

建立序列表：

CREATE TABLE  tb_sequence(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO tb_sequence VALUES(),(),(),(),(),(),(),(),();

mysql> SELECT * FROM tb_sequence;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
9 rows in set (0.01 sec)

mysql>

最终语句

SELECT b.user_roles_id,b.role_name,
REPLACE(SUBSTRING(SUBSTRING_INDEX(major_roles,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(major_roles,',',a.id-1))+1),',','') AS major_roles FROM tb_sequence a CROSS JOIN(SELECT user_roles_id,role_name,CONCAT(major_roles,',') AS major_roles,
LENGTH(major_roles)-LENGTH(REPLACE(major_roles,',',''))+1 size
FROM user_roles b ORDER BY b.user_roles_id) b ON a.id<=b.size;

语句解读

• 内部子查询：目的是在 major_roles 每行数据增加一个逗号，在用总长度减去没有逗号的情况时的长度 = size （表示有几个权限，将要转成几行记录）

mysql> SELECT user_roles_id,role_name,CONCAT(major_roles,',') AS major_roles,
    -> LENGTH(major_roles)-LENGTH(REPLACE(major_roles,',',''))+1 size
    -> FROM user_roles b ORDER BY b.user_roles_id;
+---------------+------------+-----------------------------+------+
| user_roles_id | role_name  | major_roles                 | size |
+---------------+------------+-----------------------------+------+
|             1 | middleware | 2,21,22,23,24,              |    5 |
|             2 | db         | 9,10,                       |    2 |
|             3 | SYSTEM     | 4,6,7,8,                    |    4 |
|             4 | bcy109     | 21,22,25,26,27,28,23,24,16, |    9 |
|             5 | bcytest    | 17,18,23,24,                |    4 |
+---------------+------------+-----------------------------+------+
5 rows in set (0.00 sec)

mysql>

• 外部查询：外层查询根据序列表 cross join 子查询结果

3.2 多列转多行场景

3.2.1 多列转多行场景实际用途

• （1）学生成绩查询
• （2） ETL 数据抽取

3.2.2 多列转多行案例分析-学生成绩行列转换

建表语句及原始数据插入语句

CREATE TABLE `TB_GRADE` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` VARCHAR(20) DEFAULT NULL,
  `CN_SCORE` FLOAT DEFAULT '0',
  `MATCH_SCORE` FLOAT DEFAULT '0',
  `EN_SCORE` FLOAT DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO TB_GRADE(USER_NAME, CN_SCORE, MATCH_SCORE,EN_SCORE)  VALUES
("张三",58 ,34,72),
("李四",87 ,79,72),
("王五",88 ,76,90);

表中原始数据

mysql> SELECT * FROM TB_GRADE;
+----+-----------+----------+-------------+----------+
| ID | USER_NAME | CN_SCORE | MATCH_SCORE | EN_SCORE |
+----+-----------+----------+-------------+----------+
|  1 | 张三      |       58 |          34 |       72 |
|  2 | 李四      |       87 |          79 |       72 |
|  3 | 王五      |       88 |          76 |       90 |
+----+-----------+----------+-------------+----------+
3 rows in set (0.02 sec)

mysql>

预期实现目标

+-----------+-------------+------+
| user_name | 学科        | 成绩 |
+-----------+-------------+------+
| 张三      | CN_SCORE    |   58 |
| 李四      | CN_SCORE    |   87 |
| 王五      | CN_SCORE    |   88 |
| 张三      | MATCH_SCORE |   34 |
| 李四      | MATCH_SCORE |   79 |
| 王五      | MATCH_SCORE |   76 |
| 张三      | EN_SCORE    |   72 |
| 李四      | EN_SCORE    |   72 |
| 王五      | EN_SCORE    |   90 |
+-----------+-------------+------+

3.2.3 使用UNION方法实现列转行

语句拆分解读

mysql> SELECT user_name,'CN_SCORE' AS '语文',CN_SCORE AS '成绩' FROM TB_GRADE a;
+-----------+----------+------+
| user_name | 语文     | 成绩 |
+-----------+----------+------+
| 张三      | CN_SCORE |   58 |
| 李四      | CN_SCORE |   87 |
| 王五      | CN_SCORE |   88 |
+-----------+----------+------+
3 rows in set (0.00 sec)

mysql> SELECT user_name,'MATCH_SCORE' AS '数学',MATCH_SCORE AS '成绩' FROM TB_GRADE a;
+-----------+-------------+------+
| user_name | 数学        | 成绩 |
+-----------+-------------+------+
| 张三      | MATCH_SCORE |   34 |
| 李四      | MATCH_SCORE |   79 |
| 王五      | MATCH_SCORE |   76 |
+-----------+-------------+------+
3 rows in set (0.00 sec)

mysql> SELECT user_name,'EN_SCORE' AS '英语',EN_SCORE AS '成绩' FROM TB_GRADE a;
+-----------+----------+------+
| user_name | 英语     | 成绩 |
+-----------+----------+------+
| 张三      | EN_SCORE |   72 |
| 李四      | EN_SCORE |   72 |
| 王五      | EN_SCORE |   90 |
+-----------+----------+------+
3 rows in set (0.00 sec)

最终语句

SELECT user_name,'CN_SCORE' AS '学科',CN_SCORE AS '成绩' FROM TB_GRADE a
UNION ALL
SELECT user_name,'MATCH_SCORE' AS '学科',MATCH_SCORE AS '成绩' FROM TB_GRADE a
UNION ALL
SELECT user_name,'EN_SCORE' AS '学科',EN_SCORE AS '成绩' FROM TB_GRADE a;

最终效果

mysql> SELECT user_name,'CN_SCORE' AS '学科',CN_SCORE AS '成绩' FROM TB_GRADE a
    -> UNION ALL
    -> SELECT user_name,'MATCH_SCORE' AS '学科',MATCH_SCORE AS '成绩' FROM TB_GRADE a
    -> UNION ALL
    -> SELECT user_name,'EN_SCORE' AS '学科',EN_SCORE AS '成绩' FROM TB_GRADE a;
+-----------+-------------+------+
| user_name | 学科        | 成绩 |
+-----------+-------------+------+
| 张三      | CN_SCORE    |   58 |
| 李四      | CN_SCORE    |   87 |
| 王五      | CN_SCORE    |   88 |
| 张三      | MATCH_SCORE |   34 |
| 李四      | MATCH_SCORE |   79 |
| 王五      | MATCH_SCORE |   76 |
| 张三      | EN_SCORE    |   72 |
| 李四      | EN_SCORE    |   72 |
| 王五      | EN_SCORE    |   90 |
+-----------+-------------+------+
9 rows in set (0.01 sec)

mysql>

3.2.4 使用序列化表的方法实现列转行

语句拆分解析

• 通过序列固定获取学科

mysql> SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科'
    -> FROM   TB_GRADE a
    -> CROSS JOIN tb_sequence c WHERE c.id<=3 ;
+-----------+-------------+
| user_name | 学科        |
+-----------+-------------+
| 张三      | CN_SCORE    |
| 李四      | CN_SCORE    |
| 王五      | CN_SCORE    |
| 张三      | MATCH_SCORE |
| 李四      | MATCH_SCORE |
| 王五      | MATCH_SCORE |
| 张三      | EN_SCORE    |
| 李四      | EN_SCORE    |
| 王五      | EN_SCORE    |
+-----------+-------------+
9 rows in set (0.00 sec)

mysql>

• 通过序列固定获取成绩

mysql> SELECT user_name, COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
    -> FROM   TB_GRADE a
    -> CROSS JOIN tb_sequence c WHERE c.id<=3
    -> ;
+-----------+------+
| user_name | 成绩 |
+-----------+------+
| 张三      |   58 |
| 李四      |   87 |
| 王五      |   88 |
| 张三      |   34 |
| 李四      |   79 |
| 王五      |   76 |
| 张三      |   72 |
| 李四      |   72 |
| 王五      |   90 |
+-----------+------+
9 rows in set (0.00 sec)

mysql>

• 最终查询

SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科',
COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
FROM   TB_GRADE a
CROSS JOIN tb_sequence c WHERE c.id<=3 ORDER BY user_name;

mysql> SELECT user_name, CASE WHEN c.id=1 THEN 'CN_SCORE' WHEN c.id=2 THEN 'MATCH_SCORE' WHEN c.id=3 THEN 'EN_SCORE' END AS '学科',
    -> COALESCE(CASE WHEN c.id=1 THEN CN_SCORE END, CASE WHEN c.id=2 THEN MATCH_SCORE END, CASE WHEN c.id=3 THEN EN_SCORE END) AS '成绩'
    -> FROM   TB_GRADE a
    -> CROSS JOIN tb_sequence c WHERE c.id<=3 ORDER BY user_name;
+-----------+-------------+------+
| user_name | 学科        | 成绩 |
+-----------+-------------+------+
| 张三      | MATCH_SCORE |   34 |
| 张三      | EN_SCORE    |   72 |
| 张三      | CN_SCORE    |   58 |
| 李四      | CN_SCORE    |   87 |
| 李四      | MATCH_SCORE |   79 |
| 李四      | EN_SCORE    |   72 |
| 王五      | CN_SCORE    |   88 |
| 王五      | MATCH_SCORE |   76 |
| 王五      | EN_SCORE    |   90 |
+-----------+-------------+------+
9 rows in set (0.00 sec)

mysql>

4. 生成唯一序列号

4.1 唯一序列号场景及序列号生成方式

需要使用唯一序列号的场景：

1. 作为数据库主键。

2. 业务序列号。

生成序列号的方法：

• MySQL：AUTO_INCREMENT

• SQLServer：IDENTITY/SEQUENCE

• Oracle：SEQUENCE

• PgSQL：SEQUENCE

如何选择生成序列号的方式：

【原则】：优先选择系统提供的序列号生成方式。

【优点】：

1. 控制并发；

2. 不重复，保证序列号的唯一性。

【缺点】：序列号不连续（数据空洞），例如 1、2、4。

【原因】：对已有的数据的删除，以及事务回滚等方式不会影响自增长的序号，例如已有数据 1、2、3，删除 3 号数据。之后再插入一条数据，此时数据表的数据为 1、2、4。

4.2 建立特殊需求的序列号

创建订单号，订单序列号格式如下：YYYYMMDDnnnnnnn。如201810170000002

基础表

CREATE TABLE order_seq(
timestr INT UNSIGNED,
order_sn INT UNSIGNED
);

存储过程

DELIMITER //
  CREATE PROCEDURE seq_no()
  BEGIN
	  DECLARE v_cnt   INT;
	  DECLARE v_timestr INT;
	  DECLARE rowcount BIGINT;
	  SET     v_timestr = DATE_FORMAT(NOW(), '%Y%m%d');
	  SELECT  ROUND(RAND() * 100, 0) + 1 INTO v_cnt;
	  START   TRANSACTION;
		UPDATE  order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;
		IF      
			ROW_COUNT() = 0 THEN INSERT INTO order_seq(timestr, order_sn) VALUES(v_timestr,v_cnt);
		END IF;
		SELECT CONCAT(v_timestr, LPAD(order_sn, 7, 0)) AS order_sn
			FROM order_seq
		WHERE timestr = v_timestr;
	  COMMIT;
  END
//
DELIMITER ;

结果展示
此存储过程经测试，每秒中可以生成1千个订单号

mysql> call seq_no();
+-----------------+
| order_sn        |
+-----------------+
| 201810170000135 |
+-----------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> select * from order_seq;
+----------+----------+
| timestr  | order_sn |
+----------+----------+
| 20181017 |      135 |
+----------+----------+
1 row in set (0.00 sec)

mysql>

知识点：

• 1、在sql语句中添加变量。 declare @local_variable data_type 声明时需要指定变量的类型，可以使用SET、SELECT、SELECT…INTO对变量进行赋值，在sql语句中就可以使用@local_variable来调用变量。

• 2、RAND()返回一个介于 0 到 1（不包括 0 和 1）之间的伪随机 float 值。

• 3、事务

• 4、ROW_COUNT()函数返回查询语句执行后，被影响的列数目

• 5、IF…THEN…END IF;

5. 删除重复数据

5.1 查询数据是否重复

mysql> SELECT * FROM runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2017-04-12      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java    | RUNOOB.COM    | 2015-05-01      |
|         4 | 学习 Python  | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
5 rows in set (0.00 sec)

mysql>
mysql> SELECT runoob_author,COUNT(*) FROM runoob_tbl GROUP BY runoob_author HAVING COUNT(*)>1;
+---------------+----------+
| runoob_author | COUNT(*) |
+---------------+----------+
| RUNOOB.COM    |        2 |
| 菜鸟教程      |        2 |
+---------------+----------+
2 rows in set (0.00 sec)

mysql>

5.2 删除重复数据，对于相同数据保留ID最大的

sql 语句

DELETE a
FROM runoob_tbl a
  JOIN (SELECT
          runoob_author,
          COUNT(*),
          MAX(runoob_id) AS runoob_id
        FROM runoob_tbl
        GROUP BY runoob_author
        HAVING COUNT( * ) > 1) b
    ON a.runoob_author = b.runoob_author
WHERE a.runoob_id < b.runoob_id;

效果

mysql> DELETE a
    -> FROM runoob_tbl a
    ->   JOIN (SELECT
    ->           runoob_author,
    ->           COUNT(*),
    ->           MAX(runoob_id) AS runoob_id
    ->         FROM runoob_tbl
    ->         GROUP BY runoob_author
    ->         HAVING COUNT( * ) > 1) b
    ->     ON a.runoob_author = b.runoob_author
    -> WHERE a.runoob_id < b.runoob_id;
Query OK, 2 rows affected, 1 warning (0.22 sec)

mysql> select * from  runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | 学习 MySQL   | 菜鸟教程      | 2017-04-12      |
|         4 | 学习 Python  | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C       | FK            | 2017-04-05      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql>

6 希望大家手动敲一遍代码，会收获颇丰！

7 参考

菜鸟教程 http://www.runoob.com/sql/sql-tutorial.html

慕课网sqlercn老师-mysql 开发技巧 https://www.imooc.com/learn/427

10.欢迎关注米宝窝，持续更新中，谢谢！

米宝窝 https://rocklei123.github.io/