MYSQL高阶语句
一、SQL高级语句
1.1 SELECT
显示表格中一个或数个栏位的所有资料
语法：SELECT "栏位" FROM "表名";
1.2 DISTINCT
不显示重复的资料
语法：SELECT DISTINCT "栏位" FROM "表名";
SELECT DISTINCT Store_Name FROM Store_Info;
1.3 WHERE
有条件查询
语法：SELECT "栏位" FROM "表名" WHERE "条件";
1.4 AND OR
代表且、或、和的意思
语法：SELECT "栏位" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
1.5 IN ----显示已知的值的资料
语法：SELECT "栏位" FROM "表名" WHERE "栏位" IN ('值1', '值2', ...);
1.6 BETWEEN
显示两个值范围内的资料
语法：SELECT "栏位" FROM "表名" WHERE "栏位" BETWEEN '值1' AND '值2';
1.7 通配符
通常通配符都是跟 LIKE 一起使用的
通配符 代表的意思
% 百分号表示零个、一个或多个字符（相当于linux中的“*”号）
_ 下划线表示单个字符（相当于linux中的“?”号）
‘A_Z’ 所有以 ‘A’ 起头，另一个任何值的字符，且以 ‘Z’ 为结尾的字符串。例如，‘ABZ’ 和 ‘A2Z’ 都符合这一个模式，而 ‘AKKZ’ 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
‘ABC%’ 所有以 ‘ABC’ 起头的字符串。例如，‘ABCD’ 和 ‘ABCABC’ 都符合这个模式。
‘%XYZ’ 所有以 ‘XYZ’ 结尾的字符串。例如，‘WXYZ’ 和 ‘ZZXYZ’ 都符合这个模式。
‘%AN%’ 所有含有 'AN’这个模式的字符串。例如，‘LOS ANGELES’ 和 ‘SAN FRANCISCO’ 都符合这个模式。
‘_AN%’ 所有第二个字母为 ‘A’ 和第三个字母为 ‘N’ 的字符串。例如，‘SAN FRANCISCO’ 符合这个模式，而 ‘LOS ANGELES’ 则不符合这个模式。
1.8 LIKE
匹配一个模式来找出我们要的资料
语法：SELECT "栏位" FROM "表名" WHERE "栏位" LIKE {模式};
1.9 ORDER BY
按关键字排序
语法：SELECT "栏位" FROM "表名" [WHERE "条件"] ORDER BY "栏位" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。
二、SQL函数
2.1 数学函数
数学函数 返回的结果
abs(x) 返回 x 的绝对值
rand() 返回 0 到 1 的随机数
mod(x,y) 返回 x 除以 y 以后的余数
power(x,y) 返回 x 的 y 次方
round(x) 返回离 x 最近的整数
round(x,y) 保留 x 的 y 位小数四舍五入后的值
sqrt(x) 返回 x 的平方根
truncate(x,y) 返回数字 x 截断为 y 位小数的值
ceil(x) 返回大于或等于 x 的最小整数
floor(x) 返回小于或等于 x 的最大整数
greatest(x1,x2…) 返回集合中最大的值
least(x1,x2…) 返回集合中最小的值
select abs(-10),rand(),mod(10,3),power(2,10),round(3.1415);
select round(3.1415926,3),sqrt(9),truncate(3.1415926,3),ceil(3.5),floor(1.11),greatest(1,2,3,4),least(1,2,3,4);
2.2 聚合函数
数学函数 返回的结果
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
2.3 字符串函数
数学函数 返回的结果
trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x) 将字符串 x 反转
2.4 GROUP BY
对GROUP BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的 GROUP BY 有一个原则，就是 SELECT
后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。
2.5 HAVING
用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。
语法：SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件);
2.6 别名
栏位別名 表格別名
语法：SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
三、VIEW（视图）
3.1 概念
可以被当作是虚拟表或存储查询
视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
3.2 创建、查看和删除视图
建立两张表，字段数据如下，后面测试用：
CREATE VIEW "视图表名" AS "SELECT 语句"; #创建视图表
SELECT * FROM 'V_NAME_VALUE'; #查看视图表
DROP VIEW V_NAME_VALUE; #删除视图表
实例：
CREATE VIEW V_NAME_VALUE AS SELECT A.name NAME,SUM(B.value) VALUE FROM club A INNER JOIN football B ON A.name = B.name GROUP BY NAME;
四、联集
将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类
4.1 UNION
生成结果的资料值将没有重复，且按照字段的顺序进行排序
语法：[SELECT 语句 1] UNION [SELECT 语句 2];
示例：
SELECT name FROM club UNION SELECT name FROM football;
4.2 UNION ALL
将生成结果的资料值都列出来，无论有无重复
语法：[SELECT 语句 1] UNION ALL [SELECT 语句 2];
示例：
SELECT name FROM club UNION ALL SELECT name FROM football;
五、交集值
取两个SQL语句结果的交集
SELECT A.name FROM club A INNER JOIN football B ON A.name = B.name;
SELECT A.name FROM club A INNER JOIN football B USING(name);
示例：
5.1 两表没有单独重复的行，并且确实有交集的时候用
SELECT A.name FROM (SELECT name FROM club UNION ALL SELECT name FROM football ) A GROUP BY A.name HAVING COUNT(*) > 1;
这里的A表示派生表
示例：
5.2 取两个SQL语句结果的交集，且没有重复
SELECT A.name FROM (SELECT B.name FROM club B INNER JOIN football C ON B.name = C.name ) A GROUP BY A.name;
SELECT DISTINCT A.name FROM club A INNER JOIN football B USING(name);
SELECT DISTINCT name FROM club WHERE (name) IN (SELECT name FROM football);
SELECT DISTINCT A.name FROM club A LEFT JOIN football B USING(name) WHERE B.name IS NOT NULL;
示例：
六、无交集值
显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复
SELECT DISTINCT name FROM club WHERE (name) NOT IN (SELECT name FROM football);
实例：
七、CASE的用法
是SQL用来作为IF-THEN-ELSE之类逻辑的关键字
语法格式：
SELECT CASE （字段名）
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
……
ELSE "结果N"
END
FROM "表名"
#条件可以是一个数值或是公式。ELSE子句不是必须的。
示例：
select case area #选择area字段
when 'America' then value + 10000000 #如果球员是America的就加一千万
when 'Europe' then value + 10000000 #如果球员是Europe的就减一千万
else value / 2 #其他情况除2
end #结束此case
result,name,area #别名为result，显示name和area字段
from football; #以上操作对于football表进行
八、排名的计算
表格自我连接（self join），然后将结果依序列出，算出每一行之前（包括那一行本身）有多少行数
示例：
select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;
原理解释：
字段中的每个值跟所有的值进行比较，最大的值跟所有值比较后，大于或等于的只有一个，排名第二的值跟所有的值比较后，大于或等于的有两个，以此类推。
九、中位数的计算
## 求出中位数，显示姓名，value和排名
select * from (select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from football);
## 求出中位数，仅显示value值
select value mid from (select A1.name,A1.value,count(A2.value) rank from football A1,football A2 where A1.value < A2.value OR (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from football);
示例：
十、累积总计的计算
同字段后面中的值都是前面所有值累计而成的
例：
## 计算value的累积总计
select A1.*,sum(A2.value) sum_socore from football A1,football A2 where A1.value < A2.value or(A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;
十一、总合百分比的计算
每个值占总和的比例
例：
## 计算每位球员value值占整个value值的比例
select A1.*,A1.value/(select sum(value) from football) z_sum from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name;
十二、累积总合百分比的计算
同字段后面中的值对应的百分比都是前面所有值的百分比累计而成的
示例：
##计算value的累积总计百分比
select A1.name,A1.value,sum(A2.value),sum(A2.value)/(select sum(value) from football) Z from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;
##计算value的累积总计百分比并用%表示出来
select A1.name,A1.value,sum(A2.value),TRUNCATE(sum(A2.value)/(select sum(value) from football),2) ||'%' Z from football A1,football A2 where A1.value < A2.value or (A1.value=A2.value and A1.name=A2.name) group by A1.name order by A1.value desc;
十三、空值（NULL）和无值（“”）的区别
无值的长度为0，不占用空间；而空值null 的长度是null，是占用空间的；
IS NULL或者IS NOT NULL，是用来判断字段是不是NULL或者不是NULL，是不能查出是不是无值的；
无值的判断使用=’‘或者<>’'来处理。<>代表不等于；
在通过count()指定字段统计又多少行数时，如果遇到NULL值会自动忽略掉，遇到空值会自动加入记录中进行计算。
## 创建表
create table SITE(site varchar(20));
insert into SITE values('jiangsu');
insert into SITE values('anhui');
insert into SITE values('');
insert into SITE values('shandong');
insert into SITE values('');
insert into SITE values();
insert into SITE values('heilongjiang');
## 测试
select length(site) from SITE;
select * from SITE where site is NULL;
select * from SITE where site is not NULL;
select * from SITE where site ='';
select * from SITE where site <> '';
1）新建SITE表
2）测试
