MySQL庫的操作

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, 
create_specification] ...]
 create_specification:
 [DEFAULT] CHARACTER SET charset_name
 [DEFAULT] COLLATE collation_name

說明：

• 大寫的表示關(guān)鍵字
• [] 是可選項
• CHARACTER SET: 指定數(shù)據(jù)庫采用的字符集
• COLLATE: 指定數(shù)據(jù)庫字符集的校驗規(guī)則

注意： 如果創(chuàng)建數(shù)據(jù)庫時未指明數(shù)據(jù)庫的編碼格式或校驗規(guī)則，則默認(rèn)使用MySQL配置文件中對應(yīng)的編碼格式或校驗規(guī)則。

創(chuàng)建數(shù)據(jù)庫案例

采用默認(rèn)的編碼格式和校驗規(guī)則創(chuàng)建數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫時不指明數(shù)據(jù)庫的編碼格式和校驗規(guī)則即可。如下：

注意： 如果沒有對MySQL的配置文件進(jìn)行過修改，則默認(rèn)的編碼格式是utf8，默認(rèn)的校驗規(guī)則是utf8_general_ci。

指定utf8編碼格式創(chuàng)建數(shù)據(jù)庫

注意： SQL中的charset=utf8，也可以寫成character set=utf8或character set utf8。

指定utf8編碼格式和utf8_general_ci校驗規(guī)則創(chuàng)建數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫時通過charset和collate分別指明數(shù)據(jù)庫的編碼格式和校驗規(guī)則即可。如下：

注意： SQL中的collate utf8_general_ci 也可以寫成。collate=utf8_general_ci

字符集和校驗規(guī)則

查看系統(tǒng)默認(rèn)字符集以及校驗規(guī)則

查看系統(tǒng)默認(rèn)字符集以及校驗規(guī)則

通過查看MySQL系統(tǒng)變量variables中的character_set_database，可以得知系統(tǒng)默認(rèn)的字符集。如下：

注意： 如果是在指定數(shù)據(jù)庫下使用該SQL，則查看的是該數(shù)據(jù)庫對應(yīng)的字符集。

查看系統(tǒng)默認(rèn)的字符集校驗規(guī)則

通過查看MySQL系統(tǒng)變量variables中的collation_database，可以得知系統(tǒng)默認(rèn)的字符集校驗規(guī)則。如下：

查看數(shù)據(jù)庫支持的字符集

使用show charsetSQL可以查看數(shù)據(jù)庫支持的字符集。如下：

說明一下： 字符集主要是控制用什么語言，比如utf8就可以使用中文。

查看數(shù)據(jù)庫支持的字符集校驗規(guī)則

查看數(shù)據(jù)庫支持的字符集校驗規(guī)則

使用show collationSQL可以查看數(shù)據(jù)庫支持的字符集校驗規(guī)則。如下：

校驗規(guī)則對數(shù)據(jù)庫的影響

字符集編碼格式和字符集校驗規(guī)則的區(qū)別

• 字符集編碼格式指的是在存儲數(shù)據(jù)時各個字符的底層編碼，用于指定數(shù)據(jù)的存儲格式。
• 字符集校驗規(guī)則是在字符集內(nèi)用于比較字符的一套規(guī)則，用于對數(shù)據(jù)進(jìn)行比對。

比如我們存儲數(shù)據(jù)的時候是按照utf8mb4的格式進(jìn)行存儲的，那么將來在對數(shù)據(jù)做比對時也必須按照utf8mb4的格式進(jìn)行比對，因為“存數(shù)據(jù)”和“取數(shù)據(jù)”的方式必須保持一致。

注意： 在對數(shù)據(jù)庫當(dāng)中的數(shù)據(jù)進(jìn)行增刪查改時，不可避免的需要進(jìn)行數(shù)據(jù)的比對，因為在對數(shù)據(jù)做增刪查改之前，都需要先通過比對的方式找到目標(biāo)數(shù)據(jù)。

校驗規(guī)則對數(shù)據(jù)庫的影響

