MySQL中SQL命令語句條件查詢範例詳解

2022-11-09 14:02:28

一、聚合函數

聚合函數：又叫組函數，用來對錶中的資料進行統計和計算，結合group by分組使用，用於統計和計算分組資料

常用聚合函數

count(col)：求指定列的總行數
max(col)：求指定列的最大值
min(col)：求指定列的最小值
sum(col)：求指定列的和
avg(col)：求指定列的平均值(不計null值)
- ifnull(欄位名,指定值)：判斷指定欄位是否為null，若為空則使用指定值替換

二、分組查詢

分組查詢：將查詢結果按指定欄位進行分組，欄位中資料相等的分為一組

語法：group by 列名 [having 條件表示式][with rollup]

列名：指按指定欄位的值進行分組
having 條件表示式：用來過濾分組後的資料
with rollup：在所有記錄最後加上一條記錄，顯示select查詢時聚合函數的統計和計算結果

group by：用於單個或多個欄位分組

group_concat(欄位名)：統計每個分組指定欄位的資訊集合，每個資訊間用逗號分隔

group by結合聚合函數使用

group by結合having使用

having作用：與where類似，用來過濾分組資料，只能用於group by

group by結合with rollup使用

with rollup作用：在記錄最後新增一行，顯示select查詢時聚合函數的統計和計算結果

三、連線查詢

連線查詢：能夠實現多個表之間的查詢，適合查詢的欄位資料來自不同的表的情況

內連線：查詢兩個表中符合條件的共有記錄，根據連線查詢條件取出兩個表中的交集
內連線查詢語法：select 欄位 from 表1 inner join 表2 on 表1.欄位1=表2.欄位2;
inner join:內連線查詢關鍵字
on:連線查詢條件

左連線：以左表為主根據條件查詢右表資料，若右表資料不存在則用null填充
左連線查詢語法：select 欄位 from 表1 left join 表2 on 表1.欄位1=表2.欄位2;
left join:左連線查詢關鍵字
on:連線查詢條件
表1為左表，表2為右表

右連線：以右表為主根據條件查詢左表資料，若左表資料不存在則用null值填充
右連線查詢語法：select 欄位 from 表1 right join 表2 on 表1.欄位1=表2.欄位2;
right join:右連線查詢關鍵字
on：連線查詢條件
表1為左表，表2為右表

自連線：左表與右表為同一個表，根據連線查詢條件查詢兩個表中資料，必須對錶起別名
自連線查詢語法：select 欄位 from 表名 as 別名1 inner join 表名 as 別名2 on 別名1.欄位1=別名2.欄位2;
自連線查詢就是把一張表模擬成左右兩張表，然後進行連表查詢，是一種特殊的連線方式，連線的表還是本身這張表

四、子查詢

子查詢：在select語句中嵌入另一個select語句，被嵌入的select稱為子查詢語句，外部select稱為主查詢

子查詢與主查詢關係

子查詢是嵌入在主查詢中的
子查詢輔助主查詢，用來充當條件或資料來源
子查詢可以獨立存在，是一條完整的select語句

五、資料庫設計三正規化及E-R模型

正規化：對設計資料庫提出的一些規範，共有8正規化，一般只需遵循3正規化

第一規格化(1NF)：強調列的原子性，即列不能再細分為其他列
第二正規化(2NF)：滿足1NF，另有兩部分內容
- 表必須有一個主鍵
- 非主鍵欄位必須完全依賴於主鍵，不能只依賴與主鍵一部分
第三正規化(3NF)：滿足 2NF，另外非主鍵列必須直接依賴於主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴於非主鍵列 B，非主鍵列 B 依賴於主鍵的情況

E-R模型：即實體-關係模型，用來描述資料庫儲存資料的結構模型

實體：用矩形表示，並標註實體名稱
屬性：用橢圓表示，並標註屬性名稱
關係：用菱形表示，並標註關係名稱
- 一對一：如人與身份證，在兩個表中建立一個欄位，其中一個表儲存另一個表的主鍵值，關係是一種資料，需要通過一個欄位儲存在表中
- 一對多：如學生表與班級表，在多的一方表(學生表)中建立一個欄位，儲存班級表的主鍵值
- 多對多：如學生表、班級表、科目表，新建一張表C，這個表只有兩個欄位，一個用於儲存A的主鍵值，一個用於儲存B的主鍵值

六、外來鍵約束

外來鍵約束：對外來鍵欄位的值進行更新和插入時會和參照表中欄位的資料進行驗證，若資料不合法則更新和插入失敗，能夠保證資料有效性

新增和刪除外來鍵約束

已存在的欄位新增外來鍵約束：alter table 表1 add foreign key(表1中的外來鍵) references 表2(表2主鍵);
                    或：alter table 從表 add foreign key(外來鍵欄位) references 主表(主鍵欄位);
例：alter table students add foreign key(sid) references classe(id);
 
建立表新增外來鍵約束：
create table teacher(
    id int primary key auto_increment not null,
    name varchar(10),
    sid int not null,
    foreign key(sid) references students(id)
);
 
刪除外來鍵約束：
獲取外來鍵約束名稱：show create table 表名;
刪除：alter table 表名 drop foreign key 外來鍵名;