MySQL一些常用高階SQL語句詳解

一、MySQL進階查詢

首先先建立兩張表

mysql -u root -pXXX     #登陸資料庫，XXX為密碼
create database jiangsu;   #新建一個名為jiangsu的資料庫
use jiangsu;   #使用該資料庫
create table location(Region char(20),Store_name char(20));   #建立location表，欄位1為Region，資料型別為char，資料長度為20；欄位2為Store_name，資料型別為char，長度為20.
insert into location values('North','Xuzhou');   #插入4條資料
insert into location values('North','Suqian'); 
insert into location values('South','Nanjing'); 
insert into location values('South','Suzhou'); 
create table store_info(Store_name char(20),Sales int(10),Date char(10));   #再建立一張store_info表
insert into store_info values('Xuzhou',300,'2020-12-08');   #插入4條資料
insert into store_info values('Suqian',249,'2020-12-07');
insert into store_info values('Nanjing',1500,'2020-12-05');
insert into store_info values('Suzhou',700,'2020-12-08');

----------------select-------------------
select用於查詢表格中的一個或多個欄位的資料記錄
語法格式：select 欄位1，欄位2，... from 表名;
select * from location;   #查詢location表中的所有欄位，*表示所有，如果不嫌麻煩，當然也可以將所有欄位都輸進去
select Region from location;   #查詢location表中的Region的資料
select sales,Date from store_info;   #查詢store_info表中的sales,Date欄位的資料  

----------------DISTINCT-------------------
DISTINCT用於將重複的資料壓縮為一個
語法格式：select distinct 欄位名 from 表名；
select distinct Store_name from store_info;   #查詢dtore_info表中的Store_name欄位的資料，如有重複，則只顯示一個

----------------where-------------------
where用於帶條件查詢
語法格式：select 欄位名 from 表名 where 條件語句;
select Store_name from store_info where sales=300;   #查詢store_info表中sales欄位的值等於300的Store_name的資料
select Store_name from store_info where sales>500;   #查詢store_info表中sales欄位的值大於500的Store_name的資料

----------------and   or-------------------
and，且，用於查詢一個資料範圍；or，或，用於查詢包含條件語句的所有資料
語法格式：select 欄位名 from 表名 where 條件1 and/or 條件2;
select Store_name from store_info where sales>250 and sales<1000;   #查詢store_info表中sales欄位的值大於250，且小於1000的Store_name的資料
select Store_name from store_info where sales<250 or sales>1000;   #查詢store_info表中sales欄位的值小於250，或者 大於1000的Store_name的資料
select Store_name from store_info where sales>1000 or (sales >200 and sales < 500);   #括號的優先順序高，所以先根據括號裡的and條件語句進行篩選，然後再根據or進行篩選，最後查詢最終篩選出來的資料；該語句先篩選出sales大於200且小於500的值，再使用or進行或的刪選，最終篩選出來的結果應該是在200到500之間的值或者大於1000的值。
select Store_name from store_info where sales>1000 or sales >200 and sales < 500;   #如果不加括號，and的優先順序是比or要高的，也就是說，當一條條件語句中同時存在and和or（沒有括號），會先執行and條件。

----------------in-------------------
in用來顯示已知值的資料，簡單來說，in後面跟的是一個資料集合，查詢語句會根據資料集合中的值進行篩選查詢。not in 就是取資料集合中的反，不在資料集合中的資料。
語法格式：select 欄位名1 from 表名 where 欄位名2 in ('欄位名2的值1','欄位名2的值2，......');
select * from store_info where Store_name in ('Nanjing','Xuzhou');   #將Nanjing和Xuzhou的所有資訊都查詢出來。
注：in可以用or代替
上述語句等於：select * from store_info where Store_name='Nanjing' or Store_name='Xuzhou';

----------------between...and-------------------
between 值1 and 值2 ,在值1與值2之間(值2 > 值1)，該語句查詢的是一個範圍，包含值1和值2。其作用相在某一方面當於大於等於 ... and 小於等於 ... 。
語法格式：select 欄位名 from 表名 where 欄位名 between 值1 and 值2;
select * from store_info where Date between '2020-12-07' and '2020-12-10';   #查詢store_info表中的Data的值在12-06與12-10之間的所有資料

