MySQL Select查询

1. 基本语法：

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

1.1 全部查询

SELECT * FROM < 表名 >;

1.2 选择指定字段

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

1.3 过滤字段中的重复值: DISTINCT

SELECT DISTINCT <字段名> FROM <表名>;

1.4 别名 :AS

给很长的表或者字段可以起个别名，用别名进行查询

mysql> select a.id,a.name from user as a;
+----+------+
| id | name |
+----+------+
|  1 | Wang |
+----+------+

mysql> select id as userID from user;
+--------+
| userID |
+--------+
| 1 |
+--------+

1.5 限制查询结果的记录数量：LIMIT

<LIMIT> [<位置偏移量>,] <行数>

“位置偏移量”指从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推）；

第二个参数“行数”指示返回的记录条数。

# 查询从第3+1条记录开始的4条数据。也可以理解为跳过前面3条记录，选取后面的4条数据。
mysql> SELECT * FROM <表名> LIMIT 3,4;

1.6 排序查询：ORDER BY

ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]

ASC 升序
DESC 降序

e.g.

从user中查询所有字段，先按照 balance 字段进行降序排列，再按照 debt 字段升序排序

mysql> select * from user order by balance desc,debt asc;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  9 | NULL | NULL     |    NULL |   70 |
|  8 | NULL | NULL     |    NULL |   80 |
|  7 | Zhou | NULL     |    NULL |   90 |
+----+------+----------+---------+------+

1.7 条件查询： WHERE

WHERE <查询条件> {<判定运算1>，<判定运算2>，…}

判断条件：
1. >,<,<=,>=,=,<>,!=,<=>        # 注：<=> 也是用来判断两个数据是否相等，它可以和 NULL 进行比较，其他方面和 = 没啥差别。
2. [NOT]LIKE
3. IS [NOT] NULL
4. [NOT] BETWEEN ... AND ...
5. [NOT][REGEXP|RLIKE]

单一查询：

mysql> select balance from user where name = 'Wang';
+---------+
| balance |
+---------+
|   15000 |
+---------+

多条件查询：

mysql> select name,balance from user where balance>=3000 and balance<16000;
+------+---------+
| name | balance |
+------+---------+
| Wang |   15000 |
| song |    3000 |
+------+---------+

1.8 LIKE 模糊查询

<表达式1> [NOT] LIKE <表达式2>

通配符：

% ：可以匹配 0 个或多个字符，不匹配空值。
_ ：只匹配一个字符。

mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
+----+------+----------+---------+------+

mysql> select name,debt from user where name like '%ng';
+------+------+
| name | debt |
+------+------+
| Wang |    0 |
| song |  100 |
+------+------+

1.9 查询日期

select * from <table> where date<'2019-01-20';

select * from <table> where date between '2019-01-20' and '2019-10-01';

连接

2.0 内连接查询

mysql> select * from grade;
+------+--------+
| gid  | record |
+------+--------+
|    1 |    100 |
|    2 |    200 |
|    3 |    400 |
+------+--------+


mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
+----+------+----------+---------+------+

# 内连接查询
mysql> select * from user inner join grade where user.id=grade.gid;
+----+------+----------+---------+------+------+--------+
| id | name | password | balance | debt | gid  | record |
+----+------+----------+---------+------+------+--------+
|  1 | Wang | 1234     |   15000 |    0 |    1 |    100 |
|  2 | song | 1234     |    3000 |  100 |    2 |    200 |
|  3 | li   | 1234     |    2000 |    0 |    3 |    400 |
+----+------+----------+---------+------+------+--------+

# 不写 inner join，默认也是内连接
mysql> select * from user, grade where user.id=grade.gid;
+----+------+----------+---------+------+------+--------+
| id | name | password | balance | debt | gid  | record |
+----+------+----------+---------+------+------+--------+
|  1 | Wang | 1234     |   15000 |    0 |    1 |    100 |
|  2 | song | 1234     |    3000 |  100 |    2 |    200 |
|  3 | li   | 1234     |    2000 |    0 |    3 |    400 |
+----+------+----------+---------+------+------+--------+

# 指定查询哪些字段
mysql> select user.id,user.name,user.balance,grade.gid from user, grade where user.id=grade.gid;
+----+------+---------+------+
| id | name | balance | gid  |
+----+------+---------+------+
|  1 | Wang |   15000 |    1 |
|  2 | song |    3000 |    2 |
|  3 | li   |    2000 |    3 |
+----+------+---------+------+

