摘要：張三李四張三北京海底市南區(qū)李四北京海底市南區(qū)操作符的優(yōu)勢(shì)使用長(zhǎng)的合法選項(xiàng)清單時(shí)候操作符比較直觀。支持對(duì)子句取反。張三李四趙蕓上海市徐匯區(qū)王麗廣州廈門(mén)我的網(wǎng)站

在數(shù)據(jù)庫(kù)的使用中排序和過(guò)濾也是經(jīng)常的操作

排序檢索數(shù)據(jù),關(guān)鍵字order

普通排序
mysql> select * from user;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
+----+--------+-----+-----------------------+-----+
4 rows in set (0.00 sec)
按照列名name排序是什么樣呢？
mysql> select * from user order by name;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
+----+--------+-----+-----------------------+-----+
4 rows in set (0.00 sec)

mysql> select * from user order by name, age;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
+----+--------+-----+-----------------------+-----+
4 rows in set (0.00 sec)

使用關(guān)鍵字desc,可以改為降序排列
mysql> select * from user order by name desc;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
+----+--------+-----+-----------------------+-----+
4 rows in set (0.00 sec)

mysql> select * from user order by name limit 3;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
+----+--------+-----+-----------------------+-----+
3 rows in set (0.00 sec)

數(shù)據(jù)過(guò)濾，關(guān)鍵字where

mysql> select * from user where id = 2;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
+----+--------+-----+-----------------------+-----+
1 row in set (0.00 sec)

和order by 配合使用
mysql> select * from user where id <4  order by name limit 3;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
+----+--------+-----+-----------------------+-----+
3 rows in set (0.00 sec)

關(guān)于where子句的位置: 在同時(shí)使用where和order by子句時(shí)候, 我們應(yīng)該讓order by位于where 子句之后。

mysql> select * from user where id between 2 and 4;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
+----+--------+-----+-----------------------+-----+
3 rows in set (0.00 sec)

mysql> select * from user where id >1 and id < 4;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
+----+--------+-----+-----------------------+-----+
2 rows in set (0.00 sec)

mysql> select * from user where id <2 or id >=3;
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)          |   2 |
|  4 | 王麗   |  31 | 廣州廈門(mén)              |   2 |
+----+--------+-----+-----------------------+-----+
3 rows in set (0.00 sec)

in操作符可以用于指定操作范圍，范圍內(nèi)每個(gè)條件都可以進(jìn)行匹配。
mysql> select * from user where name in ("張三","李四");
+----+--------+-----+-----------------------+-----+
| id | name   | age | address               | sex |
+----+--------+-----+-----------------------+-----+
|  1 | 張三   |  20 | 北京海底市南區(qū)        |   1 |
|  2 | 李四   |  22 | 北京海底市南區(qū)        |   1 |
+----+--------+-----+-----------------------+-----+
2 rows in set (0.00 sec)

in操作符的優(yōu)勢(shì)：
1. 使用長(zhǎng)的合法選項(xiàng)清單時(shí)候, in操作符比較直觀。
2. in操作符計(jì)算的次序比較好管理
3. in操作符一般比or操作符效率快
4. in操作符可以包括其他select語(yǔ)句，能夠更加動(dòng)態(tài)的創(chuàng)建where子句

not操作符只有一個(gè)特點(diǎn), 就是否定它后面的任何條件。
mysql支持not對(duì)in, between, exists子句取反。

mysql> select * from user where name not in ("張三","李四");
+----+--------+-----+--------------------+-----+
| id | name   | age | address            | sex |
+----+--------+-----+--------------------+-----+
|  3 | 趙蕓   |  32 | 上海市徐匯區(qū)       |   2 |
|  4 | 王麗   |  31 | 廣州廈門(mén)           |   2 |
+----+--------+-----+--------------------+-----+
2 rows in set (0.00 sec)

我的網(wǎng)站：https://wayne214.github.io