MySQL基础-数据操作(二)

基本格式：

SELECT 
[DISTINCT]
{* | <字段列名>}
[    
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

注意，以上指令的输入顺序不能乱！

基础示例：

mysql> SELECT * FROM demo_0;
+-------+------+------+--------+
| id    | name | age  | addr   |
+-------+------+------+--------+
| 33703 | Mike |   23 | 南京   |
| 36640 | XUAN |   22 | 南京   |
| 36964 | Han  |   22 | 南京   |
+-------+------+------+--------+
3 rows in set (0.01 sec)

mysql> SELECT `name` FROM demo_0;
+------+
| name |
+------+
| Mike |
| XUAN |
| Han  |
+------+
3 rows in set (0.01 sec)

去重

DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。

1	SELECT DISTINCT <字段名> FROM <表名>;

注意：如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重

mysql> SELECT DISTINCT * FROM demo_0;
+-------+-------+------+--------+
| id    | name  | age  | addr   |
+-------+-------+------+--------+
| 33703 | Mike  |   23 | 南京   |
| 36640 | XUAN  |   22 | 南京   |
| 36964 | Han   |   22 | 南京   |
| 12398 | Jack  |   20 | 成都   |
| 21343 | Cling |   21 | 深圳   |
+-------+-------+------+--------+
5 rows in set (0.01 sec)

mysql> SELECT DISTINCT age FROM demo_0;
+------+
| age  |
+------+
|   23 |
|   22 |
|   20 |
|   21 |
+------+
4 rows in set (0.00 sec)

因为 DISTINCT 只能返回它的目标字段，而无法返回其它字段，所以在实际情况中，我们经常使用 DISTINCT 关键字来返回不重复字段的条数。查询 student 表中对 name 和 age 字段去重之后记录的条数，SQL 语句和运行结果如下：

mysql> SELECT COUNT(DISTINCT name,age) FROM student;
+--------------------------+
| COUNT(DISTINCT name,age) |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.01 sec)

别名

给列起别名：

mysql> SELECT (数学+语文+英语+物理+化学) AS 总分 FROM demo_2;
+--------+
| 总分   |
+--------+
|    397 |
|    388 |
|    436 |
|    352 |
+--------+
4 rows in set (0.00 sec)

mysql> SELECT 姓名 (数学+语文+英语+物理+化学) AS 总分 FROM demo_2;
ERROR 1305 (42000): FUNCTION stuinfo.姓名 does not exist
mysql> SELECT 姓名 ,(数学+语文+英语+物理+化学) AS 总分 FROM demo_2;
+--------+--------+
| 姓名    | 总分   |
+--------+--------+
| Jack   |    397 |
| Mike   |    388 |
| Han    |    436 |
| Xuan   |    352 |
+--------+--------+

给表起别名：

mysql> SELECT stu.name ,stu.addr FROM stu_info_table AS stu;
+-------+--------+
| name  | addr   |
+-------+--------+
| Mike  | 南京   |
| XUAN  | 南京   |
| Han   | 南京   |
| Jack  | 成都   |
| Cling | 深圳   |
+-------+--------+
5 rows in set (0.01 sec)

注意：表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。
某些情况下必须使用别名，如自连接时需要给表取别名，详见：表的连接

限制查询的条数

当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。

1	mysql> SELECT * FROM tbl LIMIT 初始位置,条数;

如果不加初始位置，则默认从第0条开始。

mysql> SELECT * FROM stu_info_table LIMIT 3,2;
+-------+--------+
| name  | addr   |
+-------+--------+
| Jack  | 成都   |
| Cling | 深圳   |
+-------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM stu_info_table LIMIT 3;
+------+--------+
| name | addr   |
+------+--------+
| Mike | 南京   |
| XUAN | 南京   |
| Han  | 南京   |
+------+--------+
3 rows in set (0.00 sec)

每页显示 N 条，则公式为：LIMIT (第几页-1)*N , N

对查询结果排序

1	ORDER BY <字段名> [ASC\|DESC]

其中， ASC 代表升序，DESC 代表降序；默认升序 ；
单字段进行排序：

