MySQL中DDL數據定義語句有哪些

小編給大家分享一下MySQL中DDL數據定義語句有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

一、DDL數據定義語句

庫的管理

創建：create database [if not exists] 庫名;
修改：若需要修改庫名,直接修改文件夾
刪除：DROP DATABASE IF EXISTS 庫名;

表的管理

創建

create table IF NOT EXISTS 表名(
列名 列的類型[長度、約束],
列名 列的類型[長度、約束],
列名 列的類型[長度、約束],
...
)

修改
alter table 表名 add|drop|modify|change column 列名 [列類型 約束];

刪除
drop table 表名;

【庫的管理】# 創建庫CREATE DATABASE IF NOT EXISTS books;# 修改庫的字符集ALTER DATABASE books CHARACTER SET gbk;# 刪除庫DROP DATABASE IF EXISTS books;【表的管理】# 1.創建表CREATE TABLE IF NOT EXISTS book(
	id INT,
	bName VARCHAR(20),
	authorID INT,
	publishDate DATETIME);DESC book;CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10));DESC author;# 2.修改表# 修改列名ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的類型或約束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# 添加新列ALTER TABLE author ADD COLUMN annual DOUBLE; # 添加新列作為第一個字段ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 刪除列ALTER TABLE author DROP COLUMN annual;# 修改表名ALTER TABLE author RENAME TO book_author;# 3.表的刪除DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4.表的復制INSERT INTO author VALUES(1,'小櫻','日本'),(2,'悟空','中國'),(3,'綠巨人','美國'),(4,'哪吒','中國');# 僅復制表的結構CREATE TABLE author2 LIKE author;# 復制表的結構+數據CREATE TABLE author3 SELECT * FROM author;# 只復制部分數據CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中國';# 僅復制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;

二、數據類型

2.1 整型

【整型】# 1.默認為有符號,可以添加unsigned設置為無符號CREATE TABLE IF NOT EXISTS tab_int(
	t1 INT, # 有符號
	t2 INT UNSIGNED, # 無符號
	t3 INT ZEROFILL #添加zerofill后自動變更為無符號整型,位數不夠0填充.);DESC tab_int;SELECT * FROM tab_int;# 2.如果插入的數值超出了整型的范圍,會報out of range異常INSERT INTO tab_int VALUES(2147483648,1);# 3.如果不設置長度,會有默認的長度,位數不夠時0填充(前提是字段有ZEROFILL).

2.2 小數

注意：
M:整數部位+小數部位的總長度
D:小數部位
D和M都省略時：
1、如果是decimal類型,則M默認為10,D默認為0;
2、如果是floact和double,會根據插入的數值的精確度來決定精度。
3、定點型的精確度較高,如果要求插入數值的精確度較高如貨幣運算則考慮使用。

2.3 字符型

binary和varbinary類型，類似于char和varchar，不同的是它們包含二進制字符而不包含非二進制字符，即保存較短的二進制。

Bit(M)類型，字節為1~8，范圍為Bit(1)~Bit(8)。

Enum類型，即枚舉類型，要求插入的值必須屬于列表中指定的值之一，如果列成員為1~255，則需要1個字節存儲；如果列成員為255~65535，則需要2個字節存儲，最多需要65535個成員。

Set類型，和Enum類似，可以保存0~64個成員。和Enum最大的區別是：Set類型一次可以選取多個成員，而Enum只能選一個，根據成員個數不同，存儲所占的字節也不同。

