MySQL高階SQL陳述句（學會這些，讓你對于資料庫游刃有余）-有解無憂

文章目錄

環境準備：

use gcc;
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

select * from location;
location 表格如下:
+--------+-------------+
| Region | Store_Name  |
+--------+-------------+
| East   | Boston      |
| East   | New York    |
| West   | Los Angeles |
| West   | Houston     |
+--------+-------------+
4 rows in set (0.00 sec)


create table Store_Info (Store_Name char(20),Sales int(10),Date char(10));
insert into Store_Info values('Los Angeles','1500','2020-12-05');
insert into Store_Info values('Houston','250','2020-12-07');
insert into Store_Info values('Los Angeles','300','2020-12-08');
insert into Store_Info values('Boston','700','2020-12-08');

 select * from Store_Info;
 +-------------+-------+------------+
| Store_Name  | Sales | Date       |
+-------------+-------+------------+
| Los Angeles |  1500 | 2020-12-05 |
| Houston     |   250 | 2020-12-07 |
| Los Angeles |   300 | 2020-12-08 |
| Boston      |   700 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)

-----SELECT -------顯示表格中一個或數個欄位的所有資料
語法: SELECT "欄位" FROM "表名";
SELECT Store_Name FROM Store_Info;

---- DISTINCT ----不顯示重復的資料
語法: SELECT DISTINCT "欄位" FROM "表名";
SELECT DISTINCT Store_Name FROM Store_Info;

-----WHERE-------有條件查詢
語法: SELECT "欄位" FROM "表名" WHERE "條件";
SELECT Store_Name FROM Store_Info WHERE Sales > 1000;

-----AND OR -----且 或
語法: SELECT "欄位" FROM "表名" WHERE "條件1" （[AND|OR] "條件2"）+ ;
SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200);

----IN-----------顯示已知的值的資料
語法: SELECT "欄位" FROM "表名" WHERE "欄位" IN ('值1','值2',...);
SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston');

----BETWEEN------顯示兩個值范圍內的資料
語法: SELECT "欄位" FROM "表名" WHERE "欄位" BETWEEN '值1' AND '值2';
SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';

------通配符-------通常通配符都是跟 LIKE 一起使用的
% : 百分號表示零個、一個或多個字符
_ : 下劃線表示單個字符
'A_Z': 所有以'A'起頭，另一個任何值的字符，且以'Z'為結尾的字串，例如，'ABZ' 和'A2Z'都符合這一個模式，而'AKKZ' 并不符合
'ABC%': 所有以'ABC' 開頭的字串， 例如，'ABCD' 和'ABCABC' 都符合這個模式，
'%XYZ': 所有以'XYZ' 結尾的字串， 例如，'WXYZ' 和'ZZXYZ' 都符合這個模式，
'%AN%': 所有含有'AN'這個模式的字串，例如，'LOS ANGELES'和'SAN FRANCISCO' 都符合這個模式，
'_AN%': 所有第二個字母為'A' 和第三個字母為'N' 的字串，例如，'SAN FRANCISCO' 符合這個模式，而'LOS ANGELES'則不符合這個模式，

------LIKE----匹配一個模式來找出我們要的資料
語法: SELECT "欄位” FROM "表名" WHERE "欄位" LIKE {模式};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';

---- ORDER BY -----按關鍵字排序
語法: SELECT "欄位" FROM "表名" [WHERE "條件"] ORDER BY "欄位" [ASC，DESC];
#ASC是按照升序進行排序的，是默認的排序方式，
#DESC是按降序方式進行排序，
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC;

------------------------函式-------------------------
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(-1),rand(),mod(5,3),power(2,3),round(1.89);
SELECT round(1.8937,3),truncate(1.253,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,3.3);

2、聚合函式：

函式	作用
avg()	回傳指定列的平均值
count()	回傳指定列中非NULL值的個數
min()	回傳指定列的最小值
max()	回傳指定列的最大值
sum(x)	回傳指定列的所有值之和

舉例：

SELECT avg(Sales) FROM Store_Info;

SELECT count(Store_Name) FROM Store_Info;
SELECT count(DISTINCT Store_Name) FROM Store_Info;   #DISTINCT為不顯示重復資訊
#count(*) 包括了所有的列的行數，在統計結果的時候，不會忽略列值為NULL（空）的行；
#count(列名) 只包括列名那一行的行數，在統計結果的時候，會忽略列值為NULL（空）的行，

