[打卡]约束上课笔记

#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','TOM@163.com',5000);

SELECT * FROM test1;

#last_name不能为空，为空报错
INSERT INTO test1
(id ,last_name,email, salary)
VALUES
(2,'TOM2@163.com',8000);
#内容为空与本身为空值是两个概念
INSERT into test1
(id,email)
VALUES
(4,'abc@163.com');

#更新id=1的email为null
UPDATE test1
SET email=null
WHERE id=1;

#更新id=4的email为null
UPDATE test1
SET email=null
WHERE id=4;



#在建表后alter table 添加约束

SELECT * FROM test1;

DESC test1;
#MODIFY修改

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(25),
email VARCHAR(25),
salary INT

);

DESC test2;

CREATE TABLE test3(
id int UNIQUE,# 在一个字段后约束为列级约束
last_name VARCHAR(25),
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,'Som','som@163.com',7000);

INSERT into test3
(id,last_name,email,salary)
VALUES(3,'lili','lili@163.com',9000);

#在ALTER TABLE 时添加约束
#方式一
SELECT * from test3;

UPDATE test3
SET salary=3000
WHERE id=1;

DESC test3;

ALTER TABLE test3
ADD CONSTRAINT uk_test3_sal UNIQUE(salary);


DESC test3;
#方式二

ALTER TABLE test3
MODIFY last_name VARCHAR(15) UNIQUE;

DESC test3;