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

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

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

語法：CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

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

說明：

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

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

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

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

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

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

創(chuàng)建好如下：

我們在前面也說過，創(chuàng)建一個數(shù)據(jù)庫其實就是在 Linux 下創(chuàng)建一個目錄，這里就不再重復(fù)介紹了。

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

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

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

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

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

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

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

如下：

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

		show charset;

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

		show collation;

（4）校驗規(guī)則對數(shù)據(jù)庫的影響

• 不區(qū)分大小寫

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

		create database test1 collate utf8_general_ci;

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

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

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

接下來我們篩選出 a 這個字符：select * from for_test where name='a';

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

• 區(qū)分大小寫

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

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

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

接下來我們篩選出 a 字符：

如上圖，因為該數(shù)據(jù)庫的檢驗規(guī)則為 utf8_ bin，進行區(qū)分大小寫的方式進行嚴格匹配，所以篩選出來的字符 a 就是字符 a.

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

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

		show databases;

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

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

例如：

說明：

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

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

語法：

		ALTER DATABASE db_name
		[alter_spacification [,alter_spacification]...]

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

說明：對數(shù)據(jù)庫的修改主要指的是修改數(shù)據(jù)庫的字符集，校驗規(guī)則。

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

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

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

語法：

		DROP DATABASE [IF EXISTS] db_ name;

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

如下：

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

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

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

5. 備份和恢復(fù)

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

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

語法：

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

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

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

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

接下來我們進入 mysql 中，把這個數(shù)據(jù)庫刪掉：

如上圖，test1 庫就被我們刪除了，接下來我們進行還原。

（2）還原

語法：

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

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

（3）拓展

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

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

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

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

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

6. 查看連接情況

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

語法：

		show processlist;

例如：

二、表的操作

1. 創(chuàng)建表

語法：

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

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

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

說明：

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

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

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

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

其中，它們分別表示：

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

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

2. 查看表

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

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

語法：desc 表明;

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

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

4. 修改表

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

		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 表添加一個字段，用于保存圖片路徑：alter table users add assets varchar(100) comment '圖片路徑' after birther;

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

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

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

我們再查看表中的數(shù)據(jù)，發(fā)現(xiàn) password 這一列的數(shù)據(jù)都不見了：

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

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

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

5. 刪除表

語法：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

例如：

再次查看：

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