MySQL 單表操作介紹

今天我將詳細的爲大家介紹 MySQL 中單表操作的相關知識，希望大家能夠從中收穫多多！

數據操作

複製表結構和數據

1）複製已有的表結構

在開發時，需要創建一個與已有數據表相同的表結構的數據表時，可以通過下述語法來完成表結構的複製。

基本語法格式：

#複製已有的表結構法1：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 LIKE 舊錶名; 

#複製已有的表結構法2：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(LIKE 舊錶名);

語法說明：

爲了更好理解已有的表結構複製操作，現舉例說明：

2）複製已有的表數據

數據複製也可稱爲蠕蟲複製，是新增數據的一種方式，它是從已有的數據表中獲取數據，並將此數據插入到對應的數據表中。

此種方式獲取數據與插入數據的數據表的表結構要相同，否則可能會出現插入不成功的情況。

複製已有的表數據的語法格式：

INSERT [INTO] 數據表名1 [(字段列表)] SELECT [(字段列表)] FROM 數據表名2;

語法說明：

爲了更好理解已有的表數據複製操作，現舉例說明：

3）擴展：臨時表的使用

臨時表指的是一種僅在當前會話中可見，並在當前會話關閉時自動刪除的數據表，主要用於臨時存儲數據。

臨時表創建的語法格式：

#創建臨時表方法1：
CREATE TEMPORARY TABLE 表名(字段名 字段類型);

#創建臨時表方法2：
CREATE TEMPORARY TABLE 表名 SELECT (字段列表) FROM 已有數據表的表名;

語法說明：

爲了更好理解臨時表的使用，現舉例說明：

解決主鍵衝突

1）主鍵衝突

當你在數據庫表中設置一字段爲主鍵時，這意味着該字段的值是唯一的，不能重複。如果你試圖在表中插入一條新記錄，並且主鍵字段的值已經存在，就會發生主鍵衝突，進而導致你試圖插入的新記錄無法成功插入。

例如，假設你有一張學生信息表，其中有個名爲 "id" 的字段被設爲主鍵時，那麼在表中不能有兩條記錄的 id 值相同。如果你試圖插入一條新記錄，其 id 值已經存在於表中，就會發生主鍵衝突，進而導致記錄插入失敗。

爲了更好理解何爲主鍵衝突，現舉例說明：

1，學生表準備

2，主鍵衝突演示

3，主鍵衝突的解決

如果你在嘗試向數據庫表中插入一條新記錄時遇到主鍵衝突問題，若要解決這問題，可以使用 MySQL 所提供了兩種方式，即爲主鍵衝突更新和主鍵衝突替換。

主鍵衝突更新

主鍵衝突更新是指當插入數據時發生主鍵衝突，"更新" 操作會在表中查找發生主鍵衝突的記錄，然後用新記錄中的值更新該記錄中的值。

INSERT [INTO]  表名稱 [字段列表] {VALUE|VALUES} (值列表) 
ON DUPLICATE KEY UPDATE 字段名1 = 新值1 [,字段名2 = 新值2, ...];

主鍵衝突替換

主鍵衝突替換是指當插入數據時發生主鍵衝突，” 替換 “操作會在表中查找發生主鍵衝突的記錄，然後將該記錄完全刪除，並用新記錄代替

REPLACE [INTO] 表名稱 [字段列表] {VALUE|VALUES} (值列表);

注意：兩種操作都可以用來解決主鍵衝突，但是使用 "更新" 操作時，可以保留原有記錄中的部分值，而使用 "替換" 操作時會完全刪除原有記錄。因此，在選擇哪種操作時，需要考慮自己的需求。

清空數據

MySQL 中不僅可以使用 DELETE 語句進行刪除數據，還可以使用 TRUNCATE 清空指定表的全部數據，其基本語法格式如下：

#清空數據法1:
DELETE FROM 表名稱;

#清空數據法2:
TRUNCATE [TABLE] 表名稱;

爲了更好理解這兩種清空數據方法的使用，現舉例說明：

1，操作表準備

2，刪除數據操作

擴展：TRUNCATE 與 DELETE 的區別

儘管 TRUNCATE 操作與 DELETE 操作在使用來清空數據時非常相似，但這兩者之間存在本質區別。

區別歸納如下：

