MySQL高级SQL语句

一、MySQL 进阶查询

在对 MySQL 数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法。SQL 语句的熟练使用，在平时的运维工作中可以提供不小的帮助，尤其是在一些规模较小的公司，运维身兼数职，可能会有不少数据库的相关工作。本章将从不 同的方面出发介绍 SQL 语句的高级运用方法。

二、常用查询介绍

对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等，这些内容就是接下来要讲解的知识。

1.按关键字排序

mysql> select * from test order by level desc;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  4 | zhaoliu  |    63 |
|  7 | tom      |    63 |
|  3 | wangwu   |    55 |
|  6 | heiba    |    52 |
|  1 | zhangsan |    46 |
|  5 | tianqi   |    43 |
|  8 | jerry    |    43 |
|  2 | lisi     |    35 |
+----+----------+-------+
8 rows in set (0.00 sec)

按单字段排序

mysql> select * from test where level >= 45 order by level desc;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  4 | zhaoliu  |    63 |
|  7 | tom      |    63 |
|  3 | wangwu   |    55 |
|  6 | heiba    |    52 |
|  1 | zhangsan |    46 |
+----+----------+-------+
5 rows in set (0.00 sec)

按多字段排序

mysql> select * from test where level>=45 order by level desc,id desc;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  7 | tom      |    63 |
|  4 | zhaoliu  |    63 |
|  3 | wangwu   |    55 |
|  6 | heiba    |    52 |
|  1 | zhangsan |    46 |
+----+----------+-------+
5 rows in set (0.00 sec)

2.对结果进行分组

mysql> select count(name) 数量,level from test where level>=40 group by level;
+--------+-------+
| 数量   | level |
+--------+-------+
|      2 |    43 |
|      1 |    46 |
|      1 |    52 |
|      1 |    55 |
|      2 |    63 |
+--------+-------+
5 rows in set (0.00 sec)

3.限制结果条目

[1,3] 从0开始,1开始那行的三行

mysql> select column1,column2,... from table_name limit [offset,] number;

LIMIT限制结果条数

mysql> select * from test limit 3;
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | zhangsan |    46 |
|  2 | lisi     |    35 |
|  3 | wangwu   |    55 |
+----+----------+-------+
3 rows in set (0.00 sec)

不从第一条开始取值

mysql> select * from test limit 2,3;	//从第三条记录开始显示之后的三条数据
+----+---------+-------+
| id | name    | level |
+----+---------+-------+
|  3 | wangwu  |    55 |
|  4 | zhaoliu |    63 |
|  5 | tianqi  |    43 |
+----+---------+-------+
3 rows in set (0.00 sec)

4.设置别名

mysql> select column_name AS alias_name from table_name;	//列的别名
mysql> select column_name(s) from table_name AS alias_name;	//表的别名

AS的用法

mysql> select name AS 姓名 from test;
+----------+
| 姓名     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| zhaoliu  |
| tianqi   |
| heiba    |
| tom      |
| jerry    |
+----------+
8 rows in set (0.00 sec)

AS作为连接语句

mysql> create table tmp as select * from test;

5.通配符

通配符%的用法

mysql> select * from test where name like 'zh%';
+----+----------+-------+
| id | name     | level |
+----+----------+-------+
|  1 | zhangsan |    46 |
|  4 | zhaoliu  |    63 |
+----+----------+-------+
2 rows in set (0.01 sec)

通配符_的用法

mysql> select * from test where name like 'to_';
+----+------+-------+
| id | name | level |
+----+------+-------+
|  7 | tom  |    63 |
+----+------+-------+
1 row in set (0.00 sec)

6.子查询

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

mysql> select name,level from test where id in (select id from test where levvel>=45);
+----------+-------+
| name     | level |
+----------+-------+
| zhangsan |    46 |
| wangwu   |    55 |
| zhaoliu  |    63 |
| heiba    |    52 |
| tom      |    63 |
+----------+-------+
5 rows in set (0.01 sec)

mysql> insert into tmp set select * from player where id in (select id from player);

mysql> update tmp set level=level-7 where id in (select id from test);

mysql> delete from tmp where id in (select a.id from (select id from tmp where level=47) a);

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 (select id from tmp where name='shirley');

7.NULL值

