Mysql 資料庫 -------- SQL語句進階查詢 ------- 前部分

量子黑洞丶發表於2020-10-16

Mysql進階查詢

按 order by 關鍵字排序

  • 使用 order by 語句來實現排序
  • 排序可針對一個或多個欄位
  • asc :升序(語法中預設排序方式)
  • desc :降序
order by:
語法結構1:
select 欄位名1,欄位名2... from 表名 order by 欄位名1,欄位名2... asc|desc;
 
 示例:
 select id from info order by desc;

語法結構2:
select 欄位名1,欄位名2... from 表名 where 欄位名 >60 order by 欄位名 desc,欄位名 asc;

示例:
 select socre from info where socre >60 order by socre desc;

對 group by 結果進行分組

  • 使用 group by 語句來實現分組
  • 通常結合聚合函式一起使用
  • 可以按一個或者多個欄位對結果進行分組
group by:
語法結構1:
select 聚合函式(根據這個欄位名),被統計欄位名 from 表名 where 被統計欄位名 >=70 group by 被統計欄位名;

示例:
select count(name),socre from info where socre >=70 group by socre;
  • group by 結合 order by
示例:
select count(name),socre from info where socre >=70 group by socre order by count(name) desc;

limit 限制結果條目

  • 只返回 select 查詢結果的第一行或前幾行
  • 使用 limit 語句限制條目
limit:
語法結構:
select 欄位名1,欄位名2.. from 表名 limit[位置偏移量,從0開始] number;                 ####number 為 返記錄行的最大數目

示例:
select * from info limit 3;         ###查詢表中第3行
select * from info limit 3,5;       ###從索引號0開始數3行也就是第四行,指定行數五行,包括該行

as 設定別名

  • 使用 as 語句設定別名,關鍵字 as可以省略
  • 設定別名時,保證不能與庫中其他表或欄位名衝突
as :
結構語法1:給欄位設定別名
select 欄位名 as 新設的欄位名,欄位名 as 新設的欄位名 from 表名;
示例:
select name as 姓名,socre as 成績 from info;
select count(*) as cc from info;

語法結構2:給表設別名
select name 姓名,score  成績 from info as i;
示例:
select name 姓名,score 成績 from info as i;   ###原欄位或表名在庫內不會被改變;as 可以省略
  • as 作為連線語句
示例:
create table xxx as select * from info;       ###建立xxx表,複製info的表結構
desc xxx;         ###xxx的表結構與info的表結構相同

萬用字元

  • 用於替換字串中的部分字元
  • 通常配合 like 一起使用,並協同 where 完成查詢
  • 常用萬用字元
    • % 表示零個、一個或多個
    • _ 表示單個字元
  • 常用於模糊查詢(如 淘寶、各種商業平臺)
語法結構:
select * from 表名 where 欄位名 like '字元%';

示例:
select * from info where name like 'z%';      ###匹配多個帶有 z 字的欄位

select * from info where name like 'l_s_';       ###匹配一個帶有l和s的欄位

子查詢

  • 也稱作 內查詢 或 巢狀查詢
  • 先於主查詢被執行,其結果將作為外層主查詢的條件
  • 在增刪改查中都可以使用子查詢
  • 支援多層巢狀
  • in 語句是用來判斷某個值是否在給定的結果集中
語法結構:
select * from 表名 where 欄位名1 in (select 欄位名1 from 表名 where 欄位名2 in (select 欄位名 from 表名));

示例:
select * from info where id in (select id from tmp where name in (select name from aba));          ###巢狀查詢,先讀括號裡的值作為結果,給外層作為條件,由內而外
括號中的欄位型別要與外層的欄位型別匹配,才能被執行,否則不會被執行!

select a.id from (select id,name from info) a;      ####這種巢狀也是可以的,不過得設定一個別名
  • view 檢視
    • 檢視為資料庫中的虛擬表,這張虛擬表中不包含任何資料,只是做了資料對映。(動態的儲存一個結果集,但這個結果集中是不包含資料的,這個資料是存在於真實的表中)
示例:
create view v_score as select * from info where score >= 80;          ###v_score 表示 檢視名;as 連線  ;  查詢info表中成績大於等於80的人連線新建的v_score檢視表
select * from v_score;            ### 檢視檢視表
update into set score=77 where name='libai';              ### 修改info表中libai的成績
select * from info;         ###檢視 info表中 libai 成績變成 77
select * from v_score;            ### 再次檢視 檢視表中 libai 的欄位 被更新掉了
驗證:檢視的作用為動態的儲存一個結果集,但這個結果集中是不包含資料的,如果條件不滿足這張檢視,則檢視被更新掉,更改的記錄在原表中,不會在檢視表中,檢視表只會記錄滿足條件的資料(條件:為 where 新增的條件)

