MySQL資料庫教程（mysql資料庫怎麼用）

2023-06-06 04:13:27

以前，在開發程式時，我們會把很多的資料和資訊儲存到某個資料夾中的檔案中，例如：user.txt 、db.xlsx 等。

現在，有那麼一個叫：資料庫管理系統（DBMS，Database Management System）的軟體，可以幫助我們實現對資料夾中的檔案進行操作，而我們只要學習DBMS能識別的指令，就能控制它去幫助我們實現的檔案和資料夾的處理。例如：

資料庫管理系統（DBMS）專注於幫助開發者解決資料儲存的問題，這樣開發者就可以把主要精力放在實現業務功能上了。

MySQL，原來是sun公司，後來被甲骨文收購。現在網際網路企業幾乎都在使用。【免費收費】
Oracle，甲骨文。收費，一般國企、事業單位居多。【收費】
Microsoft SQL Server，微軟。【收費】
DB2，IBM。【免費收費】
SQLite，D. Richard Hipp個人開發。【免費】
Access，微軟。【收費】
PostgreSQL，加州大學伯克利分校。【免費】
等眾多..

在專案開發中想要基於MySQL來進行資料儲存，大致應該怎麼做呢？

本系列的MySQL模組會分為5部分來講解：

MySQL入門，安裝和快速應用Python實現資料庫的操作。
必備SQL和授權，學習更多必備的指令讓資料庫實現更多業務場景。
SQL強化和實踐，強化練習必備”指令“（專案開發寫的最多）。
索引和函式以及儲存過程，掌握常見效能提升手段以及那些應用不是很頻繁卻又需瞭解的知識點。
Python操作MySQL和應用，側重點在於Python開發，讓大家瞭解Python開發中必備的實戰應用，例如：鎖、事務、資料庫連線池等。

安裝、配置、啟動

MySQL現在的版本主要分為：

5.x 版本，現在網際網路企業中的主流版本，包括：頭條、美圖、百度、騰訊等網際網路公司主流的版本。
8.x 版本，新增了一些了視窗函式、持久化配置、隱藏索引等其他功能。

所以，我們課程會以常用大版本中最新的版本為例來講解，即：5.7.31 （依然有很多企業在用5.6.x，但新專案基本上都是5.7.x了）。

win系統

一、下載安裝

https://downloads.mysql.com/archives/community/

二、解壓至任意資料夾

建議解壓至軟體安裝目錄，例如：

三、建立配置檔案

在MySQL的安裝目錄下建立 my.ini 的檔案，作為MySQL的配置檔案。

其實，MySQL的配置檔案可以放在很多的目錄，下圖是配置檔案的優先順序：

強烈，建議大家還是把配置檔案放在MySQL安裝目錄下，這樣以後電腦上想要安裝多個版本的MySQL時，配置檔案可以相互獨立不影響。

注意：如果你電腦的上述其他目錄存在MySQL配置檔案，建議刪除，否則可能會影響MySQL的啟動。

四、初始化

>>> "C:/Program Files/mysql-5.7.31-winx64/bin/mysqld.exe" --initialize-insecure

初始化命令在執行時，會自動讀取配置檔案並執行初始化，此過程主要會做兩件事：

自動建立data目錄，以後我們的資料都會存放在這個目錄。
同時建立建必備一些的資料，例如預設賬戶 root （無密碼），用於登入MySQL並通過指令操作MySQL。

在windowns安裝過程中如果有報錯（ msvcr120.dll不存在），請下載並安裝下面的兩個補丁：

vcredist：https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=40784 （主要）

dirctx：https://www.microsoft.com/zh-CN/download/details.aspx?id=35

五、啟動

啟動MySQL常見的有兩種方式：

臨時啟動

>>> "C:/Program Files/mysql-5.7.31-winx64/bin/mysqld.exe"

注意：此時程式會掛起，內部就是可以接收客戶端發來的MySQL指令，關閉視窗或Ctrl c 就可以停止執行。
這種啟動方式每次開機或想要開啟都需要手動執行一遍命令比較麻煩。
製作windows服務，基於windows服務管理。

>>>"C:/Program Files/mysql-5.7.31-winx64/bin/mysqld.exe" --install mysql57

