笔记 | MySQL 数据库

### MySQL WHERE 子句
#### 基本语法
查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
    [WHERE condition1 [AND [OR]] condition2.....

### 基本查询条件
#### 数值比较
字段类型必须数据数值类型

| 类 型 | 用 途 |
| :------: | ------ |
| = | 等于 |
| > 、 >= | 大于、大于 或 等于 |
| < 、 <= | 小于、小于 或 等于 |
| != | 不等于 |

#### 字符比较 / 匹配空 / 非空
字段类型必须

| 类 型 | 用 途 |
| :------: | ------ |
| = | 等于 |
| != | 不等于 |
| IS NULL | 匹配空 |
| IS NOT NULL | 非空 |

#### 逻辑比较
多个判断条件时使用

| 类 型 | 用 途 |
| :------: | ------ |
| OR | 逻辑或 |
| AND | 逻辑与 |
| ! | 逻辑非 |
| ( ) | 提高优先级 |

```
mysql> select * from user_test where uid = 0 and name = 'root';
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid  | gid  | comment | homedir | shell     |
+----+------+----------+------+------+---------+---------+-----------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

mysql> select * from user_test where uid = 0 or name = 'mysql';
+----+-------+----------+------+------+--------------+----------------+------------+
| id | name  | password | uid  | gid  | comment      | homedir        | shell      |
+----+-------+----------+------+------+--------------+----------------+------------+
|  1 | root  | x        |    0 |    0 | root         | /root          | /bin/bash  |
| 22 | mysql | x        |   27 |   27 | MySQL Server | /var/lib/mysql | /bin/false |
+----+-------+----------+------+------+--------------+----------------+------------+
2 rows in set (0.00 sec)
```

#### 范围内匹配 / 去重显示
匹配范围内的任意一个值即可

| 类 型 | 用 途 |
| :-------: | ------ |
| In ( 值列表 ) | 在…里… |
| Not in ( 值列表 ) | 不在…里… |
| Between 数字 1 and 数字 2 | 在…之间… |
| DISTINCT 字段名 | 去重显示 |

```
mysql> select * from user_test where name in ('mysql', 'root');
+----+-------+----------+------+------+--------------+----------------+------------+
| id | name  | password | uid  | gid  | comment      | homedir        | shell      |
+----+-------+----------+------+------+--------------+----------------+------------+
|  1 | root  | x        |    0 |    0 | root         | /root          | /bin/bash  |
| 22 | mysql | x        |   27 |   27 | MySQL Server | /var/lib/mysql | /bin/false |
+----+-------+----------+------+------+--------------+----------------+------------+
2 rows in set (0.00 sec)

mysql> select * from user_test where id Between 1 and 2;
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid  | gid  | comment | homedir | shell         |
+----+------+----------+------+------+---------+---------+---------------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  2 | bin  | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+
2 rows in set (0.00 sec)
```

### 高级查询条件
#### 模糊匹配
基本用法
- WHERE 字段名 LIKE ' 通配字串 '
- 通配符 _ 匹配单个字符
- % 匹配 0~N 个字符

```
mysql> select * from user_test where shell like '%nologin' limit 1;
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid  | gid  | comment | homedir | shell         |
+----+------+----------+------+------+---------+---------+---------------+
|  2 | bin  | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+
1 row in set (0.00 sec)
```

#### 正则匹配
基本用法
- WHERE 字段名 REGEXP ' 正则表达式'
- ^ $ . [ ] \*

#### 四则运算
运算操作
- 字段必须是数值类型

| 类 型 | 用 途 |
| :-------: | ------ |
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
| % | 取余数（求模） |

#### 聚集函数
MySQL 内置数据统计函数
- avg( 字段名 ) ： 求平均值
- sum( 字段名 ) ：求和
- min( 字段名 ) ： 统计最小值
- max( 字段名 ) ：统计最大值
- count( 字段名 ) ：统计个数

```
mysql> select max(uid) from user_test;
+----------+
| max(uid) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

mysql> select min(uid) from user_test;
+----------+
| min(uid) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(uid) from user_test;
+------------+
| count(uid) |
+------------+
|         22 |
+------------+
1 row in set (0.00 sec)
```

### 操作查询结果
#### 查询结果排序
基本用法

SQL 查询
    ORDER BY 字段名 [ asc | desc ]

**通常是数值类型字段**

```
mysql> select * from user_test order by uid desc limit 2;
+----+---------+----------+------+------+------------------+-------------+---------------+
| id | name    | password | uid  | gid  | comment          | homedir     | shell         |
+----+---------+----------+------+------+------------------+-------------+---------------+
| 20 | admin   | x        | 1000 | 1000 | admin            | /home/admin | /bin/bash     |
| 16 | polkitd | x        |  999 |  998 | User for polkitd | /           | /sbin/nologin |
+----+---------+----------+------+------+------------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from user_test order by uid limit 2;
+----+------+----------+------+------+---------+---------+---------------+
| id | name | password | uid  | gid  | comment | homedir | shell         |
+----+------+----------+------+------+---------+---------+---------------+
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash     |
|  2 | bin  | x        |    1 |    1 | bin     | /bin    | /sbin/nologin |
+----+------+----------+------+------+---------+---------+---------------+
2 rows in set (0.01 sec)
```

#### 查询结果分组
基本用法

SQL 查询
    group by 字段名

**通常是字符类型字段**

#### 查询结果过滤
基本用法
- SQL 查询 HAVING 条件表达式；
- SQL 查询 where 条件 HAVING 条件表达式；
- SQL 查询 group by 字段名 HAVING 条件表达式；

#### 限制查询结果显示行数
基本用法
- SQL 查询 LIMIT N; 显示查询结果前 N 条记录
- SQL 查询 LIMIT N,M ；显示指定范围内的查询记录
- SQL 查询 where 条件查询 LIMIT N ；显示查询结果前 N 条记录
- SQL 查询 where 条件查询 LIMIT N ， M ；显示指定范围内的查询记录

```
mysql> select * from user_test order by uid limit 2,4;
+----+--------+----------+------+------+---------+----------------+---------------+
| id | name   | password | uid  | gid  | comment | homedir        | shell         |
+----+--------+----------+------+------+---------+----------------+---------------+
|  3 | daemon | x        |    2 |    2 | daemon  | /sbin          | /sbin/nologin |
|  4 | adm    | x        |    3 |    4 | adm     | /var/adm       | /sbin/nologin |
|  5 | lp     | x        |    4 |    7 | lp      | /var/spool/lpd | /sbin/nologin |
|  6 | sync   | x        |    5 |    0 | sync    | /sbin          | /bin/sync     |
+----+--------+----------+------+------+---------+----------------+---------------+
4 rows in set (0.00 sec)
```

晓松

MySQL 匹配条件

MySQL 查询条件、查询函数和查询结果处理

晓松

MySQL 匹配条件 MySQL 查询条件、查询函数和查询结果处理

MySQL 匹配条件

MySQL 查询条件、查询函数和查询结果处理