MySQL select 命令

select理解

MySQL数据库中，数据量往往很庞大，所以我们需要一个强大的语句。
巧的是，MySQL中正好有一个 DQL 语句供我们 查询/排序/连接/运算 数据，他就是—— select ！

来吧！展示！！

实现准备两个表

mysql> select * from chengshi;
+---------+--------+--------+
| address | name   | sheng  |
+---------+--------+--------+
|       1 | 南京   | 江苏   |
|       2 | 苏州   | 江苏   |
|       3 | 镇江   | 江苏   |
|       4 | 常州   | 江苏   |
+---------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from sanban;
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   1 | caicai        |       1 |
|   2 | chengu        |       1 |
|   3 | chenjian      |       3 |
|   4 | chenqiang     |       2 |
|   5 | guoyuwei      |       4 |
|   6 | huchangan     |       1 |
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
7 rows in set (0.00 sec)

按关键字排序

使用语句 order by 实现排序，可以对一个或多个字段进行排序
ASC：升序，不用敲，默认就是这个
DESC：降序

select 字段1 字段2 from 表名 order by 字段 升/降序；
单字段

表名：sanban  字段：num  name  降序
mysql> select num,name from sanban order by num desc;
+-----+---------------+
| num | name          |
+-----+---------------+
|   7 | wangxingjiang |
|   6 | huchangan     |
|   5 | guoyuwei      |
|   4 | chenqiang     |
|   3 | chenjian      |
|   2 | chengu        |
|   1 | caicai        |
+-----+---------------+
7 rows in set (0.00 sec)

多字段

注：在 address 相同时，根据 num 进行排序
mysql> select address,name,num from sanban order by address desc,num desc; 
+---------+---------------+-----+
| address | name          | num |
+---------+---------------+-----+
|       4 | guoyuwei      |   5 |
|       3 | chenjian      |   3 |
|       2 | chenqiang     |   4 |
|       1 | wangxingjiang |   7 |
|       1 | huchangan     |   6 |
|       1 | chengu        |   2 |
|       1 | caicai        |   1 |
+---------+---------------+-----+
7 rows in set (0.00 sec)

对查询结果进行分组

使用 group by 语句进行分组，一般需要结合函数使用
可以根据一个或者多个字段对结果进行分组
Count（字段名称） 计数
Sum（字段名称） 求和
Avg（字段名称）平均值
Max（字段名称）最大值
Min（字段名称）最小值

统计 address 相同的值的数量，显示address，根据address排序
mysql> select count(address),address from sanban where address<=4 group by address order by address;
+----------------+---------+
| count(address) | address |
+----------------+---------+
|              4 |       1 |
|              1 |       2 |
|              1 |       3 |
|              1 |       4 |
+----------------+---------+
4 rows in set (0.00 sec)

limit 限制输出条目

输出查询出的几行
0，2 从第一行开始数两行，包括自身
第一行为行号0

mysql> select count(address),address from sanban where address<=4 group by address order by address limit 0,2;
+----------------+---------+
| count(address) | address |
+----------------+---------+
|              4 |       1 |
|              1 |       2 |
+----------------+---------+
2 rows in set (0.00 sec)

mysql> select count(address),address from sanban where address<=4 group by address order by address limit 1,3;
+----------------+---------+
| count(address) | address |
+----------------+---------+
|              1 |       2 |
|              1 |       3 |
|              1 |       4 |
+----------------+---------+
3 rows in set (0.00 sec)

别名

mysql> select count(*) as'条目数量' from sanban;
+--------------+
| 条目数量     |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

mysql> select name as '姓名',address as '地址代码' from sanban order by address;
+---------------+--------------+
| 姓名          | 地址代码     |
+---------------+--------------+
| caicai        |            1 |
| chengu        |            1 |
| huchangan     |            1 |
| wangxingjiang |            1 |
| chenqiang     |            2 |
| chenjian      |            3 |
| guoyuwei      |            4 |
+---------------+--------------+
7 rows in set (0.00 sec)

多表查询

我改了下表，把 sanban 的caicai的城市改成了上海

内连接

