MYSQL-SQL开发总结（二）-开发技巧1

1.目标

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

通过本人能够掌握：

• MySQL 使用JOIN更新表
• MySQL 使用JOIN优化子查询
• MySQL 使用JOIN优化聚合子查询
• MySQL 实现分组选择

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

2. 测试表数据

mysql> SELECT * FROM apps;
+----+----------+-------------------------+---------+
| id | app_name | url                     | country |
+----+----------+-------------------------+---------+
|  1 | QQ APP   | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+----------+-------------------------+---------+
3 rows in set (0.02 sec)

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>

3. 使用JOIN更新表

3.1 测试场景

测试 apps 表中url字段与 websites表url相等的记录中，将 apps 的country字段都更改为‘中国’，预期目标效果如下：

3.2 用JOIN更新表失败

执行更新发现会报错，update的表不能出现在from语句中，原因是mysql对子查询的支持是比较薄弱的

mysql> UPDATE apps SET country='中国' WHERE url IN (SELECT b.url FROM apps a JOIN websites b ON a.url = b.url);
ERROR 1093 (HY000): You can t specify target table 'apps' for update in FROM clause
mysql>

如下情况mysql 都会报错:

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

3.3 解决办法

官方手册推荐如下两种方式解决

内连接方式

UPDATE apps a INNER JOIN websites b ON a.url = b.url SET a.country='中国';

嵌套一层

UPDATE apps a JOIN (SELECT b.url FROM apps a JOIN websites b ON a.url = b.url) b ON a.url = b.url SET a.country='CN' ;

4.使用JOIN优化子查询

4.1 一般查询方法

mysql> SELECT a.app_name,(SELECT NAME FROM websites b WHERE a.url = b.url ) AS app_name2 FROM apps a;
+----------+-----------+
| app_name | app_name2 |
+----------+-----------+
| QQ APP   | NULL      |
| 微博 APP | 微博      |
| 淘宝 APP | 淘宝      |
+----------+-----------+
3 rows in set (0.00 sec)

mysql>

问题：子查询对apps表的每一条记录都要进行一次子查询，
数据小时，没有多大影响，如果数据量大时，则要消耗大量的查寻

4.2 JOIN优化(左连接)后的写法

mysql> SELECT a.app_name ,b.name FROM apps a LEFT JOIN  websites b ON a.url = b.url ;
+----------+------+
| app_name | name |
+----------+------+
| 淘宝 APP | 淘宝 |
| 微博 APP | 微博 |
| QQ APP   | NULL |
+----------+------+
3 rows in set (0.00 sec)

mysql>

5.使用JOIN优化聚合子查询

5.1 测试场景

统计website 表中不同应用类型在 access_log 表中登录count最大的记录中的日期。

5.2 一般写法，未能解决

无法达到预期效果

mysql> SELECT w.name,MAX(a.count),a.date FROM websites  AS w JOIN access_log a ON w.id = a.site_id GROUP BY w.name ,a.date ;
+----------+--------------+------------+
| name     | MAX(a.count) | date       |
+----------+--------------+------------+
| Facebook |          205 | 2016-05-14 |
| Facebook |          545 | 2016-05-16 |
| Google   |           45 | 2016-05-10 |
| Google   |          230 | 2016-05-14 |
| 微博     |           13 | 2016-05-15 |
| 淘宝     |           10 | 2016-05-14 |
| 菜鸟教程 |          100 | 2016-05-13 |
| 菜鸟教程 |          220 | 2016-05-15 |
| 菜鸟教程 |          201 | 2016-05-17 |
+----------+--------------+------------+
9 rows in set (0.00 sec)

mysql>

5.3 使用聚合子查询实现

mysql> SELECT w.name,a.count,a.date FROM websites  AS w JOIN access_log a ON w.id = a.site_id WHERE a.count = (SELECT MAX(c.count) FROM access_log c WHERE w.id = c.site_id);
+----------+-------+------------+
| name     | count | date       |
+----------+-------+------------+
| Google   |   230 | 2016-05-14 |
| 淘宝     |    10 | 2016-05-14 |
| 菜鸟教程 |   220 | 2016-05-15 |
| 微博     |    13 | 2016-05-15 |
| Facebook |   545 | 2016-05-16 |
+----------+-------+------------+
5 rows in set (0.03 sec)

