Mysql数据库的高级语句-VIEW视图、联集和常见计算

VIEW（视图）

概念

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

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

创建、查看和删除视图

CREATE VIEW "视图表名" AS "SELECT 语句";              #创建视图表
SELECT * FROM `V_NAME_age`;                         #查看视图表
DROP VIEW V_NAME_age;                               #删除视图表

UNION ALL

将生成结果的资料值都列出来，无论有无重复

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

交集值

取两个SQL语句结果的交集

方法1：distinct inner join on.../using(...)

select distinct 相同字段 from 表格1 inner join 表格2 on 表格1.相同字段=表格2.相同字段   #可以用别名代替

SELECT A.name FROM mation A INNER JOIN team B ON A.name = B.name;    
SELECT A.name FROM mation A INNER JOIN team B USING(name);

方法2：union all + group by + having count(../*) > 1

两表没有单独重复的行，并且确实有交集的时候用

select 别名.相同字段 from  
(select 相同字段 from 表格1 union all select 相同字段 from 表格2) 别名 group by 别名.相同字段 having count(*) > 1;

我们进行这样的拆分：
select 相同字段 from 表格1 union all select 相同字段 from 表格2;
用group by进行分组汇总：select 别名.相同字段 from （select 相同字段 from 表格1 union all select 相同字段 from 表格2）别名 group by 别名.相同字段; 
再加having条件语句判断：如果大于1，表示两张表内的相同字段出现过多次，则是交集

SELECT A.name FROM (SELECT name FROM mation UNION ALL SELECT name FROM team ) A GROUP BY A.name HAVING COUNT(*) > 1;

方法三：外查询 in （内查询 另一张表查询的结果）

select distinct 相同字段 from 表格1 where 相同字段 in （select 相同字段 from 表格2）;

方法四：left join where ... is not null

select distinct 别名1.相同字段 from 表格1 别名1 left join 表格2 别名2 on 表格1.相同字段=表格2.相同字段 where 别名1.相同字段 is not null;

我们可以这样拆分：

先左连接把数据全联集起来：select * from 表格1 别名1 left join 表格2 别名2 on 表格1.相同字段=表格2.相同字段;
再加where语句判断值不为空的就是交集:select * from 表格1 别名1 left join 表格2 别名2 on 表格1.相同字段=表格2.相同字段 where 别名1.相同字段 is not null;
再最后distinct去重

同理用right join 也可以达到

无交集值

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

SELECT DISTINCT name FROM mation WHERE (name) NOT IN (SELECT name FROM team);

CASE的用法

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

语法格式：

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

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

排名的计算

表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数

select A1.name,A1.age,count(A2.age) rank from team A1,team A2 where A1.age < A2.age OR (A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc;

中位数的计算

## 求出中位数，显示姓名，age和排名
select * from (select A1.name,A1.age,count(A2.age) rank from team A1,team A2 where A1.age < A2.age OR (A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from team);

## 求出中位数，仅显示age值
select age mid from (select A1.name,A1.age,count(A2.age) rank from team A1,team A2 where A1.age < A2.age OR (A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from team);

累积总计的计算

同字段后面中的值都是前面所有值累计而成的

## 计算age的累积总计
select A1.*,sum(A2.age) sum_socore from team A1,team A2 where A1.age < A2.age or(A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc;

总合百分比的计算

每个值占总和的比例

## 计算每位球员age值占整个age值的比例
select A1.*,A1.age/(select sum(age) from team) z_sum from team A1,team A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name;

累积总合百分比的计算

同字段后面中的值对应的百分比都是前面所有值的百分比累计而成的

##计算age的累积总计百分比
select A1.name,A1.age,sum(A2.age),sum(A2.age)/(select sum(age) from team) Z from team A1,team A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc;

##计算age的累积总计百分比并用%表示出来
select A1.name,A1.age,sum(A2.age),TRUNCATE(sum(A2.age)/(select sum(age) from team),2) ||'%' Z from team A1,team A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name order by A1.age desc;

空值（NULL）和无值（“”）的区别

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