表示缺失的值
与数字0或者空白(spaces)是不同的
使用ISNULL或IS NOT NULL进行判断
NULL值和空值的区别
空值长度为0,不占空间;NULL值的长度为NULL,占用空间
IS NULL无法判断空值
空值使用’=‘或者’<>'来处理
count()计算时,NULL会忽略,空值会加入计算
NULL的用法

//查询coin字段为NULL值的记录
mysql> select * from test where coin is null;	

//查询coin字段不为空的记录
mysql> select * from test where coin is not null;

8.正则表达式

根据指定的匹配模式匹配记录中符合要求的特殊字符
使用REGEXP关键字指定模式
常用的匹配模式
^匹配开始字符
$匹配结束字符
. 匹配任意单个字符
*匹配任意个前面的字符
+匹配前面字符至少一次
p1|p2匹配p1或p2
[…]匹配字符集中的任意一个字符
[^…]匹配不在中括号内的任何字符
{n}匹配前面的字符串n次
{n,m}匹配前面的字符串至少n次,至多m次

9.运算符

用于对记录中的字段值进行运算
运算符分类
算术运算符
比较运算符
逻辑运算符
位运算符
算术运算符
+加法
-减法
*乘法
/除法
%取余数

mysql> select 1+2,2-1,2*3,4/2,7%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 2*3 | 4/2    | 7%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

比较运算符

字符串的比较默认不区分大小写,可使用binary来区分
常用比较运算符
=等于>大于<小于>=大于等于<=小于等于!=或<>不等于
IN在集合中
LIKE通配符匹配
IS NULL判断一个值是否为NULL
IS NOT NULL判断一个值是否不为NULL
BETWEEN AND两者之间
GREATEST两个或多个参数时返回最大值
LEAST两个或多个参数时返回最小值
逻辑运算符
且 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或! : 逻辑非
AND或&& : 逻辑与
OR或|| : 逻辑或
XOR : 逻辑异或
逻辑运算符的用法

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

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

10.连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。

//查看student1表的信息
mysql> select * from student1;
+----+----------+--------+--------+
| id | name     | socre  | course |
+----+----------+--------+--------+
|  1 | lisi     | 104.00 |      1 |
|  2 | zhangsan |  93.00 |      1 |
|  3 | tianqi   |  77.00 |      2 |
|  4 | qianba   |  78.00 |      3 |
|  5 | tianbi   |  66.00 |      3 |
+----+----------+--------+--------+
5 rows in set (0.00 sec)

//查看student表的信息
mysql> select * from student;
+----+----------+--------+--------+
| id | name     | socre  | course |
+----+----------+--------+--------+
|  1 | lisi     | 104.00 |      1 |
|  2 | zhangsan |  93.00 |      1 |
|  3 | tianqi   |  77.00 |      2 |
|  4 | long     | 120.00 |      3 |
|  5 | zhuge    |  20.00 |      2 |
+----+----------+--------+--------+
5 rows in set (0.00 sec)

//查询course表的信息
mysql> select * from course;
+----+--------+---------+
| id | name   | teacher |
+----+--------+---------+
|  1 | 语文   | yangqi  |
|  2 | 数学   | yangba  |
|  3 | 英语   | yangjiu |
+----+--------+---------+
3 rows in set (0.00 sec)

内连接
MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件。内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。
外连接之左连接
MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分， 有左连接和右连接之分。左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。
外连接之右连接
右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。

三、MySQL存储过程

1.存储过程简介

是一组为了完成特定功能的SQL语句集合
比传统SQL速度更快、执行效率更高
存储过程的优点
执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率
SQL语句加上控制语句的集合,灵活性高
在服务器端存储,客户端调用时,降低网络负载
可多次重复被调用,可随时修改,不影响客户端调用
可完成所有的数据库操作,也可控制数据库的信息访问权限

2.存储过程用法示例

//定义存储过程
mysql> delimiter $$
mysql> create procedure 存储过程名 (in 参数名 参数类型)
mysql> begin
//定义变量
mysql> declare 变量名 变量类型
//变量赋值
mysql> set 变量名 = 值
mysql> SQL语句1;
mysql> SQL语句2;
mysql> ...;
mysql> SQL语句n;
mysql> end$$
mysql> delimiter ;		//分号前有一个空格

//调用存储过程
mysql> call 存储过程名(实际参数);

//查询存储过程
mysql> show procedure status where db='数据库';

//删除存储过程
mysql> drop procedure 存储过程名;