爲了更好的理解這兩者的區別，實例如下：

1，操作表準備 2，刪除數據操作 3，添加記錄操作 4，TRUNCATE 與 DELETE 操作結果對比

TRUNCATE 與 DELETE 語句在刪除數據的區別具體如下：

5，TRUNCATE 與 DELETE 操作說明

去除重複記錄

有時出於對數據記錄的分析需求，需要去除查詢記錄中的重複記錄，例如，想查看班級學生的民族種類數，就需要去除重複的民族記錄。

MySQL 中提供使用 SELECT 語句的選項進行去除重複記錄操作，其基本語法如下：

SELECT select選項 字段列表 from 表名稱;

語法說明：

爲了更好地理解去除重複記錄操作的使用，舉例如下：

排序

在實際開發時，爲了使查詢到的數據結果滿足用戶的需求，通常會對查詢到的數據進行升序或者降序的排序方式。

例如，用戶在進行網絡購物的時候，通常會對想買的商品數據進行排序的處理，例如以商品銷量或者以商品的綜合評價進行排序，讓滿足要求的商品數據放在前面，方便用戶進一步操作。

在 MySQL 中，針對不同的開發需求提供了兩種排序的方式，分別爲單字段排序和多字段排序。

單字段排序

單字段排序指的是在查詢時僅按照一個指定字段對查詢到的數據進行升序或者降序排序。其基本語法如下：

SELECT * | {字段列表} FROM 表名稱 ORDER BY 字段名 [ASC|DESC];

語法注意事項：

ASC 表示 升序 ，DESC 表示 降序，而 ORDER BY 默認爲 ASC ，即對錶數據進行排序操作，默認升序。

爲了更好地理解單字段排序，實例如下：

多字段排序

多字段排序指的是在查詢時按照多個指定字段對查詢到的數據進行升序或者降序排序。其基本語法如下：

SELECT * | {字段列表} FROM 表名稱 ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC], ...;

語法注意事項：

爲了更好地理解多字段排序，實例如下：

擴展：中文字段排序

1，面臨問題

在默認情況下 ，MySQL 使用的字符集是拉丁字符集，因此對於中文字符串的排序，默認不會按照中文拼音的順序排序，而是以區位碼來進行排序中文字符串，進而出現不同於你所期望的情況。

2，問題緣由

區位碼可以用來對中文字符串進行排序，但並不是每個字符的區位碼都是按照字母順序排列的。

在 Unicode 編碼系統中，中文字符的區位碼是按照筆畫順序排列的，而不是按照字母順序。

例如，字符 的 的區位碼是 U+7684，字符 一 的區位碼是 U+4E00，所以在使用區位碼排序中文字符串時，的 會排在 一 的前面。

3，解決方案

爲了避免這種情況，你可以使用 utf8mb4 字符集，或者使用拼音排序算法。

在不改變數據表結構的前提下，可以使用 MySQL 的 ORDER BY CONVERT(字段名 USING gbk) 函數來強制讓指定的字段按照中文拼音順序進行排序。

限量

數據查詢的排序與限量

對於一次性查詢出的大量數據記錄，不僅不便於閱讀查看，還會浪費系統資源。

爲了解決上述問題，MySQL 中提供了一個關鍵字 LIMIT，不僅可以限定記錄的數據，還可以指定查詢記錄從哪條記錄開始。

SELECT [select選項] {*|字段列表} FROM 表名 [WHERE 條件表達式] [ORDER BY 字段 ASC|DESC] LIMIT [OFFEST,] 記錄數;

語法說明：

爲了更好地理解數據查詢時的排序與限量使用，實例如下

1，成績表準備

實例：查找該學生成績表中的成績前三名的學生的全部信息。

2，限量記錄數

SELECT * FROM student ORDER BY score DESC LIMIT 3;

3，獲取指定區間記錄

SELECT * FROM student ORDER BY score DESC LIMIT 0,3;

數據更新的排序與限量

數據更新的排序與限量，基本語法：

UPDATE 表名稱 SET 字段 = 新值,... [WHERE 條件表達式] ORDER BY 字段 [ASC|DESC] LIMIT [OFFEST,] 記錄數;

語法說明：

數據更新操作時使用限量，只會對限量的數據進行更新操作，其它的數據不進行更新操作。

