MySQL 數據表操作
數據表操作
每一張數據表都相當於一個文件，在數據表中又分為表結構與表記錄。
表結構：包括存儲引擎，欄位，主外鍵類型，約束性條件，字元編碼等
表記錄：數據表中的每一行數據（不包含欄位行）
| id | name | gender | age |
|---|---|---|---|
| 1 | YunYa | male | 18 |
| 2 | Jack | male | 17 |
| 3 | Baby | female | 16 |
創建數據表
創建數據表其實大有講究，它包括表名稱，表欄位，存儲引擎，主外鍵類型，約束性條件，字元編碼等。
如果沒有InnoDB數據表沒有創建主鍵，那麼MySQL會自動創建一個以行號為準的主鍵。
#語法: []為可選
create table 表名(
欄位名1 類型[(寬度) 約束條件],
欄位名2 類型[(寬度) 約束條件],
欄位名3 類型[(寬度) 約束條件]
) [chrset="字元編碼"];
#注意:
1. 在同一張表中，欄位名是不能相同
2. 寬度和約束條件可選
3. 欄位名和類型是必須的
4. 表中最後一個欄位不要加逗號
以下示例將演示在school資料庫中創建student數據表
mysql> use school;
Database changed
mysql> create table student(
-> name varchar(32),
-> gender enum("male","female"),
-> age smallint
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
也可以不進入資料庫在外部或另外的庫中進行創建，那麼創建時就應該指定資料庫
create table 資料庫名.新建的數據表名(
欄位名1 類型[(寬度) 約束條件],
欄位名2 類型[(寬度) 約束條件]
);查看數據表
在某一資料庫中使用show tables;可查看該庫下的所有數據表。
使用show create table 表名;可查看該表的創建資訊。
使用desc 表名;可查看該表的表結構，包括欄位，類型，約束條件等資訊
mysql> select database(); # 查看當前所在資料庫
+------------+
| database() |
+------------+
| school |
+------------+
1 row in set (0.00 sec)
mysql> show tables; # 查看當前庫下所有表名
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
mysql> show create table student; # 查看student表的創建資訊
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`name` varchar(32) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`age` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table student\G; # 使用\G可將其轉換為一行顯示
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` varchar(32) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`age` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> desc student; # 查看錶結構
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
修改表名字
使用alter table 舊錶名 rename 新表名;可修改表名
以下示例將展示將studnet表名修改為students。
mysql> alter table student rename students;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; # 查看當前庫下所有表名
+------------------+
| Tables_in_school |
+------------------+
| students |
+------------------+
1 row in set (0.00 sec)
mysql>
刪除數據表
使用drop table 表名;可刪除某一數據表，也可使用,分割進行批量刪除的操作。
以下示例將演示創建出一個temp表再將其進行刪除的操作
mysql> create table temp(id smallint); # 新建temp表
Query OK, 0 rows affected (0.03 sec)
mysql> show tables; # 查看當前庫下所有表名
+------------------+
| Tables_in_school |
+------------------+
| students |
| temp |
+------------------+
2 rows in set (0.00 sec)
mysql> drop table temp; # 刪除temp表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; # 查看當前庫下所有表名
+------------------+
| Tables_in_school |
+------------------+
| students |
+------------------+
1 row in set (0.00 sec)
mysql>
複製表操作
結構複製
以下將演示只複製mysql.user表的結構，不複製記錄
mysql> create table temp like mysql.user;
由於mysql.user表結構太多，故這裡不進行展示
全部複製
又要複製表結構，又要複製表記錄，則使用以下語句（不會複製主鍵，外鍵，索引）
create table temp select * from mysql.user;
選擇複製
選擇某一欄位及其記錄進行複製，可使用以下語句
mysql> create table temp select host,user from mysql.user;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql>
表欄位操作
表欄位是屬於表結構的一部分，可以將他作為文檔的標題。
其標題下的一行均屬於當前欄位下的數據。
新增欄位
# ==== 增加多個欄位 ====
ALTER TABLE 表名
ADD 欄位名 數據類型 [完整性約束條件…],
ADD 欄位名 數據類型 [完整性約束條件…];
# ==== 增加單個欄位，排在最前面 ====
ALTER TABLE 表名
ADD 欄位名 數據類型 [完整性約束條件…] FIRST;
# ==== 增加單個欄位，排在某一欄位後面 ====
ALTER TABLE 表名
ADD 欄位名 數據類型 [完整性約束條件…] AFTER 欄位名;
以下示例將展示為students表新增一個名為id的非空欄位，該欄位放在最前面，並且在age欄位後新增class欄位。
mysql> alter table students
-> add id mediumint not null first, # 非空，排在最前面
-> add class varchar(12) not null after age; # 非空，排在age後面
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | mediumint(9) | NO | | NULL | |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| class | varchar(12) | NO | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
修改欄位
修改欄位分為修改欄位名或者修改其數據類型
# ==== MODIFY只能修改數據類型及其完整性約束條件 ====
ALTER TABLE 表名
MODIFY 欄位名 數據類型 [完整性約束條件…];
# ==== CHANGE能修改欄位名、數據類型及其完整性約束條件 ====
ALTER TABLE 表名
CHANGE 舊欄位名 新欄位名 舊數據類型 [完整性約束條件…];
ALTER TABLE 表名
CHANGE 舊欄位名 新欄位名 新數據類型 [完整性約束條件…];
以下示例將展示修改id欄位為自增主鍵，並將其名字修改為stu_id
mysql> alter table students
-> change id stu_id mediumint not null primary key auto_increment first;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| stu_id | mediumint(9) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| class | varchar(12) | NO | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
如果不修改名字只修改其原本的類型或完整性約束條件，可使用modify進行操作。
刪除欄位
使用以下命令可刪除某一欄位
ALTER TABLE 表名
DROP 欄位名;
以下示例將展示刪除class欄位
mysql> alter table students drop class;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| stu_id | mediumint(9) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
其他操作
存儲引擎
以下示例將展示如何將數據表students存儲引擎修改為memory
mysql> alter table students engine='memory';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
字元編碼
以下示例將展示如何將數據表students字元編碼修改為gbk
mysql> alter table students charset="gbk";
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
