mysql 高阶语句与视图

一前言

二 msql 高阶语句使用方法

（一）查询并排序（order by）

1，排序方式

2，查询指定列并排序

3, 条件判断（过滤指定行）再查询指定列并排序

4，查询指定列并多次排序

（二）查询区间（用and or）

1，and 用法

2 ，or 用法

3，嵌套（or and 一起使用）

（三）查询去重（distinct）

（四）对结果进行分组（group by）

1, 聚合函数

2， count 例子

2.1分组统计个数

2.2 条件过滤分组统计个数再排序

（五）聚合函数的使用

1，count 查看表有多少个数据

2，sum 求和

3，min最小 max最大

4，avg 平均值

（六）limit 的使用

1，显示前三行数据

2，跳过前3行显示后2行

3，按武力值排序再显示前三行

（七）设置别名

1，生产环境使用意义

2，使用场景

3 对列名做别名

4，对表名和列名做别名

5，作为连接语句的操作符

6，把一个表的数据有条件的复制过来并创建新表

7，as 和聚合函数一起使用

8，提取指定行再排序

（八）通配符

（九）子查询（多表查询）

1，子查询概念

2，子查询语法

3 ，子查询示例

3.1 select 搭配子查询使用

3.2 子查询里有where条件

3.3 insert 搭配子查询使用

3.4 update 搭配子查询使用

3.5 delete 搭配子查询使用

3.6 子查询 exists

3.7 聚合函数搭配子查询使用

4 子查询与别名

三 mysql 视图

（一）视图基本概念介绍

1，视图定义

2，视图作用场景

3，视图作用范围

4，视图功能

5，视图与存储过程

6，视图和表的区别和联系

7，视图命令

8 ，示例视图

8.1 直接映射表

8.2 创建视图（有条件判断原表）

8.3 视图多表查询

8.4 修改表视图会变

8.5 修改视图表会变

四 NULL 值

（一）null 概念介绍

1，null 是啥

2，null 和空白和0

3，null 和空值长度对比

4，查询null值命令

五连接查询

（一）连接查询概念

1，内连接

1.1 内连接定义

1.2 内连接架构

1.3 内连接语法

2，左连接

2.1 左连接定义

2.2 左连接架构图

编辑

2.3 左连接语法

3，右连接

3.1 右连接定义

3.2 右连接架构图

3.3 右连接语法

（二）连接查询示例

1，演示环境

2，内连接显示表1 表2 相同数据

3，左连接显示左表所有加交集部分

4,右连接显示右表所有加交集部分

六存储过程

（一）存储过程概念

（二）存储过程的优点

（三）语法

1，创建存储过程

2，调用存储过程

3，查看存储过程

4，修改存储过程

5，删除存储过程

（四）存储过程的参数

（五）存储过程示例

1，创建存储过程

2，调用存储过程

3，查看存储过程

4, 行参与实参

七总结

一前言

本篇博客旨在各位看官在对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等

所以罗列mysql 高阶语句的常用使用情况

本次演示的库为sanguo 表为san 数据如下：

二 msql 高阶语句使用方法

（一）查询并排序（order by）

PS:类比于windows 任务管理器
使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，可以使用 ORDER BY 语句来对语句实现排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段

1，排序方式

ASC 是按照升序进行排序的，是默认的排序方式

DESC 是按降序方式进行排列

2，查询指定列并排序

要求：查询 wuli 名字并按照wuli排序

升序查看：

降序查看:

3, 条件判断（过滤指定行）再查询指定列并排序

要求:要魅力大于50的人武力排序

4，查询指定列并多次排序

ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定
但order by 之后的第一个参数只有在出现相同值时，第二个字段才有意义

要求：按照武力值排序如果武力值相同按智力再排序

可以发现关羽张飞武力一样再按智力排序

（二）查询区间（用and or）

1，and 用法

要求查询武力值大于60 小于等于100的并排序

2 ，or 用法

要求查询武力值大于98 或小于等于30的并排序

3，嵌套（or and 一起使用）

（三）查询去重（distinct）

要求:查询武力去重并排序

（四）对结果进行分组（group by）

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

1, 聚合函数

常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN）

2， count 例子

2.1分组统计个数

要求：查询武力把武力相同的放在同一组看相同武力的有几个

2.2 条件过滤分组统计个数再排序

要求：过滤出魅力60以上的再按武力值分组统计个数最后按照魅力大小排序

