MySQL高級SQL語句

                        引言

                        對MySQL數據庫的查詢,除了基本的查詢外,有時候需要對查詢的結果集進行處理。例如只取10條數據、對查詢結果進行排序或分組等等。

                        一、常用查詢

                        1、order by按關鍵字排序

                        使用SELECT語句可以將需要的數據從MySQL數據庫中查詢出來,如果對查詢的結果進行排序,可以使用order by語句來對語句實現排序,并最終將排序的結果返回給用戶。這個語句的排序不光可以針對某一個字段,也可以針對多個字段。

                        語法:

                        select 字段1,字段2... from 表名 order by 字段1,字段2... asc   #查詢結果以升序方式顯示,asc可以省略
                        select 字段1,字段2... from 表名 order by 字段1,字段2... desc #查詢結果以降序方式顯示
                        
                        1. ASC是按照升序進行排序,是默認的排序方式,即ASC可以省略。
                        2. SELECT語句中如果沒有指定具體的排序方式,則默認按ASC方式進行排序。
                        3. DESC是按照降序方式進行排列。當然order by前面也可以使用where子句對查詢結果進一步過濾。

                        1.1 升序排序

                        select name ,score from report order by score;
                        

                        在這里插入圖片描述

                        1.2 降序排序

                        select name ,score from report order by score desc;
                        

                        在這里插入圖片描述

                        1.3 結合where進行條件過濾再排序

                        select name,score,address from report where address='hangzhou' order by score;
                        

                        在這里插入圖片描述

                        1.4 多字段排序

                        ORDER BY 語句也可以使用多個字段來進行排序,當排序的第一個字段相同的記錄有多條的情況下,這些多條的記錄再按照第二個字段進行排序,ORDER BY 后面跟多個字段時,字段之間使用英文逗號隔開,優先級是按先后順序而定,但order by 之后的第一個參數只有在出現相同值時,第二個字段才有意義。

                        select name,score,score2 from report order by score2,score desc;
                        

                        此處做的實驗是先升序排序再降序排序,也可以都降序排序或者都升序排序,這里就不做過多的例子了。
                        在這里插入圖片描述

                        2、and/or判斷

                        在大型數據庫中,有時查詢數據需要數據符合某些特點條件
                        AND/OR 且/或 和shell腳本中一樣使用方法

                        2.1 and/or 且與或的使用

                        select name,score,cardid from report where score>80;
                        

                        在這里插入圖片描述

                        select name,score,cardid from report where score>80 and score<90;
                        select name,score,cardid from report where score<60 or score>90;
                        

                        在這里插入圖片描述

                        2.2 嵌套、多條件使用

                        select name,score,cardid from report where score<60 or (score>70 and score<80);
                        select name,score,cardid from report where score<60 or (score>70 and score<=100) order by score desc;
                        

                        在這里插入圖片描述
                        在這里插入圖片描述

                        3、distinct 查詢不重復記錄

                        select distinct 字段 from 表名﹔
                         
                         distinct 必須放在最開頭
                         
                         distinct 只能使用需要去重的字段進行操作
                         
                         distinct 去重多個字段時,含義是:幾個字段同時重復時才能被過濾,會默認按左邊第一個字段為依據。
                        

                        在這里插入圖片描述

                        4、GROUP BY對結果進行分組

                        通過 SQL 查詢出來的結果,還可以對其進行分組,使用 GROUP BY 語句來實現 ,GROUP BY 通常都是結合聚合函數一起使用的,常用的聚合函數包括:計數(COUNT)、 求和(SUM)、求平均數(AVG)、最大值(MAX)、最小值(MIN),GROUP BY 分組的時候可以按一個或多個字段對結果進行分組處理。

                        1. 對group by 后面的字段的查詢結果進行匯總分組,通常是結合聚合函數一起使用的
                        2. group by 有一個原則,就是select 后面的所有列中,沒有使用聚合函數的列必須出現在 group by 的后面。
                        select 字段,聚合函數 from 表名 (where 字段名(匹配) 數值) group by 字段名;
                        

                        在這里插入圖片描述
                        在這里插入圖片描述

                        5、limit限制結果條目

                        limit 限制輸出的結果記錄

                        在使用 MySQL SELECT 語句進行查詢時,結果集返回的是所有匹配的記錄(行)。有時候僅需要返回第一行或者前幾行,這時候就需要用到 LIMIT 子句。

                        語法格式:
                         
                        select 字段 from 表名 limit [offset,] number
                         
                        limit 的第一個參數是位置偏移量(可選參數),是設置 mysql 從哪一行開始
                         
                        如果不設定第一個參數,將會從表中的第一條記錄開始顯示。
                         
                        第一條偏移量是0,第二條為1
                         
                        offset 為索引下標
                         
                        number 為索引下標后的幾位
                        

                        在這里插入圖片描述
                        在這里插入圖片描述
                        在這里插入圖片描述

                        6、設置別名(alias—as)

                        在 mysql 查詢時,當表的名字比較長或者表內某些字段比較長時,為了方便書寫或者多次使用相同的表,可以給字段列或表設置別名,方便操作,增強可讀性。

                        列的別名  select 字段 as 字段別名 表名
                         
                        表的別名 select 別名.字段 from 表名 as 別名
                         
                        as 可以省略
                        

                        使用場景:

                        1. 對復雜的表進行查詢的時候,別名可以縮短查詢語句的長度
                        2. 多表相連查詢的時候(通俗易懂、減短sql語句)

                        在為表設置別名時,要保證別名不能與數據庫中的其他表的名稱沖突。

                        列的別名是在結果中有顯示的,而表的別名在結果中沒有顯示,只在執行查詢時使用。
                        在這里插入圖片描述

                        6.1 查詢表的記錄數量,以別名顯示

                        在這里插入圖片描述
                        在這里插入圖片描述

                        6.2 利用as,將查詢的數據導入到另外一個表內

                        create table report2 as select * from report;
                        select * from report2;
                        select * from report;
                        

                        此處as起到的作用:

                        1. 創建了一個新表, 并定義表結構,插入表數據(與school表相同)
                        2. 但是”約束“沒有被完全”復制“過來 #但是如果原表設置了主鍵,那么附表的:default字段會默認設置一個0
                          在這里插入圖片描述
                          在這里插入圖片描述

                        7、通配符

                        1. 通配符主要用于替換字符串中的部分字符,通過部分字符的匹配將相關結果查詢出來。
                        2. 通常通配符都是跟LIKE一起使用的,并協同WHERE
                        3. 子句共同來完成查詢任務。常用的通配符有兩個,分別是:
                        %:百分號表示零個、一個或多個字符
                        _:下劃線表示單個字符
                        

                        在這里插入圖片描述
                        在這里插入圖片描述
                        通配符“%”和“_”不僅可以單獨使用,也可以組合使用
                        在這里插入圖片描述

                        二、子查詢

                        1. 子查詢也被稱作內查詢或者嵌套查詢,是指在一個查詢語句里面還嵌套著另一個查詢語句。
                        2. 子查詢語句是先于主查詢進行下一步的查詢過濾。
                        3. 在子查詢中可以與主語句查詢相同的表,也可以是不同的表。

                        1、select查詢

                        子語句可以與主語句所查詢的表相同,也可以是不同表

                        語法格式
                        select 字段1,字段2 from 表名1 where 字段 in (select 字段 from 表名 where 條件);
                        
                        主語句:select 字段1,字段2 from 表名1 where 字段
                        
                        in:將主表和子表關聯/連接的語法
                        
                        子語句(集合):select 字段 from 表名 where 條件
                        

                        1.1 相同表查詢

                        select name,score from report where id in (select id from report where score>80);
                        

                        在這里插入圖片描述

                        1.2 多表查詢

                        select name,score from report where id in (select id from lcdb2 where score<80);
                        

                        在這里插入圖片描述

                        1.3 NOT 取反,將子查詢的結果,進行取反操作

                        select name,score from liy where id not in (select id from school where score < 80);
                        

                        在這里插入圖片描述

                        2、insert插入

                        子查詢還可以用在insert語句中,子查詢的結果集可以通過insert語句插入到其它表中

                        create table report3 like report;
                        desc report3;
                        insert into report3 select * from report where id in (select id from report where score>80);
                        

                        在這里插入圖片描述

                        3、update更新

                        update語句也可以使用子查詢,update內的子查詢,在set更新內容時,可以是單獨的一列,也可以是多列。

                        update report set score=70 where id in (select id from report2 where score<60);
                        

                        在這里插入圖片描述
                        在這里插入圖片描述

                        4、delete刪除

                        delete也適用于子查詢。

                        delete from report2 where id in (select id where score<20);
                        

                        在這里插入圖片描述

                        5、exists

                        exists這個關鍵字在子查詢時,主要用于判斷子查詢的結果集是否為空,如果不為空,則返回true,反之則返回false
                        :在使用exists時,當子查詢有結果時,不關心子查詢的內容,執行主查詢操作;當子查詢沒有結果時,則不執行主查詢操作。

                        select count(*) from report where exists(select id from report where score=59);
                        
                        select count(*) from report where exists(select id from report where score=80);
                        

                        在這里插入圖片描述

                        6、as別名

                        將結果集作為一張表進行查詢的時候,需要用到別名

                        select count(*) from (select id from report where score>=70) a;
                        

                        在這里插入圖片描述

                        三、MySql視圖

                        視圖是一張虛擬的表,這張虛擬表中不包含真實數據,只是做了真實數據的映射。

                        功能:

                        1. 簡化查詢結果集、靈活查詢、可以針對不同用戶呈現不同結果集、相對有更高的安全性
                        2. 本質而言,視圖是一種select(結果集的呈現)

                        注:視圖適合于多表瀏覽時使用,不適合增、刪、改

                        1、視圖與表的區別

                        1. 視圖是已經編譯好的sql語句,而表不是
                        2. 視圖沒有實際的物理記錄,而表有
                        3. 表占用物理空間而視圖不占用物理空間
                        4. 視圖只是邏輯概念的存在,表可以及時對它進行修改,但視圖只能有創建的語句來修改
                        5. 視圖是查看數據表的一種方法,可以查詢數據表中某些字段構成的數據,只是一些SQL語句的集合。
                        6. 從安全的角度說,視圖可以不給用戶接觸數據表,從而不知道表結構
                        7. 表屬于全局模式中的表,是實表;視圖屬于局部模式的表,是虛表
                        8. 視圖的建立和刪除只影響視圖本身,不影響對應的基本表。(但是更新視圖數據,是會影響到基本表的)

                        2、視圖與表的聯系

                        1. 視圖(view)是在基本表之上建立的表,它的結構(即所定義的列)和內容(即所有數據行)都來自基本表,它依據基本表存在而存在。
                        2. 一個視圖可以對應一個基本表,也可以對應多個基本表。
                        3. 視圖是基本表的抽象和在邏輯意義上建立的新關系。
                        #創建視圖
                        create view 視圖表名 as select * from 表名 where 條件;
                        #查看視圖
                        select * from 視圖表名
                        #查看表狀態
                        show table status\G
                        #查看視圖結構
                        desc 視圖表名
                        

                        在這里插入圖片描述
                        在這里插入圖片描述
                        在這里插入圖片描述

                        2.1 修改視圖表數據

                        當數據發生變化時,若數據與之前創建視圖表時的關聯條件不一致時,視圖表的數據將會發生改變

                        update v_report set score=100 where id=3;
                        select * from v_report;
                        select * from report;
                        

                        在這里插入圖片描述

                        2.2 更改源表數據

                        update report set score=63 where id=1;
                        select * from v_report;
                        

                        在這里插入圖片描述

                        四、NULL值

                        在 SQL 語句使用過程中,經常會碰到 NULL 這幾個字符。通常使用 NULL 來表示缺失的值,也就是在表中該字段是沒有值的。如果在創建表時,限制某些字段不為空,則可以使用 NOT NULL 關鍵字,不使用則默認可以為空。在向表內插入記錄或者更新記錄時,如果該字段沒有 NOT NULL 并且沒有值,這時候新記錄的該字段將被保存為 NULL。需要注意 的是,NULL 值與數字 0 或者空白(spaces)的字段是不同的,值為 NULL 的字段是沒有 值的。在 SQL 語句中,使用 IS NULL 可以判斷表內的某個字段是不是 NULL 值,相反的用 IS NOT NULL 可以判斷不是NULL值。

                        1、NULL值與空值區別

                        1. 空值長度為0,不占空間,NULL值的長度為null,占用空間
                        2. is null無法判斷空值
                        3. 空值使用"=“或者”<>"來處理(!=)
                        4. count()計算時,NULL會忽略,空值會加入計算

                        注:NULL是占用內存空間的,而空值則不占用內存空間

                        alter table report3 add card char(10);
                        select * from report3;
                        select count(card) from report3;
                        

                        在這里插入圖片描述

                        update report3 set card=' ' where id=4;
                        select * from report3;
                        select count(card) from report3;
                        

                        在這里插入圖片描述

                        2、查詢空值與非空值

                        select * from school where card is null;
                        select * from report3 where card is not null;
                        

                        在這里插入圖片描述

                        五、連接查詢

                        MySQL 的連接查詢,通常都是將來自兩個或多個表的記錄行結合起來,基于這些表之間的 共同字段,進行數據的拼接。首先,要確定一個主表作為結果集,然后將其他表的行有選擇 性的連接到選定的主表結果集上。
                        使用較多的連接查詢包括:內連接、左連接和右連接

                        1、內連接

                        1. MySQL 中的內連接就是兩張或多張表中同時符合某種條件的數據記錄的組合。
                        2. 通常在 FROM 子句中使用關鍵字 INNER JOIN 來連接多張表,并使用 ON 子句設置連接條件。
                        3. 內連接是系統默認的表連接,所以在 FROM 子句后可以省略 INNER 關鍵字,只使用 關鍵字 JOIN。
                        4. 同時有多個表時,也可以連續使用 INNER JOIN 來實現多表的內連接。
                        5. 不過為了更好的性能,建議最好不要超過三個表。

                        內連查詢:通過inner join的方式將倆張表指定的相同字段的記錄行輸出出來

                        select 表名1.字段1,表名1.字段2 from 表名1 inner join 表名2 on 表名1.字段 = 表名2.字段; 
                        

                        在這里插入圖片描述

                        2、左連接

                        1. 左連接也可以被稱為左外連接,在FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 關鍵字來表示。
                        2. 左連接以左側表為基礎表,接收左表的所有行,并用這些行與右側參考表中的記錄進行匹配,也就是說匹配左表中的所有行以及右表中符合條件的行。
                        select * from report left join lcdb2 on report.name=lcdb2.name;
                        

                        在這里插入圖片描述

                        3、右連接

                        1. 右連接也被稱為右外連接,在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 關鍵字來表示。
                        2. 右連接跟左連接正好相反,它是以右表為基礎表,用于接收右表中的所有行,并用這些記錄與左表中的行進行匹配。
                        select * from report right join lcdb2 on report.name=lcdb2.name;
                        

                        在這里插入圖片描述

                        六、總結

                        1. 在MySQL中,視圖表與索引一樣,都是MySQL數據庫的一種優化,其可以加快查詢速度,但需要注意的時,視圖表一般只作查詢使用,不對其進行增、刪、改;視圖表并不占用實際內存
                        2. 在表中的NULL值與空值,NULL值是占用內存空間,但是不計入數據統計,而空值是不占內存空間,但是算數據,計入數據統計的。
                        3. 內連接inner join,顯示的數據為左右表都同時滿足條件。
                        4. 左連接 left join ,是以左表為基礎顯示,右表需滿足條件。
                        5. 右連接right join ,是以右表為基礎顯示,左表需滿足條件。

                        “相關推薦”對你有幫助么?

                        • 非常沒幫助
                        • 沒幫助
                        • 一般
                        • 有幫助
                        • 非常有幫助
                        提交
                        ??2022 CSDN 皮膚主題:數字20 設計師:CSDN官方博客 返回首頁

                        打賞作者

                        風雪一更ing

                        你的鼓勵將是我創作的最大動力

                        ¥2 ¥4 ¥6 ¥10 ¥20
                        輸入1-500的整數
                        余額支付 (余額:-- )
                        掃碼支付
                        掃碼支付:¥2
                        獲取中
                        掃碼支付

                        您的余額不足,請更換掃碼支付或充值

                        打賞作者

                        實付
                        使用余額支付
                        點擊重新獲取
                        掃碼支付
                        錢包余額 0

                        抵扣說明:

                        1.余額是錢包充值的虛擬貨幣,按照1:1的比例進行支付金額的抵扣。
                        2.余額無法直接購買下載,可以購買VIP、C幣套餐、付費專欄及課程。

                        余額充值
                        久久悠悠精品综合网