MySQL------高阶SQL语句二_mysql计算每个人的总成绩并排名-CSDN博客

本文链接：https://blog.csdn.net/weixin_55611216/article/details/118340423

一.连接查询

使用以下两张表进行演示
在这里插入图片描述

inner join(等值相连)：只返回两个表中联结字段相等的行
left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录

select * from Area A INNER JOIN Output B on A.City_Name = B.City_Name ;

在这里插入图片描述

select * from Area A LEFT JOIN Output B on A.City_Name = B.City_Name ;

在这里插入图片描述

select * from Area A RIGHT JOIN Output B on A.City_Name = B.City_Name ;

在这里插入图片描述

select * from Area A, Output B WHERE A.City_Name = B.City_Name;

在这里插入图片描述

select A.Region REGION, SUM(B.Yield) SALES from Area A, Output B where A.City_Name = B.City_Name group by REGION;

在这里插入图片描述

二. CREATEVIEW（视图）

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

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

语法: CREATE VIEW "视图表名”AS "SELECT 语句";

create view V_REGION AS select A.Region REGION, SUM(B.Yield) SALES from Area A inner join Output B ON A.City_Name = B.City_Name group by REGION;

在这里插入图片描述

select  * from V_REGION;

在这里插入图片描述

drop view V_REGION;

在这里插入图片描述

三.连集，交集，非交集

1.连集(UNION)

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

UNION :生成结果的资料值将没有重复，且按照字段的顺序进行排序
语法: [SELECT 语句1] UNION [SELECT 语句2];
UNIONALL:将生成结果的资料值都列出来，无论有无重复

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

在这里插入图片描述

select City_Name from Area UNION select City_Name from Output;

在这里插入图片描述

select City_Name from Area UNION ALL select City_Name from Output;

在这里插入图片描述

2.交集值

取两个SQL语句结果的交集

select A.City_Name from Area A INNER JOIN Output B ON A.City_Name = B.City_Name;

在这里插入图片描述

select A.City_Name from Area A INNER JOIN Output B using (City_Name);

在这里插入图片描述

#两表没用单独重复的行，并且确实有交集的时候用
select A.City_Name from
(select City_Name from Area UNION ALL select City_Name from Output) A
group by A.City_Name HAVING COUNT(*) > 1;

在这里插入图片描述

#两表其中的一个表没有指定的行，而另一个表这个行有重复不适用，要求两个表确实有交集的行的时候用
select A.City_Name from
(select City_Name from Area UNION ALL select City_Name from Output) A
group by A.City_Name HAVING COUNT(*) > 1;

在这里插入图片描述

#取两个SQL语句结果的交集，且没有重复
select A.City_Name from (select B.City_Name from Area B INNER JOIN Output C ON B.City_Name =
C.City_Name) A
group by A.City_Name;

在这里插入图片描述

select distinct A.City_Name from Area A INNER JOIN Output B USING (City_Name);

在这里插入图片描述

select distinct City_Name from Area WHERE (City_Name) IN (SEIECT City_Name from Output);

在这里插入图片描述

select distinct A.City_Name from Area A LEFT JOIN Output B USING (City_Name) WHERE B.City_Name IS NOT
NULL;

在这里插入图片描述

3.无交集值

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

select distinct City_name from Area where (City_name) not in (select City_name from Output);

在这里插入图片描述

select distinct A.City_name from Area A left join Output B using(City_name) where B.City_name is null;

在这里插入图片描述

四. CASE

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

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

# "条件”可以是一个数值或是公式。 ELSE子句则并不是必须的。
select City_Name, CASE City_Name
WHEN 'nanjing' THEN Yield * 2
WHEN 'lanzhou' THEN Yield * 1.5
ELSE Yield
END
"New Yield",Date
from Output;
#"New Sales" 是用于CASE那个栏位的栏位名。

在这里插入图片描述

五. 数据排序

CREATE TABLE Total (Name char(10),Sales int(5));
INSERT INTO Total VALUES ('zhangsan',100);
INSERT INTO Total VALUES ('lisi',150);
INSERT INTO Total VALUES ('wangwu',200);
INSERT INTO Total VALUES ('zhaoliu',400);
INSERT INTO Total VALUES ('sunqi',500);
INSERT INTO Total VALUES ('zhouba',200);
INSERT INTO Total VALUES ('wujiu',300);

在这里插入图片描述

1.算排名

表格自我连结(Self Join)，然后将结果依序列出，算出每一行之前(包含那一行本身)有多少行数

select A1.Name, A1.Sales,count(A2.Sales) Rank from Total A1,Total A2
WHERE A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name = A2.Name)
group by A1.Name, A1.Sales ORDER BY A1.Sales DESC;
#统计Sales栏位的值是比自己本身的值小的以及sales栏位和Name栏位都相同的数量