SELECT max(Sales) FROM Store_Info;
SELECT min(Sales) FROM Store_Info;
SELECT sum(Sales) FROM Store_Info;

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反轉

舉例：

SELECT concat(Region,Store_Name) FROM location WHERE Store_Name ='Boston';
結果如下：
+---------------------------+
| concat(Region,Store_Name) |
+---------------------------+
| EastBoston                |
+---------------------------+
1 row in set (0.00 sec)


#如sql_mode開啟 開啟了PIPES_AS_CONCAT(該模塊在/etc/my.cnf中查看是否開啟)，"||"視為字串的連接運算子而非或運算子，和字串的拼接函式Concat相類似，這和Oracle資料庫使用方法一樣
SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';
結果如下：
+-----------------------------+
| Region || ' ' || Store_Name |
+-----------------------------+
| East Boston                 |
+-----------------------------+
1 row in set (0.00 sec)


SELECT substr(Store_Name,3) FROM location WHERE Store_Name = 'Los Angeles';
SELECT substr(Store_Name,2,4) FROM location WHERE Store_Name = 'New York';

SELECT TRIM ([位置] [要移除的字串] FROM 字串);
#[位置]: 值可以為 LEADING (起頭)，TRAILING (結尾)，BOTH (起頭及結尾)，
#[要移除的字串]: 從字串的起頭、結尾或起頭及結尾移除的字串，預設時為空格，
SELECT TRIM(LEADING 'Ne' FROM 'New York');

SELECT Region,length(Store_Name) FROM location;
SELECT REPLACE (Region,'ast','astern') FROM location;

------GROUP BY------對GROUP BY后面的欄位的查詢結果進行匯總分組，通常是結合聚合函式一起使用的
GROUP BY 有一個原則，就是 SELECT 后面的所有列中，沒有使用聚合函式的列，必須出現在GROUPBY后面，

語法: SELECT "欄位1",SUM("欄位2") FROM "表名" GROUP BY "欄位1";
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name ORDER BY sales desc;

------- HAVING ----用來過濾由 GROUP BY 陳述句回傳的記錄集，通常與 GROUP BY 陳述句聯合使用
HAVING 陳述句的存在彌補了 WHERE 關鍵字不能與聚合函式聯合使用的不足，如果被 SELECT 的只有函式欄，那就不需要GROUP BY子句，
語法: SELECT "欄位1",SUM("欄位2") FROM "表名" GROUP BY "欄位1" HAVING (函式條件);
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM(Sales) > 1500;

---- ----別名---------欄位別名  表格別名
語法: SELECT "表格別名"."欄位1" [AS] "欄位別名" FROM "表格名" [AS] "表格別名";
SELECT A.Store_Name Store,SUM(A.Sales) "Total Sales" FROM Store_Info A GROUP BY A.Store_Name;

---------子查詢--------連接表格，在WHERE子句或HAVING 子句中插入另一個SQL陳述句
語法: SELECT "欄位1" FROM "表格1" WHERE "欄位2” [比較運算子]    #外查詢
(SELECT "欄位1" FROM "表格2" WHERE "條件");     #內查詢

#可以是符號的運算子，例如=、>、<、>=、<= ;也可以是文字的運算子，例如 LIKE、IN、BETWEEN
SELECT SUM(Sales) FROM Store_Info WHERE Store_Name IN
(SELECT Store_Name FROM location WHERE Region = 'West');

SELECT SUM(A.Sales) FROM Store_Info A WHERE A.Store_Name IN
(SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name);

SELECT Store_Name,SUM(Sales),COUNT(Sales) FROM Store_Info GROUP BY Store_Name ORDER BY Sales;
+-------------+------------+--------------+
| Store_Name  | SUM(Sales) | COUNT(Sales) |
+-------------+------------+--------------+
| Houston     |        250 |            1 |
| Boston      |        700 |            1 |
| Los Angeles |       1800 |            2 |
+-------------+------------+--------------+
3 rows in set (0.01 sec)

------- EXISTS ------用來測驗內查詢有沒有產生任何結果，類似布林值是否為真
#如果有的話，系統就會執行外查詢中的SQL陳述句，若是沒有的話，那整個SQL陳述句就不會產生任何結果，
語法: SELECT "欄位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "條件");
SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');