mysql> select * from sanban;
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   1 | caicai        |       5 |
|   2 | chengu        |       1 |
|   3 | chenjian      |       3 |
|   4 | chenqiang     |       2 |
|   5 | guoyuwei      |       4 |
|   6 | huchangan     |       1 |
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
7 rows in set (0.00 sec)

mysql> select * from chengshi;
+---------+--------+--------+
| address | name   | sheng  |
+---------+--------+--------+
|       1 | 南京   | 江苏   |
|       2 | 苏州   | 江苏   |
|       3 | 镇江   | 江苏   |
|       4 | 常州   | 江苏   |
|       5 | 上海   | 上海   |
+---------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from sanban join chengshi on sanban.address=chengshi.address;
+-----+---------------+---------+---------+--------+--------+
| num | name          | address | address | name   | sheng  |
+-----+---------------+---------+---------+--------+--------+
|   1 | caicai        |       5 |       5 | 上海   | 上海   |
|   2 | chengu        |       1 |       1 | 南京   | 江苏   |
|   3 | chenjian      |       3 |       3 | 镇江   | 江苏   |
|   4 | chenqiang     |       2 |       2 | 苏州   | 江苏   |
|   5 | guoyuwei      |       4 |       4 | 常州   | 江苏   |
|   6 | huchangan     |       1 |       1 | 南京   | 江苏   |
|   7 | wangxingjiang |       1 |       1 | 南京   | 江苏   |
+-----+---------------+---------+---------+--------+--------+
7 rows in set (0.00 sec)

