MySQL資料庫管理的常用操作命令錦集

shsnchyw發表於2015-11-13
MySql資料庫

1、資料庫（database）管理

1.1 create 建立資料庫

1.2 show 檢視所有資料庫

1.3 alter 修改資料庫

1.4 use 使用資料庫

1.5 檢視當前使用的資料庫

1.6 drop 刪除資料庫

2、資料表（table）管理

2.1 create 建立表

2.2 show 顯示錶

2.3 desc 檢視錶結構

2.4 alter 修改表結構（增、刪、改）

2.4.1 insert 在表中新增列（欄位）

2.4.2 alter 修改表（列）欄位

2.4.3 delete 刪除表（列）欄位

2.4.4 rename 重新命名錶名

2.5 create 利用已有資料建立新表

3、資料的操作及管理

3.1 增加資料（增）

3.2 刪除資料（刪）

3.3 修改資料（改）

3.4 查詢資料（查）

4、例項

4.1、資料庫（database）管理

4.1.1 create 建立資料庫

1 create database firstDB;
 

4.1.2 show 檢視所有資料庫

1
2
3
4
5
6
7
8
9
10 		mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| firstDB |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
 

4.1.3 alter 修改資料庫

alter 命令修改資料庫編碼：

預設建立的資料庫預設不支援中文字元，如果我們需要它支援中文字元，則將它的編碼設定為utf8格式：

1
2 		mysql> ALTER DATABASE testDB CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)
 

4.1.4 use 使用資料庫

1
2 		mysql> use firstDB;
Database changed
 

4.1.5 檢視當前使用的資料庫

1
2
3
4
5
6
7 		mysql> select database();
+------------+
| database() |
+------------+
| firstdb |
+------------+
1 row in set (0.00 sec)
 

4.1.6 drop 刪除資料庫

1
2 		mysql> drop database firstDB;
Query OK, 0 rows affected (0.00 sec)
 

4.2、資料表（table）管理

建立一個資料庫：

1
2 		mysql> create database testDB;
Query OK, 1 row affected (0.00 sec)
 

建立後記得用use命令進入（使用）資料庫，不然後面的操作都會不成功的。

4.2.1 create 建立表

1
2
3
4
5
6 		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)
 

4.2.2 show 顯示錶

顯示當前資料庫所有的資料表

1
2
3
4
5
6
7 		mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| PEOPLE |
+------------------+
1 row in set (0.00 sec)
 

4.2.3 desc 檢視錶結構

1
2
3
4
5
6
7
8
9
10
11 		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)
 

4.2.4 alter 修改表結構（增、刪、改）

預設建立的表不支援中文字元，所以需將表編碼設定為utf8：

1
2
3 		mysql> ALTER TABLE KEYCHAIN CONVERT TO CHARACTER SET UTF8;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
 

(1)insert 在表中新增列（欄位）

1
2
3 		mysql> alter table PEOPLE add star BOOL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
 

提示：在MySQL裡，布林型別會自動轉換為tinyint(1)型別。

我們不妨使用desc去檢視一下PEOPLE表結構：

1
2
3
4
5
6
7
8
9
10
11 		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)
 

(2)alter 修改表（列）欄位

1
2
3 		mysql> alter table PEOPLE MODIFY star int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 

也可以指定 int(n) 的長度，比如 int(2)。

我們再次使用desc檢視PEOPLE表結構：

1
2
3
4
5
6
7
8
9
10
11 		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)
 

(3)delete 刪除表（列）欄位

1
2
3 		mysql> alter table PEOPLE DROP column star;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
 

刪除後，再次檢視PEOPLE表結構：

1
2
3
4
5
6
7
8
9
10 		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)
 

刪除欄位成功，現在我們已經不能看到star的欄位了。

(4)rename 重新命名錶名

1
2 		mysql> RENAME TABLE PEOPLE TO NEW_PEOPLE;
Query OK, 0 rows affected (0.00 sec)
 

(5) null or not null

修改表欄位允許為空或不允許為空：

1
2
3 		mysql> ALTER TABLE PEOPLE MODIFY AGE INT(3) NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 

把 PEOPLE 表的 AGE 欄位設定成“允許為空”，即插入記錄時這個欄位可以不錄入。否則相反。

它的格式為：ALTER TABLE MODIFY

4.2.5 create 利用已有資料建立新表

1
2
3 		mysql> create table newTable select * from PEOPLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 

我們檢視一下目前資料庫存在的表：

1
2
3
4
5
6
7
8 		mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| PEOPLE |
| newTable |
+------------------+
2 rows in set (0.00 sec)
 

4.3、資料的操作及管理

資料表的基本操作，包含增、刪、改、查資料。

以下命令均在PEOPLE表上操作。

4.3.1 增加資料（增）

PEOPLE表目前是沒有資料的，它是空的資料表，我們現在先新增一些資料。

insert into 命令新增資料：

1
2 		mysql> insert into PEOPLE VALUES (null, 'Anny', 22, '1992-05-22');
Query OK, 1 row affected (0.00 sec)
 

