[打卡]5/24

SELECT DATABASE();

CREATE DATABASE dbtest;

use dbtest;

# UNIQUE 唯一约束- 复合约束

CREATE TABLE USER(
id INT,
`name` VARCHAR(10),#关键字用着重号
`password` VARCHAR(25),
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)

);

DESC USER;

#查看某个表已有的约束

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'USER';
#所有表的约束
#information_schema库中的Table_constraints


#给user表插入
INSERT INTO USER
VALUES(1,'Tom','abc');

SELECT * FROM USER;

#如果将‘Tom’改成‘Tom1’是否能成功
INSERT INTO USER
VALUES(1,'Tom1','abc');
#能执行，——‘name，password’是复合约束的一个整体
#也就是说‘Tom，abc’与‘Tom1，abc’整体是不一样的，所以是认为新添加一条

#不能执行，‘name，password’一样
INSERT INTO USER
VALUES(2,'Tom','abc');

#可以执行
INSERT INTO USER
VALUES(2,'Tom2','abc');

#删除唯一性约束
#删除约束的名字
ALTER TABLE USER
DROP INDEX uk_user_name_pwd;

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

DESC USER;


# PRIMARY KEY 主键约束

#在 CREATE TABLE 创建表的时候增加约束

#一个表中最多只能有一个primary key 主键
CREATE TABLE test61(

id INT PRIMARY KEY,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25)

);

DESC test61;

CREATE TABLE test62(

id INT ,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25),
CONSTRAINT pm_test62_id PRIMARY KEY(id)

);

#主键没有必要起名字
#主键约束的特征：非空且唯一，用于唯一的标识表中的一条记录
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test61';

DESC test61;

INSERT into test61
VALUES(1,'Sam',3000,'sam@qq.com');

SELECT * FROM test61;

#主键重复不能运行
INSERT into test61
VALUES(1,'Sam1',3000,'sam1@qq.com');

INSERT into test61
VALUES(10,'Sam1',30000,'sam1@qq.com');

INSERT into test61
VALUES(NULL,'Sam1',30000,'sam1@qq.com');

#复合主键约束

CREATE TABLE test63(

id INT ,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25),
PRIMARY KEY(last_name,salary)

);

DESC test63;

INSERT into test63
VALUES(1,'Sam1',3000,'sam1@qq.com');

SELECT * FROM test63;

INSERT into test63
VALUES(1,'Sam',3000,'sam@qq.com');

#不可以为空
INSERT into test63
VALUES(1,null,null,'sam@qq.com');

#在 ALTER TABLE 时添加约束
CREATE TABLE test64(

id INT ,
last_name VARCHAR(15),
salary INT,
email VARCHAR(25)

);

DESC test64;

ALTER TABLE test64
ADD PRIMARY KEY(id);

#删除主键约束(在实际开发中不会删除主键约束，‘id’还是非空)
ALTER TABLE test64
DROP PRIMARY KEY;

DESC test64;