数据库(5)SQL约束
1.主键约束
primary key 约束:
- 主键必须包含唯一的值, 不能重复。
- 主键列不能包含 NULL 值。
- 每个表都应该有一个主键,并且每个表只能有一个主键。
添加主键约束
方式一:创建表时,在字段描述处,声明指定字段为主键:
- 格式:
字段名 数据类型[长度] PRIMARY KEY
CREATE TABLE persons (
pid int primary key, -- 添加了主键约束
lastname varchar(255),
firstname varchar(255),
address varchar(255)
);
INSERT INTO persons VALUES(1, '星驰','周','香港');
INSERT INTO persons VALUES(1, '德华','刘','香港'); -- 设置主键后, 插入失败, 值重复 INSERT INTO persons VALUES(2, '德华','刘',NULL);
INSERT INTO persons VALUES(NULL, '润发','周','香港'); -- 设置主键后, 插入失败, 值 不能为 NULL
方式二:创建表时,在constraint约束区域,声明指定字段为主键:
- 格式:
[constraint 名称] primary key (字段列表)
关键字constraint可以省略,如果需要为主键命名,constraint不能省略,主键名称一般没用。
字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。声明两个以上字段为主键,我们称为联合主键。
CREATE TABLE persons (
pid int, lastname varchar(255),
firstname varchar(255),
address varchar(255),
constraint pk_persons primary key (pid) -- 添加主键约束, 单一字段
);
CREATE TABLE persons (
pid INT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255),
CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname) -- 添加主键约束, 多个字 段, 我们称为联合主键。
);
INSERT INTO persons VALUES(1, '星驰','周','香港');
INSERT INTO persons VALUES(1, '德华','刘','香港');
INSERT INTO persons VALUES(2, '德华','刘',NULL); -- 插入失败
方式三:创建表之后,通过修改表结构,声明指定字段为主键:
- 格式:
ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
CREATE TABLE persons (
pid int,
lastname varchar(255),
firstname varchar(255),
address varchar(255)
);
alter table persons add constraint pk_persons primary key (lastname, firstname); -- 添加联合主键
删除主键约束
如需删除 PRIMARY KEY 约束,请使用下面的 SQL:
- 格式: ALTER TABLE 表名 DROP PRIMARY KEY
alter table persons drop primary key;
2.自动增长列
我们可以在表中使用 auto_increment(自动增长列)关键字,自动增长列类型必须是整
形,自动增长列必须为键(通常是用于主键)。
下列 SQL 语句把 “persons” 表中的 “pid” 列定义为auto_increment 主键
- 格式:
字段名 整数类型[长度][约束] auto_increment
CREATE TABLE persons (
pid INT PRIMARY KEY AUTO_INCREMENT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255)
);
向persons添加数据时,可以不为pid字段设置值,也可以设置成null,数据库将自动维护主键值:
insert into persons(lastname, firstname, address) values('富成','郭','香港');
insert into persons(pid,lastname, firstname, address) values(null,'龙','成','香 港');
- 扩展:默认AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法:
alter table persons auto_increment=100;
面试题
问:针对auto_increment ,删除表中所有记录使用 delete from 表名 或使用 truncate t able 表名 ,二者有什么区别?删除过程:
- delete: 表中记录一条一条删除, auto_increment 计数不会重置为1; 新记录添加时在原有计 数基础上+1
- truncate: 直接将表删除,重新创建新表, auto_increment 计数重置为1;
3.非空约束
NOT NULL 约束:
- 列不接受 NULL 值。
- 要求字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
下面的 SQL 语句要求 “lastname” 列和 “firstname” 列不接受 NULL 值:
添加非空约束
方式一:创建表时,在字段描述处,声明指定字段为非空约束
- 格式:
字段名 数据类型[长度] NOT NULL
CREATE TABLE persons (
pid INT PRIMARY KEY AUTO_INCREMENT,
lastname VARCHAR(255) not null,
firstname VARCHAR(255) not null,
address VARCHAR(255)
);
方式二:修改表时
- 格式:
ALTER TABLT <表名> MODIFY [COLUMN] <列名> <数据类型> NOT NULL;
ALTER TABLE persons MODIFY firstname varchar(255) NOT NULL ;
`删除非空约束
- 格式:
ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]
alter table persons modify lastname varchar(255); alter table persons modify firstname varchar(255);
4.唯一约束
UNIQUE 约束:
指定列的值 不能重复.
注意:
1. UNIQUE 和 PRIMARY KEY 约束均为列提供了唯一性的保证。PRIMARY KEY 是自动定义的 UNIQUE 约束。
2. 每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
3. UNIQUE 不限制 null 值 出现的次数
添加唯一约束
与主键添加方式相同,共有3种
方式一:创建表时,在字段描述处,声明唯一:
- 格式:
字段名 数据类型[长度] UNIQUE
CREATE TABLE persons (
pid INT,
lastname VARCHAR(255) ,
firstname VARCHAR(255) ,
address VARCHAR(255) UNIQUE -- 添加唯一约束
);
INSERT INTO persons(pid,lastname, firstname, address) VALUES(1,'富成','郭','香 港');
INSERT INTO persons(pid,lastname, firstname, address) VALUES(2,'润发','周','香 港');-- 插入失败,值重复
方式二:创建表时,在约束区域,声明唯一:
- 格式:
[constraint 名称] UNIQUE (字段)
CREATE TABLE persons (
pid INT, lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255),
CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束
);
方式三:创建表后,修改表结构,声明字段唯一:
- 格式:
ALTER TABLE 表名 ADD [CONSTRAINT 名称] UNIQUE (字段)
CREATE TABLE persons (
pid INT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255)
);
ALTER TABLE persons ADD CONSTRAINT uni_address UNIQUE (address) -- 添加唯一约束
删除唯一约束
- 如需删除 UNIQUE 约束,请使用下面的 SQL:
- 如果添加唯一约束时,没有设置约束名称,默认是当前字段的字段名。
- 格式:
ALTER TABLE 表名 DROP INDEX 名称
alter table persons drop index uni_address; -- 有唯一约束名称, 使用约束名称删除
alter table persons drop index address; -- 没有唯一约束名称, 使用字段名删除
5.默认约束
default 约束:
- 用于指定字段默认值。
- 当向表中插入记录时,如果没有明确的为字段赋值,则自动赋予默认值。
添加默认约束
添加默认约束,在创建表时候添加
- 格式:
字段名 数据类型[长度] DEFAULT 默认值
CREATE TABLE persons (
pid INT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
);
INSERT INTO persons(pid,lastname, firstname, address) VALUES(1,'富成','郭','北 京');
INSERT INTO persons(pid,lastname, firstname) VALUES(2,'龙','成'); -- 使用address 的默认值
INSERT INTO persons(pid,lastname, firstname, address) VALUES(3,'润发','周', NULL);
删除默认约束
- 格式:
ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]
ALTER TABLE persons MODIFY address VARCHAR(255);
相关文章
- Confluence 6 数据库整合的方法 1:基本流程
- Confluence 6 MySQL 数据库设置准备
- 大数据-NoSQL数据库-HBase操作框架:Phoenix【Java写的基于JDBC API的操作HBase数据库的SQL引擎框架;低延迟、事务性、可使用sql语句、提供JDBC接口】
- Windows下Postgre SQL数据库通过Slony-I 实现数据库双机同步备份
- 冷备份MySQL数据库并且使用Docker直接运行的操作过程
- Oracle、Mysql和SQL Server数据库连接的URL写法
- 数据库SQL优化大总结之 百万级数据库优化方案(转)
- 如何访问局域网的Access数据库?
- Spring data jpa @Version注解及自定义数据库乐观锁实现
- 玩转数据库之 Group by Grouping
- 【数据库摘要】12_Sql_存储过程
- [Sqlite] 移动嵌入式数据库Sqlite日报SQL操作语句汇总
- mysql 数据库替换字段文字
- 23.数据库学习七 IFORMATION_SCHEMA 详解和SQL注入语句分析
- Sql Server系列:数据库操作
- Mac OS安装PostgreSQL数据库
- 数据库与 SQL 博客清单
- MySQL数据库25条规范解读
- flashback_area 区域溢出导致数据库宕机