使用select命令檢視錶（會在後面介紹），現在我們檢視PEOPLE資料表的資料：

1
2
3
4
5
6
7 		mysql> select * from PEOPLE;
+----+------+-----+---------------------+
| ID | NAME | AGE | BIRTHDAY |
+----+------+-----+---------------------+
| 1 | Anny | 22 | 1992-05-22 00:00:00 |
+----+------+-----+---------------------+
1 row in set (0.00 sec)
 

資料表多新增幾條資料：

1
2
3
4
5
6
7
8
9
10
11 		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)
 

4.3.2 刪除資料（刪）

delete 命令刪除資料：

1
2 		mysql> delete from PEOPLE where name = 'Lisa';
Query OK, 1 row affected (0.01 sec)
 
再次查詢PEOPLE表：
1
2
3
4
5
6
7
8
9
10 		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)
 

已經看不到名為“Lisa”的資料了。

4.3.3 修改資料（改）

update 命令修改資料：

1
2
3 		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表內容：

1
2
3
4
5
6
7
8
9
10 		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”的記錄已經修改為“Calvin”。

4.3.4 查詢資料（查）

select 命令查詢資料，最簡單的就是查詢表的所有資料，也就是我們最初使用到的那條命令：

1
2
3
4
5
6
7
8
9
10 		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 <表名>，*代表所有欄位。

查詢資料時也可指定顯示的（列）欄位：

1
2
3
4
5
6
7
8
9
10 		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查詢命令還有很多的高階用法，比如用來查詢不重複（distinct）的資料，使資料按條件排序（order by），按查詢條件顯示資料（where）等。

5、管理檢視

建立檢視

檢視是從資料庫裡匯出一個或多個表的虛擬表，是用來方便使用者對資料的操作。

1
2
3 		mysql> CREATE VIEW PEOPLE_VIEW (
 -> NAME, AGE)
 -> AS SELECT NAME, AGE FROM PEOPLE;
 

建立成功後檢視檢視。

1
2
3
4
5
6
7
8
9
10
11
12 		PEOPLE PEOPLE.AGE PEOPLE.BIRTHDAY PEOPLE.ID PEOPLE.NAME
mysql> SELECT * FROM PEOPLE_VIEW
 -> ;
+--------+-----+
| NAME | AGE |
+--------+-----+
| Anny | 22 |
| Calvin | 23 |
| Nick | 24 |
| Rick | 24 |
+--------+-----+
4 rows in set (0.00 sec)
 

我們也可以使用 DESC 命令檢視檢視的結構。

1
2
3
4
5
6
7 		mysql> DESC PEOPLE_VIEW;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
 

替換檢視

建立或替換原有檢視。

1
 
2 		mysql> CREATE OR REPLACE VIEW PEOPLE_VIEW(PEOPLE_ID,PEOPLE_NAME,
PEOPLE_AGE) AS SELECT ID,NAME,AGE FROM PEOPLE;
Query OK, 0 rows affected (0.00 sec)
 

建立或替換後檢視檢視。

1
2
3
4
5
6
7
8
9
10 		mysql> SELECT * FROM PEOPLE_VIEW;
+-----------+-------------+------------+
| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE |
+-----------+-------------+------------+
| 1 | Anny | 22 |
| 2 | Calvin | 23 |
| 4 | Nick | 24 |
| 5 | Rick | 24 |
+-----------+-------------+------------+
4 rows in set (0.00 sec)
 

操作檢視

當檢視資料有變化時（增、刪、改），真實的表資料也會隨著改變。也就是說，對檢視的操作就是對錶的資料，所以我們可以把檢視當作表。

例：往檢視插入一條資料。

1
2 		mysql> INSERT INTO PEOPLE_VIEW VALUES(NULL, 'Kerry', '33');
Query OK, 1 row affected (0.00 sec)
 

插入資料成功後檢視檢視。

1
2
3
4
5
6
7
8
9
10
11 		mysql> SELECT * FROM PEOPLE_VIEW ;
+-----------+-------------+------------+
| PEOPLE_ID | PEOPLE_NAME | PEOPLE_AGE |
+-----------+-------------+------------+
| 1 | Anny | 22 |
| 2 | Calvin | 23 |
| 4 | Nick | 24 |
| 5 | Rick | 24 |
| 6 | Kerry | 33 |
+-----------+-------------+------------+
5 rows in set (0.00 sec)
 

可以在檢視上看到我們剛剛插入的資料，現在我們就來驗證一下真實的表是否也會作出變化。

1
2
3
4
5
6
7
8
9
10
11 		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 |
| 6 | Kerry | 33 | NULL |
+----+--------+-----+---------------------+
5 rows in set (0.00 sec)
 

可見，真實的表資料也已經有所改變，剛剛往檢視裡插入的那一條資料存在於真實表中，真理便是：對檢視的操作就是對錶的資料。

刪除檢視

1 mysql> DROP VIEW PEOPLE_VIEW;
2 Query OK 0 rows affected (0.00 sec)
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29960155/viewspace-1837115/，如需轉載，請註明出處，否則將追究法律責任。

相關文章