MySQL有很多的可視化管理工具，比如“mysql-workbench”和“sequel-pro-”。 現(xiàn)在我寫MySQL的終端命令操作的文章，是想強(qiáng)化一下自己對(duì)于MySQL的理解，總會(huì)比使用圖形化的理解透徹，因?yàn)槲冶緛?lái)就比較喜歡寫代碼。同時(shí)寫出來(lái)這些文章，是想要給大家當(dāng)個(gè)參考，希望也能對(duì)大家有所幫助，有所提升，這就是我為什么要寫終端操作MySQL的文章了。
注意：MySQL數(shù)據(jù)庫(kù)命令不區(qū)分大小寫。但在MAC的終端，如果你想使用tab自動(dòng)補(bǔ)全命令，那么你就必須使用大寫，這樣MAC的終端才會(huì)幫你補(bǔ)全命令，否則你按N遍tab都不會(huì)有響應(yīng)。
1、數(shù)據(jù)庫(kù)（database）管理
1.1 create 創(chuàng)建數(shù)據(jù)庫(kù)
create database firstDB;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | firstDB | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
alter 命令修改數(shù)據(jù)庫(kù)編碼：
默認(rèn)創(chuàng)建的數(shù)據(jù)庫(kù)默認(rèn)不支持中文字符，如果我們需要它支持中文字符，則將它的編碼設(shè)置為utf8格式：
mysql> ALTER DATABASE testDB CHARACTER SET UTF8; Query OK, 1 row affected (0.00 sec)
mysql> use firstDB; Database changed
1.5 查看當(dāng)前使用的數(shù)據(jù)庫(kù)
mysql> select database(); +------------+ | database() | +------------+ | firstdb | +------------+ 1 row in set (0.00 sec)
mysql> drop database firstDB; Query OK, 0 rows affected (0.00 sec)
2、數(shù)據(jù)表（table）管理
我們首先創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，提供我們往后的使用：
mysql> create database testDB; Query OK, 1 row affected (0.00 sec)
創(chuàng)建后記得用use命令進(jìn)入（使用）數(shù)據(jù)庫(kù)，不然后面的操作都會(huì)不成功的。
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)
顯示當(dāng)前數(shù)據(jù)庫(kù)所有的數(shù)據(jù)表
mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | PEOPLE | +------------------+ 1 row in set (0.00 sec)
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
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)在，你該相信我了吧？
mysql> alter table PEOPLE MODIFY star int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
我們?cè)俅问褂胐esc查看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)
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的字段了。
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ù)庫(kù)存在的表：
mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | PEOPLE | | newTable | +------------------+ 2 rows in set (0.00 sec)
3、數(shù)據(jù)的操作及管理
數(shù)據(jù)表的基本操作，包含增、刪、改、查數(shù)據(jù)。
以下命令均在PEOPLE表上操作。
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)
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ù)了。
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”。
select 命令查詢數(shù)據(jù)，最簡(jiǎn)單的就是查詢表的所有數(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í)用法，比如用來(lái)查找不重復(fù)（distinct）的數(shù)據(jù)，使數(shù)據(jù)按條件排序（order by），按查詢條件顯示數(shù)據(jù)（where）等等。這些都會(huì)在下一篇文章作重點(diǎn)介紹，請(qǐng)大家繼續(xù)留意我的博客，謝謝。
哈爾濱品用軟件有限公司致力于為哈爾濱的中小企業(yè)制作大氣、美觀的優(yōu)秀網(wǎng)站，并且能夠搭建符合百度排名規(guī)范的網(wǎng)站基底，使您的網(wǎng)站無(wú)需額外費(fèi)用，即可穩(wěn)步提升排名至首頁(yè)。歡迎體驗(yàn)最佳的哈爾濱網(wǎng)站建設(shè)。