MySQL 高阶语句（二）

最新推荐文章于 2024-06-11 18:40:56 发布

樂夢い

最新推荐文章于 2024-06-11 18:40:56 发布

阅读量1.1k

点赞数 14

文章标签： mysql 数据库运维服务器网络日志 linux

本文链接：https://blog.csdn.net/qq_35995197/article/details/137076334

版权

一、子查询

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

多表查询

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

1. 用法

语法：

<表达式> [NOT] IN <子查询>

1.1 查询分数大于等于80的记录

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

1.2 将test2里的记录全部删除，重新插入test表的记录

子查询还可以用在INSERT语句中。子查询的结果集可以通过INSERT语句插入到其他的表中。

1.3 将xiaowang的分数改为80

UPDATE语句也可以使用子查询。UPDATE内的子查询，在set更新内容时，可以是单独的一列，也可以是多列。

1.4 删除分数大于80的记录

DELETE 也适用于子查询.

1.5 NOT IN（结果取反）

在 IN 前面还可以添加 NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）

删除分数不是大于等于80的记录

1.6 EXISTS关键字

主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；反之，则返回 FALSE

查询如果存在分数等于80的记录则计算test1中的字段数

1.7 as别名

从test1表中的id和name字段的内容做为"内容" 输出id的部分

二、MySQL视图

1. 视图的介绍

MySQL视图是一种强大的数据库对象，可以简化查询操作，提供数据安全性和数据抽象等功能，从而提高数据库的使用和管理效率。

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

作用场景

视图在数据库中有很多应用场景，例如：

简化复杂查询：将复杂的查询逻辑封装为视图，提供一个简单的接口供用户查询。
提供数据安全性：通过视图，可以控制用户对表的访问权限，只允许用户访问视图而不是直接访问表，提高数据安全性。
数据抽象和数据隐藏：通过视图，可以隐藏数据的真实表结构，只提供部分数据给用户，降低数据泄露的风险。
数据聚合和汇总：通过视图，可以对数据进行聚合和汇总，生成报表和统计数据。

语法

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

2. 视图和表的区别和联系

区别：

视图是已经编译好的sql语句。而表不是
视图没有实际的物理记录。而表有。
表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

联系：

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

3. 创建视图(单表)

将分数80分的学生展示在视图中

查看视图与源表结构

4. 多表创建视图

创建test4表

创建视图

操作：

修改原表数据

同时可以通过视图修改原表

三、NULL 值

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

null值与空值的区别：

null值可以用于任何数据类型，包括数值、日期、字符串等，而空值仅适用于字符串类型。
null值表示缺失或未知值，而空值表示一个空字符串。
null值不等于任何其他值，包括null本身，而空值可以与空值相等。
对于null值，可以使用IS NULL或IS NOT NULL来进行检查。对于空值，可以使用空字符串进行检查。
null值分配的存储空间更多，因为它需要额外的信息来表示该值的缺失或未知状态，而空值只需要存储一个空字符串。

四、连接查询

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

1. 内连接

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

语法：

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

用法：

2. 左连接

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

用法：

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为 NULL。

3. 右连接

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

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以 NULL 补足。

五、存储过程

存储过程是一段预先编译好的SQL代码，可以被多次调用和执行。存储过程通常用于完成一系列的数据库操作，并将其作为一个单独的单元进行维护和管理。

存储过程可以接受输入参数和返回输出结果，可以在数据库中定义和存储，并在需要时进行调用。存储过程可以包含条件判断、循环、异常处理等逻辑控制结构，可以方便地实现复杂的业务逻辑。

存储过程可以提高数据库的性能和安全性。通过将常用的业务逻辑封装在存储过程中，可以减少网络传输的开销，提高数据库的响应速度。此外，存储过程也可以限制对数据库的直接访问，增加数据的安全性。

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

存储过程的优点

提高性能：存储过程是预编译的，可以在数据库中进行优化和缓存，执行速度通常比动态SQL语句更快。存储过程可以减少网络传输的开销，提高数据库的响应速度。
提高安全性：存储过程可以限制对数据库的直接访问。通过授予用户对存储过程的执行权限，可以控制用户对数据库的访问和操作，增加数据的安全性。
简化开发和维护：通过将复杂的业务逻辑封装在存储过程中，可以减少应用程序的代码量，简化应用程序的开发和维护过程。存储过程可以集中管理和维护，提高代码的复用性和可维护性。
支持事务处理：存储过程可以包含事务控制语句，可以在一个单独的事务中执行多个数据库操作，保证数据的一致性和完整性。
提高代码复用性：存储过程可以被多个应用程序调用，提高代码的复用性。通过调用存储过程，可以避免代码的重复编写，提高开发效率。

语法：

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

1. 示例操作

1.1 创建存储过程

mysql> delimiter $$            #将语句的结束符号从分号;临时改为两个$$(可以自定义)
mysql> create procedure cp()    #创建存储过程，过程名为Proc，不带参数
    -> begin                    #过程体以关键字 BEGIN 开始
    -> create table test01(id int(5),name varchar(10),score int(5));                                                       
    -> insert into test01 values(1,'xiaoming',60);                                                                         
    -> insert into test01 values(2,'xiaochao',90);                                                                         
    -> select * from test01;        #过程体语句                                                                                               
    -> end $$                       #过程体以关键字 END 结束                                                                                                    
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;            #将语句的结束符号恢复为分号

1.2 调用存储过程

mysql> call cp();

1.3 查看存储过程

格式：

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

1.4 查看指定存储过程信息

#查看存储过程
SHOW PROCEDURE STATUS 

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

1.5 修改存储过程

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

1.6 删除存储过程

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

DROP PROCEDURE IF EXISTS cp;

2. 存储过程的参数

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

mysql> delimiter $$
mysql> create procedure sc (in inname varchar(20))    #行参
    -> begin
    -> select * from test1 where name=inname;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call sc('xiaohong');    #实参
+------+----------+-------+---------+--------+
| id   | name     | score | address | hobbid |
+------+----------+-------+---------+--------+
|    1 | xiaohong | 80.00 | beijing |      2 |
+------+----------+-------+---------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

樂夢い

关注

14
点赞
踩
20

收藏

觉得还不错? 一键收藏
1
评论
MySQL 高阶语句（二）

MySQL视图是一种强大的数据库对象，可以简化查询操作，提供数据安全性和数据抽象等功能，从而提高数据库的使用和管理效率。数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射视图可以理解为镜花水月/倒影，动态保存结果集（数据）基础表test ===> 映射（投影）=== 视图。
复制链接

扫一扫