新聞推薦實戰（一）：MySQL 基礎

前文萬字入門推薦系統提到了後續內容圍繞兩大系列：推薦算法理論 + 新聞推薦實戰。

新聞推薦實戰大綱

本文屬於新聞推薦實戰—數據層—構建物料池之 MySQL。MySQL 數據庫在該項目中會用來存儲結構化的數據（用戶、新聞特徵），作爲算法工程師需要了解常用的 MySQL 語法（比如增刪改查，排序等），因爲在實際的工作經常會用來統計相關數據或者抽取相關特徵。本着這個目的，本文對 MySQL 常見的語法及 Python 操作 MySQL 進行了總結，方便大家快速瞭解。文末附上參考資料

本文目錄如下：

1.1 安裝
1.2 配置 MySQL 的安全性
1.3 以 root 用戶登錄
1.4 修改密碼
1.5 撤銷用戶授權
1.6 刪除用戶
2.1 SQL 書寫規範
2.2 命名規則
2.3 數據類型
（1）數值類型
（2）日期和時間類型
（3）字符串類型
3.1 數據庫的創建
3.2 數據庫的查看
3.3 選擇數據庫
3.4 刪除數據庫
4.1 表的創建
4.2 表的刪除
4.3 表的更新
4.4 表的查詢
4.5 表的複製
5.1 算術運算符
5.2 比較運算符
5.3 邏輯運算符
6.1 聚合函數
6.2 對錶分組
6.3 使用 WHERE 語句
6.4 爲聚合結果指定條件
6.5 對錶的查詢結果進行排序
7.1 數據的插入
7.2 數據的刪除
7.3 數據的更新
8.1 安裝 pymysql
8.2 連接數據庫
8.3 創建遊標
8.4 類方法
8.5 實戰

前言 MySQL 簡介

MySQL 是一個關係型數據庫管理系統，由瑞典 MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型數據庫管理系統之一，在 WEB 應用方面，MySQL 是最好的 RDBMS (Relational Database Management System，關係數據庫管理系統) 應用軟件之一。

MySQL 在過去由於性能高、成本低、可靠性好，已經成爲最流行的開源數據庫，因此被廣泛地應用在 Internet 上的中小型網站中。隨着 MySQL 的不斷成熟，它也逐漸用於更多大規模網站和應用，比如維基百科、Google 和 Facebook 等網站。非常流行的開源軟件組合 LAMP 中的 “M” 指的就是 MySQL。

一、 Ubuntu 下安裝 MySQL

安裝教程是在Ubuntu20.04下進行的，安裝的 MySQL 版本爲8.0.27。

1.1 安裝

首先，在終端輸入如下的命令：

sudo apt install mysql-server mysql-client

再輸入y即可開始安裝。

安裝完成後，通過運行命令mysql -V查看版本號：

lyons@ubuntu:~$ mysql -V
mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

驗證 MySQL 服務正在運行，命令行下輸入：

sudo service mysql status

如果正在運行，則會顯示：

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2021-10-27 10:27:59 CST; 9h ago
   Main PID: 6179 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 4599)
     Memory: 348.9M
     CGroup: /system.slice/mysql.service
             └─6179 /usr/sbin/mysqld

10月 27 10:27:59 ubuntu systemd[1]: Starting MySQL Community Server...
10月 27 10:27:59 ubuntu systemd[1]: Started MySQL Community Server.

Active欄顯示active(running)表示 MySQL 正在運行。之後，輸入Ctrl+C退出。

1.2 配置 MySQL 的安全性

首先，在終端運行命令mysql_secure_installation：
```
sudo mysql_secure_installation
```

VALIDATE PASSWORD COMPONENT

設置驗證密碼插件。它被用來測試MySQL用戶的密碼強度，並且提高安全性。如果想設置驗證密碼插件，請輸入y：

Connecting to MySQL using a blank password.
    
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
    
Press y|Y for Yes, any other key for No: y

接下來，將進行密碼驗證等級設置，根據數字設置對應等級，這裏設置爲 0：

There are three levels of password validation policy:
    
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
    
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

設置密碼

爲 MySQL root 用戶設置密碼，設置過程中密碼不會顯示。如果需要設置驗證密碼插件，請輸入y，在後面將會顯示密碼的強度。

Please set the password for root here.
New password: 
    
Re-enter new password: 
    
Estimated strength of the password: 25 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

移除匿名用戶

默認情況下，MySQL 安裝有一個匿名用戶，允許任何人登錄 MySQL，而不必爲他們創建用戶帳戶。輸入y進行刪除：

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
    
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

禁止遠程 root 用戶登錄

輸入y後按enter，將會禁止root用戶登錄。

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
    
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

刪除測試庫

輸入y後按enter，將會刪除測試庫。

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
    
    
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

重新加載特權表

輸入y後按enter，將會重新加載特權表。

 - Removing privileges on test database...
Success.
    
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
    
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
    
All done!

至此，配置完成。

1.3 以 root 用戶登錄

在 MySQL 8.0 上，root 用戶默認通過auth_socket插件授權。auth_socket插件通過 Unix socket 文件來驗證所有連接到localhost的用戶。

這意味着你不能通過提供密碼，驗證爲 root。此時，輸入mysql -uroot -p可能會被拒絕訪問：

lyons@ubuntu:~$ mysql -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

若要以 root 用戶身份登錄 MySQL 服務器，輸入sudo mysql，如下：

# 登錄密碼爲linux系統用戶的root密碼
lyons@ubuntu:~$ sudo mysql
[sudo] lyons 的密碼： 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

退出 MySQL，請輸入exit命令：

mysql> exit
Bye
lyons@ubuntu:~$

如果你想以 root 身份登錄 MySQL 服務器，使用其他的程序，有兩個選擇。

方式 1

修改登錄root用戶的密碼驗證方法從auth_socket修改成mysql_native_password。

首先通過sudo mysql進入 MySQL，然後在 MySQL 下將密碼的驗證方式設置爲mysql_native_password，命令如下：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';
FLUSH PRIVILEGES;

示例：

-- 首先要以root的身份登錄mysql
-- sudo mysql

-- 設置root用戶的登陸密碼，方式爲mysql_native_password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql123';
Query OK, 0 rows affected (0.00 sec)

-- 刷新系統權限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

-- 退出
mysql> exit
Bye

通過mysql -uroot -p登錄：

# 登錄密碼爲前面設置的mysql123

lyons@ubuntu:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

如果要改回通過sudo mysql驗證root用戶登錄，將 root 用戶的密碼驗證方式改爲auth_socket即可：

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket BY '你的密碼';

方式 2

推薦的選項，就是創建一個新的獨立管理用戶，擁有所有數據庫的訪問權限。核心命令如下：

-- 1. 創建用戶admin
CREATE USER 'admin'@'localhost' IDENTIFIED BY '你的密碼';

-- 2. 賦予admin用戶
--    with gran option表示該用戶可給其它用戶賦予權限，但不可能超過該用戶已有的權限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' with grant option;

