MySQL高级语句(order by / group by / as 、通配符、运算符、null、limit、多表查询（内接、左连接、右连接）)

https://www.cnblogs.com/clsn/p/8087417.html #auto-id-21

https://www.cnblogs.com/itdragon/p/8194622.html 行锁

MySQL高级语句

字段1类型： int（5） 整型 00000-99999
double 浮点型 8字节
decimal(5,2) 有效数字5位，小数点后面保留2位
如：100.00 099.50
float 单精度 浮点
char （10） 固定长度字符串
varchar （20） 可变长度字符串
char 字符
字段1约束：非空约束：内容不允许为空
主键约束：非空且唯一 标识
默认值 ：若没有填数字，默认预先设定的值填写
自增特性：auto_increment

1. order by 排序

1.1 单字段字符排序

• 使用ORDER BY语句来实现排序
• 排序可针对一个或多个字段
• ASC：升序，默认排序方式
• DESC：降序
• 一般默认是升序

注意：NULL值的排序

在MySQL中，把NULL值当做一列值中的最小值对待。

因此，升序排序时，它出现在最前面

ORDER BY的语法结构

order by

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... 
ASC|DESC;

案例

mysql> select * from info05;
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  2 | lisi     |     9 |    56 |
|  3 | wangwu   |     5 |    54 |
|  4 | lili     |     3 |    78 |
+----+----------+-------+-------+
4 rows in set (0.00 sec)
mysql> select id,name,level from info05 where score>80 order by score desc;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | zhangsan |     3 |
+----+----------+-------+
1 row in set (0.00 sec)

1.2 双字段字符排序

• 双字段进行排序时，根据前后顺序，先排序前面的条件。

如下

• 对表info05的level进行降序，然后相同内容的部分再次进行score分数进行降序。最终结果如图。

mysql> select id,name,level,score from info05 order by level desc,score desc;
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  5 | qq       |     9 |    67 |
|  2 | lisi     |     9 |    56 |
|  3 | wangwu   |     5 |    54 |
|  1 | zhangsan |     3 |    89 |
|  4 | lili     |     3 |    78 |
+----+----------+-------+-------+
5 rows in set (0.00 sec)

2. group by 对结果进行分组

• 使用GROUP BY语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组

聚合函数

函数

• count（字段名称）计数
• sum （字段名称） 求和
• avg （字段名称） 平均值
• max（字段名称） 最大值
• min （字段名称）最小值
• 聚合函数：只有一个值

GROUP BY的语法结构

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

案例一

mysql> select * from info05;
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  2 | lisi     |     9 |    56 |
|  3 | wangwu   |     5 |    54 |
|  4 | lili     |     3 |    78 |
|  5 | qq       |     9 |    67 |
+----+----------+-------+-------+
5 rows in set (0.00 sec)

#########################################

对表info05 进行平均值计算然后以等级分组查询。
mysql> select level,avg(score) as avg from info05 group by level;
+-------+---------+
| level | avg     |
+-------+---------+
|     3 | 83.5000 |
|     5 | 54.0000 |
|     9 | 61.5000 |
+-------+---------+
3 rows in set (0.00 sec)

案例二：查询最大值

mysql> select name,sum(score) as '最大值' from info05 group by name;
+----------+-----------+
| name     | 最大值    |
+----------+-----------+
| lili     |        78 |
| lisi     |        56 |
| qq       |        67 |
| wangwu   |        54 |
| zhangsan |        89 |
+----------+-----------+
5 rows in set (0.00 sec)

mysql> 

案例三：对player 表中level大于等于45进行计算并分组表示之后按降序显示

mysql> select count(name),level from player where level>=45 group by level order by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           4 |    46 |
|           2 |    45 |
|           1 |    47 |
+-------------+-------+

mysql> select level,count(level) as 等级个数 from info05 group by level order by count(level) desc;
+-------+--------------+
| level | 等级个数     |
+-------+--------------+
|     3 |            2 |
|     9 |            2 |
|     5 |            1 |
+-------+--------------+
3 rows in set (0.00 sec)

mysql> 

**案例四： 多表查询

mysql> select * from Websites
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+
9 rows in set (0.00 sec)

mysql> select * from access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+

9 rows in set (0.00 sec)

mysql>SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log LEFT JOIN Websites ON access_log.site_id=Websites.id
GROUP BY Websites.name; 
+------------+-----------+
| name            | nums |
+------------+-----------+
| Facebook       |     2 |
|   Google        |    2 |  
|   微博        |    1 |  
|   淘宝        |    1 |
|   菜鸟教程    |    3 | 
+------------+-----------+
5 rows in set (0.00 sec)

3. limit 限制结果条目

• 只返回SELECT查询结果的第一行或前几行
• 使用LIMIT语句限制条目

LIMIT语法结构

注意

• 位置偏移量，从0开始
• 返回记录行的最大数目

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number;

案例

位置偏移量，从0开始

mysql> select * from info05 limit 1,2;
+----+--------+-------+-------+
| id | name   | level | score |
+----+--------+-------+-------+
|  2 | lisi   |     9 |    56 |
|  3 | wangwu |     5 |    54 |
+----+--------+-------+-------+
2 rows in set (0.00 sec)

