MySQL数据库之——高级[进阶]SQL语句(二)VIEW视图、联集和常见计算
2021/9/4 2:07:27
本文主要是介绍MySQL数据库之——高级[进阶]SQL语句(二)VIEW视图、联集和常见计算,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、VIEW(视图)
1、 概念
可以被当作是虚拟表或存储查询
视图跟表格的不同是,表格中有实际储存资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。
临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写SQL语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
视图本身
2、 创建、查看和删除视图
CREATE VIEW "视图表名" AS "SELECT 语句"; #创建视图表 SELECT * FROM `V_NAME_VALUE`; #查看视图表 DROP VIEW V_NAME_VALUE; #删除视图表
实例操作:
select A.name,A.age from test6 A where name in (select B.name from test1 B where age >19 and age < 23); create view v_test1_6 as select A.name,A.age from test6 A where name in (select B.name from test1 B where age >19 and age < 23); select * from v_test1_6; drop view v_test1_6;
二、联集
将两个SQL语句的结果合并起来,两个SQL语句所产生的栏位需要是同样的资料种类
1、UNION
生成结果的资料值将没有重复,且按照字段的顺序进行排序
语法:[SELECT 语句 1] UNION [SELECT 语句 2];
实例操作:
select name from test1 union select name from test7;
2、UNION ALL
将生成结果的资料值都列出来,无论有无重复
语法:[SELECT 语句 1] UNION ALL [SELECT 语句 2];
实例操作:
select name from test1 union all select name from test7;
三、交集值
取两个SQL语句结果的交集
1、取交集值的方法1(2种简单方法,内连接+on/using去重则加上distinct)
select A.name from test1 A inner join test6 B on A.name=B.name; select A.name from test1 A inner join test6 B using(name); select distinct A.name from test1 A inner join test6 B on A.name=B.name;
2、取交集方法2(1种,union all结合group by)
两表其中的一个表没有指定的行,而另一个表这个行有重复不可用,要求两个表确实有交集的时候用
select A.name from (select name from test1 union all select name from test7) A group by A.name having count(*) > 1; select A.name from (select name from test1 union all select name from test7) A group by A.name having count(name) > 1; #不去重显示 select name from test1 union all select name from test7; #拆分上面的SQL语句 select A.name,count(name) from (select name from test1 union all select name from test7) A group by A.name having count(name) > 1; #显示count值,便于理解 select A.name,count(name) from (select distinct name from test1 union all select distinct name from test7) A group by A.name having count(name) > 1; #去重显示,在联集两个表之前先把表去重,以防一个表中本身就有重复值
3、取交集(去重)——4种方法
取两个SQL语句结果的交集,且没有重复
方法一: select A.name from (select B.name from test1 B inner join test6 C on B.name=C.name) A group by A.name; select B.name from test1 B inner join test6 C on B.name=C.name; select * from test1 B inner join test6 C on B.name=C.name; 方法二: select distinct A.name from test1 A inner join test6 B using(name); 方法三: select distinct name from test1 where name in (select name from test6); 方法四: select distinct A.name from test1 A left join test6 B using(name) where B.name is NOT NULL; select distinct A.name from test1 A left join test6 B using(name); select distinct A.name,B.name,B.age from test1 A left join test6 B using(name);
方法一:内连接取交集结合group by去重
方法二:内连接取交集结合distinct去重
方法三:where+in遍历取交集并结合distinct去重
方法四:使用左连接(也可用右连接)+where 判断NOT NULL 取交集并结合distinct去重
四、无交集值
显示第一个SQL语句的结果,且与第二个SQL语句没有交集的结果,且没有重复
方法一: select A.name from (select distinct name from test1 union all select distinct name from test6) A group by A.name having count(name)=1; 方法二: select distinct name from test1 where name not in (select name from test6); select distinct name from test6 where name not in (select distinct name from test1); 方法三: select distinct A.name from test1 A left join test6 B using(name) where B.name is NULL; select distinct B.name from test1 A right join test6 B using(name) where A.name is NULL;
方法一:union all结合group by进行分组汇总并使用count=1取无交集值
方法二:where+not in遍历取无交集值并结合distinct去重
方法三:使用左连接(或者右连接)+where 判断NULL 取无交集并结合distinct去重
五、CASE的用法
是SQL用来作为IF-THEN-ELSE之类逻辑的关键字
1、语法格式:
SELECT CASE (字段名) WHEN "条件1" THEN "结果1" WHEN "条件2" THEN "结果2" …… ELSE "结果N" END FROM "表名"
条件可以是一个数值或是公式。ELSE子句不是必须的
2、实例操作
mysql> select case name #选择字段name -> when 'lili' then age + 2 #当name=lili,则age的值+2 -> when 'kate' then age - 2 #当name=kate,则age的值-2 -> when 'lucy' then age * 2 #当name=lucy,则age的值*2 -> else age / 2 #其他值,则age的值/2 -> end 'new age', #结束case,并命名新的age值 -> name,age from test1; #选择要显示的字段以及要指定的表
六、排名的计算
表格自我连接(self join),然后将结果依序列出,算出每一行之前(包括那一行本身)有多少行数
select A1.name,A1.age,count(A1.age) from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by count(A1.age); select A1.name,A1.age,count(A1.age) rank from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by y A1.name order by rank;
注释:统计age字段的值是比自己本身的值小的以及age字段和name字段都相同的数量,比如lisi为6+1=7
七、中位数的计算
方法一:使用派生表 select name,age from ( select A1.name,A1.age,count(A1.age) rank from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by rank) A3 where A3.rank = (select (count(*)+1) DIV 2 from test1); 方法二:使用视图表 create view v_rank as select A1.name,A1.age,count(A1.age) rank from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.na.name=A2.name) group by A1.name order by rank; select name,age 'middle age' from v_rank v_rank where rank=(select (count(*)+1) DIV 2 from v_rank);
注释:每个派生表必须有自己的别名,所以别名A3必须有
DIV是在MySQL中算出商的方式
方法一:使用派生表
派生表为计算排名的sql语句,先进行排名,排名好之后取中位数
方法二:使用视图表
视图表为计算排名的sql语句,先进行排名,排名好之后取中位数
八、累积总计的计算
表格自我连接(self join),然后将结果依序列出,算出每一行之前(包括那一行本身)的总和
select A1.name,A1.age,sum(A2.age) "total age",count(A1.age) rank from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by rank;
九、总合百分比的计算
每个值占总和的比例
第一步:算出所占总合比(小数形式) select A1.name,A1.age,A1.age/(select sum(age) from test1) per_age,count(A1.age) rank from test1 A1,test1 A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by rank; 第二步:使用round四舍五入取2位小数,并乘以100 select A1.name,A1.age,round(A1.age/(select sum(age) from test1)*100,2) per_age,count(A1.age) rank from test1 A1,test1 A2 where A1.age e < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by rank; 第三步:使用|| 拼接%,得出百分比 select A1.name,A1.age,round(A1.age/(select sum(age) from test1)*100,2) || '%' per_age,count(A1.age) rank from test1 A1,test1 A2 where e A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by rank;
select sum(age) from test1 这一段子查询是用来统计总合
总合算出后,我们就能够每一行除以总合求出每一行的总合百分比
十、空值(NULL)和无值(“”)的区别
区别:
无值的长度为0,不占用空间;而空值null 的长度是null,是占用空间的;
IS NULL或者IS NOT NULL,是用来判断字段是不是NULL或者不是NULL,是不能查出是不是无值的;
无值的判断使用=’‘或者<>’'来处理。<>代表不等于;
在通过count()指定字段统计又多少行数时,如果遇到NULL值会自动忽略掉,遇到空值会自动加入记录中进行计算。
1、判断空值和无值的字符长度
select length(NULL),length(''),length('1');
2、判断并查找空值和非空值
select id,name from test1 where name is NULL; select id,name from test1 where name is not NULL;
3、判断并查找无值和非无值
select id,name from test1 where name=''; select id,name from test1 where name <> '';
4、使用count统计行数(体现null与空值的区别)
count(*) 表示包括所有列的行数,不会忽略null值;空值正常统计
count(列名) 表示只包括这一列,统计时会忽略null值的行;空值正常统计
select id,name from test1; select count(*) from test1; select count(name) from test1;
这篇关于MySQL数据库之——高级[进阶]SQL语句(二)VIEW视图、联集和常见计算的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-24MySQL集群部署资料:新手入门教程
- 2024-12-24MySQL集群资料详解:新手入门教程
- 2024-12-24MySQL集群部署入门教程
- 2024-12-24部署MySQL集群学习:新手入门教程
- 2024-12-24部署MySQL集群入门:一步一步搭建指南
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
- 2024-12-07MySQL慢查询入门:快速掌握性能优化技巧