MySql基础（三）基本查询

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;//按姓名降序，姓名一样再按时间升序排列

MySql基础（三）基本查询

推荐阅读更多精彩内容