Blog.064 MySQL 高级 SQL 语句② VIEW 视图、联集与常见计算

2022-11-13 17:00:19

本章目录

1. VIEW（视图）
2. 联集
3. 交集值
4. 无交集值
5. CASE
6. 排名的计算
7. 中位数的计算
8. 累积总计的计算
9. 总合百分比的计算
10. 空值（NULL）和无值（“”）的区别

1. VIEW（视图）

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

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

2. 联集

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

　　　　（1）UNION

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

　　　　（2）UNION ALL

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

3. 交集值

　　　　取两个SQL语句结果的交集。

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

　　　　（2）方法②：union all结合group by

两表其中的一个表没有指定的行，而另一个表这个行有重复不可用，要求两个表确实有交集的时候用

　　　　（3）取交集（去重）：取两个SQL语句结果的交集，且没有重复

方法①：内连接取交集结合group by去重
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;

方法②：内连接取交集结合distinct去重
select distinct A.name from test1 A inner join test6 B using(name);

方法③：where+in遍历取交集并结合distinct去重
select distinct name from test1 where name in (select name from test6);

方法④：使用左连接（也可用右连接）+where 判断NOT NULL 取交集并结合distinct去重
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);

4. 无交集值

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

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

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;

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

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

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

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;

5. CASE

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

语法：

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

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

6. 排名的计算

　　　　表格自我连接（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;

7. 中位数的计算

　　　　每个派生表必须有自己的别名，所以别名A3必须有。
　　　　DIV是在MySQL中算出商的方式。

　　　　（1）方法①：使用派生表

派生表为计算排名的sql语句，先进行排名，排名好之后取中位数。
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);

　　　　（2）方法②：使用视图表

视图表为计算排名的sql语句，先进行排名，排名好之后取中位数。
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);

8. 累积总计的计算

表格自我连接（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;

9. 总合百分比的计算

　　　　每个值占总和的比例。

第一步：算出所占总合比（小数形式）
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;

10. 空值（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;

码农公寓

相关文章