MYSQL常用命令之入门进阶—约束
一、主键约束
主键约束最显著的特征是主键列中的值是不允许重复(唯一)的,通过主键约束可强制表的实体完整性。当创建或更改表时可通过定义 primary key约束来创建主键。一个表只能有一个primary key约束,且primary key约束中的列不能接受NULL值。
alter table tab_name add constraint pk_name primary key (deptno);
创建表时有以下几种方法均可实现主键约束:
--创建表user
create table `user`(
`id` int primary key, --设置主键
`name` varchar(20)
);
create table `user`(
`id` int not null,
`name` varchar(20),
primary key(`id`) --设置主键
);
create table `user`(
`id` int not null,
`name` varchar(20),
constraint pk primary key(`id`) --设置主键
);
查看约束
show create table table_name;
二、自增长列(标识列)
并不是所有表在设计完成后都能找到适合作为主键的列,为此数据库提供了自增长列,自增长列是数值类型( INT , FLOAT , DOUBLE 等)的,其值是由数据库自动维护的,是永远都不会重复的,因此自增长是最适合作为主键列的。在创建表时,通过 auto_increment 关键字来标识自增长列,在MySQL数据库中自增长列可以是主键列,也可以是唯一列(有唯一约束的列)。
特点:
- 标识列必须和一个Key搭配(Key指主键、唯一、外键…);
- 一个表最多有一个标识列;
- 标识列的类型只能是数值型;
- 标识可以通过
SET auto_increment_increment = 3;
,SET @@auto_increment_increment = 3;
设置步长(全局),可以通过插入行时手动插入标识列值设置起始值。
create table emp(
id int primary key auto_increment,
name varchar(20)
);
我们看到即使我们在插入数据时没有插入id
,最后表中数据id
列也是从1
开始自增长,这是因为我们建表时设置了id
为主键且设置了自增长!
设置自增长列的初始值:
create table emp(
id int primary key auto_increment,
name varchar(20)
)auto_increment=10;
设置自增长列的初始值:
ALTER TABLE emp AUTO_INCREMENT = 10;
将表中某列设为自增长列:
ALTER TABLE 表名称 MODIFY column id int auto_increment;
删除自增长列:
ALTER TABLE 表名称 MODIFY COLUMN 列名 列属性;
如果设置步长会从 1 + 步长 依次递增。
如果表中已有数据,会按照 1 + 步长进行计算,如果这个 值 已有则再加直到没有。
三、唯一约束
对于非主键列中的值也要求唯一性时,就需要唯一约束
CREATE TABLE emp(
id int NOT NULL,
name varchar(20) UNIQUE #唯一约束
);
CREATE TABLE emp(
id int NOT NULL,
name varchar(20),
constraint uq_name unique(name) #唯一约束
);
向表中添加唯一约束:
alter table 表名称 add unique(列名称);
向 dept 表中的 deptname 列添加名为 uq_name 的唯一约束。
ALTER TABLE dept ADD CONSTRAINT uq_name UNIQUE(deptname);
删除表中唯一的约束
ALTER TABLE tb_name DROP CONSTRAINT constraint_name;
- 唯一约束要求值不能重复
- 可以存在多个空值( NULL )的数据
- 一张可以有多个唯一约束列
- 约束默认的名称为其列名
- 唯一约束创建后会自动创建一个唯一索引
四、默认约束
为列表中的值设置默认值,default ··· ,如果已经定了值,默认值就无效。
创建表时设置:
CREATE TABLE table_name (
id INT DEFAULT NULL, --默认约束
name VARCHAR(20) UNIQUE --唯一约束
);
创建表后设置:
ALTER TABLE table_name MODIFY COLUMN 列名 列类型 DEFAULT 默认值;
删除默认设置:
-- 将默认值改为NULL
ALTER TABLE table_name MODIFY COLUMN 列名 列类型;
-- 删除了默认值,新增时必须有值
ALTER TABLE table_name ALTER col_name DROP DEFAULT;
五、非空约束
NOT NULL :非空,用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等
create table emp(
id int not null, #非空约束
name varchar(20)
);
修改表中某个列的定义,将其设置为非空(not null):
alter table 表名称 modify column 列名 列类型 not null;
六、外键约束
A表中列的值来自于另外一张表B的主键或唯一键的列称为 外键FK,将被引用值得表称为主表或父表,将引用值得表称为从表或子表。 例如: emp 表中有 deptno 列,值来自于 dept 表的 主键 deptno 。 dept 是主表, emp 是从表。
创建表时:
CREATE TABLE `dept`(
`dept_no` INT PRIMARY KEY,
`dept_name` VARCHAR(20)
);
CREATE TABLE `emp` (
`id` int NOT NULL,
`name` varchar(20),
`deptno` int,
CONSTRAINT fk_dept_no FOREIGN KEY(deptno) REFERENCES dept(dept_no)
);
创建表后:
ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY (本表列名) REFERENCES 被引用表名 (被引用表列名);
ALTER TABLE emp ADD CONSTRAINT fk_dept_no FOREIGN KEY (deptno) REFERENCES dept(dept_no);
删除外键约束:
1.ALTER TABLE table_name DROP CONSTRAINT 外键约束名;
2.ALTER TABLE table_name DROP DOREIGN KEY 外键约束名;