MySQL高级语言（三）

一.连接查询

inner join(等值相连)：只返回两个表中联结字段相等的行
left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录

select * from Area A INNER JOIN GDP B on A.City_Name = B.City_Name ;

select * from Area A LEFT JOIN GDP B on A.City_Name = B.City_Name ;

select * from Area A RIGHT JOIN GDP B on A.City_Name = B.City_Name ;

select * from Area A, GDP B WHERE A.City_Name = B.City_Name;

select A.Region REGION, SUM(B.GDP) SALES from Area A, GDP B where A.City_Name = B.City_Name group by REGION;

二. CREATEVIEW（视图）

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

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

语法: CREATE VIEW "视图表名”AS “SELECT 语句”;

create view V_REGION AS select A.Region REGION, SUM(B.GDP) SALES from Area A inner join GDP B ON A.City_Name = B.City_Name group by REGION;

select  * from V_REGION;

drop view V_REGION;

三.连集，交集，非交集

1.连集(UNION)

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

UNION :生成结果的资料值将没有重复，且按照字段的顺序进行排序
语法: [SELECT 语句1] UNION [SELECT 语句2];
UNIONALL:将生成结果的资料值都列出来，无论有无重复

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

select City_Name from Area UNION select City_Name from GDP;

select City_Name from Area UNION ALL select City_Name from GDP;

2.交集值

取两个SQL语句结果的交集

select A.City_Name from Area A INNER JOIN GDP B ON A.City_Name = B.City_Name;

#两表没用单独重复的行，并且确实有交集的时候用
select A.City_Name from
(select City_Name from Area UNION ALL select City_Name from GDP) A
group by A.City_Name HAVING COUNT(*) > 1;

#取两个SQL语句结果的交集，且没有重复
select A.City_Name from (select B.City_Name from Area B INNER JOIN GDP C ON B.City_Name =
C.City_Name) A
group by A.City_Name;

select distinct A.City_Name from Area A INNER JOIN Output B USING (City_Name); 

select distinct City_Name from Area WHERE (City_Name) IN (SEIECT City_Name from GDP);

select distinct A.City_Name from Area A LEFT JOIN GDP B USING (City_Name) WHERE B.City_Name IS NOT NULL;

3. 无交集值

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

select distinct City_name from Area where (City_name) not in (select City_name from Output);

select distinct A.City_name from Area A left join Output B using(City_name) where B.City_name is null;

四.、CASE

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

语法:
SELECT CASE (“栏位名”)
WHEN “条件1” THEN “结果1”
WHEN “条件2” THEN "结果2”
[ELSE “结果N”]
END
FROM “表名”;

# "条件”可以是一个数值或是公式。 ELSE子句则并不是必须的。
select City_Name, CASE City_Name
WHEN 'nanjing' THEN Yield * 2
WHEN 'lanzhou' THEN Yield * 1.5
ELSE Yield
END
"New Yield",Date
from Output;
#"New Sales" 是用于CASE那个栏位的栏位名。

五. 数据排序

CREATE TABLE Total (Name char(10),Sales int(5));
INSERT INTO Total VALUES ('zhangsan',100);
INSERT INTO Total VALUES ('lisi',150);
INSERT INTO Total VALUES ('wangwu',200);
INSERT INTO Total VALUES ('zhaoliu',400);
INSERT INTO Total VALUES ('sunqi',500);
INSERT INTO Total VALUES ('zhouba',200);
INSERT INTO Total VALUES ('wujiu',300);

1.算排名

表格自我连结(Self Join)， 然后将结果依序列出，算出每一行之前(包含那一行本身)有多少行数

select A1.Name, A1.Sales,count(A2.Sales) Rank from Total A1,Total A2
WHERE A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name = A2.Name)
group by A1.Name, A1.Sales ORDER BY A1.Sales DESC;
#统计Sales栏位的值是比自己本身的值小的以及sales栏位和Name栏位都相同的数量

2.算中位数

SELECT Sales Middle FROM (SELECT A1.Name, A1.Sales, COUNT(A2.Sales) Rank FROM Total A1, Total A2
WHERE A1.Sales < A2.Sales OR (A1.Sales = A2.Sales AND A1.Name >= A2.Name)
GROUP BY A1.Name, A1.Sales ORDER BY A1.Sales DESC) A3
WHERE A3.Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total Sales);
#每个派生表必须有自己的别名，所以别名A3必须要有
#DIV是在MySQL中算出商的方式

3.算累积总计

表格自我连结(Self Join)，然后将结果依序列出，算出每一行之前(包含那一行本身)的总合

SELECT A1.Name,A1.Sales,SUM(A2.Sales) Sum_Total FROM Total A1, Total A2
WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name, A1.Sales ORDER BY A1.Sales DESC;

4.算累积总合百分比

SELECT A1.Name,A1.Sales,A1.Sales/(SELECT SUM(Sales)  FROM Total) Per_Total
FROM Total A1, Total A2
WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;

用累积总计SUM（A2.Sales）除以总和求出每一行的累计总合百分比

SELECT A1.Name,A1.Sales,A1.Sales,TRUNCATE(ROUND(SUM(A2.Sales)/(SELECT SUM(Sales) FROM Total),4)*100,2) || '%' Per_Total
FROM Total A1, Total A2
WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;

5、空值(NULL)和无值("")的区别

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

6、数据库中的正则表达式

匹配	模式描述	实例
^	匹配文本的开始宁符	'^bd’匹配以 bd开头的字符串
$	匹配文本的结束字符	'qn$'匹配以qn结尾的字符串
.	匹配任何单个字符	's.t’匹配任何s和t之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	'fo*t’匹配t前面有任意个o
+	匹配前面的字符1次或多次	'hom+'匹配以 ho开头,后面至少一个m的字符串
字符串	匹配包含指定的字符串	'clo’匹配含有clo 的字符串
p1lp2	匹配 p1或p2	'bg|fg’匹配bg或者fg
[ …]	匹配字符集合中的任意一个字符	'[abc]匹配a或者b或者c
[^…]	匹配不在括号中的任何字符	'[ ^ab]'匹配不包含a或者b的字符串
{n}	匹配前面的字符串n次	'g{2]'匹配含有2个g的字符串
{n, m}	匹配前面的字符串至少n次,至多m次	'f{1,3}'匹配f最少1次，最多 3 次

语法:SELECT "栏位”FROM "表名”WHERE"栏位”REGEXP{模式};
SELECT * FROM Store_Info WHERE Store_Name REGEXP ‘os’ ;
SELECT * FROM Store_Info WHERE Store_Name REGEXP '^[A-G] ';
SELECT * FROM Store_Info WHERE Store_Name REGEXP ‘HolBo’;