mysql> 

• 但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。
• 下面是一个实例，从第三位开始提取 3 个记录：

mysql> select * from info05 limit 2 offset 3;
+----+------+-------+-------+
| id | name | level | score |
+----+------+-------+-------+
|  4 | lili |     3 |    78 |
|  5 | qq   |     9 |    67 |
+----+------+-------+-------+
2 rows in set (0.00 sec)

mysql> 

4.设置别名 as

• 使用AS语句设置别名，关键字AS可省略
• 设置别名时，保证不能与库中其他表或字段名称冲突

别名的语法结构

• alias_name 列的别名
• table-alias_name 表的别名

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

案例

mysql> select count(*) as number from info05;
+--------+
| number |
+--------+
|      5 |
+--------+
1 row in set (0.01 sec)

注意

as不仅有别名的作用，也有相连的作用
如下作用：
新建表tmp并且将表player的数据复制到表tmp中

mysql> create table tmp as select * from player;
Query OK, 3218 rows affected (0.15 sec)
Records: 3218  Duplicates: 0  Warnings: 0

5. 通配符

• 用于替换字符串中的部分字符
• 通常配合LIKE一起使用，并协同WHERE完成查询

常用通配符

• % 表示零个、一个或多个
• _ 表示单个字符

案例

mysql> select id,name,level from player where name like '%s'; 
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
| 713 | guess   |    25 |
+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select id,name,level from player where name like '_uess'; 
+-----+-------+-------+
| id  | name  | level |
+-----+-------+-------+
| 713 | guess |    25 |
+-----+-------+-------+
1 row in set (0.01 sec)
mysql> select id,name,level from player where name like '_es%'; 
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2237 | leslieF |     3 |
+------+---------+-------+
1 row in set (0.01 sec)

6. 子查询

• 也称作内查询或者嵌套查询
• 先于主查询被执行，其结果将作为外层主查询的条件
• 在增删改查中都可以使用子查询
• 支持多层嵌套
• IN语句是用来判断某个值是否在给定的结果集中

mysql> select name,level from player where id in (select id from player where level>=45);
mysql> insert into tmp select * from player where id in (select id from player);
Query OK, 3218 rows affected (0.12 sec)          
Records: 3218  Duplicates: 0  Warnings: 0
mysql> update tmp set level = level - 7 where id in (select a.id from (select id from tmp where level >= 47) a);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> delete from tmp where id in (select a.id from (select id from tmp where level=47) a);
Query OK, 1 row affected (0.01 sec)
mysql> select id,name,level from tmp where id = (select id from tmp where name='shirley');
mysql> select count(*) as number from tmp where EXISTS （相当于if）(select id from tmp where name='shirley');  

注意

exists 用于判断后面的语句是否正确，如果正确那么执行外语句。如果不正确就不执行。

exists 后面的语句只能用select 如果select是正确的那么执行上一个语句，如果select筛选错误，那就不执行上一个语句

7. NULL

• 表示缺失的值
• 与数字0或者空白（spaces）是不同的
• 使用IS NULL或IS NOT NULL进行判断

NULL值和空值的区别

• 空值长度为0，不占空间；NULL值的长度为NULL，占用空间
• IS NULL无法判断空值
• 空值使用“=”或者“<>”来处理
• COUNT()计算时，NULL会忽略，空值会加入计算

mysql> select * from info05 where name is not null;
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  2 | lisi     |     9 |    56 |
|  3 | wangwu   |     5 |    54 |
|  4 | lili     |     3 |    78 |
|  5 | qq       |     9 |    67 |
|  6 |          |     9 |    98 |
|  7 | pp       |  NULL |    88 |
+----+----------+-------+-------+
7 rows in set (0.00 sec)

mysql> select * from info05 where level is null;
+----+------+-------+-------+
| id | name | level | score |
+----+------+-------+-------+
|  7 | pp   |  NULL |    88 |
+----+------+-------+-------+
1 row in set (0.01 sec)

mysql> 

8. 正则表达式

• 根据指定的匹配模式匹配记录中符合要求的特殊字符
• 使用REGEXP关键字指定匹配模式
• 常用匹配模式

^ 匹配开始字符

$ 匹配结束字符

. 匹配任意单个字符

* 匹配任意个前面的字符

+ 匹配前面字符至少1次

p1|p2 匹配p1或p2

[...] 匹配字符集中的任意一个字符

[^...] 匹配不在中括号内的任何字符

{n} 匹配前面的字符串n次

{n,m} 匹配前面的字符串至少n次，至多m次

案例

mysql> select * from info05 where name regexp '[^wangwu]';
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  2 | lisi     |     9 |    56 |
|  4 | lili     |     3 |    78 |
|  5 | qq       |     9 |    67 |
+----+----------+-------+-------+
4 rows in set (0.00 sec)

mysql> select * from info05 where name regexp '[^w]';
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  2 | lisi     |     9 |    56 |
|  3 | wangwu   |     5 |    54 |
|  4 | lili     |     3 |    78 |
|  5 | qq       |     9 |    67 |
+----+----------+-------+-------+
5 rows in set (0.00 sec)

