21 天 Python 計劃：MySQL中DML與權限管理

文章目錄

- 前言
- 一、介紹
- 二、MySQL數據操作：DML
- - 2.1 插入數據（INSERT）
  - - 2.1.1 插入完整數據（順序插入）
    - 2.1.2 指定字段插入數據
    - 2.1.3 插入多條記錄
    - 2.1.4 插入查詢結果
  - 2.2 更新數據（UPDATE）
  - 2.3 刪除數據（DELETE）
  - - 練習
  - 2.4 查詢數據（SELECT）
  - - 2.4.1 單表查詢
    - - 基本語法
      - 示例
      - 查詢所有學生的信息
      - 查詢所有學生的姓名和年齡
      - 查詢年齡大于 20 歲的學生信息
    - 2.4.2 多表查詢
    - - 基本類型
      - 內連接（INNER JOIN）
      - 左連接（LEFT JOIN）
      - 右連接（RIGHT JOIN）
      - 全連接（FULL JOIN）
      - 示例
    - 2.4.3 總結
- 三、權限管理
- - 3.1 授權表
  - 3.2 創建用戶
  - 3.3 授權
  - 3.4 刪除權限
- 結語

前言

Python是一種強大且易于學習的編程語言。通過這個21天的計劃，我們將逐步深入MySQL中DML與權限管理。無論你是初學者還是有一定基礎的開發者，這個計劃都將幫助你鞏固和擴展你的Python知識。

在學習本篇之前，我們先復習一下前面的內容：
day1：Python下載和開發工具介紹
day2：數據類型、字符編碼、文件處理
day3：基礎語法與課外練習
day4：函數簡單介紹
day5：模塊與包
day6：常用模塊介紹
day7：面向對象
day8：面向對象高級
day9：異常處理
day10：網絡編程
day11：并發編程
day12：MySQL數據庫初識
day13：MySQL庫相關操作
day14：MySQL表相關操作

一、介紹

在數據庫管理中，數據的操作和權限管理是至關重要的環節。MySQL作為一款廣泛使用的關系型數據庫管理系統，提供了強大的功能來滿足這些需求。本文將詳細介紹MySQL中數據操作語言（DML）的使用，包括數據的插入、更新、刪除和查詢，同時也會涉及到用戶權限管理的相關內容。

二、MySQL數據操作：DML

2.1 插入數據（INSERT）

在MySQL中，使用INSERT語句可以將數據插入到表中，具體有以下幾種方式：

2.1.1 插入完整數據（順序插入）

語法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

這種語法明確指定了要插入數據的字段，然后按照字段順序提供對應的值。

語法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

此語法要求提供的值的順序與表中字段的順序一致，適用于插入所有字段的數據。

2.1.2 指定字段插入數據

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

當只需要插入部分字段的數據時，可以使用這種語法，只指定要插入數據的字段及其對應的值。

2.1.3 插入多條記錄

INSERT INTO 表名 VALUES(值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);

通過這種方式可以一次性插入多條記錄，提高插入效率。

2.1.4 插入查詢結果

