[打卡]万物不及桐神 522

-- UNIQUE 唯一性约束

CREATE DATABASE db;

USE db;

SELECT DATABASE();

-- 复合约束
CREATE TABLE USER(

id INT,

`name` VARCHAR(15),

`password` VARCHAR(25),

CONSTRAINT uk_user_game_pwd UNIQUE(`name`,`PASSWORD`)


);

DESC USER;

SELECT * FROM information_schema.table_constraints
WHERE table_name ='USER';

INSERT INTO USER
VALUES(1,'Tom','abc');

SELECT * FROM USER;

INSERT INTO USER
VALUES(1,'Tom1','abc');

-- 删除约束

DESC USER;

ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_name ='USER';

-- PRIMARY KEY 主键约束
-- 一个表中最多只能有一个primary key 约束
-- 在 CREATE TABLE 时添加约束

CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15) ,
salary INT,
email VARCHAR(521)
);

DESC test3;

-- 一旦某个字段成为主键约束，则这个字段非空，用于唯一的标识表中的一条记录

CREATE TABLE test4(
id INT,
last_name VARCHAR(15) ,
salary INT,
email VARCHAR(52),
-- 表级约束
CONSTRAINT pr_test4_id PRIMARY KEY(id)#没有必要取名字
);

SELECT * FROM information_schema.TABLE_constraints
WHERE table_name='test4';

INSERT INTO test4
VALUES(NULL,'Tom',5000,'abc.com');

-- 复合主键约束
CREATE TABLE fuhe(
id INT,
NAME VARCHAR(10),
PASSWORD VARCHAR(25),
PRIMARY KEY (NAME,PASSWORD)
);

DESC fuhe;

INSERT INTO fuhe
VALUES (1,'Tom','abc');

SELECT*FROM fuhe;


INSERT INTO fuhe
VALUES (1,'Tom6','abc');

INSERT INTO fuhe
VALUES (1,'Tom6',null);