數(shù)據(jù)庫(kù)和表的操作

一、數(shù)據(jù)庫(kù)的操作

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

語(yǔ)法：CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

	create_specification:
	[DEFAULT] CHARACTER SET charset_name
	[DEFAULT] COLLATE collation_name

說(shuō)明：

• 大寫的表示關(guān)鍵字，mysql 不區(qū)分大小寫，所以也可以用小寫
• [] 是可選項(xiàng)
• CHARACTER SET: 指定數(shù)據(jù)庫(kù)采用的字符集
• COLLATE: 指定數(shù)據(jù)庫(kù)字符集的校驗(yàn)規(guī)則

假設(shè)現(xiàn)在我們現(xiàn)在需要?jiǎng)?chuàng)建一個(gè)名為 d1 的數(shù)據(jù)庫(kù)，首先我們先查看一下數(shù)據(jù)庫(kù)，查看數(shù)據(jù)庫(kù)：show databases;

下面開始創(chuàng)建 d1 數(shù)據(jù)庫(kù)：create database d1;

如上，d1 數(shù)據(jù)庫(kù)就創(chuàng)建好了。

注意：當(dāng)我們創(chuàng)建數(shù)據(jù)庫(kù)沒(méi)有指定字符集和校驗(yàn)規(guī)則時(shí)，系統(tǒng)使用默認(rèn)字符集：utf8，校驗(yàn)規(guī)則是：utf8_ general_ ci.

• 創(chuàng)建一個(gè)使用 utf8 字符集的 d2 數(shù)據(jù)庫(kù)：create database d2 charset=utf8;
• 創(chuàng)建一個(gè)使用 utf8 字符集，并帶校對(duì)規(guī)則的 d3 數(shù)據(jù)庫(kù)：create database d3 charset=utf8 collate utf8_general_ci;

創(chuàng)建好如下：

我們?cè)谇懊嬉舱f(shuō)過(guò)，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)其實(shí)就是在 Linux 下創(chuàng)建一個(gè)目錄，這里就不再重復(fù)介紹了。

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

當(dāng)我們創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候，有兩個(gè)編碼集：

1. 數(shù)據(jù)庫(kù)編碼集 - - - 數(shù)據(jù)庫(kù)未來(lái)存儲(chǔ)數(shù)據(jù)所采用的編碼集；
2. 數(shù)據(jù)庫(kù)校驗(yàn)集 - - - 支持?jǐn)?shù)據(jù)庫(kù)，進(jìn)行字段比較使用的編碼，本質(zhì)也是一種讀取數(shù)據(jù)庫(kù)中數(shù)據(jù)所采用的編碼格式；

所以數(shù)據(jù)庫(kù)無(wú)論對(duì)數(shù)據(jù)做任何操作，都必須保證操作和編碼必須是編碼一致的。

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

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

		show variables like 'character_set_database';  # 默認(rèn)字符集
		show variables like 'collation_database';      # 檢驗(yàn)規(guī)則

如下：

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

		show charset;

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

		show collation;

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

• 不區(qū)分大小寫

創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，校驗(yàn)規(guī)則使用 utf8_ general_ ci (不區(qū)分大小寫，即在檢驗(yàn)的時(shí)候不嚴(yán)格匹配，不對(duì)大小寫字母進(jìn)行區(qū)分)

		create database test1 collate utf8_general_ci;

隨后我們需要使用這個(gè)數(shù)據(jù)庫(kù)：use test1

然后我們?yōu)檫@個(gè)數(shù)據(jù)庫(kù)創(chuàng)建一張表，并插入一些數(shù)據(jù)，創(chuàng)建表和插入的語(yǔ)法我們先不做介紹，后面再介紹；如下：

接下來(lái)我們對(duì)這個(gè)表的插入結(jié)果進(jìn)行查看，注意，該表的校驗(yàn)方法是不進(jìn)行區(qū)分大小寫進(jìn)行匹配的；所以我們先查看整個(gè)表的情況：select * from for_test;

接下來(lái)我們篩選出 a 這個(gè)字符：select * from for_test where name='a';

我們可以看到，數(shù)據(jù)庫(kù)在匹配 a 這個(gè)字符的時(shí)候不進(jìn)行大小寫區(qū)分，無(wú)論大寫還是小寫都給我們顯示出來(lái)了。

• 區(qū)分大小寫

創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，校驗(yàn)規(guī)則使用 utf8_ bin (區(qū)分大小寫，校驗(yàn)時(shí)按照嚴(yán)格匹配的方式，區(qū)分大小寫)

