Mysql进阶学习（八）DDL语言+数据类型和DTL语言

DDL语言

1、简介：

DDL（Data Definition Language）：数据定义语言

主要功能：库和表的管理

库的管理

创建、修改、删除

表的管理

创建、修改、删除
创建： create
修改： alter
删除： drop

1.1、库的管理

1.1.1、库的创建

语法：

create database  [if not exists]库名;

案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;

1.1.2、库的修改

1、对于数据库的修改，一般不用语法修改，会造成一些数据的丢失，可以在本地修改，然后重启服务
2、可以修改数据库的字符集

RENAME DATABASE books TO 新库名;

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

1.1.3、库的删除

DROP DATABASE IF EXISTS books;

1.2、表的管理

1.2.1.表的创建 ★

语法：

create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)

案例1：创建表Book

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期
);

DESC book;

案例2：创建表author

CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)

DESC author;

1.2.2.表的修改

语法

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

②修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE; 

④删除列

ALTER TABLE book_author DROP COLUMN  annual;

⑤修改表名

ALTER TABLE author RENAME TO book_author;

DESC book;

1.2.3.表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;

通用的写法：

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

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

1.2.4.表的复制

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

SELECT * FROM Author;
SELECT * FROM copy2;

（1）仅仅复制表的结构

CREATE TABLE copy LIKE author;

（2）复制表的结构+数据

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

测试案例

1. 创建表dept1

NAME	NULL?	TYPE
id		INT(7)
NAME		VARCHAR(25)

USE test;

CREATE TABLE dept1(
	id INT(7),
	NAME VARCHAR(25)
);

2. 将表departments中的数据插入新表dept2中

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

3. 创建表emp5

NAME	NULL?	TYPE
id		INT(7)
First_name		VARCHAR (25)
Last_name		VARCHAR (25)
Dept_id		INT(7)

CREATE TABLE emp5(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

4. 将列Last_name的长度增加到50

ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

5. 根据表employees创建employees2

CREATE TABLE employees2 LIKE myemployees.employees;

6. 删除表emp5

DROP TABLE IF EXISTS emp5;

7. 将表employees2重命名为emp5

ALTER TABLE employees2 RENAME TO emp5;

8.在表dept和emp5中添加新列test_column，并检查所作的操作

ALTER TABLE emp5 ADD COLUMN test_column INT;

9.直接删除表emp5中的列 dept_id

DESC emp5;
ALTER TABLE emp5 DROP COLUMN test_column;

常见的数据类型

1.常见的数据类型简介

数值型：
整型：tinyint、smallint、mediumint、int/integer、bigint
小数：
定点数：decimal(M,D)
浮点数
float(M,D) 4
double(M,D) 8
字符型：
char、varchar、binary、varbinary、enum、set、text、blob
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
日期型：
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

2.整型

分类：

tinyint	smallint	mediumint	int/integer	bigint
1	2	3	4	8

特点：

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

如何设置无符号和有符号
设置无符号的字段：unsigned

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 
);

DESC tab_int;

INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;

结果显示的是临界值

警告代码：

共 1 行受到影响， 1 个警告

执行耗时   : 0.012 sec
传送时间   : 0.001 sec
总耗时      : 0.013 sec

Warning Code : 1264
Out of range value for column 't2' at row 1

3.小数

分类：

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，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

测试M和D

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

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);

原则：

所选择的类型越简单越好，能保存数值的类型越小越好

4.字符型

较短的文本：

char
varchar

其他：

binary和varbinary用于保存较短的二进制
说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二
进制字符串。
enum用于保存枚举
说明:又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。
如果列表成员为1~255，则需要1个字节存储
如果列表成员为255~65535，则需要2个字节存储
最多需要65535个成员！
set用于保存集合
说明：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区
别是：SET类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不同，存储所占的字节也不同

较长的文本：

text
blob(较大的二进制)

特点：

写法	M的意思	特点	空间的耗费	效率
char char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

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');

SELECT * FROM tab_set;

在此处的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');

5.日期型

分类：

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());

SELECT * FROM tab_date;
# 查看当前时区
SHOW VARIABLES LIKE 'time_zone';
# 更换时区
SET time_zone='+9:00';

常见的约束

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

分类：六大约束

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

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：

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

主键和唯一的大对比：

保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键 √	×	至多有1个	√，但不推荐
唯一 √	√	可以有多个	√，但不推荐

外键：

1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

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

)
CREATE DATABASE students;

1.创建表时添加约束

1.1.添加列级约束

语法：

直接在字段名和类型后面追加 约束类型即可。
只支持：
默认：NOT NULL
非空：DEFAULT
主键：PRIMARY KEY
唯一：UNIQUE

下面案例中check写上不报错，单mysql不支持

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


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

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

SHOW INDEX FROM stuinfo;

1.2.添加表级约束

语法：在各个字段的最下面

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

通用的写法：★
外键放在表级，其他的放在列级
外键起名规范：fk_当前表名_外键表名

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

2.修改表时添加约束

2.1.添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束;

2.2.添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

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

2.3.添加非空约束

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

2.4.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

2.5.添加主键

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

2.6.添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

2.7.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

3.修改表时删除约束

3.1.删除非空约束

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

3.2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

3.4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

3.5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

案例（常见约束）

1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）

ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

2. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）

ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。

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;设置步长
可以通过 手动插入值，设置起始值

错误案例：
标识列的类型只能是数值型

1.创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY  AUTO_INCREMENT ,
	NAME VARCHAR(20) ,
	seat INT 
);

TRUNCATE TABLE tab_identity;

INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

auto_increment_increment（步长）：每次的增长个数
auto_increment_offset（偏移量）：起始值

在MYSQL中可以设置步长，但是不能设置起始值

SET auto_increment_increment=3;

2.修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

3.修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT ;