1.目标
通过本文可以了解在使用mysql数据库开发时，一些开发技巧。本文比较基础，大神请绕道。
通过本人能够掌握：
- MySQL UNION
- MySQL 排序
- MySQL 分组
- Mysql 连接的使用
- MySQL 导入数据
2.环境准备
本教程的 SQL 在 MySQL 中测试通过。
使用基础数据下载地址：sql 教程
- 本教程使用到的 Websites 表 SQL 文件：websites.sql。
- 本教程使用到的 access_log 表 SQL 文件：access_log.sql。
- 本教程使用到的 apps 表 SQL 文件：apps.sql。
- Mysql join 使用到的表结构下载地址： runoob-mysql-join-test.sql
- mysql 分组使用到的 employee_tbl 表语句如下：
1 | SET NAMES utf8; |
2.1 建库准备
创建数据库，该命令的作用：
- 如果数据库不存在则创建，存在则不创建。
- 创建RUNOOB数据库，并设定编码集为utf8
1 | CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci; |
2.2 导入数据
- 通过sqlyog 工具或其他图形化工具的 import功能
登录mysql cli 执行：source /home/abc/abc.sql;
1
2
3mysql> use abc; # 使用已创建的数据库
mysql> set names utf8; # 设置编码
mysql> source /home/abc/abc.sql # 导入备份数据库mysql -uroot -p123456 < runoob.sql
3 开发技巧整理
3.1 MySQL 排序
案例分析
1 | mysql> SELECT * FROM websites ORDER BY country,alexa; |
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 操作符语法格式：1
2
3
4
5
6
7SELECT 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测试时也需要此表数据演示）
1 | mysql> SELECT * FROM apps; |
3.2.3 MySQL UNION 实例演示
SQL UNION 实例 （无重复数据）
1 | mysql> SELECT country,url FROM websites UNION SELECT country,url FROM apps ORDER BY country ; |
SQL UNION ALL 实例 （存在重复数据）
1 | mysql> SELECT country,url FROM websites UNION ALL SELECT country,url FROM apps ORDER BY country ; |
带有 WHERE 的 SQL UNION ALL 1
2
3
4
5
6
7
8
9
10
11
12
13
14mysql> 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 分组
原始数据 1
2
3
4
5
6
7
8
9
10
11
12mysql> 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 语句将数据表按名字进行分组，并统计每个人有多少条记录,并排序 1
2
3
4
5
6
7
8
9
10
11mysql> 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…）。（目的看谁登录次数最多）1
2
3
4
5
6
7
8
9
10
11
12mysql> 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 语法：1
2select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
以下实例中如果名字为空我们使用总数代替：
1 | mysql> SELECT COALESCE(NAME,'总数'),SUM(singin) FROM employee_tbl GROUP BY NAME WITH ROLLUP; |
注意事项
1、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：1
2SELECT name FROM employee_tb1 GROUP BY name;返回的结果集就是所有员工的名字。
SELECT DISTINCT NAME FROM employee_tbl;
哪种效率更高呢？<做过对比测试的兄弟希望能给出你的建议>
2、分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：
1 | 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 ; |
3.4 MySQL 连接JOIN
SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
3.4.1 内连接
INNER JOIN
INNER JOIN：如果表中有至少一个匹配，则返回行1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17mysql> 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：即使右表中没有匹配，也从左表返回所有的行
1 | 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; |
RIGHT JOIN
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> 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 连接，错误如下:1
2
3mysql> 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 代替:1
2
3SELECT 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
1 |
|
关键字 on
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 left jion 时，on 和 where 条件的区别如下：
1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
3.4.1 Cross 交叉连接
CROSS JOIN
把表A和表B的数据进行一个N*M的组合，即笛卡尔积。如本例会产生3x5=15条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。
1 | mysql> SELECT a.country ,a.app_name ,a.url,b.country ,b.name,b.alexa FROM apps a CROSS JOIN websites b ; |
