MySQL新手入門進階語句彙總

2022-09-24 14:02:06

引言

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

一、常用查詢

1.1、order by按關鍵字排序

實用select語句可以講需要的資料從mMySQL資料庫中查詢出來，如果對插敘的結果進行排序，可以實用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子句對查詢結果進一步過濾。

升序排序

select name,power from name order by power;

降序排序

select name,power from name order by power desc;

結合where進行條件過濾在排序

select name,power,phone from name where phone=1234566777 order by power;

多欄位排序

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

select name,power,phone from name order by phone,power desc;

1.2、and/or判斷

在大型資料庫中，有時查詢資料需要資料符合某些特定條件

AND/OR且/或和shell指令碼中一樣的使用方法

and/or使用

mysql> select name,address,power from name where power>66 and power<999;
select name,address,power from name where power>66 or power<999;

1.3、distinct查詢不重複記錄

select distinct 欄位 from 表名﹔
 
 distinct 必須放在最開頭
 
 distinct 只能使用需要去重的欄位進行操作
 
 distinct 去重多個欄位時，含義是：幾個欄位同時重複時才能被過濾，會預設按左邊第一個欄位為依據。

1.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 欄位名;

1.5、limit限制結果條目

limit限制輸出的結果記錄

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

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

1.6、設定別名（alias-as）

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

列的別名 select 欄位 as 欄位別名表名

表的別名 select 別名.欄位 from 表名 as 別名

as 可以省略

使用場景：
對複雜的表進行查詢時，別名可以縮短查詢語句的長度
奪標相連查詢的時候（通俗易懂，減短sql語句）

在為表設定別名時，要保證別名不能與資料庫中的其他表的名稱衝突

列的別名在結果中有顯示的，而表的別名在結果中沒有顯示，只在執行查詢時使用

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

利用as，將查詢的資料匯入另一個表內

create table test2 as select * from name;
select * from test2;
select * from name;

這裡as起到的作用

1、建立了一個新表，並定義表結構，插入表資料(與school表相同)

2、但是約束沒有被完全複製過來，但是如果原表設定了主鍵，那麼附表的：default欄位會預設設定一個0

1.7、萬用字元

1、萬用字元主要用於替換字串中的部分字元，通過部分字元的匹配將相關結果查詢出來

2、通常萬用字元都是跟LIKE一起使用的，並協同WHERE

3、子句共同來完成查詢任務，常用的萬用字元有兩個，分別為

%:百分號表示零個、一個或多個字元
_:下劃線表示單個字元

二、子查詢

1、自查詢也被稱作內查詢或者巢狀查詢，是指在一個查詢語句裡面還巢狀著另一個查詢語句

2、子查詢語句是先於主查詢進行下一步的查詢過濾

3、在自查詢中可以與主語句查詢相同的表，也可以是不同的表‘

2.1、select查詢

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

語法格式
select 欄位1，欄位2 from 表名1 where 欄位 in (select 欄位 from 表名 where 條件)；

主語句：select 欄位1，欄位2 from 表名1 where 欄位

in：將主表和子表關聯/連線的語法

子語句（集合）：select 欄位 from 表名 where 條件

相同表查詢

select name,address from test2 where id in (select id from test2 where power>0);

多表查詢

select name,power from test2 where id in (select id from test1 where power<100);

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

select name,power from test2 where id not in (select id from test1 where power<100);

2.2、insert插入

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

create table test3 like test2;
desc test3;
insert into test3 select * from test2 where id in (select id from test2 where power<100);

2.3、update更新

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

update test2 set power=555 where id in (select id from test3 where power<0);

2.4、delete刪除

selete也使用與子查詢

delete from test2 where id in (select id from test3 where power<50)；

2.5、exists

exists這個關鍵字在子查詢時，主要用於判斷子查詢的結果集是否為空，如果不為空，則返回ture，反之則返回false

注：在使用exists時，當子查詢有結果時，不關心子查詢的內容，執行主查詢操作，當子查詢沒有結果時，則不執行主查詢操作

三、MySQL檢視

檢視是一張虛擬表，這張虛擬表中不包含真實資料，只是做了真實資料的對映

功能：

1、簡化查詢結果集、靈活查詢，可以針對不同使用者呈現不同結果集，相對有更高的安全性

2、本質而言，檢視是一種select(結果集的呈現)

注意：檢視適合於多表瀏覽時使用，不適合增，刪，改

3.1、檢視與表的區別

