MySQL高级语言（二）

一、视图

1、VIEW

1.1概念

可以被当作是虚拟表或存储查询
视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

1.2创建、查看和删除视图

CREATE VIEW "视图表名" AS "SELECT 语句";                      #创建视图表
SELECT * FROM `V_NAME_VALUE`;                               #查看视图表
DROP VIEW V_NAME_VALUE;                         　　　　　　  #删除视图表

实例操作：
mysql> create view v_test_01 as select A.name,A.address from test A where name in (select B.name from t1 B where score > 20);
mysql> select *from v_test_01;
drop view v_test_01;

2、联集

将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的

2.1UNION

生成结果值将没有重复，且按照字段的顺序进行排序
语法：[SELECT 语句 1] UNION [SELECT 语句 2];

实例操作：
select name from t1 union select name from test;

2.2UNION ALL

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

实例操作：
select name from t1 union all select name from test;

3、交集值（取两个SQL语句结果的交集）

3.1取交集值的方法1

注：2种简单方法，内连接+on/using，去重则加上distinct

select A.name from t1 A inner join test B on A.name=B.name;
select A.name from t1 A inner join test B using(name);
select distinct A.name from t1 A inner join test B on A.name=B.name;

3.2取交集方法2

1种，union all结合group by：两表其中的一个表没有指定的行，而另一个表这个行有重复不可用，要求两个表确实有交集的时候用。

select A.name from (select name from t1 union all select name from test) A group by A.name having count(*) > 1;
select A.name from (select name from t1 union all select name from test) A group by A.name having count(name) > 1; 
select name from t1 union all select name from test; #拆分上面的SQL语句
select A.name,count(name) from (select name from t1 union all select name from test) A group by A.name having count(name) > 1; #显示count值，便于理解
select A.name,count(name) from (select distinct name from t1 union all select distinct name from test) A group by A.name having count(name) > 1; #去重显示，在联集两个表之前先把表去重，以防一个表中本身就有重复值

3.3取交集

取两个SQL语句结果的交集，且没有重复

方法一：
mysql> select A.name from (select B.name from t1 B inner join test C on B.name=C.name) A group by A.name;
方法二：
select distinct A.name from t1 A inner join test B using(name);
方法三：
select distinct name from t1 where name in (select name from test);
方法四：
select distinct A.name from t1 A left join test B using(name) where B.name is NOT NULL;

（1）内连接取交集结合group by去重

（2）内连接取交集结合distinct去重

（3）where+in遍历取交集并结合distinct去重

（4）使用左连接（也可用右连接）+where 判断NOT NULL 取交集并结合distinct去重

4、无交集值

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

方法一：
select A.name from (select distinct name from t1 union all select distinct name from test) A group by A.name having count(name)=1;
方法二：
select distinct name from test where name not in (select distinct name from t1);
select distinct name from t1 where name not in (select distinct name from test);
方法三：
select distinct A.name from t1 A left join test B using(name) where B.name is NULL;
select distinct B.name from t1 A right join test B using(name) where A.name is NULL;

（1）union all结合group by进行分组汇总并使用count=1取无交集值

（2）where+not in遍历取无交集值并结合distinct去重

（3）使用左连接（或者右连接）+where 判断NULL 取无交集并结合distinct去重

5、CASE的用法

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

5.1语法格式

SELECT CASE （字段名）
    WHEN "条件1" THEN  "结果1"
    WHEN "条件2" THEN  "结果2"
    ……
    ELSE "结果N"
    END
FROM "表名"

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

5.2实例操作

6、空值（NULL）和无值（“”）的区别

区别：
无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的；
IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的；
无值的判断使用=’‘或者<>’'来处理。<>代表不等于；
在通过count()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算。

6.1判断空值和无值的字符长度

select length(NULL),length(''),length('1');

6.2使用count统计行数（体现null与空值的区别）

count(*) 表示包括所有列的行数，不会忽略null值；空值正常统计
count(列名) 表示只包括这一列，统计时会忽略null值的行；空值正常统计

二、正则表达式（REGEXP）

1、正则表达式匹配符

2、语法

SELECT 选项 FROM 表名 WHERE 选项 REGEXP (模式)

select * from test where name regexp 'wu$';　　　　###以wu结尾的
select * from test where score regexp '50|80';　　　　
select * from test1 where name regexp '^F|^Z';

三、存储过程

1、概述

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

2、优点

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

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

（1）创建存储过程

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

实操：

mysql> delimiter ##
mysql> create procedure proc()
    -> begin
    -> insert into test2 values (2,'lucy',23);
    -> insert into test2 values (2,'jack',21);
    -> insert into test2 values (4,'nancy',22);
    -> select * from test2;
    -> end ##

　　mysql> delimiter ;

（2）调用存储过程

mysql> CALL proc;

（3）查看存储过程

SHOW CREATE PROCEDURE [数据库.] 存储过程名;      #查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE fzr.proc;                 #未省略库名
SHOW CREATE PROCEDURE proc;                     #省略库名
 
SHOW PROCEDURE STATUS [LIKE '%proc%'] \G                #竖列查看

（4）存储过程的参数

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

实例操作：
IN 传入参数

mysql> delimiter ##
mysql> create procedure proc1(in iage int)
    -> begin
    -> select * from test2 where age > iage ;
    -> end ##
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
mysql> call proc1(21);

OUT输出参数

因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的num为null

#调用了proc2存储过程，输出参数，改变了num变量的值

INOUT输入输出参数

调用了proc3存储过程，接受了输入的参数，也输出参数，改变了变量

（5）删除存储过程

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

DROP PROCEDURE IF EXISTS proc1;

4、存储过程的控制语句

（1）条件控制语句 if-then-else … end if

mysql> delimiter ##                                #修改默认结束符为##
mysql> create procedure proc1(in iage int)         #创建存储过程proc1，参数为iage,数据类型为int
    -> begin                                       #过程体以关键词begin开始
    -> declare var int;                            #定义变量var为int类型
    -> set var=iage*2;                             #设置变量var等于传入参数的2倍
    -> if var >=20 then                            #如果var大于等于20,则执行下面的过程体
    -> update test2 set age=age+1;                 #设置test2中的age+1
    -> else                                        #如果变量var不大于10,则执行下面过程体
    -> update test2 set age=age-1;                 #设置表test2中的age-1
    -> end if;                                     #结束if语句
    -> end ##                                      #结束创建存储过程
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;                                 #重新修改默认结束符为原来的;
mysql> call proc1(10);                             #调用proc1的存储过程，并传入参数10
Query OK, 6 rows affected (0.00 sec)

（2）循环语句while … end while

mysql> delimiter ##                       #修改默认结束符为##
mysql> create procedure proc4()           #创建存储过程为proc4
    -> begin                              #过程体以关键字begin开始
    -> declare var int;                   #定义变量var为int类型
    -> set var=0;                         #设置var=0
    -> while var <10 do                   #使用while循环，var要小于10
    -> insert into test2(id) values(var); #满足条件则进行添加数据，内容为变量var
    -> set var=var+1;                     #变量var每次循环后加1
    -> end while;                         #结束while循环
    -> end ##                             #结束创建存储过程
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;                        #重新修改默认结束符为原来的;
mysql> call proc4;                        #调用proc4存储过程
Query OK, 1 row affected (0.01 sec)

posted @ 2022-12-04 17:42 崧蓝阅读(46) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

feizirui