数据库造神计划第十一天---数据库约束（2）

drop table student;
 
create table student (
    id bigint not null unique,
    name varchar(20) not null,
    age int DEFAULT 18,
    sno varchar(10) UNIQUE
);

desc student;

insert into student(id, name, sno) values (1, '张三', '100001');

insert into student(id, name, sno) values (1, '李四', '100001');

drop table student;

# 重构学⽣表
create table student (
    id bigint PRIMARY KEY auto_increment,  # 设置⾃增主键
    name varchar(20) not null,
    age int DEFAULT 18,
    sno varchar(10) UNIQUE
);

# 主键列的值为NULL
insert into student(id, name, sno) values (NULL, '张三', '100001');

# 不指定主键
insert into student(name, sno) values ('李四', '100002');

# 主键列的值⾃动⽣成
select * from student;

desc student;

# 由于学号重复，产⽣了唯⼀冲突，导致插⼊失败，ID为3的主键值作废
insert into student(name, sno) values ('王五', '100002');

# 修改学号后成功插⼊数据
insert into student(name, sno) values ('王五', '100003');

# 查询后发现新记录ID列的值为4
select * from student;

# ⼿动指定⼀个值
insert into student(id, name, sno) values (100, '赵六', '100004');

select * from student;

# 下⼀次⾃增从主键的最⼤值开始
insert into student(name, sno) values ('钱七', '100005');

select * from student;

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

# 插⼊ID为100，学号为100100的学⽣记录时，报主键冲突
insert into student(id, name, sno) values (100, '赵六', '100100');

# 可以使⽤以上语法，如果插⼊时有冲突则更新当前列的值
insert into student(id, name, sno) values (100, '赵六', '100100') ON DUPLICATE KEY UPDATE name = '赵六', sno = '100100';
# 两⾏受影响，表⽰删除了原来的记录，⼜新写⼊了⼀条记录
# 与update student set name = '赵六',sno ='100100'whereid = 100; 等效

REPLACE [INTO] table_name
    [(column [, column] ...)]
VALUES 
    (value_list) [, (value_list)] ...

value_list: value, [, value] ...

# 写⼊或更新Id为101的记录
REPLACE into student(id, name, sno) values (101, '钱七', '100101');

# 原数据已更新
select * from student;

# 写⼊⼀条新数据
REPLACE into student(id, name, sno) values (102, '吴⼋', '100102');

drop table student;
# 重构学⽣表
create table student (
    id bigint PRIMARY KEY auto_increment,  # 定义主键
    name varchar(20) PRIMARY KEY           # 定义主键
);

drop table student;
# 重构学⽣表
create table student (
    id bigint,
    name varchar(20),
    PRIMARY KEY (id, name)  # 指定复合主键
);

# 插⼊数据
insert into student(id, name) values (1, '张三');

# 重复插⼊主键冲突，此时主键值由id和name两个列共同决定
insert into student(id, name) values (1, '张三');

# 修改id值插⼊成功
insert into student(id, name) values (2, '张三');

select * from student;