我們按照上面的方式進(jìn)行創(chuàng)建，如下圖：

接下來(lái)我們查看該表的數(shù)據(jù)：

接下來(lái)我們篩選出 a 字符：

如上圖，因?yàn)樵摂?shù)據(jù)庫(kù)的檢驗(yàn)規(guī)則為 utf8_ bin，進(jìn)行區(qū)分大小寫的方式進(jìn)行嚴(yán)格匹配，所以篩選出來(lái)的字符 a 就是字符 a.

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

（1）查看數(shù)據(jù)庫(kù)

		show databases;

（2）顯示創(chuàng)建的語(yǔ)句

		show create database 數(shù)據(jù)庫(kù)名;

例如：

說(shuō)明：

• MySQL 建議我們關(guān)鍵字使用大寫，但是不是必須的；
• 數(shù)據(jù)庫(kù)名字的反引號(hào)``,是為了防止使用的數(shù)據(jù)庫(kù)名剛好是關(guān)鍵字
• / * !40100 default… * / 這個(gè)不是注釋，表示當(dāng)前mysql版本大于4.01版本，就執(zhí)行這句話；

（3）修改數(shù)據(jù)庫(kù)

語(yǔ)法：

		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ī)則。

假設(shè)將我們上面創(chuàng)建的 test1 數(shù)據(jù)庫(kù)的字符集改成 gbk：alter database test1 charset=gbk;

如上圖 test1 數(shù)據(jù)庫(kù)的字符集就修改成了 gbk.

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

語(yǔ)法：

		DROP DATABASE [IF EXISTS] db_ name;

例如我們刪掉我們前面建的 test2 數(shù)據(jù)庫(kù)：drop database test2;

如下：

如上圖，test2 數(shù)據(jù)庫(kù)就被刪除了。

執(zhí)行刪除之后的結(jié)果：

• 數(shù)據(jù)庫(kù)內(nèi)部看不到對(duì)應(yīng)的數(shù)據(jù)庫(kù)
• 對(duì)應(yīng)的數(shù)據(jù)庫(kù)文件夾被刪除，級(jí)聯(lián)刪除，里面的數(shù)據(jù)表全部被刪

5. 備份和恢復(fù)

（1）備份數(shù)據(jù)庫(kù)

在備份數(shù)據(jù)庫(kù)之前我們先需要退出 mysql.

語(yǔ)法：

		mysqldump -P3306 -u root -p密碼 -B 數(shù)據(jù)庫(kù)名 > 數(shù)據(jù)庫(kù)備份存儲(chǔ)的文件路徑

其中密碼部分我們可以不在命令行輸入，當(dāng)我們執(zhí)行這條命令的時(shí)候命令行會(huì)提示我們輸入。

例如我們把 test1 庫(kù)備份到文件中：mysqldump -P3306 -uroot -p -B test1 > /home/lmy/test1.sql

這時(shí)，可以打開看看 test1.sql 文件里的內(nèi)容，其實(shí)把我們整個(gè)創(chuàng)建數(shù)據(jù)庫(kù)，建表，導(dǎo)入數(shù)據(jù)的語(yǔ)句都裝載這個(gè)文件中。

接下來(lái)我們進(jìn)入 mysql 中，把這個(gè)數(shù)據(jù)庫(kù)刪掉：

如上圖，test1 庫(kù)就被我們刪除了，接下來(lái)我們進(jìn)行還原。

（2）還原

語(yǔ)法：

		source 數(shù)據(jù)庫(kù)備份的文件路徑;

我們?cè)?mysql 中輸入指令：source /home/lmy/test1.sql; 即可在 mysql 中恢復(fù) test1 庫(kù)：

（3）拓展

如果備份的不是整個(gè)數(shù)據(jù)庫(kù)，而是其中的一張表，怎么做？做法如下：

		mysqldump -uroot -p 數(shù)據(jù)庫(kù)名 表名1 表名2 > 備份文件路徑

如果同時(shí)備份多個(gè)數(shù)據(jù)庫(kù)，如下：

		mysqldump -uroot -p -B 數(shù)據(jù)庫(kù)名1 數(shù)據(jù)庫(kù)名2 ... > 數(shù)據(jù)庫(kù)存放路徑

如果備份一個(gè)數(shù)據(jù)庫(kù)時(shí)，沒(méi)有帶上 -B 參數(shù)， 在恢復(fù)數(shù)據(jù)庫(kù)時(shí)，需要先創(chuàng)建空數(shù)據(jù)庫(kù)，然后使用數(shù)據(jù)庫(kù)，再使用 source 來(lái)還原。