2.1 外连接

左外连接：

基于左边的表，将右边表中的字段合并过来

mysql> select id,name,record from user left join grade on user.id = grade.gid;
+----+------+--------+
| id | name | record |
+----+------+--------+
|  1 | Wang |    100 |
|  2 | song |    200 |
|  3 | li   |    400 |
|  4 | zhao |   NULL |
|  5 | qian |   NULL |
|  6 | sun  |   NULL |
|  7 | Zhou |   NULL |
|  8 | NULL |   NULL |
|  9 | NULL |   NULL |
+----+------+--------+

右外连接：

mysql> select id,name,record from user right join grade on user.id = grade.gid;
+------+------+--------+
| id   | name | record |
+------+------+--------+
|    1 | Wang |    100 |
|    2 | song |    200 |
|    3 | li   |    400 |
+------+------+--------+

2.2 子查询

<表达式> [NOT] IN <子查询>

此处的子查询只能返回一行数据

比较运算符

<表达式> {= | < | > | >= | <= | <=> | < > | != }
{ ALL | SOME | ANY} <子查询>

EXISTS

判断子查询是否存在，如果存在，True，否则False；

IN：

mysql> select * from grade;
+------+--------+
| gid  | record |
+------+--------+
|    1 |    100 |
|    2 |    200 |
|    3 |    400 |
+------+--------+


mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
+----+------+----------+---------+------+

mysql> select * from user where id in (select gid from grade where gid=2);
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  2 | song | 1234     |    3000 |  100 |
+----+------+----------+---------+------+

EXISTS：

mysql> select * from user where exists (select * from grade where gid=1);
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
|  8 | NULL | NULL     |    NULL |   80 |
|  9 | NULL | NULL     |    NULL |   70 |
+----+------+----------+---------+------+

2.3 分组查询

分组查询查到是每个组，相同的数据会合并到一个组里，是看不到的。可以使用聚合函数对每个组的数据进行求和、统计数量等。

mysql> select * from user;
+----+------+----------+---------+------+
| id | name | password | balance | debt |
+----+------+----------+---------+------+
|  1 | Wang | 1234     |   15000 |    0 |
|  2 | song | 1234     |    3000 |  100 |
|  3 | li   | 1234     |    2000 |    0 |
|  4 | zhao | NULL     |    NULL | NULL |
|  5 | qian | NULL     |    NULL | NULL |
|  6 | sun  | NULL     |    NULL | NULL |
|  7 | Zhou | NULL     |    NULL |   90 |
| 10 | Wang | NULL     |   15000 | NULL |
+----+------+----------+---------+------+

# 按照名字分组，查询每个分组的id，name，balance的总和。sum 是聚合函数之一。
mysql> select id,name,sum(balance) from user group by name;
+----+------+--------------+
| id | name | sum(balance) |
+----+------+--------------+
|  1 | Wang |        30000 |
|  2 | song |         3000 |
|  3 | li   |         2000 |
|  4 | zhao |         NULL |
|  5 | qian |         NULL |
|  6 | sun  |         NULL |
|  7 | Zhou |         NULL |
+----+------+--------------+

HAVING

having，给分组添加过滤，筛选符合条件的分组

mysql> select count(name) ,name from user group by name having count(name)>1;
+-------------+------+
| count(name) | name |
+-------------+------+
|           2 | Wang |
+-------------+------+

2.4 正则表达式查询：REGEXP

^　　　　匹配开始字符　　　　　　　　'^b'匹配以b开头的字符串

$　　　　匹配结束字符　　　　　　　　'b$'匹配以b结束的字符串

.　　　　 匹配任意单个字符　　　　　　

*　　　　匹配前面的字符0次或多次　　   a*b 匹配 b、ab、aaaaaaaab

+　　　　匹配前面的字符1次或多次　　  a+b 匹配 ab、aaaaaab

<字符>　 匹配包含的字符　　　　　　　 <fa> 匹配 afa,fa,bbbfaccc

[]　　　　匹配内部的任意一个字符　　　[xyz] 匹配 x 或 y 或 z

[^]　　　  匹配不在括号内的字符　　　　[^1-9] 不匹配 1-9

{n,}　　　匹配前一个字符最少n次

{n,m}　　匹配前一个字符最少n次，最多m次。