[打卡]5.21...

#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;

INSERT INTO test1
(id,last_name,email,salary)
VALUES
(1,'Sam','tom@163.com',8000);
SELECT * FROM test1;

INSERT INTO test1
(id,email)
VALUES
(4,'abc@163.com');

DESC  test1;

UPDATE test1
SET email= NULL
where id=1;

#在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 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',7000);
#不能成功

#在ALTER TABLE 时添加约束
#方式1
SELECT * FROM test3;

SELECT test3
SET salary=3000
WHERE id=1;

DESC test3;

ALTER TABLE test3
ADD CONSTRAINT uk_test3_sal UNION(salary);

DESC test3;

#方式2

ALTER TABLE test3
MODIFY last_name VARCHAR(15) UNIQUE;
DESC test3;