MySQL精講之三：DDL資料定義語句

免費學習推薦：mysql影片教學

文章目錄

• 一、DDL資料定義語句
• 二、資料型別
• • 2.1 整型
  • 2.2 小數
  • 2.3 字元型
  • 2.4 日期型別
• 二、六大約束
• 四、識別列
• 五、級聯刪除與置空

---

一、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 整數

#整數型別#位元組無符號範圍有符號範圍#Tinint#微整型10~255-128~127Smallin小整型20~65535-32768~32767Mediumint#中整數30~1677215-8388608~8388607Int或Integer整數40~4294967295-2147483648~2147483647Bigint大整型 80~9223372036854775807*2 1-9223372036854775808~9223372036854775807#2#.

浮點數類型

位元組範圍##float( M,D)4-2^128 ~ 2^128double(M,D)-2^1024 ~ 2^1024##定點數型別#\ M:整數部位小數部位的總長度 D和M都省略時： 1、如果是decimal類型,則M預設為10,D預設為0; 2、如果是floact和double,會根據插入的數值的精確度來決定精度。

		8
\	#DEC(M,D)	M 2最大取值範圍與double相同，給定decimal的有效範圍由M和D決定
	注意：	D:小數部位

3、定點型的精確度較高,如果要求插入數值的精確度較高如貨幣運算則考慮使用。

2.3 字元型別

#字串型別

M是否可以省略

特點空間耗費定長比較耗費比較節省##M為0~65535之間的整數#binary和

效率	範圍			char(M)	M可以省略,預設為1
高	M為0~255之間的整數		varchar(M)	M不可以省略	可變長
低

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只能選一個，依成員個數不同，儲存所佔的位元組也不同。

成員數

字節數1~8#1~819~162#17~24325~324#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');# 插入后,内部会进行排序,如插入c,a会变成a,c

2.4 日期类型

日期和时间类型	特点	字节	最小值	最大值
date	只保存日期	4	1000-01-01	9999-12-31
datetime	保存日期+时间	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp(使用较多)	保存日期+时间	4	187001080001	2038年的某个时刻
time	只保存时间	3	-838:59:59	838:59:59
year	只保存年	1	1901	2155

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'; #显示当前时区

二、六大约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。

NOT NULL	非空约束，规定某个字段不能为空
UNIQUE	唯一约束，规定某个字段在整个表汇中是唯一的
PRIMARY KEY	主键(唯一且非空)
FOREIGN KEY	外键
CHECK	检查约束(mysql中不支持)
DEFAULT	默认值,保证该字段有默认值

列级约束：六大约束语法上都支持，但外键约束没有效果。

表级约束：除了非空、默认，其他都支持。

常用的做法是：其他约束都写在列级，外键约束写在表级。

主键和唯一对比	是否保证唯一性	是否允许为空	允许有几个	是否允许组合(不推荐)
主键	√	×	至多一个主键	允许组合主键
唯一	√	√(允许有一个null)	可以有多个唯一	允许组合唯一

外键的特点：

• 要求在从表设置外键关系。
• 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。
• 主表的关联列必须是一个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教程(视频)

以上是MySQL精講之三：DDL資料定義語句的詳細內容。更多資訊請關注PHP中文網其他相關文章！