MySQL全面瓦解4：資料定義-DDL

翁智華發表於2020-10-30
MySql

 前言

SQL的語言分類主要包含如下幾種：

DDL 資料定義語言 create、drop、alter 資料定義語言 create、drop、alter 語句 。
DML 資料操縱語言 insert、delete、update 定義對資料庫記錄的增、刪、改操作。
DQL 資料庫查詢語言 select 定義對資料庫記錄的查詢操作。
DCL 資料庫控制語言 grant、remove

定義對資料庫、表、欄位、使用者的訪問許可權和安全級別。

（授權grant,收回許可權revoke等）。
TCL 事務控制語言

set autocommit=0、

start transaction、

savepoint、commit、rollback

 定義對資料庫的事務操作。

這小節主要了解下資料定義語言DDL(Data Define Language)。我們用它對資料庫、表進行一些管理操作（建立、刪除、修改等），比如：建庫、刪庫、建表、修改表、刪除表、對欄位的增刪改等，庫表結構的管理。

接下來我們逐一來說明（下文[]中的內容屬於可選項）。

資料庫管理

建立資料庫

1 create database [if not exists] dbname;

刪除資料庫

drop databases [if exists] dbname;

完整的寫法如下：

1 drop databases [if exists] o_dbname;
2 create database n_dbname;

 o_dbname 代表舊的資料庫名，n_dbname 代表新的資料庫名。

測試一下：

 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | buyerparty         |
 7 | buyerparty1        |
 8 | git_jeeshop        |
 9 | jz                 |
10 | kdmy               |
11 | kdmygf             |
12 | localsdk           |
13 | mgrcentercontrol   |
14 | mysql              |
15 | performance_schema |
16 | stroke_data        |
17 | test               |
18 +--------------------+
19 13 rows in set
20 
21 mysql> drop database if exists test1;
22 Query OK, 0 rows affected
23 
24 mysql> create database test1;
25 Query OK, 1 row affected
26 
27 mysql> create database test1;
28 1007 - Can't create database 'test1'; database exists

 

通過上面的測試可以知道：刪除之前要先判斷資料庫是否存在，否則會報出異常；同時建立之前也要判斷是否存在，如果存在則會提示已存在。

 

表管理

建立表

在資料庫中一張表的基本語法格式如下：

1 create table tbname(
2 column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'], 
3 column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],
4 column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']
5 )[table_options];
語法說明

1、column_name是指欄位名；column_type指的是欄位型別（CHAR、INT等）；n代表欄位寬度，可選；constraints 約束，可選；comment 為欄位備註，可以對欄位詳細描述。

2、同一個表裡面，column_name不能相同 

3、欄位名和型別為必選，其他均為可選引數

4、型別限制了 欄位 的儲存格式，必須以給定的資料型別來儲存，並可以額外新增的約束

約束說明
not null：非空約束
1 mysql> use test;
2 Database changed
3 
4 mysql> create table if not exists `user1`(age int comment '年齡',name char(5) comment '姓名' not null);
5 Query OK, 0 rows affected
6 
7 mysql> insert into user1 values(8,null);
8 1048 - Column 'name' cannot be null

 建表的時候，對name欄位做了非空約束，這時候傳入的值為null，就會有錯誤提示。所以非空約束的目的是保證欄位不為空。

 

default value：提供欄位預設值
 1 mysql> use test;
 2 Database changed
 3 
 4 mysql> create table if not exists `user2`(age int not null default 0 comment '年齡',name char(50) comment '姓名' not null);
 5 Query OK, 0 rows affected
 6 
 7 mysql> insert into user2(name) values('brand');
 8 Query OK, 1 row affected
 9 mysql> select * from user2; 
10 +-----+-------+
11 | age | name  |
12 +-----+-------+
13 |   0 | brand |
14 +-----+-------+
15 1 row in set

 設定了預設值之後，如果在寫入資料時，不指定值，他會自動取預設值0。

primary key：標識主鍵約束

設定該欄位為表的主鍵，全域性唯一標識錄，插入重複時報錯。

有兩種表現方式：一種是直接在欄位約束中跟上；一種是欄位都宣告完了之後，在結尾加上，與上一個欄位之間用逗號隔開。

 1 mysql> use test;
 2 Database changed
 3 
 4 mysql> create table if not exists `user3`(id int primary key,age int not null default 0 comment '年齡',name char(50) comment '姓名' not null);
 5 Query OK, 0 rows affected
 6 
 7 mysql> insert into user3 values(1,20,'brand');
 8 Query OK, 1 row affected
 9 
10 mysql> insert into user3 values(1,22,'sol');
11 1062 - Duplicate entry '1' for key 'PRIMARY'
12 
13 mysql> insert into user3 values(2,22,'sol');
14 Query OK, 1 row affected
15 
16 mysql> select * from user3;
17 +----+-----+-------+
18 | id | age | name  |
19 +----+-----+-------+
20 |  1 |  20 | brand |
21 |  2 |  22 | sol   |
22 +----+-----+-------+
23 2 rows in set

