MySQL进阶查询

MySQL进阶查询

按关键字排序

使用ORDER BY语句来实现排序

排序可针对一个或多个字段

ASC:升序，默认排序方式

DESC:降序

ORDER BY的语法结构

select column1，column2，...from table_name order by column1，column2，...asc|desc;

按单字段排序

mysql> select name,fenshu from bbb where fenshu>=50 order by fenshu;
+----------+--------+
| name     | fenshu |
+----------+--------+
| zhaoliu  |     50 |
| lisi     |     60 |
| wangwu   |     70 |
| tianqi   |     70 |
| wanger   |     88 |
| zhangsan |     89 |
+----------+--------+
6 rows in set (0.00 sec)

mysql> select name,fenshu from bbb where fenshu>=50 order by fenshu desc;
+----------+--------+
| name     | fenshu |
+----------+--------+
| zhangsan |     89 |
| wanger   |     88 |
| wangwu   |     70 |
| tianqi   |     70 |
| lisi     |     60 |
| zhaoliu  |     50 |
+----------+--------+
6 rows in set (0.00 sec)

按多字段排序

mysql> select id,name,fenshu from bbb where fenshu>=50 order by fenshu desc,id desc;
+----+----------+--------+
| id | name     | fenshu |
+----+----------+--------+
|  2 | zhangsan |     89 |
|  1 | wanger   |     88 |
|  6 | tianqi   |     70 |
|  4 | wangwu   |     70 |
|  3 | lisi     |     60 |
|  5 | zhaoliu  |     50 |
+----+----------+--------+
6 rows in set (0.00 sec)

对结果进行分组

使用GROUP BY语句来实现分组

GROUP BY通常都是结合聚合函数一起使用的，常用的聚合函数包括:计数（COUNT)、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN）

GROUP BY分组的时候可以按一个或多个字段对结果进行分组

GROUP BY的语法结构

SELECT column_name,aggregate_function(column_name)FROM table_name
WHERE column_name operator value GROUP BY column_name;

mysql> select avg(fenshu) from bbb;
+-------------+
| avg(fenshu) |
+-------------+
|     71.1667 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(name),fenshu from bbb where fenshu>=60 group by fenshu;
+-------------+--------+
| count(name) | fenshu |
+-------------+--------+
|           1 |     60 |
|           2 |     70 |
|           1 |     88 |
|           1 |     89 |
+-------------+--------+
4 rows in set (0.00 sec)

mysql> select count(name),fenshu from bbb where fenshu>=60 group by fenshu order by fenshu desc;
+-------------+--------+
| count(name) | fenshu |
+-------------+--------+
|           1 |     89 |
|           1 |     88 |
|           2 |     70 |
|           1 |     60 |
+-------------+--------+
4 rows in set (0.00 sec)

限制结果条目

只返回SELECT查询结果的第一行或前几行

使用LIMIT语句限制条目

LIMIT语法结构

SELECT column1,column2,..FROM table_name LIMIT [offset,] number;
[offset,]代表的的是位置偏移量，从0开始    
number是返回记录行的最大数目

mysql> select id,name from bbb limit 3,3;	#从第4条记录开始显示之后的3条数据
+----+---------+
| id | name    |
+----+---------+
|  4 | wangwu  |
|  5 | zhaoliu |
|  6 | tianqi  |
+----+---------+
3 rows in set (0.00 sec)

mysql> select id,name,fenshu from bbb order by fenshu desc limit 3;	
#显示fenshu最高的前三
+----+----------+--------+
| id | name     | fenshu |
+----+----------+--------+
|  2 | zhangsan |     89 |
|  1 | wanger   |     88 |
|  6 | tianqi   |     70 |
+----+----------+--------+
3 rows in set (0.00 sec)

设置别名

使用AS语句设置别名，关键字AS可省略

设置别名时，保证不能与库中其他表或字段名称冲突

别名的语法结构

SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s) FROM table_name AS alias_name;

mysql> select count(*) from bbb;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) as 人数 from bbb;
+--------+
| 人数   |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

mysql> select name as '姓名',address as '地址' from  bbb order by address;
+----------+--------+
| 姓名     | 地址   |
+----------+--------+
| wanger   | nanjin |
| zhangsan | nanjin |
| lisi     | nanjin |
| wangwu   | nanjin |
| zhaoliu  | nanjin |
| tianqi   | nanjin |
+----------+--------+
6 rows in set (0.00 sec)

