SQL SERVER identity（自增）的用法

SQL SERVER identity（自增）的用法

一. identity 的基本用法

1.含义

identity 表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给identity 修饰的字符赋值，否则编译时会报错。

2.语法

列名 数据类型 约束  identity (m,n)

m 表示的是初始值，n 表示的是每次自动增加的值

如果m和n的值都没有指定，默认为（1,1）

要么同时指定m和n的值，要么m和n都不指定，不能只写其中一个值，不然会出错

3.实例演示

不指定m和n的值

CREATE TABLE student1
(
sid int primary key identity,
sname nchar(8) not null,
ssex nchar(1)
)

INSERT INTO student1(sname,ssex)  VALUES ('张三','男')
INSERT INTO student1 VALUES ('李四','女')   ------可以省略列名
INSERT INTO student1 VALUES ('王五','女')

SELECT * FROM  student1

sid	sname	ssex
1	张三      	男
2	李四      	女
3	王五      	女

指定m和n的值

CREATE TABLE student2
(
sid int primary key identity(20,5),
sname nchar(8) not null,
ssex nchar(1)
)

INSERT INTO student2(sname,ssex) VALUES ('张三','男')
INSERT INTO student2 VALUES ('李四','女')   -----可以省略列名
INSERT INTO student2 VALUES ('王五','女')

SELECT * FROM  student2

sid	sname	ssex
20	张三      	男
25	李四      	女
30	王五      	女

4.删除一条记录接着插入

把sid为3的记录删除，接着插入，新插入的记录的sid不是3，而是4

SELECT * FROM  student1

sid	sname	ssex
1	张三      	男
2	李四      	女
3	王五      	女

delete from student1 where sid = 3
insert into student1(sname,ssex) values ('李四1','女')
select * from student1

sid	sname	ssex
1	张三      	男
2	李四      	女
4	李四1     	女

二. 重新设置 identity 的值

1.语法

dbcc checkident(表名,reseed,n)

n+1表示的是表中 identity 字段的初始值(n的初始值可以为0)

也就是说：如果插入的是id为2的记录，则n的值是1

2.实例演示

create table student3
(
sid int primary key identity,
sname nchar(8) not null,
ssex nchar(1)
)

insert into student3 values ('张三','男')
insert into student3 values ('李四','女')

select * from  student3
sid	sname	ssex
1	张三      	男
2	王五      	女

delete from student3 where sid = 2     -----把sid=2的记录删除

select * from student3
sid	sname	ssex
1	张三      	男

dbcc checkident ('student3',reseed,1)     -----把student3表中identity字段的初始值重新设置为1
insert into student3 values ('王五','女')
select * from student3
sid	sname	ssex
1	张三      	男
2	王五      	女

-----student3表中identity字段的初始值重新设置为3
delete from  student3 where  sid = 2     -----把sid=2的记录删除

select * from student3
sid	sname	ssex
1	张三      	男

dbcc checkident ('student3',reseed,3)    -----把student3表中identity字段的初始值重新设置为3
insert into student3 values ('王五','女')
select * from student3
sid	sname	ssex
1	张三      	男
4	王五      	女

-----再次插入，即按照默认的identity字段的初始值1进行插入
insert into student3 values ('王六','女')
insert into student3 values ('王七','女')

select * from student3
sid	sname	ssex
1	张三      	男
4	王五      	女
5	王六      	女
6	王七      	女

三. 向identity 字段插入数据

1.语法

set identity_insert 表名 on
insert into 表名（列名1，列名2，列名3，列名4） values (数据1，数据2，数据3，数据4)
set identity_insert 表名 off

注意：插入数据时必须得指定identity修饰字段的名字

2.实例演示

create table student4
(
sid int primary key identity(20,5),
sname nchar(5) not null,
ssex nchar(1)
)
insert into student4 values ('张三','男')
insert into student4 values ('李四','女')
insert into student4 values ('王五','女')
select * from student4 
sid	sname	ssex
20	张三   	男
25	李四   	女
30	王五   	女

set identity_insert student4 on                  -----向identity字段插入数据
insert into student4 (sid,sname,ssex) values ('21','黑六','男')
set identity_insert student4 off 

select * from student4 
sid	sname	ssex
20	张三   	男
21	黑六   	男
25	李四   	女
30	王五   	女


insert into student4 values ('赵七','女')
select * from student4 
sid	sname	ssex
20	张三   	男
21	黑六   	男
25	李四   	女
30	王五   	女
35	赵七   	女