在这里插入图片描述

2.算中位数

SELECT Sales Middle FROM (SELECT A1.Name, A1.Sales, COUNT(A2.Sales) Rank FROM Total A1, Total A2
WHERE A1.Sales < A2.Sales OR (A1.Sales = A2.Sales AND A1.Name >= A2.Name)
GROUP BY A1.Name, A1.Sales ORDER BY A1.Sales DESC) A3
WHERE A3.Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total Sales);
#每个派生表必须有自己的别名，所以别名A3必须要有
#DIV是在MySQL中算出商的方式

在这里插入图片描述

3.算累积总计

表格自我连结(Self Join)，然后将结果依序列出，算出每一行之前(包含那一行本身)的总合

select A1.Name,A1.Sales,sum(A2.Sales) Sum_Total from Total A1, Total A2
where A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
group by A1.Name, A1.Sales ORDER BY A1.Sales DESC;

在这里插入图片描述

4.算总合百分比

select A1.Name,A1.Sales,A1.Sales/ (select sum(Sales) from Total) Per_Total from Total A1, Total A2
where A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name = A2.Name)
group by A1.Name,A1.Sales order by A1.Sales DESC;

#SELECT SUM(Sales) FROM Total这一段子查询是用来算出总合
#总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比

在这里插入图片描述

select A1.Name,A1.Sales,round(A1.Sales/(select sum(Sales) from Total)*100,2)||'%' Percent,count(A2. Sales)
Rank from Total A1,Total A2 where A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name >= A2.Name)
group by A1.Name, A1.Sales order by A1.Sales DESC;

在这里插入图片描述

5.算累积总合百分比

select A1.Name,A1.Sales,A1.Sales/(select sum(Sales)  from Total) Per_Total
from Total A1, Total A2
where A1.Sales < A2.Sales or (A1.Sales=A2.Sales AND A1.Name = A2.Name)
group by A1.Name,A1.Sales order by A1.Sales DESC;

在这里插入图片描述
用累积总计SUM（A2.Sales）除以总和求出每一行的累计总合百分比

select A1.Name,A1.Sales,A1.Sales,truncate(round(sum(A2.Sales)/(select sum(Sales) from Total),4)*100,2) || '%' Per_Total
from Total A1, Total A2
where A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
group by A1.Name,A1.Sales order by A1.Sales DESC;

在这里插入图片描述

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

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

重新创建一个新的表格，表格数据如下：

create table City (name char(10));
insert into City(name) values('nanjing');
insert into City(name) values('guangzhou');
insert into City(name) values(NULL);
insert into City(name) values('beijing');
insert into City(name) values('');
insert into City(name) values(NULL);
insert into City(name) values('hangzhou');

在这里插入图片描述

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

在这里插入图片描述

select * from City where name IS NULL;
select * from City where name IS NOT NULL;

在这里插入图片描述

select * from City where name = '';
select * from City where name <> '';

在这里插入图片描述

select COUNT(*) from City;
select COUNT(name) from City;

在这里插入图片描述

七. 正则表达式

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

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

在这里插入图片描述

select * from Output WHERE City_Name REGEXP 'ou';

在这里插入图片描述

select * from Output WHERE City_Name REGEXP '^[A-G]';

在这里插入图片描述

select * from Output WHERE City_Name REGEXP '^(la|na)';

在这里插入图片描述

八. 存储过程

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

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

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

1.创建存储过程

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

在这里插入图片描述

2.调用存储过程

CALL Proc;

在这里插入图片描述

3.查看存储过程

SHOW CREATE PROCEDURE [数据库. ]存储过程名;

#查看某个存储过程的具体信息
SHOW CREATE PROCEDURE Proc;
SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G

在这里插入图片描述

4.存储过程的参数

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

DELIMITER %%
CREATE PROCEDURE Proc1 (IN newname char(10))
-> BEGIN
-> select * from Output where City_Name = newname;
-> END %%
DELIMITER ;
CALL Proc1 ('nanjing');

在这里插入图片描述

5.删除存储过程

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

DROP PROCEDURE IF EXISTS Proc;
#仅当存在时删除，不添加IF EXISTS时，如果指定的过程不存在，则产生一个错误

在这里插入图片描述

6.存储过程的控制语句

create table t (id int(10));
insert into t values(10) ;

在这里插入图片描述

6.1 条件语句

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

在这里插入图片描述

6.2循环语句

while … end while

DELIMITER %%
CREATE PROCEDURE proc3 ()
-> begin
-> declare var int(10);
-> set var=0;
-> while var<6 do
-> insert into t values (var);
-> set var=var+1;
-> end while;
-> end %%
DELIMITER ;
CALL Proc3;