mysql>

5.4 使用JOIN优化聚合子查询

优化查询效率

SELECT
  w.name,
  a.count,
  a.date
FROM websites AS w
  JOIN access_log a
    ON w.id = a.site_id
  JOIN access_log b
    ON w.id = b.site_id
GROUP BY w.name,a.date,a.count
HAVING a.count = MAX(b.count);

mysql> SELECT w.name,a.count,a.date FROM websites  AS w JOIN access_log a ON w.id = a.site_id JOIN access_log b ON w.id = b.site_id GROUP BY w.name ,a.date,a.count HAVING a.count = MAX(b.count);
+----------+-------+------------+
| name     | count | date       |
+----------+-------+------------+
| Facebook |   545 | 2016-05-16 |
| Google   |   230 | 2016-05-14 |
| 微博     |    13 | 2016-05-15 |
| 淘宝     |    10 | 2016-05-14 |
| 菜鸟教程 |   220 | 2016-05-15 |
+----------+-------+------------+
5 rows in set (0.03 sec)

mysql>

6.实现分组选择

6.1 测试场景

查询website表中每种name的app在access_log 表中count字段数据最大的两条数据

6.2 传统模式

Google 公司网站访问次数最多的两条记录

mysql> SELECT w.name,a.date,a.count FROM websites AS w JOIN access_log a ON w.id = a.site_id WHERE w.name = 'Google' ORDER BY a.count DESC LIMIT 2 ;
+--------+------------+-------+
| name   | date       | count |
+--------+------------+-------+
| Google | 2016-05-14 |   230 |
| Google | 2016-05-10 |    45 |
+--------+------------+-------+
2 rows in set (0.00 sec)

mysql>

Facebook 公司网站访问次数最多的两条记录

mysql> SELECT w.name,a.date,a.count FROM websites AS w JOIN access_log a ON w.id = a.site_id WHERE w.name = 'Facebook' ORDER BY a.count DESC LIMIT 2 ;
+----------+------------+-------+
| name     | date       | count |
+----------+------------+-------+
| Facebook | 2016-05-16 |   545 |
| Facebook | 2016-05-14 |   205 |
+----------+------------+-------+
2 rows in set (0.00 sec)

mysql>

菜鸟教程公司网站访问次数最多的两条记录

mysql> SELECT w.name,a.date,a.count FROM websites AS w JOIN access_log a ON w.id = a.site_id WHERE w.name = '菜鸟教程' ORDER BY a.count DESC LIMIT 2 ;
+----------+------------+-------+
| name     | date       | count |
+----------+------------+-------+
| 菜鸟教程 | 2016-05-15 |   220 |
| 菜鸟教程 | 2016-05-17 |   201 |
+----------+------------+-------+
2 rows in set (0.01 sec)

mysql>

缺点：

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

6.2 通过join和子查询实现分组

SELECT
  d.name,
  c.date,
  c.count
FROM (SELECT
        a.aid,
        a.site_id,
        a.date,
        a.count,
        (SELECT
           COUNT(*)
         FROM access_log b
         WHERE b.site_id = a.site_id
             AND a.count <= b.count) AS cnt
      FROM access_log a
      GROUP BY a.aid,a.date,a.count) c
  JOIN websites d
    ON c.site_id = d.id
WHERE cnt <= 2;

结果：

+----------+------------+-------+
| name     | date       | count |
+----------+------------+-------+
| Google   | 2016-05-14 |   230 |
| Google   | 2016-05-10 |    45 |
| 淘宝     | 2016-05-14 |    10 |
| 菜鸟教程 | 2016-05-15 |   220 |
| 菜鸟教程 | 2016-05-17 |   201 |
| 微博     | 2016-05-15 |    13 |
| Facebook | 2016-05-16 |   545 |
| Facebook | 2016-05-14 |   205 |
+----------+------------+-------+
8 rows in set (0.00 sec)

mysql>

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

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

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