其中，'admin'@'localhost'中，admin爲用戶名，你可以自定義。localhost指本地纔可連接，可以將其換成%指任意ip都能連接，也可以指定ip連接。

示例：

-- 首先要以root的身份登錄mysql

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'mysql123';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

-- 此時再查看mysql下的user表
-- 會發現多了一名用戶admin
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | localhost |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

退出root用戶，使用admin用戶登陸：

lyons@ubuntu:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1.4 修改密碼

將用戶admin的登錄密碼修改爲mysql321：

ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';

你可以根據自己對應的用戶名和 host 去修改密碼，mysql_native_password指定的是密碼驗證方式。

1.5 撤銷用戶授權

以用戶admin舉例，撤銷該用戶的權限：

-- 查看用戶的權限
SHOW GRANTS FOR 'admin'@'localhost';

-- 撤銷用戶所有的權限
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';

示例：

-- 查看權限
mysql> SHOW GRANTS FOR 'admin'@'localhost' \G
*************************** 1. row ***************************
Grants for admin@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for admin@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`localhost` WITH GRANT OPTION
2 rows in set (0.00 sec)

-- 取消用戶的權限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';
Query OK, 0 rows affected (0.01 sec)

-- 再次查看權限
mysql> SHOW GRANTS FOR 'admin'@'localhost' \G
*************************** 1. row ***************************
Grants for admin@localhost: GRANT USAGE ON *.* TO `admin`@`localhost`
1 row in set (0.00 sec)

1.6 刪除用戶

以用戶admin舉例，刪除該用戶：

-- 刪除用戶後，無法再用該用戶登陸
DROP USER 'admin'@'localhost';

注：MySQL 8.0 版本和 5.0 部分命令有所改掉，上述語法都是在 8.0 版本下運行通過的；請務必檢查自己的 MySQL 版本號。

二、MySQL 預備知識

在正式學習 MySQL 之前，我們先來了解一下 SQL 語句的書寫規範以及命名規則等。

2.1 SQL 書寫規範

在寫 SQL 語句時，要求按照如下規範進行：

SQL 語句要以分號（;）結尾
SQL 不區分關鍵字的大小寫，這對於表名和列名同樣適用。
插入到表中的數據是區分大小寫的。例如，數據 Computer、COMPUTER 或 computer，三者是不一樣的。
常數的書寫方式是固定的，在 SQL 語句中直接書寫的字符串、日期或者數字等稱爲常數。常數的書寫方式如下所示。
SQL 語句中含有字符串的時候，需要像'abc'這樣，使用單引號（'）將字符串括起來，用來標識這是一個字符串。
SQL 語句中含有日期的時候，同樣需要使用單引號將其括起來。日期的格式有很多種（'26 Jan 2010' 或者'10/01/26' 等）。
在 SQL 語句中書寫數字的時候，不需要使用任何符號標識，直接寫成 1000 這樣的數字即可。
單詞之間需要用半角空格或者換行來分隔。
SQL 中的註釋主要採用--和/* ... */的方式，第二種方式可以換行。在 MySQL 下，還可以通過#來進行註釋。

2.2 命名規則

在數據庫中，只能使用半角英文字母、數字、下劃線（_）作爲數據庫、表和列的名稱。
名稱必須以半角英文字母作爲開頭。
名稱不能重複，同一個數據庫下不能有 2 張相同的表。

2.3 數據類型

MySQL 支持所有標準 SQL 數值數據類型，包括：

（1）數值類型

數值包含的類型如下：

整型數據：TINYINT、INTEGER、SMALLINT、MEDIUMINT、DECIMAL 、NUMERIC 和BIGINT。
浮點型數據：DECIMAL、FLOAT、REAL 和 DOUBLE PRECISION)。

其中，關鍵字INT是INTEGER的同義詞，關鍵字 DEC 是的同義詞。

不同關鍵字的主要區別就是表示的範圍或精度不一樣。具體如下表：

JVHQsw

（2）日期和時間類型

表示時間值的日期和時間類型爲DATETIME、DATE、TIMESTAMP、TIME和YEAR。具體如下表：

tG4Ac8

（3）字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。具體如下表：

zAt9tX

char聲明的是定長字符串。若實際中字符串長度不足，則會在末尾使用空格進行填充至聲明的長度。
varchar聲明的是可變長字符串。存儲過程中，只會按照字符串的實際長度來存儲。另加一個字節來記錄長度 (如果字段聲明的長度超過 255，則使用兩個字節)。

三、數據庫的基本操作

首先，我們來學習在 MySQL 下如何操作數據庫。

3.1 數據庫的創建

通過CREATE命令，可以創建指定名稱的數據庫，語法結構如下：

CREATE DATABASE [IF NOT EXISTS] <數據庫名稱>;

MySQL 的數據存儲區將以目錄方式表示 MySQL 數據庫，因此數據庫名稱必須符合操作系統的文件夾命名規則，不能以數字開頭，儘量要有實際意義。

MySQL 下不運行存在兩個相同名字的數據庫，否則會報錯。如果使用IF NOT EXISTS（可選項），可以避免此類錯誤。

示例：

-- 創建名爲shop的數據庫。
CREATE DATABASE shop;

3.2 數據庫的查看

查看所有存在的數據庫

SHOW DATABASES [LIKE '數據庫名'];;

LIKE從句是可選項，用於匹配指定的數據庫名稱。LIKE 從句可以部分匹配，也可以完全匹配。

示例：

SHOW DATABASES;

-- 結果如下：
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

-- %表示任意0個或多個字符，可匹配任意類型和長度的字符。
SHOW DATABASES LIKE 'S%';

-- 結果如下
+---------------+
| Database (S%) |
+---------------+
| shop          |
| sys           |
+---------------+
2 rows in set (0.00 sec)

查看創建的數據庫

SHOW CREATE DATABASE <數據庫名>;

示例：

SHOW CREATE DATABASE shop;

-- 或者
SHOW CREATE DATABASE shop \G

-- 結果如下
*************************** 1. row ***************************
       Database: shop
Create Database: CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

CHARACTER SET utf8mb4表示編碼字符集爲utf8mb4。

3.3 選擇數據庫

在操作數據庫前，必須指定所要操作的數據庫。通過USE命令，可以切換到對應的數據庫下。

USE <數據庫名>

示例：

-- 切換到數據庫shop下。
USE shop;

-- 結果如下
Database changed

3.4 刪除數據庫

通過DROP命令，可以將相應數據庫進行刪除。

DROP DATABASE [IF EXISTS] <數據庫名>

其中，IF EXISTS爲可選性，用於防止數據庫不存在時報錯。

示例：

DROP DATABASE shop;

SHOW DATABASES;

考慮到後面表的操作都是 shop 數據庫下，在實驗完DROP刪除數據庫命令後，請從新創建數據庫 shop 並通過USE命令切換到該數據庫下。

四、表的基本操作

表相當於文件，表中的一條記錄就相當於文件的一行內容，不同的是，表中的一條記錄有對應的標題，稱爲表的字段。

4.1 表的創建

創建表的語法結構如下：