mysql> SELECT * FROM demo_2 ORDER BY math DESC;
+--------+--------+--------+--------+--------+--------+
| 姓名   | 数学   | 语文   | 英语   | 物理   | 化学   |
+--------+--------+--------+--------+--------+--------+
| Jack   |     98 |     87 |     87 |     68 |     57 |
| Han    |     87 |     98 |     97 |     96 |     58 |
| Mike   |     76 |     87 |     68 |     59 |     98 |
| Xuan   |     67 |     57 |     86 |     58 |     84 |
+--------+--------+--------+--------+--------+--------+

多字段进行排序：

mysql> SELECT Chinese, Math FROM demo_2 ORDER BY Chinese ,Math DESC;
+---------+------+
| Chinese | Math |
+---------+------+
|      57 |   67 |
|      87 |   98 |
|      87 |   76 |
|      98 |   87 |
+---------+------+

注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

mysql> SELECT Chinese, Math FROM demo_2 ORDER BY Chinese DESC,Math DESC;
+---------+------+
| Chinese | Math |
+---------+------+
|      98 |   87 |
|      87 |   98 |
|      87 |   76 |
|      57 |   67 |
+---------+------+
4 rows in set (0.00 sec)

DESC 关键字只对前面的列进行降序排列，在这里只对 height 字段进行降序。因此，height 按降序排序，而 name 仍按升序排序。如果想在多个列上进行降序排序，必须对每个列指定 DESC 关键字 。

条件查询

查询条件可以是：

带比较运算符：> ，<，>=，<=，!= ，= 等；
逻辑运算符：AND(&&) , NOT , OR(||) , XOR

XOR：记录满足其中任意一个条件，并且不满足另一个条件时，才会被查询出来。
带 BETWEEN AND 关键字的查询条件：等价于 >=L AND <= R
带 IS NULL 关键字的查询条件：判断是否为空；**不能将 IS 换做 = **
带 IN 关键字的查询条件：显示在 IN 列表中的值，如 IN(98,87)；
带 LIKE ，NOT LIKE 关键字的查询条件

####IN
mysql> SELECT * FROM demo_2
    -> WHERE Math IN(98,76);
+------+------+---------+---------+---------+-----------+
| name | Math | Chinese | English | Physics | Chemistry |
+------+------+---------+---------+---------+-----------+
| Jack |   98 |      87 |      87 |      68 |        57 |
| Mike |   76 |      87 |      68 |      59 |        98 |
+------+------+---------+---------+---------+-----------+


####BETWEEN AND
mysql> SELECT Chinese FROM demo_2
    -> WHERE Chinese BETWEEN 87 AND 98;
+---------+
| Chinese |
+---------+
|      87 |
|      87 |
|      98 |
+---------+
####XOR
mysql> SELECT * FROM demo_2
   -> WHERE MATH > 70 XOR Physics < 70;
+------+------+---------+---------+---------+-----------+
| name | Math | Chinese | English | Physics | Chemistry |
+------+------+---------+---------+---------+-----------+
| Han  |   87 |      98 |      97 |      96 |        58 |
| Xuan |   67 |      57 |      86 |      58 |        84 |
+------+------+---------+---------+---------+-----------+

LIKE 模糊查询：% 代表任何长度的字符串，字符串的长度可以为 0；_ 只能代表单个字符，字符的长度不能为 0；

mysql> SELECT * FROM demo_1
    -> WHERE `name` LIKE "%an%";
+-------+--------+------+--------+
| id    | name   | age  | addr   |
+-------+--------+------+--------+
| 12336 | QuanHa |   20 | 四川   |
| 12321 | Han    |   20 | 天津   |
+-------+--------+------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM demo_1
    -> WHERE `name` LIKE "_a%";
+-------+------+------+--------+
| id    | name | age  | addr   |
+-------+------+------+--------+
| 12321 | Han  |   20 | 天津   |
+-------+------+------+--------+

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的 。如果需要区分大小写，可以加入 BINARY 关键字：

mysql> SELECT * FROM demo_1
    -> WHERE `name` LIKE BINARY "han";
Empty set (0.00 sec)
mysql> SELECT * FROM demo_1
    -> WHERE `name` LIKE BINARY "Han";
+-------+------+------+--------+
| id    | name | age  | addr   |
+-------+------+------+--------+
| 12321 | Han  |   20 | 天津   |
+-------+------+------+--------+
1 row in set (0.00 sec)

