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 ('王五','女');

指定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 ('王五','女');

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

把sid为2的记录删除,继续插入,新插入的记录的sid不是2,而是3

create table student3
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student3(sname,ssex) values ('张三','男');
insert into student3 values ('李四','女');
delete from student3 where sid=2;--把sid为2的记录删除
insert into student3 values ('王五','女');

二、重新设置identity的值

1.语法

dbcc checkident(表名,reseed,n);

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

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

2.实例演示

create table student4
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student4(sname,ssex) values ('张三','男');
insert into student4 values ('李四','女');
delete from student4 where sid=2;--把sid为2的记录删除
dbcc checkident('student4',reseed,1);--把student4表中identity字段的初始值重新设置为1
insert into student4 values ('王五','女');

三、向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 student5
(
 sid int primary key identity(20,5),
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student5(sname,ssex) values ('张三','男');
insert into student5 values ('李四','女');
insert into student5 values ('王五','女');
set identity_insert student5 on;
/*
insert into student5 values ('黑六','男');--error
insert into student5 values (21,'黑六','男');--error
*/
insert into student5(sid,sname,ssex) values (21,'黑六','男');
set identity_insert student5 off;
/*
insert into student5 values (22,'赵七','女');--error
insert into student5(sid,sname,ssex) values (22,'赵七','女');--error
*/
insert into student5 values ('赵七','女');

补充知识:SQL Server 添加与删除主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键,并且每个表只能有一个主键。

主键约束操作包含了添加约束和删除约束,修改约束其实是添加约束或者删除约束。

添加主键约束比较 简单,但是删除一个没有约束名的主键约束则比较复杂,如果不是很不了解SQL Server的话则比很难实现该功能。

主键约束操作基本语句

如果是在新建表的时候添加主键约束比较简单,直接在列名后加入primary key即可,标准的添加主键约束的SQL脚本如下:

create table 表名 (  字段名1 int not null, ...,
  [constraint 约束名] primary key (字段名1, …)
)

其中约束名可选(建议选择,否则系统会分配随机临时名称)。这种方法可以任意添加多个或一个主键约束。

对已有表添加主键约束,与上面的脚本类似,如下:

alter table 表名 [add constraint 约束名] primary key(字段名1,... )

其中约束名与上相同,可选,建议指定。

SQL Server删除主键约束与MySQL不同,需要主键的约束名称才能删除,SQL Server标准的删除主键的脚本如下:

ALTER TABLE 表名DROP CONSTRAINT 约束名

当然该语句只能删除已知约束名的主键约束。

删除未知主键约束名的约束

根据之前的语句判断,这个约束名如果之前已经指定了那还好,否则此次就没有办法删除了,重点在于如何获取表中的主键约束名,既然可以根据约束名,那么就说明约束名是存储在数据库中的,当然是存储在系统表中的,运行下SELECT * FROM SYS.OBJECTS 语句看看查询结果,数据库中所有的约束都 在里面了,name项以PK大头的都是主键约束,看看它的type_desc是不是PRIMARY_KEY_CONSTRAINT或者type是不是 PK?(当然指定的名称没使用PK前缀另当别论)。

既然已知表名,而且也知道主键约束存储位置,关键就是二者如何关联起来,使得使用表名就可以查询到主键 约束名称,这其中沟通的桥梁便是parent_object_id。

这里给出的只是object_id,那么又如何找出表的object_id呢?再仔细 看看刚才的搜索结果,原来数据库中的相关信息全在里面,表,函数等都在里面,而且有自身的object_id。

接下来整合下上面的思路,根据表名查询表ID,根据表ID找到主键约束名称,采用级联方式查询就可以查询出来。可以构造如下的查询语句:

SELECT NAME from SYS.OBJECTS WHERE TYPE_DESC ='PRIMARY_KEY_CONSTRAINT' AND
PARENT_OBJECT_ID = (SELECT OBJECT_ID
FROM SYS.OBJECTS WITH(NOLOCK) WHERE NAME = '表名')

然后NAME就是我们需要的约束名称了。然后在构造一个上面的删除约束的SQL语句执行就可以了,典型的SQL如下:

ALTER TABLE 表名 DROP CONSTRAINT NAME(约束名)

其他约束都可以采用这种方法进行删除。

以上这篇SQL Server中identity(自增)的用法详解就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

(0)

相关推荐

  • SqlServer生成连续数字根据指定的数字操作

    需求是使用sqlserver根据指定的数字和表生成一串连续的数字,类似于oracle中ROWNUM的功能,具体实现如下: 一.Oracle使用ROWNUM实现方式 SELECT ROWNUM number_list FROM 表名 WHERE ROWNUM <= 10; 二.SqlServer实现上述功能的三种方式 1.使用MASTER-spt_values方式 SELECT number FROM MASTER..spt_values WHERE TYPE = 'P' AND number >

  • SQL Server中row_number函数的常见用法示例详解

    一.SQL Server Row_number函数简介 ROW_NUMBER()是一个Window函数,它为结果集的分区中的每一行分配一个连续的整数. 行号以每个分区中第一行的行号开头. 以下是ROW_NUMBER()函数的语法实例: select *,row_number() over(partition by column1 order by column2) as n from tablename 在上面语法中: PARTITION BY子句将结果集划分为分区. ROW_NUMBER()函

  • SQL Server如何通过创建临时表遍历更新数据详解

    前言: 前段时间新项目上线为了赶进度很多模块的功能都没有经过详细的测试导致了生成环境中的数据和实际数据对不上,因此需要自己手写一个数据库脚本来更新下之前的数据.(线上数据库用是SQL Server2012)关于数据统计汇总的问题肯定会用到遍历统计汇总,那么问题来了数据库中如何遍历呢?好像并没有for和foreach这种类型的功能呀,不过关于数据库遍历最常见的方法当然是大家经常会想到的游标啦,但是这次我并没有使用游标,而是通过创建临时表的方式来更新遍历数据的. 为什么不使用游标,而使用创建临时表?

  • SQL Server 开窗函数 Over()代替游标的使用详解

    前言: 今天在优化工作中遇到的sql慢的问题,发现以前用了挺多游标来处理数据,这样就导致在数据量多的情况下,需要一行一行去遍历从而计算需要的数据,这样处理的结果就是数据慢,容易卡死. 语法介绍: 1.与Row_Number() 函数结合使用,对结果进行排序,这个是我们使用的非常多的 2.与聚合函数结合使用,利用over子句的分组和排序,对需要的数据进行操作 例如:SUM() Over() 累加值.AVG() Over() 平均数 MAX() Over() 最大值.MIN() Over() 最小值

  • SQL Server 使用join all优化 or 查询速度

    比如:,master,test, 表示 该用户为 test 的下级代码,test登录后可以看到 test名下的业务和所有下级代理的业务.相关表的结构如下: user表 大约10万条记录 |-uid-|-user-|----site------| | 1 | test | ,master, | | 2 | user | ,master,test,| product表 大约30万条记录 |-pid-|-product-|-puser-| | 1 | order01 | test | | 2 | or

  • 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 p

  • SQL Server中row_number分页查询的用法详解

    ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号.在查询时应用了一个排序标准后,只有通过编号才能够保证其顺序是一致的,当使用ROW_NUMBER函数时,也需要专门一列用于预先排序以便于进行编号. ROW_NUMBER() 说明:返回结果集分区内行的序列号,每个分区的第一行从1开始. 语法:ROW_NUMBER () OVER ([ <partition_by_clause> ] <order_by_clause>) . 备注:ORDER

  • SQL Server中日期时间函数的用法详解

    1.getdate():获取当前日期 返回当前SQLServer服务器所在计算机的日期和时间.返回值舍入到最近的秒小数部分,精度为.333秒数据库十七偏移量不包含在内. select getdate() --输出 2013-03-09 15:16:00.570 2.getutcdate():获取UTC时间值 select GETUTCDATE() -- 2013-06-18 08:02:53.253 3.year():获取年度信息 year函数以int数据类型的格式返回特定日期的年度信息.其中的

  • SQL Server中的排名函数与分析函数详解

    一.排名开窗函数概述 SQL Server的排名函数是对查询的结果进行排名和分组,TSQL共有4个排名函数,分别是:ROW_NUMBER.RANK.DENSE_RANK和NTILE. 他们和OVER()函数搭配使用,按照特定的顺序排名. 排名开窗函数可以单独使用ORDER BY 语句,也可以和PARTITION BY同时使用. PARTITION BY用于将结果集进行分组,开窗函数应用于每一组. ODER BY 指定排名开窗函数的顺序.在排名开窗函数中必须使用ORDER BY语句. 1.ROW_

  • Sql server中内部函数fn_PhysLocFormatter存在解析错误详解

    前言 有网友指出,SQL Server 2012中fn_PhysLocFormatter内部函数在解析数据行记录位置时存在错误,见:http://www.itpub.net/thread-1751655-1-1.html,实际测试后发现,一是2008R2中同样存在问题,二是不仅页号解析存在问题,槽号解析也存在同样问题. 下面先查看表NT_SiteInfo的数据行记录位置. select SiteID,%%physloc%%,sys.fn_PhysLocFormatter(%%physloc%%)

  • Sql Server 开窗函数Over()的使用实例详解

    利用over(),将统计信息计算出来,然后直接筛选结果集 declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,'name3','P2',4 union all select 4,'name4

  • JSP 中request与response的用法详解

    JSP 中request与response的用法详解 概要: 在学习这两个对象之前,我们应该已经有了http协议的基本了解了,如果不清楚http协议的可以看我的关于http协议的介绍.因为其实request和response的使用大部分都是对http协议的操作. request对象的介绍 我们先从request对象进行介绍: 我们知道http协议定义了请求服务器的格式: 请求行 请求头 空格 请求体(get请求没有请求体) 好了,这里我们就不详细介绍了,我们只看几个应用就可以了,没什么难度: 应

  • IOS开发中NSURL的基本操作及用法详解

    NSURL其实就是我们在浏览器上看到的网站地址,这不就是一个字符串么,为什么还要在写一个NSURL呢,主要是因为网站地址的字符串都比较复杂,包括很多请求参数,这样在请求过程中需要解析出来每个部门,所以封装一个NSURL,操作很方便. 1.URL URL是对可以从互联网上得到的资源的位置和访问方法的一种简洁的表示,是互联网上标准资源的地址.互联网上的每个文件都有一个唯一的URL,它包含的信息指出文件的位置以及浏览器应该怎么处理它. URL可能包含远程服务器上的资源的位置,本地磁盘上的文件的路径,甚

  • JavaScript中SetInterval与setTimeout的用法详解

    setTimeout 描述 setTimeout(code,millisec) setTimeout() 方法用于在指定的毫秒数后调用函数或计算表达式. 注:调用过程中,可以使用clearTimeout(id_of_settimeout)终止 参数 描述 code 必需,要调用的函数后要执行的 JavaScript 代码串. millisec 必需,在执行代码前需等待的毫秒数. setTimeinterval setInterval(code,millisec[,"lang"]) 参数

  • C++中auto_ptr智能指针的用法详解

    智能指针(auto_ptr) 这个名字听起来很酷是不是?其实auto_ptr 只是C++标准库提供的一个类模板,它与传统的new/delete控制内存相比有一定优势,但也有其局限.本文总结的8个问题足以涵盖auto_ptr的大部分内容. auto_ptr是什么? auto_ptr 是C++标准库提供的类模板,auto_ptr对象通过初始化指向由new创建的动态内存,它是这块内存的拥有者,一块内存不能同时被分给两个拥有者.当auto_ptr对象生命周期结束时,其析构函数会将auto_ptr对象拥有

随机推荐