MySQL高级语句（二）

原创已于 2022-06-16 19:16:28 修改 · 177 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#运维 #sql #mysql #数据库 #linux

于 2022-06-14 20:55:28 首次发布

六、空值(NULL) 和无值(’ ') 的区别

一、 CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

（3）视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

#语法：
CREATE VIEW "视图表名" AS "SELECT 语句";

简单来说，就是把select查询的语句保存起来。但保存的结果并不是固定的，select语句的结果如果发生了变化，保存的结果也会随之改变。

面试题：视图表中能否插入数据？

答：依情况而定。若视图表与原表结构发生了改变，就不能被插入或修改。他也可以插入数据，就看视图表的定义结构与原表结构是否一样。

视图表保存着select语句执行的结果，视图表相当于select语句的别名。。

方便后面对select语句再次操作。

派生表：把查询结果当成一个表去看待。（子查询是查询的一个字段的结果）

将该select语句作为派生表：

此时，C就是select语句的派生表。

语句太长，拆分为两端：

之前创建了V_REGION_SALES的视图表。

直接对视图表进行操作即可。

视图表保存着select语句执行的结果，视图表相当于select语句的别名。

可以让select语句后续操作简化。

面试题：视图表中能否插入数据？

答：依情况而定。若视图表与原表结构发生了改变，就不能被插入或修改。他也可以插入数据，就看视图表的定义结构与原表结构是否一样。

例：

我们去修改该视图表：

查看实际表，也被修改了。

表结构改变后，就不可以修改内容了。

二、联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类
2.1UNION
生成结果的资料值将没有重复，且按照字段的顺序进行排序

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];

2.2UNION ALL

将生成结果的资料值都列出来，无论有无重复


UNIONALL:将生成结果的数据记录值都列出来，无论有无重复
语法: [SELECT 语句1] UNION ALL [SELECT 语句2] ;

举例：
------------------
select store_name from location union select store_name from store_info;
select store_name from location union all select store_name from store_info;

三、交集值

取两个SQL语句结果的交集

---- 交集值----取两个sQL语句结果的交集
SELECT A.Store_ Name FROM location A INNER JOIN Store_ Info B ON A.Store_ Name = B.Store_ Name ; 
SELECT A.Store_ Name FROM location A INNER JOIN Store_ Info B USING(Store_ Name) ;

#取两个sQL语句结果的交集，且没有重复
SELECT DISTINCT A.Store_ Name FROM location A INNER JOIN Store_ Info B USING (Store_ Name) ;
SELECT DISTINCT Store_ Name FROM location WHERE (Store_ Name) IN (SELECT Store_ Name FROM Store_ Info);
SELECT DISTINCT A.Store_ Name FROM location A LEFT JOIN Store_ Info B USING(Store_ Name) WHERE B.Store_ Name IS NOT NULL;
SELECT A.Store_ Name FROM (SELECT B.Store_ Name FROM location B INNER JOIN Store_ Info C ON B.Store_ Name = C.Store_ Name) A
GROUP BY A. Store_ Name;

两个store_name求交集值：

法一：

法二：

法三：（using）（要保证两个表字段是同名）

此时，给表store_info插入一个字段：

此时用using发现多了一条记录：

用on也多了一条记录：

用where也多了一条记录：

可以通过distinct去重：

除了通过内连接以外，求交集还有其他方法：

1.用左连接的方法求交集

先查询，发现左表中的字段都列了出来，但左表中有一行记录是没有交集的，此时右表的值为null

用where将右表的null值过滤掉：

2.用右连接的方法求交集：

3.in方法

把store_info这张表中的store_name这个列的值查询出来，作为一个取值列表给location这个表去查询，作为他查询的条件，

4. 通过子查询的方法去个重：

5.通过group by：

再对其进行分组，在用count 统计，若有重复，则是有交集的，没有重复，则为1，

group by A.Store_Name Having count(*)>1;

这边比较难理解，我们可以先创建一个视图表：

再对这个视图表进行分组，

再通过count进行过滤，把出现2次的过滤出来：

再把count删去，达到求交集的目的：

四、无交集值

法一：通过count进行过滤，把出现1次的过滤出来：

法二：通过not in只能过滤单边的，再进行合并，即可过滤出双边的无交集值：

法三：通过单边过滤null，再合并

not in和null 只能求出单边无交集值，要想一步到位，只能用法一，求出现次数。