爲了更好地理解數據更新時的排序與限量使用，實例如下

實例：將前三名的成績都減少 5，求成績更新後成績爲前三的學生信息

UPDATE student SET SCORE = SCORE - 5 ORDER BY score DESC LIMIT 3;

UPDATE student SET SCORE = SCORE - 5 ORDER BY score DESC LIMIT 0,3;

數據更新前後的表數據（降序排序）區別：

數據刪除的排序與限量

數據刪除的排序與限量，基本語法：

DELETE FROM 表名稱 [WHERE 條件表達式] ORDER BY 字段 [ASC|DESC] LIMIT [OFFEST,] 記錄數;

語法說明：

數據刪除操作時使用限量，只會對限量的數據進行刪除操作，其它的數據不進行刪除操作。

爲了更好地理解數據刪除時的排序與限量使用，實例如下

實例：將前三名的成績刪除，求成績刪除後成績爲前三的學生信息數據刪除前後的表數據（降序排序）區別：

分組

在 MySQL 中，可以對數據記錄進行分組操作。也就是可根據一個字段或者多個字段對記錄進行分組，字段值相同的記錄爲一組。

爲了能夠更好地理解分組的相關操作及使用，接下來將對其進行逐一講解。

1，操作表的準備

分組統計

在查詢數據時，在 WHERE 條件後添加 GROUP BY 就可以根據指定的字段對記錄進行分組操作，基本語法如下：

SELECT [select選項] 字段列表 FROM 表名稱 [WHERE 條件表達式] GROUP BY 字段名;

語法注意事項：

爲了更好地理解分組統計的使用，實例如下：

實例：查詢每個成績分值的所佔人數

SELECT score,COUNT(*) FROM score GROUP BY score;

分組排序

在 MySQL 中，默認情況下爲分組操作的字段提供了升序排序的功能，在對記錄進行分組操作時，可以爲指定的字段進行升序和降序排序，其基本語法如下：

SELECT [select選項] 字段列表 FROM 表名稱 [WHERE 條件表達式] GROUP BY 字段名 [ASC|DESC];

語法注意事項：

GROUP BY 分組排序的實現不需要使用 ORDER BY，直接在分組字段後添加 ASC(升序，默認可省略)，DESC(降序) 即可。

爲了更好地理解分組排序的使用，實例如下：

實例：查看各個成績段的學生信息，成績由高到低排序

SELECT score.GROUP_CONCAT(name) FROM score GROUP BY score DESC;

多分組統計

在對數據進行分組統計時，MySQL 還支持數據按照某一個字段進行分組後，對已經分組的數據進行再次分組的操作，從而實現對數據的多分組統計。其基本語法如下：

