MySQL之高级语句（二）

MrHOPE
136 阅读9分钟

MySQL之高级语句（二）


⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢀⣤⣶⣿⣿⣿⣿⣿⣿⣿⣿⣿⣦⣤
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢰⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣷
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⡇
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⠿⠛⠉⠛⣿⣿⡄
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣍⣀⣈⣙⣿⣿⣿⣿⡿⠟⣀⣠⣤⣄⡄⠻⣿⣇
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠄⣿⡿⠛⠛⠛⢻⣿⣿⣧⣾⣏⣀⣀⠀⢁⣠⣼⣿
⠀⠀⠀⠀⠀⠀⠀⠀⠀⢀⣿⣷⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣷⣶⣿⣿⣿⡧
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠘⣿⣿⣿⣿⣿⢿⣿⣿⣿⣟⣙⢻⣿⣿⣿⡿⠿⠗
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢸⣿⣿⣿⠏⣼⠿⣿⠿⠟⠋⠀⠘⣿⠏⠀⠀⠀⢀⠀⠁
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣿⣿⣿⣴⣿⣶⣷⣶⣶⣶⡆⢠⣿⣷⣾⡟⠀⠈
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠘⣿⣿⣿⣿⠛⠉⠁⠀⠀⠀⠀⠿⣿⠟⠁
⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠙⣿⣿⣷⣦⣽⣓⡠⠝⠀⠠⠘⠋
⠀⠀⠀⠀⠀⠀⠀⣠⣤⡀⠀⠀⠀⢹⣿⣿⣿⣤⣍⣀⣀⣀⡴⠁⠀⠀⠀⢀\
⠀⠀⠀⠀⠀⠀⠀⢸⣿⣿⠀⠀⠀⠀⠙⢄⠙⠛⠛⠉⠀⠈\
⠀⠀⠀⠀⠀⠀⠀⢸⣿⣿⠀⠀⠀⠀⠀⠀⠈⠐⠠⠤\
⠀⠀⠀⠀⠀⠀⠀⣿⣿⣿⣀⡀⠀⠀⣀⣀⡀\
⠀⠀⠀⠀⠀⠀⠀⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣶⡄\
⠀⠀⠀⠀⠀⠀⢸⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣷\
⠀⠀⠀⠀⠀⠀⣿⣿⣿⣿⣿⣿⣿⣿⣴⣿⣿⣟⡉⡁\
⠀⠀⠀⠀⢀⣴⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⡿⠏\
⠀⠀⢀⣴⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⠿⠥⡆\

视图（CREATE VIEW）

CREATE VIEW——视图，可以被当作是虚拟表或存储查询。

  • 视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
  • 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
  • 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。 比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。 视图表就是保存了select语句的查询结果，原表数据变化后，视图表的结果也会发生变化。
语法：
CREATE VIEW "视图表名" AS "SELECT 语句";   #创建视图表
DROP VIEW "视图表名";                     #删除视图表

示例：

 #将两个表的连接查询，创建为视图表
 create view v_region_sales as select A.region region,sum(B.sales) sales from location A inner join store_info B on A.store_name=B.store_name group by region;
 ​
 select * from v_region_sales;     #查看视图表的存储结果
 ​
 drop view v_region_sales;         #删除视图表

Snipaste_2022-06-11_13-53-37.png

Snipaste_2022-06-11_13-54-14.png

视图表与派生表

视图表，简化复杂的查询。只需创建一次，后面可以直接对已创建好的视图表进行操作。

派生表，两个select查询写在一个句子当中，比较复杂冗长。

  #视图表：只需创建一次，后面可以直接对已创建好的视图表进行操作。
 create view v_region_sales as select A.region region,sum(B.sales) sales from location A inner join store_info B on A.store_name=B.store_name group by region;
 
 select sum(sales) from v_region_sales;    #对视图表进行操作
 
 #派生表：C就是子查询中select语句的派生表。
 select sum(C.sales) from (select A.region region,sum(B.sales) sales from location A,store_info B where A.store_name=B.store_name group by region) C;

Snipaste_2022-06-11_14-05-07.png

视图表插入数据

  1. 如果视图表的结构和原表结构一致，可以修改和插入数据。

    • 例如原表有3个字段，视图表有2个字段，只要这2个字段和原表结构是一致的，也可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）

  2. 如果视图表是两个表的连接查询，则无法插入数据。因为表结构和原表不一致。

