建表及约束关系(DDL语句)
1.什么是数据表
回顾数据库系统组成。
数据表是一个数据库里面用来存储数据的基本单元,是由表结构、表数据组成。
SQL语句不区分大小写,但是表就是一个文件名,windows不区分大小写,linux区分大小写,所以在命名时候,需要注意:
- 一定要有意义(英文或英文组合和多个单词的缩写)
- 自己定义的名称最好都是小写
- Sql语句关键字最好都大写
表的作用:存放相同规则的数据
表是有由列和数据组成的。每行数据称作一个条目,每一列称作一个字段
2. 数据表操作
- 创建:
create
- 销毁:
drop
- 清空:
truncate
- 重命名:
rename
- 修改:
alter
2.1 创建表
建表语句规范:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名称(
字段名1 列类型 [属性] [索引],
字段名2 列类型 [属性] [索引],
。。。
字段名n 列类型 [属性] [索引],
表的约束条件
) [表类型] [表字符集类型];
表名称和字段名 需要用户自定义名称;
SQL语句不区分大小写,但是表就是一个文件名,windows不区分大小写,linux区分大小写,所以在命名时候,需要注意:
- 一定要有意义(英文或英文组合和多个单词的缩写,避免使用拼音)
- 自己定义的名称最好都是小写
- Sql语句关键字最好都大写
- TEMPORARY:临时的,暂时的 临时表临时处理数据,不经常使用;在本次链接内生效,关闭连接后立马失效
- IF NOT EXISTS:如果不存在,就创建,如果已经存在不执行创建语句。
约束:用来限制和保护表的数据符合我们定义的条件
约束的表现形式:
- 列的约束条件:写在列的后面,对本列(字段)做一个限制
- 表的约束条件:建表语句的最后。
- 列级约束和表级约束作用相同,但是表级约束功能更强一些,
常用约束:
- not null / null (非空/可空):该列可以为空或该列不能为空
create table t2(id int null);
create table t3(id int,name varchar(30) not null);
- default value (默认值):该列会具有一个默认值
create table t3(id int,name varchar(30) not null default ‘briup’);
- unique (唯一):此列的值是唯一的,如果再次插入相同的值会引起报错
create table t6(name varchar(30) unique not null,gender enum(‘male’,’famale’) default ‘male’);
- 联合唯一:
create table t7(
id int,
name varchar(30),
unique(id),
unique(name)
);
create table t8(
id int,
name varchar(30),
unique(id,name)
);
- primary key(主键):非空唯一字段。
强制某个或者多个列唯一并且非空(一个表只能有一个主键!) - foreign key(外键):一个表中的某个字段,是用来和另一个表中的字段进行关联。例如学生选课表中,有学生ID和课程ID两个外键。
- foreign key:必须是表级约束
- 引擎必须是innodb
- 参照完整性 外键的值 依赖于 主表中对应的数据
- 数据类型必须一致
- 不能直接删除在外表中有对应关系的主表的数据
- 先删除外表中的数据;
- 再删除主表中的数据;
- 主表中对应的列是主键,外表中对应的列是外键,外表中该列是否为主键没不关系。
- 主表中的列也可以是非空、唯一、索引
- 外键是用于约束数据的一致性。
- 外键必须匹配主表的主键列或者唯一列。
- 外键可以为空。
create table t9( //主表 学生表
id int primary key, //学号
name varchar(30) //名字
)engine = innodb;
create table t10( //课程表
classId int primary key, //课程编号
className varchar(30),//课程名
credit double //学分
)engine=innodb;
create table t11( //成绩表
id int, //学号
classId int, //课程编号
score double, //成绩
primary key(id,classId),
foreign key(id) references t9(id),
foreign key(classId) reference t10(classId)
)engine=innodb;
注意:现在在成绩表中,单一的一个属性无法唯一标识一条记录,学号和课程号组合起来才可以唯一标识一条记录,所有学号和课程号的属性组是一个主键。
成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键(更加规范而言,只要学号是学生表中的唯一标识即可),则称成绩表中的学号是学生表的外键。
- AUTO_INCREMENT:自增字段,表示这个字段可以不显示提供,数据库会自动将其自动补齐,并且每次自增1
- 注意:
- 数据类型必须为整形;
- 默认起始值1,每次自增1;
- 从最大值开始加1;
- 必须是primary key/key(默认主键),或者非空索引;
- 注意:
key和index索引 :类似于字典的目录,用于加快查询速度
- 索引是与表相关的一种选择结构,索引可建立在一表的一列或多列上,一旦建立,由数据库自动维护和使用,对用户是完全透明的。
- 索引是逻辑地和物理地独立于数据,它们的建立或删除对表没有影响,所有应用可继续处理。
- 注意
- 索引是一种数据库对象,并不改变表的逻辑结构,而是在物理存储结构上增加一些辅助信息,以提高查询速度。
- 只有当按指定的索引列的值查找或按索引列的顺序存取表时,才可利用索引提高性能。
- 当表中记录增加或删除时,索引结构均要发生变化,因此,当有大量数据装入数据库时,应该先装入数据,后建立索引,以提高数据装入速度
- 作用
- 加快查询速度
- 确保唯一性特征。可以为表中某一列建一个唯一性索引,那么如果有人企图向表中插入这样一行记录,即它在这个有索引的列的数值与以前已有值重复,则这个操作就会失败。
- key和index是同义词,区别:key可以是列级约束,也可以表级约束;index只能是表级约束(依赖与表建立),可以独立建立;
- 创建:
create table tbname(
列的完整定义1,
列的完整定义2,
。。。
列的完整定义n,
key k_name(列的名字),
index idx_name(列名)
);
也可以单独创建
create index idx_name on tb_name(字段1,字段2...);
删除一个索引
drop index idx_name on tb_name;
视图 view
视图就是数据中表的窗口,在表上的查询所形成的一个数据集体。通过视图,我们可以看到自己需要的信息,而排除其他不关心的内容。
使用视图的好处有:
- 将表中所需要的列和行选取出来传递给用户
- 不是真正的将数据重新复制一遍,不需要占用存储空间
- 不仅可以在表的基础上建立,也可以在视图的基础上再次建立;
使用视图的主要目的:
- 保护数据安全,防止机密数据泄露
- 简化数据查询方式,建立有效查询
- 爆出数据独立性,保证程序不会随着数据的位置变化、名称变化而需要改变
创建和删除:
create view v_name as select id,name from tb_name;
drop view v_name;
总结:
- 列级约束:列的定义中,作用对象只是该列;
- 表级约束:所有定义之后,作用的对象是左右列;
- 联合约束:是表级约束中的一种,选取多个列;
- 联合唯一:全部一样才是重复的
建表三大范式
__1.第一范式(确保每列保持原子性):__保证数据库表中的所有字段值都是不可分解的原子值,比如一个字段地址,可以在拆分为省份,县市,街道,门牌号。也视需求而定。
2.第二范式(确保表中的每列都和主键相关): 比如在一个订单表中,有订单的相关信息以外,还有商品编号,商品名称,商品数量,商品单位等信息,但是显然,商品相关的信息与订单无关,一个合理的做法应当是把商品表从订单表中拆分出来。
__3.第三范式(确保每列都和主键列直接相关,而不是间接相关):__第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。
表与表之间的关系
- 一对一关系:如丈夫和妻子
- 一对多关系:如部门和员工,省和市
- 多对多关系:如学生和选修课
- 在表设计时:一对一关系,一般设计成一个唯一外键;一对多的关系,一般通过普通外键;多对多关系:一般将两个表拆成三个表,变成两个一对多处理。
2.2 销毁表
drop table tablename
:将整个表直接删除,所有表中的数据和表定义均被删除,故小心使用这个命令!
2.3 清空表
truncate table
:清空表内数据,恢复自增序列ID号
2.4 重命名表
alter table test rename as T;
:将数据表清空。
2.5 修改表 ALTER
有的时候我们需要对某个表增加字段
有的时候我们需要修改某个字段的长度
有的时候需要去掉某个字段
有的时候需要增加索引
有的时候我们需要增加或者删除主键
有的时候需要增加或者删除表的其他约束
操作实例
- 创建一张alter_test表,列id int
- 【增加列】往alter_test表中增加一列name varchar(20) 非空唯一
- 【增加索引】将name列设置为索引列,索引名字为name_index
- 【增加主键】将id列设置为主键列
- 【增加唯一性】往alter_test表中先增加一列no int,然后用alter语句更新表中的no列,加上唯一约束。
- 【新增默认值】将name列的默认值设置为’aaa’
- 【删除默认值】将name的默认值删除
- 【添加自增属性】将主键设置为自增序列
- 【改变自增值】将主键的自增值重新设置
- 【修改已经存在列属性(同时重命名列)】将no列修改成alter_test_no int(20) not null unique
- 【整体修改已经存在列属性】将alter_test_no列数据类型修改成int(21)
- 【删除列】删除alter_test_no列
- 【删除索引】删除name列的索引约束
- 【删除主键】删除id的主键约束
- 【修改表名】将表的名字alter_test改成alter_test2
- 【修改数据库引擎】将表alter_test2的数据引擎改成myisam
- 【将ID设为主键列】去重复数据(ignore),增加主键
- 【添加外键】将alter_test_fk中的fk_id列外键关联alter_test_pk表中的id列
注意:
- 使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。
- 数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。
- 类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。
总结
- truncate tb_name;只针对表清空里面所有数据,同时状态恢复(自增恢复初始值为1);
- alter修改,与update不同,alter操作对象为Table,而update操作对象是数据本身。