建立好服務之後，可以通過命令啟動和關閉服務，例如：

>>> net start mysql57 >>> net stop mysql57

以後不再想要使用window服務了，也可以將製作的這個MySQL服務刪除。

>>>"C:/Program Files/mysql-5.7.31-winx64/bin/mysqld.exe" --remove mysql57

六、測試連結MySQL

安裝並啟動MySQL之後，就可以連線MySQL來測試是否已正確安裝並啟動成功

以後在開發時，肯定是要用Python程式碼來連線MySQL並且進行資料操作（後面講）。

在安裝MySQL時，其實也自動安裝了一個工具（客戶端），讓我們快速實現連線MySQL併傳送指令。

注意：如果把bin目錄加入環境變數，每次在執行命令時，就不用再重新輸入絕對路徑了。

上述過程如果操作完成之後，證明你的安裝和啟動過程就搞定了。

mac系統

mac系統和win不同，MySQL為他提供了非常方便的一站式安裝程式，只要點選、next就可以安裝、初始化完成。

第1步：安裝和初始化

https://downloads.mysql.com/archives/community/

這個基於dmg檔案的安裝過程，其實包含了：

安裝，預設安裝在了 /usr/local/mysql-5.7.31-macos10.14-x86_64/目錄。
初始化，在安裝目錄下建立data目錄用於存放資料；初始化模組資料庫以及賬戶相關等，例如：賬cd

第2步：建立配置檔案

建議在MySQL安裝目錄下建立 etc/my.cnf 作為MySQL的配置檔案。

MySQL的配置檔案按照優先順序，會在以下目錄中尋找：

為了避免多個版本共存時，配置檔案混亂的問題，建議大家還是把配置檔案放在當前MySQL的安裝目錄下

第3步：啟動

在Mac系統中啟動MySQL常見的有2種方式：

安裝目錄中自帶 mysql.server 指令碼（建議）

sudo /usr/local/mysql/support-files/mysql.server start # 輸入電腦密碼 sudo mysql.server start # 輸入電腦密碼

sudo /usr/local/mysql/support-files/mysql.server stop

為了避免每次執行命令都需要些路徑，可以將路徑
/usr/local/mysql/support-files加入到環境變數中。

操作完成之後，再在終端執行下命令：source ~/.zprofile 讓設定的環境變數立即生效。

注意：mac系統的版本如果比較老，會顯示空白的 zprofile 檔案，此就要去開啟 bash_profile 檔案。

這樣設定好之後，以後就可以使用下面的命令去啟動和關閉MySQL了。

sudo mysql.server start sudo mysql.server stop

系統偏好設定（不推薦）

第一種mysql.server指令碼的形式，內部是使用 mysqld_safe執行，可以守護我們的MySQL程序，如意外掛掉可自動重啟。

第4步：測試連線MySQL

安裝並啟動MySQL之後，就可以連線MySQL來測試是否已正確安裝並啟動成功。

以後在開發時，肯定是要用Python程式碼來連線MySQL並且進行資料操作（後面講）。

在安裝MySQL時，其實也自動安裝了一個工具（客戶端），讓我們快速實現連線MySQL併傳送指令。

注意：/usr/local/mysql/bin也可以加入到環境變數。

至此，在Mac系統中關於MySQL的安裝和配置就完成了。

關於配置檔案

上述的過程中，我們在配置檔案中只新增了很少的配置。

其實，配置項有很多，而哪些配置項都有預設值，如果我們不配置，MySQL則自動使用預設值。

關於密碼

設定和修改root密碼

在windows系統中模組預設 root 賬戶是沒有密碼的，如果想要為賬戶設定密碼，可以在利用root賬戶登入成功之後，執行：

忘記root密碼

如果你忘記了MySQL賬戶的密碼。

修改配置檔案，在 [mysqld] 節點下新增 skip-grant-tables=1

[mysqld] ... skip-grant-tables=1 ...

重啟MySQL，再次登入時，不需要密碼直接可以進去了

windows重啟

net stop mysql57 net start mysql57

mac重啟

sudo mysql.server restart

重啟後，無序密碼就可以進入。

mysql -u root -p

進入資料庫後執行修改密碼命令

use mysql; update user set authentication_string = password('新密碼'), password_last_changed=now() where user='root';

