MySQL

llz233發表於2024-11-16
MySql

MySQL

RDBMS 術語

資料庫: 資料庫是一些關聯表的集合
資料表: 表是資料的矩陣
列: 一列包含了相同型別的資料
行：一行一組相關的資料
冗餘：儲存兩倍資料
主鍵：主鍵是唯一的,可以用主鍵來查詢資料
外來鍵：外來鍵用於關聯兩個表
複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引
索引：使用索引可快速訪問資料庫表中的特定資訊,類似於書籍的目錄
參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證資料的一致性

啟動及關閉 MySQL

啟動

sudo systemctl start mysql

關閉

sudo systemctl stop mysql

重啟

sudo systemctl restart mysql

檢查

sudo systemctl status mysql

MySQL連線

mysql -u username -p

執行 SQL 查詢

列出可用的資料庫

SHOW DATEBASES；

選擇需要使用的資料庫

USE datebase;

列出所選資料中的所有表

SHOW TABLES;

檢視錶的結構

DESC table_name;

檢視錶的索引

SHOW INDEX FROM table_name;

檢視錶的建立語句

SHOW CREATE TABLE table_name;

檢視錶的行數

SELECT COUNT(*) FROM table_name;

退出

mysql>EXIT;/QUIT;

建立資料庫、資料表

CREATE DATABASE 資料庫名
CHARACTER SET 字符集名稱
COLLATE 校對規則；

字符集是可以識別、儲存和表示的字元和符號的集合，

校對規則是字符集內部字元比較和排序的規則。

CREATE TABLE 資料表名(
表名1，資料型別1，
表名2，資料型別2，
)；

NOT NULL 欄位不為空；

AUTO_INCREMENT 用於建立自增長的列；

PRIMARY KEY 用於定義主鍵；

ENGINE 設定儲存引擎，CHARSET 設定編碼

使用mysqladmin建立資料庫

mysqladmin -u 使用者名稱 -p 資料庫名\
  --default-character-set=字符集名稱\
  --default-collation=校對規則

刪除資料庫、資料表

DROP DATABASE/TABLE 資料庫/表名

插入資料

INSERT INTO 表名(列名1，2，3)
VALUES(具體數值1，2，3),
      (.....);

查詢資料

SELECT 列名 FROM 表名；

查詢所有列時用*表示

WHERE 子句

SELECT 列名 
FROM 表名 
WHERE 過濾條件

  • 查詢多個表用，隔開
  • <> != 相當於不等於
  • 組合條件（AND,OR)
  • 模糊匹配條件（LINK)
  • IN條件（和=類似）
  • BETWEEN AND
  • IS NULL / IS NOT NULL

UPDATE 更新

UPDATE 表名
SET 列名 = 新的值
WHERE 指定要更改的行

省略WHERE將更新所有行，可以更新多列

image-20241024170416966

QQ截圖20241024170138

DELETE 語句

DELETE FROM 表名
WHERE 指定要刪除的行

省略WHERE刪除所有行，

可以使用子查詢刪除符合條件的行

LIKE 子句

SELECT 要選擇的列名列名
FROM 表名
WHERE 要應用LIKE子句的列名 LIKE 匹配模式

萬用字元：

%：表示零個或多個任意字元
_：表示一個任意字元

不使用%和_時，LINK與 = 效果一樣

SELECT * FROM uuu WHERE name LIKE _a%o

查詢uuu中，第二個字元是a以o結尾的name

UNION 運算子

SELECT 列名 FROM 表名
UNION
SELECT 列名 FROM 表名
  • 使用 UNION ALL 不去除重複行
  • UNION 操作中的列數和資料型別必須相同
  • 可以透過 UNION 過濾條件

ORDER BY 語句

SELECT 列名
FROM 表名
ORDER BY 指定排序順序的表名

  • ASC 為升序(預設)，DESC 為降序

  • 可以多列排序，哪個在前先按哪個排，在內容一樣時再考慮下一項

  • 用數字表示列的位置

    其中，weight 為第二列，height 為第三列

  • 使用表示式排序

  • 從 MySQL 8.0.16 版本開始，可以使用 NULLS FIRST 或 NULLS LAST 處理 NULL 值

GROUP BY 語句

SELECT 列名,對分組後的每個組執行的聚合函式
FROM 表名
WHERE(可選，用於過濾條件)
GROUP BY 列名

  • 聚合函式：

    SUM() AVG() MIN() MAX() COUNT()

  • 按country分組，並統計每個國家有幾個

  • 按name分組，並計算每個人的平均身高

  • WITH ROLLUP 可實現在分組統計資料基礎上再進行相同的統計，

    其中記錄 NULL 表示所有人的登入次數，

    我們可以使用 coalesce 來設定一個可以取代 NUll 的名稱

coalesce(a,b,c)

連線的使用

INNER JOIN

SELECT column1,column2,...
FROM table1 AS 1
INNER JOIN table2 AS 2 ON 1.column1_name=2.column2_name;

  • 使用表別名

  • 多表連線
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;
  • 使用 WHERE 子句進行過濾

LEFT JOIN

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN 會讀取左邊資料表的全部資料，即使右邊表無對應資料；當右表中沒有匹配的行時，相關列將顯示為 NULL

RIGHT JOIN

SELECT column1，column2...
FROM table1
RIGHT JOIN table2 ON table1.column_name=table2.column_name;

