MySQL ddl語句的使用

更新時間：2020年11月12日 15:20:23 作者：翁智華

這篇文章主要介紹了MySQL ddl語句的使用，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下

前言

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

DDL	數(shù)據(jù)定義語言	create、drop、alter	數(shù)據(jù)定義語言 create、drop、alter 語句。
DML	數(shù)據(jù)操縱語言	insert、delete、update	定義對數(shù)據(jù)庫記錄的增、刪、改操作。
DQL	數(shù)據(jù)庫查詢語言	select	定義對數(shù)據(jù)庫記錄的查詢操作。
DCL	數(shù)據(jù)庫控制語言	grant、remove	定義對數(shù)據(jù)庫、表、字段、用戶的訪問權(quán)限和安全級別。（授權(quán)grant,收回權(quán)限r(nóng)evoke等）。
TCL	事務(wù)控制語言	set autocommit=0、 start transaction、 savepoint、commit、rollback	定義對數(shù)據(jù)庫的事務(wù)操作。

這小節(jié)主要了解下數(shù)據(jù)定義語言DDL(Data Define Language)。我們用它對數(shù)據(jù)庫、表進行一些管理操作（創(chuàng)建、刪除、修改等），比如：建庫、刪庫、建表、修改表、刪除表、對字段的增刪改等，庫表結(jié)構(gòu)的管理。

接下來我們逐一來說明（下文[]中的內(nèi)容屬于可選項）。

數(shù)據(jù)庫管理

創(chuàng)建數(shù)據(jù)庫

create database [if not exists] dbname;

刪除數(shù)據(jù)庫

drop databases [if exists] dbname;

完整的寫法如下：

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

o_dbname 代表舊的數(shù)據(jù)庫名，n_dbname 代表新的數(shù)據(jù)庫名。

測試一下：

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| buyerparty   |
| buyerparty1  |
| git_jeeshop  |
| jz     |
| kdmy    |
| kdmygf    |
| localsdk   |
| mgrcentercontrol |
| mysql    |
| performance_schema |
| stroke_data  |
| test    |
+--------------------+
13 rows in set

mysql> drop database if exists test1;
Query OK, 0 rows affected

mysql> create database test1;
Query OK, 1 row affected

mysql> create database test1;
1007 - Can't create database 'test1'; database exists

通過上面的測試可以知道：刪除之前要先判斷數(shù)據(jù)庫是否存在，否則會報出異常；同時創(chuàng)建之前也要判斷是否存在，如果存在則會提示已存在。

表管理

創(chuàng)建表

在數(shù)據(jù)庫中一張表的基本語法格式如下：

 create table tbname(
 column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'], 
 column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],
 column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']
 )[table_options];

語法說明

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

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

3、字段名和類型為必選，其他均為可選參數(shù)

4、類型限制了字段的存儲格式，必須以給定的數(shù)據(jù)類型來存儲，并可以額外添加的約束

約束說明

not null：非空約束

mysql> use test;
Database changed

mysql> create table if not exists `user1`(age int comment '年齡',name char(5) comment '姓名' not null);
Query OK, 0 rows affected

mysql> insert into user1 values(8,null);
1048 - Column 'name' cannot be null

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

default value：提供字段默認值

mysql> use test;
Database changed

mysql> create table if not exists `user2`(age int not null default 0 comment '年齡',name char(50) comment '姓名' not null);
Query OK, 0 rows affected

mysql> insert into user2(name) values('brand');
Query OK, 1 row affected
mysql> select * from user2;
+-----+-------+
| age | name |
+-----+-------+
| 0 | brand |
+-----+-------+
1 row in set

設(shè)置了默認值之后，如果在寫入數(shù)據(jù)時，不指定值，他會自動取默認值0。

primary key：標(biāo)識主鍵約束
設(shè)置該字段為表的主鍵，全局唯一標(biāo)識錄，插入重復(fù)時報錯。

有兩種表現(xiàn)方式：一種是直接在字段約束中跟上；一種是字段都聲明完了之后，在結(jié)尾加上，與上一個字段之間用逗號隔開。

mysql> use test;
Database changed

mysql> create table if not exists `user3`(id int primary key,age int not null default 0 comment '年齡',name char(50) comment '姓名' not null);
Query OK, 0 rows affected

mysql> insert into user3 values(1,20,'brand');
Query OK, 1 row affected

mysql> insert into user3 values(1,22,'sol');
1062 - Duplicate entry '1' for key 'PRIMARY'

mysql> insert into user3 values(2,22,'sol');
Query OK, 1 row affected

