一、庫操作

1. 創(chuàng)建數據庫

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 表示為數據庫名稱
• [ IF NOT EXISTS ] 表示可選項
• CHARACTER SET : 指定數據庫采用的字符集
• COLLATE : 指定數據庫字符集的校驗規(guī)則

?? 創(chuàng)建數據庫

create database if not exists test1;

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

如果已經存在同名的數據庫，則會報警，并且不會創(chuàng)建一個同名的數據庫。

如果我們想要刪除一個數據庫，直接輸入 drop database 數據庫名，當我們刪除一個數據庫后，該數據庫對應的目錄也會被刪除，若該目錄下有表存在，則也會被直接刪除。因此不建議隨意刪除數據庫。

創(chuàng)建數據庫的本質就是在 /var/lib/mysql 路徑下創(chuàng)建一個目錄，刪除數據庫的本質就是在 /var/lib/mysql 下刪除一個目錄。

2. 數據庫的編碼問題

創(chuàng)建數據庫時，有兩個編碼集：數據庫的字符集 和 數據庫的校驗集

數據庫的字符集 —— 數據庫存數據時，采用什么編碼

如果沒有對MySQL的配置文件進行過修改，則默認的編碼格式時utf8，默認的校驗規(guī)則是utf8_general_ci。

數據庫的校驗集 —— 支持數據庫，進行字段比較使用的編碼，本質是讀取數據庫中數據的采用的編碼格式 ( 查找或匹配數據庫，就需要進行比較字段，想要被比較，兩者就需要先被讀出來，此時兩者的校驗規(guī)則應該相同)

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

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

show variables like 'character_set_database';

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

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

show variables like 'collation_database';

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

查看數據庫支持的字符集

show charset;

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

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

show collation;

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

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

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

例如我們在存儲數據時是按照utf8的格式進行存儲的，那么將來在對數據進行做對比時也必須按照utf8的格式進行對比，因為 “存數據” 和 “取數據” 的方式必須保持一致。

?? 校驗規(guī)則對數據庫的影響

字符集編碼格式和校驗規(guī)則必須是對應的，但實際上同一種字符集編碼格式一般會有多種可選擇的校驗規(guī)則，比如 utf8編碼格式的校驗規(guī)則有 utf8_general_ci、utf8_bin、utf8_unicode_ci等校驗規(guī)則。使用不同的校驗規(guī)則操作數據庫中的數據可能會得到不同的結果。例如：utf8_general_ci校驗規(guī)則在進行比對數據時是不區(qū)分大小寫的，而utf8_bin校驗規(guī)則在對比數據時是區(qū)分大小寫的。

3. 操縱數據庫

查看數據庫

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

show databases;

顯示創(chuàng)建語句

使用 show create database 數據庫名 SQL 可以查看對應數據庫的創(chuàng)建語句。

show create database test1;

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

修改數據庫

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

這里的修改數據庫指的是修改數據庫的字符集或校驗規(guī)則。

刪除數據庫

DROP DATABASE [IF EXISTS] db_name;

刪除數據庫后該數據庫對應的文件夾就被刪除了。

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

4. 數據庫的備份和恢復

數據庫的備份

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

對指定數據庫進行備份的命令：

mysqldump -P 端口號 -u 用戶名 -p 密碼 -B 數據庫名1 數據庫名2 ... > 數據庫備份存儲的文件路徑

打開test.sql，我們可以發(fā)現(xiàn)文件中的內容就是我們在該數據庫中執(zhí)行的各種SQL命令，包括創(chuàng)建數據庫、創(chuàng)建表、插入數據等SQL語句。

數據庫的恢復

恢復數據庫的SQL語句：

source 數據庫備份存儲的文件路徑

先將指定數據庫刪除：

恢復數據庫：

source /root/MySQL/test.sql

這里我們可以看到數據庫已經被恢復出來了。

表的刪除與恢復

?? 表備份

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

將表刪除掉：

?? 表恢復

source /root/MySQL/table.sql;

這里我們看到表已經被完全恢復出來了。

5. 查看連接情況

使用 show processlist SQL來查看當前連接MySQL的用戶。

show processlist;

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

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

二、表操作

表操作至少需要會下面兩類的SQL語句：

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

創(chuàng)建表

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

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中大寫的表示關鍵字，[ ]中代表的是可選項。
• field表示列名，datatype表示列的類型。
• CHARSET用于指定表所采用的編碼格式，如果沒有指定則以所在數據庫的編碼格式為準。
• COLLATE用于指定表所采用的校驗規(guī)則，如果沒有指定則以所在數據庫的校驗規(guī)則為準。
• ENGINE用于指定表所采用的存儲引擎。
• COMMENT用于對指定列添加注釋信息。

這里我們需要注意的是：MySQL默認使用的是InnoDB存儲引擎，如果沒有指定使用哪種存儲引擎那么就會默認使用InnoDB存儲引擎。

?? 創(chuàng)建表

查看表結構

使用 desc 表名 SQL可以查看表的結構。

desc user;

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

如果想要查看創(chuàng)建表時的相關細節(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語句中的after表示將該列新增到哪一列之后，如果想要將新增列放在第一列，可以將after及其之后的語句換成 not null first。

通過desc命令查看確認新增成功。

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

?? 修改列類型

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

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

這里需要注意的是，如果需要修列類型后仍然保留comment字段，需要在修改時重新指定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;

刪除列后，該列所對應的內容全部都沒有了。

刪除表

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

• 在創(chuàng)建表語句中加上TEMPORARY關鍵字，那么服務器將創(chuàng)建出一個臨時表，該表會在你與服務器的會話終止時自動消失。
• TEMPORARY表的名字可以與某個已有的永久表相同，當有TEMPORARY表存在時，對應的永久表會隱藏起來（即無法訪問）。
• 為了避免重新連接后（TEMPORARY已經不存在），在未做檢測的情況下調用DROP誤刪了對應永久表，因此在使用DROP刪除臨時表時需要帶上TEMPORARY關鍵字。

刪除掉user表如下：

總結 

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

最新評論