CREATE TABLE <表名> （<字段1> <數據類型> <該列所需約束>，
   <字段2> <數據類型> <該列所需約束>，
   <字段3> <數據類型> <該列所需約束>，
   <字段4> <數據類型> <該列所需約束>，
   .
   .
   .
   <該表的約束1>， <該表的約束2>，……）；

示例：

-- 創建一個名爲Product的表
CREATE TABLE Product(
  product_id CHAR(4) NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  product_type VARCHAR(32) NOT NULL,
  sale_price INT,
  purchase_price INT,
  regist_date DATE,
  PRIMARY KEY (product_id)
);

在第二章中，我們介紹過不同的數據類型：

CHAR爲定長字符，這裏CHAR旁邊括號裏的數字表示該字段最長爲多少字符，少於該數字將會使用空格進行填充。
VARCHAR表示變長字符，括號裏的數字表示該字段最長爲多少字符，存儲時只會按照字符的實際長度來存儲，但會使用額外的 1-2 字節來存儲值長度。

簡單介紹一下該語句中出現的約束條件：

PRIMARY KEY：主鍵，表示該字段對應的內容唯一且不能爲空。
NOT NULL：在 NULL 之前加上了表示否定的NOT，表示該字段不能輸入空白。

關於各類約束條件的具體含義和用法，可以參考菜鳥 SQL 教程中的約束，文末附有參考鏈接。

通過SHOW TABLES命令來查看當前數據庫下的所有的表名：

SHOW TABLES;

-- 結果如下
+----------------+
| Tables_in_shop |
+----------------+
| Product        |
+----------------+
1 rows in set (0.00 sec)

通過DESC <表名>來查看錶的結構：

DESC Product;

-- 結果如下
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id     | char(4)      | NO   | PRI | NULL    |       |
| product_name   | varchar(100) | NO   |     | NULL    |       |
| product_type   | varchar(32)  | NO   |     | NULL    |       |
| sale_price     | int          | YES  |     | NULL    |       |
| purchase_price | int          | YES  |     | NULL    |       |
| regist_date    | date         | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

4.2 表的刪除

刪除表的語法結構如下：

DROP TABLE <表名>;

-- 例如：DROP TABLE Product;

說明：通過DROP刪除的表示無法恢復的，在刪除表的時候請謹慎。

4.3 表的更新

通過ALTER TABLE語句，我們可以對錶字段進行不同的操作，下面通過示例來具體學習用法。

示例：

創建一張名爲 Student 的表

CREATE TABLE Student(
  id INT PRIMARY KEY,
  name CHAR(15)
);

DESC student;

-- 結果如下
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(15) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改表名

通過RENAME命令，將表名從 Student => Students。

ALTER TABLE Student RENAME Students;

插入新的字段

通過ADD命令，新增字段 sex 和 age。

-- 不同的字段通過逗號分開
ALTER TABLE Students ADD sex CHAR(1), ADD age INT;

其它插入技巧：

-- 通過FIRST在表首插入字段stu_num
ALTER TABLE Students ADD stu_num INT FIRST;

-- 指定在字段sex後插入字段height
ALTER TABLE Students ADD height INT AFTER sex;

DESC Students;

-- 結果如下
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int      | YES  |     | NULL    |       |
| id      | int      | NO   | PRI | NULL    |       |
| name    | char(15) | YES  |     | NULL    |       |
| sex     | char(1)  | YES  |     | NULL    |       |
| height  | int      | YES  |     | NULL    |       |
| age     | int      | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)

字段的刪除

通過DROP命令，可以對不在需要的字段進行刪除。

-- 刪除字段stu_num
ALTER TABLE Students DROP stu_num;

字段的修改

通過MODIFY修改字段的數據類型。

-- 修改字段age的數據類型
ALTER TABLE Students MODIFY age CHAR(3);

通過CHANGE命令，修改字段名或類型

-- 修改字段name爲stu_name，不修改數據類型
ALTER TABLE Students CHANGE name stu_name CHAR(15);

-- 修改字段sex爲stu_sex，數據類型修改爲int
ALTER TABLE Students CHANGE sex stu_sex INT;

DESC Students;

-- 結果如下
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int      | NO   | PRI | NULL    |       |
| stu_name | char(20) | YES  |     | NULL    |       |
| stu_sex  | int      | YES  |     | NULL    |       |
| height   | int      | YES  |     | NULL    |       |
| age      | char(3)  | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4.4 表的查詢

通過SELECT語句，可以從表中取出所要查看的字段的內容：

SELECT <字段名>, ……
 FROM <表名>;

如要直接查詢表的全部字段：

SELECT *
 FROM <表名>;

其中，** 星號（*）** 代表全部字段的意思。

示例：

建表並插入數據

在 MySQL 中，我們通過INSERT語句往表中插入數據，該語句在後面會詳細介紹，該小節的重點是學會使用SELECT。

-- 向Product表中插入數據
INSERT INTO Product VALUES
  ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),
  ('0002', '打孔器', '辦公用品', 500, 320, '2009-09-11'),
  ('0003', '運動T恤', '衣服', 4000, 2800, NULL),
  ('0004', '菜刀', '廚房用具', 3000, 2800, '2009-09-20'),
  ('0005', '高壓鍋', '廚房用具', 6800, 5000, '2009-01-15'),
  ('0006', '叉子', '廚房用具', 500, NULL, '2009-09-20'),
  ('0007', '擦菜板', '廚房用具', 880, 790, '2008-04-28'),
  ('0008', '圓珠筆', '辦公用品', 100, NULL,'2009-11-11')
 ;

查看錶的內容

-- 查看錶的全部內容
SELECT * 
 FROM Product;

-- 結果如下
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圓珠筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

-- 查看部分字段包含的內容
SELECT 
  product_id,
  product_name,
  sale_price 
 FROM Product;
 
-- 結果如下
+------------+--------------+------------+
| product_id | product_name | sale_price |
+------------+--------------+------------+
| 0001       | T恤衫        |       1000 |
| 0002       | 打孔器       |        500 |
| 0003       | 運動T恤      |       4000 |
| 0004       | 菜刀         |       3000 |
| 0005       | 高壓鍋       |       6800 |
| 0006       | 叉子         |        500 |
| 0007       | 擦菜板       |        880 |
| 0008       | 圓珠筆       |        100 |
+------------+--------------+------------+
8 rows in set (0.00 sec)

對查看的字段從新命名

通過AS語句對展示的字段另起別名，這不會修改表內字段的名字。

SELECT 
  product_id AS ID,
  product_type AS TYPE
 FROM Product;
	
-- 結果如下
+------+--------------+
| ID   | TYPE         |
+------+--------------+
| 0001 | 衣服         |
| 0002 | 辦公用品     |
| 0003 | 衣服         |
| 0004 | 廚房用具     |
| 0005 | 廚房用具     |
| 0006 | 廚房用具     |
| 0007 | 廚房用具     |
| 0008 | 辦公用品     |
+------+--------------+
8 rows in set (0.00 sec)

設定漢語別名時需要使用雙引號（"）括起來，英文字符則不需要。

