一、庫(kù)操作

1. 創(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

• create database 表示為 固定用法
• db_name 表示為數(shù)據(jù)庫(kù)名稱
• [ IF NOT EXISTS ] 表示可選項(xiàng)
• CHARACTER SET : 指定數(shù)據(jù)庫(kù)采用的字符集
• COLLATE : 指定數(shù)據(jù)庫(kù)字符集的校驗(yàn)規(guī)則

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

create database if not exists test1;

IF NOT EXISTS 表示：當(dāng)前數(shù)據(jù)庫(kù)不存在 test1，則創(chuàng)建數(shù)據(jù)庫(kù)。

如果已經(jīng)存在同名的數(shù)據(jù)庫(kù)，則會(huì)報(bào)警，并且不會(huì)創(chuàng)建一個(gè)同名的數(shù)據(jù)庫(kù)。

如果我們想要?jiǎng)h除一個(gè)數(shù)據(jù)庫(kù)，直接輸入 drop database 數(shù)據(jù)庫(kù)名，當(dāng)我們刪除一個(gè)數(shù)據(jù)庫(kù)后，該數(shù)據(jù)庫(kù)對(duì)應(yīng)的目錄也會(huì)被刪除，若該目錄下有表存在，則也會(huì)被直接刪除。因此不建議隨意刪除數(shù)據(jù)庫(kù)。

創(chuàng)建數(shù)據(jù)庫(kù)的本質(zhì)就是在 /var/lib/mysql 路徑下創(chuàng)建一個(gè)目錄，刪除數(shù)據(jù)庫(kù)的本質(zhì)就是在 /var/lib/mysql 下刪除一個(gè)目錄。

2. 數(shù)據(jù)庫(kù)的編碼問(wèn)題

創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，有兩個(gè)編碼集：數(shù)據(jù)庫(kù)的字符集 和 數(shù)據(jù)庫(kù)的校驗(yàn)集

數(shù)據(jù)庫(kù)的字符集 —— 數(shù)據(jù)庫(kù)存數(shù)據(jù)時(shí)，采用什么編碼

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

數(shù)據(jù)庫(kù)的校驗(yàn)集 —— 支持?jǐn)?shù)據(jù)庫(kù)，進(jìn)行字段比較使用的編碼，本質(zhì)是讀取數(shù)據(jù)庫(kù)中數(shù)據(jù)的采用的編碼格式 ( 查找或匹配數(shù)據(jù)庫(kù)，就需要進(jìn)行比較字段，想要被比較，兩者就需要先被讀出來(lái)，此時(shí)兩者的校驗(yàn)規(guī)則應(yīng)該相同)

查看系統(tǒng)默認(rèn)支持的字符集

?? 系統(tǒng)默認(rèn)支持的字符集

show variables like 'character_set_database';

通過(guò)查看MySQL系統(tǒng)變量variables 當(dāng)中的 character_set_database ，可以看到系統(tǒng)默認(rèn)的字符集。

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

show variables like 'collation_database';

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

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

show charset;

字符集主要是控制用什么語(yǔ)言，例如：utf8 就可以使用中文。

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

show collation;

校驗(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ì)比。

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

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

字符集編碼格式和校驗(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é)果。例如：utf8_general_ci校驗(yàn)規(guī)則在進(jìn)行比對(duì)數(shù)據(jù)時(shí)是不區(qū)分大小寫的，而utf8_bin校驗(yàn)規(guī)則在對(duì)比數(shù)據(jù)時(shí)是區(qū)分大小寫的。

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

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

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

show databases;

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

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

show create database test1;

/*!40100 DEFAULT CHARACTER SET utf8 */ 不是表示注釋，而是表示當(dāng)前MySQL的版本如果大于 4.10，則執(zhí)行后面的SQL語(yǔ)句。

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

ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];

這里的修改數(shù)據(jù)庫(kù)指的是修改數(shù)據(jù)庫(kù)的字符集或校驗(yàn)規(guī)則。

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

DROP DATABASE [IF EXISTS] db_name;

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

這里需要說(shuō)明的是，刪除數(shù)據(jù)庫(kù)后，該數(shù)據(jù)庫(kù)下的所有表也會(huì)都被級(jí)聯(lián)刪除，因此不要隨便刪除數(shù)據(jù)庫(kù)。

4. 數(shù)據(jù)庫(kù)的備份和恢復(fù)

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

首先，我們先創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)并且向其中插入一張表。

對(duì)指定數(shù)據(jù)庫(kù)進(jìn)行備份的命令：

mysqldump -P 端口號(hào) -u 用戶名 -p 密碼 -B 數(shù)據(jù)庫(kù)名1 數(shù)據(jù)庫(kù)名2 ... > 數(shù)據(jù)庫(kù)備份存儲(chǔ)的文件路徑

