（2.10）Mysql之SQL基础——约束及主键重复处理
（2.10）Mysql之SQL基础——约束及主键重复处理
关键词：mysql约束，批量插入数据主键冲突
【1】查看索引： show index from table_name;
【2】查看有约束的列：select * from information_schema.key_column_usage where table_schema= 'db_name' and table_name = 'table_name';
【3】查看有约束的表及表约束类型：select * from information_schema.table_constraints where table_schema= 'db_name' and table_name = 'table_name';
【4】查看外键约束：select * from information_schema.referential_constraints where constraint_schema='test1' and table_name='yg';
1.概念
mysql中约束有五种：
【1】非空约束（not null）：列值不能为空
【2】唯一约束（unique）：列值都是唯一的，可以有Null（可以有多个Null）
【3】主键约束（primariy）：用于唯一的表示表行的数据，列值不能重复，不能为空。（每个表只能有一个主键）
【4】外键约束（foreign key）：表与表的依赖关系，外键列数据必须要在主表的主键列中存在，或者为Null。
【5】检查约束、条件约束（check）：强制列值数据必须满足定义的条件。如设定值为10-20，不在范围内的报错。
【6】默认约束（default value）：默认值为多少，可以用default 来获取。如insert into test101 values(default,1);
修改默认约束：alter table users_info alter column role_id set default 1;
注意：
【1】NOT NULL约束只能在列级定义
【2】作用在多个列上的约束只能定义在表级别，例如复合索引。
【3】列级别上不能定义外键约束，并且不能给约束起名字，由mysql自动命名（NOT NULL除外）
【4】表级别上定义的约束可以给约束起名字（check约束除外，会自动命名）
2.一般形式使用（建表时与非建表时）
3、主键的使用
（3.1）有主键的表，在批量插入数据时有主键冲突怎么处理？
【1】ignore：忽略 【2】replace：替换 【3】 on duplicate key update：有重复插入时更新原表重复Key所在行的值
（3.2）有重复值，没有主键，要加主键怎么处理？
【1】重复数据时没有用的，可以清除掉，再加主键。
【2】找出重复的数据，修改重复的数据行，再加主键；
【3】直接新增一个id列，加auto_increment primary key 。（相当于构建一个row_number，但是该方法会修改结构）
或者用row_number选出重复的插入新表
4.外键的基本形式
基本形式（建表时创建请看2）
foreign key(本表的外键字段) references 主表名（关联字段）【主记录删除时 on delete/ 更新时 on update】
如果指定了 on delete/ on update 还有几个选择
cascade：级联操作，主表更新从表也更新（主键值），主表删除从表也删除
set null：设置Null，主表更新从表设置为null（主键值），主表删除从表也设置null（列属性不是not null）
restrict：拒绝附表删除和更新。
no action：标准的SQL关键字，痛restrict
2.一般形式使用（select * from information_schema.table_constraints where table_name = 'test1012'）
约束定义的语法： 列级别： CREATE TABLE table_name(column_name data_type [ [NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY] |CHECK(expr)],…) 表级别： CREATE TABLE table_name( column_name data_type [NOT NULL], column_name data_type [not null],…, [CONSTRAINT constraint_name] PRIMARY KEY (col_name,...) |[CONSTRAINT constraint_name] unique (col_name,...) |[CONSTRAINT constraint_name] foreign KEY (col_name) REFERENCES tbl_name (index_col_name) |check(expr)
演示：
【1】建表时定义约束(列级)
create table test1014(
id int primary key auto_increment, --主键约束
num1 int unique, --唯一约束
num2 int default 10, --默认约束
num3 int not null, --非空约束
num4 int check(num4>0) --check约束
)
【2】建表时定义约束(表级)
create table emp( id int, name varchar(20), deptid int, constraint emp_id_pk primary key(id), --主键 constraint emp_deptid_fk foreign key(deptid) references dept(deptid)--外键
);
【3】使用alter table 或者 drop index 增加、删除约束
1）删除索引/删除约束
<1>DROP INDEX index_name ON tbl_name
<2>ALTER TABLE tbl_name DROP INDEX index_name
2）添加索引，添加约束
<1>alter table test add key_type(column); --alter table test add primary key(test_no);
<2>create unique index UK_student_name on student (name);
<3>alter table student add constraint uk_student_name unique (name);
<4>外键：ALTER TABLE tblActivationRecords ADD CONSTRAINT fk_Activation_License FOREIGN KEY (LicenseID) REFERENCES tblLicenses(ID)
3、主键的使用
基本形式（建表时创建请看2）
-- 其他形式
1）增加/删除主键 <1>增加：alter table table_name add primariy key(column) or <3>alter table student add constraint uk_student_name unique (name); <2>删除：alter table table_name drop primary key;
（3.1）有主键的表，在批量插入数据时有主键冲突怎么处理？
【1】-- ignore（忽略）：自动忽略重复的记录行，不影响后面的记录插入；
insert ignore into test101(id , num1) values(1,2),(2,3) ,(1,3) -- 这样插入也是从左到右顺序插入，所以 (1,3)后插入，又由于id为主键，所以和(1,2)行所在主键冲突，被忽略；
【2】-- replace（替换）：后插入的相同重复主键会替换之前已存在的数据；
replace into test101(id , num1) values(1,2),(2,3) ,(1,3)
【3】-- on duplicate key update：先执行insert 操作再根据主键执行对表中已有值进行update操作
insert into test101 values(1,11),(2,22) ;
insert into test101 values(1,100),(2,100) on duplicate key update num1=num1+100;
结果如下：
（3.2）有重复值，没有主键，要加主键怎么处理？
【1】重复数据时没有用的，可以清除掉，再加主键。
解决办法：（1）查出没有重复的记录， select id,max(num1) from test102 group by id having count(id)>=1;
（2）再创建一个新表把不重复的记录插入进去
（3）再将原表与新表名称互换
（4）加主键
【2】找出重复的数据，修改重复的数据行，再加主键；
直接根据值修改，或者用变量构造row_number修改，甚至构造row_number() over(partition by) 来修改（参考：mysql实现开窗函数、Mysql实现分析函数）
【3】直接新增一个id列，加auto_increment primary key 。（相当于构建一个row_number，但是该方法会修改结构）
也可以直接把所有数据插入到一个新的有自增列主键的表中去，然后再进行相关改名即可。
4.外键的基本形式
基本形式（建表时创建请看2）
-- 基本演示 -- 部门表 create table bm( b_id int primary key auto_increment, b_name varchar(30) not null ) character set utf8 comment '部门信息表'; -- 员工表 create table yg( y_id int primary key auto_increment, y_name varchar(30) not null, b_id int, constraint FK_yg_b_id foreign key (b_id) references bm(b_id) ) character set utf8 comment '员工信息表';
ALTER TABLE yg ADD CONSTRAINT fk_yg_b_id FOREIGN KEY (b_id) REFERENCES bm(b_id);
--依赖关系说明
【1】必须部门表中有数据，员工表中的b_id字段才能插入数据。
且员工表中的b_id值必须是部门表中的b_id（如果不涉及该列的插入则不影响）
foreign key(本表的外键字段) references 主表名（关联字段）【主记录删除时 on delete/ 更新时 on update】
如果指定了 on delete/ on update 还有几个选择
cascade：级联操作，主表更新从表也更新（主键值），主表删除从表也删除
set null：设置Null，主表更新从表设置为null（主键值），主表删除从表也设置null（列属性不是not null）
restrict：拒绝附表删除和更新。
no action：标准的SQL关键字，痛restrict
演示：
-- 部门表 create table bm( b_id int primary key auto_increment, b_name varchar(30) not null ) character set utf8 comment '部门信息表'; -- 员工表 create table yg( y_id int primary key auto_increment, y_name varchar(30) not null, b_id int, constraint FK_yg_b_id foreign key (b_id) references bm(b_id) on delete cascade ) character set utf8 comment '员工信息表'; 父表：bm（部门表） 子表：yg（员工表） --级联 cascade 【1】级联删除：部门表删除了b_id值，对应员工表的b_id值所在行也会被删除。 【2】级联更新：部门表修改了b_id的值，那么员工表对应的b_id值也会被更新。 --置空 set null alter table yg drop foreign key fk_yg_b_id; alter table yg add foreign key (b_id) references bm(b_id) on delete set null; 【1】置空删除：部门表删除了b_id值，对应员工表的b_id值会被赋值null。 【2】置空更新：部门表更新了b_id值，对应员工表的b_id值会被赋值null。 --拒绝父表删除和更新 restrict（只要子表有记录是不让删除父表的记录值得） alter table yg drop foreign key fk_yg_b_id; alter table yg add foreign key (b_id) references bm(b_id) on delete restrict; 【1】拒绝父表删除：在子表（员工表）中存在的b_id值时，父表中b_id不允许删除 【1】拒绝父表更新：在子表（员工表）中存在的b_id值时，父表中b_id不允许更新 -- 推荐级联删除，拒绝父表更新 alter table yg drop foreign key fk_yg_b_id; alter table yg add foreign key (b_id) references bm(b_id) on delete cascade on update restrict;