SELECT  
  product_id AS "產品編號",
  product_type AS "產品類型"  
 FROM Product;

常數的查詢

SELECT子句中，除了可以寫字段外，還可以寫常數。

SELECT 
  '商品' AS string,
  '2009-05-24' AS date,
  product_id,
  product_name
 FROM Product;
	
-- 結果如下
+--------+------------+------------+--------------+
| string | date       | product_id | product_name |
+--------+------------+------------+--------------+
| 商品   | 2009-05-24 | 0001       | T恤衫        |
| 商品   | 2009-05-24 | 0002       | 打孔器       |
| 商品   | 2009-05-24 | 0003       | 運動T恤      |
| 商品   | 2009-05-24 | 0004       | 菜刀         |
| 商品   | 2009-05-24 | 0005       | 高壓鍋       |
| 商品   | 2009-05-24 | 0006       | 叉子         |
| 商品   | 2009-05-24 | 0007       | 擦菜板       |
| 商品   | 2009-05-24 | 0008       | 圓珠筆       |
+--------+------------+------------+--------------+
8 rows in set (0.00 sec)

刪除重複行

在SELECT語句中使用DISTINCT可以去除重複行。

SELECT 
  DISTINCT regist_date 
 FROM Product;

-- 結果如下
+-------------+
| regist_date |
+-------------+
| 2009-09-20  |
| 2009-09-11  |
| NULL        |
| 2009-01-15  |
| 2008-04-28  |
| 2009-11-11  |
+-------------+
6 rows in set (0.01 sec)

在使用DISTINCT 時，NULL也被視爲一類數據。NULL存在於多行中時，會被合併爲一條NULL數據。

還可以通過組合使用，來去除列組合重複的數據。DISTINCT關鍵字只能用在第一個列名之前。

SELECT 
  DISTINCT product_type, regist_date
 FROM Product;

-- 結果如下，列出了所有的組合
+--------------+-------------+
| product_type | regist_date |
+--------------+-------------+
| 衣服         | 2009-09-20  |
| 辦公用品     | 2009-09-11  |
| 衣服         | NULL        |
| 廚房用具     | 2009-09-20  |
| 廚房用具     | 2009-01-15  |
| 廚房用具     | 2008-04-28  |
| 辦公用品     | 2009-11-11  |
+--------------+-------------+
7 rows in set (0.00 sec)

指定查詢條件

首先通過WHERE 子句查詢出符合指定條件的記錄，然後再選取出SELECT語句指定的列，語法結構如下：

SELECT <字段名>, ……
  FROM <表名>
 WHERE <條件表達式>;

示例：

SELECT product_name
  FROM Product
 WHERE product_type = '衣服';
	
-- 結果如下
+--------------+
| product_name |
+--------------+
| T恤衫        |
| 運動T恤      |
+--------------+
2 rows in set (0.01 sec)

注意，WHERE子句要緊跟在FROM子句之後。

4.5 表的複製

表的複製可以將表結構與表中的數據全部複製，或者只複製表的結構。

-- 將整個表複製過來
CREATE TABLE Product_COPY1
	SELECT * FROM Product;

SELECT * FROM Product_COPY1;

-- 結果如下
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圓珠筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

-- 通過LIKE複製表結構
CREATE TABLE Product_COPY2
	LIKe Product;

SELECT * FROM Product_COPY2;

-- 結果如下
Empty set (0.00 sec)  -- 表爲空的

DESC Product_COPY2;

-- 結果如下
-- 表結構已複製過來
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id     | char(4)      | NO   | PRI | NULL    |       |
| product_name   | varchar(100) | NO   |     | NULL    |       |
| product_type   | varchar(32)  | NO   |     | NULL    |       |
| sale_price     | int          | YES  |     | 0       |       |
| purchase_price | int          | YES  |     | NULL    |       |
| regist_date    | date         | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

五、運算符

5.1 算術運算符

我們可以在SELECT語句中使用計算表達式：

SELECT 
  product_name,
  sale_price,
  sale_price * 2 AS "sale_price_x2"
 FROM Product;
 
-- 結果如下
+--------------+------------+---------------+
| product_name | sale_price | sale_price_x2 |
+--------------+------------+---------------+
| T恤衫        |       1000 |          2000 |
| 打孔器       |        500 |          1000 |
| 運動T恤      |       4000 |          8000 |
| 菜刀         |       3000 |          6000 |
| 高壓鍋       |       6800 |         13600 |
| 叉子         |        500 |          1000 |
| 擦菜板       |        880 |          1760 |
| 圓珠筆       |        100 |           200 |
+--------------+------------+---------------+
8 rows in set (0.00 sec)

四則運算所使用的運算符 (+、-、*、/） 稱爲算術運算符。
在運算表達式中，也可以使用 ()，括號中的運算表達式優先級會得到提升。
NULL 的計算結果，仍然還是 NULL。

5.2 比較運算符

在 WHERE 子句中通過使用比較運算符可以組合出各種各樣的條件表達式。

SELECT product_name, product_type
  FROM Product
 WHERE sale_price = 500;

常見比較運算符如下表：

W9FCqg

不能對 NULL 使用任何比較運算符，只能通過IS NULL語句來判斷：

SELECT 
   product_name,
   purchase_price
  FROM Product
 WHERE purchase_price IS NULL;

希望選取不是 NULL 的記錄時，需要使用IS NOT NULL運算符。

對字符串使用比較符

MySQL 中字符串的排序與數字不同，典型的規則就是按照字典順序進行比較，也就是像姓名那樣，按照條目在字典中出現的順序來進行排序。例如：

'1'  < '10' < '11' < '2' < '222' < '3'

5.3 邏輯運算符

使用NOT否認某一條件：

SELECT 
  product_name,
  product_type,
  sale_price
  FROM Product
 WHERE NOT sale_price >= 1000;

AND運算符合OR運算符

SELECT product_type, sale_price
    FROM Product
	WHERE product_type = '廚房用具' 
	AND sale_price >= 3000;
	
-- 結果如下
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 廚房用具     |       3000 |
| 廚房用具     |       6800 |
+--------------+------------+
2 rows in set (0.00 sec)

SELECT product_type, sale_price
		FROM Product
	WHERE product_type = '廚房用具'
	OR sale_price >= 3000;
	
-- 結果如下
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 衣服         |       4000 |
| 廚房用具     |       3000 |
| 廚房用具     |       6800 |
| 廚房用具     |        500 |
| 廚房用具     |        880 |
+--------------+------------+
5 rows in set (0.00 sec)

邏輯運算符和真值

符 NOT、AND 和 OR 稱爲邏輯運算符；
真值就是值爲真（TRUE）或假（FALSE）；
在查詢 NULL 時，SQL 中存在第三種真值，不確定（UNKNOWN），NULL 和任何值做邏輯運算結果都是不確定；
考慮 NULL 時的條件判斷也會變得異常複雜，因此儘量給字段加上 NOT NULL 的約束。

六、分組查詢

6.1 聚合函數

通過 SQL 對數據進行某種操作或計算時需要使用函數。

COUNT：計算表中的記錄數（行數）
SUM：計算表中數值列中數據的合計值
AVG：計算表中數值列中數據的平均值
MAX：求出表中任意列中數據的最大值
MIN：求出表中任意列中數據的最小值

示例：

-- 計算全部數據的行數
SELECT COUNT(*) FROM Product;

-- 結果如下
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

注意點 1：除了COUNT可以將*作爲參數，其它的函數均不可以。

-- 計算最高的銷售價格
SELECT MAX(sale_price) FROM Product;

-- 結果如下
+-----------------+
| MAX(sale_price) |
+-----------------+
|          680000 |
+-----------------+
1 row in set (0.00 sec)

** 注意點 2：** 當將字段名作爲參數傳遞給函數時，只會計算不包含NULL的行。

示例：

-- purchase_price字段是包含NULL值的
SELECT purchase_price FROM Product;

-- 結果如下
+----------------+
| purchase_price |
+----------------+
|            500 |
|            320 |
|           2800 |
|            700 |
|           1250 |
|           NULL |
|            198 |
|           NULL |
+----------------+
8 rows in set (0.00 sec)

以 * 爲參數傳遞給COUNT函數

SELECT COUNT(*) FROM Product;

-- 結果如下
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

以 purchase_price 爲參數傳遞給COUNT函數

SELECT COUNT(purchase_price) FROM Product;

-- 結果如下
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)

