drop table if exists emp;
CREATE table emp(
id INT PRIMARY KEY auto_increment , — 员工id,自增长
ename VARCHAR(50) NOT NULL UNIQUE, — 员工姓名,非空且唯一
joindate date NOT NULL, — 入职日期,非空
salary DOUBLE(7,2) NOT NULL, — 工资,非空
bonus DOUBLE(7,2) DEFAULT 0 — 奖金,如果没有奖金默认为0
);
insert into emp(id,ename,joindate,salary,bonus)values(1,’张三’,’1990-11-11′,8800,5000);
— 演示主键约束:非空且唯一
insert into emp(id,ename,joindate,salary,bonus)values(null,’张三’,’1990-11-11′,8800,5000);
insert into emp(id,ename,joindate,salary,bonus)values(1,’张三’,’1990-11-11′,8800,5000);
insert into emp(id,ename,joindate,salary,bonus)values(2,’李四’,’1990-11-11′,8800,5000);
— 演示非空约束:
insert into emp(id,ename,joindate,salary,bonus)values(3,null,’1990-11-11′,8800,5000);
— 演示唯一约束:
insert into emp(id,ename,joindate,salary,bonus)values(4,’李四’,’1990-11-11′,8800,5000);
— 演示默认值约束:
insert into emp(id,ename,joindate,salary)values(3,’王五’,’1990-11-11′,8800);
insert into emp(id,ename,joindate,salary,bonus)values(4,’赵六’,’1990-11-11′,8800,null);
— 演示自动增长:auto_increment :当列是数字类型,并且他是唯一约束
insert into emp(ename,joindate,salary,bonus)values(‘赵六’,’1990-11-11′,8800,null);
insert into emp(id,ename,joindate,salary,bonus)values(null,’赵六3′,’1990-11-11′,8800,null);
SELECT * from emp;
还可以通过 alter table 表名 alter 列名 drop 约束 去删除约束
原文地址:http://www.cnblogs.com/cy-xt/p/16916806.html