MySQL------高阶SQL语句二

最新推荐文章于 2023-12-05 21:25:04 发布
最新推荐文章于 2023-12-05 21:25:04 发布
阅读量246 收藏 4
点赞数 2
分类专栏： MySQL 文章标签： mysql sql 数据库 高阶语句
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：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;

在这里插入图片描述

下雨天的放羊娃
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQL求百分比的几种方法
一个混错圈儿的小测试
11-12 3万+
一、函数介绍 1、ROUND(X,D)和ROUND(X) round函数用于数据的四舍五入，x指要处理的数，d是指保留几位小数 round(x) ，其实就是 round(x,0) d可以是负数，代表指定小数点左边的d位整数位为0,同时小数位均为0 2、FORMAT(X,D) 提供数据内容格式化功能，可以格式化数据为整数或者浮点数，能四舍五入 D为负数时，按0处理 3、LEFT(str,len) left函数是一个字符串函数，它返回具有指定长度的字符串的左边部分,str为要.
MySQL统计数量以及百分比
weixin_61428498的博客
07-26 2374
COUNT(*) 计算表中总的行数，无论某列有数值或者为空值。COUNT（字段名）计算指定列下总的行数，计算时将忽略空值的行。返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。round(x,d) -- x指要处理的数，d是指保留几位小数round(x,d) -- 同round(x,0)，即不保存小数。
MySql计算百分比
诚的博客
06-30 9680
根据相应条件抽出相应count数（myCount） 抽出总count数(totalCount) 计算百分比：myCount / totalCount * 100 四舍五入：使用ROUND函数ROUND(number，要保留的小数位)，ROUND(myCount / totalCount * 100,1) 下面给出示例供参考： SELECT ROUND(T1.co/T2.totalCo*100,1) FROM (SELECT COUNT(*) AS co FROM fup1 WHERE fu
MySQL 高级函数
最新发布
weixin_41312759的博客
12-05 1037
MySQL高级函数IFNULL(v1,v2)用于判断一个表达式的值是否为NULL，如果MySQL高级函数IFNULL(v1,v2)用于判断一个表达式的值是否为NULL，如果为NULL则返回另一个表达式的值，否则返回第一个表达式的值。否则返回第一个表达式的值。这将返回一个结果集，其中包含每个员工的id、name和age，以及一个名为row_number的列，该列包含根据年龄排序后的唯一数字。这个示例中，我们使用IF()函数来判断1是否大于0，如果为真则返回’True’，否则返回’False’。
MySQL常用函数大全（实例演示）
weixin_55076626的博客
10-18 7883
【代码】MySQL常用函数大全（实例演示）
mysql-connector-java-8.0.22.jar
08-14
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
MySQL内核源码解读-SQL解析
03-01
本文是基于MySQL5.7.22进行分析通过上面图,可以从全局上了解SQL语句执行流程以及与其他模块交互在介绍具体的MySQL数据库解析SQL之前,先来回归一下编程语言的知识点形式语言(Formallanguage)形式语言是用精确的数学或...
MySQL-Front_Setupv6.1.zip
01-10
著名的mysql-fornt客户端工具6.1版，作者是个德国青年，已于2019年初停止对mysql-fornt软件的开发和下载说是要“全神贯注于与上帝一起寻求和平与爱”，上次停更是因为mysql要求的，这次八成也是新东家oracle干的。...
ISO SQL-92标准
03-13
ISO SQL-92标准
Mysql经典50题
weixin_72036027的博客
12-08 1380
MySQL经典50题
MySql函数大全
12-19
MySql常用函数大全： MySQL必要时自动变换数字为字符串，并且反过来也如此： mysql> SELECT 1+"1"; -> 2 mysql> SELECT CONCAT(2,' test'); -> '2 test' 如果你想要明确地变换一个数字到一个字符串，把它作为参数传递到CONCAT()。 如果字符串函数提供一个二进制字符串作为参数，结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。
学科、姓名、分数。 分别计算每门学科分数最高的前三名，并显示名次。写入MySQL中。
01-20
hadoop,xiaoji,99 hadoop,mingyang,98 hadoop,susan,99 spark,xiaoli,98 spark,xiaohua,95 hive,lele,89 spark,zhangsan,99 hive,tim,97 hive,kebi,94 hive,mike,99 hadoop,xiaoming,96 hive,susan,96 spark,wenwen,97 hive,kimi,90 import org.apache.spark.rdd.RDD import org.apache.spark.sql.{DataFrame, SQLContext}
MySQL | 常见面试题练手及总结
樽前有酒休辞醉，心上无忧慢赏花
09-05 1015
参考源：上海-悠悠的博客 非常重要的是：仅作为自己的笔记，笔者小白，思路蛮混乱的，轻易勿参考 创建表 create table if not exists student( id int unsigned primary key auto_increment, name char(32) not null, age int(32) not null )engine = innodb auto_i...
Mysql经典45道题(详解16-30)
Hudas的博客
02-12 5088
Mysql经典45道题(详解16-30)
MySQL百分比显示和显示前百分之几的方法
热门推荐
杨明金的博客
12-22 3万+
1）查询所有时间内，所有产品销售金额占比，按占比大小降序排序，筛选累计占比在前80%的产品，结果输出排名产品名称销售金额占比累计占比。 2）查询所有时间内，各个国家的销售情况，销售合计金额大于10000视为业绩合格, 否则为不合格，结果输出国家销售金额业绩情况。 3）查询中国、英国每个月份的销售情况，2020年8月份销售合计金额大于10000视为业绩合格，否则为不合格，2020年9月份销售合计金额大于12000视为业绩合格，否则为不合格，结果输出月份中国销售业绩、英国销售业绩。
mysql查询成绩并排名_Mysql 查询实现成绩排名
weixin_42132598的博客
01-18 4259
近日系统要实现总分成绩排名，而且相同分数的学生排名要一样，在网上搜了一圈，没有找到合适的方法，只能靠自己实现了，这里提供两种方法1、sql查询实现测试如下：mysql> select * from score ;+----------+--------------+---------------------+--------------+-------+| study_no | studen...
mysql统计－关于学生成绩
mayanyun2013的专栏
03-10 2万+
转载地址http://blog.csdn.net/suntanyong88/article/details/7683089 问题： 1.   计算每个人的总成绩并排名(要求显示字段：姓名，总成绩) 2.   计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩) 3.   计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩) 4.   计算每
MySql实现按分数排名
m0_46211448的博客
03-05 3215
MySql实现排名效果
mysql中对总成绩进行排名_MYSQL成绩排名
weixin_32821533的博客
02-02 1067
今天在坛子上看到了，顺便写下来。有两种方法：1、效率不高，因为有子查询。但是简洁。而且我对SOCRES表做了INDEX。所以性能上也差不了多少。mysql> show create table scores\G*************************** 1. row ***************************Table: scoresCreate Table: CRE...
mysql -u root -p改成T-sql语句
09-14
在 T-SQL 中，连接到 SQL Server 数据库并使用已知的用户名和密码，可以使用以下语句： ``` sqlcmd -S <server_name> -U <username> -P ``` 请将 `<server_name>` 替换为你要连接的 SQL Server 实例的名称，`...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值