可以看到結果並不一樣，函數忽略了值爲 NULL 的行。

SUM，AVG函數時也一樣，計算時會直接忽略，** 並不會當做 0 來處理！** 特別注意AVG函數，計算時分母也不會算上NULL行。

注意點 3：MAX/MIN函數幾乎適用於所有數據類型的列，包括字符和日期。SUM/AVG函數只適用於數值類型的列。

注意點 4：在聚合函數刪除重複值

SELECT COUNT(DISTINCT product_type)
 FROM Product;
 
-- 結果如下
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.01 sec)

DISTINCT必須寫在括號中。這是因爲必須要在計算行數之前刪除 product_type 字段中的重複數據。

6.2 對錶分組

如果對 Python 的 Pandas 熟悉，那麼大家應該很瞭解groupby函數，可以根據指定的列名，對錶進行分組。在 MySQL 中，也存在同樣作用的函數，即GROUP BY。

語法結構如下：

SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

示例：

SELECT product_type, COUNT(*)
 FROM Product
 GROUP BY product_type;
 
-- 結果如下
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 辦公用品     |        2 |
| 廚房用具     |        4 |
+--------------+----------+
3 rows in set (0.01 sec)

在該語句中，我們首先通過GROUP BY函數對指定的字段 product_type 進行分組。分組時，product_type 字段中具有相同值的行會匯聚到同一組。
最後通過COUNT函數，統計不同分組的包含的行數。

簡單來理解：

例如做操時，老師將不同身高的同學進行分組，相同身高的同學會被分到同一組，分組後我們又統計了每個小組的學生數。
將這裏的同學可以理解爲表中的一行數據，身高理解爲表的某一字段。
分組操作就是GROUP BY，GROUP BY後面接的字段等價於按照身高分組，統計學生數就等價於在SELECT後用了COUNT(*)函數。

注意：GROUP BY子句的位置一定要寫在FROM 語句之後（如果有 WHERE 子句的話需要寫在 WHERE 子句之後）

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

當被聚合的鍵中，包含NULL時，在結果中會以 “不確定” 行（空行）的形式表現出來，也就是字段中爲NULL的數據會被聚合爲一組。

6.3 使用 WHERE 語句

在對錶進行分組之前，也可以是先使用WHERE對錶進行條件過濾，然後再進行分組處理。語法結構如下：

SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 WHERE 
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

示例：

-- WHERE語句先將表中類型爲衣服的行篩選出來
-- 然後再按照purchase_price來進行分組
SELECT purchase_price, COUNT(*)
 FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
 
-- 結果如下
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+
2 rows in set (0.01 sec)

該語法實際的執行順序爲：

FROM → WHERE → GROUP BY → SELECT

使用GROUP BY子句時，SELECT子句中不能出現聚合鍵之外的字段名。即，若GROUP BY選中 purchase_price 字段進行分組，則在SELECT語句中只能選中 purchase_price 字段，其它字段如 product_id 等均不行。
WHERE語句中，不可以使用聚合函數。WHERE子句只能指定記錄（行）的條件，而不能用來指定組的條件。即WHERE MAX(purchase_price) > 1000這樣的語句是非法的。

6.4 爲聚合結果指定條件

前面提到了WHERE語句中不能使用聚合函數，但是實際操作時需要通過聚合函數來進行過濾怎麼辦呢？這就要用到HAVING語句了。語法結構如下：

SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分組結果對應的條件>

在HAVING的子句中能夠使用的 3 種要素如下所示：

● 常數

● 聚合函數

● GROUP BY子句中指定的字段名（即聚合鍵）

示例：

-- 不使用HAVING語句
SELECT product_type, AVG(sale_price)
 FROM Product
 GROUP BY product_type;
 
-- 結果如下
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 辦公用品     |        300.0000 |
| 廚房用具     |     279500.0000 |
+--------------+-----------------+
3 rows in set (0.00 sec)

-- 使用HAVING語句
-- 通過HAVING語句將銷售平均價格大於等於2500的組給保留了
SELECT product_type, AVG(sale_price)
 FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

-- 結果如下
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服         |       2500.0000 |
| 廚房用具     |     279500.0000 |
+--------------+-----------------+
2 rows in set (0.00 sec)

可以看到使用HAVING語句後，輸出的結果有所變化。大致流程如下：

首先，FROM語句會選中表 Product；
然後，GROUP BY語句會選中字段 product_type 進行分組；
之後，通過HAVING語句將銷售平均價格大於等於 2500 的組保留下來；
最後，通過SELECT語句將保留下的組的產品類型和平均價格顯示出來；

如果是對表的行進行條件指定，WHERE和HAVING都可以生效。

-- 下面兩條語句執行結果一致
SELECT product_type, COUNT(*)
  FROM Product
  GROUP BY product_type
 HAVING product_type = '衣服';

SELECT product_type, COUNT(*)
  FROM Product
  WHERE product_type = '衣服'
 GROUP BY product_type;
 
-- 結果如下
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
+--------------+----------+
1 row in set (0.01 sec)

但是，一般而言如果是對錶的行進行條件指定，最好還是使用WHERE語句，因爲WHERE的執行速度更快。

6.5 對錶的查詢結果進行排序

如果希望對錶的查詢結果根據某指定的字段進行排序，可以使用ORDER BY語句。語法結構如下：

SELECT <列名1>, <列名2>, <列名3>, ……
 FROM <表名>
 ORDER BY <排序基準列1>, <排序基準列2>, ……

示例：

SELECT product_id, product_name, sale_price, purchase_price
 FROM Product;
 