多表查询

内连接

mysql> select * from bbb;
+----+----------+--------+---------+
| id | name     | fenshu | address |
+----+----------+--------+---------+
|  1 | wanger   |     88 | nanjin  |
|  2 | zhangsan |     89 | nanjin  |
|  3 | lisi     |     60 | nanjin  |
|  4 | wangwu   |     70 | nanjin  |
|  5 | zhaoliu  |     50 | nanjin  |
|  6 | tianqi   |     70 | nanjin  |
+----+----------+--------+---------+
6 rows in set (0.00 sec)

mysql> select * from test2;
+----+--------+
| id | xingqv |
+----+--------+
|  1 | 篮球   |
|  2 | 足球   |
|  3 | 电脑   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from bbb join test2 on bbb.id=test2.id;
+----+----------+--------+---------+----+--------+
| id | name     | fenshu | address | id | xingqv |
+----+----------+--------+---------+----+--------+
|  1 | wanger   |     88 | nanjin  |  1 | 篮球    |
|  2 | zhangsan |     89 | nanjin  |  2 | 足球    |
|  3 | lisi     |     60 | nanjin  |  3 | 电脑    |
+----+----------+--------+---------+----+--------+

模糊查询

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

mysql> select id,name from bbb where name like 'z%';
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
|  5 | zhaoliu  |
+----+----------+
2 rows in set (0.00 sec)

mysql> select id,name from bbb where name like 'z_______';
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

子查询

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

mysql> select id,name,fenshu from bbb where fenshu in (select fenshu from bbb where fenshu>=60);
+----+----------+--------+
| id | name     | fenshu |
+----+----------+--------+
|  1 | wanger   |     88 |
|  2 | zhangsan |     89 |
|  3 | lisi     |     60 |
|  4 | wangwu   |     70 |
|  6 | tianqi   |     70 |
+----+----------+--------+
5 rows in set (0.00 sec)

运算

加法
减法
乘法
/ 除法
% 取余数

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

比较运算符

= 等于
‘>’ 大于
'< ’ 小于
‘>=’ 大于等于
'<= ’ 小于等于
!=或<> 不等于
IS NULL 判断一个值是否为 NULL
IS NOT NULL 判断一个值是否不为 NULL
BETWEEN AND 两者之间
IN 在集合中
LIKE 通配符匹配
GREATEST 两个或多个参数时返回最大值
LEAST 两个或多个参数时返回最小值
REGEXP 正则表达式

mysql> select 'a'='a','a'='b',3+2=1+4;
+---------+---------+---------+
| 'a'='a' | 'a'='b' | 3+2=1+4 |
+---------+---------+---------+
|       1 |       0 |       1 |
+---------+---------+---------+
1 row in set (0.00 sec)
#成立则返回 1，不成立则返回 0；

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
mysql> select least(11,222,3333),greatest(11,222,3333);
+--------------------+-----------------------+
| least(11,222,3333) | greatest(11,222,3333) |
+--------------------+-----------------------+
|                 11 |                  3333 |
+--------------------+-----------------------+
1 row in set (0.00 sec)

in 和 not in
判断 值 是否在 一组数字中
mysql> select 1 in (1,2,3),2 in (1,3,4);
+--------------+--------------+
| 1 in (1,2,3) | 2 in (1,3,4) |
+--------------+--------------+
|            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)

逻辑运算符

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 值的操作数，如果两者都是 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 次

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

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

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

用 . 代替一个字符
mysql> select * from sanban where name regexp 'l.si';
+-----+--------+---------+
| num | name   | address |
+-----+--------+---------+
|   1 | lisi   |       5 |
+-----+--------+---------+
1 row in set (0.00 sec)
或 关系
mysql> select * from sanban where name regexp 'lisi|wangwu';
+-----+--------+---------+
| num | name   | address |
+-----+--------+---------+
|   1 | lisi   |       5 |
|   2 | wangwu |       6 |
+-----+--------+---------+
1 row in set (0.00 sec)

##注：aa*，星号前面的a有零个，一个，或多个，所以只要有a的就能出来
mysql> select * from sanban where name regexp 'aa*';
+-----+---------------+---------+
| num | name          | address |
+-----+---------------+---------+
|   7 | as       	  |       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 |
+-----+-----------+---------+
|   1 | lisi  	  |       5 |
|   2 | wangwu 	  |       6 |
+-----+-----------+---------+
3 rows in set (0.00 sec)