打開(kāi)test.sql，我們可以發(fā)現(xiàn)文件中的內(nèi)容就是我們?cè)谠摂?shù)據(jù)庫(kù)中執(zhí)行的各種SQL命令，包括創(chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建表、插入數(shù)據(jù)等SQL語(yǔ)句。

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

恢復(fù)數(shù)據(jù)庫(kù)的SQL語(yǔ)句：

source 數(shù)據(jù)庫(kù)備份存儲(chǔ)的文件路徑

先將指定數(shù)據(jù)庫(kù)刪除：

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

source /root/MySQL/test.sql

這里我們可以看到數(shù)據(jù)庫(kù)已經(jīng)被恢復(fù)出來(lái)了。

表的刪除與恢復(fù)

?? 表備份

mysqldump -P3306 -u root -p Test student > /root/MySQL/table.sql

將表刪除掉：

?? 表恢復(fù)

source /root/MySQL/table.sql;

這里我們看到表已經(jīng)被完全恢復(fù)出來(lái)了。

5. 查看連接情況

使用 show processlist SQL來(lái)查看當(dāng)前連接MySQL的用戶。

show processlist;

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

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

二、表操作

表操作至少需要會(huì)下面兩類的SQL語(yǔ)句：

• DDL（Data Definition Language）數(shù)據(jù)定義語(yǔ)言：比如建表、刪表、該表、新增列、刪除列等。
• DML（Data Manipulation Language）數(shù)據(jù)操作語(yǔ)言：比如插入記錄、刪除記錄、修改記錄等。

創(chuàng)建表

創(chuàng)建表的SQL語(yǔ)句如下：

CREATE TABLE [IF NOT EXISTS] table_name(
	field1 datatype1 [COMMENT '注釋信息'],
	field2 datatype2 [COMMENT '注釋信息'],
	field3 datatype3  [COMMENT '注釋信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

• SQL中大寫的表示關(guān)鍵字，[ ]中代表的是可選項(xiàng)。
• field表示列名，datatype表示列的類型。
• CHARSET用于指定表所采用的編碼格式，如果沒(méi)有指定則以所在數(shù)據(jù)庫(kù)的編碼格式為準(zhǔn)。
• COLLATE用于指定表所采用的校驗(yàn)規(guī)則，如果沒(méi)有指定則以所在數(shù)據(jù)庫(kù)的校驗(yàn)規(guī)則為準(zhǔn)。
• ENGINE用于指定表所采用的存儲(chǔ)引擎。
• COMMENT用于對(duì)指定列添加注釋信息。

這里我們需要注意的是：MySQL默認(rèn)使用的是InnoDB存儲(chǔ)引擎，如果沒(méi)有指定使用哪種存儲(chǔ)引擎那么就會(huì)默認(rèn)使用InnoDB存儲(chǔ)引擎。

?? 創(chuàng)建表

查看表結(jié)構(gòu)

使用 desc 表名 SQL可以查看表的結(jié)構(gòu)。

desc user;

• Field表示該字段的名字。
• Type表示該字段的類型。
• Null表示該字段是否允許為空。
• Key表示索引類型，比如主鍵索引為PRI。
• Default表示該字段的默認(rèn)值。
• Extra表示該字段的額外信息說(shuō)明。

如果想要查看創(chuàng)建表時(shí)的相關(guān)細(xì)節(jié)，可以使用 show create table 表名SQL。如下：

show create table user\G

修改表

修改表的 SQL如下：

ALTER TABLE table_name ADD 新增列名 新增列的屬性;
ALTER TABLE table_name MODIFY 列名 修改后的列屬性;
ALTER TABLE table_name DROP 列名;
ALTER TABLE table_name RENAME [TO] 新表名;
ALTER TABLE table_name CHANGE 列名 新列名 新列屬性;

先看一下表中原有的信息：

?? 新增列

alter table user add path varchar(128) comment '照片路徑' after password;

新增一列SQL語(yǔ)句中的after表示將該列新增到哪一列之后，如果想要將新增列放在第一列，可以將after及其之后的語(yǔ)句換成 not null first。

通過(guò)desc命令查看確認(rèn)新增成功。

這時(shí)我們插入一條用戶信息后查看表中的信息就一目了然了。

?? 修改列類型

將user表中的password列的類型由char(30)改成char(50)。如下：

alter table user modify password char(50) comment '用戶密碼';

這里需要注意的是，如果需要修列類型后仍然保留comment字段，需要在修改時(shí)重新指定comment字段。

?? 修改列名

將user表中的password列的列明改成passwd。如下：

alter table user change password passwd char(50) comment '用戶密碼';

?? 修改表名

將表user的名字改成People

alter table user rename People;

?? 刪除列

將user表中的birthday列刪除。

alter table user drop birthday;

刪除列后，該列所對(duì)應(yīng)的內(nèi)容全部都沒(méi)有了。

刪除表

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

• 在創(chuàng)建表語(yǔ)句中加上TEMPORARY關(guān)鍵字，那么服務(wù)器將創(chuàng)建出一個(gè)臨時(shí)表，該表會(huì)在你與服務(wù)器的會(huì)話終止時(shí)自動(dòng)消失。
• TEMPORARY表的名字可以與某個(gè)已有的永久表相同，當(dāng)有TEMPORARY表存在時(shí)，對(duì)應(yīng)的永久表會(huì)隱藏起來(lái)（即無(wú)法訪問(wèn)）。
• 為了避免重新連接后（TEMPORARY已經(jīng)不存在），在未做檢測(cè)的情況下調(diào)用DROP誤刪了對(duì)應(yīng)永久表，因此在使用DROP刪除臨時(shí)表時(shí)需要帶上TEMPORARY關(guān)鍵字。

刪除掉user表如下：

總結(jié) 

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

最新評(píng)論