如上，主鍵必須保持值的唯一性，如果插入重複值，會提示違反主鍵約束

另外一種方式是在欄位宣告的尾部，可以支援多個主鍵，用逗號隔開並且不可重複，格式：primary key(欄位1,欄位2,欄位n)，這種叫組合主鍵（或複合主鍵），舉個例子：

1  create table if not exists `user4`(id int,age int not null default 0 comment '年齡',name char(50) comment '姓名' not null,primary key(id,name));
foreign key：標識外來鍵約束

語法：foreign key(t1_columnname) references t2(columnname),t1 為當前表，t2為外來鍵表，當前表和外來鍵表有一個欄位約束成外來鍵。

 1 mysql> create table if not exists `class`(classid int primary key,classname varchar(50));
 2 Query OK, 0 rows affected
 3 
 4 mysql> create table if not exists `user4`(id int primary key,age int comment '年齡',name char(50) comment '姓名',cid int not null,foreign key(cid) references class(classid));
 5 Query OK, 0 rows affected
 6 
 7 mysql> insert into `user4` values(1,20,'brand',1);
 8 1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`user4`, CONSTRAINT `user4_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`classid`))
 9 
10 mysql> insert into `class` values(1,'grad 3');
11 Query OK, 1 row affected
12 
13 mysql> insert into `user4` values(1,20,'brand',1);
14 Query OK, 1 row affected
15 
16 mysql> select a.age as '年齡',a.name as '學生姓名',b.classname as '班級' from user4 a left join class b on a.cid = b.classid;
17 +------+----------+--------+
18 | 年齡 | 學生姓名 | 班級   |
19 +------+----------+--------+
20 |   20 | brand    | grad 3 |
21 +------+----------+--------+
22 1 row in set

 幾點說明：

1、插入user4表的時候，會檢查關聯的外來鍵classid的值是否存在，如果不存在就會報錯誤。如上述程式碼中第三段，classid=1的值在class表中不存在。

2、建立外來鍵關係的兩張表的對應欄位，型別需要保持一致。

3、設定為外來鍵的欄位不能為本表的主鍵，而關聯表的欄位需要為主鍵。（所以外來鍵cid關聯到class表的classid欄位為主鍵）。

unique key：標識唯一值約束

可以設定一個到多個欄位，不允許重複值，重複會報違反唯一約束，導致插入失敗。

同樣的有兩種定義方式，一種是直接在欄位後設定，一種是定義完所有欄位之後再設定。以下例子：

 1 mysql> create table `user5` (id int primary key,name varchar(50),ident char(18) unique key);
 2 Query OK, 0 rows affected
 3 
 4 mysql> create table `user6` (id int primary key,name varchar(50),ident char(18) not null,sex int not null,unique key(ident,sex));
 5 Query OK, 0 rows affected
 6 
 7 mysql> insert into `user5` values(1,'brand','012345678901234567');
 8 Query OK, 1 row affected
 9 mysql> insert into `user5` values(2,'sol','012345678901234567');
10 1062 - Duplicate entry '012345678901234567' for key 'ident'

 第二段中演示了支援多欄位，用逗號隔開，語法格式：unique key(欄位1,欄位2,欄位n)；

第三段重複輸入了ident的值，他就提示重複輸入了。

auto_inc：標識自動增長
mysql> create table `user7` (id int auto_increment primary key,name varchar(50));
Query OK, 0 rows affected

mysql> insert into `user7`(name) values ('brand'),('sol'),('helen');
Query OK, 3 rows affected

Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from `user7`;
+----+-------+
| id | name  |
+----+-------+
|  1 | brand |
|  2 | sol   |
|  3 | helen |
+----+-------+
3 rows in set

 auto_increment 說明：

1、auto_increacement 的欄位為自動增長，預設值從1開始，每次+1

2、自動增長欄位的初始值、步長可以在mysql中進行設定，比如設定初始值為1萬，步長每次增長10

3、自增列當前值儲存在記憶體中，資料庫重啟後，會查詢當前表中自增列max為當前值。

4、如果表資料被清空並重啟資料庫，自增列會從初始值開始。

刪除表

1 drop table [if exists] tname;

修改表名、備註

1 alter table o_tname rename [to] n_tname;
2 alter table tname comment 'memo'; 

複製表

僅複製架構
1 create table tname like from_tname; 
 1 mysql> select * from `user7`;
 2 +----+-------+
 3 | id | name  |
 4 +----+-------+
 5 |  1 | brand |
 6 |  2 | sol   |
 7 |  3 | helen |
 8 +----+-------+
 9 3 rows in set
10 
11 mysql> create table `user8` like `user7`;
12 Query OK, 0 rows affected
13 
14 mysql> select *  from `user8`;
15 Empty set
複製架構+資料
1 create table tname [as] select column1,column2,... from from_tname [where condition]; 
mysql> select * from `user7`;
+----+-------+
| id | name  |
+----+-------+
|  1 | brand |
|  2 | sol   |
|  3 | helen |
+----+-------+
3 rows in set

mysql> create table `user9` select id,name from `user7`;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `user9`;
+----+-------+
| id | name  |
+----+-------+
|  1 | brand |
|  2 | sol   |
|  3 | helen |
+----+-------+
3 rows in set

 資料和架構都被複制過來了，這個超實用。

管理欄位

新增欄位

1 alter table tname add column column_name column_type [constraints];
 1 mysql> select * from `user9`;
 2 +----+-------+
 3 | id | name  |
 4 +----+-------+
 5 |  1 | brand |
 6 |  2 | sol   |
 7 |  3 | helen |
 8 +----+-------+
 9 3 rows in set
10 
11 mysql> alter table `user9` add column newcolumn int not null default 0;
12 Query OK, 0 rows affected
13 Records: 0  Duplicates: 0  Warnings: 0
14 
15 mysql> select * from `user9`;
16 +----+-------+-----------+
17 | id | name  | newcolumn |
18 +----+-------+-----------+
19 |  1 | brand |         0 |
20 |  2 | sol   |         0 |
21 |  3 | helen |         0 |
22 +----+-------+-----------+
23 3 rows in set 

修改欄位

1 alter table tname modify column col_name new_col_type [constraints]; -- 修改型別、約束，不能修改欄位名 
2 alter table tname change column col_name new_col_name  new_col_type [constraints]; -- 修改欄位名、型別、約束

以下分別是modify和change示例：

 1 mysql> desc `user9`;
 2 +-----------+-------------+------+-----+---------+-------+
 3 | Field     | Type        | Null | Key | Default | Extra |
 4 +-----------+-------------+------+-----+---------+-------+
 5 | id        | int(11)     | NO   |     | 0       |       |
 6 | name      | varchar(50) | YES  |     | NULL    |       |
 7 | newcolumn | int(11)     | NO   |     | 0       |       |
 8 +-----------+-------------+------+-----+---------+-------+
 9 3 rows in set
10 
11 mysql> alter table `user9` modify column name varchar(100);
12 Query OK, 3 rows affected
13 Records: 3  Duplicates: 0  Warnings: 0
14 
15 mysql> desc `user9`;
16 +-----------+--------------+------+-----+---------+-------+
17 | Field     | Type         | Null | Key | Default | Extra |
18 +-----------+--------------+------+-----+---------+-------+
19 | id        | int(11)      | NO   |     | 0       |       |
20 | name      | varchar(100) | YES  |     | NULL    |       |
21 | newcolumn | int(11)      | NO   |     | 0       |       |
22 +-----------+--------------+------+-----+---------+-------+
23 3 rows in set

 

 1 mysql> desc `user9`;
 2 +-----------+--------------+------+-----+---------+-------+
 3 | Field     | Type         | Null | Key | Default | Extra |
 4 +-----------+--------------+------+-----+---------+-------+
 5 | id        | int(11)      | NO   |     | 0       |       |
 6 | name      | varchar(100) | YES  |     | NULL    |       |
 7 | newcolumn | int(11)      | NO   |     | 0       |       |
 8 +-----------+--------------+------+-----+---------+-------+
 9 3 rows in set
10 
11 mysql> alter table `user9` change column name name1 varchar(100);
12 Query OK, 0 rows affected
13 Records: 0  Duplicates: 0  Warnings: 0
14 
15 mysql> desc `user9`;
16 +-----------+--------------+------+-----+---------+-------+
17 | Field     | Type         | Null | Key | Default | Extra |
18 +-----------+--------------+------+-----+---------+-------+
19 | id        | int(11)      | NO   |     | 0       |       |
20 | name1     | varchar(100) | YES  |     | NULL    |       |
21 | newcolumn | int(11)      | NO   |     | 0       |       |
22 +-----------+--------------+------+-----+---------+-------+
23 3 rows in set

 

刪除欄位

1 alter table tname drop column col_name; 
 1 mysql> desc `user9`;
 2 +-----------+--------------+------+-----+---------+-------+
 3 | Field     | Type         | Null | Key | Default | Extra |
 4 +-----------+--------------+------+-----+---------+-------+
 5 | id        | int(11)      | NO   |     | 0       |       |
 6 | name1     | varchar(100) | YES  |     | NULL    |       |
 7 | newcolumn | int(11)      | NO   |     | 0       |       |
 8 +-----------+--------------+------+-----+---------+-------+
 9 3 rows in set
10 
11 mysql> alter table `user9` drop column newcolumn;
12 Query OK, 0 rows affected
13 Records: 0  Duplicates: 0  Warnings: 0
14 
15 mysql> desc `user9`;
16 +-------+--------------+------+-----+---------+-------+
17 | Field | Type         | Null | Key | Default | Extra |
18 +-------+--------------+------+-----+---------+-------+
19 | id    | int(11)      | NO   |     | 0       |       |
20 | name1 | varchar(100) | YES  |     | NULL    |       |
21 +-------+--------------+------+-----+---------+-------+
22 2 rows in set

後記

為了表示嚴謹性，每一個都嘗試一遍，確實很耗時，寫到現在。

相關文章