----------------萬用字元-------------------
萬用字元一般情況下和like一起使用進行模糊查詢，模糊查詢的概念就是將所有符合條件的資料全部查詢出來，而等於號是精確查詢，會直接將具體的某一資料查詢出來
模糊查詢的字元如下：
%：百分號表示0個，1個或多個字元
_:下劃線表示單個字元
語法格式：select 欄位 from 表名 where 欄位 like '萬用字元';
select * from store_info where Date like '2020%';   #將Date的值為2020，後面隨便（2020後有沒有都行）的值全部查詢出來
select * from store_info where Date like '2020-12-0_';   #將2020-12-0，後面只能匹配一個字元（必須存在且只能有一個）的所有資料查詢出來

----------------like-------------------
like，模糊查詢，用於查詢符合條件的所有資料，通常和萬用字元一起使用，語法和萬用字元一樣的，因為是結合使用。
create database name;
use name;
create table stu_name(sname char(10));
insert into stu_name values('張');
insert into stu_name values('張三');
insert into stu_name values('張四');
insert into stu_name values('張無忌');
insert into stu_name values('一張紙');
insert into stu_name values('弓長張');
select * from stu_name where sname like '張%';   #查詢所有張姓的名字，只要姓張就行
select * from stu_name where sname like '%張';   #查詢所有最後一個字是張的姓名，前面無所謂
select * from stu_name where sname like '%張%';   #查詢所有包含張的姓名，張字在姓在名都行
select * from stu_name where sname like '張_';   #查詢所有張姓且只有兩個字的名字
select * from stu_name where sname like '張__';（兩條下劃線）  #查詢所有張姓，且必須為三個字的名字
select * from stu_name where sname like '_張%';  #查詢所有第二個字為張的名字
select * from stu_name where sname like '張_%';   #查詢所有張姓，名字至少包含兩個字的名字
select * from stu_name where sname like '張%_';   #查詢所有張姓，名字至少包含兩個字的名字，該語句和上面的查詢結果一樣，但理解是不同的。

----------------order by-------------------
order by 用於關鍵字的排序
語法格式：select 欄位 from 表名 [where 條件語句] order by 欄位 asc/desc;
asc:按欄位升序，預設為asc
desc：按欄位降序
select Store_name,Date,sales from store_info order by sales;   #按照sales升序排列後，查詢name、date和sales
select Store_name,Date,sales from store_info order by sales desc;   #按照sales降序排列後，查詢name、date和sales

二、MySQL資料庫函數

-------------數學函數-------------------
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(x,y,z,...)   #返回集合中最大的值
least(x,y,z,...)      #返回集合中最小的值
---------------------------------------------
select abs(-1),rand(),mod(5,2),power(2,3),round(1.75);
select round(3.1415926,5),sqrt(2),truncate(3.141592653,4),ceil(5.2),floor(3.2),greatest(1.61,2.54,0.87),least(5.23,8.71,4.13);

--------------聚合函數--------------------
avg()       #返回指定列的平均值
count()     #返回指定列中非空值的個數
min()       #返回指定列的最小值
max()       #返回指定列的最大值
sum(x)      #返回指定列的所有值的和  
------------------------------------------
select avg(sales) from store_info;   #查詢sales的平均值
平均值的另一種方法：
select sum(sales)/(select count(sales) from store_info) from store_info;

select count(Date) from store_info;   #統計Date的資料個數，包括重複的值，但不包括空值

select count(distinct Date) from store_info;   #統計Date的資料個數,重複的資料只統計一次，不包括空值

select count(*) from store_info;   #全部統計，包括空值，count(*)掃描全表

select min(sales) from store_info;   #查詢sales的最小值
最小值的另一種方法：
select sales from store_info order by sales limit 1;

select max(sales) from store_info;   #查詢sales的最大值
最大值的另一種方法：
select sales from store_info order by sales desc limit 1;

select sum(sales) from store_info;   #查詢sales的和

-----------------字串函數--------------------
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='Xuzhou';   #將location表中Store_name='Xuzhou'的Region,Store_name的值拼接在一起

select Region || ' ' || Store_name from location where Store_name='Xuzhou';   #在my.cnf中開啟了PIPES_AS_CONCAT模式後，可以使用 || 代替concat函數，將多個字串拼接在一起

