MYSQL-SQL开发总结（一）-SQL基础

rocklei123的技术点滴 2018-10-18

406

1.目标

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

通过本人能够掌握：

MySQL UNION
MySQL 排序
MySQL 分组
Mysql 连接的使用
MySQL 导入数据

2.环境准备

本教程的 SQL 在 MySQL 中测试通过。使用基础数据下载地址：sql 教程

本教程使用到的 Websites 表 SQL 文件：websites.sql。
本教程使用到的 accesslog 表 SQL 文件：accesslog.sql。
本教程使用到的 apps 表 SQL 文件：apps.sql。
Mysql join 使用到的表结构下载地址： runoob-mysql-join-test.sql
mysql 分组使用到的 employee_tbl 表语句如下：

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` INT(11) NOT NULL,
  `name` CHAR(10) NOT NULL DEFAULT '',
  `date` DATETIME NOT NULL,
  `singin` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

2.1 建库准备

创建数据库，该命令的作用：

如果数据库不存在则创建，存在则不创建。
创建RUNOOB数据库，并设定编码集为utf8

CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2.2 导入数据

通过sqlyog 工具或其他图形化工具的 import功能

登录mysql cli 执行：source /home/abc/abc.sql;

mysql> use abc;                  # 使用已创建的数据库
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

mysql -uroot -p123456 < runoob.sql

3 开发技巧整理

3.1 MySQL 排序

案例分析

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

mysql>

ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序；

1）、先将country值这一列排序，同为CN的排前面，同属USA的排后面；
2）、然后在同属CN的这些多行数据中，再根据alexa值的大小排列。
3）、ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC(升序)。

多列排序注意事项

ORDER BY 多列的时候，eg:

order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A ,B desc 这个时候 A 升序，B 降序排列

即 desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。

3.2 MySQL UNION 操作符

3.2.1 MySQL UNION 语法及注意事项

* 语法 *

MySQL UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

* 参数 *expression1, expression2, ... expression_n: 要检索的列。

tables: 要检索的数据表。

WHERE conditions: 可选，检索条件。

DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

ALL: 可选，返回所有结果集，包含重复数据

* 请注意 * ，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

3.2.2 测试表原始数据（JOIN测试时也需要此表数据演示）

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>

3.2.3 MySQL UNION 实例演示

* SQL UNION 实例（无重复数据）*

mysql> SELECT country,url FROM websites UNION  SELECT country,url FROM apps ORDER BY country ;
+---------+---------------------------+
| country | url                       |
+---------+---------------------------+
| CN      | http://weibo.com/         |
| CN      | http://im.qq.com/         |
| CN      | https://www.taobao.com/   |
| CN      | http://www.runoob.com/    |
| USA     | https://www.facebook.com/ |
| USA     | https://www.google.cm/    |
+---------+---------------------------+
6 rows in set (0.00 sec)

mysql>

* SQL UNION ALL 实例（存在重复数据） *

mysql> SELECT country,url FROM websites UNION ALL SELECT country,url FROM apps ORDER BY country ;
+---------+---------------------------+
| country | url                       |
+---------+---------------------------+
| CN      | http://weibo.com/         |
| CN      | http://im.qq.com/         |
| CN      | http://weibo.com/         |
| CN      | https://www.taobao.com/   |
| CN      | https://www.taobao.com/   |
| CN      | http://www.runoob.com/    |
| USA     | https://www.facebook.com/ |
| USA     | https://www.google.cm/    |
+---------+---------------------------+
8 rows in set (0.00 sec)

mysql>

* 带有 WHERE 的 SQL UNION ALL *

mysql> SELECT country,url FROM websites WHERE country = 'CN' UNION ALL SELECT country,url FROM apps WHERE country = 'CN' ORDER BY country;
+---------+-------------------------+
| country | url                     |
+---------+-------------------------+
| CN      | http://im.qq.com/       |
| CN      | http://weibo.com/       |
| CN      | https://www.taobao.com/ |
| CN      | https://www.taobao.com/ |
| CN      | http://www.runoob.com/  |
| CN      | http://weibo.com/       |
+---------+-------------------------+
6 rows in set (0.00 sec)

mysql>

3.3 MySQL 分组

* 原始数据 *

mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

* GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录,并排序 *

mysql> SELECT NAME,COUNT(*) AS count_num FROM employee_tbl GROUP BY NAME ORDER BY count_num DESC ;
+------+-----------+
| NAME | count_num |
+------+-----------+
| 小明 |         3 |
| 小王 |         2 |
| 小丽 |         1 |
+------+-----------+
3 rows in set (0.03 sec)

mysql>

* 使用 WITH ROLLUP *

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。（目的看谁登录次数最多）

mysql> SELECT NAME,SUM(singin) AS singin_count FROM employee_tbl GROUP BY NAME WITH ROLLUP;
+------+--------------+
| NAME | singin_count |
+------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL |           16 |
+------+--------------+
4 rows in set (0.00 sec)

mysql>

其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT COALESCE(NAME,'总数'),SUM(singin) FROM employee_tbl GROUP BY NAME WITH ROLLUP;
+-----------------------+-------------+
| COALESCE(NAME,'总数') | SUM(singin) |
+-----------------------+-------------+
| 小丽                  |           2 |
| 小明                  |           7 |
| 小王                  |           7 |
| 总数                  |          16 |
+-----------------------+-------------+
4 rows in set (0.01 sec)

mysql>

* 注意事项 *

1、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：

SELECT name FROM employee_tb1 GROUP BY name;返回的结果集就是所有员工的名字。
SELECT DISTINCT NAME FROM employee_tbl;

* 哪种效率更高呢？<做过对比测试的兄弟希望能给出你的建议> *

2、分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：

mysql> SELECT NAME,SUM(singin) AS singin_count FROM employee_tbl WHERE id <>  1 GROUP BY NAME HAVING singin_count > 5 ORDER BY singin_count DESC ;
+------+--------------+
| NAME | singin_count |
+------+--------------+
| 小王 |            7 |
| 小明 |            6 |
+------+--------------+
2 rows in set (0.00 sec)

mysql>

3.4 MySQL 连接JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

3.4.1 内连接

* INNER JOIN *

INNER JOIN：如果表中有至少一个匹配，则返回行

mysql> SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a INNER JOIN websites b  ON a.country = b.country;
+---------+----------+-------------------------+---------+----------+-------+
| country | app_name | url                     | country | name     | alexa |
+---------+----------+-------------------------+---------+----------+-------+
| CN      | QQ APP   | http://im.qq.com/       | CN      | 淘宝     |    13 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 淘宝     |    13 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 淘宝     |    13 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 菜鸟教程 |  4689 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 微博     |    20 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 微博     |    20 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 微博     |    20 |
+---------+----------+-------------------------+---------+----------+-------+
9 rows in set (0.00 sec)

mysql>

3.4.1 外连接

* LEFT JOIN *

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

mysql> SELECT a.country ,a.NAME ,a.url,a.alexa,b.country ,b.app_name FROM websites a LEFT JOIN apps b  ON a.country = b.country;
+---------+----------+---------------------------+-------+---------+----------+
| country | NAME     | url                       | alexa | country | app_name |
+---------+----------+---------------------------+-------+---------+----------+
| CN      | 淘宝     | https://www.taobao.com/   |    13 | CN      | QQ APP   |
| CN      | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      | QQ APP   |
| CN      | 微博     | http://weibo.com/         |    20 | CN      | QQ APP   |
| CN      | 淘宝     | https://www.taobao.com/   |    13 | CN      | 微博 APP |
| CN      | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      | 微博 APP |
| CN      | 微博     | http://weibo.com/         |    20 | CN      | 微博 APP |
| CN      | 淘宝     | https://www.taobao.com/   |    13 | CN      | 淘宝 APP |
| CN      | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      | 淘宝 APP |
| CN      | 微博     | http://weibo.com/         |    20 | CN      | 淘宝 APP |
| USA     | Google   | https://www.google.cm/    |     1 | NULL    | NULL     |
| USA     | Facebook | https://www.facebook.com/ |     3 | NULL    | NULL     |
+---------+----------+---------------------------+-------+---------+----------+
11 rows in set (0.00 sec)

mysql>

* RIGHT JOIN *

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

mysql> SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a RIGHT JOIN websites b  ON a.country = b.country;
+---------+----------+-------------------------+---------+----------+-------+
| country | app_name | url                     | country | name     | alexa |
+---------+----------+-------------------------+---------+----------+-------+
| CN      | QQ APP   | http://im.qq.com/       | CN      | 淘宝     |    13 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 微博     |    20 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 淘宝     |    13 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 微博     |    20 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 淘宝     |    13 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 菜鸟教程 |  4689 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 微博     |    20 |
| NULL    | NULL     | NULL                    | USA     | Google   |     1 |
| NULL    | NULL     | NULL                    | USA     | Facebook |     3 |
+---------+----------+-------------------------+---------+----------+-------+
11 rows in set (0.01 sec)

mysql>

* FULL JOIN *

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

MySQL 中不支持Full JOIN 连接，错误如下:

mysql> SELECT a.country ,a.app_name ,a.url,b.country ,b.name FROM apps a  FULL JOIN websites b  ON a.country = b.country;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN websites b  ON a.country = b.country' at line 1
mysql>

技巧1 （使MYSQL 支持Full JOIN ，通过UNION ALL 代替:

SELECT a.country ,a.NAME ,a.url,a.alexa,b.country ,b.app_name FROM websites a LEFT JOIN apps b  ON a.country = b.country
UNION ALL
SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a RIGHT JOIN websites b  ON a.country = b.country

mysql> SELECT a.country ,a.NAME ,a.url,a.alexa,b.country ,b.app_name FROM websites a LEFT JOIN apps b  ON a.country = b.country
    -> UNION ALL
    -> SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a RIGHT JOIN websites b  ON a.country = b.country;
+---------+----------+---------------------------+-------+----------+----------+
| country | NAME     | url                       | alexa | country  | app_name |
+---------+----------+---------------------------+-------+----------+----------+
| CN      | 淘宝     | https://www.taobao.com/   | 13    | CN       | QQ APP   |
| CN      | 菜鸟教程 | http://www.runoob.com/    | 4689  | CN       | QQ APP   |
| CN      | 微博     | http://weibo.com/         | 20    | CN       | QQ APP   |
| CN      | 淘宝     | https://www.taobao.com/   | 13    | CN       | 微博 APP |
| CN      | 菜鸟教程 | http://www.runoob.com/    | 4689  | CN       | 微博 APP |
| CN      | 微博     | http://weibo.com/         | 20    | CN       | 微博 APP |
| CN      | 淘宝     | https://www.taobao.com/   | 13    | CN       | 淘宝 APP |
| CN      | 菜鸟教程 | http://www.runoob.com/    | 4689  | CN       | 淘宝 APP |
| CN      | 微博     | http://weibo.com/         | 20    | CN       | 淘宝 APP |
| USA     | Google   | https://www.google.cm/    | 1     | NULL     | NULL     |
| USA     | Facebook | https://www.facebook.com/ | 3     | NULL     | NULL     |
| CN      | QQ APP   | http://im.qq.com/         | CN    | 淘宝     | 13       |
| CN      | QQ APP   | http://im.qq.com/         | CN    | 菜鸟教程 | 4689     |
| CN      | QQ APP   | http://im.qq.com/         | CN    | 微博     | 20       |
| CN      | 微博 APP | http://weibo.com/         | CN    | 淘宝     | 13       |
| CN      | 微博 APP | http://weibo.com/         | CN    | 菜鸟教程 | 4689     |
| CN      | 微博 APP | http://weibo.com/         | CN    | 微博     | 20       |
| CN      | 淘宝 APP | https://www.taobao.com/   | CN    | 淘宝     | 13       |
| CN      | 淘宝 APP | https://www.taobao.com/   | CN    | 菜鸟教程 | 4689     |
| CN      | 淘宝 APP | https://www.taobao.com/   | CN    | 微博     | 20       |
| NULL    | NULL     | NULL                      | USA   | Google   | 1        |
| NULL    | NULL     | NULL                      | USA   | Facebook | 3        |
+---------+----------+---------------------------+-------+----------+----------+
22 rows in set (0.01 sec)

mysql>

* 关键字 on *

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用 left jion 时，on 和 where 条件的区别如下：

1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

[SQL JOIN 中 on 与 where 的区别]("" x "http://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html")

3.4.1 Cross 交叉连接

* CROSS JOIN *

把表A和表B的数据进行一个N*M的组合，即笛卡尔积。如本例会产生3x5=15条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

mysql> SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a  CROSS JOIN websites b ;
+---------+----------+-------------------------+---------+----------+-------+
| country | app_name | url                     | country | name     | alexa |
+---------+----------+-------------------------+---------+----------+-------+
| CN      | QQ APP   | http://im.qq.com/       | USA     | Google   |     1 |
| CN      | 微博 APP | http://weibo.com/       | USA     | Google   |     1 |
| CN      | 淘宝 APP | https://www.taobao.com/ | USA     | Google   |     1 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 淘宝     |    13 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 淘宝     |    13 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 淘宝     |    13 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 菜鸟教程 |  4689 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 菜鸟教程 |  4689 |
| CN      | QQ APP   | http://im.qq.com/       | CN      | 微博     |    20 |
| CN      | 微博 APP | http://weibo.com/       | CN      | 微博     |    20 |
| CN      | 淘宝 APP | https://www.taobao.com/ | CN      | 微博     |    20 |
| CN      | QQ APP   | http://im.qq.com/       | USA     | Facebook |     3 |
| CN      | 微博 APP | http://weibo.com/       | USA     | Facebook |     3 |
| CN      | 淘宝 APP | https://www.taobao.com/ | USA     | Facebook |     3 |
+---------+----------+-------------------------+---------+----------+-------+
15 rows in set (0.00 sec)

mysql>

4 参考借鉴

[菜鸟教程mysql] http://www.runoob.com/mysql/mysql-join.html

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

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

mysql

文章转载自rocklei123的技术点滴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。