NULL值

  • 表示缺失值,與數字0 或 空白(spaces)是不同的
  • 使用ls null 或 ls not null 進行判斷
  • null值和空值的區別:
    • 空值長度為0,不佔空間;null 值長度為null,佔空間
    • ls null 無法判斷空值
    • 空值使用 “=”或 “<>”來處理 (<> 意思是不等於;’<>’ = ‘!=’)
    • count()計算時,null 值 會忽略,不被統計 ; ''空值 會被統計
示例:
update info set addr='nj' where score >=70;            ###將 欄位addr 預設null值 修改為 nj
select * from info;
select count(addr) from info;         ###null 沒有被加入統計

示例:
update info set addr='' where name='xiaoli';          ###將xiaoli 的addr 型別 null值修改為空值
select * from info;
select count(addr) from info;        ###空值加入了統計中

正規表示式

  • 根據指定的匹配模式匹配記錄中符合要求的特殊字元
  • 使用 regexp 關鍵字指定 正規表示式
  • 常用匹配模式:
    • ^ :匹配開始字元
    • $ :匹配結束字元
    • . :匹配任意單個字元
    • *:匹配任意個前面的字元
    • +:匹配前面字元至少1次
    • p1|p2 :匹配p1或p2
    • […] :匹配字符集中的任意一個字元
    • [^…] :匹配不在括號內的任意字元
    • a{n}:匹配a n次
    • a{n,m}:匹配a至少n次,最多m次
語法格式:
select * from info where name regexp '匹配模式';

運算子

  • 用於對記錄中的欄位進行運算
  • mysql 的運算子有四種:算術運算子、比較運算子、邏輯運算子、位運算子

算術運算子

  • 加“+”、減“-”、乘“*”、除“/”、取餘“%”
select 4+2,3-1,2*3,4/2,7%2;
在除法運算和求餘數運算中,除數不能為0,若除數是0,返回的結果則為null。
如果有多個運算子,按照先乘除後加減的優先順序進行運算,相同優先順序的運算子沒有先後順序

create table rest as select 4+2,3-1,2*3,4/2,7%2;           ####處理的結果作為test的表結構
create view v_oot as select 4+2,3-1,2*3,4/2,7%2;           ####處理的結果生成 v_oot檢視表

比較運算子

  • =、 >、 <、 >=、 <=、 != 或 <> 、ls null 、ls not null

等於運算子

  • 等號(=)是用來判斷數字、字串和表示式是否相等的,如果相等則返回1,如果不相等則返回O。如果比較的兩者有一個值是 NULL,則比較的結果就是NULL。其中字元的比較是根據ASCIl碼來判斷的,如果ASCIl碼相等,則表示兩個字元相同;如果ASCII 碼不相等,則表示兩個字元不相同。
示例:
select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=null;

1、如果兩者都是整數,則按照整數值進行比較
2、如果一個整數一個字串,則會自動將字串轉換成數字再進行比較。
3、如果兩者都是字串,則按照字串進行比較
4、如果兩者中至少有一個值是null,則比較的結果是null


單個字元比 ASCll 碼        0 :  48     A :  65    a :  97
select 'abc' = 'abc'      比字串比的是內容 比的值為1

不等於運算子

  • 不等於號有兩種寫法,分別是<>或者!=,用於針對數字、字串和表示式不相等的比較。如果不相等則返回1,如果相等則返回O,這點正好跟等於的返回值相反。需要注意的是不等於運算子不能用於判斷 NULL
示例:
select 'kgc'<>'bdqn',1<>2,3!=3,2.5!=2,null<>null;


比較原理:
select 'abc' < 'bcd'       比的是內容  1,成立,是或的關係
select 'abc' > 'bcd'       比的是內容  0,不成立,是且的關係(都要比,'abc'中a、b、c  都比  'bcd'中b、c、d 大)

select 'abc' < 'baa'       比的是內容  1,成立,是或的關係(只要比第一個:'baa'中b 比 'abc'中a 大,就不比了 )

大於、小於 、大於等於、小於等於運算

示例:
select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=null;

比的是ASCII碼錶

ls null 、ls not null

  • ls null 判斷一個值是否為null,如果為null返回1,否則返回0
  • ls not null 判斷一個值是否不為null,如果不為null 返回1,否則返回0