SELECT SUM(Sales) FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston');
+------------+
| SUM(Sales) |
+------------+
|       2050 |
+------------+
1 row in set (0.00 sec)

--------------連接查詢----------------
location 表格如下:
+--------+-------------+
| Region | Store_Name  |
+--------+-------------+
| East   | Boston      |
| East   | New York    |
| West   | Los Angeles |
| West   | Houston     |
+--------+-------------+
4 rows in set (0.00 sec)

UPDATE Store_Info SET store_name='Washington' WHERE sales=300;
Store_Info 表格如下：
mysql> select * from Store_Info;
+-------------+-------+------------+
| Store_Name  | Sales | Date       |
+-------------+-------+------------+
| Los Angeles |  1500 | 2020-12-05 |
| Houston     |   250 | 2020-12-07 |
| Washington  |   300 | 2020-12-08 |
| Boston      |   700 | 2020-12-08 |
+-------------+-------+------------+
4 rows in set (0.00 sec)

inner join(等值相連): 只回傳兩個表中聯結欄位相等的行
left join(左聯接): 回傳包括左表中的所有記錄和右表中聯結欄位相等的記錄
right join(右聯接): 回傳包括右表中的所有記錄和左表中聯結欄位相等的記錄

SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.Store_Name;

SELECT * FROM location A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name;

SELECT * FROM location A,Store_Info B WHERE A.Store_Name = B.Store_Name;

SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A,Store_Info B WHERE A.Store_Name = B.Store_Name GROUP BY REGION;

-------------CREATE VIEW---------視圖，可以被當作是虛擬表或存盤查詢
視圖跟表格的不同是，表格中有實際儲存資料，而視圖是建立在表格之上的一個架構，它本身并不實際儲存資料，
臨時表在用戶退出或同資料庫的連接斷開后就自動消失了，而視圖不會消失，
視圖不含有資料，只存盤它的定義，它的用途一般可以簡化復雜的查詢，比如你要對幾個表進行連接查詢，而且還要進行統計排序等操作，寫的SQL陳述句會很麻煩的，用視圖將幾個表聯結起來，然后對這個視圖進行查詢操作，就和對一個表查詢一樣，很方便，

語法: CREATE VIEW "視圖表名" AS "SELECT 陳述句";
CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A INNER 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 location UNION SELECT Store_Name FROM Store_Info;
SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM Store_Info;

-------------交集值------- 取兩個SQL陳述句結果的交集
SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;

SELECT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);

SELECT A.Store_Name FROM
(SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM Store_Info) A
GROUP BY A.Store_Name HAVING COUNT(*) > 1;

#取兩個SQL陳述句結果的交集，且沒有重復
SELECT A.Store_Name FROM (SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name) A
GROUP BY A.Store_Name HAVING COUNT(*) >= 1;

SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name); _Name);

--------------無交集值-------顯示第一個SQL陳述句的結果，且與第二個SQL陳述句沒有交集的結果，且沒有重復
SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) NOT IN (SELECT Store_Name FROM Store_Info);

SELECT DISTINCT A.Store_Name FROM location 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);

Total_Sales 表格如下:
mysql> select * from Total_Sales;
+----------+-------+
| Name     | Sales |
+----------+-------+
| zhangsan |    10 |
| lisi     |    15 |
| wangwu   |    20 |
| zhaoliu  |    40 |
| sunqi    |    50 |
| zhouba   |    20 |
| wujiu    |    30 |
+----------+-------+
7 rows in set (0.00 sec)

-------算排名 ------表格自我連結 (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為5+1=6

----------算中位數 ------------
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,A1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Per_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 SUM(Sales) FROM Total_sales 這一段子查詢是用來算出總合
#總合算出后，我們就能夠將每一行一一除以總合來求出每一行的總合百分比

--------算累積總合百分比--------------
SELECT A1.Name,A1.Sales,SUM(A2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Per_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;

#用累積總計SUM(a2.sales) 除以總合來求出每一行的累積總合百分比.

SELECT A1.Name,A1.sales,TRUNCATE(ROUND(SUM(A2.Sales)/(SELECT SUM(Sales) FROM Total_Sales),4)*100,2) || '%' Per_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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/255599.html

標籤：其他

上一篇：MySQL資料庫的預處理（prepared statement）性能測驗

下一篇：MyCat組態檔詳解（三）：rule.xml&sequence 組態檔！