.jpg)
mysql有很多的可視化管理工具，比如“mysql-workbench”和“sequel-pro-”。 現(xiàn)在我寫mysql的終端命令操作的文章，是想強(qiáng)化一下自己對(duì)于mysql的理解，總會(huì)比使用圖形化的理解透徹，因?yàn)槲冶緛砭捅容^喜歡寫代碼。同時(shí)寫出來這些文章，是想要給大家當(dāng)個(gè)參考，希望也能對(duì)大家有所幫助，有所提升，這就是我為什么要寫終端操作mysql的文章了。
注意：MySQL數(shù)據(jù)庫命令不區(qū)分大小寫。但在MAC的終端，如果你想使用tab自動(dòng)補(bǔ)全命令，那么你就必須使用大寫，這樣MAC的終端才會(huì)幫你補(bǔ)全命令，否則你按N遍tab都不會(huì)有響應(yīng)。
1、數(shù)據(jù)庫（database）管理
1.1 create 創(chuàng)建數(shù)據(jù)庫
create?database?firstDB;
1.2 show 查看所有數(shù)據(jù)庫
mysql>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?firstDB????????????| |?mysql??????????????| |?performance_schema?| +--------------------+ 4?rows?in?set?(0.00?sec)
1.3 alter 修改數(shù)據(jù)庫
alter 命令修改數(shù)據(jù)庫編碼：
默認(rèn)創(chuàng)建的數(shù)據(jù)庫默認(rèn)不支持中文字符，如果我們需要它支持中文字符，則將它的編碼設(shè)置為utf8格式：
mysql>?ALTER?DATABASE?testDB?CHARACTER?SET?UTF8; Query?OK,?1?row?affected?(0.00?sec)
1.4 use 使用數(shù)據(jù)庫
mysql>?use?firstDB; Database?changed
1.5 查看當(dāng)前使用的數(shù)據(jù)庫
mysql>?select?database(); +------------+ |?database()?| +------------+ |?firstdb????| +------------+ 1?row?in?set?(0.00?sec)
1.6 drop 刪除數(shù)據(jù)庫
mysql>?drop?database?firstDB; Query?OK,?0?rows?affected?(0.00?sec)
2、數(shù)據(jù)表（table）管理
我們首先創(chuàng)建一個(gè)數(shù)據(jù)庫，提供我們往后的使用：
mysql>?create?database?testDB; Query?OK,?1?row?affected?(0.00?sec)
創(chuàng)建后記得用use命令進(jìn)入（使用）數(shù)據(jù)庫，不然后面的操作都會(huì)不成功的。
2.1?create?創(chuàng)建表
mysql>?create?table?PEOPLE?( ????->?ID?int?AUTO_INCREMENT?PRIMARY?KEY, ????->?NAME?varchar(20)?not?null, ????->?AGE?int?not?null, ????->?BIRTHDAY?datetime);????????????????????????????????????????????????????? Query?OK,?0?rows?affected?(0.01?sec)
2.2 show 顯示表
顯示當(dāng)前數(shù)據(jù)庫所有的數(shù)據(jù)表
mysql>?show?tables; +------------------+ |?Tables_in_testdb?| +------------------+ |?PEOPLE???????????| +------------------+ 1?row?in?set?(0.00?sec)
2.3 desc 查看表結(jié)構(gòu)
mysql>?desc?PEOPLE ????->?; +----------+-------------+------+-----+---------+----------------+ |?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+ |?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 4?rows?in?set?(0.01?sec)
2.4 alter 修改表結(jié)構(gòu)（增、刪、改）
默認(rèn)創(chuàng)建的表不支持中文字符，所以需將表編碼設(shè)置為utf8：
mysql>?ALTER?TABLE?KEYCHAIN?CONVERT?TO?CHARACTER?SET?UTF8; Query?OK,?1?row?affected?(0.02?sec) Records:?1??Duplicates:?0??Warnings:?0
2.4.1 insert 在表中添加列（字段）
mysql>?alter?table?PEOPLE?add?star?BOOL; Query?OK,?0?rows?affected?(0.02?sec) Records:?0??Duplicates:?0??Warnings:?0
提示：在MySQL里，布爾類型會(huì)自動(dòng)轉(zhuǎn)換為tinyint(1)類型。
我們不妨使用desc去查看一下PEOPLE表結(jié)構(gòu)：
mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+ |?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+ |?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| |?star?????|?tinyint(1)??|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 5?rows?in?set?(0.00?sec)
現(xiàn)在，你該相信我了吧？
2.4.2 alter 修改表（列）字段
mysql>?alter?table?PEOPLE?MODIFY?star?int; Query?OK,?0?rows?affected?(0.01?sec) Records:?0??Duplicates:?0??Warnings:?0
我們再次使用desc查看PEOPLE表結(jié)構(gòu)：
mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+ |?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+ |?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| |?star?????|?int(11)?????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 5?rows?in?set?(0.00?sec)
2.4.3 delete 刪除表（列）字段
mysql>?alter?table?PEOPLE?DROP?column?star; Query?OK,?0?rows?affected?(0.02?sec) Records:?0??Duplicates:?0??Warnings:?0
刪除后，再次查看PEOPLE表結(jié)構(gòu)：
mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+ |?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+ |?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 4?rows?in?set?(0.00?sec)
刪除字段成功，現(xiàn)在我們已經(jīng)不能看到star的字段了。
2.4.4 rename 重命名表名
mysql>?RENAME?TABLE?PEOPLE?TO?NEW_PEOPLE; Query?OK,?0?rows?affected?(0.00?sec)
2.5 create 利用已有數(shù)據(jù)創(chuàng)建新表
mysql>?create?table?newTable?select?*?from?PEOPLE; Query?OK,?0?rows?affected?(0.01?sec) Records:?0??Duplicates:?0??Warnings:?0
我們查看一下目前數(shù)據(jù)庫存在的表：
mysql>?show?tables; +------------------+ |?Tables_in_testdb?| +------------------+ |?PEOPLE???????????| |?newTable?????????| +------------------+ 2?rows?in?set?(0.00?sec)
3、數(shù)據(jù)的操作及管理
數(shù)據(jù)表的基本操作，包含增、刪、改、查數(shù)據(jù)。
以下命令均在PEOPLE表上操作。
3.1 增加數(shù)據(jù)（增）
PEOPLE表目前是沒有數(shù)據(jù)的，它是空的數(shù)據(jù)表，我們現(xiàn)在先添加一些數(shù)據(jù)。
insert into 命令添加數(shù)據(jù)：
mysql>?insert?into?PEOPLE?VALUES?(null,?'Anny',?22,?'1992-05-22'); Query?OK,?1?row?affected?(0.00?sec)
使用select命令查看表（會(huì)在后面介紹），現(xiàn)在我們查看PEOPLE數(shù)據(jù)表的數(shù)據(jù)：
mysql>?select?*?from?PEOPLE; +----+------+-----+---------------------+ |?ID?|?NAME?|?AGE?|?BIRTHDAY????????????| +----+------+-----+---------------------+ |??1?|?Anny?|??22?|?1992-05-22?00:00:00?| +----+------+-----+---------------------+ 1?row?in?set?(0.00?sec)
數(shù)據(jù)表現(xiàn)在有一條數(shù)據(jù)。
我們多添加幾條數(shù)據(jù)，如：
mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+ |?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+ |??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Garvey?|??23?|?1991-05-22?00:00:00?| |??3?|?Lisa???|??25?|?1989-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?| |??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+ 5?rows?in?set?(0.00?sec)
3.2 刪除數(shù)據(jù)（刪）
delete 命令刪除數(shù)據(jù)：
mysql>?delete?from?PEOPLE?where?name?=?'Lisa'; Query?OK,?1?row?affected?(0.01?sec)
再次查詢PEOPLE表：
mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+ |?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+ |??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Garvey?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?| |??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+ 4?rows?in?set?(0.00?sec)
已經(jīng)看不到名為“Lisa”的數(shù)據(jù)了。
3.3 修改數(shù)據(jù)（改）
update 命令修改數(shù)據(jù)：
mysql>?update?PEOPLE?set?name='Calvin'?where?name?=?'Garvey'; Query?OK,?1?row?affected?(0.00?sec) Rows?matched:?1??Changed:?1??Warnings:?0
查詢PEOPLE表內(nèi)容：
mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+ |?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+ |??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Calvin?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?| |??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+ 4?rows?in?set?(0.00?sec)
名為“Garvey”的記錄已經(jīng)修改為“Calvin”。
3.4 查詢數(shù)據(jù)（查）
select 命令查詢數(shù)據(jù)，最簡單的就是查詢表的所有數(shù)據(jù)，也就是我們最初使用到的那條命令：
mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+ |?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+ |??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Calvin?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?| |??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+ 4?rows?in?set?(0.00?sec)
格式：select *?from ，*代表所有字段。
查詢數(shù)據(jù)時(shí)也可指定顯示的（列）字段：
mysql>?select?NAME,?AGE,?BIRTHDAY?from?PEOPLE; +--------+-----+---------------------+ |?NAME???|?AGE?|?BIRTHDAY????????????| +--------+-----+---------------------+ |?Anny???|??22?|?1992-05-22?00:00:00?| |?Calvin?|??23?|?1991-05-22?00:00:00?| |?Nick???|??24?|?1990-05-22?00:00:00?| |?Rick???|??24?|?1991-05-22?00:00:00?| +--------+-----+---------------------+ 4?rows?in?set?(0.00?sec)
格式：select from 。
select查詢命令還有很多的高級(jí)用法，比如用來查找不重復(fù)（distinct）的數(shù)據(jù)，使數(shù)據(jù)按條件排序（order by），按查詢條件顯示數(shù)據(jù)（where）等等。這些都會(huì)在下一篇文章作重點(diǎn)介紹，請大家繼續(xù)留意我的博客，謝謝。
?以上就是MySQL終端管理數(shù)據(jù)庫操作指南的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！?
.png)