（五）聚合函数的使用

1，count 查看表有多少个数据

小技巧: 生产环境中可以select count(唯一键的列名) from 表名

达到同样效果因为唯一键（primary key 唯一且不为空）

2，sum 求和

要求：求和武力这一列

3，min最小 max最大

要求找到武力值最大和最小的

先找到武力最大值

再找到对应的行

min同理

4，avg 平均值

（六）limit 的使用

limit 限制输出的结果记录
在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录（行）。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句

1，显示前三行数据

2，跳过前3行显示后2行

3，按武力值排序再显示前三行

（七）设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性
注意！ alias 简写as as 可写可不写 as别名后查询不是改表结构只是查出来的结果变了

1，生产环境使用意义

多用于表做别名比如表1中有id 手机号码表2中也有id 手机号码等重复的列，在多表查询的时候，为了方便比较查看，可以将表名分别设置别名。

2，使用场景

对复杂的表进行查询的时候，别名可以缩短查询语句的长度
多表相连查询的时候（通俗易懂、减短sql语句）

3 对列名做别名

4，对表名和列名做别名

5，作为连接语句的操作符

就是不复制表结构（主键唯一键）复制数据

6，把一个表的数据有条件的复制过来并创建新表

只要武力值大于60的数据

查看新表

7，as 和聚合函数一起使用

8，提取指定行再排序

这里先提取前三行生成一个表把这个表当做别名a

再从别名a 中将数据排序

（八）通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。

通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符
_：下划线表示单个字符

找到名字是 z开头的

找到名字里有 z 的

找到 zha_gf__ 的 (有点像精确匹配)

（九）子查询（多表查询）

演示表格 1

演示表格2

1，子查询概念

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。
PS: 子语句可以与主语句所查询的表相同，也可以是不同表

子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

2，子查询语法

IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用
<表达式> [NOT] IN <子查询>
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。多数情况下，子查询都是与 SELECT 语句一起使用的

3 ，子查询示例

3.1 select 搭配子查询使用

3.2 子查询里有where条件

子查询里有where条件

解释：水浒传里政治大于59的的分别是id2 ,4,6,8

因此显示三国表里的 id2,4,6,8

3.3 insert 搭配子查询使用

解释： shui 表id 根据shui 表id 输入san 表内容

最后插到 shui 表

3.4 update 搭配子查询使用

解释：先看水浒传表政治小于60的id 为id2 和id10

改三国表里 id2 和id10 的wuli 为0

3.5 delete 搭配子查询使用

3.6 子查询 exists

EXISTS 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；反之，则返回 FALSE

解释; 水浒表里有两个人的政治都是小于60的所以返回结果为真

即执行前面的语句

解释; 水浒表里没有政治都是大于101的所以返回结果为假

即不执行前面的语句

3.7 聚合函数搭配子查询使用

解释：exists 返回结果真

4 子查询与别名

#此时会报错，原因为：
select * from 表名此为标准格式，而以上的查询语句，"表名"的位置其实是一个完整结果集，mysql并不能直接识别，而此时给与结果集设置一个别名，以”select a.id from a“的方式查询将此结果集视为一张"表"，就可以正常查询数据了

第二个例子：

这里先提取前三行生成一个表把这个表当做别名a

再从别名a 中将数据排序

三 mysql 视图

（一）视图基本概念介绍

1，视图定义

数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射
视图可以理解为镜花水月/倒影，动态保存结果集（数据）

2，视图作用场景

针对不同的人（权限身份），提供不同结果集的“表”（以表格的形式展示）

3，视图作用范围

elect * from info; #展示的部分是info表
select * from view_name; #展示的一张或多张表

4，视图功能

简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
本质而言视图是一种select(结果集的呈现)

5，视图与存储过程

视图适合于多表连接浏览时使用!不适合增、删、改
而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率

6，视图和表的区别和联系

#区别：
①、视图是已经编译好的sql语句。而表不是

②、视图没有实际的物理记录。而表有。
show table status\G

③、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改

④、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

⑤、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

⑥、视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

#联系：
视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

7，视图命令

create view 视图名 as select * from 表名 # 创建视图

select * from 视图名 # 查看视图

8 ，示例视图

8.1 直接映射表

创建视图

可以发现唯一建（表结构）不会复制

8.2 创建视图（有条件判断原表）

8.3 视图多表查询