現(xiàn)在我們知道了，為了保證讀取的數(shù)據(jù)和存儲的數(shù)據(jù)是一致的，因此字符集編碼格式和校驗規(guī)則必須是對應(yīng)的。但實際同一中字符集編碼格式一般會有多種可選擇的校驗規(guī)則，比如utf8編碼格式的校驗規(guī)則有utf8_general_ci、utf8_bin、utf8_unicode_ci等校驗規(guī)則。

使用不同的校驗規(guī)則操作數(shù)據(jù)庫中的數(shù)據(jù)可能會得到不同的結(jié)果，比如utf8mb4_general_ci校驗規(guī)則在比對數(shù)據(jù)時是不區(qū)分大小寫的，而utf8mb4_bin校驗規(guī)則在對比數(shù)據(jù)時則是區(qū)分大小寫的。

我們以兩個庫test1和test2來比對兩種校驗規(guī)則

不區(qū)分大小寫用utf8mb4_ general_ ci

創(chuàng)建一個數(shù)據(jù)庫test1，校驗規(guī)則使用utf8_ general_ ci[不區(qū)分大小寫]如下：

區(qū)分大小寫用utf8mb4_ bin

創(chuàng)建一個數(shù)據(jù)庫，校驗規(guī)則使用utf8mb4_ bin[區(qū)分大小寫]如下：

進(jìn)行查詢

• 不區(qū)分大小寫的查詢以及結(jié)果

• 區(qū)分大小寫的查詢以及結(jié)果

結(jié)果排序

• 不區(qū)分大小寫排序以及結(jié)果：

• 區(qū)分大小寫排序以及結(jié)果：

注：A的ASCII碼為65，a的ASCII碼值為97

操縱數(shù)據(jù)庫

查看數(shù)據(jù)庫

使用show databaseSQL可以查看系統(tǒng)中所有的數(shù)據(jù)庫。如下：

顯示創(chuàng)建語句

使用show create database 數(shù)據(jù)庫名SQL可以查看對應(yīng)數(shù)據(jù)庫的創(chuàng)建語句。如下：

說明一下：

• MySQL建議SQL中的關(guān)鍵字使用大寫，但不是必須的。
• 數(shù)據(jù)庫的名字加上反引號，是為了防止使用的數(shù)據(jù)庫名與關(guān)鍵字沖突。
• 80016指的是 MySQL 版本號：8.0.16。
• /*!80016 DEFAULT ENCRYPTION='N' */不是注釋，作用是告訴 MySQL 8.0.16+，建表時默認(rèn)不開啟表級加密；而低版本 MySQL 不支持表級加密，所以直接忽略。

修改數(shù)據(jù)庫

ALTER DATABASE db_name
 [alter_spacification [,alter_spacification]...]
 alter_spacification:
 [DEFAULT] CHARACTER SET charset_name
 [DEFAULT] COLLATE collation_name

說明： 對數(shù)據(jù)庫的修改主要指的是修改數(shù)據(jù)庫的字符集，校驗規(guī)則

比如將 db3 數(shù)據(jù)庫字符集改成 gbk

刪除數(shù)據(jù)庫

刪除數(shù)據(jù)庫的SQL如下：

DROP DATABASE [IF EXISTS] db_name;

刪除數(shù)據(jù)庫后該數(shù)據(jù)庫對應(yīng)的文件夾就被刪除了。比如：

并且刪除數(shù)據(jù)庫后，該數(shù)據(jù)庫下的所有表也都會被級聯(lián)刪除，因此不要隨意刪除數(shù)據(jù)庫。

備份和恢復(fù)

數(shù)據(jù)庫備份

使用如下命令即可對指定數(shù)據(jù)庫(helloworld)進(jìn)行備份：

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock helloworld > ~/back/helloworld.sql

• mysqldump MySQL 官方“邏輯備份”工具，導(dǎo)出表結(jié)構(gòu) + 數(shù)據(jù)為可執(zhí)行的 SQL。
• -u root指定 MySQL 用戶名為 root。
• -S /var/run/mysqld/mysqld.sock指定 UNIX Socket 文件連接 MySQL（免密登錄，本機(jī)直連，不走 TCP）