select substr(Store_name,3) from store_info where Store_name='Suqian';   #將Suqian的第3個字元往後的所有字元擷取出來

select substr((select Region || ' ' || Store_name from location where Store_name='Xuzhou'),1,5);   #將上一條拼接的欄位的第1到5個字元擷取出來

select trim([[位置] [要除移的字串] from] 字串);
#位置：leading（開頭），tariling（結尾），both（開頭及結尾）
#要除移的字串：從字串的開頭、結尾，或開頭及結尾除移的字串，預設時為空格
select trim(leading 'Xu' from 'Xuzhou');   #將Xuzhou開頭的Xu去掉

select Region,length(Store_name) from location;   #查詢location表中的Region欄位的值和Store_name的值的長度

select replace(Region,'th','thern') from location;   #將location表中的Region欄位的值包含th的替換為thern，然後返回。

------------------group by-------------------
group by用於對查詢結果進行彙總分組，通常是結合聚合函數
一起使用，group by有一個原則，凡是在group by後面出現的
欄位，必須在select後面出現。凡是在select後面出現、且未在
group by後面出現的欄位，必須出現在group by後面。
語法格式：select 欄位1，sum(欄位2) from 表名 group by 欄位1;

select Store_name,sum(sales) from store_info group by Store_name order by sales;

------------------having----------------------
having用來過濾由group by語句返回的記錄值，通常與group by語句聯合使用。having語句的存在彌補了where關鍵字不能與聚合函數聯合使用的不足。
語法格式：select 欄位1,sum(欄位2) from 表名 group by 欄位1 having (函數條件)；
select Store_name,sum(sales) from  store_info group by Store_name having sum(sales) >1000;

-------------------別名----------------------
別名包括欄位別名和表的別名，當一張表的名字或者表中的某一個欄位名過於冗長時，可以使用別名將之代替，從而降低查詢的失誤率。
語法格式：select 欄位 [AS] 欄位別名 from 表名 [AS] 表格別名；
select Store_name NAME,sales SALE from store_info;

------------------子查詢---------------------
子查詢通常用於連線表格，在where子句或者having子句中插入另一個SQL語句。
語法格式：select 欄位1 from 表格1 where 欄位2 [比較運運算元] (select 欄位2 from 表格2 where 條件語句);括號裡的select語句是內查詢，括號外的select語句是外查詢
select Region from location where Store_name=(select Store_name from store_info where sales=300);   #比較運運算元，可以是=、>、<或者>=、<=

select sales from store_info where Store_name in (select Store_name from  location);   #也可以是in、between...and、like等

 ------------------exists---------------------
 exists用來測試內查詢有沒有產生任何結果，類似布林值
 是否為真。如果內查詢產生了結果，則將結果作為外查詢
 的條件繼續執行，如果沒有結果，那麼整條語句都不會產
 生結果。
 語法格式：select 欄位1 from 表1 where exists (select 欄位2 from 表2 where 條件語句);
 select sum(sales) from store_info where exists (select * from location where Region='North');   #
 #先執行內查詢語句，即在location表中查詢Region為North的所有資料，如果存在，則執行外查詢語句；如果不存在，就不執行外查詢
------------------連線查詢-----------------------
inner join(內連線)：內連線只返回兩個表中欄位的值相等的資料，即兩表的交集
left join(左連線)：返回包括左表中的所有記錄和右表中聯結欄位相等的記錄
right join(右連線)：返回包括右表中的所有記錄和左表中聯結欄位相等的記錄
select * from location left join store_info on location.Store_name=store_info.Store_name;  #左連線

select * from location right join store_info on location.Store_name=store_info.Store_name;   #右連線

select * from location inner join store_info on location.Store_name=store_info.Store_name;   #內連線法1

SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name;   #內連線法2

SELECT Region REGION, SUM(B.Sales) SALES FROM location A, store_info B WHERE A.Store_Name = B.Store_Name GROUP BY Region;   #查詢兩表中name值相等的Region的值和sales的和，並按照Region欄位進行分組，REGION是欄位Region的別名，SALES是sum(sales)的別名，A是表location的別名，B是info表的別名