退出並再次修改配置檔案，刪除 [mysqld] 節點下的 skip-grant-tables=1

[mysqld] ... # skip-grant-tables=1 ...

再次重啟，以後就可以使用新密碼登入了。

資料庫管理

安裝上資料庫之後，就需要開始學習指令了，通過指令讓MySQL去做出一些檔案操作。

如果將資料庫管理系統與之前的檔案管理做類比的話：

資料庫管理系統	檔案管理
資料庫	資料夾
資料表	資料夾下的excel檔案

接下來，我們先學習資料庫（資料夾）相關操作的指令。

內建客戶端操作
當連線上MySQL之後，執行如下指令（一般稱為SQL語句），就可以對MySQL的資料進行操作。

檢視當前所有的資料庫：show databases;
建立資料庫：create databases 資料庫名 DEFAULT utf8 COLLATE utf8_general_ci;

create database day25db; create database daya25db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

刪除資料庫：drop database 資料庫名
進入資料（進入檔案）：use 資料庫

示例：

# 1.登入MySQL wupeiqi@wupeiqideMBP ~ % /usr/local/mysql/bin/mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 5 Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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. # 2.檢視當前資料庫 mysql> show databases; -------------------- | Database | -------------------- | information_schema | | mysql | | performance_schema | | sys | -------------------- 4 rows in set (0.00 sec) # 3. 建立資料庫: create database 資料庫名 default charset 編碼 collate 排序規則; mysql> create database db1 default charset utf8 collate utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> show databases; -------------------- | Database | -------------------- | information_schema | | db1 | | mysql | | performance_schema | | sys | -------------------- 5 rows in set (0.01 sec) # 4. 刪除資料庫 mysql> drop database db1; Query OK, 0 rows affected (0.00 sec) # 5. 檢視當前資料庫 mysql> show databases; -------------------- | Database | -------------------- | information_schema | | mysql | | performance_schema | | sys | -------------------- 4 rows in set (0.00 sec) # 6. 進入資料庫 mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # 7. 進入mysql資料庫（資料夾），檢視此資料庫下的所有表。 mysql> show tables; --------------------------- | Tables_in_mysql | --------------------------- | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | --------------------------- 31 rows in set (0.00 sec) # 8. 退出 mysql>exit;

Python程式碼操作

無論通過何種方式去連線MySQL，本質上傳送的指令都是相同的，只是連線的方式和操作形式不同而已。

當連線上MySQL之後，執行如下指令，就可以對MySQL的資料進行操作。（同上述過程）

檢視當前所有的資料庫 show databases;
建立資料庫：create database 資料庫名 default charset utf8 collate utf8_general_ci;
刪除資料庫： drop database 資料庫名
進入資料（進入檔案）：use資料庫；

想要使用Python操作MySQL需要安裝第三方模組：

pip3 install pymysql

安裝完成後，就可以編寫程式碼：

import pymysql # 連線MySQL(socket) conn = pymysql.conncet(host='127.0.0.1',port=3306,user='root', password='root123',charset='utf8') cursor = conn.cursor() # 1、檢視資料庫 # 傳送指令 cursor.execute("show databases") 獲取指令的結果 result = cursor.fetchall() print(reslut) 2、建立資料庫（新增、刪除、修改） #傳送指令 cursor.execute("create database db3 default charset utf8 collate utf8_general_ci") conn.commit() 3.檢視資料庫 # 傳送指令 cursor.execute("show databases") #獲取指令的結果 reslut = cursor.fetchall() print(reslut) 4.刪除資料庫 # 傳送指令 cursor.execute("drop database db3") conn.commit() 5.進入資料庫，檢視錶 #傳送指令 cursor.execute("use mysql") cursor.execute("show tables") reslut = cursor.fetchall() print(reslut) # 關閉連線 cursor.close() conn.close()

資料表管理

如果將資料庫管理系統與之前的檔案管理做類比的話：

資料庫管理系統	檔案管理
資料庫	資料夾
資料表	資料夾下的檔案

接下來，我們先學習資料表（資料夾中的檔案）相關操作的指令。

其實在資料庫中建立資料庫和建立Excel非常類似，需要指定：表名、列名稱、類型別（整型、字串或其他)。

內建客戶端操作

