MySQL庫(kù)的操作

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

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

說(shuō)明：

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

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

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

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

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

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

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

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

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

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

注意： SQL中的collate utf8_general_ci 也可以寫(xiě)成。collate=utf8_general_ci

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

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

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

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

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

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

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

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

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

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

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

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

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

校驗(yàn)規(guī)則對(duì)數(shù)據(jù)庫(kù)的影響

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

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

比如我們存儲(chǔ)數(shù)據(jù)的時(shí)候是按照utf8mb4的格式進(jìn)行存儲(chǔ)的，那么將來(lái)在對(duì)數(shù)據(jù)做比對(duì)時(shí)也必須按照utf8mb4的格式進(jìn)行比對(duì)，因?yàn)?ldquo;存數(shù)據(jù)”和“取數(shù)據(jù)”的方式必須保持一致。

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

校驗(yàn)規(guī)則對(duì)數(shù)據(jù)庫(kù)的影響

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

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

我們以?xún)蓚€(gè)庫(kù)test1和test2來(lái)比對(duì)兩種校驗(yàn)規(guī)則

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

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

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

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

進(jìn)行查詢(xún)

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

• 區(qū)分大小寫(xiě)的查詢(xún)以及結(jié)果

結(jié)果排序

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

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

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

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

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

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

顯示創(chuàng)建語(yǔ)句

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

說(shuō)明一下：

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

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

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

說(shuō)明： 對(duì)數(shù)據(jù)庫(kù)的修改主要指的是修改數(shù)據(jù)庫(kù)的字符集，校驗(yàn)規(guī)則

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

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

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

DROP DATABASE [IF EXISTS] db_name;

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

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

備份和恢復(fù)

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

使用如下命令即可對(duì)指定數(shù)據(jù)庫(kù)(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 用戶(hù)名為 root。
• -S /var/run/mysqld/mysqld.sock指定 UNIX Socket 文件連接 MySQL（免密登錄，本機(jī)直連，不走 TCP）

1.創(chuàng)建目錄

mkdir -p ~/back

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

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

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

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

2.進(jìn)行備份

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

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

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

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

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

# 1) 建庫(kù)（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)入備份（把路徑替換成你的絕對(duì)路徑更穩(wěn)妥）
sudo mysql -S /var/run/mysqld/mysqld.sock helloworld < /home/lzg/back/helloworld.sql

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

當(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的用戶(hù)。比如：

說(shuō)明一下：

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

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

總結(jié)

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

最新評(píng)論