五、CASE

CASE是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字

#语法：
SELECT CASE ("栏位名")
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
...
[ELSE "结果N"]
END
FROM "表名";

# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

原表不改变。

六、空值(NULL) 和无值(’ ') 的区别

（1）无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。

（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

（3）无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。

（4）在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

#示例2：查看空值和非空值
select * from info where name = '';
select * from info where name <> '';

查看空值，发现有两个空值。

#示例3：统计行数
select COUNT(*) from info;
select COUNT(name) from info;

七、正则表达式

---- 正则表达式----
匹配模式描述
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任何单个字符
* 匹配零个或多个在它前面的字符
+ 匹配前面的字符1次或多次
字符串匹配包含指定的字符串
p1|p2 匹配p1或p2
[...] 匹配字符集合中的任意一个字符
[^...] 匹配不在括号中的任何字符
{n} 匹配前面的字符串n次
{n,m} 匹配前面的字符串至少n次，至多m次

语法：SELECT "字段" FROM "表名”WHERE "字段" REGEXP {模式};
SELECT * FROM Store_ Info WHERE Store_ Name REGEXP 'os' ;
SELECT * FROM Store_ Info WHERE Store_ Name REGEXP I^[A-G] ' ;
SELECT * FROM Store_ Info WHERE Store_ Name REGEXP 'Ho | Bo' ;

#语法:
SELECT "栏位”FROM "表名" WHERE "栏位”REGEXP {模式};

#示例1：查看数据表中的name字段
select name from students;

#示例2：返回表中name中字符带'ua'的数据的全部字段
select * from students where name regexp 'ua';

#示例3：返回表中字段中以'A-G'开头的数据，不区分大小写
select * from students where name regexp '^[A-G]';

#示例4：返回表中字段中以'du'和'hu'开头的全部信息
select * from students where name regexp '^(du|hu)';

八、存储过程

存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。
当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

8.1 存储过程的优点

封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

提高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

8.2 创建存储过程

DELIMITER $$                      #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()           #创建存储过程，过程名为Proc，不带参数
> BEGIN                           #过程体以关键字BEGIN开始
-> select * from Store_ Info;     #过程体语句
-> END $$                         #过程体以关键字END结束
DELIMITER ;                       #将语句的结束符号恢复为分号;

语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格

示例：

delimiter $$					#将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
create procedure proc5()        #创建存储过程，过程名为proc， 不带参数
-> begin                        #过程体以关键字BEGIN开始
-> select * from students;      #过程体语句（自己根据需求进行编写）
-> end $$						#过程体以关键字END结束
delimiter ;                     #将语句的结束符号恢复为分号

8.3 调用存储过程

call proc;

8.4 查看存储过程

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

SHOW CREATE PROCEDURE Proc;
SHOW PROCEDURE STATUS [LIKE ' 8Proc8' ] \G

8.5 存储过程的参数

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

#示例：
delimiter $$                                                                        
create procedure proc11(in inname char(40))
-> begin
-> select * from students where name = inname;
-> end $$                                                                              
mysql> delimiter ;
call proc11('Xi Ren');

8.6 删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc;
#仅当存在时删除，不添加IF EXISTS 时，如果指定的过程不存在，则产生一个错误

8.7 存储过程的控制语句

条件语句：if-then-else .... end if

DELIMITER $$
CREATE PROCEDURE proc2 (IN pro int)
-> begin
-> declare var int ;
-> set var=pro*2;
if var>=10 then
-> update t set id=id+1;
-> else
-> update t set id=id-1;
-> end if;
-> end $$;

DELIMITER ;

CALL Proc2 (6) ;

while......end while

DELIMITER $$                                            #修改默认结束符为$$
-> create procedure yxp()                           #创建存储过程yxp
-> begin                                                #过程体以关键字begin开始
-> declare i int;                               #定义变量i为int类型（长度最大为10）
-> set i = 1;                                           #设置i = 1;
->  while i <= 100                                  #使用while循环，i要小于100
-> do insert into nametest(name,age) values
            #满足条件则进行添加数据，内容为变量i
-> set i=i+1;                                       #变量i每次循环后加1                             
-> end while;                                           #结束while循环
-> end $$                                               #创建存储过程结束
delimiter ;                                             #重新修改默认结束符为原始的;
CALL proc6;                                             #调用yxp存储过程