---------------------view-----------------------
view，檢視，檢視是一張虛擬的表，通常用於儲存多表聯合查詢出來資料，這樣可以極大的減輕SQL語句的複雜度，在執行n張表的聯合查詢時，檢視可以起到很大的便利作用。檢視跟表格的不同是，表格中有實際儲存資料記錄，而檢視是建立在表格之上的一個架構，它本身並不實際儲存資料記錄。而檢視不會因為退出資料庫而消失。
語法格式1：create view 檢視名 as 查詢語句;   #建立檢視
語法格式2：drop view 檢視名;   #刪除檢視
show tables from 庫名;   #該命令不僅可以檢視庫所包含的表，也可以檢視有哪些檢視

------------------------union-------------------------
union，聯集，其作用是將兩個SQL語句的結果合併起來，兩個SQL語句所產生的欄位需要是同樣的資料記錄種類
union ：生成結果的資料記錄值將沒有重複，且按照欄位的順序進行排序
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;

-------------------求交集的幾種方法------------------
存在重複資料：
1.select A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;  
2.select A.Store_name from location A,store_info B where A.Store_name=B.Store_name; 
3.select A.Store_name from location A inner join store_info B using (Store_name);

無重複資料：
1.想要得到無重複資料其實很簡單，在重複資料的查詢語句select後加上distinct去重即可。
select distinct A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;

2.使用子查詢，在內查詢中查詢info中的name欄位，並將之作為外查詢的條件，這樣，外查詢的查詢語句的範圍就只能在內查詢查出的資料中進行。（子查詢實際上就是變相的內查詢）
select distinct Store_name from location where Store_name in (select Store_name from store_info);

3.使用左查詢，將location和info表進行左查詢，會查詢出location表中的所有name欄位的值以及info表中與location表中name的值相等的資料，再使用distinct進行去重
select distinct A.Store_name from location A left join store_info B using(Store_name) where B.Store_name is not null;