-- 結果如下
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0001       | T恤衫        |       1000 |            500 |
| 0002       | 打孔器       |        500 |            320 |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0004       | 菜刀         |     300000 |            700 |
| 0005       | 高壓鍋       |     680000 |           1250 |
| 0006       | 叉子         |      50000 |           NULL |
| 0007       | 擦菜板       |      88000 |            198 |
| 0008       | 圓珠筆       |        100 |           NULL |
+------------+--------------+------------+----------------+
8 rows in set (0.01 sec)

-- 根據字段sale_price的值進行排序
SELECT product_id, product_name, sale_price, purchase_price
 FROM Product
ORDER BY sale_price;

-- 結果如下
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 圓珠筆       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0001       | T恤衫        |       1000 |            500 |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0006       | 叉子         |      50000 |           NULL |
| 0007       | 擦菜板       |      88000 |            198 |
| 0004       | 菜刀         |     300000 |            700 |
| 0005       | 高壓鍋       |     680000 |           1250 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)

可以看到ORDER BY默認是按照升序的方式進行排序的，正式的書寫方式應該是在字段後加上關鍵字ASC，即ORDER BY sale_price ASC。

如果我們希望按照降序的方式，可以通過DESC關鍵詞進行指定。

SELECT product_id, product_name, sale_price, purchase_price
 FROM Product
ORDER BY sale_price DESC;

-- 結果如下
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005       | 高壓鍋       |     680000 |           1250 |
| 0004       | 菜刀         |     300000 |            700 |
| 0007       | 擦菜板       |      88000 |            198 |
| 0006       | 叉子         |      50000 |           NULL |
| 0003       | 運動T恤      |       4000 |           2800 |
| 0001       | T恤衫        |       1000 |            500 |
| 0002       | 打孔器       |        500 |            320 |
| 0008       | 圓珠筆       |        100 |           NULL |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)

前面展示了指定一個字段來對錶進行排序，實際上我們可以指定多個字段來進行排序。

示例：

SELECT regist_date, product_id, sale_price, purchase_price
 FROM Product
ORDER BY regist_date, product_id;

-- 結果如下
+-------------+------------+------------+----------------+
| regist_date | product_id | sale_price | purchase_price |
+-------------+------------+------------+----------------+
| 2009-10-10  | 0002       |        500 |            320 |
| 2009-10-10  | 0003       |       4000 |           2800 |
| 2009-10-10  | 0004       |     300000 |            700 |
| 2009-10-10  | 0005       |     680000 |           1250 |
| 2009-10-10  | 0006       |      50000 |           NULL |
| 2009-10-10  | 0007       |      88000 |            198 |
| 2009-10-10  | 0008       |        100 |           NULL |
| 2021-10-30  | 0001       |       1000 |            500 |
+-------------+------------+------------+----------------+

可以看到先按照regist_date的大小進行排序，在字段regist_date中具有相同的值的行，接着會按照product_id進行排序。

使用含有 NULL 的列作爲排序鍵時，NULL 會在結果的開頭或末尾彙總顯示。

在ORDER BY子句中可以使用SELECT子句中定義的別名。

-- 將product_id命名爲ID，然後按照ID進行排序
SELECT product_id as ID, product_name, sale_price, purchase_price
 FROM Product
ORDER BY ID;

-- 結果如下
+------+--------------+------------+----------------+
| ID   | product_name | sale_price | purchase_price |
+------+--------------+------------+----------------+
| 0001 | T恤衫        |       1000 |            500 |
| 0002 | 打孔器       |        500 |            320 |
| 0003 | 運動T恤      |       4000 |           2800 |
| 0004 | 菜刀         |     300000 |            700 |
| 0005 | 高壓鍋       |     680000 |           1250 |
| 0006 | 叉子         |      50000 |           NULL |
| 0007 | 擦菜板       |      88000 |            198 |
| 0008 | 圓珠筆       |        100 |           NULL |
+------+--------------+------------+----------------+
8 rows in set (0.00 sec)

爲什麼ORDER BY中可以使用SELECT定義的別名呢？

這是因爲在 MySQL 中，ORDER BY的執行次序在SELECT之後。

七、數據的插入及更新

7.1 數據的插入

通過命令INSERT，可以向表中插入數據：

-- 往表中插入一行數據
INSERT INTO <表名> (字段1, 字段2, 字段3, ……) VALUES (值1, 值2, 值3, ……);

-- 往表中插入多行數據
INSERT INTO <表名> (字段1, 字段2, 字段3, ……) VALUES 
	(值1, 值2, 值3, ……),
	(值1, 值2, 值3, ……),
	...
	;

示例：

創建表並插入數據

-- 創建表
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER DEFAULT 0, -- DEFAULT 0：表示將字段sale_price的默認值設爲0
 purchase_price INT ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
-- 通過單行方式插入
INSERT INTO 
 ProductIns(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
 VALUES ('0001', '打孔器', '辦公用品', 500, 320, '2009-09-11');
 
-- 當對錶插入全字段時，可以省略表後的字段清單
INSERT INTO ProductIns VALUES('0002', '高壓鍋', '廚房用具', 6800, 5000, '2009-01-15');
  
-- 通過多行方式插入
INSERT INTO ProductIns VALUES 
 ('0003', '菜刀', '廚房用具', 3000, 2800, '2009-09-20'),
 ('0004', '訂書機', '辦公用品', 100, 50, '2009-09-11'),
 ('0005', '裙子', '衣服', 4100, 3200, '2009-01-23'),
 ('0006', '運動T恤', '衣服', 4000, 2800, NULL),
 ('0007', '牙刷', '日用品', 20, 10, '2010-03-22');

SELECT * FROM ProductIns;

-- 結果如下
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0002       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0003       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0004       | 訂書機       | 辦公用品     |        100 |             50 | 2009-09-11  |
| 0005       | 裙子         | 衣服         |       4100 |           3200 | 2009-01-23  |
| 0006       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0007       | 牙刷         | 日用品       |         20 |             10 | 2010-03-22  |
+------------+--------------+--------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

插入 NULL

INSERT語句中想給某一列賦予 NULL 值時，可以直接在VALUES子句的值清單中寫入 NULL。

INSERT INTO ProductIns VALUES ('0008', '叉子', '廚房用具', 500, NULL, '2009-09-20');

插入默認值

在前面我們創建表時，字段 sale_price 包含了一條約束條件，默認爲 0。我們在插入數據時，可以直接用DEFAULT對該字段賦值。前提是，該字段被指定了默認值。

-- 通過顯式方法設定默認值
INSERT INTO 
 ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
 VALUES ('0009', '擦菜板', '廚房用具', DEFAULT, 790, '2009-04-28');

-- 通過隱式方法插入默認值
INSERT INTO 
 ProductIns (product_id, product_name, product_type, purchase_price, regist_date)
 VALUES ('0010', '擦菜板', '廚房用具', 790, '2009-04-28');

7.2 數據的刪除

通過DROP TABLE或者DELETE語句，可以對錶進行刪除，但二者存在一定的區別。

DROP TABLE 語句可以將表完全刪除。
DELETE 語句會留下表結構，而刪除表中的全部數據。

無論通過哪種方式刪除，數據都是難以恢復的。

通過DROP進行刪除

語法結構爲：

DROP <表名>;

通過DELETE進行刪除

語法結構如下，記得要加FROM：

DELETE FROM <表名>;

同時，也可以通過WHERE語句來指定刪除的條件：

DELETE FROM <表名>
	WHERE <條件>;

需要注意的是，DELETE語句的刪除對象並不是表或者列，而是記錄（行）。

示例：

SELECT * FROM Product;

-- 結果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圓珠筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

-- 刪除銷售價格大於等於4000的行
DELETE FROM Product
 WHERE sale_price >= 4000;

-- 結果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-09-11  |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-09-20  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 廚房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圓珠筆       | 辦公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
6 rows in set (0.00 sec)

通過TRUNCATE進行刪除

在 MySQL 中，還存在一種刪除表的方式，就是利用TRUNCATE語句。它的功能和DROP類似，但是不能通過WHERE指定條件，優點是速度比DROP快得多。

TRUNCATE Product;

-- 結果如下
mysql> SELECT * FROM Product;
Empty set (0.00 sec)

mysql> DESC Product;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id     | char(4)      | NO   | PRI | NULL    |       |
| product_name   | varchar(100) | NO   |     | NULL    |       |
| product_type   | varchar(32)  | NO   |     | NULL    |       |
| sale_price     | int          | YES  |     | NULL    |       |
| purchase_price | int          | YES  |     | NULL    |       |
| regist_date    | date         | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

7.3 數據的更新

當我們使用INSERT語句插入錯誤的數據後，若我們不想刪除後從新插入，那就要使用到UPDATE語句。

基本用法

UPDATE的語法結構如下：

UPDATE <表名>
	SET <字段名> = <表達式>;

示例：

-- 由於前面演示刪除語句時，表Product的內容已清空
-- 所以，這裏從新進行數據插入
INSERT INTO Product VALUES
  ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),
  ('0002', '打孔器', '辦公用品', 500, 320, '2009-09-11'),
  ('0003', '運動T恤', '衣服', 4000, 2800, NULL),
  ('0004', '菜刀', '廚房用具', 3000, 2800, '2009-09-20'),
  ('0005', '高壓鍋', '廚房用具', 6800, 5000, '2009-01-15'),
  ('0006', '叉子', '廚房用具', 500, NULL, '2009-09-20'),
  ('0007', '擦菜板', '廚房用具', 880, 790, '2008-04-28'),
  ('0008', '圓珠筆', '辦公用品', 100, NULL,'2009-11-11')
 ;
 