示例:
select 2 ls null,'f' ls not null,null ls null;

between and

select 2 between 2 and 6      成立    2包含在兩者範圍內

least 、greatest (函式)

  • least :當有兩個或者多個引數時,返回其中的最小值。如果其中一個值為null,則返回結果為null
  • greatest :當有兩個或者多個引數時,返回其中的最大值。如果其中一個值為null,則返回結果就為null
示例:
select least(10,20,30),greatest(10,20,30);        ###返回結果為 least:10   ;  greatest:30

select least(10.4,20,30),greatest(10,20,40.8);         ###型別不同也能查詢;返回結果為 least:10.4   ;  greatest:40.8

select least(10.4,20,30,'a'),greatest(10,20,40.8,'a');       ### 返回結果為 least:0   ,不能被執行成功   ;  greatest:40.8

select least(10.4,20,30,null),greatest(10,20,40.8,null);         ### 返回值都為 null

總結:least 如果放入的是不同的資料型別(如 包含字元 和 數值 的混合擺放),其函式是不能被識別的;
     greatest 資料型別(字元和數值的混合)則遮蔽掉字元。其中一個值為null,返回值就位null

in 、notin

  • in :判斷一個值是否在對應的列表中,如果是返回1,否則為0
  • notin :判斷一個值是否不在對應的列表中 ,如果不是返回1,否則為0
示例:
select 'a' in ('a','b','c'),'a' not in ('a','b','c');          ###返回值  in :為1,成立 ;not in0,不成立
select 97 in ('a','b','c'),'a' not in ('a','b','c');          ###返回值  in :為0,不成立,不能與ASCII碼相比 ;not in0,不成立

like 、 not like

  • like :用來匹配字串,如果匹配成功則返回1,否則為0。支援兩種萬用字元:’%‘用於匹配任意數目的字元,而’_'只能匹配一個字元
  • not like :和 like 相反,如果沒有匹配成功則返回1,否則為0。
示例:
select 'libai' like 'li%';           ### 返回值為1
select 'libai' like 'li___';           ### 返回值為0
select 'libai' not like 'li___';           ###返回值為1

select * from info where name like 'li__';           ###用於模糊查詢info表中name欄位中 帶有li字元後面跟三位 的人名

邏輯運算子

  • 對應關係表
0&&0=0        1&&0=0      0&&1=0      1&&1=10||0=0        1||0=1      0||1=1      1||1=1

異或      0^0=0        1^0=1       0^1=1       1^1=0
  • 非關係
select not 0,!2,!(4-4);          ###返回值為 101
  • 且關係
select 2&&3,0&&1,0&&null,1and null;         ###返回值為 100null
  • 或關係
select 2 or 3,'a' or 0;           ###返回值為 10
select 2 or 3,'a' || 0;           ###返回值為 1 ;a0
select 0 or null,1 or null;          ###返回值為 null1
select 0 || null,1 || null;           ###返回值為 nullnull
總結:或運算子中 :建議使用 or ,不建議使用 || , 結果不一樣;  || 的意思是  把左右兩邊一起輸出  ;    或運算返回值判斷是 :只要返回值為非零值,那就為成立,後面不用執行了,所以 or 才是最適合或關係運算的語句
select null or 1;       ###返回值為1
select null or 0;         ###返回值為 nullnull 為空,有長度、佔空間 ;0為空值,長度為0,不佔空間
  • 異或關係
select 2 xor 3;         ###返回值為 0
select 0 xor 3,0 xor 0,0 xor null,1 xor null;              ###返回值為 10nullnull
總結:異或       碰到null 則為null ;兩個非零值 為零

位運算子(運維開發)

  • 位運算子 :對二進位制數進行計算的運算子
  • mysql 內位運算會先將運算元變成二進位制格式,然後進行位運算,最後在將計算結果從二進位制變回到十進位制格式,方便使用者檢視
    在這裡插入圖片描述
示例:
select 10 | 15;            ### 返回值為15
select 10 ^ 15;             ###返回值為 5
select 5&~1;           ####返回值為 4
  • “<<” 左移位運算
select 10 << 3;         返回值為80
  • “>>” 右移位運算
select 15 >> 2;          返回值為3

運算子的優先順序

  • 決定不同的運算子在計算過程中的先後順序
  • 優先順序高的先運算,同級的按從左到右進行計算
  • 可以使用 () 小括號來改變計算優先順序
  • !的優先順序最高,而 :=的優先順序最低
    在這裡插入圖片描述

相關文章