MYSQL中的SQL語句不區(qū)分大小寫，一般習慣將關鍵字大寫业筏，而數(shù)據(jù)列和表名使用小寫憔杨，養(yǎng)成一個良好習慣，對寫出來的SQL語句更容易閱讀和維護蒜胖。
- IN查詢
//查詢年齡是18或者30的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age in(18,30);
+----+--------+------+--------+--------+
| id | name | age | salary | daptid |
+----+--------+------+--------+--------+
| 13 | 小環(huán) | 18 | 3000 | 2 |
+----+--------+------+--------+--------+
1 row in set (0.00 sec)
//查詢年齡不是18和30的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age not in(18,30);
+----+-----------------+------+---------+--------+
| id | name | age | salary | daptid |
+----+-----------------+------+---------+--------+
| 7 | 燕虹 | 21 | 7000.14 | 2 |
| 8 | 鬼王 | 180 | 8000 | 4 |
| 9 | 毒神 | 200 | 7000 | 6 |
| 10 | 陸小琪 | 20 | 1000 | 1 |
| 11 | 蒼松好道人 | 170 | 7000 | 1 |
| 12 | 周一仙 | 500 | 5000 | 2 |
| 15 | 笨蛋 | 1 | 1 | 2 |
| 16 | 笨笨 | 1 | 1 | 2 |
| 17 | 小本 | 12 | 1000 | 1 |
| 18 | 小歡 | 12 | 1000 | 1 |
| 19 | 小歡 | 12 | 1000 | 1 |
| 20 | 小歡 | 12 | 1000 | 1 |
| 21 | 小歡 | 12 | 1000 | 1 |
| 24 | 1 | 1 | 1 | 1 |
| 25 | xiaoming | 22 | 15555 | 5 |
| 26 | 1 | 1 | 1 | 1 |
+----+-----------------+------+---------+--------+
16 rows in set (0.00 sec)
- 范圍查詢 BETWEEN AND
//查詢年齡在范圍18~30的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age BETWEEN 18 AND 30;
+----+-----------+------+---------+--------+
| id | name | age | salary | daptid |
+----+-----------+------+---------+--------+
| 7 | 燕虹 | 21 | 7000.14 | 2 |
| 10 | 陸小琪 | 20 | 1000 | 1 |
| 13 | 小環(huán) | 18 | 3000 | 2 |
| 25 | xiaoming | 22 | 15555 | 5 |
+----+-----------+------+---------+--------+
4 rows in set (0.00 sec)
//查詢年齡不在范圍18~30的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age NOT BETWEEN 18 AND 30;
+----+-----------------+------+--------+--------+
| id | name | age | salary | daptid |
+----+-----------------+------+--------+--------+
| 8 | 鬼王 | 180 | 8000 | 4 |
| 9 | 毒神 | 200 | 7000 | 6 |
| 11 | 蒼松好道人 | 170 | 7000 | 1 |
| 12 | 周一仙 | 500 | 5000 | 2 |
| 15 | 笨蛋 | 1 | 1 | 2 |
| 16 | 笨笨 | 1 | 1 | 2 |
| 17 | 小本 | 12 | 1000 | 1 |
| 18 | 小歡 | 12 | 1000 | 1 |
| 19 | 小歡 | 12 | 1000 | 1 |
| 20 | 小歡 | 12 | 1000 | 1 |
| 21 | 小歡 | 12 | 1000 | 1 |
| 24 | 1 | 1 | 1 | 1 |
| 26 | 1 | 1 | 1 | 1 |
+----+-----------------+------+--------+--------+
13 rows in set (0.00 sec)
- LIKE 查詢
- 1百分號（%）通配符匹配任意長度的字符消别，包括零字符
//查詢所有姓名以“小”開頭的人員,
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE name like "小%";
+----+--------+------+--------+--------+
| id | name | age | salary | daptid |
+----+--------+------+--------+--------+
| 13 | 小環(huán) | 18 | 3000 | 2 |
| 17 | 小本 | 12 | 1000 | 1 |
| 18 | 小歡 | 12 | 1000 | 1 |
| 19 | 小歡 | 12 | 1000 | 1 |
| 20 | 小歡 | 12 | 1000 | 1 |
| 21 | 小歡 | 12 | 1000 | 1 |
+----+--------+------+--------+--------+
6 rows in set (0.00 sec)
//查詢所有姓名包含笨字的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE name like "%笨%";
+----+--------+------+--------+--------+
| id | name | age | salary | daptid |
+----+--------+------+--------+--------+
| 15 | 笨蛋 | 1 | 1 | 2 |
| 16 | 笨笨 | 1 | 1 | 2 |
+----+--------+------+--------+--------+
2 rows in set (0.00 sec)
- 下劃線通配符（_）,匹配一個字符
//查詢姓名以仙字結尾，前面有兩個字符的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE name like "__仙";
+----+-----------+------+--------+--------+
| id | name | age | salary | daptid |
+----+-----------+------+--------+--------+
| 12 | 周一仙 | 500 | 5000 | 2 |
+----+-----------+------+--------+--------+
1 row in set (0.00 sec)
- AND 多條件查詢
使用SELECT 查詢時台谢，可以增加查詢的限制條件寻狂，這樣可以使查詢的結果更加準確。MYSQL 在WHERE 子句中使用AND操作符限定只有滿足所有的查詢條件的記錄才會被返回朋沮∩呷可以使用AND連接兩個甚至多個查詢條件，多個條件表達式之間用AND分開。
//查詢 年齡在16和25之間并且姓名以小開頭的人員
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age>16 AND age <25 and name LIKE "小%";
+----+---------+------+--------+--------+
| id | name | age | salary | daptid |
+----+---------+------+--------+--------+
| 13 | 小環(huán) | 18 | 3000 | 2 |
| 19 | 小歡2 | 22 | 1000 | 1 |
| 20 | 小歡3 | 17 | 1000 | 1 |
+----+---------+------+--------+--------+
3 rows in set (0.00 sec)
- DISTINCT 消除重復數(shù)據(jù)
mysql> SELECT DISTINCT age FROM user;
select `user`.ID,`user`.`Name`,`user`.Age ,department.DapName from `user` INNER
JOIN department ON department.DaptID=`user`.DaptID
同上面語句
select a.ID,a.Name,a.Age ,department.DapName from `user` a INNER JOIN department ON department.DaptID=a.DaptID
+----+-----------------+------+-----------+
| ID | Name | Age | DapName |
+----+-----------------+------+-----------+
| 4 | 田胖子 | 112 | 青云門 |
| 5 | 金瓶兒 | 20 | 萬毒門 |
| 6 | 秦無炎 | 27 | 合歡派 |
| 7 | 燕虹 | 21 | 焚香谷 |
| 8 | 鬼王 | 180 | 鬼王宗 |
| 9 | 毒神 | 200 | 合歡派 |
| 10 | 陸小琪 | 20 | 青云門 |
| 11 | 蒼松好道人 | 170 | 青云門 |
| 12 | 周一仙 | 500 | 焚香谷 |
| 13 | 小環(huán) | 18 | 焚香谷 |
| 15 | 笨蛋 | 1 | 焚香谷 |
| 16 | 笨笨 | 1 | 焚香谷 |
| 17 | 小本 | 12 | 青云門 |
| 18 | 小歡 | 12 | 青云門 |
+----+-----------------+------+-----------+
14 rows in set (0.00 sec)
- 排序 ORDER BY
用SELECT 查詢數(shù)據(jù)時纠亚，顯示的順序默認是按數(shù)據(jù)插入的先后順序塘慕。
SELECT name,age,salary,createtime FROM user ORDER BY name; //按姓名進行排序
多列排序：
SELECT name,age,salary,createtime FROM user ORDER BY name,createtime;//先按姓名排序，姓名一樣時按創(chuàng)建時間排序
ORDER BY 默認采用升序方式蒂胞，可采用關鍵字DESC變?yōu)榻敌蛉纾?br>mysql> SELECT name,age,salary,createtime FROM user ORDER BY name DESC;//按姓名降序
SELECT name,age,salary,createtime FROM user ORDER BY name DESC,createtime;//按姓名降序图呢，姓名一樣再按時間升序排列
