[打卡]万物不及桐神 5.21

-- NOT NULL 非空约束只能用列级约束，不能表级约束

CREATE DATABASE niubi;

USE niubi;

-- 在
-- CREATE TABLE 时添加约束

CREATE TABLE niubi1(
id INT NOT NULL,
last_name VARCHAR(12)NOT NULL,
email VARCHAR(25),
salary INT

);

DESC niubi1;

INSERT INTO niubi1
(id,last_name,email,salary)
VALUES
(1,'Tom','66666',5000);

SELECT * FROM niubi1;

INSERT INTO niubi1
(id,last_name,email,salary)
VALUES
(1,NULL,'66666',5000);

INSERT INTO niubi1
(id,last_name,email,salary)
VALUES
(NULL,'Sam','S66666',6000);

UPDATE niubi1
SET email = NULL
WHERE id=1;

UPDATE niubi1
SET salary = 6000
WHERE id=1;

DESC niubi1;
-- 在alter table 时添加约束
-- 如果报错，本身有null值，再改为null时不能添加

ALTER TABLE niubi1
MODIFY email VARCHAR(25) NOT NULL;

-- ALTER TABLE 时删除约束

ALTER TABLE niubi1
MODIFY last_name VARCHAR(10) NULL;

DESC niubi1;
-- UNIQUE 唯一性约束
-- 在CREATE TABLE 时添加约束

CREATE TABLE niubi3(
id INT UNIQUE, #劣迹约束
last_name VARCHAR(25),
email VARCHAR(25),
salary INT,

-- 表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);
DESC niubi3;
SELECT*FROM niubi3;

1 Tom 666666 8000

INSERT INTO niubi3
(id,last_name,email,salary)
VALUES
(2,'Sam','666666',7000);

-- 在 ALTER TABLE时添加约束
-- 方式1
DESC niubi3；
ALTER TABLE niubi3
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
-- 方式2
ALTER TABLE niubi3
MODIFY last_name VARCHAR(20) UNIQUE;