注意：在确定使用通配符后，除非绝对有必要，否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
WHERE 查询条件中不可以使用字段别名 ：

mysql> SELECT name AS 姓名,Math AS 数学 FROM demo_2
    -> WHERE 数学>60;
ERROR 1054 (42S22): Unknown column '数学' in 'where clause'
mysql> SELECT name AS 姓名,Math AS 数学 FROM demo_2
    -> HAVING 数学>60;
+--------+--------+
| 姓名   | 数学   |
+--------+--------+
| Jack   |     98 |
| Mike   |     76 |
| Han    |     87 |
| Xuan   |     67 |
+--------+--------+
4 rows in set (0.00 sec)

分组查询

1	GROUP BY <字段名>

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()等：

mysql> SELECT * FROM demo_2;
+------+------+------+---------+---------+---------+-----------+
| SEX  | name | Math | Chinese | English | Physics | Chemistry |
+------+------+------+---------+---------+---------+-----------+
| 男   | Jack |   98 |      87 |      87 |      68 |        57 |
| 男   | Mike |   76 |      87 |      68 |      59 |        98 |
| 女   | Han  |   87 |      98 |      97 |      96 |        58 |
| 女   | Xuan |   67 |      57 |      86 |      58 |        84 |
+------+------+------+---------+---------+---------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT SEX ,AVG(Math) AS 数学平均分 FROM demo_2
    -> GROUP BY SEX;
+------+-----------------+
| SEX  | 数学平均分       |
+------+-----------------+
| 女   |         77.0000 |
| 男   |         87.0000 |
+------+-----------------+
2 rows in set (0.00 sec)

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来：

mysql> SELECT `name`, SEX FROM demo_2
    -> GROUP BY SEX;
ERROR 1055 (42000): Expression 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stuinfo.demo_2.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### 直接根据SEX分组打印信息会报错
mysql> SELECT SEX, GROUP_CONCAT(name) AS name FROM demo_2
    -> GROUP BY SEX;
+------+-----------+
| SEX  | name      |
+------+-----------+
| 女   | Han,Xuan  |
| 男   | Jack,Mike |
+------+-----------+
2 rows in set (0.00 sec)

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

mysql> SELECT SEX, GROUP_CONCAT(name) AS name FROM demo_2
    -> GROUP BY SEX WITH ROLLUP;
+------+--------------------+
| SEX  | name               |
+------+--------------------+
| 女   | Han,Xuan           |
| 男   | Jack,Mike          |
| NULL | Han,Xuan,Jack,Mike |
+------+--------------------+
3 rows in set (0.01 sec)

过滤分组

1	HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。但是 WHERE 和 HAVING 关键字也存在以下几点差异：

一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤，所以 HAVING 绝大多数时候应该搭配 GROUP BY 使用！
WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

#对于第4点
mysql> SELECT name FROM demo_2
    -> HAVING Math>60;
ERROR 1054 (42S22): Unknown column 'Math' in 'having clause'
mysql> SELECT name FROM demo_2
    -> WHERE Math>60;
+------+
| name |
+------+
| Jack |
| Mike |
| Han  |
| Xuan |
+------+
#对于第5点
mysql> SELECT Math AS 数学 FROM demo_2
    -> WHERE 数学>60;
ERROR 1054 (42S22): Unknown column '数学' in 'where clause'
mysql> SELECT Math AS 数学 FROM demo_2
    -> HAVING 数学>60;
+--------+
| 数学   |
+--------+
|     98 |
|     76 |
|     87 |
|     67 |
+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM demo_0;
+-------+-------+------+--------+
| id    | name  | age  | addr   |
+-------+-------+------+--------+
| 33703 | Mike  |   23 | 南京   |
| 36640 | XUAN  |   22 | 南京   |
| 36964 | Han   |   22 | 南京   |
| 12398 | Jack  |   20 | 成都   |
| 21343 | Cling |   21 | 深圳   |
+-------+-------+------+--------+
5 rows in set (0.01 sec)

mysql> SELECT GROUP_CONCAT(name) ,addr FROM demo_0
    -> GROUP BY addr
    -> HAVING addr!="深圳";