4.使用級聯查詢，union all會將兩張表的所有資料都連線到一起，這時只需要通過count()函數將大於1的數值統計出來，即可實現查詢兩表的共同數值。
 select Store_name,count(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(A.Store_name)>1 ;

---------------------無交集---------------------
既然我們可以查詢出有交集的資料，那麼取反就可以實現無交集的查詢了
select distinct Store_name from location  where Store_name not in (select Store_name from store_info);

---------------------case---------------------
case是SQL語句用來做為when-then-else（當...就...否則...）之類邏輯的關鍵字
語法格式：
select case 欄位名
when 條件1 then 結果1
when 條件2 then 條件2
.....
[else 結果n]
end
from 表名;
 
 --------空值(NULL) 和 無值('') 的區別----------
1．無值的長度為 0，不佔用空間的；而 NULL 值的長度是 NULL，是佔用空間的。
2．IS NULL 或者 IS NOT NULL，是用來判斷欄位是不是為 NULL 或者不是 NULL，不能查出是不是無值的。
3．無值的判斷使用=''或者<>''來處理。<> 代表不等於。 
4．在通過 count()指定欄位統計有多少行數時，如果遇到 NULL 值會自動忽略掉，遇到無值會加入到記錄中進行計算。
create table city(name char(10));   #新建city表
insert into city values ('beijing');   #插入三個值
insert into city values ('nanjing');
insert into city values ('xuzhou');
insert into city values ('');   #插入兩個無值
insert into city values ('');
insert into city values (null);   #插入兩個空值
insert into city values (null);
select * from city;   #查詢city表的所有值
select length(name) from city;   #查詢name欄位值的長度
select count(name) from city;   #統計name欄位的值，空值會被忽略
select length('111'),length(null),length('');   #比較有值、空值、無值的長度
select * from city where name is null;   #查詢name為空的值
select * from city where name is not null;   #查詢name欄位不為空的值
select * from city where name = '';   #查詢name值為無值的資料
select * from city where name <> '';   #查詢name欄位不為無值的資料，空值會被忽略

--------------------正規表示式-------------------------
^:匹配文字的開始字元
$:匹配文字的結束字元
.:匹配任何一個字元
*:匹配零個或多個在它前面的字元
+:匹配前面的字元1次或多次
字串:匹配包含指定的字串                     
p1|p2:匹配p1或p2                             
[...]:匹配字元集合中的任意一個字元             
[^...]:匹配不在括號中的任何字元                 
{n}:匹配前面的字串 n 次 
{n,m}:匹配前面的字串至少n次，至多m次
語法格式：select 欄位 from 表名 where 欄位 REGEXP {模式};
select * from city where name regexp 'zhou';   #匹配資料中帶zhou的
select * from city where name regexp 'nan|bei';   #匹配資料中有nan或bei的
select * from city where name regexp '^[xnb]';   #匹配以xnb任一字元開頭的資料 

三、MySQL儲存過程

儲存過程是一組為了完成特定功能的SQL語句集合。

儲存過程在使用過程中是將常用或者複雜的工作預先使用SQL語句寫好並用一個指定的名稱儲存起來，這個過程經編譯和優化後儲存在資料庫伺服器中。當需要使用該儲存過程時，只需要呼叫它即可。儲存過程在執行上比傳統SQL速度更快、執行效率更高。

儲存過程的優點：

1、執行一次後，會將生成的二進位制程式碼駐留緩衝區，提高執行效率

2、SQL語句加上控制語句的集合，靈活性高

3、在伺服器端儲存，使用者端呼叫時，降低網路負載

4、可多次重複被呼叫，可隨時修改，不影響使用者端呼叫

5、可完成所有的資料庫操作，也可控制資料庫的資訊存取許可權

------------------建立儲存過程-----------------------
delimiter ！！  #將語句的結束符號從分號;臨時改為兩個！！(可以是自定義)
create procedure Proc()   #建立儲存過程，過程名為Proc，不帶引數
-> begin   #過程體以關鍵字 BEGIN 開始
-> select * from Store_Info;   #過程體語句
-> end $$   #過程體以關鍵字 END 結束
delimiter ;       #將語句的結束符號恢復為分號

------------------呼叫儲存過程-----------------------------
call Proc;
------------------檢視儲存過程----------------------------
show create procrdure [資料庫.]儲存過程名;       #檢視某個儲存過程的具體資訊
show create procedure proc;
show procedure status [LIKE '%Proc%'] G

----------------儲存過程的引數--------------------
IN 輸入引數：表示呼叫者向過程傳入值（傳入值可以是字面量或變數）
OUT 輸出引數：表示過程向呼叫者傳出值(可以返回多個值)（傳出值只能是變數）
INOUT 輸入輸出引數：既表示呼叫者向過程傳入值，又表示過程向呼叫者傳出值（值只能是變數）

範例：
delimiter !
create procedure proc1(in inname char(10))
-> begin
-> insert into city values(inname);
-> select * from city;
-> end !
delimiter ;
call proc1('hangzhou');

------------------刪除儲存過程----------------------
儲存過程內容的修改方法是通過刪除原有儲存過程，之後再以相同的名稱建立新的儲存過程。
drop procedure if exists 儲存過程名;        #僅當存在時刪除，不新增if exists時，如果指定的過程不存在，則產生一個錯誤

##儲存過程的控制語句##
create table num (id int);
insert into num values(10);
（1）條件語句if...then...else ...end if 
delimiter ！！ 
create procedure proc2(in nid int)  
-> begin 
-> declare var int;   #declare 變數名 資料型別 來建立一個變數
-> set var=nid*3;     #設定變數的引數
-> if var>=10 then    #if-else判斷
-> update num set id=id+1;  
-> else 
-> update num set id=id-1;  
-> end if;   #結束判斷
-> end ！！
delimiter ;
call proc2(6);

（2）迴圈語句while...do...end while   #用法和if一樣，while的話必須要給定義的變數自增的過程，否則會死迴圈，可以參考shell中的while語句
delimiter ！！  
create procedure proc3()
-> begin 
-> declare var int(10);  
-> set var=0;  
-> while var<6 do  
-> insert into num values(var);  
-> set var=var+1;  
-> end while;  
-> end ！！ 
delimiter ;
call proc3;

總結

• 各種函數的使用
• 多表查詢時的欄位所屬的表
• 儲存過程的整體流程
• 靈活運用所學知識，舉一反三

到此這篇關於MySQL一些常用高階SQL語句詳解的文章就介紹到這了,更多相關MySQL SQL語句內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！