示例：

如果视图表的结构和原表结构一致，可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）

 #该视图表只是对单个表的查询结果。
 create view v_test_info as select * from store_info;
 ​
 #查看视图表结构，和原表一致。
 desc v_test_info;
 ​
 #插入数据成功
 insert into v_test_info values('NYC',500,'2020-12-10');

Snipaste_2022-06-11_14-17-32.png

Snipaste_2022-06-11_14-18-37.png

如果视图表是两个表的连接查询，则无法插入数据。因为表结构和原表不一致

 #该视图表是两个表的连接查询
 create view v_region_sales as select A.region region,sum(B.sales) sales from location A inner join store_info B on A.store_name=B.store_name group by region;
 ​
 #查看视图表结构，和原表不一致。
 desc v_region_sales;
 ​
 #插入数据失败
 insert into v_region_sales values('middle',500);

Snipaste_2022-06-11_14-21-23.png

联集

UNION联集：将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类。

UNION（合并后去重）

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序。

 [select 语句1] UNION [select 语句2];
 ​
 示例：
 select store_name from location union select store_name from store_info;
 #将两个表的store_name字段的值合并起来，union生成的结果去重

Snipaste_2022-06-11_14-24-58.png

UNION ALL（合并后不去重）

将生成结果的数据记录值都列出来，无论有无重复。

 [select 语句1] UNION ALL [SELECT 语句2];
 ​
 示例：
 select store_name from location union all select store_name from store_info;
 #将两个表的store_name字段的值合并，全都列出来，union all生成的结果不去重。

Snipaste_2022-06-11_14-26-57.png

交集值

交集值：取两个SQL语句结果的交集。

1 使用union all + group by+having求交集值

注意：使用联集的方式求交集时，两个表的目标字段值必须先各自去重，之后再合并。避免因为单个表内存在重复值，导致错误计算。

 #两个表各自将store_name字段的值进行去重，之后合并，再创建视图表。
 create view v_store_name as select distinct store_name from location union all select distinct store_name from store_info;
 ​
 #对视图表的store_name字段进行分组汇总，计算每组的数量。
 select store_name,count(*) from v_store_name group by store_name;
 ​
 #对视图表的store_name字段进行分组汇总，计算每组的数量，过滤出数量大于1的store_name字段值，就是两个表的交集部分。
 select store_name from v_store_name group by store_name having count(*) >1;

Snipaste_2022-06-11_14-31-19.png

Snipaste_2022-06-11_14-34-20.png

2 使用内连接求交集值

取两个表的store_name字段值的交集部分：

 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);

Snipaste_2022-06-11_15-20-56.png 取两个表的store_name字段值的交集部分，之后去重，加distinct：

 select distinct A.store_name from location A inner join store_info B using(store_name);

Snipaste_2022-06-11_15-23-29.png

3 使用左连接求交集值

 #使用左连接查询store_name字段的交集部分
 select * from location A left join store_info B using(store_name);
 ​
 #使用左连接查出store_name字段的交集值，之后去重
 select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is not null;

Snipaste_2022-06-11_15-25-57.png

4 使用右连接求交集值

 #使用右连接查出store_name字段的交集值，之后去重
 select distinct A.store_name from location A right join store_info B using(store_name) where A.store_name is not null;
 ​
 #方法二：
 select distinct A.store_name from location A right join store_info B on A.store_name=B.store_name where A.store_name is not null;

Snipaste_2022-06-11_15-27-57.png

5 使用子查询的方式求交集值 in

 #使用子查询的方式查出store_name字段的交集值，之后去重
 select distinct store_name from location where store_name in (select store_name from store_info);

Snipaste_2022-06-11_15-28-47.png

无交集值

无交集值：显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复。

使用union all + group by+having求无交集值：

 #两个表各自将store_name字段的值进行去重，之后合并，再创建视图表。
 create view v_store_name as select distinct store_name from location union all select distinct store_name from store_info;
 ​
 #对视图表的store_name字段进行分组汇总，计算每组的数量。
 select store_name,count(*) from v_store_name group by store_name;
 ​
 #对视图表的store_name字段进行分组汇总，计算每组的数量，过滤出数量等于1的store_name字段值，就是两个表无交集的部分。
 select store_name from v_store_name group by store_name having count(*) =1;