RIGHT JOIN 會讀取右邊資料表的全部資料，即使左邊邊表無對應資料；RIGHT JOIN 並不經常使用，因為它可以用 LEFT JOIN 和表的順序交換來實現相同的效果。

NULL值處理

IS NULL： 當列的值是 NULL，此運算子返回 true。
IS NOT NULL： 當列的值不為 NULL， 運算子返回 true。
<=>： 比較運算子（不同於 = 運算子），當比較的兩個值相等或者都為 NULL 時返回 true。

正規表示式

MySQL 可以透過 LIKE ...% 來進行模糊匹配；

MySQL 同樣也支援其他正規表示式的匹配， MySQL 中使用 REGEXP 和 RLIKE運算子來進行正規表示式匹配

RLIKE 和 REGEXP

RLIKE 是 MySQL 中用於進行正規表示式匹配的運算子，與 REGEXP 是一樣的，RLIKE 和 REGEXP 可以互換使用，沒有區別

REGEXP

SELECT column1,column2,...
FROM table_name
WHERE column_name REGEXP 'pattern';

RLIKE

SELECT column1,column2,...
FROM table_name
WHERE column_name RLIKE 'pattern';

正則模式

正規表示式匹配的字元類

  • .：匹配任意單個字元
  • ^：匹配字串的開始
  • $：匹配字串的結束
  • *：匹配零個或多個前面的元素
  • +：匹配一個或多個前面的元素
  • ?：匹配零個或一個前面的元素
  • [abc]：匹配字符集中的任意一個字元
  • [^abc]：匹配除了字符集中的任意一個字元以外的字元
  • [a-z]：匹配範圍內的任意一個小寫字母
  • [0-9]：匹配一個數字字元
  • \w：匹配一個字母數字字元（包括下劃線）
  • \s：匹配一個空白字元

MySQL 事務

MySQL 事務主要用於處理操作量大，複雜度高的資料

在 MySQL 中，事務是一組SQL語句的執行，它們被視為一個單獨的工作單元

  • 在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務
  • 事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行
  • 事務用來管理 insert、update、delete 語句

事務是必須滿足4個條件（ACID）：原子性、一致性、隔離性、永續性

原子性：一個事務中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。

隔離性：資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交、讀提交、可重複讀和序列化。

永續性：事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丟失。

MYSQL 事務處理主要有兩種方法

1、用 BEGIN, ROLLBACK, COMMIT 來實現

BEGIN 或 START TRANSACTION：開用於開始一個事務
ROLLBACK 事務回滾，取消之前的更改
COMMIT：事務確認，提交事務，使更改永久生效

2、直接用 SET 來改變 MySQL 的自動提交模式:

SET AUTOCOMMIT=0 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交

開始事務

BENGIN/START TRANSACTION;

提交事務

COMMIT;

回滾事務，撤銷自上次提交以來所做的所有更改

ROLLBACK;

在事務中設定儲存點，以便稍後能夠回滾到該點

SAVEPOINT savepoint_name;

回滾到之前設定的儲存點

ROLLBACK TO SAVEPOINT savepoint_name;

ALTER 命令

新增列

ALTER TABLE table_name
ADD COLUMN new_column_name datatpye

修改列的資料型別

ALTER TABLE table_name
MODIFY COLUMN

修改列名

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

刪除列

ALTER TABLE table_name
DROP COLUMN column_name

新增 PRIMARY KEY

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

新增 FOREIGN KEY

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

修改表名

ALTER TABLE old_table_name
RENAME TO new_table_name;

索引

MySQL 索引類似於書籍的索引，透過儲存指向資料行的指標，可以快速定位和訪問表中的特定資料

單列索引和組合索引

單列索引，即一個索引只包含單個列，一個表可以有多個單列索引

組合索引，即一個索引包含多個列

建立索引

CREATE INDEX index_name
ON table_name (column1[ASC|DESC],column2[ASC|DESC],...);

預設情況 索引升序ASC

新增索引

使用 ALTER TABLE 命令可以在已有的表中建立索引

ALTER TABLE table_name
ADD INDEX index_name (column1[ASC|DESC],column2[ASC|DESC],...);

建立表的時候直接指定

CREATE TABLE table_name(
column1 data_type,
column2 data_type,
...,
INDEX index_name(column1[ASC|DESC],column2[ASC|DESC],...)
)

刪除索引的語法

DROP INDEX index_name ON table_name;

使用 ALTER TABLE 語句刪除索引

ALTER TABLE table_name
DROP INDEX index_name;

顯示索引資訊

SHOW INDEX FROM table_name\G

命令大全

資料庫相關命令

資料表相關命令

其他：

  • 重要的地方要大寫，末尾加；才能執行
  • 命令後加IF NOT EXISTS ，避免執行出現錯誤
  • 使用mysqladmin前要先保證MySQL在執行
mysqladmin -u 使用者名稱 -p 命令

  • 建立/刪除資料表前要先選擇資料庫

  • 不寫列名連線不到

  • 查詢中的字串記住要加引號

  • date < 2006-12-23 是指之前的時間

  • COLLATE utf8mb4_general_ci 不區分大小寫

  • 插入多行資料，要在一行資料後加上，

  • 聚合函式:

    SUM()：計算某列總和

    AVG()：計算平均值

    MIN()：返回最小值

    MAX()：返回最大值

    COUNT()：計算某列行數

相關文章