+--------------------+--------+
| GROUP_CONCAT(name) | addr   |
+--------------------+--------+
| Mike,XUAN,Han      | 南京   |
| Jack               | 成都   |
+--------------------+--------+
2 rows in set (0.00 sec)

子查询

子查询指将一个查询语句嵌套在另一个查询语句中。在 SELECT 语句中，子查询可以被嵌套在 SELECT 语句的列、表和查询条件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句，而且可以进行多层嵌套。子查询经常出现在 WHERE 子句中。使用子查询时，常用 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

为方便演示，先给出以下酒店数据库的三张表：

##房间类型
mysql> SELECT * FROM type_info;
+------+--------------+-------+----------+
| id   | type         | price | capacity |
+------+--------------+-------+----------+
|    1 | 标准间       |   150 | 2        |
|    2 | 大床房       |   200 | 2        |
|    3 | 情侣房       |   210 | 2        |
|    4 | 家庭套间     |   400 | 4        |
|    5 | 总统套房     |   600 | 4        |
+------+--------------+-------+----------+
##房间信息
mysql> SELECT * FROM room_info;
+--------------+----------+-------+---------+
| type         | location | state | room_id |
+--------------+----------+-------+---------+
| 标准间       | 10-1     |     1 | 001     |
| 标准间       | 10-2     |     1 | 002     |
| 标准间       | 10-3     |     0 | 003     |
| 标准间       | 10-4     |     0 | 004     |
| 情侣房       | 10-5     |     0 | 005     |
| 情侣房       | 11-6     |     0 | 006     |
| 大床房       | 11-7     |     0 | 007     |
| 大床房       | 12-8     |     0 | 008     |
| 家庭套间     | 12-9     |     0 | 009     |
| 家庭套间     | 13-10    |     0 | 010     |
| 总统套房     | 10-11    |     1 | 011     |
+--------------+----------+-------+---------+
##客人信息
mysql> SELECT * FROM customer_info;
+--------+--------+------+-------------+---------+
| name   | ID     | age  | tel         | room_id |
+--------+--------+------+-------------+---------+
| 李华   | 511323 |   18 | 18990889826 | 001     |
| 周建   | 511345 |   20 | 17390199497 | 011     |
| 王霸   | 532435 |   45 | 18932981949 | 002     |
| 李斯   | 343234 |   34 | 14342324323 | 001     |
| 巴嘎   | 343323 |   23 | 32232123343 | 002     |
+--------+--------+------+-------------+---------+

单行子查询：

1）查询和李华住在一个房间的人（单次嵌套）：

mysql> SELECT name FROM customer_info
    -> WHERE room_id = (
    -> SELECT room_id FROM customer_info WHERE `name`="李华")
    -> AND `name`!='李华';
+--------+
| name   |
+--------+
| 李斯   |
+--------+

2）查询李华住的房间的价格（多次嵌套，横跨三张表）：

mysql> SELECT price FROM type_info
    -> WHERE type=(
    -> SELECT type FROM room_info
    -> WHERE room_id=(
    -> SELECT room_id FROM customer_info
    -> WHERE name = "李华"));
+-------+
| price |
+-------+
|   150 |
+-------+

多行子查询：
1）查询状态为空的所有房间的价格：

mysql> SELECT price FROM type_info
    -> WHERE type IN(
    -> SELECT DISTINCT type FROM room_info
    -> WHERE state=0);
+-------+
| price |
+-------+
|   150 |
|   210 |
|   200 |
|   400 |
+-------+

2）打印出各 capacity 中的最高 price 的房型，输出列包含：type，price，capacity。

mysql> SELECT type,price,capacity FROM type_info
    -> WHERE price IN(
    -> SELECT MAX(price) FROM type_info
    -> GROUP BY capacity);
+--------------+-------+----------+
| type         | price | capacity |
+--------------+-------+----------+
| 情侣房       |   210 | 2        |
| 总统套房     |   600 | 4        |
+--------------+-------+----------+

#小白一般可能会这样：
mysql> SELECT type, MAX(price), capacity FROM type_info
    -> GROUP BY capacity;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'custom_info.type_info.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#报错是因为 “type 不依赖 GROUP BY 子句中的列”

如上，多行子查询常用 IN 和 NOT IN，同时也经常用到 EXISTS 关键字；EXISTS 用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回布尔值 true 或 false，EXISTS 指定一个子查询，检测行的存在 。