資料表常見操作指令：

進入資料庫use資料庫，檢視當前所有表：show tables;
建立表結構

create table 表名（列名型別，列名型別，列名型別）default charset=utf8;

create table tb1( id int name varchar(16) )default charset=utf8;

create table tb2( id int name varchar(16) not null -- 不允許為空 email varchar(32) null -- 允許為空（預設） age int )default charset=utf8;

create table tb3( id int, name varchar(16) not null, -- 不允許為空 email varchar(32) null, -- 允許為空（預設） age int default 3 -- 插入資料時，如果不給age列設定值，預設值：3 )default charset=utf8;

create table tb4( id int primary key, -- 主鍵（不允許為空、不能重複） name varchar(16) not null, -- 不允許為空 email varchar(32) null, -- 允許為空（預設） age int default 3 -- 插入資料時，如果不給age列設定值，預設值：3 )default charset=utf8;

主鍵一般用於表示當前這條資料的ID編號（類似於人的身份證），需要我們自己來維護一個不重複的值，比較繁瑣。所以，在資料庫中一般會將主鍵和自增結合。

create table tb5( id int not null auto_increment primary key, -- 不允許為空 & 主鍵 & 自增 name varchar(16) not null, -- 不允許為空 email varchar(32) null, -- 允許為空（預設） age int default 3 -- 插入資料時，如果不給age列設定值，預設值：3 )default charset=utf8;

注意：一個表中只能有一個自增列【自增列，一般都是主鍵】。

刪除表drop table 表名；
清空表 delete from 表名；或 truncate table 表名；(速度快、無法回滾撤銷等)
修改列表

新增列

alter table 表名 add 列名型別; alter table 表名 add 列名型別 DEFAULT 預設值; alter table 表名 add 列名型別 not null default 預設值; alter table 表名 add 列名型別 not null primary key auto_increment;

刪除列

alter table 表名 drop column 列名;

修改列型別

alter table 表名 change 原列名新列名新型別;

alter table tb change id nid int not null; alter table tb change id id int not null default 5; alter table tb change id id int not null primary key auto_increment; alter table tb change id id int; -- 允許為空，刪除預設值，刪除自增。

修改列預設值

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

刪除列預設值

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

新增主鍵

alter table 表名 add primary key(列名);

刪除主鍵

alter table 表名 drop primary key;

常見列型別

create table 表( id int name varchar(16) ) default charset = utf8;

in t[(m)] [unsigned] [zerofill]

int 表示有符號，取值範圍：-2147483648 ～ 2147483647 int unsigned 表示無符號，取值範圍：0 ～ 4294967295 int(5)zerofill 僅用於顯示，當不滿足5位時，按照左邊補0，例如：00002；滿足時，正常顯示。

mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8; Query OK, 0 rows affected (0.03 sec) mysql> insert into L1(id,uid,zid) values(1,2,3); Query OK, 1 row affected (0.00 sec) mysql> insert into L1(id,uid,zid) values(2147483641,4294967294,300000); Query OK, 1 row affected (0.00 sec) mysql> select * from L1; ------------ ------------ -------- | id | uid | zid | ------------ ------------ -------- | 1 | 2 | 00003 | | 2147483641 | 4294967294 | 300000 | ------------ ------------ -------- 2 rows in set (0.00 sec) mysql> insert into L1(id,uid,zid) values(214748364100,4294967294,300000); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql>

tinyint [(m)] [unsigned] [zerofill]

有符號，取值範圍：-128 ～ 127. 無符號，取值範圍：0 ～ 255

bigint[(m)][unsigned][zerofill]

有符號，取值範圍：-9223372036854775808 ～ 9223372036854775807 無符號，取值範圍：0 ～ 18446744073709551615

decimal [(m[,d])] [unsigned] [zerofill]

準確的小數值，m是數字總個數（負號不算），d是小數點後個數。 m最大值為65，d最大值為30。例如： create table L2( id int not null primary key auto_increment, salary decimal(8,2) )default charset=utf8;

