数据库——数据约束

默认值约束
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10),
addr VARCHAR(30) DEFAULT '山东淄博'
)
SELECT * FROM teacher
INSERT INTO teacher VALUES(1,'李老师','山东济南')
INSERT INTO teacher(tid,tname) VALUES(2,'张老师')
INSERT INTO teacher VALUES(3,'徐老师',NULL)
非空约束
DROP TABLE teacher
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10) NOT NULL, -- 非空约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'小红帽','山东青岛')
INSERT INTO teacher(tid,addr) VALUES(2,'山东枣庄')
INSERT INTO teacher VALUES(2,NULL,'山东枣庄')
唯一约束
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'李老师','370303xx','山东淄博')
INSERT INTO teacher(tid,tname) VALUES(2,'张老师')
INSERT INTO teacher VALUES(3,'徐老师',NULL,'山东枣庄')
主键约束
CREATE TABLE teacher(
tid INT PRIMARY KEY, -- PRIMARY KEY 主键约束
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'小红帽','1','山东济南')
INSERT INTO teacher VALUES(1,'大灰狼','2','山东淄博')
INSERT INTO teacher VALUES(NULL,'大灰狼','2','山东淄博')
自增长约束
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT 自增长约束
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher(tname,idcard) VALUES('小红帽','1');
INSERT INTO teacher(tname,idcard) VALUES('大灰狼','2');
INSERT INTO teacher(tname,idcard) VALUES('小灰狼','3');
INSERT INTO teacher(tid,tname,idcard) VALUES(10,'大灰狼','4')
零填充
CREATE TABLE teacher(
tid INT(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- ZEROFILL 零填充
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)