Mysql数据库中的高阶语句（中）

一，VIEW（视图）

1.1概念
可以被当作是虚拟的表，或者存储查询。

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

1.2创建、查看和删除视图
建立两张测试表

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

实例：

二、联集

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

2.1 UNION

生成结果的资料值将没有重复，且按照字段的顺序进行排序

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

示例：
SELECT age FROM list UNION SELECT age FROM list2;

三、交集值

取两个SQL语句结果的交集

SELECT A.name FROM list A INNER JOIN list2 B ON A.name = B.name;
SELECT A.name FROM list A INNER JOIN list2 B USING(name);


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

SELECT A.name FROM (SELECT name FROM list UNION ALL SELECT name FROM list2 ) A GROUP BY A.name HAVING COUNT(*) > 1;
这里的A表示派生表

3.2 取两个SQL语句结果的交集，且没有重复

SELECT A.name FROM (SELECT B.name FROM list B INNER JOIN list2 C ON B.name = C.name ) A GROUP BY A.name;
SELECT DISTINCT A.name FROM list A INNER JOIN list2 B USING(name);
SELECT DISTINCT name FROM list WHERE (name) IN (SELECT name FROM list2);
SELECT DISTINCT A.name FROM list A LEFT JOIN list2 B USING(name) WHERE B.name IS NOT NULL;

四，无交集值

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

SELECT DISTINCT name FROM list WHERE (name) NOT IN (SELECT name FROM list2);

五、CASE的用法

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

语法格式：

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

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

示例：

select case sex #选择sex字段
when ‘男’ then age + 10 #如果是20岁的就加10
when ‘女’ then age - 10 #如果是18岁的就减10
else value / 2 #其他情况除2
end #结束此case
result,name,age #别名为result，显示name和age字段
from list; #以上操作对于list表进行

六，排名的计算

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

示例：
select A1.name,A1.age,count(A2.age) rank from list A1,list 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 list A1,list 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 list);

求出中位数，仅显示value值

select age mid from (select A1.name,A1.age,count(A2.age) rank from list A1,list 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 list);

八、累积总计的计算

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

例：

计算value的累积总计

select A1.*,sum(A2.age) sum_socore from list A1,list 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;

九、总合百分比的计算

每个值占总和的比例

例：

计算每位球员value值占整个value值的比例

select A1.*,A1.age/(select sum(age) from list) z_sum from list A1,list A2 where A1.age < A2.age or (A1.age=A2.age and A1.name=A2.name) group by A1.name;

十、累积总合百分比的计算

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

##计算value的累积总计百分比并用%表示出来
select A1.name,A1.age,sum(A2.age),TRUNCATE(sum(A2.age)/(select sum(age) from list),2) ||’%’ Z from list A1,list 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;

##计算value的累积总计百分比并用%表示出来
select A1.name,A1.age,sum(A2.age),TRUNCATE(sum(A2.age)/(select sum(age) from list),2) ||’%’ Z from liat A1,list 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）和无值（“”）的区别

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

insert into num values(‘100’);
insert into num values(‘200’);
insert into num values(‘300’);
insert into num values(’’);
insert into num values(‘400’);
insert into num values(’’);
insert into num values(‘500’);
insert into num values();
insert into num values(‘600’);

查看空值和null的区别