Mysql数据库基础第四章DDL（数据定义语言）：库表的管理、数据类型与约束条件

Mysql数据库基础系列

1.DDL语言基本概述

DDL：数据定义语言
主要包括以下几部分内容：

库的管理
表的管理
数据类型
约束
下面我们先介绍库的管理

2. 库的管理

库的管理包含：库的创建，更改，删除
使用的关键字： creater alter drop. 注意与数据操作语言区分
基本语法：

create|alter|drop database

2.1 库的创建

创建books为例

create database books if not exists books;

因为当存在已有的数据库时，我们不能在新建相同名字的数据库，否则会报错，因此在创建新的库时加上if条件表达式

2.2 库的修改

修改名字

rename database books to new_book;

修改字符集

alter database books character set utf-8

2.3库的删除

由于不能删除不存在的库，因此加上下列条件表达式

drop database books if exists books

3.表的管理

表的管理和库的管理一样，主要包括：创建，修改，删除

3.1 表的创建

create tables 表名(
			列名 列的类型 长度 约束，
			...
)

创建book表

CREATE TABLE book(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	authorID INT,
	publish_date DATETIME
);

创建author表

CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(20)
);

3.2表的修改

基本语法:

alter table 表名 change/drop/modify/add/rename column 列名 类型

1.修改列名：change

ALTER TABLE book CHANGE COLUMN publish_date pubdate DATETIME;

2.修改列的类型和约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

3.增加列

ALTER TABLE book ADD COLUMN annual DOUBLE;

4.删除列

ALTER TABLE book DROP COLUMN annual;

5.修改名

ALTER TABLE author RENAME COLUMN TO book_author

3.3 删除表

drop TABLE author if exists author

3.4 表的复制

首先将相关数据导入到author表中

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

此时author表中包含了上述信息，如果我们需要新建一个表，只需要author表的结构，不需要内容，则：
1.只复制表的结构：

create table copy like author;

2.复制表的内容和结构
此时我们不仅需要复制表的结构，还需要其内容，此时我们需要将author表的内容也复制

create table copy2 
select * from author;

3.只复制某些列

create table copy3
select id,au_name from author
where nation = '中国';

4.仅仅复制某些字段的结构
此时我们不需要内容，因此可以自己添加一个永远为错的条件

create table copy4
select id,au_name from author
where 1=0

4.数据类型

常见的数据类型主要包括：数值型、字符型、日期型。

4.1数值型

数值型包含：整数和小数

4.1.1整数型

tinyint、smallint、mediumint、int/integer、bigint
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

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

CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) unsigned 
)
insert into tab_int values(-123,-123)

t1是有符号的，t2是无符号的.因此t1会返回-123，t2会返回0

4.1.2 小数

分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)
特点：

①
M：整数部位+小数部位的个数
D：小数部位的个数
如果超过范围，则返回临界值

②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.456,123.456,123.456);

上述结果将为: f1:123.46 f2:123.46 f3:123

4.2字符型

较短的文本：
char
varchar
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob(较大的二进制)

CREATE TABLE tab_char(
	c1 ENUM('a','b','c')


);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');

上述m不在枚举的范围内，返回空值

CREATE TABLE tab_set(

	s1 SET('a','b','c','d')


);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');
INSERT INTO tab_set VALUES('a,c,d,e');

上述第三条和第四条返回相同的结果，因为e不在集合内。

4.3日期型

分类：
date保存日期
time 只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间

特点：

                字节					范围					是否受时区影响
datetime	 	8					1000——9999	                  不受
timestamp	  	4	                1970-2038	                  受


CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP

);


INSERT INTO tab_date VALUES(NOW(),NOW());
SET time_zone='+9:00';#设置时区为东九区
SELECT * FROM tab_date;

当设置时区为东九区后，t2的时间会相应的加一个小时，t1的时间不变

5.约束条件

添加约束条件主要是用于限制表中的数据，保证表中的数据的准确和可靠性

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

可以在创建表和修改表时设置约束条件
约束的添加分类：

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

基本语法：


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

5.1列级约束

只支持：默认、非空、主键、唯一
创建students表

CREATE DATABASE students;
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键

);

创建major表

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

#查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

5.2添加表级约束

语法：在各个字段的最下面
【constraint 约束名】约束类型(字段名)
同样的创建stuinfo表

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

DESC stuinfo;

SHOW INDEX FROM stuinfo;

上述两种方法都有自己的优缺点，列级约束不支持外键，我们在创建表时可以综合两种方法，通用代码如下。

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

5.3修改表时添加约束

有时我们需要对现有表进行约束的添加，此时需要在修改表时添加约束。首先创建没有约束条件的表

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);

DESC stuinfo;

#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;

#②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;