【MySQL】數據庫和表的操作詳解

一、數據庫：

1、查看數據庫：

2、創建數據庫：

3、刪除數據庫：

4、數據庫的編碼問題：

5、校驗規則對數據庫的影響：

6、修改數據庫：

7、庫的備份與恢復：

8、查看鏈接情況：

二、表的操作：

1、創建表：

2、查看表：

3、修改表：

插入數據：

修改表名：

增加字段屬性：

?編輯

修改字段的數據類型：

修改字段的名稱：

刪除某一字段：

表的刪除和備份：

刪除表：

一、數據庫：

1、查看數據庫：

首先，在創建數據庫之前，要先查看當前有哪些數據庫：

show databases;

這些本質是在Linux中，/var/lib/mysql目錄下創建的目錄

當創建了很多數據庫，我們無法知道是正在使用哪一個數據庫的，使用需要用SQL語句

select database();

這樣，在use后，使用SQL語句就能夠查看當前正在使用哪一個數據庫了

如何知道創建數據庫時的詳細信息：

show create database 數據庫名;

理解：

test1創建時，可以看到關鍵字是大寫的，可以這樣也可以不這樣

數據庫的名字是用引號引起來了，是為了和關鍵字區分

后面/**/不是注釋，其表示當MySQL版本大于4.1.00的時候，執行其后面SQL語句，當MySQL版本大于8.0.16的時，執行其后面的語句

2、創建數據庫：

使用SQL語句：

create database [if not exists] 數據庫名;

如果創建重復名的數據庫，就會出錯，可以加上if not exists選項，它的意思是如果不存在數據庫就創建

當我們加上這個選項后，可以發現沒有error了，取而代之的是warning

3、刪除數據庫：

使用SQL語句：

drop database 數據庫名;

刪除數據庫在Linux下的體現就是刪除對應路徑下的目錄

如果刪除不存在的數據庫是會報錯的，如果想讓這個報錯改為警告可以加上if exists

注意：

一般不要輕易刪除數據庫，畢竟這里和Linux中刪除目錄差不多，如果刪除，里面的所有數據就都沒了

4、數據庫的編碼問題：

在系統創建的時候，有兩個編碼集合，字符集和校驗集

字符集

字符集表示數據庫的內容寫的時候采用的編碼，用于規定未來存儲時的編碼格式

查看MySQL中默認的字符集用SQL語句：

show variables like 'character_set_database';

查看MySQL支持的所有字符集：

使用SQL語句：

show charset;

校驗集

校驗集表示在已經有數據，進行讀的時候以特定的方式去讀，對讀到的數據做特定的解釋，規定數據查詢時的校驗規則

查看MySQL中默認的校驗集用SQL語句：

show variables like 'collation_database';

查看MySQL支持的所有字符集：

show conllation;

使用SQL語句：（有很長，這里只截取部分）

使用指定編碼創建數據庫

// 設置字符集
create database 數據庫名稱 [charset=字符集 || character set 字符集];
// 設置校驗集
create database 數據庫名稱 charset=utf8mb4 collate 校驗集;

5、校驗規則對數據庫的影響：

首先，我們要知道：表是繼承其所在數據庫的編碼格式的

那么，我們創建兩個數據庫，分別用不同的校驗規則：其中一個是區分大小寫的，一個是不區分大小寫的

//不區分大小寫
create database test1 collation utf8_general_ci;
//區分大小寫
create database test2 collate utf8_bin;

接著，分別在里面創建一張表，并且在表中插入大寫和小寫的a

其中test1是不區分大小寫的，test2是區分大小寫的

在插入后，直接查找表中對應的a，

select * from testtable where id='a';

我們應該會發現：如果是test1的話，查找a會出現a和A；如果是test2的話，查找a只是出現a

如上，結果如期

6、修改數據庫：

修改數據庫編碼格式：

使用指令：

alter databas 數據庫名 charset=修改后的字符規則 COLLATE=修改后的校驗規則

如將test1進行的字符編碼規則和字符校驗規則進行修改

alter database test1 charset=gbk collate gbk_chinese__ci;

7、庫的備份與恢復：

備份：

將一個數據庫進行備份采用SQL語句：

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

接下來舉個例子：

mysqldump -P3306 -uroot -p -B test1 > test.sql

如上，這就是對我們的test1數據庫進行備份了，對這個文件進行查看，我們可以知道其是備份了我們的SQL語句和數據

恢復：

