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

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

免費(fèi)學(xué)習(xí)推薦:mysql視頻教程

文章目錄

  • 一、DDL數(shù)據(jù)定義語句
  • 二、數(shù)據(jù)類型
  • 二、六大約束
  • 四、標(biāo)識(shí)列
  • 五、級(jí)聯(lián)刪除與置空

一、DDL數(shù)據(jù)定義語句

庫的管理

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

表的管理

創(chuàng)建

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

)

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

刪除
drop table 表名;

【庫的管理】# 創(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;

二、數(shù)據(jù)類型

2.1 整型

整數(shù)類型 別名 字節(jié) 無符號(hào)范圍 有符號(hào)范圍
Tinint 微整型 1 0~255 -128~127
Smallin 小整型 2 0~65535 -32768~32767
Mediumint 中整型 3 0~1677215 -8388608~8388607
Int或Integer 整型 4 0~4294967295 -2147483648~2147483647
Bigint 大整型 8 0~9223372036854775807*2+1 -9223372036854775808~9223372036854775807
【整型】# 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).

2.2 小數(shù)

浮點(diǎn)數(shù)類型 字節(jié) 范圍
Float(M,D) 4 -2^128 ~ +2^128
double(M,D) 8 -2^1024 ~ +2^1024
定點(diǎn)數(shù)類型
DEC(M,D) M+2 最大取值范圍與double相同,給定decimal的有效范圍由M和D決定

注意:
M:整數(shù)部位+小數(shù)部位的總長(zhǎng)度
D:小數(shù)部位
D和M都省略時(shí):
1、如果是decimal類型,則M默認(rèn)為10,D默認(rèn)為0;
2、如果是floact和double,會(huì)根據(jù)插入的數(shù)值的精確度來決定精度。
3、定點(diǎn)型的精確度較高,如果要求插入數(shù)值的精確度較高如貨幣運(yùn)算則考慮使用。

2.3 字符型

字符串類型 M是否可以省略 特點(diǎn) 空間耗費(fèi) 效率 范圍
char(M) M可以省略,默認(rèn)為1 定長(zhǎng) 比較耗費(fèi) M為0~255之間的整數(shù)
varchar(M) M不可以省略 可變長(zhǎng) 比較節(jié)省 M為0~65535之間的整數(shù)

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

Bit(M)類型,字節(jié)為1~8,范圍為Bit(1)~Bit(8)。

enum類型,即枚舉類型,要求插入的值必須屬于列表中指定的值之一,如果列成員為1~255,則需要1個(gè)字節(jié)存儲(chǔ);如果列成員為255~65535,則需要2個(gè)字節(jié)存儲(chǔ),最多需要65535個(gè)成員。

Set類型,和Enum類似,可以保存0~64個(gè)成員。和Enum最大的區(qū)別是:Set類型一次可以選取多個(gè)成員,而Enum只能選一個(gè),根據(jù)成員個(gè)數(shù)不同,存儲(chǔ)所占的字節(jié)也不同。

成員數(shù) 字節(jié)數(shù)
1~8 1
9~16 2
17~24 3
25~32 4
33~64 8
【枚舉】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

2.4 日期類型

日期和時(shí)間類型 特點(diǎn) 字節(jié) 最小值 最大值
date 只保存日期 4 1000-01-01 9999-12-31
datetime 保存日期+時(shí)間 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp(使用較多) 保存日期+時(shí)間 4 187001080001 2038年的某個(gè)時(shí)刻
time 只保存時(shí)間 3 -838:59:59 838:59:59
year 只保存年 1 1901 2155

timestamp和實(shí)際時(shí)區(qū)有關(guān),更能反映實(shí)際的日期;datetime則只能反映出插入時(shí)的當(dāng)?shù)貢r(shí)區(qū)。
timestamp的屬性受Mysql版本和SQLMode的影響很大。

【日期類型】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ū)

二、六大約束

含義:一種限制,用于限制表中的數(shù)據(jù),為了保證表中數(shù)據(jù)的準(zhǔn)確性和可靠性。

NOT NULL 非空約束,規(guī)定某個(gè)字段不能為空
UNIQUE 唯一約束,規(guī)定某個(gè)字段在整個(gè)表匯中是唯一的
PRIMARY KEY 主鍵(唯一且非空)
FOREIGN KEY 外鍵
CHECK 檢查約束(mysql中不支持)
default 默認(rèn)值,保證該字段有默認(rèn)值

列級(jí)約束:六大約束語法上都支持,但外鍵約束沒有效果。

表級(jí)約束:除了非空、默認(rèn),其他都支持。

常用的做法是:其他約束都寫在列級(jí),外鍵約束寫在表級(jí)。

主鍵和唯一對(duì)比 是否保證唯一性 是否允許為空 允許有幾個(gè) 是否允許組合(不推薦)
主鍵 × 至多一個(gè)主鍵 允許組合主鍵
唯一 √(允許有一個(gè)null) 可以有多個(gè)唯一 允許組合唯一

外鍵的特點(diǎn):

  • 要求在從表設(shè)置外鍵關(guān)系。
  • 從表的外鍵列的類型和主表的關(guān)聯(lián)列的類型要求一致或兼容,名稱無要求。
  • 主表的關(guān)聯(lián)列必須是一個(gè)key(一般是主鍵或唯一)
  • 插入數(shù)據(jù)時(shí),先插入主表再插入從表;刪除數(shù)據(jù)時(shí),先刪除從表再刪除主表。
列級(jí)約束和表級(jí)約束比較 位置 支持的約束類型 是否可以起別名
列級(jí)約束 列的后面 語法都支持,但外鍵沒有效果 不可以
表級(jí)約束 所有列的下面 默認(rèn)和非空不支持,其他支持 可以(主鍵沒有效果)
【列級(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;

學(xué)習(xí)了約束,嘗試完成秈米的測(cè)試題MySQL精講之三:DDL數(shù)據(jù)定義語句
1、列級(jí)約束:
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列約束不支持起名字
表級(jí)約束:
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 實(shí)際上主鍵起了名字也沒效果
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);

四、標(biāo)識(shí)列

標(biāo)識(shí)列又稱為自增長(zhǎng)列,其實(shí)也可以將標(biāo)識(shí)列納入約束的范圍。

含義:可以不用手動(dòng)的插入值,系統(tǒng)提供默認(rèn)的序列值。

特點(diǎn):
1.標(biāo)識(shí)列不一定非要和主鍵搭配,但要求是一個(gè)key。
2.一個(gè)表至多可以有一個(gè)表示列。
3.表示列的類型只能為數(shù)值型。
4.標(biāo)識(shí)列可以通過SET auto_increment_increment=3;設(shè)置步長(zhǎng),也可以通過手動(dòng)插入值來設(shè)置起始值。

【創(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)刪除:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON delete CASCADE;
級(jí)聯(lián)置空:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

【演示級(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;

更多相關(guān)免費(fèi)學(xué)習(xí)推薦:mysql教程(視頻)

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊6 分享