6. 查看連接情況

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

語(yǔ)法：

		show processlist;

例如：

二、表的操作

1. 創(chuàng)建表

語(yǔ)法：

		CREATE TABLE table_name (
			field1 datatype,
			field2 datatype,
			field3 datatype
		) character set 字符集 collate 校驗(yàn)規(guī)則 engine 存儲(chǔ)引擎;

在創(chuàng)建表之前需要指定數(shù)據(jù)庫(kù)，即使用：use 數(shù)據(jù)庫(kù); 為該數(shù)據(jù)庫(kù)創(chuàng)建表。

注意，數(shù)據(jù)庫(kù)的數(shù)據(jù)類型我們暫時(shí)先不介紹，后續(xù)會(huì)介紹。

說(shuō)明：

• field 表示列名
• datatype 表示列的類型
• character set 字符集，如果沒(méi)有指定字符集，則以所在數(shù)據(jù)庫(kù)的字符集為準(zhǔn)
• collate 校驗(yàn)規(guī)則，如果沒(méi)有指定校驗(yàn)規(guī)則，則以所在數(shù)據(jù)庫(kù)的校驗(yàn)規(guī)則為準(zhǔn)

例如我們創(chuàng)建一個(gè) users 表，里面存儲(chǔ)用戶的 id、用戶名、密碼、生日：

		create table users(
		    -> id int,
		    -> name varchar(20) comment '用戶名',
		    -> password char(20) comment '密碼',
		    -> birther date comment '生日'
		    -> ) character set utf8 engine MyISAM;

說(shuō)明：不同的存儲(chǔ)引擎，創(chuàng)建表的文件不一樣。users 表存儲(chǔ)引擎是 MyISAM ，在數(shù)據(jù)庫(kù)目錄中有三個(gè)不同的文件，我們可以進(jìn)入該目錄查看：cd /var/lib/mysql/d1，分別是：

其中，它們分別表示：

• users.frm：表結(jié)構(gòu)
• users.MYD：表數(shù)據(jù)
• users.MYI：表索引

而 db.opt 則是該數(shù)據(jù)庫(kù)對(duì)應(yīng)的字符集和檢驗(yàn)規(guī)則。

2. 查看表

上面我們已經(jīng)創(chuàng)建好了一張 users 表，此時(shí)我們可以查看該數(shù)據(jù)庫(kù)有哪些表：show tables;

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

語(yǔ)法：desc 表明;

例如查看 users 表的結(jié)構(gòu)：

以上就是表的結(jié)構(gòu)中的介紹，我們后面會(huì)詳細(xì)介紹每一列的功能的。

4. 修改表

在項(xiàng)目實(shí)際開發(fā)中，經(jīng)常修改某個(gè)表的結(jié)構(gòu)，比如字段名字，字段大小，字段類型，表的字符集類型，表的存儲(chǔ)引擎等等。我們還有需求，添加字段，刪除字段等等；這時(shí)我們就需要修改表。

		ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
		datatype]...);  # 添加
		
		ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
		datatype]...);  # 修改
		
		ALTER TABLE tablename DROP (column);  # 刪除

例如：

先在 users 表添加兩條記錄：

	mysql> insert into users values(1, 'a', 'b', '2000-01-01'), 
	    -> (2, 'c', 'd', '2000-01-02');

• 在 users 表添加一個(gè)字段，用于保存圖片路徑：alter table users add assets varchar(100) comment '圖片路徑' after birther;

插入新字段后，我們查看原表的數(shù)據(jù)，對(duì)原來(lái)表中的數(shù)據(jù)沒(méi)有影響：

• 修改 name，將其長(zhǎng)度改成 60： alter table users modify name varchar(60);

• 刪除 password 列： alter table users drop password;

我們?cè)俨榭幢碇械臄?shù)據(jù)，發(fā)現(xiàn) password 這一列的數(shù)據(jù)都不見了：

所以刪除字段一定要小心，刪除字段及其對(duì)應(yīng)的列數(shù)據(jù)都沒(méi)了。

• 修改表名為 employee： alter table users rename to employee; ，其中 to 可以省略

• 將 name 列修改為 xingming： alter table employee change name xingming varchar(60); ，新字段需要完整定義

5. 刪除表

語(yǔ)法：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

例如：

再次查看：

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

最新評(píng)論