-- 修改表中所有行regist_date的值
UPDATE Product
 SET regist_date = '2009-10-10';

-- 結果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-10-10  |
| 0002       | 打孔器       | 辦公用品     |        500 |            320 | 2009-10-10  |
| 0003       | 運動T恤      | 衣服         |       4000 |           2800 | 2009-10-10  |
| 0004       | 菜刀         | 廚房用具     |       3000 |           2800 | 2009-10-10  |
| 0005       | 高壓鍋       | 廚房用具     |       6800 |           5000 | 2009-10-10  |
| 0006       | 叉子         | 廚房用具     |        500 |           NULL | 2009-10-10  |
| 0007       | 擦菜板       | 廚房用具     |        880 |            790 | 2009-10-10  |
| 0008       | 圓珠筆       | 辦公用品     |        100 |           NULL | 2009-10-10  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

指定條件

UPDATE <表名>
	SET <列名> = <表達式>
	WHERE <條件>;

示例：

UPDATE Product
 SET regist_date = '2021-10-30'
 WHERE product_id = '0001';

注意，你也可是使用 NULL 對錶進行更新，不過更新的字段必須滿足沒有主鍵和 NOT NULL 的約束條件。

多列更新

多列更新只需要用逗號（，）連接更改的字段即可。

UPDATE Product
 SET 
 	sale_price = sale_price * 10,
  purchase_price = purchase_price / 2
 WHERE product_type = '廚房用具';

八、Pymysql 的使用

在正式介紹pymysql的用法之前，我們先思考一件事，我們希望藉助pymysql完成什麼事情？

之前，我們在命令行下，通過輸入 SQL 語句來完成對數據庫和表的增刪改查。那麼，我們也希望能夠在 Python 下能夠完成同樣的操作，並且能夠返回相應的反饋。具體任務包括：

登陸並連接到 MySQL 下的用戶；
切換到相應的數據庫下；
完成對錶的增刪改查；

接下來的內容將圍繞這 3 部分來介紹。

8.1 安裝 pymysql

通過pip，我們可以完成對pymysql的安裝：

python3 -m pip install PyMySQL

8.2 連接數據庫

如果希望在 Python 中操作 MySQL 數據庫，那麼首先就要登陸到 MySQL 下的用戶。

我們通過創建庫 pymysql 下的類connect的一個實例來登陸到數據庫。

示例：

import pymysql

# 這裏登陸到我之前創建的admin賬戶
db = pymysql.connect(
     host='localhost',
     user='admin',
     password='mysql123',
     database='shop',    
     charset='utf8mb4',
     cursorclass=pymysql.cursors.DictCursor
)

參數解釋：

host：數據庫服務器地址，默認localhost；
user：所要登陸的用戶名；
password：用戶的登錄密碼；
database：所要連接的數據庫庫名；
charset：使用的字符類型；
cursorclass：定義遊標使用的類型，通過指定遊標使用的類型，在返回輸出的結果時將按照指定的類型進行返回。例如，這裏設置爲字典遊標。

8.3 創建遊標

關於遊標，可以理解爲在命令行中的光標。在命令行中，我們是在光標處鍵入語句的。這裏遊標的起到類似作用。

# 創建遊標
cursor = db.cursor()

實際上，除了在初始化connect的實例時指定遊標類型，我們在初始化遊標時也可以指定遊標類型，默認爲元組類型。

cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

cursors共支持四類遊標：

Cursor: 默認，元組類型；
DictCursor: 字典類型；
SSCursor: 無緩衝元組類型；
SSDictCursor: 無緩衝字典類型；

8.4 類方法

初始化完類connect和cursor的實例後，我們先來了解一下這兩個類下包含的部分方法。瞭解這些方法有利於我們後面在 python 下操作 mysql：

pymysql.connect下的類方法：
close()：在完成操作後，需要斷開與數據庫之間的連接；
commit()：如果執行語句中發生了數據更改，需要提交更改到穩定的存儲器；
cursor(cursor=None)：創建一個遊標，前面我們在初始化connect類是指定了遊標類型，通過cursor初始化遊標時，也可以進行遊標類型指定；
rollback()：事務回滾；
pymysql.cursors下的類方法：
mode=absolute：絕對位置移動，控制遊標位置到上一次查詢的第value條數據，最小值爲0；
mode=relative：相對位置移動，基於當前位置，跳過value條數據；
close()：結束時，關閉遊標；
execute()：通過遊標執行語句；
executemany()：通過遊標執行多條語句；
fetchone()：獲取單條數據；
fetchmany(size=None)：獲取 size 條數據；
fetchall()：獲取單條數據；
scroll(value, mode)：數據的查詢操作都是基於遊標，可以通過scroll控制遊標的位置。