檢視是語句編譯好的sql語句，而表不是
檢視沒有實際的物理記錄，而表有
表佔用物理空間而檢視不佔用物理空間
檢視只是邏輯概念的存在，表可以及時對它進行修改，但檢視只能有建立的語句來修改
檢視是檢視資料表的一種方法，可以查詢資料表中某些欄位構成的資料，只是一些sql語句的集合
從安全的角度說，檢視可以不給使用者接觸資料表，從而不知道表結構
表屬於全域性模式中的表，是實表；檢視屬於區域性模式的表，是虛表
檢視的建立和刪除隻影響檢視本身，不影響對應的基本表。（但是更新檢視資料，是會影響到基本表的）

3.2、檢視與表的聯絡

檢視（view）是在基本表之上建立的表，它的結構（即定義的列）和內容（即所有資料行）都來自基本表
一個檢視可以對應一個基本表，也可以對應多個基本表
檢視是基本表的抽象和在邏輯意義上建立的新關係。

#建立檢視
create view 檢視表名 sa select * from 表名 where 條件；
#檢視檢視
select * from 檢視表名
#檢視表狀態
show table statusG
#檢視檢視結構
desc 檢視表名

修改檢視表資料

當資料傳送變化時，若資料與之前建立檢視表時的關聯條件不一致時，檢視表的資料將會傳送改變

update name_view set power=300 where id=4;
select * from name_view;
select * from name;

更改源表資料

update name_view set power=100 where id=2;
select * from name_view;
select * from name;

四、NULL值

在SQL語句使用過程中，接觸會碰到NULL這幾個字元。通常使用NULL來表示缺失的值，也就是在表中該欄位是沒有值的。如果在建立表時，限制某些欄位不為空，則可以使用NOT NULL關鍵字，不使用則預設可以為空。在向表內插入記錄或者更新記錄或者更新記錄時，如果該欄位沒有NOT NULL並且沒有值，這時候新記錄的該欄位將被儲存為NULL。需要注意的是，NULL值與數位0或者空白（spaces）的欄位是不同的，值為NULL的欄位是沒有值的。在SQL語句中，使用IS NULL可以判斷表內的某個欄位是不是NULL值，相反的用IS NOT NULL可以判斷不是NULL值。

4.1、NULL值與空值區別

空值長度為0，不佔空間，NULL值得長度為null，佔用空間
is null無法判斷空值
空值使用"=“或者”<>"來處理（!=）
count()計算時，NULL會忽略，空值會加入計算

注：NULL是佔用記憶體空間的，而空值則不佔用記憶體空間

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

update name set card=' ' where id=3;
select * from name;
select count(card) from name;

4.2、查詢空值與非空值

select *  from name where card is null;
select *  from name where card is not null;

五、連線查詢

MySQL 的連線查詢，通常都是將來自兩個或多個表的記錄行結合起來，基於這些表之間的共同欄位，進行資料的拼接。首先，要確定一個主表作為結果集，然後將其他表的行有選擇性的連線到選定的主表結果集上。
使用較多的連線查詢包括：內連線、左連線和右連線

5.1、內連線

MySQL中的內連線就是兩種或多張表中同時符合某種條件的資料記錄的組合
通常在FROM子句中使用關鍵子INNER　JOIN來連線多張表，並使用ON子句設定連線條件，
內連線是系統預設的表連線，所以在 FROM 子句後可以省略 INNER 關鍵字，只使用關鍵字 JOIN。
同時有多個表時，也可以連續使用 INNER JOIN 來實現多表的內連線。
不過為了更好的效能，建議最好不要超過三個表。

內連查詢：通過inner　join的方式將兩張表指定的相同欄位的記錄行輸出出來

select 表名1.欄位1，表名1.欄位2 from 表名1 inner join 表名2 on 表名1.欄位 = 表名2.欄位; 
 
select name.id,name.name,name.address from name inner join test2 on name.id=test2.id;

5.2、左連線

左連線也可以被稱為左外連線，在FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 關鍵字來表示。
左連線以左側表為基礎表，接收左表的所有行，並用這些行與右側參考表中的記錄進行匹配，也就是說匹配左表中的所有行以及右表中符合條件的行。

select * from name left join test2 on name.id=test2.id;

5.3、右連線

右連線也被稱為右外連線，在FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 關鍵字來表示。
右連線跟左連線正好相反，它是以右表為基礎表，用於接收右表中的所有行，並用這些記錄與左表中的行進行匹配。

總結

在MySQL中，檢視表與索引一樣，都是MySQL資料庫的一種優化，其可以加快查詢速度，但需要注意的時，檢視表一般只作查詢使用，不對其進行增、刪、改；檢視表並不佔用實際記憶體

在表中的NULL值與空值，NULL值是佔用記憶體空間，但是不計入資料統計，而空值是不佔記憶體空間，但是算資料，計入資料統計的。

內連線inner join，顯示的資料為左右表都同時滿足條件。

左連線 left join ，是以左表為基礎顯示，右表需滿足條件。

右連線right join ，是以右表為基礎顯示，左表需滿足條件。

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