Snipaste_2022-06-11_15-35-35.png

Snipaste_2022-06-11_15-36-13.png

Snipaste_2022-06-11_15-36-49.png

CASE

case：是 SQL 用来作为 IF-THEN-ELSE 之类逻辑的关键字。 语法：

 SELECT CASE ("字段 名")
     WHEN "条件1" THEN "结果1"
     WHEN "条件2" THEN "结果2"
     [ELSE "结果N"]
     END
 FROM "表名";
     
 # "条件"可以是一个数值或是公式。ELSE子句则并不是必须的。

示例：

 select store_name,CASE store_name        #对sttore_name字段值进行判断
     WHEN 'Los Angeles' THEN sales * 2    #当店名为洛杉矶时销售额乘2
     WHEN 'Boston' THEN 2000              #当店名为波士顿时销售额显示2000
     ELSE sales / 2                       #其他店名则销售额除以2
     END                                  #判断结束
 "new sales", date                        #用于显示的字段名
 from store_info;
 ​
 #"new sales" 是用于case那个字段的字段名。
 ​
 #注：查询并不改变原表的值。

Snipaste_2022-06-11_15-50-13.png

Snipaste_2022-06-11_15-52-26.png

空值（NULL）和无值（' '）的区别

空值（NULL）和无值（' '）的区别：

  1. 无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的。
  1. IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。
  1. 无值的判断使用=' '或者< >' '来处理。<>代表不等于。
  1. 在通过 count ()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。 先准备一个表city：
 create table city(name varchar(10));
 insert into city values('beijing');
 insert into city values('nanjing');
 insert into city values();
 insert into city values();
 insert into city values('hangzhou');
 insert into city values('');

Snipaste_2022-06-11_16-01-52.png

示例：

  1. 查看无值和空值的长度。 无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的。
 select length(NULL), length(''), length('123');

Snipaste_2022-06-11_16-21-18.png

  1. IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。
 select * from city where name is null;      
 #查询字段值为NULL的数据记录
 select * from city where name is not null;   
 #查询字段值不为NULL的数据记录

Snipaste_2022-06-11_16-24-02.png

  1. 无值的判断使用=' '或者< >' '来处理。< > 代表不等于。
select * from city where name='';      
 #查询字段值为无值的数据记录
select * from city where name <> '';   
#查询字段值不为NULL以及不为无值的数据记录

Snipaste_2022-06-11_16-29-49.png

  1. 在通过 count () 指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。
 select count(name) from city;     
 #会忽略null值，但不会忽略无值
 select count(*) from city;        
 #会统计所有行

Snipaste_2022-06-11_16-33-00.png

正则表达式——精确查询

匹配模式描述实例
^匹配文本的开始字符‘^bd’ 匹配以 bd 开头的字符串
$匹配文本的结束字符‘qn$’ 匹配以 qn 结尾的字符串
.匹配任何单个字符‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*匹配零个或多个在它前面的字符‘fo*t’ 匹配 t 前面有任意个 o
+匹配前面的字符 1 次或多次‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串匹配包含指定的字符串‘clo’ 匹配含有 clo 的字符串
p1p2匹配 p1 或 p2
[...]匹配字符集合中的任意一个字符‘[abc]’ 匹配 a 或者 b 或者 c
[^...]匹配不在括号中的任何字符'[abc]' 匹配不包含 a 或者 b 的字符串
{n}匹配前面的字符串 n 次‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}匹配前面的字符串至少 n 次，至多m 次‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

语法：

 select "字段" from "表名" where "字段" regexp {模式};

示例：

 #查询store_name字段包含字符串"os"的行
 select * from store_info where store_name regexp 'os';
 ​
 #查询store_name字段以A-G开头的行
 select * from store_info where store_name regexp '^[A-G]';
 ​
 #包含Ho或者Bo
 select * from store_info where store_name regexp 'Ho|Bo'; 
 ​
 #以“Bo”开头且以“on”为结尾
 select * from store_info where store_name regexp '^Bo.*on$';

Snipaste_2022-06-11_16-51-11.png

存储过程

存储过程是什么

存储过程是一组为了完成特定功能的SQL语句集合。

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

优点

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

