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

本章目录

 

 

 

 

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

 

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

 

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

 

  • 方法④:使用左连接(也可用右连接)+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;<br>


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


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

 

 

 

 

 

-

 

上一篇:flume安装配置


下一篇:【100个 Unity小知识点】☀️ | Unity 中的原始预制体 和 预制体变体 的区别和作用