```bash
mysql> select sanban.num as '学号',sanban.name as '姓名',chengshi.name as '所在城市',chengshi.sheng as '所在省' from sanban join chengshi on sanban.address=chengshi.address;
+--------+---------------+--------------+-----------+
| 学号   | 姓名          | 所在城市     | 所在省    |
+--------+---------------+--------------+-----------+
|      1 | caicai        | 上海         | 上海      |
|      2 | chengu        | 南京         | 江苏      |
|      3 | chenjian      | 镇江         | 江苏      |
|      4 | chenqiang     | 苏州         | 江苏      |
|      5 | guoyuwei      | 常州         | 江苏      |
|      6 | huchangan     | 南京         | 江苏      |
|      7 | wangxingjiang | 南京         | 江苏      |
+--------+---------------+--------------+-----------+
7 rows in set (0.00 sec)

外连接

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分， 有左连接和右连接之分。
左连接
以左边表为主匹配右边

+-----+---------------+---------+---------+--------+--------+
| num | name          | address | address | name   | sheng  |
+-----+---------------+---------+---------+--------+--------+
|   2 | chengu        |       1 |       1 | 南京   | 江苏   |
|   6 | huchangan     |       1 |       1 | 南京   | 江苏   |
|   7 | wangxingjiang |       1 |       1 | 南京   | 江苏   |
|   4 | chenqiang     |       2 |       2 | 苏州   | 江苏   |
|   3 | chenjian      |       3 |       3 | 镇江   | 江苏   |
|   5 | guoyuwei      |       4 |       4 | 常州   | 江苏   |
|   1 | caicai        |       5 |       5 | 上海   | 上海   |
|   8 | aaaaabbc      |       6 |       6 | wuxi   | NULL   |
+-----+---------------+---------+---------+--------+--------+
8 rows in set (0.00 sec)

右链接
以右边表为主匹配左边

mysql> select * from sanban right join chengshi on sanban.address=chengshi.addr
+------+---------------+---------+---------+--------+--------+
| num  | name          | address | address | name   | sheng  |
+------+---------------+---------+---------+--------+--------+
|    1 | caicai        |       5 |       5 | 上海   | 上海   |
|    2 | chengu        |       1 |       1 | 南京   | 江苏   |
|    3 | chenjian      |       3 |       3 | 镇江   | 江苏   |
|    4 | chenqiang     |       2 |       2 | 苏州   | 江苏   |
|    5 | guoyuwei      |       4 |       4 | 常州   | 江苏   |
|    6 | huchangan     |       1 |       1 | 南京   | 江苏   |
|    7 | wangxingjiang |       1 |       1 | 南京   | 江苏   |
|    8 | aaaaabbc      |       6 |       6 | wuxi   | NULL   |
+------+---------------+---------+---------+--------+--------+
8 rows in set (0.00 sec)

模糊查询

使用 mysql通配符
一般结合 like where使用
常见通配符有：
% 表示零个，一个或多个字符
_ 表示单个字符，有几个就是几个字符

% 用法

匹配 sanban 以 c开头的人
mysql> select num,name from sanban where name like 'c%';
+-----+-----------+
| num | name      |
+-----+-----------+
|   1 | caicai    |
|   2 | chengu    |
|   3 | chenjian  |
|   4 | chenqiang |
+-----+-----------+
4 rows in set (0.00 sec)

_用法

匹配c开头，有六个字符的人
mysql> select num,name from sanban where name like 'c_____';  ##五个_
+-----+--------+
| num | name   |
+-----+--------+
|   1 | caicai |
|   2 | chengu |
+-----+--------+
2 rows in set (0.00 sec)

结合使用

mysql> select num,name from sanban where name like '%xing_____';
+-----+---------------+
| num | name          |
+-----+---------------+
|   7 | wangxingjiang |
+-----+---------------+
1 row in set (0.00 sec)

子查询

括号里面的成为子语句
优先被执行
成为外面语句的条件

mysql> select num,name,address from sanban where address in (select address from sanban where address=1);
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   2 | chengu        |       1 |
|   6 | huchangan     |       1 |
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
3 rows in set (0.00 sec)


##注： ！=取反
mysql> select num,name,address from sanban where address in (select address from sanban where address!=1);
+-----+-----------+---------+
| num | name      | address |
+-----+-----------+---------+
|   1 | caicai    |       5 |
|   3 | chenjian  |       3 |
|   4 | chenqiang |       2 |
|   5 | guoyuwei  |       4 |
+-----+-----------+---------+
4 rows in set (0.00 sec)

运算

• 加法

• 减法

• 乘法
  / 除法
  % 取余数

mysql> select 1+2 as '加',2-3 as'减',3*4 as '乘',4/5 as '除',6%5 as '取余';
+-----+-----+-----+--------+--------+
| 加  | 减  | 乘  | 除     | 取余   |
+-----+-----+-----+--------+--------+
|   3 |  -1 |  12 | 0.8000 |      1 |
+-----+-----+-----+--------+--------+
1 row in set (0.00 sec)

除数 为 0 时
 mysql> select 5/0;
+------+
| 5/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

#先乘除后加减
mysql> select 2+3*4-8/2;
+-----------+
| 2+3*4-8/2 |
+-----------+
|   10.0000 |
+-----------+
1 row in set (0.00 sec)

比较运算符

这边比较牵扯到 ASC II 码，点击可以查看
= 等于
‘>’ 大于
'< ’ 小于
‘>=’ 大于等于
'<= ’ 小于等于
!=或<> 不等于
IS NULL 判断一个值是否为 NULL
IS NOT NULL 判断一个值是否不为 NULL
BETWEEN AND 两者之间
IN 在集合中
LIKE 通配符匹配
GREATEST 两个或多个参数时返回最大值
LEAST 两个或多个参数时返回最小值
REGEXP 正则表达式 过程在下面**

mysql> select 1=1,2!=3,3='3','a'='a','a'='b',3+2=1+4,'abc'=null;
+-----+------+-------+---------+---------+---------+------------+
| 1=1 | 2!=3 | 3='3' | 'a'='a' | 'a'='b' | 3+2=1+4 | 'abc'=null |
+-----+------+-------+---------+---------+---------+------------+
|   1 |    1 |     1 |       1 |       0 |       1 |       NULL |
+-----+------+-------+---------+---------+---------+------------+
1 row in set (0.00 sec)
##观察发现：成立则返回 1，不成立则返回 0；如果比较的两者有一个值是 NULL，则比较的结果就是 NULL；

mysql> select 1=1,1!=1,1<>1;
+-----+------+------+
| 1=1 | 1!=1 | 1<>1 |
+-----+------+------+
|   1 |    0 |    0 |
+-----+------+------+
1 row in set (0.00 sec)

between and 比较运算通常用于判断一个值是否落在某两个值之间

mysql> select 'a' between 'A' and 'Z' as 'A-Z';
+-----+
| A-Z |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select 'a' between 'a' and 'z' as 'a-z';
+-----+
| a-z |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

最小值 least 和 最大值 greatest
发现有 null 时，就会显示null

mysql> select least(500,20,3000),least(500,20,3000,null);
+--------------------+-------------------------+
| least(500,20,3000) | least(500,20,3000,null) |
+--------------------+-------------------------+
|                 20 |                    NULL |
+--------------------+-------------------------+
1 row in set (0.00 sec)

mysql> select greatest(500,20,3000),greatest(500,20,3000,null); 
+-----------------------+----------------------------+
| greatest(500,20,3000) | greatest(500,20,3000,null) |
+-----------------------+----------------------------+
|                  3000 |                       NULL |
+-----------------------+----------------------------+
1 row in set (0.00 sec)

in 和 not in
判断 值 是否在 一组数字中

mysql> select 10 in (8,9,10),12 in (8,9,10);
+----------------+----------------+
| 10 in (8,9,10) | 12 in (8,9,10) |
+----------------+----------------+
|              1 |              0 |
+----------------+----------------+
1 row in set (0.00 sec)

like 和 not like

mysql> mysql> select 'gundam' like 'gunda_','gundam' like 'gun%','gundam' not like 'raise';
+------------------------+----------------------+---------------------------+
| 'gundam' like 'gunda_' | 'gundam' like 'gun%' | 'gundam' not like 'raise' |
+------------------------+----------------------+---------------------------+
|                      1 |                    1 |                         1 |
+------------------------+----------------------+---------------------------+
1 row in set (0.00 sec)

NULL

mysql> select 1 is null,1 is not null,null is null;
+-----------+---------------+--------------+
| 1 is null | 1 is not null | null is null |
+-----------+---------------+--------------+
|         0 |             1 |            1 |
+-----------+---------------+--------------+
1 row in set (0.00 sec)

唉，我又改表了

插入一个 null
mysql> insert into chengshi(address,name) values (6,'wuxi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from chengshi;
+---------+--------+--------+
| address | name   | sheng  |
+---------+--------+--------+
|       1 | 南京   | 江苏   |
|       2 | 苏州   | 江苏   |
|       3 | 镇江   | 江苏   |
|       4 | 常州   | 江苏   |
|       5 | 上海   | 上海   |
|       6 | wuxi   | NULL   |
+---------+--------+--------+
6 rows in set (0.00 sec)

查询 null 的字段
mysql> select * from chengshi where sheng is null;
+---------+------+-------+
| address | name | sheng |
+---------+------+-------+
|       6 | wuxi | NULL  |
+---------+------+-------+
1 row in set (0.00 sec)

查询不为 null 的
mysql> select * from chengshi where sheng is not null;
+---------+--------+--------+
| address | name   | sheng  |
+---------+--------+--------+
|       1 | 南京   | 江苏   |
|       2 | 苏州   | 江苏   |
|       3 | 镇江   | 江苏   |
|       4 | 常州   | 江苏   |
|       5 | 上海   | 上海   |
+---------+--------+--------+
5 rows in set (0.00 sec)

逻辑运算符

百度百科对逻辑运算解释
NOT 或 ！ 逻辑非
AND 或 && 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或

运算方式如下：
且 0&&0=0 1&&0=0 0&&1=0 1&&1=1
或 0||0=0 1||0=1 0||1=1 1||1=1
异或 0^0=0 1^0=1 0^1=1 1^1=0

逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。

mysql> select !2,not 2,!0,not 0,!(2-2);                                   
+----+-------+----+-------+--------+
| !2 | not 2 | !0 | not 0 | !(2-2) |
+----+-------+----+-------+--------+
|  0 |     0 |  1 |     1 |      1 |
+----+-------+----+-------+--------+
1 row in set (0.00 sec)

逻辑与
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。

mysql> select 5 and 6,5 && 6,'a' and null;
+---------+--------+--------------+
| 5 and 6 | 5 && 6 | 'a' and null |
+---------+--------+--------------+
|       1 |      1 |            0 |
+---------+--------+--------------+
1 row in set, 1 warning (0.00 sec

逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。逻辑或通常使用 OR 或者||来表示。

mysql> select 2 or 3,3 || 4,0 or null,1 && null;
+--------+--------+-----------+-----------+
| 2 or 3 | 3 || 4 | 0 or null | 1 && null |
+--------+--------+-----------+-----------+
|      1 | 34     |      NULL |      NULL |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。

mysql> select 1 xor 1,1 xor 0,0 xor 1,0 xor 0,1 xor null,1 xor null;
+---------+---------+---------+---------+------------+------------+
| 1 xor 1 | 1 xor 0 | 0 xor 1 | 0 xor 0 | 1 xor null | 1 xor null |
+---------+---------+---------+---------+------------+------------+
|       0 |       1 |       1 |       0 |       NULL |       NULL |
+---------+---------+---------+---------+------------+------------+
1 row in set (0.00 sec)

位运算
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。
& 按位与
| 按位或
~ 按位取反
^ 按位异或
<< 按位左移
>> 按位右移

mysql> select 9 & 10,10 | 9,10 ^ 9;
+--------+--------+--------+
| 9 & 10 | 10 | 9 | 10 ^ 9 |
+--------+--------+--------+
|      8 |     11 |      3 |
+--------+--------+--------+
1 row in set (0.00 sec)
9 二进制 1001
10 二进制 1010
例：9&10=1001&1010=1000=十进制8

左移 << 右移>>

mysql> select 9<<3,10>>2;
+------+-------+
| 9<<3 | 10>>2 |
+------+-------+
|   72 |     2 |
+------+-------+
1 row in set (0.00 sec)
例2：9<<3=1001000=十进制72

正则表达式

唉…正则难记啊…
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. <-- 这有个点 匹配任何单个字符

• 匹配零个或多个在它前面的字符

• 匹配前面的字符 1 次或多次
  A|B 匹配 A 或 B
  […] 匹配字符集合中的任意一个字符
  [^…] 匹配不在括号中的任何字符
  {n} 匹配前面的字符串 n 次
  {n,m} 匹配前面的字符串至少 n 次，至多m 次

查找以 cai 为开头的
mysql> select * from sanban where name regexp '^cai';
+-----+--------+---------+
| num | name   | address |
+-----+--------+---------+
|   1 | caicai |       5 |
+-----+--------+---------+
1 row in set (0.00 sec)

查找以 gu 为结尾的
mysql> select * from sanban where name regexp 'gu$';
+-----+--------+---------+
| num | name   | address |
+-----+--------+---------+
|   2 | chengu |       1 |
+-----+--------+---------+
1 row in set (0.00 sec)

查找 记录 中包含 aic 的
mysql> select * from sanban where name regexp 'aic';
+-----+--------+---------+
| num | name   | address |
+-----+--------+---------+
|   1 | caicai |       5 |
+-----+--------+---------+
1 row in set (0.00 sec)

用 . 代替一个字符
mysql> select * from sanban where name regexp 'wang.ingjiang';
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
1 row in set (0.00 sec)

或 关系
mysql> select * from sanban where name regexp 'wang|cai';
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   1 | caicai        |       5 |
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
2 rows in set (0.00 sec)

唉，我又插了一个
mysql> insert into sanban(num,name,address) values (8,'aaaaabbc',6);
Query OK, 1 row affected (0.00 sec)
##注：aa*，星号前面的a有零个，一个，或多个，所以只要有a的就能出来
mysql> select * from sanban where name regexp 'aa*';
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   1 | caicai        |       5 |
|   3 | chenjian      |       3 |
|   4 | chenqiang     |       2 |
|   6 | huchangan     |       1 |
|   7 | wangxingjiang |       1 |
|   8 | aaaaabbc      |       6 |
+-----+---------------+---------+
6 rows in set (0.00 sec)

+ 匹配前面字符至少一次
mysql> select * from sanban where name regexp 'aaaaa+';
+-----+----------+---------+
| num | name     | address |
+-----+----------+---------+
|   8 | aaaaabbc |       6 |
+-----+----------+---------+
1 row in set (0.00 sec)

匹配 d-z 开头的
mysql> select * from sanban where name regexp '^[d-z]';
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   5 | guoyuwei      |       4 |
|   6 | huchangan     |       1 |
|   7 | wangxingjiang |       1 |
+-----+---------------+---------+
3 rows in set (0.00 sec)