创建、调用、查看、删除存储过程

 ##创建存储过程：
 DELIMITER $$                  #将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
 CREATE PROCEDURE Proc ()      #创建存储过程，过程名为Proc，不带参数
 -> BEGIN                      #过程体以关键字BEGIN开始
 -> select * from store_info;  #过程体语句
 -> END $$                     #过程体以关键字END结束
 ​
 DELIMITER;                    #将语句的结束符号恢复为分号
 ​
 ##调用存储过程##
 CALL Proc;
 ​
 ##查看存储过程##
 SHOW CREATE PROCEDURE [数据库.]存储过程名;  #查看某个存储过程的具体信息
 ​
 SHOW CREATE PROCEDURE Proc;
 SHOW CREATE PROCEDURE Proc\G
 ​
 SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G
 ​
 ##删除存储过程##
 #存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
 DROP PROCEDURE IF EXISTS Proc;
 #仅当存在时删除，不添加If EXISTS 时，如果指定的过程不存在，则产生一个错误。

示例：

  1. 创建和调用
 delimiter $$
 create procedure proc01 ()    #创建存储过程，过程名为proc01，不带参数
 -> begin                      #过程体以关键字BEGIN开始
 -> create table student(id int,name char(10),age int);
 -> insert into student values(1,'huge',25);
 -> insert into student values(2,'dingzhen',20);
 -> select * from student;
 -> end $$                     #过程体以关键字END结束
 
 delimiter ;                   #将语句的结束符号恢复为分号
 
 call proc01;                  #调用存储过程

Snipaste_2022-06-11_17-12-00.png Snipaste_2022-06-11_17-12-33.png

  1. 查看存储过程
 show create procedure proc01;
 show create procedure proc01\G      
 #查看存储过程的具体信息
 ​
 show procedure status like '%Proc01%'\G

Snipaste_2022-06-11_17-15-41.png 3. 删除存储过程

 drop procedure if exists proc01;
 #仅当存在时删除，不添加If EXISTS 时，如果指定的过程不存在，则产生一个错误。

Snipaste_2022-06-11_17-17-17.png

存储过程的参数

  • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
  • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
  • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
 delimiter $$
 create procedure proc2(in stuname char(20))    #参数为stuname，数据类型一定要与下面的where语句后字段的数据类型相同
 -> begin
 -> select * from student where name = stuname;
 -> end $$
 delimiter ;
  
 call proc2('dingzhen');      #调用存储过程，并传入参数'dingzhen'
 注：变量名不能有下划线

Snipaste_2022-06-11_17-23-15.png

存储过程的控制语句

 #创建一个表格
 create table abc (id int(10)) ;
 insert into abc values (10) ;

Snipaste_2022-06-11_17-28-08.png

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

 delimiter $$  
 create procedure proc03(in innum int)   #创建存储过程proc03，参数为innum，类型为int 
 -> begin 
 -> declare var int;         #定义变量var为int类型 
 -> set var=innum*2;         #变量var的值等于传入的参数值乘2
 -> if var>=10 then          #当var的值大于10时，id值会加1，否则减1
 -> update abc set id=id+1;  
 -> else 
 -> update abc set id=id-1;  
 -> end if;  
 -> end $$
  
 delimiter ;
 ​
 call proc03(8);    #调用存储过程，并传入参数8
 ​
 call proc03(3);    #调用存储过程，并传入参数3

Snipaste_2022-06-11_17-44-51.png

Snipaste_2022-06-11_17-47-40.png

循环语句 while ···· end while

 delimiter $$                   #修改默认结束符为$$
 create procedure proc04()      #创建存储过程proc04
 -> begin                       #过程体以关键字begin开始
 -> declare var int(10);        #定义变量var为int类型
 -> set var=0;                  #var的起始值为0
 -> while var<6 do              #使用while循环，当var值小于6时满足条件，则向表中插入var的值
 -> insert into abc values(var);  
 -> set var=var+1;              #每次循环后var值自增1
 -> end while;                  #结束while循环
 -> end $$                      #创建存储过程结束
 ​
   delimiter ;                 #重新修改默认结束符为原始的;
 ​
 call proc04;                   #调用存储过程proc04

Snipaste_2022-06-11_17-53-35.png declare命令也可以用于shell中，例如：

decalre -i var 声明变量var为整数型（ 即增加整型属性）。

decalre +i var 删除变量var的整型属性。

avatar
文章
阅读
粉丝