一、常见约束
1、default:声明默认值,若未有值输入则会按照默认值存储---create table t1(name varchar(8) default 'other')
2、unique:声明为唯一
①单列唯一:若输入的值在该列中已存在,则会报错---create table t2(name varchar(8) unique)
②联合唯一:其中部分列可以重复,但是不可全体重复,否则报错---create table t3(id int, uid int, unique(id, uid))
3、primary key:主键----create table t4(name varchar(8) primary key)
①约束效果:not null + unique
②其他职能:主键除了有约束效果以外,还是innodb存储引擎组织数据的依据,相当于innodb创建表时的目录,能够帮助提升查询效率。
③一张表有且只能有一个主键,如果没有手动设置主键,innodb会从左往右依次检索,直到遇到一个非空且唯一的字段则将其自动升级为主键。---create table t5(name varchar(8) not null unique)
④如果表中没有设置主键,也没有其他非空且唯一的字段,innodb会采用自己内部的一个隐藏字段作为主键,该隐藏字段无法使用,所以也没法依靠其提升查询速度,隐藏字段只是因为用innodb存储引擎创建表的时候必须要有主键而临时配置的。
⑤一张表中通常应该主动设置一个主键,比如id,num,count等用于标识序号或者编号等。
⑥联合主键:即多个非空字段的联合唯一----create table t6(id int, uid int, primary key(id, uid))
4、auto_increment:自增,只能用于主键,升序增长----create table t7(id int primary key auto_increment)
①delete from t7:删除表中部分或者全部记录后,新增数据的自增值会按照老的向后接,不会复用已经删除了的。
②truncate t7:重置t7的记录,自增值重新开始计算。
二、表与表之间建立关系
1、表与表的关系类型:一对多,多对多,一对一,无关系。
2、foreign key:外键,用来实现表与表之间建立关系。
3、一对多关系:
①外键字段在多的一方,即关联表。
②要先创建一的一方,即被关联表,再创建关联表,并声明外键字段。
③要先存入被关联表的记录,再存入关联表的记录。
④级连:即同步,同步更新---on update cascade,同步删除---on delete cascade
⑤实例:create table t8(id int primary key auto_increment, name varchar(8)); create table t9(id int primary key auto_increment, name varchar(8), school_id int, foreign key(school_id) references t8(id) on update cascade on delete cascade)
4、多对多关系:
①互为关联表与被关联表。
②不区分创建于与存入记录的先后顺序。
③不能在原表里面声明设置外键,需要借助额外的表来专门存储原表记录之间的关联关系。
④实例:create table t10(id int primary key auto_increment, name varchar(8)); create table t11(id int primary key auto_increment, name varchar(8)); create table t12(id int primary key auto_increment, team_id int, member_id int, foreign key(team_id) references t11(id) on update cascade on delete cascade, foreign key(member_id) references t10(id) on update cascade on delete cascade)
5、一对一关系:
①相当于把原本一张表的字段分在了两张表上面。
②原则上外键可以在两者中的任意一方,但是一般把外键设置在查询频率更高的一方。
③实例:create table t13(id int primary key auto_increment, age int); create table t14(id int primary key auto_increment, name varchar(8), other_info_id int, foreign key(other_info_id) references t13(id) on update cascade on delete cascade)
三、修改表的一些语法
1、修改表名:alter table t1 rename t111
2、增加字段:alter table t111 add age int not null
alter table t111 add age int not null first
alter table t111 add age int not null after name
3、删除字段:alter table t111 drop age
4、修改字段:alter table t111 modify name char(12) not null
5、替换字段:alter table t111 change name new_name varchar(16) unique
四、复制表:我们用SQL语法查询得到的显示结果也是一张虚拟的表,但是这份虚拟表只有数据结构和数据值,没有主键,外键,约束等。
1、create table t_t1 select * from t111
2、可以设置搜索条件:create table t_t2 select * from t111 where new_name = 'tom',得到的只有符合条件的虚拟表,若没有符合条件的记录,则得到空的虚拟表。