【枚舉】CREATE TABLE tab_set(
	s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,內部會進行排序,如插入c,a會變成a,c

2.4 日期類型

timestamp和實際時區有關，更能反映實際的日期；datetime則只能反映出插入時的當地時區。
timestamp的屬性受Mysql版本和SQLMode的影響很大。

【日期類型】CREATE TABLE tab_date(
	t1 DATETIME, # 不受時區影響
	t2 TIMESTAMP # 受時區影響);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 設置時區SHOW VARIABLES LIKE 'time_zone'; #顯示當前時區

二、六大約束

含義：一種限制，用于限制表中的數據，為了保證表中數據的準確性和可靠性。

列級約束：六大約束語法上都支持，但外鍵約束沒有效果。

表級約束：除了非空、默認，其他都支持。

常用的做法是：其他約束都寫在列級，外鍵約束寫在表級。

外鍵的特點：

• 要求在從表設置外鍵關系。

• 從表的外鍵列的類型和主表的關聯列的類型要求一致或兼容，名稱無要求。

• 主表的關聯列必須是一個key(一般是主鍵或唯一)

• 插入數據時，先插入主表再插入從表；刪除數據時，先刪除從表再刪除主表。

【列級約束】
直接在字段名和類型后面追加約束類型。
注意：只支持默認、非空、主鍵、唯一，不支持外鍵約束CREATE DATABASE students;USE students;CREATE TABLE major(
	id INT PRIMARY KEY,# 主鍵
	majorName VARCHAR(20));CREATE TABLE stuinfo(
	id INT PRIMARY KEY,# 主鍵
	stuName VARCHAR(20) NOT NULL, #非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#檢查
	seat INT UNIQUE,# 唯一
	age INT DEFAULT 18, #默認約束
	majorId INT REFERENCES major(id) # 外鍵);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外鍵,唯一【表級約束】# 語法:[CONSTRAINT 約束名 ] 約束類型(字段名)DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),# 主鍵
	CONSTRAINT uq UNIQUE(seat), # 唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 檢查(不報錯,但無效)
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外鍵);SHOW INDEX FROM stuinfo;【修改表時添加約束】# 添加非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默認約束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主鍵ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一鍵ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外鍵ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表時刪除約束】# 刪除非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 刪除默認約束ALTER TABLE stuinfo MODIFY COLUMN age INT;# 刪除主鍵ALTER TABLE stuinfo DROP PRIMARY KEY;# 刪除唯一ALTER TABLE stuinfo DROP INDEX seat;# 刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

學習了約束，嘗試完成秈米的測試題
1、列級約束:
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列約束不支持起名字
表級約束:
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 實際上主鍵起了名字也沒效果
2、與1類似
3、
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

四、標識列

標識列又稱為自增長列，其實也可以將標識列納入約束的范圍。

含義：可以不用手動的插入值，系統提供默認的序列值。

特點：
1.標識列不一定非要和主鍵搭配，但要求是一個key。
2.一個表至多可以有一個表示列。
3.表示列的類型只能為數值型。
4.標識列可以通過SET auto_increment_increment=3;設置步長，也可以通過手動插入值來設置起始值。

【創建表時設置標識列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT, #設置自動自增
	NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL,'花花');INSERT INTO tab_identity(NAME) VALUE('Hudie');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#設置步長為3# 起始值可以通過改變第一條記錄的值來更改TRUNCATE TABLE tab_identity;【修改表時設置標識列】CREATE TABLE tab_identity(
	id INT, #設置自動自增
	NAME VARCHAR(20));# 設置主鍵和標識列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表時刪除標識列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;

五、級聯刪除與置空

級聯刪除：
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
級聯置空：
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

【演示級聯刪除、級聯置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20));INSERT INTO majorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT);INSERT INTO stuinfoSELECT 1,'join1','女',NULL,NULL,1 UNION ALLSELECT 2,'join2','女',NULL,NULL,1 UNION ALLSELECT 3,'join3','女',NULL,NULL,2 UNION ALLSELECT 4,'join4','女',NULL,NULL,2 UNION ALLSELECT 5,'join5','女',NULL,NULL,1 UNION ALLSELECT 6,'join6','女',NULL,NULL,3 UNION ALLSELECT 7,'join7','女',NULL,NULL,3 UNION ALLSELECT 8,'join8','女',NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 傳統方式添加外鍵ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 刪除major表的3號專業# 方式1:級聯刪除#   先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外鍵時添加級聯刪除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:級聯置空#   先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外鍵時添加級聯置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;

以上是“MySQL中DDL數據定義語句有哪些”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！