深入探索MySQL高阶查询语句的艺术与实践

比较运算符	功能
> 或!=	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN ... AND ...	在某个范围之内(含最小、最大值)
IN(..)	在in之后的列表中的值
LIKE 占位符	模糊匹配( _ ：匹配单个字符,%：匹配任意个字符)
IS NULL	是NULL

1.使用匹配符号查询

首先看一下表中的原始数据

分别找出age值等于30、age值大于40、age值小于40的数据

分别找出age值大于等于45、age值小于等于45、age值不等于45的数据

2.范围查找

使用BETWEEN ... AND ...指定查询的值的范围，比如找出age值为30到50之间的数据，包含30与50

使用in进行多个值的匹配，比如想查看age值为30，和值为45的数据

使用like进行模糊匹配

%：表示后面有任意单个、多个、或者空字符

_：表示后面有任意一个字符

找到字段值为空的数据

（二）逻辑运算符

逻辑运算符	功能
AND 或 &&	并且(多个条件同时成立)
OR 或 \|\|	或者(多个条件任意一个成立)
NOT 或 !	非，不是

1.AND，组合条件查询

使用：select 字段 from 表名 where 条件1 and 条件2;

2.多条件查询

使用：select 字段 from 表名 where 条件1 or 条件2;

3.反选查询

使用：select 字段 from 表名 where ！条件 ;

二、关键字排序

可以对字段值进行排序，可以是数字，也可以是字母

基本语法为：select 字段1, 字段2, ... from 表名 [where 条件]order by 排序字段1,排序字段2, ... [asc|desc]

关键字	排序方式
asc	升序排序，默认排序方式asc 可以省略
desc	降序排序，

注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

1.按数字排序

2.按字母排序

按字段值的首字母进行排序

3.数据压缩

数据压缩，查看不重复的记录

使用distinct指令过滤重复的信息

三、分组与聚合函数

当我们需要基于某一列或多列数据进行统计时，分组查询和聚合函数就派上了用场，分组的关键字为group by，而group by通常与聚合函数一同使用。常见的聚合函数有以下几种

函数值	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

基本语法为：

select 字段名, 聚合函数(字段名) from 表名 [where 条件] GROUP BY 字段名;

全表数据为

首先了解一下聚合函数的用法

select 聚合函数(字段) from 表名

count函数是统计值的总数，只统计表中有值的数量，null不纳入统计范围

使用分组查询数据

第一个示例表示：查询将返回每个（grender）员工的性别。每个性别构成了一个组，函数统计每个组内的员工性别数量。

第二个示例表示：查询将返回每个员工的性别（grender），以及工作地址（workid）。每个工作地址构成了一个组，函数统计每个组内的员工性别数量。

四、限制查询

在MySQL中，LIMIT 关键字用于限制SELECT语句返回的记录数量。

基本用法有两种

select 字段... from 表名 limit 行数；
#查询从第一行开始到指定行数结束的数据

select 字段... from 表名 limit 行数1,行个数2；
#查询从行数1的下一行开始查询，到指定行的个数结束的数据
#例如：select 字段... from 表名 limit 3,5；
#从第3行开始,向下查询5行，也就是4、5、6、7、8行的数据

limit一般与order by组合使用，以便在限制记录数量的同时保证排序的准确性

比如查询age(年龄)最大的5位员工

五、别名

在MySQL中，查询别名（Alias）是为表名或列赋予临时名称的一种方式，可以使SQL查询更加简洁易读，尤其是在处理复杂的查询或联接多个表的时候。别名通常在查询中通过AS关键字定义，但AS关键字在大多数情况下是可以省略的。

select 字段名 [as] 别名 from 表名;
#对列设置别名

select 表别名.字段名 from 表名 [as] 表别名;
#对表设置别名

（一）设置列别名

通过设置列的别名使数据看起来更有可读性

（二）设置表别名

将表设置别名，用于区分不同表之间的同字段，主要在进行多表联查的时候使用