mysql> select * from info05 where name regexp 'w+';
+----+--------+-------+-------+
| id | name   | level | score |
+----+--------+-------+-------+
|  3 | wangwu |     5 |    54 |
+----+--------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from info05 where name regexp 'a{1,2}';
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  3 | wangwu   |     5 |    54 |
+----+----------+-------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from info05 where name regexp 'l.';
+----+------+-------+-------+
| id | name | level | score |
+----+------+-------+-------+
|  2 | lisi |     9 |    56 |
|  4 | lili |     3 |    78 |
+----+------+-------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from info05 where name regexp 'a|c';
+----+----------+-------+-------+
| id | name     | level | score |
+----+----------+-------+-------+
|  1 | zhangsan |     3 |    89 |
|  3 | wangwu   |     5 |    54 |
+----+----------+-------+-------+
2 rows in set (0.00 sec)

9. 运算符

用于对记录中的字段值进行运算

1. 运算符分类
2. 算术运算符
3. 比较运算符
4. 逻辑运算符
5. 位运算符

9.1 MySQL支持的算术运算符

+  加法

-   减法

*   乘法

/   除法

%   取余数

案例

mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;
+----------+-------------+----------------+----------+-----------+
| addition | subtraction | multiplication | division | remainder |
+----------+-------------+----------------+----------+-----------+
|        3 |           1 |              6 |   2.0000 |         1 |
+----------+-------------+----------------+----------+-----------+
1 row in set (0.01 sec)

9.2 比较运算符

mysql> select greatest(1,2,3);
+-----------------+
| greatest(1,2,3) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

mysql> select greatest(1,2,3) as '最大值';
+-----------+
| 最大值    |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

mysql> select 2 in(1,2,3), 'c' in('b','d');
+-------------+-----------------+
| 2 in(1,2,3) | 'c' in('b','d') |
+-------------+-----------------+
|           1 |               0 |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select 'bdqn' like 'bdq_';
+--------------------+
| 'bdqn' like 'bdq_' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select 'bdqn' like 'bdq_','kgc' like '%c', 'etc' not like '%th';
+--------------------+-----------------+----------------------+
| 'bdqn' like 'bdq_' | 'kgc' like '%c' | 'etc' not like '%th' |
+--------------------+-----------------+----------------------+
|                  1 |               1 |                    1 |
+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)

9.2 逻辑运算符

• 又被称为布尔运算符
• 用来判断表达式的真假
• 常用的逻辑运算符
• NOT 或 ！：逻辑非
• AND 或 &&：逻辑与
• OR 或 || ：逻辑或
• XOR ： 逻辑异或

案例

mysql> SELECT 2 OR 3,4 || 0,0 OR NULL,1 || NULL;      
+--------+--------+-----------+-----------+
| 2 OR 3 | 4 || 0 | 0 OR NULL | 1 || NULL |
+--------+--------+-----------+-----------+
|      1 |      1 |      NULL |         1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)
mysql> SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;    
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
|       0 |       0 |       1 |       NULL |          NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)
mysql> select 2 and 3, 0 and 1, 0 and null, 1 and null;
+---------+---------+------------+------------+
| 2 and 3 | 0 and 1 | 0 and null | 1 and null |
+---------+---------+------------+------------+
|       1 |       0 |          0 |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)

mysql> 

9.3 位运算符

对二进制数进行计算的运算符

常用的位运算符

& 按位与

|  按位或

~ 按位取反

^ 按位异或

<< 按位左移

>> 按位右移

案例

mysql> SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 &~1;    
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
|      10 |      15 |       5 |     4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)
mysql> SELECT 1<<2, 2<<2,10>>2,15>>2;      
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
|    4 |    8 |     2 |     3 |
+------+------+-------+-------+
1 row in set (0.00 sec)

10.运算符的优先级

• 决定了不同的运算符在计算过程中的先后顺序
• 优先级高的先运算，同级的按从左到右进行计算
• 可以使用()小括号来改变计算优先级

运算符的优先级

11.内连接

• 两张或多张表中同时符合某种条件的数据记录组合
• FROM子句中使用INNER JOIN关键字连接多张表，并 使用ON设置连接条件
• 是系统默认的表连接方式，可以省略INNER关键字
• 多表支持连续使用INNER JOIN，建议不超过三个表

语法结构

select column_name(s) from table1 inner join table2 ON table1.column_name = table2.column_name;

实现原理

案例

mysql> select a_id,a_name,a_level from a_player a inner join b_player b on a_id=b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
+------+--------+---------+
2 rows in set (0.00 sec)

12. 左连接

• 也被称为左外连接
• 在FROM子句中使用LEFT JOIN关键字来表示
• 匹配左表中所有行及右表中符合条件的行

实现原理

案例

mysql> select * from a_player a left join b_player b on a.a_id=b.b_id;         
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

13. 右连接

• 也被称为右外连接
• 在FROM子句中使用RIGHT JOIN关键字来表示
• 匹配右表中所有行及左表中符合条件的行

实现原理

案例

mysql> select * from a_player a right join b_player b on a.a_id=b.b_id;      
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)