高阶SQL语言VIEW（视图）

于 2022-08-15 10:53:46 发布
阅读量3.3k 收藏 5
点赞数
文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Sisonglin1988/article/details/126342794
版权

高阶SQL语言VIEW（视图）

1.1 概念

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

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

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

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

1.2 创建、查看和删除视图

建立两张表，字段数据如下，后面测试用：

img

CREATE VIEW “视图表名” AS “SELECT 语句”; #创建视图表

SELECT * FROM ‘V_NAME_VALUE’; #查看视图表

DROP VIEW V_NAME_VALUE; #删除视图表

实例：

CREATE VIEW V_NAME_VALUE AS SELECT A.name NAME,SUM(B.value) VALUE FROM club A INNER JOIN football B ON A.name = B.name GROUP BY NAME;

img

二、联集

将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

2.1 UNION

生成结果的资料值将没有重复，且按照字段的顺序进行排序

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

示例：

SELECT name FROM club UNION SELECT name FROM football;

img

2.1 UNION ALL

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

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

示例：

SELECT name FROM club UNION ALL SELECT name FROM football;

img

三、交集值

取两个SQL语句结果的交集

SELECT A.name FROM club A INNER JOIN football B ON A.name = B.name;

SELECT A.name FROM club A INNER JOIN football B USING(name);

示例：

img

3.1 两表没有单独重复的行，并且确实有交集的时候用

SELECT A.name FROM (SELECT name FROM club UNION ALL SELECT name FROM football ) A GROUP BY A.name HAVING COUNT(*) > 1;

这里的A表示派生表

示例：

img

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

SELECT A.name FROM (SELECT B.name FROM club B INNER JOIN football C ON B.name = C.name ) A GROUP BY A.name;

SELECT DISTINCT A.name FROM club A INNER JOIN football B USING(name);

SELECT DISTINCT name FROM club WHERE (name) IN (SELECT name FROM football);

SELECT DISTINCT A.name FROM club A LEFT JOIN football B USING(name) WHERE B.name IS NOT NULL;

示例：

img

三、无交集值

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

SELECT DISTINCT name FROM club WHERE (name) NOT IN (SELECT name FROM football);

实例：

img

四、CASE的用法

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

语法格式：

SELECT CASE （字段名）

WHEN “条件1” THEN “结果1”

WHEN “条件2” THEN “结果2”

……

ELSE “结果N”

END

FROM “表名”

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

示例：

select case area #选择area字段

when ‘America’ then value + 10000000 #如果球员是America的就加一千万

when ‘Europe’ then value + 10000000 #如果球员是Europe的就减一千万

else value / 2 #其他情况除2

end #结束此case

result,name,area #别名为result，显示name和area字段

from football; #以上操作对于football表进行

img

五、排名的计算

表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数

示例：

select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;

原理解释：

字段中的每个值跟所有的值进行比较，最大的值跟所有值比较后，大于或等于的只有一个，排名第二的值跟所有的值比较后，大于或等于的有两个，以此类推。

img

六、中位数的计算

## 求出中位数，显示姓名，value和排名

select * from (select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from football);

## 求出中位数，仅显示value值

select value mid from (select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from football);

示例：

img

七、累积总计的计算

同字段后面中的值都是前面所有值累计而成的

例：

## 计算value的累积总计