更詳細的資料，可參考官方的 API 或者 Github，見文末。

8.5 實戰

示例 1：

在這個示例中，我們將做兩件事情：創建表和插入數據。

import pymysql

# 以admin身份連接到數據庫shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

# 創建遊標
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

# 1. 創建了一個表
sql = """
CREATE TABLE Employee(
    id INT PRIMARY KEY,
    name CHAR(15) NOT NULL
    )
    """

# 提交執行
cursor.execute(sql)

# 2. 往表中插入數據
sql = "INSERT INTO Employee (id, name) VALUES (%s, %s)"
values = [(1, 'XiaoBai'),
          (2, 'XiaoHei'),
          (3, 'XiaoHong'),
          (4, 'XiaoMei'),
          (5, 'XiaoLi')]

try:
  # 通過executemany可以插入多條數據
    cursor.executemany(sql, values)
    # 提交事務
    connection.commit()
except:
    connection.rollback()


# 3. 關閉光標及連接
cursor.close()
connection.close()

示例 2

在示例 1 的基礎上，我們繼續執行查詢工作。

import pymysql

# 以admin身份連接到數據庫shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

with connection:
    # 創建遊標
    cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

    # 1. 通過fetchone只查詢一條
    cursor.execute("SHOW CREATE TABLE Employee")
    result = cursor.fetchone()
    print(f'查詢結果1： \n{result}')

    # 2. 通過fetchmany查詢size條
    cursor.execute("DESC Employee")
    result = cursor.fetchmany(size=2)
    print(f'查詢結果2： \n{result}')

    # 3. 通過fetchall查詢所有
    cursor.execute("SELECT * FROM Employee")
    result = cursor.fetchall()
    print(f'查詢結果3： \n{result}')

    # 4. 通過scroll回滾到第0條進行查詢
    cursor.scroll(0, mode='absolute')
    result = cursor.fetchone()
    print(f'查詢結果4： \n{result}')
  
    # 5. 通過scroll跳過2條進行查詢
    cursor.scroll(2, mode='relative')
    result = cursor.fetchone()
    print(f'查詢結果5： \n{result}')

    cursor.close()

控制檯打印結果如下：

查詢結果1： 
{'Table': 'Employee', 'Create Table': 'CREATE TABLE `Employee` (\n  `id` int NOT NULL,\n  `name` char(15) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'}
查詢結果2： 
[{'Field': 'id', 'Type': 'int', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {'Field': 'name', 'Type': 'char(15)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''}]
查詢結果3： 
[{'id': 1, 'name': 'XiaoBai'}, {'id': 2, 'name': 'XiaoHei'}, {'id': 3, 'name': 'XiaoHong'}, {'id': 4, 'name': 'XiaoMei'}, {'id': 5, 'name': 'XiaoLi'}]
查詢結果4： 
{'id': 1, 'name': 'XiaoBai'}
查詢結果5： 
{'id': 4, 'name': 'XiaoMei'}

示例 3：

該示例將演示 SQL 注入的問題。先建立一個表並插入數據：

import pymysql

# 以admin身份連接到數據庫shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

# 創建遊標
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

sql = """
        CREATE TABLE UserInfo(
          id INT PRIMARY KEY,
          name VARCHAR(15),
          password CHAR(15) NOT NULL
          )
    """

cursor.execute(sql)

sql = "INSERT INTO UserInfo (id, name, password) VALUES (%s, %s, %s)"
values = [(1, 'XiaoBai', '123'),
          (2, 'XiaoHei', '234'),
          (3, 'XiaoHong', '567'),
          (4, 'XiaoMei', '321'),
          (5, 'XiaoLi', '789')]

cursor.executemany(sql, values)
connection.commit()

再寫一個程序，根據輸入判定登陸是否成功：

import pymysql

# 以admin身份連接到數據庫shop
connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='mysql123',
    database='shop',
    charset='utf8mb4',
)

# 創建遊標
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

while True:
    user = input("輸入用戶：").strip()
    password = input("輸入密碼：").strip()
    sql = "select name, password from UserInfo where  % (user, password)

    cursor.execute(sql)
    # 打印用戶和密碼
    result=cursor.fetchone()
    print(result)

    if result:
        print("成功登陸\n")
    else:
        print("登陸失敗\n")

在控制檯下，我們進行了三組用戶和密碼的驗證：

輸入用戶：XiaoBai
輸入密碼：123
{'name': 'XiaoBai', 'password': '123'}
成功登陸

輸入用戶：XiaoBai
輸入密碼：321
None
登陸失敗

輸入用戶：XiaoBai' -- dsd
輸入密碼：321
{'name': 'XiaoBai', 'password': '123'}
成功登陸

可以看出，第 1 組和第 2 組驗證正常，但是第三組出現了異常，輸入錯誤的密碼卻可以正確登陸。

這是因爲在 MySQL 中--的含義是註釋，如果通過字符串進行拼接：

select name, password from UserInfo where name='XiaoBai' -- dsd' and password='321'

實際等價於：

select name, password from UserInfo where name='XiaoBai'

這其實是一個 MySQL 注入問題，通過繞開用戶密碼進行登錄查詢。那麼如何抵禦 SQL 注入的問題？

解決辦法：通過execute或者executemany來進行拼接。將語句：

sql = "select name, password from UserInfo where  % (user, password)
cursor.execute(sql)

改爲：

sql = "select name, password from UserInfo where 
cursor.execute(sql, (user, password))

防止 SQL 注入，我們需要注意以下幾個要點：

永遠不要信任用戶的輸入。對用戶的輸入進行校驗，可以通過正則表達式，或限制長度；對單引號和雙 "-" 進行轉換等。
永遠不要使用動態拼裝 SQL，可以使用參數化的 SQL 或者直接使用存儲過程進行數據查詢存取。
永遠不要使用管理員權限的數據庫連接，爲每個應用使用單獨的權限有限的數據庫連接。
不要把機密信息直接存放，加密或者 hash 掉密碼和敏感的信息。
應用的異常信息應該給出儘可能少的提示，最好使用自定義的錯誤信息對原始錯誤信息進行包裝
SQL 注入的檢測方法一般採取輔助軟件或網站平臺來檢測，軟件一般採用 SQL 注入檢測工具 jsky，網站平臺就有億思網站安全平臺檢測工具。

參考資料

[日], MICK 著.《SQL 基礎教程第 2 版》，人民郵電出版社
菜鳥 MySQL 教程：https://www.runoob.com/mysql/mysql-tutorial.html
菜鳥 SQL 教程：https://www.runoob.com/sql/sql-tutorial.html
林海峯 MySQL 系列：https://www.cnblogs.com/linhaifeng/articles/7356064.html
Ubuntu20.04 下安裝 MySQL：https://devanswers.co/install-secure-mysql-server-ubuntu-20-04/
PyMySQL Github：https://github.com/PyMySQL/PyMySQL
PyMySQL Document：https://pymysql.readthedocs.io/en/latest/modules/index.html#

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