mysql> create table L2(id int not null primary key auto_increment,salary decimal(8,2))default charset=utf8; Query OK, 0 rows affected (0.03 sec) mysql> insert into L2(salary) values(1.28); Query OK, 1 row affected (0.01 sec) mysql> insert into L2(salary) values(5.289); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into L2(salary) values(5.282); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into L2(salary) values(512132.28); Query OK, 1 row affected (0.00 sec) mysql> insert into L2(salary) values(512132.283); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from L2; ---- ----------- | id | salary | ---- ----------- | 1 | 1.28 | | 2 | 5.29 | | 3 | 5.28 | | 4 | 512132.28 | | 5 | 512132.28 | ---- ----------- 5 rows in set (0.00 sec) mysql> insert into L2(salary) values(5121321.283); ERROR 1264 (22003): Out of range value for column 'salary' at row 1 mysql>

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

單精度浮點數，非準確小數值，m是數字總個數，d是小數點後個數。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

雙精度浮點數（非準確小數值），m是數字總個數，d是小數點後個數。

char(m)

定長字串，m代表字串的長度，最多可容納255個字元。定長的體現：即使內容長度小於m，也會佔用m長度。例如：char(5)，資料是：yes，底層也會佔用5個字元；如果超出m長度限制（預設MySQL是嚴格模式，所以會報錯）。如果在配置檔案中加入如下配置， sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 儲存並重啟，此時MySQL則是非嚴格模式，此時超過長度則自動截斷（不報錯）。。注意：預設底層儲存是固定的長度（不夠則用空格補齊），但是查詢資料時，會自動將空白去除。如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。檢視模式sql-mode，執行命令：show variables like 'sql_mode'; 一般適用於：固定長度的內容。 create table L3( id int not null primary key auto_increment, name varchar(5), depart char(3) )default charset=utf8; insert into L3(name,depart) values("alexsb","sbalex");

varchar(m)

變長字串，m代表字串的長度，最多可容納65535個位元組。變長的體現：內容小於m時，會按照真實資料長度儲存；如果超出m長度限制（（預設MySQL是嚴格模式，所以會報錯）。如果在配置檔案中加入如下配置， sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 儲存並重啟，此時MySQL則是非嚴格模式，此時超過長度則自動截斷（不報錯）。例如： create table L3( id int not null primary key auto_increment, name varchar(5), depart char(3) )default charset=utf8;

mysql> create table L3(id int not null primary key auto_increment,name varchar(5),depart char(3))default charset=utf8; Query OK, 0 rows affected (0.03 sec) -- 插入多行 mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from L3; ---- ------- -------- | id | name | depart | ---- ------- -------- | 1 | wu | WU | | 2 | wupei | ALS | ---- ------- -------- 2 rows in set (0.00 sec) -- 非嚴格模式下，不會報錯。 mysql> insert into L3(name,depart) values("wupeiqi","ALS"); ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into L3(name,depart) values("wupei","ALSB"); ERROR 1406 (22001): Data too long for column 'depart' at row 1 mysql> -- 如果 sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，則查詢時char時空白會保留。 mysql> select name,length(name),depart,length(depart) from L3; ------- -------------- -------- ---------------- | name | length(name) | depart | length(depart) | ------- -------------- -------- ---------------- | wu | 2 | WU | 3 | | wupei | 5 | ALS | 3 | ------- -------------- -------- ---------------- 4 rows in set (0.00 sec) mysql>

text

text資料型別用於儲存變長的大字串，可以組多到65535 (2**16 − 1)個字元。一般情況下，長文字會用text型別。例如：文章、新聞等。

create table L4( id int not null primary key auto_increment, title varchar(128), content text )default charset=utf8;

mediumtext

A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext

A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

datetime

YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

timestamp

YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

對於TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行儲存，查詢時，將其又轉化為客戶端當前時區進行返回。對於DATETIME，不做任何改變，原樣輸入和輸出。