select A1.*,sum(A2.value) sum_socore from football A1,football A2 where A1.value < A2.value or(A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;

img

八、总合百分比的计算

每个值占总和的比例

例：

## 计算每位球员value值占整个value值的比例

select A1.*,A1.value/(select sum(value) from football) z_sum from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name;

img

九、累积总合百分比的计算

同字段后面中的值对应的百分比都是前面所有值的百分比累计而成的

示例：

##计算value的累积总计百分比

select A1.name,A1.value,sum(A2.value),sum(A2.value)/(select sum(value) from football) Z from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;

img

##计算value的累积总计百分比并用%表示出来

select A1.name,A1.value,sum(A2.value),TRUNCATE(sum(A2.value)/(select sum(value) from football),2) ||‘%’ Z from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;

img

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

无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的；

IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的；

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

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

## 创建表

create table SITE(site varchar(20));

insert into SITE values(‘jiangsu’);

insert into SITE values(‘anhui’);

insert into SITE values(‘’);

insert into SITE values(‘shandong’);

insert into SITE values(‘’);

insert into SITE values();

insert into SITE values(‘heilongjiang’);

## 测试

select length(site) from SITE;

select * from SITE where site is NULL;

select * from SITE where site is not NULL;

select * from SITE where site =‘’;

select * from SITE where site <> ‘’;

1）新建SITE表

img

2）测试

img

一、正则表达式（REGEXP）

1.1 正则表达式匹配符

img

1.2 正则表达式示例

建立下面mysql表，方便测试使用：

img

示例1：查找name中包含字符串’罗’的数据

SELECT * FROM football WHERE name REGEXP ‘罗’;

img

示例2：查找age中年龄是2开头的数据

SELECT * FROM football WHERE age REGEXP ‘^2’;

img

示例3：查找value中1和0之间是任意字符的数据

SELECT * FROM football WHERE value REGEXP ‘1.0’;

img

示例4：查找value中包含80的，且0可以是任意个的数据

SELECT * FROM football WHERE value REGEXP ‘80*’;

img

示例5：查找age中年龄是29或者34岁的数据

img

示例6：查找value中身价为0到3开头的数据

img

二、存储过程

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

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

2.1 存储过程的优点

1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率；

2、SQL语句加.上控制语句的集合，灵活性高；；

3、在服务器端存储，客户端调用时，降低网络负载

5、可完成所有的数据库操作，也可控制数据库的信息访问权限

2.2 创建、调用和查看存储的过程

1）创建存储过程

DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲将语句的结束符号从分号;临时改… (可以是自定义)

CREATE PROCEDURE proc() #创建存储过程，过程名为Proc， 不带参数

-> BEGIN #过程体以关键字BEGIN开始

-> SELECT * FROM football; #过程体语句（自己根据需求进行编写）

-> END $$ #过程体以关键字END结束

DELIMITER ; #将语句的结束符号恢复为分号

示例：

img

2）调用存储过程

CALL proc;

示例：

img

3）查看存储过程

SHOW CREATE PROCEDURE [数据库.] 存储过程名; #查看某个存储过程的具体信息（如果在指定库中，库名可以省略）

SHOW CREATE PROCEDURE athlete.proc; #未省略库名

SHOW CREATE PROCEDURE proc; #省略库名

SHOW CREATE PROCEDURE STATUS [LIKE ‘%proc%’] \G #竖列查看

示例：

img

4）删除存储过程

DROP PROCEDURE IF EXISTS proc;

示例：删除存储过程并查看

img

2.3 存储过程的参数

IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)

OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

CREATE PROCEDURE proc1(IN inname CHAR(10))

-> BEGIN

-> SELECT * FROM football WHERE name = inname;

-> END $$

mysql> DELIMITER ;

mysql> CALL proc1(‘梅西’);

示例：

img

2.4 存储过程的控制语句

2.4.1 条件控制语句 if-then-else … end if

DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲修改默认结束符为

CREATE PROCEDURE proc2(IN pro int) #创建存储过程proc2，参数为pro，类型为int

-> BEGIN #过程体以关键字BEGIN开始

-> DECLARE var int; #定义变量var为int类型

-> SET var=pro*3; #设置变量var等于传入参数的3倍

-> if var>=10 then #如果变量var大于10，则执行下面过程体

-> UPDATE football SET id=id+10; #设置表football中的id加10

-> else #如果变量var不大于10，则执行下面过程体

-> UPDATE football SET id=id-10; #设置表football中的id减10

-> end if; #结束if语句

-> END $$ #创建存储过程结束

DELIMITER ; #重新修改默认结束符为原始的;

CALL proc2(4); #调用proc2存储过程，并传入参数4

实例：

1）查看原始数据

img

2）创建含有控制语句存储过程并调用

img

3）查看调用存储过程之后的数据

img

2.4.2 循环语句 while … end while

CREATE TABLE test(id int(3)); #创建test表，只有一个字段id

DELIMITER KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲修改默认结束符为

-> CREATE PROCEDURE proc6() #创建存储过程proc6

-> BEGIN #过程体以关键字BEGIN开始

-> DECLARE var int(10); #定义变量var为int类型（长度最大为10）

-> set var=0; #设置var=0

-> while var<6 do #使用while循环，var要小于6

-> INSERT INTO test VALUES(var); #满足条件则进行添加数据，内容为变量var

-> set var=var+1; #变量var每次循环后加1

-> end while; #结束while循环

-> END$$ #创建存储过程结束

DELIMITER ; #重新修改默认结束符为原始的;

CALL proc6; #调用proc2存储过程

实例：

1）创建表test，只有一个字段id，查看数据为空

img

2）创建含有while循环语句存储过程并调用

img

3）查看调用存储过程之后的数据

img

-> CREATE PROCEDURE proc6() #创建存储过程proc6

-> BEGIN #过程体以关键字BEGIN开始

-> DECLARE var int(10); #定义变量var为int类型（长度最大为10）

-> set var=0; #设置var=0

-> while var<6 do #使用while循环，var要小于6

-> INSERT INTO test VALUES(var); #满足条件则进行添加数据，内容为变量var

-> set var=var+1; #变量var每次循环后加1

-> end while; #结束while循环

-> END$$ #创建存储过程结束

DELIMITER ; #重新修改默认结束符为原始的;

CALL proc6; #调用proc2存储过程

实例：

1）创建表test，只有一个字段id，查看数据为空

[外链图片转存中…(img-w87fPduC-1660532000619)]

2）创建含有while循环语句存储过程并调用

[外链图片转存中…(img-Ayg9Rs8h-1660532000620)]

3）查看调用存储过程之后的数据

[外链图片转存中…(img-RE2Klm6D-1660532000620)]

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值