Mysql数据库_第二天（DDL 语句）

DDL（数据定义语言）-- create drop alter，对表结构的增删改。

1、创建表

• 建表语句的语法格式：
  create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型…);
• MySQL中常见数据类型

数据类型	解释
int	整数型
bigint	长整型
float	浮点型
char	定长字符串，小于长度上限时，空格补齐，效率更高
varchar	可变长字符串，智能在长度上限内分配合适长度
date	日期类型
BLOB	二进制大对象，Binary Large OBject（存储图片、视频等流媒体信息）
CLOB	字符大对象，Character Large OBject（存储较大文本，如存储4G的字符串）

• char和varchar类型
  在实际开发中，当某个字段中的数据长度不发生改变，为定长时（如：性别、生日等）采用char。
  当一个字段的数据长度不确定（如：简介、姓名等）采用varchar。
• BLOB和CLOB类型
  BLOB和CLOB类型很少用，两者所占空间都过大，一般是放在云上，在数据库内存放相关路径。

# 创建学生表：
# 学生信息包括：学号bigint、姓名varchar、性别char、班级编号int、生日char
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);

2、约束

在创建表的时候，可以给表的字段添加相应的约束。添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有:
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）
检查约束(check)：Oracle数据库有check约束，但目前mysql不支持该约束。

2.1 非空约束 not null

定义字段数据不为空，当字段未赋值且没有非空默认值时报错。not null约束只有列级约束，没有表级约束。

	drop table if exists t_user;
	create table t_user(
		id int,
		username varchar(255) not null,
		password varchar(255)
	);

	insert into t_user(id,password) values(1,'123');
	ERROR 1364 (HY000): Field 'username' doesn't have a default value

2.2 唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL（NULL不是值，两个NULL不相等）。

• 列级约束：单列字段添加唯一约束

drop table if exists t_user;
create table t_user(
id int, 
usercode varchar(255) unique,  # 列级约束 -- 约束语句直接跟在创建字段后
username varchar(255) unique
);
insert into t_user values(1,'111','zs'); 
insert into t_user values(2,'111','ls');  # 限制的任一个字段重复则报错

• 表级约束：括号内多个字段联合起来添加1个unique约束

drop table if exists t_user;
create table t_user(
id int, 
usercode varchar(255),
username varchar(255),
unique(usercode,username)  # 表级约束 -- 约束语句在所有字段创建后，多字段联合约束，字段之间用','隔开
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
insert into t_user values(4,'zs','222');
insert into t_user values(4,'111','zs');  # 限制的两个联合字段组合重复时才报错

2.3 主键约束

表的设计三范式中，第一范式就要求任何一张表都应该有主键。主键值是这行记录在这张表当中的唯一标识（像身份证号码一样）。主键字段中的数据不能为NULL，也不能重复。一张表的主键约束只能有1个。

• 主键的分类：
  1、根据主键字段的字段数量划分：
  单一主键（推荐，常用）
  复合主键（多个字段联合起来添加一个主键约束，因为复合主键违背三范式，不建议使用。）
  2、根据主键性质划分：
  自然主键：主键值最好就是一个和业务没有任何关系的自然数。（推荐,auto_increment自动维护一个自增的数字，从1开始，以1递增。）
  业务主键：主键值和系统的业务挂钩，如：银行卡号做主键、身份证号码作主键。（不推荐）
  最好不要拿和业务挂钩的字段作为主键。因为业务一旦改变，主键值可能也需要随着发生变化，主键值变化可能会导致主键值的重复。
• 列级约束

drop table if exists t_user;
create table t_user(
id int primary key auto_increment,  # auto_increment自动维护一个自增的数字，从1开始，以1递增。
username varchar(255),
email varchar(255)
);

• 表级约束

drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)  # 表级约束
);

提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

2.4 外键约束

语法格式：foreign key(外键字段) references 主表(主表关联字段)
外键可以为NULL，且被引用的字段不一定是主键，但至少具有unique约束。
设置两表关联，避免数据的冗余。
e.g
t_class 班级表

cno(pk)	cname
101	北京大兴区经济技术开发区亦庄二中高三1班
102	北京大兴区经济技术开发区亦庄二中高三2班

t_student 学生表

sno(pk)	sname	classno(该字段添加外键约束fk)
1	zs1	101
2	zs2	101
3	zs3	102
4	zs4	102
5	zs5	102

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class表叫做父表。

• 子表与父表操作顺序要求：
  添加数据的时候，先添加父表，在添加子表。
  删除数据的时候，先删除子表，再删除父表。
  创建表的时候，先创建父表，再创建子表。
  删除表的时候，先删除子表，在删除父表。

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
cno int primary key,
cname varchar(255),
);
create table t_student(
sno int primary key,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
insert into t_student values(7,'lisi',103);  # 当子表中插入数据在不在父表中时，报错

3、表的复制

语法格式：create table 表名 as select语句;

将查询结果当做表创建出来。
e.g
create table emp1 as select * from emp; # 将查询出来的数据完全复制到新表

4、表的删除

语法格式：
drop table 表名;
drop table if exists 表名; # oracle不支持这种写法。