[打卡]dong打卡

# NOT NULL (非空约束) 只能列级约束，不能表级约束

create database dbtest;

use dbtest;

# 在 create table 时添加约束

create table test1(

id int not null,

last_name varchar(10) not null,

email varchar(25),

salary int


);

desc test1;

insert into test1
(id,last_name,email,salary)
values
(1,'TOM','tom1@163.com',5000);

select * from test1;

insert into test1
(id,last_name,email,salary)
values
(null,'sam','tom2@163.com',8000);

insert into test1
(id,email)
VALUES
(4,'abc@163.com');

desc test1;

update test1
set email=null
where id=4;

# 在ALTER TABLE 时添加约束

select * from test1;

desc test1;

alter table test1
MODIFY email varchar(25) not null;

# alter table 删除约束

alter table test1
modify email varchar(20) null;

# unique 唯一性约束

# 在 create table 时添加约束

create table test2(

id int unique,#列级约束

last_name varchar(15),

email varchar(25),

salary int



)

desc test2;


create table test3(

id int unique,#列级约束

last_name varchar(15),

email varchar(25),

salary int,

#表级约束
CONSTRAINT uk_test3_email unique(email)


);

desc test3;

insert into test3
(id,last_name,email,salary)
values(1,'Tom','tom@163.com',8000);

select * from test3;

insert into test3
(id,last_name,email,salary)
values(2,'sam','tom@163.com',81000);

insert into test3
(id,last_name,email,salary)
values(2,'s2','2@163.com',81000);

# 在alter table时添加约束

select * from test3;

update test3
set salary=3000
where id=1;

desc test3;

alter table test3
add CONSISTENT uk_test3_sal unique(salary);

desc test3;

# 方式2

alter table test3
MODIFY last_name varchar(15) unique;