SELECT [select選項] 字段列表 FROM 表名稱 [WHERE 條件表達式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...;

語法注意事項：

查詢出的數據首先按照字段 1 進行分組排序，再將字段 1 相同的記錄再按照字段 2 進行分組排序，依次類推。

爲了更好地理解多分組統計的使用，實例如下：

實例：查看各組學生的成績，成績由高到低，小組號由低到高

SELECT group_id,score FROM score GROUP BY group_id ASC, score DESC;

回溯統計

對數據進行分組統計時，MySQL 還支持回溯統計功能。回溯統計可簡單理解爲：在根據指定字段進行分組後，系統會自動對分組的字段進行一次新的統計併產生有關新的統計數據，且對應的分組字段值爲 NULL。其基本語法如下：

SELECT [select選項] 字段列表 FROM 表名稱 
[WHERE 條件表達式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...WITH ROOLLUP;

語法注意事項：

爲了更好地理解回溯統計的使用，實例如下：

實例：

SELECT score,group_id,COUNT(*) FROM score GROUP BY group_id ASC,score DESC WITH ROLLUP;

統計篩選

在對查詢到的數據進行分組統計時，還可以利用 HAVING 根據條件進行數據的篩選操作。其基本語法爲：

SELECT [select選項] 字段列表 FROM 表名稱 
[WHERE 條件表達式] GROUP BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC],...[WITH ROOLLUP] HAVING 條件表達式;

語法注意事項：

爲了更好地理解統計篩選的使用，實例如下：

實例：查詢小組平均分高於 85 分的小組

SELECT group_id,AVG(score) FROM score GROUP BY group_id HAVING AVG(score)>=85;

聚合函數

在 MySQL 中，MySQL 所提供的聚合函數在查詢數據時能夠起一些特殊功能，查詢出更有價值的數據記錄。

常用的聚合函數爲了能夠更好地理解上述各個聚合函數的使用，接下來將對其進行逐一講解。

操作表的準備

COUNT 函數

SELECT COUNT(*) FROM 表名稱;      //返回表中的總記錄數

SELECT COUNT(字段名) FROM 表名稱;  //返回表中字段不爲null的記錄數

注意點說明：

爲了能夠更好地理解 COUNT 函數的使用，實例如下：

實例：查詢各個小組的小組成員人數

SELECT group_id,COUNT(*) FROM score GROUP BY group_id;

MIN 和 MAX 函數

SELECT MAX(字段名) FROM 表名稱;   //返回表中該字段的最大值

SELECT MIN(字段名) FROM 表名稱;   //返回表中該字段的最小值

注意點說明：

MIN() 和 MAX() 函數適用於任何的數據類型。

爲了能夠更好地理解 MIN 和 MAX 函數的使用，實例如下：

實例：查詢學生成績的最大值及最小值

SELECT MAX(score),MIN(score) FROM score;

SUM 和 AVG 函數

SELECT SUM(字段名) FROM 表名稱;    //返回表中該字段的總和值

SELECT AVG(字段名) FROM 表名稱;    //返回表中該字段的平均值

注意點說明：

SUM() 和 AVG() 函數只適用於數值型數據類型。

爲了能夠更好地理解 SUM 和 AVG 函數的使用，實例如下：

實例：查詢各個小組學生成績的總和及平均值

SELECT group_id,SUM(score),AVG(score) FROM score GROUP BY group_id;

GROUP_CONCAT 函數

SELECT GROUP_CONCAT(字段名) FROM 表名稱;   //返回符合條件的參數字段的連續字符串

爲了能夠更好地理解 JSON_ARRAYAGG 函數的使用，實例如下：

實例：查詢各個小組的學生姓名信息

SELECT group_id,GROUP_CONCAT(name) FROM score GROUP BY group_id;

JSON_ARRAYAGG 函數

SELECT JSON_ARRAYAGG(字段名) FROM 表名稱;           //將符合條件的參數字段作爲單個JSON數組返回

爲了能夠更好地理解 JSON_ARRAYAGG 函數的使用，實例如下：

實例：查詢各個小組的學生姓名信息

SELECT group_id,JSON_ARRAYAGG(name) FROM score GROUP BY group_id;

JSON_OBJECTAGG 函數

SELECT JSON_OBJECTAGG(鍵名字段,鍵值字段) FROM 表名稱;            //將符合條件的參數字段作爲單個JSON對象返回

爲了能夠更好地理解 JSON_ARRAYAGG 函數的使用，實例如下：

實例：查詢各個小組的學生姓名及成績信息

SELECT group_id,JSON_OBJECTAGG(name,score) FROM score GROUP BY group_id;

擴展: 別名

在 MySQL 中，執行查詢操作時可以爲獲取的字段設置別名，從而來縮短字段的名稱長度和方便閱讀開發。

字段別名設置及使用

爲字段設置別名的基本語法如下：

SELECT 字段1 [AS] 字段別名1, 字段2 [AS] 字段別名2, ... FROM 表名稱;

語法注意事項：

爲了更好地理解字段別名設置及使用，實例如下：

實例：查詢各個小組的成績最高分，將 MAX(score) 該字段設置別名爲 max_score

SELECT group_id,MAX(score) max_score FROM score GROUP BY group_id;

表別名設置及使用

爲表設置別名的基本語法如下：

SELECT 字段列表 FROM 表名稱 [AS] 表別名;

語法注意事項：

爲了更好地理解表別名設置及使用，實例如下：

1，操作表準備

2，實例操作

實例：查詢學生信息 (學號，姓名，班級號)，將 student 表設置別名爲 s，class 表設置別名爲 c

SELECT s.id stu_id,s.name stu_name,c.name AS class_name FROM student s,class c WHERE s.class_id = c.id;

來源：https://blog.csdn.net/m0_64338546/

article/details/12865157

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/dymp8vWtJRjG1z1-8_WFZQ

猜你喜歡