（三）as连接语句

as可以作为连接语句，将表的数据插入到新的表当中

此方法与之前介绍的克隆表基本一致，对于表的约束无法进行保存

六、子查询

子查询（Subquery）是在SQL查询中嵌套的查询语句，它先执行内层查询，然后将结果作为外层查询的一部分进行处理。子查询通常放在比较运算符的右侧，或者用在IN、ANY、ALL、EXISTS等关键字后面。

子语句可以与主语句所查询的表相同，也可以是不同表

基本语法为：主语句（子语句）

（一）使用IN关键字的子查询

在查询表的数据时通过将子语句查询到的数据，当作主语的参数去匹配查询的表

使用子查询的方式，查询emp表中id,name字段中，所以有grender值为'男'的数据

这个SQL查询将会从emp表中选择id和name列，条件是id存在于另一个子查询中。子查询是选取emp表中性别为'男'的所有员工的id。

select id,name from emp where id in (select id from emp_user where grender='男');
#主语句：select id,name from emp where id
    #select id, name: 从emp表中选择id和name这两列数据。
    #from emp: 指定查询的表为emp，这是一个员工表。
    #where id：指定条件为id

#in：将语句做关联
#not in：将语句做关联，并返回相反的值

#子语句：(select id from emp_user where grender = '男');
    #查询所有grender值为男的数据，显示出它们的id值
#主查询部分where id in (...)则表示只选择那些id在子查询结果集中的员工。

使用子查询时，同时可以做修改、删除

1.修改数据

比如将id字段值为5的数据的age值修改为30

2.删除数据

删除数据与修改数据方法一致

注意：无论在修改还是在删除数据之前，首先需要进行确认，如果删除的数据量较多，确认之后，最好先进行备份，而后再删除数据

（二）使用比较运算符的子查询

使用比较运算符，找出id值大于4的数据，并显示它们的id，name字段信息

这条语句会先执行子语句，查询到id=4的数据，并只显示id值，即为4。

而后执行主语句，将id=4的值作比较，从而where条件则为id >4，最后显示出id字段值大于4的数据

同样，也可以进行函数统计

（三）使用EXISTS的子查询

在MySQL中，EXISTS子查询用于测试是否存在满足特定条件的行。子查询不是返回具体的数据行，而是返回一个布尔值（TRUE或FALSE），根据这个布尔值，主查询决定是否包含行。

七、视图

视图是基于一个或多个表的SQL查询结果的虚拟表。视图并不实际存储数据，而是保存了查询的定义。当你查询视图时，数据库引擎会按照视图的定义执行底层的SQL查询。

（一）视图作用

视图的主要作用时一张表或多张表的数据重新进行逻辑划分，呈现出不同的数据种类及范围，提供给不同权限，需要不同数据的客户或有关人员进行访问

（二）视图的特点

视图主要含义就是将select查询后的结果集保存成为一个逻辑表，相当于结果集的数据映射，它不会占用磁盘空间，修改基础表的数据时，视图的数据同样也会修改

视图的另一个特点查询速度快，能够迅速定位到想要查询的数据，不需要重复进行繁琐的条件筛查或者多表查询

（三）创建视图

1.单表创建视图

基本语法为：create view 视图名称 as select 字段1,字段2,... 表名 [where 条件];

比如对一张表进行不同的逻辑划分

新建的视图是辑表，它并不会占用磁盘空间，它的表信息值都是null值

在创建视图后，视图会保存表值的相关字段，但约束并不会保存，比如主键、唯一键

修改基础表的数据，视图的数据也会随之更改，删除基础表，则视图的值也会随之变为空

而删除视图，并不会影响基础表本身，但是修改视图数据，基础表的数据也会修改

2.多表创建视图

多表创建视图语法为：

create view 视图名称 as select 字段1,字段2,... 表名1,表名2.... [where 条件];

或者

create view 视图名称(字段1,字段2,... ) as select 字段1,字段2,... 表名1,表名2.... [where 条件];

查看视图

虽然在视图中，表的结构中，不会显示约束信息，但是无法修改存在于主表中的约束信息，比如主键值，修改其它无约束的视图数据，可以正常修改，并影响基本表的数据，但是主键值无法修改

这样极大提高了数据的安全性，只允许其它用户进行数据的查看

八、连接查询

MySQL中的连接查询（JOIN）是将两个或更多表中的行根据它们之间的关系合并成单个结果集的过程。连接查询根据给定的条件将表间的关系建立起来，以便从多个表中获取相关数据。

比如通过指定name字段，将两张表的数据进行连接，可以从两张表中同时获取相关数据

连接查询主要分为内连接、左连接、右连接

首先创建两张表，并添加响应数据

（一）内连接

内连接仅返回两个表中具有匹配值的行

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN

基本语法为：select 字段... from 表1 [inner] join 表2 on 表1.字段= 表2.字段;

select a.id,a.name,a.grender,b.age,b.workip 
#查询显示的字段
#a.id：表示显示后面的语句中表名或别名为a的id字段的值，
#b.age：表示显示后面的语句中表名或别名为b的age字段的值

from user1 as a join user2 as b 
#user1表设置别名为a；user2表设置别名为b，从两张表中获取数据


on a.name=b.name;
#判断条件，只显示两张表中name字段相同的数据

（二）左连接

左连接（使用：LEFT JOIN / LEFT OUTER JOIN进行连接） 左连接返回左表（第一个表）的所有行，以及右表（第二个表）与之匹配的行。若右表无匹配行，则结果中右表的列显示为NULL。

（二）右连接

右连接（使用：RIGHT JOIN / RIGHT OUTER JOIN进行连接） 右连接与左连接相反，返回右表的所有行，以及左表与之匹配的行。若左表无匹配行，则结果中左表的列显示为NULL。

九、存储过程

MySQL存储过程（Stored Procedure）是一种预编译的SQL语句集合，它允许用户定义一组完成特定任务的SQL命令，存储在数据库服务器中。存储过程可以包含条件语句、循环语句、变量声明、函数调用以及其他复杂的逻辑，提高了代码的复用性和数据库操作的效率。

（一）主要作用

1.将经常操作的SQL语句集中在一起，在使用时直接调用，避免了重复多次操作，大大提高工作效率。相当于shell脚本

2.它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性

（二）存储过程的优点包括

代码复用：定义一次，多次调用，避免了重复编写相同SQL语句的麻烦。
性能提升：由于存储过程在服务器端预编译，执行效率更高，减少了网络传输数据量。
模块化编程：将复杂的数据库操作封装在存储过程中，有利于代码维护和管理。
安全控制：通过授予对存储过程的权限，可以更精确地控制用户对数据库的操作权限。
事务处理：存储过程内部可以包含事务控制，确保数据的一致性和完整性。

（三）无参数创建存储过程

创建存储过程的基本语句为:

delimiter 自定义结束符

create procedure 存储过程名称()

SQL语句
......
......

end 结束符

delimiter ;

delimiter @@
'修改MySQL客户端的语句终止符为@@，这样做的目的是为了避免在创建存储过程时与原有的分
号（;）冲突，因为在SQL语句中，分号通常用来表示语句的结束。创建完存储过程后再将其恢复为分号。'

create procedure store()
'定义一个名为store的存储过程。存储过程名称为store，不带任何输入或输出参数。'

begin 
'标记存储过程主体的开始。'

create table if not exists bg(id int(4),name varchar(15),age int(3));
'SQL语句，建表
不过存储过程的主要目的是封装SQL逻辑，而非创建数据库对'

insert into bg values (1,'sunwukong',28);
'SQL语句，在bg表中插入数据'

select * from bg;
'SQL语句：查看bg表中的所有数据'

end @@
'标记存储过程主体的结束。'

delimiter ;
'将MySQL客户端的语句终止符还原为分号（;）,注意：分号(；)与delimiter中间右空格'