注意此处s 是san表的别名 k 是shui 表的别名

解释：

创建视图3 有id name wuli zhili 这四个列

id 的值从三国表取

name值从三国表取

wuli值从三国表取

zhili值从水浒表取

条件判断是当水浒表和三国表有相同id

8.4 修改表视图会变

会！

8.5 修改视图表会变

四 NULL 值

（一）null 概念介绍

1，null 是啥

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白

2，null 和空白和0

需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

空值长度为0，不占空间，NULL值的长度为null，占用空间

is null无法判断空值

空值使用"=“或者”<>"来处理（！=）

count（）计算时，NULL会忽略，空值会加入计算

3，null 和空值长度对比

4，查询null值命令

select * from info where addr is NULL;

查询不为空的值 select * from info where addr is not null;

五连接查询

（一）连接查询概念

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

1，内连接

1.1 内连接定义

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表理解为找表的交集

1.2 内连接架构

1.3 内连接语法

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

2，左连接

2.1 左连接定义

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。理解为输出左边的表所有内容和两张表的交集

2.2 左连接架构图

2.3 左连接语法

mysql> select * from info left join infos on info.name=infos.name;

3，右连接

3.1 右连接定义

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配理解为输出右边的表所有内容和两张表的交集

3.2 右连接架构图

3.3 右连接语法

select * from info right join infos on info.name=infos.name;

（二）连接查询示例

1，演示环境

表1

表2

2，内连接显示表1 表2 相同数据

会显示表1 和表2 相同数据

解释：inner join 是内连接找到表一中name列和表2中name列数值一样的

或者这样：

意义不大哈哈因为表1 表2 数据相同为了看的舒服只看表一的数据就好了

3，左连接显示左表所有加交集部分

命令就是把 inner 改成left

4,右连接显示右表所有加交集部分

六存储过程

（一）存储过程概念

1，存储过程是一组为了完成特定功能的SQL语句集合。两个点第一触发器（定时任务）第二个判断

2、存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

3，存储过程在数据库中L 创建并保存，它不仅仅是 SQ语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、在不同的应用程序或平台上执行相同的函数等等。

（二）存储过程的优点

（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限

（三）语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

1，创建存储过程

DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以自定义)
CREATE PROCEDURE Proc() #创建存储过程，过程名为Proc，不带参数
-> BEGIN #过程体以关键字 BEGIN 开始
-> create table mk (id int (10), name char(10),score int (10));
-> insert into mk values (1, 'wang',13);
-> select * from mk; #过程体语句
-> END $$ #过程体以关键字 END 结束
DELIMITER ; #将语句的结束符号恢复为分号（分号前有空格
）

2，调用存储过程

CALL Proc();

3，查看存储过程

SHOW CREATE PROCEDURE [数据库.]存储过程名; #查看某个存储过程的具体信息

SHOW PROCEDURE STATUS #查看存储过程

SHOW PROCEDURE STATUS like '%proc%'\G #查看指定存储过程信息

4，修改存储过程

ALTER PROCEDURE <过程名>[<特征>... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES sQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。

5，删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

DROP PROCEDURE IF EXISTS Proc;

（四）存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)

（五）存储过程示例

1，创建存储过程

注意：存储过程名字以及创建的表名都不要重复

2，调用存储过程

3，查看存储过程

方法1

方法2

4, 行参与实参

解释：开启一个存储过程存储过程（in inname varchar(30)）带一个输入参数 inname理解为一个变量名字是随便取的

赋值在下面的 name=inname

在调用存储过程时加入指定值

理解为变量调用存储过程时是位置参数

七总结

1，select、order by 和limit 一个结合

① mysql 数据库如何查一张表; (select 语法)

② 查询 ky35表 ID 和name (指定字段的查询)

③查询ky35表中成绩字段大于/小于80 的怎么查 (select配合where条件过滤的查询)

④查询 ky35表前三行记录/后三行记录怎么查 (select 结合limit 怎么查询)

⑤ ky35表中第三行记录的后两行记录怎么查 (select 结合limit 输出指定的行以下的多行内容3,2)

2，通配符

%匹配一个或多个数值

— 任意单个字符

3，内连接左连接右连接

inner join 内

left join 左

right join 右

4，存储过程

存储过程是预先编写好的，可重复使用的数据库查询，它可以封装复杂业务逻辑，提高数据处理效率和安全性