【Mysql基础】四.DDL语言（自定义）

一.数据定义语言

一.库的管理

1.库的创建

	语法：
		**Create DateBase【if not exists】 库名**

#案例：创建库Books

CREATE DATABASE Books;

2.库的修改

更改库的字符集

ALTER DATABASE Books CHARACTER SET gbk;

3库的删除

DROP DATABASE IF EXISTS Books;

二.表的管理

1.表的创建

Create table IF NOT EXISTS 表名（
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
……
)

#案例：创建表Book

CREATE Table book(
			id INT,#编号
			Bname VARCHAR(20),#书名
			Price DOUBLE,#价格
			AuthorId INT,#作者编号
			publishDate DATETIME #出版日期
		);

#案例：创建作者表

CREATE TABLE author(
			Id INT,
			Au_name VARCHAR(20),
			Nation VARCHAR(20)
		);

2.表的修改

Alter table 表名 add/drop/modify/change column 列名 【列类型 约束】;
①修改列名

ALTER TABLE book CHANGE [COLUMN] publishDate pubDate DATETIME;

②修改列的类型或约束

ALTER TABLE Book MODIFY COLUMN pubDate TIMESTAMP;

③添加新列

ALTET TABLE author ADD COLUMN annual DOUBLE;

④删除列

ALTER TABLE author DROP COLUMN annual;

⑤修改表名

ALTER TABLE author RENAME TO book_author;

3.表的删除

DROP TABLE IF EXISTS author;

#通用的写法：

DROP DATABASE IF EXISTS 旧库名;
		CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
		CREATE TABLE 新表名();

4.表的复制

INSERT INTO author VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'冯唐','中国'),(4,'金庸','中国');

1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

2.复制表的结构+数据

CREATE TABLE copy2 SELECT * FROM author;

只复制部分数据

CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中国';

仅仅复制某些字段

CREATE TABLE copy4 SELECT id,bname FROM author WHERE 0;

跨库复制表
将myemployees库中的departments表的department_id和department_name复制到test库中的新表dept2

CREATE TABLE dept2 SELECT department_id,department_name FROM myemployees.departments;

二.常见的数据类型

	数值型：
		整型
		小数：
			定点数
			浮点数
	
	字符型：
		较短的文本：char,varchar
		较长的文本：text，blob（较长的二进制数据）
	
	日期型：

一.整型

1.分类：

Tinyint、smallint、midiumint、int/Intager、bigint

2.特点：

1.默认情况下是有符号。若要设置无符号，需要加unsigned
2.如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
3.如果不设置长度，会设置成默认的长度

#1.如何设置无符号和有符号

CREATE TABLE tab_int（t1 INT,t2 INT UNSIGNED); 

二.小数

1.分类

浮点型：float(M,D),double(M,D)
定点型：dec(M,D),decimal(M,D)

2.特点：

①M和D：
M=整数位数+小数位数
D=小数位数
②M和D都可以省略，如果是DECIMAL，则默认为（10，0）
③定点型：精确度较高。

#测试M和D

CREATE TABLE tab_float(f1 FLOAT(5,2),f2 DOUBLE(5,2),f3 DECIMAL(5,2));

三.字符型

较短的文本：char,varchar
较长的文本：text
Blob(较大的二进制):

1.特点：

Char(M)：M（可以省略，默认为1）：字符数 ，固定长度字符，耗费空间，效率高
Varchar(M):M（不可以省略）：字符数，可变长度字符，省空间，效率低

Enum类型
SET类型：在Enum的基础上，可以同时添加多个元素

四.日期型

Date,dateTime,timeStamp,time,year

dateTime和timeStamp的区别：
①范围：
timeStamp支持的范围：1970-01-01 08：00：01——2038
dateTime支持的范围：1000-1-1——9999-12-31
②timeStamp和实际时区有关，更能反映实际的日期
③timeStamp的属性受mysql版本和Sqlmode的影响很大

三.常见约束

一.分类：六大约束

NOT NULL：非空，用于保证该字段的值不能为空
DEFAULT：默认，用于保证该字段有默认值
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空（学号，员工编号等）
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空（座位号）
CHECK：检查约束【mysql中不支持】
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值【在从表添加外键约束】

1.添加约束的时机：

1.创建表时
2.修改表时

2.约束的添加分类：

列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空和默认，其他的都支持

CREATE TABLE 表名(
字段名 字段类型 列级约束,
表级约束
)

主键(Primary Key)和唯一(Unique)的对比：
主键：保证唯一性，不允许为空，一个表中至多1个，允许但不推荐组合
唯一：保证唯一性，允许为空，一个表可以有多个，允许但不推荐组合

一.创建表时添加约束

1.添加列级约束
只支持默认，非空，主键，唯一

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)#外键
		);

CREATE TABLE major(
		Id INT PRIMARY KEY,
		majorName VARCHAR(20)
	)

2.添加表级约束
/*
语法：
在各个字段的最下面
【Constraint 约束名】 约束类型（字段名）

DROP IF EXISTS stuinfo;
		CREATE TABLE stuinfo(
			Id INT,
			stuName VARCHR(20),
			Gender CHAR(1),
			Seat INT,
			Age INT,
			majorId INT,
			CONSTRAINT pk PRIMARY KEY(id),
			CONSTRAINT uq UNQIUE(seat),
			CONSTRAINT ck CHECK(gender='男' OR gender='女'),
			CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
			);

#通用写法：

CREATE TABLE IF NOT EXISTS stuinfo(
			Id INT PRIMARY KEY,
			Stuname VARCHAR(20) NOT NULL,
			Sex CHAR(1),
			Age INT DEFAULT 18,
			Seat INT UNIQUE,
			majorId INT,
			CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
		);

二.修改表时添加约束

1.添加列级约束
Alter table 表名 modify column 字段名 字段类型 约束；
2.添加表级约束
Alter table 表名 add 【constraint 约束名】 约束类型 (字段名) 【外键的引用】;

1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

4.添加唯一键
①ALTER TABLE stuinfo MODIFY seat INT UNIQUE;(列级约束）
②ALTER TABLE stuinfo ADD UNIQUE(seat);（表级约束）
5.添加外键

ALTER TABLE stuinfo ADD FOREIGN KEY(majorId) REFERENCES major(id);

三.修改表时删除约束

1.删除非空约束

ALTER TABLE struinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一键

ALTER TABLE stuinfo DROP INDEX seat;
SHOW INDEX FROM stuinfo;

5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY majorId;

四.标识列

又称为自增长列
1.含义：可以不用手动的插入值，系统提供默认的序列值

2.特点：
（1）.标识列必须和主键/唯一键/外键搭配使用
（2）.一个表至多只有一个标识列
（3）.标识列的类型只能是数值型
（4）.可以通过set auto_increment_increment =步长来进行步长的设置

一.创建表时设置标识列

CREATE TABLE tab_identity(
			Id INT PRIMARY KEY AUTO_INCREMENT,
			Name VARCHAR(20)
		);

		//设置自增长的步长
		SET AUTO_INCREMENT_INCREMENT=3;

二.修改表时添加标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三.修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;