MYSQL中的SQL语句不区分大小写，一般习惯将关键字大写，而数据列和表名使用小写，养成一个良好习惯，对写出来的SQL语句更容易阅读和维护。
- IN查询
//查询年龄是18或者30的人员
mysql> SELECT id,name,age,salary,daptid
-> FROM user
-> WHERE age in(18,30);
+----+--------+------+--------+--------+
| id | name | age | salary | daptid |
+----+--------+------+--------+--------+
| 13 | 小环 | 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 | 小环 | 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 | 小环 | 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 | 小环 | 18 | 3000 | 2 |
| 19 | 小欢2 | 22 | 1000 | 1 |
| 20 | 小欢3 | 17 | 1000 | 1 |
+----+---------+------+--------+--------+
3 rows in set (0.00 sec)
- DISTINCT 消除重复数据
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 | 小环 | 18 | 焚香谷 |
| 15 | 笨蛋 | 1 | 焚香谷 |
| 16 | 笨笨 | 1 | 焚香谷 |
| 17 | 小本 | 12 | 青云门 |
| 18 | 小欢 | 12 | 青云门 |
+----+-----------------+------+-----------+
14 rows in set (0.00 sec)
- 排序 ORDER BY
用SELECT 查询数据时，显示的顺序默认是按数据插入的先后顺序。
SELECT name,age,salary,createtime FROM user ORDER BY name; //按姓名进行排序
多列排序：
SELECT name,age,salary,createtime FROM user ORDER BY name,createtime;//先按姓名排序，姓名一样时按创建时间排序
ORDER BY 默认采用升序方式，可采用关键字DESC变为降序如：
mysql> SELECT name,age,salary,createtime FROM user ORDER BY name DESC;//按姓名降序
SELECT name,age,salary,createtime FROM user ORDER BY name DESC,createtime;//按姓名降序，姓名一样再按时间升序排列
网友评论