MySQL精講之三：DDL數(shù)據(jù)定義語句

【庫的管理】# 創(chuàng)建庫CREATE DATABASE IF NOT EXISTS books;# 修改庫的字符集ALTER DATABASE books charACTER SET gbk;# 刪除庫DROP DATABASE IF EXISTS books;【表的管理】# 1.創(chuàng)建表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; # 添加新列作為第一個(gè)字段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.表的復(fù)制INSERT INTO author VALUES(1,'小櫻','日本'),(2,'悟空','中國(guó)'),(3,'綠巨人','美國(guó)'),(4,'哪吒','中國(guó)');# 僅復(fù)制表的結(jié)構(gòu)CREATE TABLE author2 LIKE author;# 復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)CREATE TABLE author3 SELECT * FROM author;# 只復(fù)制部分?jǐn)?shù)據(jù)CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中國(guó)';# 僅復(fù)制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;

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

【枚舉】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');# 插入后,內(nèi)部會(huì)進(jìn)行排序,如插入c,a會(huì)變成a,c

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

【列級(jí)約束】 直接在字段名和類型后面追加約束類型。 注意：只支持默認(rèn)、非空、主鍵、唯一，不支持外鍵約束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, #默認(rèn)約束 	majorId INT REFERENCES major(id) # 外鍵);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外鍵,唯一【表級(jí)約束】# 語法:[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='女'),# 檢查(不報(bào)錯(cuò),但無效) 	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外鍵);SHOW INDEX FROM stuinfo;【修改表時(shí)添加約束】# 添加非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默認(rèn)約束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;【修改表時(shí)刪除約束】# 刪除非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 刪除默認(rèn)約束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;

【創(chuàng)建表時(shí)設(shè)置標(biāo)識(shí)列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( 	id INT PRIMARY KEY AUTO_INCREMENT, #設(shè)置自動(dòng)自增 	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;#設(shè)置步長(zhǎng)為3# 起始值可以通過改變第一條記錄的值來更改TRUNCATE TABLE tab_identity;【修改表時(shí)設(shè)置標(biāo)識(shí)列】CREATE TABLE tab_identity( 	id INT, #設(shè)置自動(dòng)自增 	NAME VARCHAR(20));# 設(shè)置主鍵和標(biāo)識(shí)列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表時(shí)刪除標(biāo)識(shí)列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;

【演示級(jí)聯(lián)刪除、級(jí)聯(lián)置空】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;# 傳統(tǒng)方式添加外鍵ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 刪除major表的3號(hào)專業(yè)# 方式1:級(jí)聯(lián)刪除#   先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外鍵時(shí)添加級(jí)聯(lián)刪除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:級(jí)聯(lián)置空#   先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;#   添加外鍵時(shí)添加級(jí)聯(lián)置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;