MySQL基础——DDL语言

三、库和表的SQL语句（DDL的一部分）

1、简单介绍

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

2、库的管理

2.1 库的创建

#语法：
CREATE DATABASE 库名;

#可加上条件
CREATE DATABASE IF NOT EXISTS 库名;
复制代码

2.2 库的修改

#修改库名
RENAME DATABASE 库名 TO 新库名;#现在已经不支持了

#更改库的字符集
ALTER DATABASE 库名 CHARACTER SET 新字符集	#默认是utf8
复制代码

2.3 库的删除

#语法：
DROP DATABASE IF EXISTS 库名;
复制代码

3、表的管理

3.1 表的创建

#语法：
CREATE TABLE 数据库表名(
	列名1 数据类型1 其他要求【约束】，
	列名2 数据类型2 其他要求【约束】
    ... ...
);
复制代码

3.2 表的删除

DROP TABLE 数据库表名;

DROP TABLE IF EXISTS 数据库表名;
复制代码

3.3 表的修改

#添加列
ALTER TABLE 数据库表名 ADD 列名1 数据类型1 其他要求； 
#可以在其他要求的位置添加after来设置l

#修改列的数据类型、尺寸默认值
ALTER TABLE 数据库表名 MODIFY 列名1 数据类型1 其他要求；

#删除列
ALTER TABLE 数据库表名 DROP 列名1；

#重命名列
ALTER TABLE 数据库表名 CHANGE 旧列名 新列名；
复制代码

3.4 表的复制

#仅仅复制表的结构
CREATE TABLE 新表名 LIKE 旧表名;

#复制表结构 +  数据
CREATE TABLE 新表名 SELECT * FROM 旧表名;

#只复制部分数据
CREATE TABLE 新表名 SELECT 自己需要的部分;

#只复制部分表结构
CREATE TABLE 新表名 SELECT 自己需要的部分数据 WHERE 1 = 2;
#部分结构和数据都复制过来，不想要数据，那就让where始终不满足即可
复制代码

3.5 数据类型

怎么设置数据类型有无符号：默认是有符号的。设置成无符号，创建表时在表字段后面加上UNSIGNED。

（1）整型

MySQL数据类型	含义（有符号如下，无符号的数据比下面大一倍）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(+-9.22*10的18次方)

（2）浮点型

默认数值精度会按照首次插入精度来确定。

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

（3）定点型

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。默认m=10，d=0。

（4）字符串

MySQL数据类型	含义
char(n)，其中n表示字符数，n默认为1，可省略	固定长度，最多255个字符
varchar(n)，其中n表示字符数，不可以省略n	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char比varchar耗费空间，但是效率更高一点。

（5）二进制数据

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集，_BLO不用指定字符集。

（6）日期时间类型

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ’12:25:36′
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间
year	只记录年份

timestamp支持的范围比较小，也就到1970-2038年的某个时间，而datetime的取值可以从1000-9999年。

timestamp和实际的时区有关，更能反应出实际的时间，而datetime只能反映出当地时区。（建议这里可以自己测试一下）

（7）枚举类型

又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

如果列表成员为1255，则需要1个字节存储；如果列表成员为25565535，则需要2个字节存储，最多需要65535个成员！

（8）Set类型

和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：Set类型一次可以选取多个成员，而Enum只能选一个根据成员个数不同，存储所占的字节也不同。

成员数	字节数
1~8	1
9~16	2
17~24	3
25~32	4
33~64	8

CREATE TABLE table_set(
	s1 SET('a','b','c','d')	
);

#可以像下面这样插入值，插入的不只一个
INSERT INTO table_set VALUES('a');
INSERT INTO table_set VALUES('a,b');
INSERT INTO table_set VALUES('a,c,d');
复制代码

3.6 显示表结构

DESCRIBE 表名；
复制代码

4、约束

4.1 简单介绍

一种限制，用于限制数据，保证其准确可靠。

一共有6大约束：

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

约束添加的分类：

• 列级约束：上面几个都可以写，语法都支持，但外键约束没有效果；
• 表级约束：除了非空和默认，其他的都支持；

#列级约束和表级约束所在位置
CREATE TABLE 表名(
	字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
)
复制代码

4.2 添加列级约束

#使用列级方式添加约束
CREATE TABLE students(
	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 FOREIGN KEY REFERENCES major(id),#外键,当然这里是不支持的，上面说了列级约束外键没效果
)

CREATE TABLE major(
	id INT PRIMARY KEY,#主键 
    majName VARCHAR(20) NOT NULL,#非空
)
复制代码

4.3 添加表级约束

#使用表级方式添加约束
CREATE TABLE students(
	id INT,
    stuName VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorId INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#给id添加主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查约束
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCE major(id),#添加外键
)
复制代码

主键和唯一的对比：

	保证唯一性	是否允许为空	一个表可以有几个	是否允许组合
主键	√	×	至多有多个	√，但表示两个字段组合成一个主键
唯一	√	√，不过只能有一个为null，多了还是算重复	可以有多个	√

外键的特点

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容
3. 主表中的关联列必须是一个key（一般是主键或唯一键）

4.4 修改表时添加约束

#添加列级约束
ALTER TABLE 表名 MODIFY COLOMN 字段名，字段类型 新约束;

#添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键的引用];
复制代码

4.5 修改表时删除约束

#删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NULL;

#删除默认约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;

#删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

#删除唯一约束
ALTER TABLE 表名 DROP INDEX 字段名;

#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 字段名;
复制代码

5、标识列

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

一个表最多只能有一个标识列。

标识列必须和主键搭配吗，不一定，但是要求是一个key。

标识列的类型只能是数值型。

5.1 创建表时创建标识列

CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,#改为自增长
    NAME VARCHAR(20),
)
复制代码

5.2 设置步长

#可以先查询一下步长
SHOW VARIABLES LIKE '%auto_increment%';

#设置步长
SET auto_increment_increment = 3;
复制代码

5.3 修改表时创建标识列

ALTER TABLE 表名 MODIFY COLUMN 列名 INT PRIMARY KEY AUTO_INCREMENT;
复制代码