mysql> create table L5( -> id int not null primary key auto_increment, -> dt datetime, -> tt timestamp -> )default charset=utf8; Query OK, 0 rows affected (0.03 sec) mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44"); mysql> select * from L5; ---- --------------------- --------------------- | id | dt | tt | ---- --------------------- --------------------- | 1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 | ---- --------------------- --------------------- 1 row in set (0.00 sec) mysql> show variables like '%time_zone%'; ------------------ -------- | Variable_name | Value | ------------------ -------- | system_time_zone | CST | | time_zone | SYSTEM | ------------------ -------- 2 rows in set (0.00 sec) -- “CST”指的是MySQL所在主機的系統時間，是中國標準時間的縮寫，China Standard Time UT 8:00 mysql> set time_zone=' 0:00'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%time_zone%'; ------------------ -------- | Variable_name | Value | ------------------ -------- | system_time_zone | CST | | time_zone | 00:00 | ------------------ -------- 2 rows in set (0.01 sec) mysql> select * from L5; ---- --------------------- --------------------- | id | dt | tt | ---- --------------------- --------------------- | 1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 | ---- --------------------- --------------------- 1 row in set (0.00 sec)

date

YYYY-MM-DD（1000-01-01/9999-12-31）

time

HH:MM:SS（'-838:59:59'/'838:59:59'）

MySQL還有很多其他的資料型別，例如：set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等，詳細見官方文件：
https://dev.mysql.com/doc/refman/5.7/en/data-types.html

上述就是關於資料表的一些基本操作。

MySQL程式碼操作

基於Python去連線MySQL之後，想要進行資料表的管理的話，傳送的指令其實都是相同的，例如：

import pymysql # 連線MySQL conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8") cursor = conn.cursor() # 1. 建立資料庫 """ cursor.execute("create database db4 default charset utf8 collate utf8_general_ci") conn.commit() """ # 2. 進入資料庫、檢視資料表 """ cursor.execute("use db4") cursor.execute("show tables") result = cursor.fetchall() print(result) """ # 3. 進入資料庫建立表 cursor.execute("use db4") sql = """ create table L4( id int not null primary key auto_increment, title varchar(128), content text, ctime datetime )default charset=utf8; """ cursor.execute(sql) conn.commit() # 4. 檢視資料庫中的表 """ cursor.execute("show tables") result = cursor.fetchall() print(result) """ # 5. 其他 drop table... 略過 # 關閉連線 cursor.close() conn.close()

資料行

當資料庫和資料表建立完成之後，就需要對資料表中的內容進行：增、刪、改、查了。

內建客戶端操作

資料行操作的相關SQL語句（指令）如下：

新增資料

insert into 表名(列名，列名，列名) values(對應列的值，對應列的值，對應列的值)

insert into tb1(name,password) values('武沛齊','123123'); insert into tb1(name,password) values('武沛齊','123123'),('alex','123'); insert into tb1 values('武沛齊','123123'),('alex','123'); -- 如果表中只有2列

刪除資料

delete from 表名; delete from 表名 where 條件;

delete from tb1; delete from tb1 where name="wupeiqi"; delete from tb1 where name="wupeiqi" and password="123"; delete from tb1 where id>9;

修改資料

update 表名 set 列名=值; update 表名 set 列名=值 where 條件;

update tb1 set name="wupeiqi"; update tb1 set name="wupeiqi" where id=1; update tb1 set age=age 1; -- 整型 update tb1 set age=age 1 where id=2; update L3 set name=concat(name,"db"); update L3 set name=concat(name,"123") where id=2; -- concat一個函式，可以拼接字串

查詢資料

select * from 表名; select 列名,列名,列名 from 表名; select 列名,列名 as 別名,列名 from 表名; select * from 表名 where 條件;

select * from tb1; select id,name,age from tb1; select id,name as N,age, from tb1; select id,name as N,age, 111 from tb1; select * from tb1 where id = 1; select * from tb1 where id > 1; select * from tb1 where id != 1; select * from tb1 where name="wupeiqi" and password="123";

Python程式碼操作

import pymysql # 連線MySQL，自動執行 use userdb; -- 進入資料庫 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor() # 1.新增（需commit） """ cursor.execute("insert into tb1(name,password) values('武沛齊','123123')") conn.commit() """ # 2.刪除（需commit） """ cursor.execute("delete from tb1 where id=1") conn.commit() """ # 3.修改（需commit) """ cursor.execute("update tb1 set name='xx' where id=1") conn.commit() """ # 4.查詢 """ cursor.execute("select * from tb where id>10") data = cursor.fetchone() # cursor.fetchall() print(data) """ # 關閉連線 cursor.close() conn.close()

其實在真正做專案開發時，流程如下：

第一步：根據專案的功能來設計相應的資料庫 & 表結構（不會經常變動，在專案設計之初就確定好了）。
第二步：操作表結構中的資料，已達到實現業務邏輯的目的。

例如：實現一個使用者管理系統。

先使用MySQL自帶的客戶端建立相關資料庫和表結構（相當於先建立好Excel結構）。

create database usersdb default charset utf8 collate utf8_general_ci;

create table users( id int not null primary key auto_increment, name varchar(32), password varchar(64) )default charset=utf8;

再在程式中執行編寫相應的功能實現註冊、登入等功能。

import pymysql def register(): print("使用者註冊") user = input("請輸入使用者名稱：") # alex password = input("請輸入密碼：") # sb # 連線指定資料 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb") cursor = conn.cursor() # 執行SQL語句（有SQL隱碼攻擊風險，稍後講解） # sql = 'insert into users(name,password)values("alex","sb")' sql = 'insert into users(name,password) values("{}","{}")'.format(user, password) cursor.execute(sql) conn.commit() # 關閉資料庫連線 cursor.close() conn.close() print("註冊成功，使用者名稱：{},密碼:{}".format(user, password)) def login(): print("使用者登入") user = input("請輸入使用者名稱：") password = input("請輸入密碼：") # 連線指定資料 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb") cursor = conn.cursor() # 執行SQL語句（有SQL隱碼攻擊風險，稍後講解） # sql = select * from users where name='wupeiqi' and password='123' sql = "select * from users where name='{}' and password='{}'".format(user, password) cursor.execute(sql) result = cursor.fetchone() # 去向mysql獲取結果 # None # (1,wupeiqi,123) # 關閉資料庫連線 cursor.close() conn.close() if result: print("登入成功", result) else: print("登入失敗") def run(): choice = input("1.註冊；2.登入") if choice == '1': register() elif choice == '2': login() else: print("輸入錯誤") if __name__ == '__main__': run()

你會發現，在專案開發時，資料庫 & 資料表的操作其實就做那麼一次，最最常寫的還是對資料行的操作。

關於SQL隱碼攻擊

假如，你開發了一個使用者認證的系統，應該使用者登入成功後才能正確的返回相應的使用者結果。

import pymysql # 輸入使用者名稱和密碼 user = input("請輸入使用者名稱：") # ' or 1=1 -- pwd = input("請輸入密碼：") # 123 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb') cursor = conn.cursor() # 基於字串格式化來拼接SQL語句 # sql = "select * from users where name='alex' and password='123'" # sql = "select * from users where name='' or 1=1 -- ' and password='123'" sql = "select * from users where name='{}' and password='{}'".format(user, pwd) cursor.execute(sql) result = cursor.fetchone() print(result) # None，不是None cursor.close() conn.close()

如果使用者在輸入user時，輸入了： ' or 1=1 -- ，這樣即使使用者輸入的密碼不存在，也會可以通過驗證。

為什麼呢？

因為在SQL拼接時，拼接後的結果是：

select * from users where name='' or 1=1 -- ' and password='123'

注意：在MySQL中 -- 表示註釋。

那麼，在Python開發中如何來避免SQL隱碼攻擊呢？

切記，SQL語句不要在使用python的字串格式化，而是使用pymysql的execute方法。

import pymysql # 輸入使用者名稱和密碼 user = input("請輸入使用者名稱：") pwd = input("請輸入密碼：") conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor() cursor.execute("select * from users where name=%s and password=%s", [user, pwd]) # 或 # cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd}) result = cursor.fetchone() print(result) cursor.close() conn.close()

總結

除了【第5步使用者授權】以外，現在使用的是預設root賬戶（擁有最大許可權），上述所有的過程已講解完畢。

本節內容需要掌握：

安裝和啟動MySQL
SQL語句：

資料庫操作
表操作
資料行操作

基於Python操作MySQL
注意SQL隱碼攻擊的問題

MySQL資料庫教程（mysql資料庫怎麼用）

安裝、配置、啟動

win系統

mac系統

關於配置檔案

關於密碼

資料庫 管理

Python程式碼操作

資料表 管理

MySQL程式碼操作

資料行

內建客戶端操作

Python程式碼操作

關於SQL隱碼攻擊

總結

資料庫管理

資料表管理