mysql键1键2_（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 TABLEtable_name(column_name data_type[[NOT NULL] | [UNIQUE [KEY] | PRIMARY KEY]|CHECK(expr)],…)

表级别：CREATE TABLEtable_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) REFERENCEStbl_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 tablebm(

b_idint primary keyauto_increment,

b_namevarchar(30) not null)character set utf8 comment '部门信息表';--员工表

create tableyg(

y_idint primary keyauto_increment,

y_namevarchar(30) not null,

b_idint,constraint FK_yg_b_id foreign key (b_id) referencesbm(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 tablebm(

b_idint primary keyauto_increment,

b_namevarchar(30) not null)character set utf8 comment '部门信息表';--员工表

create tableyg(

y_idint primary keyauto_increment,

y_namevarchar(30) not null,

b_idint,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 keyfk_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 keyfk_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 keyfk_yg_b_id;alter table yg add foreign key (b_id) references bm(b_id) on delete cascade on update restrict;