[打卡]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';

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

SELECT * FROM USER;

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

INSERT INTO USER
VALUES(1,'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 时增加约束

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(10,'Sam',30000,'sam@qq.com');

#复合主键约束


);
DESC test63;

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

SELECT * FROM test63;

INSERT INTO test63
VALUES(1,'Sam1',30000,'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);

#删除主键约束
ALTER TABLE test64
DROP PRIMARY KEY;