恢復的話是在MySQL客戶端中，用SQL語句：

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

我們已經把test1數據庫備份好了，那么我們接下來將這個test1數據庫刪了，然后在恢復：

然后在執行SQL語句進行數據庫的恢復

source /home/silence/MySQL/test.sql;

這樣，我們的test1數據庫就恢復了（包括里面的表）

也可以只備份表

mysqldump -u root -p -P 端口 數據庫名 表名 > 目標路徑(含文件)

8、查看鏈接情況：

MySQL支持多用戶的訪問，也可能會被惡意訪問，可以通過如下SQL語句進行查看MySQL語句的鏈接

show processlist;

二、表的操作：

創建表是在數據庫中創建的，所以在進行表的創建的時候，要先use數據庫表示進入對應數據庫

1、創建表：

我們創建表的SQL語句是：

create table [if not exists] 表名(成員名1 成員類型1 [comment '注釋信息'],成員名2 成員類型2 [comment '注釋信息'],成員名3 成員類型3  [comment '注釋信息']
)[charset=字符編碼] [collate=校驗規則] [engine=引擎名];

以上comment和其注釋信息是可以忽略的，并且下面的charset，collate，engine也是可以不寫的，如果不寫的話就為系統默認

這里采用MyIsam引擎

這里采用InnoDB引擎

當創建成功后，發現person1和person2在Linux下，顯示的目錄是不一樣的，現在我只需要知道不同的引擎在Linux下的顯示是不一樣的，在后面的MySQL索引在講解

2、查看表：

查看表之前要在數據庫中，所以要先保證在數據庫中，然后在進行表的查看

SQL語句：

show tables;

這是進行表的查看，表示當前數據庫中有哪些表

desc 表名;

這個SQL語句是查看表的詳細信息

Field表示列名
Type表示類型
Null表示是否可為空
Key表示是否存在主鍵/外鍵/唯一鍵約束
Default表示是否有默認值
Extra表示額外信息

如果想看建表時的信息，創建表的時候更詳細的信息，比如所采用的字符編碼或者校驗碼或者所使用的引擎，那就需要使用：

show create table 表名 \G

3、修改表：

插入數據：

在修改前，表中需要有數據，那么首先我們向我們的表中插入數據

insert into 表名 [(字段1,字段2, ...)] values (數據1,數據2, ... 要和前面對應);

其中，如果屬性名不寫就是默認所有的

如下，這里插入兩行數據

創建成功后可以使用SQL語句來查看

select * from 表名;

修改表名：

SQL語句：

alter table 原表名稱 rename [to] 修改后的表名稱;

如下，當我們對表名進行修改后，再進行查看，發現修改成功

增加字段屬性：

如上，原本有3個字段屬性，并且在查看創建的時候能夠看到我們給每一個字段屬性的備注

接下來增加字段屬性：

用SQL語句：

alter table 表名 add 字段名稱 數據類型 備注;

這樣就能夠通過show命令查看到了增加的字段以及備注了

并且在表中的數據也會增加，由于我們沒有插入數據，所以是空

在SQL語句中，最后面還可以增加after 字段屬性表示將數據增加到對應字段屬性后

alter table 表名 add 字段名稱 數據類型 備注 after 字段屬性;

如果想插在第一行，將after和其后面的字段屬性換成not null first

修改字段的數據類型：

就是修改上述的Type用到SQL語句：

alter table 表名 modify 待修改字段名 新的數據類型 備注;

實際上，這是覆蓋式的修改的，并不是只將數據類型修改

如上，這里修改的時候沒有加上對應的備注，在修改后查看發現原來的備注沒有的，證明這里是覆蓋式的修改的

修改字段的名稱：

用SQL語句：

alter table 表名 change 原字段名 修改后字段名 數據類型 備注;

如下是將birth改為birthday，這里也是覆蓋修改的，如下我沒有加備注，當再次看的時候也沒有了，當然也可以自己重新定義

并且類型也是重新定義的

刪除某一字段：

alter table 表名 drop 字段名稱;

將ages刪除

表的刪除和備份：

因為表的刪除是不可逆的，所以，在刪除表之前最好做一下備份

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

將表備份并存儲在當前目錄下：

恢復：

source 表備份存儲的文件路徑

當恢復后就都回來了

刪除表：

drop table 表名稱;

當刪除后就沒了

這是很危險的，當刪除表后期所有數據都沒了，在開發中不要輕易刪除