mysql> select * from user3;
+----+-----+-------+
| id | age | name |
+----+-----+-------+
| 1 | 20 | brand |
| 2 | 22 | sol |
+----+-----+-------+
2 rows in set

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

另外一種方式是在字段聲明的尾部，可以支持多個主鍵，用逗號隔開并且不可重復(fù)，格式：primary key(字段1,字段2,字段n)，這種叫組合主鍵（或復(fù)合主鍵），舉個栗子：

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：標(biāo)識外鍵約束
語法：foreign key(t1_columnname) references t2(columnname),t1 為當(dāng)前表，t2為外鍵表，當(dāng)前表和外鍵表有一個字段約束成外鍵。

mysql> create table if not exists `class`(classid int primary key,classname varchar(50));
Query OK, 0 rows affected

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));
Query OK, 0 rows affected

mysql> insert into `user4` values(1,20,'brand',1);
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`))

mysql> insert into `class` values(1,'grad 3');
Query OK, 1 row affected

mysql> insert into `user4` values(1,20,'brand',1);
Query OK, 1 row affected

mysql> select a.age as '年齡',a.name as '學(xué)生姓名',b.classname as '班級' from user4 a left join class b on a.cid = b.classid;
+------+----------+--------+
| 年齡 | 學(xué)生姓名 | 班級 |
+------+----------+--------+
| 20 | brand | grad 3 |
+------+----------+--------+
1 row in set

幾點說明：

1、插入user4表的時候，會檢查關(guān)聯(lián)的外鍵classid的值是否存在，如果不存在就會報錯誤。如上述代碼中第三段，classid=1的值在class表中不存在。

2、建立外鍵關(guān)系的兩張表的對應(yīng)字段，類型需要保持一致。

3、設(shè)置為外鍵的字段不能為本表的主鍵，而關(guān)聯(lián)表的字段需要為主鍵。（所以外鍵cid關(guān)聯(lián)到class表的classid字段為主鍵）。

unique key：標(biāo)識唯一值約束
可以設(shè)置一個到多個字段，不允許重復(fù)值，重復(fù)會報違反唯一約束，導(dǎo)致插入失敗。

同樣的有兩種定義方式，一種是直接在字段后設(shè)置，一種是定義完所有字段之后再設(shè)置。以下例子：

mysql> create table `user5` (id int primary key,name varchar(50),ident char(18) unique key);
Query OK, 0 rows affected

mysql> create table `user6` (id int primary key,name varchar(50),ident char(18) not null,sex int not null,unique key(ident,sex));
Query OK, 0 rows affected

mysql> insert into `user5` values(1,'brand','012345678901234567');
Query OK, 1 row affected
mysql> insert into `user5` values(2,'sol','012345678901234567');
1062 - Duplicate entry '012345678901234567' for key 'ident'

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

第三段重復(fù)輸入了ident的值，他就提示重復(fù)輸入了。

auto_inc：標(biāo)識自動增長

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中進行設(shè)置，比如設(shè)置初始值為1萬，步長每次增長10

3、自增列當(dāng)前值存儲在內(nèi)存中，數(shù)據(jù)庫重啟后，會查詢當(dāng)前表中自增列max為當(dāng)前值。

4、如果表數(shù)據(jù)被清空并重啟數(shù)據(jù)庫，自增列會從初始值開始。

刪除表

drop table [if exists] tname;

修改表名、備注

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

復(fù)制表

僅復(fù)制架構(gòu)

create table tname like from_tname;

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

mysql> create table `user8` like `user7`;
Query OK, 0 rows affected

mysql> select * from `user8`;
Empty set

復(fù)制架構(gòu)+數(shù)據(jù)

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

數(shù)據(jù)和架構(gòu)都被復(fù)制過來了，這個超實用。

管理字段

添加字段

alter table tname add column column_name column_type [constraints];

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

mysql> alter table `user9` add column newcolumn int not null default 0;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

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

修改字段

alter table tname modify column col_name new_col_type [constraints]; -- 修改類型、約束，不能修改字段名 
alter table tname change column col_name new_col_name new_col_type [constraints]; -- 修改字段名、類型、約束

以下分別是modify和change示例：

mysql> desc `user9`;
+-----------+-------------+------+-----+---------+-------+
| Field  | Type  | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(50) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+-------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` modify column name varchar(100);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` change column name name1 varchar(100);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name1  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

刪除字段

alter table tname drop column col_name;

mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name1  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

mysql> alter table `user9` drop column newcolumn;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc `user9`;
+-------+--------------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11)  | NO |  | 0  |  |
| name1 | varchar(100) | YES |  | NULL |  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

以上就是MySQL ddl語句的使用的詳細內(nèi)容，更多關(guān)于MySQL ddl語句的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: