目 录
一、MySQL进阶查询
①---- 别名 ----栏位別名 表格別名
语法:SELECT “表格別名”.“栏位1” [AS] “栏位別名” FROM “表格名” [AS] “表格別名”;
SELECT A.Store_Name Store, SUM(A.Sales) “Total Sales” FROM Store_Info A GROUP BY A.Store_Name;
②---- 连接查询 ----
inner join(等值相连):只返回两个表中联结字段相等的行
left join(左联接):返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接):返回包括右表中的所有记录和左表中联结字段相等的记录
SELECT * FROM Localtion A INNER JOIN Store_Info B on A.Store_Name = B.Store_Name ;
SELECT * FROM Localtion A LEFT JOIN Store_Info B on A.Store_Name = B.Store_Name ;
SELECT * FROM Localtion A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name ;
SELECT * FROM Localtion A, Store_Info B WHERE A.Store_Name = B.Store_Name;
SELECT A.Region REGION, SUM(B.Sales) SALES FROM Localtion A, Store_Info B WHERE A.Store_Name = B.Store_Name GROUP BY REGION;
③---- CREATE VIEW----视图,可以被当作是虚拟表或存储查询。
-
视图跟表格的不同是,表格中有实际储存资料,而视图是建立在表格之上的一个架构,它本身并不实际储存资料。临时表在用户退出或同数据库的连接断开后就自动消失了,而视图不会消失。
-
视图不含有数据,只存储它的定义,它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询,而且还要进行统计排序等操作,写sgL语句会很麻烦的,用视图将几个表联结起来,然后对这个视图进行查询操作,就和对一个表查询一样,很方便。
语法:CREATE VIEW "视图表名” AS “SELECT语句” ;
CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION, SUM(B.Sales)SALES FROM Localtion A INNEER JOIN Store_Info B ON A.Store_Name = B.store_Name GROUP BY REGION;
SELECT *FROM V_REGION_SALES;
DROP VIEW V_REGION_SALES;
④---- UNION----联集,将两个SQL语句的结果合并起来,两个SQL语句所产生的栏位需要是同样的资料种类
UNION :生成结果的资料值将没有重复,且按照字段的顺序进行排序
语法: [ SELECT 语句 1 ] UNION [SELECT 语句 2 ];
UNION ALL :将生成结果的资料值都列出来,无论有无重复语法:[SELECT语句1 ] UNION ALL[SELECT语句2];
SELECT Store_Name FROM Localtion UNION SELECT Store_Name FROM Store_Info;
SELECT Store_Name FROM Localtion UNION ALL SELECT Store_Name FROM Store_Info;
⑤----交集值----
#取两个SQL语句结果的交集#
SELECT A.Store_Name FROM Localtion A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;
SELECT A.Store_Name FROM Localtion A INNER JOIN Store_Info B USING(Store_Name);
#两表没用单独重复的行,并且确实有交集的时候用
SELECT A.Store_Name FROM(SELECT Store_Name FROM Localtion UNION ALL SELECT Store_Name FROM Store_Info) A GROUP BY A.store_Name HAVING COUNT(*) > 1;
#取两个SQL语句结果的交集,且没有重复#
SELECT A.Store_Name FROM(SELECT B.Store_Name FROM Localtion B INNER JOIN Store_Info C ON B.Store_Name = C.Store_Name) A GROUP BY A.Store_Name;
SELECT DISTINCT A.Store_Name FROM Localtion A INNER JOIN Store_Info B USING(Store_Name);
SELECT DISTINCT Store_Name FROM Localtion WHERE (Store_Mame) IN (SBLECT Store_Name FROM Store_Info);
SELECT DISTINCT A.Store_Name FROM Localtion A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NOT NULL;
⑥----无交集值----
#显示第一个SQL语句的结果,且与第二个SQL语句没有交集的结果,且没有重复#
SELECT DISTINCT Store_Name FROM Localtion WHERE(Store_Name) NOT IN (SELECT Store_Name FROM Store_Info) ;
SELECT DISTINCT A.Store_Name FROM Localtion A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.store_Name IS NULL;
⑦---- CASE ----是SQL用来做为IF一THEN一ELSE之类逻辑的关键字语法:
SELECT CASE(“栏位名”)
WHEN “条件1” THEN “结果1” WHEN "条件2” THEN “结果2” “…
[ELSE “结果N”] END
FROM"表名”;
#"条件”可以是一个数值或是公式。ELSE子句则并不是必须的。#
SELECT Store_Name, CASE Store_Name
WHEN 'Los Angeles' THEN Sales * 2
WHEN 'Boston' THEN Sales * 1.5
ELSE Sales
END
"New Sales",Date
FROM Store_Info;
#"New Sales”是用于CASE那个栏位的栏位名。
⑧----算排名----
表格准备:
CREATE TABLE Total_Sales (Name char(10),Sales int(5));
INSERT INTO Total_Sales VALUES ('zhangsan',10);
INSERT INTO Total_Sales VALUES ('lisi',15);
INSERT INTO Total_Sales VALUES ('wangwu',20);
INSERT INTO Total_Sales VALUES ('zhaoliu',40);
INSERT INTO Total_Sales VALUES ('sunqi',50);
INSERT INTO Total_Sales VALUES ('zhouba',20);
INSERT INTO Total_Sales VALUES ('wujiu',30);
表格自我连结(Self Join),然后将结果依序列出,算出每一行之前〈包含那一行本身)有多少行数
SELECT A1.Name,A1.Sales,COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales 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栏位都相同的数量,比如zhangsan为6+1=7
⑨----算中位数----
SELECT Sales Middle FROM (SELECT A1.Name, A1.Sales, COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales 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中算出商的方式
⑩----算累积总计----
表格自我连结(self Join),然后将结果依序列出,算出每一行之前〈包含那一行本身)的总合
SELECT A1.Name,A1.Sales,SUM(A2.sales) Sum_Total FROM Total_Sales A1,Total_Sales 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;
⑩----算总合百分比 ----
SELECT A1.Name,A1.Sales,round(A1.Sales/(SELECT SUM(Sales)FROM Total_Sales)*100,2) || '%' Per_Total _Total FROM Total_Sales A1,Total_Sales A2 WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Namme = A2.Name) GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;
#SELECT SUM(sales)FROM Total_sales这一段子查询是用来算出总合
#总合算出后,我们就能够将每一行一一除以总合来求出每一行的总合百分比