DISTINCT關(guān)鍵字
作用：指示MySQL只返回不同的值（去重）粗俱；
用法：放置在需要去重的列前面说榆；
mysql> SELECT salary FROM person;
+----------+
| salary |
+----------+
| 8000.00 |
| 8000.00 |
| 10000.00 |
+----------+
3 rows in set (0.00 sec)
mysql> SELECT DISTINCT salary FROM person;
+----------+
| salary |
+----------+
| 8000.00 |
| 10000.00 |
+----------+
2 rows in set (0.00 sec)
DISTINCT 關(guān)鍵字應(yīng)用于所有列而不僅是前置它的列
mysql> SELECT salary,age FROM person;
+----------+-----+
| salary | age |
+----------+-----+
| 8000.00 | 23 |
| 8000.00 | 21 |
| 10000.00 | 25 |
+----------+-----+
3 rows in set (0.00 sec)
mysql> SELECT DISTINCT salary,age FROM person;
+----------+-----+
| salary | age |
+----------+-----+
| 8000.00 | 23 |
| 8000.00 | 21 |
| 10000.00 | 25 |
+----------+-----+
3 rows in set (0.00 sec)
LIMIT 關(guān)鍵字
mysql> SELECT name,age FROM person;
+------+-----+
| name | age |
+------+-----+
| 張三 | 23 |
| 李四 | 21 |
| 王五 | 25 |
+------+-----+
3 rows in set (0.00 sec)
mysql> SELECT name,age FROM person LIMIT 1,1;#返回第二行的數(shù)據(jù)；
+------+-----+
| name | age |
+------+-----+
| 李四 | 21 |
+------+-----+
1 row in set (0.00 sec)
mysql> SELECT name,age FROM person LIMIT 1,3;#返回從第二行開始的數(shù)據(jù)源梭，最多3條娱俺，這里實際只有2條；
+------+-----+
| name | age |
+------+-----+
| 李四 | 21 |
| 王五 | 25 |
+------+-----+
2 rows in set (0.00 sec)
mysql> SELECT name,age FROM person LIMIT 3 OFFSET 1;#上面一條命令的等價废麻，MySQL5版本支持荠卷；
+------+-----+
| name | age |
+------+-----+
| 李四 | 21 |
| 王五 | 25 |
+------+-----+
2 rows in set (0.00 sec)
ORDER BY
作用：排序
mysql> SELECT name,age,salary FROM person;
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Jack | 23 | 8000.00 |
| Mary | 21 | 8000.00 |
| Ken | 25 | 10000.00 |
+------+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT name,age,salary FROM person ORDER BY name;
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Jack | 23 | 8000.00 |
| Ken | 25 | 10000.00 |
| Mary | 21 | 8000.00 |
+------+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT age,salary FROM person ORDER BY name;# 排序列可以是檢索列也可以不是；
+-----+----------+
| age | salary |
+-----+----------+
| 23 | 8000.00 |
| 25 | 10000.00 |
| 21 | 8000.00 |
+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT age,salary FROM person ORDER BY age, name;#排序可以有多個列烛愧；
+-----+----------+
| age | salary |
+-----+----------+
| 21 | 8000.00 |
| 23 | 8000.00 |
| 25 | 10000.00 |
+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT age,salary FROM person ORDER BY age DESC,name;
+-----+----------+
| age | salary |
+-----+----------+
| 25 | 10000.00 |
| 23 | 8000.00 |
| 21 | 8000.00 |
+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT age,salary FROM person ORDER BY age DESC LIMIT 1;#注意兩者的順序油宜；
+-----+----------+
| age | salary |
+-----+----------+
| 25 | 10000.00 |
+-----+----------+
1 row in set (0.00 sec)
注意：
- 排序的字段可以是一個也可以是多個，排序的字段可以是檢索的字段怜姿，也可以是非檢索字段慎冤；
- 默認(rèn)的字段排序是升序ASC，降序需要使用關(guān)鍵字DESC沧卢，放置在待排序字段的后面蚁堤，并且只對該字段前的排序字段起作用；
- ORDER BY位于FROM的后面但狭，LIMIT位于ORDER BY的后面披诗；
WHERE
作用：條件搜索；
= 相等
!= 不相等
<> 不相等
< 小于
<= 小于等于
>大于
>= 大于等于
BETWEEN 在兩個值之間(包括兩端的值在內(nèi))
mysql> SELECT name,age,salary FROM person ORDER BY name;
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Jack | 23 | 8000.00 |
| Ken | 25 | 10000.00 |
| Mary | 21 | 8000.00 |
+------+-----+----------+
3 rows in set (0.00 sec)
mysql> SELECT name,age,salary FROM person WHERE name !='jack';
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Mary | 21 | 8000.00 |
| Ken | 25 | 10000.00 |
+------+-----+----------+
2 rows in set (0.00 sec)
mysql> SELECT name,age,salary FROM person WHERE name <>'jack';
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Mary | 21 | 8000.00 |
| Ken | 25 | 10000.00 |
+------+-----+----------+
2 rows in set (0.00 sec)
mysql> SELECT name,age,salary FROM person WHERE salary BETWEEN 9000 AND 10000;
+------+-----+----------+
| name | age | salary |
+------+-----+----------+
| Ken | 25 | 10000.00 |
+------+-----+----------+
1 row in set (0.00 sec)
注意：
- windows下MySQL默認(rèn)匹配不區(qū)分大小寫的立磁；
- WHERE位于ORDER BY之前呈队；
- 復(fù)雜的條件過濾 AND 和OR（二者的優(yōu)先級是）
mysql> SELECT productName,price FROM product;
+-------------+---------+
| productName | price |
+-------------+---------+
| 10001 | 100.00 |
| 10001 | 450.00 |
| 10002 | 30.00 |
| 10002 | 888.00 |
| 10003 | 1000.00 |
+-------------+---------+
5 rows in set (0.00 sec)
空值NULL
可以在WHERE條件后帶IS NULL 或者IS NOT NULL，但是不可以用=或者!=來判定某個字段是否為空唱歧，這樣是查不到任何數(shù)據(jù)的宪摧；