1.創(chuàng)建目錄

mkdir -p ~/back

家目錄下就創(chuàng)建了一個back目錄

back的擁有者和所屬組都是root，后面重定向時用戶無法讀寫該目錄所以要修改權(quán)限

sudo chown -R "$USER":"$USER" ~/back
chmod u+rwx ~/back

這兩條命令的作用就是把你家目錄下的 back 文件夾（以及里面的內(nèi)容）改成歸你自己所有、并確保你自己有讀寫執(zhí)行權(quán)限

2.進(jìn)行備份

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock helloworld > ~/back/helloworld.sql

打開helloworld.sql文件即可看到，文件中的內(nèi)容實際就是我們在該數(shù)據(jù)庫中執(zhí)行的各種SQL命令，包括創(chuàng)建數(shù)據(jù)庫、創(chuàng)建表、插入數(shù)據(jù)等SQL語句。如下：

數(shù)據(jù)庫恢復(fù)

為了演示數(shù)據(jù)庫恢復(fù)，我們先將剛才創(chuàng)建的數(shù)據(jù)庫刪除。如下：

這時讓MySQL服務(wù)器執(zhí)行如下命令即可對數(shù)據(jù)庫進(jìn)行恢復(fù)。如下：

# 1) 建庫（MySQL 8.0 可用 0900 排序規(guī)則；若是 5.7/MariaDB 用 utf8mb4_general_ci）
sudo mysql -S /var/run/mysqld/mysqld.sock -e \
"CREATE DATABASE IF NOT EXISTS helloworld CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"

# 2) 導(dǎo)入備份（把路徑替換成你的絕對路徑更穩(wěn)妥）
sudo mysql -S /var/run/mysqld/mysqld.sock helloworld < /home/lzg/back/helloworld.sql

同時該數(shù)據(jù)庫下的表，以及表當(dāng)中的數(shù)據(jù)也都恢復(fù)出來了。如下：

當(dāng)然還有省事的辦法

備份

sudo mysqldump -u root -S /var/run/mysqld/mysqld.sock -B helloworld > ~/back/helloworld.sql 

恢復(fù)

sudo mysql -S /var/run/mysqld/mysqld.sock < ~/back/helloworld.sql

查看連接情況

使用show processlistSQL即可查看當(dāng)前連接MySQL的用戶。比如：

說明一下：

• Id列：一個標(biāo)識，可以在MySQL中通過kill id殺死指定id的線程。
• User列：顯示當(dāng)前用戶，如果不是root，這個命令就只顯示你權(quán)限范圍內(nèi)的SQL語句。
• Host列：顯示這個語句是從哪個IP的哪個端口上發(fā)出的，可用來追蹤出問題語句的用戶。
• db列：當(dāng)前執(zhí)行的命令是在哪一個數(shù)據(jù)庫上，如果沒有指定數(shù)據(jù)庫，則該值為NULL。
• Command列：顯示當(dāng)前連接執(zhí)行的命令，一般就是休眠（Sleep）、查詢（Query）和連接（Connect）。
• Time列：表示該線程處于當(dāng)前狀態(tài)的時間，單位是秒。
• State列：顯示使用當(dāng)前連接的SQL語句的狀態(tài)。
• Info列：一般記錄的是線程執(zhí)行的語句，默認(rèn)只顯示前100個字符，如果要看全部信息，需要使用show full processlist。

show processlist可以告訴我們當(dāng)前有哪些用戶連接到我們的MySQL，如果查出某個用戶不是你正常登錄的，那么很有可能你的數(shù)據(jù)庫被人入侵了，以后如果發(fā)現(xiàn)自己的數(shù)據(jù)庫比較慢時，可以用這個SQL來查看數(shù)據(jù)庫連接情況。

總結(jié)

到此這篇關(guān)于MySQL庫的操作方法的文章就介紹到這了,更多相關(guān)ubuntu MySQL庫的操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！