INSERT INTO 表名(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2WHERE …;

該語法可以將從另一個表中查詢到的結果插入到指定的表中，方便數據的遷移和整合。

2.2 更新數據（UPDATE）

使用UPDATE語句可以對表中的數據進行更新，語法如下：

UPDATE 表名 SET字段1=值1,字段2=值2,WHERE CONDITION;

其中，SET子句用于指定要更新的字段及其新值，WHERE子句用于指定更新的條件。例如：

UPDATE mysql.user SET password=password('123') where user='root' and host='localhost';

這條語句將mysql.user表中user為root且host為localhost的記錄的password字段更新為加密后的123。

2.3 刪除數據（DELETE）

DELETE語句用于從表中刪除數據，語法如下：

DELETE FROM 表名 WHERE CONITION;

WHERE子句用于指定刪除的條件，如果不指定WHERE子句，則會刪除表中的所有記錄。例如：

DELETE FROM mysql.user WHERE password='';

這條語句將mysql.user表中password為空的記錄刪除。

練習

更新MySQL root用戶密碼為mysql123

UPDATE mysql.user SET password=password('mysql123') WHERE user='root';

刪除除從本地登錄的root用戶以外的所有用戶

DELETE FROM mysql.user WHERE user!='root' OR (user='root' AND host!='localhost');

2.4 查詢數據（SELECT）

在數據庫操作的眾多功能中，查詢數據無疑是使用頻率最高的一項。MySQL作為一款廣泛應用的關系型數據庫管理系統，為我們提供了強大且豐富的查詢功能，其中單表查詢和多表查詢是兩個重要的組成部分。下面我們將詳細探討這兩種查詢方式。

2.4.1 單表查詢

單表查詢是指僅從一個表中獲取數據的查詢操作。它是數據庫查詢中最基礎、最常見的操作，適用于只需要從一個數據源獲取信息的場景。

基本語法

單表查詢的基本語法如下：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT關鍵字用于指定要查詢的列，可以是一個或多個列，用逗號分隔。如果要查詢所有列，可以使用 * 通配符。
FROM關鍵字用于指定要查詢的表名。
WHERE子句是可選的，用于指定查詢條件，只有滿足條件的記錄才會被返回。

示例

假設我們有一個名為students的表，包含 id、name、age和 gender四個列，以下是一些常見的單表查詢示例：

查詢所有學生的信息

SELECT * FROM students;

這個查詢將返回students表中的所有記錄和所有列。

查詢所有學生的姓名和年齡

SELECT name, age FROM students;

這個查詢只返回 name和 age兩列的數據。

查詢年齡大于 20 歲的學生信息

SELECT * FROM students WHERE age > 20;

這個查詢使用 WHERE子句過濾出年齡大于 20 歲的學生記錄。

2.4.2 多表查詢

多表查詢是指從多個表中獲取數據的查詢操作。在實際應用中，數據往往分散在多個表中，通過多表查詢可以將這些數據關聯起來，獲取更全面的信息。

基本類型

多表查詢主要有以下幾種類型：

內連接（INNER JOIN）

內連接是最常用的多表查詢類型，它只返回兩個表中滿足連接條件的記錄。

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

左連接（LEFT JOIN）

左連接返回左表中的所有記錄，以及右表中滿足連接條件的記錄。如果右表中沒有匹配的記錄，則用 NULL填充。

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

右連接（RIGHT JOIN）

右連接與左連接相反，它返回右表中的所有記錄，以及左表中滿足連接條件的記錄。如果左表中沒有匹配的記錄，則用 NULL填充。

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

全連接（FULL JOIN）

全連接返回兩個表中的所有記錄，無論是否滿足連接條件。如果沒有匹配的記錄，則用 NULL填充。

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

示例

假設我們有兩個表：orders 表包含 order_id、customer_id 和 order_date列，customers表包含 customer_id、customer_name和 customer_email列。以下是一個內連接的示例：

SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

這個查詢將返回每個訂單的訂單號、客戶姓名和訂單日期。

2.4.3 總結

單表查詢和多表查詢是 MySQL 中非常重要的查詢功能。單表查詢適用于從單個數據源獲取信息，而多表查詢則可以將多個表中的數據關聯起來，獲取更全面的信息。通過不斷學習和實踐，我們可以熟練掌握這兩種查詢方式，更好地處理和分析數據庫中的數據。

三、權限管理

在MySQL中，權限管理是保障數據安全的重要手段，通過授權表來控制用戶對數據庫、表和字段的訪問權限。

3.1 授權表

user：該表放行的權限，針對所有數據，所有庫下所有表，以及表下的所有字段。
db：該表放行的權限，針對某一數據庫，該數據庫下的所有表，以及表下的所有字段。
tables_priv：該表放行的權限，針對某一張表，以及該表下的所有字段。
columns_priv：該表放行的權限，針對某一個字段。

3.2 創建用戶

create user 'xiao'@'1.1.1.1' identified by '123';
create user 'xiao'@'192.168.1.%' identified by '123';
create user 'xiao'@'%' identified by '123';

以上語句分別創建了可以從1.1.1.1、192.168.1網段和任意地址登錄的用戶xiao，密碼均為123。

3.3 授權

常用權限有select、update、alter、delete，all可以代表除了grant之外的所有權限。

針對所有庫的授權：

grant select on *.* to 'xiao1'@'localhost' identified by '123';

該語句授予用戶xiao1在本地登錄時對所有數據庫的select權限，只在user表中可以查到該用戶的select權限被設置為Y。

針對某一數據庫：

grant select on db1.* to 'xiao2'@'%' identified by '123';

此語句授予用戶xiao2對數據庫db1的所有表的select權限，只在db表中可以查到該用戶的select權限被設置為Y。

針對某一個表：

grant select on db1.t1 to 'xiao3'@'%' identified by '123';

該語句授予用戶xiao3對數據庫db1中表t1的select權限，只在tables_priv表中可以查到該用戶的select權限。

針對某一個字段：

grant select (id,name),update (age) on db1.t3 to 'xiao4'@'localhost' identified by '123';

此語句授予用戶xiao4在本地登錄時對數據庫db1中表t3的id和name字段的select權限，以及age字段的update權限，可以在tables_priv和columns_priv中看到相應的權限。

3.4 刪除權限

revoke select on db1.* from 'xiao'@'%';

該語句撤銷用戶xiao從任意地址登錄時對數據庫db1的所有表的select權限。

結語

通過這個21天的Python計劃，我們了解了MySQL中數據操作語言（DML）的使用，包括數據的插入、更新、刪除和查詢，以及用戶權限管理的相關內容。希望這些內容能幫助你更好地理解和使用Python。繼續學習和實踐，你將成為一名優秀的Python開發者！

📢 注意啦！文末有彩蛋！參與評論就有機會把這本好書抱回家～動動手指，說不定下個錦鯉就是你！贈書福利