3）若还有空房，则打印所有房间类型：

mysql> SELECT * FROM type_info
    -> WHERE EXISTS(
    -> SELECT state FROM room_info
    -> WHERE state=1);
+------+--------------+-------+----------+
| id   | type         | price | capacity |
+------+--------------+-------+----------+
|    1 | 标准间       |   150 | 2        |
|    2 | 大床房       |   200 | 2        |
|    3 | 情侣房       |   210 | 2        |
|    4 | 家庭套间     |   400 | 4        |
|    5 | 总统套房     |   600 | 4        |
+------+--------------+-------+----------+
5 rows in set (0.00 sec)

子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写 。一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。
习惯上，外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询 。

子查询临时表

现在有这样一个需求：打印出各 capacity 中的最高 price 的房型，输出列包含：type，price，capacity。

mysql> SELECT type,price,capacity FROM type_info
    -> WHERE price IN(
    -> SELECT MAX(price) FROM type_info
    -> GROUP BY capacity);
+--------------+-------+----------+
| type         | price | capacity |
+--------------+-------+----------+
| 情侣房       |   210 | 2        |
| 总统套房     |   600 | 4        |
+--------------+-------+----------+

#显示上述表中价格第二的条目，实际无需如此复杂
mysql> SELECT * FROM(
    -> SELECT type,price,capacity FROM type_info
    -> WHERE price IN(
    -> SELECT MAX(price) FROM type_info
    -> GROUP BY capacity)) temp
    -> ORDER BY temp.price DESC
    -> LIMIT 1,1;
+-----------+-------+----------+
| type      | price | capacity |
+-----------+-------+----------+
| 情侣房    |   210 | 2         |
+-----------+-------+----------+

嵌套在 SELECT 语句的 FROM 子句中的子查询语法格式如下：

SELECT * FROM (子查询) AS 表的别名；注意：必须为表指定别名。

多列子查询

mysql> SELECT * FROM emp;
+------+---------+------+------------+
| id   | name    | mgr  | department |
+------+---------+------+------------+
|    1 | Han     | NULL | 10         |
|    2 | Jack    | 1    | 12         |
|    3 | Tom     | 1    | 12         |
|    4 | Cling   | 2    | 20         |
|    5 | Black   | 3    | 20         |
|    6 | JayChou | 1    | 12         |
+------+---------+------+------------+

需求：找到和 Jack 所在部门相同且上级也相同的人：

mysql> SELECT * FROM emp
    -> WHERE (mgr,department)=(
    -> SELECT mgr,department FROM emp
    -> WHERE name="Jack")
    -> && name!="Jack";
+------+---------+------+------------+
| id   | name    | mgr  | department |
+------+---------+------+------------+
|    3 | Tom     | 1    | 12         |
|    6 | JayChou | 1    | 12         |
+------+---------+------+------------+
2 rows in set (0.00 sec)

子查询语句返回多列结果，分别与等号前的 mgr 和 department 匹配。

合并查询

通常使用 UNION ALL 或 UNION 关键字合并多张表的查询结果。前者不会去重，后者会去重。比如有这样一个需求：NO.1 中学和 NO.2 中学联考，并混合排名：

mysql> SELECT * FROM NO1SC.stu_info;
+------+------+-------+
| name | age  | score |
+------+------+-------+
| Jack |   18 |   150 |
| Mike |   20 |   149 |
| Dan  |   19 |   130 |
| Xuan |   19 |   141 |
+------+------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM NO2SC.stu_info;
+---------+------+-------+
| name    | age  | score |
+---------+------+-------+
| JayChou |   17 |   120 |
| Black   |   28 |   120 |
| Han     |   21 |   147 |
+---------+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM NO1SC.stu_info
    -> UNION ALL
    -> SELECT * FROM NO2SC.stu_info
    -> ORDER BY score DESC;
+---------+------+-------+
| name    | age  | score |
+---------+------+-------+
| Jack    |   18 |   150 |
| Mike    |   20 |   149 |
| Han     |   21 |   147 |
| Xuan    |   19 |   141 |
| Dan     |   19 |   130 |
| Black   |   28 |   120 |
| JayChou |   17 |   120 |
+---------+------+-------+