wns9778.com_威尼斯wns.9778官网

热门关键词: wns9778.com,威尼斯wns.9778官网
wns9778.com > 计算机教程 > 自增长字段值的连续递增实现【wns9778.com】

原标题:自增长字段值的连续递增实现【wns9778.com】

浏览次数:101 时间:2019-05-11

背景


在上一篇《数据库操作类SqlHelper》博文的最后,提到了一个实践运用中遇到的问题,就是数据库表中的自增长字段的赋值不受人为控制。比如数据库有一个tb_Department表,DeptNO字段为自增长主键。

1、概述

数据表,或简称为表,是数据库最重要的组成部分之一,是其他对象的基础,可以理解为类似于Excel表格,有多行多列,示例如下:
  假设要存储通讯录信息,对于数据库而言,是用表来进行存储,首先要根据实际需求确定关于通讯录要存储哪些数据。本例中要存储“姓名”、“性别”及“联系方式”,这种限定每一列的存储内容的值称为字段,同时还要确定好该字段所属的数据类型

wns9778.com 1

  当存储了全部信息后,会发现实际上每一行就是一个用户完整的通讯录信息,因此像这样的一行就被称为一条记录。

wns9778.com 2


wns9778.com 3

2、创建数据表

在登录到MySQL之后,首先查看现已存在的数据库有哪些,假设本次要在“test”数据库中存储数据,则选择使用该数据库,并用“SELECT”验证当前使用的确实为“test”数据库(该部分语句可参见初涉MySQL)。

wns9778.com 4

创建表的语法结构如下:

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
column_name data_type,
……
)
其中“[IF NOT EXISTS]”在之前已经介绍过,而“column_name”指的是字段的名称,“data_type”则是数据类型;“,”是字段之间的分隔符,最后一个字段后不需要添加“,”。

wns9778.com 5

采用这种输入方式可以使代码看起来更简洁明了

根据语法创建该表,其中:

  • “username”即用户名称,数据类型为VARCHAR,最多存储20个字节;
  • “age”即用户年龄,数据类型为TINYINT,UNSIGNED指的是无符号,即非负;
  • “birthday”即用户出生日期,数据类型为DATE;
  • “salary”即用户收入,数据类型为FLOAT,最多存储8位数,小数点后2位数;
  • “resume”即用户信息摘要,数据类型为TEXT。

现在插入一行数据

3、查看数据表

语法结构如下:

SHOE TABLES [FROM db_name]
[LIKE 'pattern' | WHERE expr]

示例如下:

wns9778.com 6

添加“[FROM db_name]”之后,可以查看其他数据库下含有的数据表,例如查看mysql数据库下的数据表:

wns9778.com 7


wns9778.com 8

4、查看数据表结构

对于已创建成功的数据表,若想查看其结构是否与输入的相一致,可使用如下语句:

SHOE COLUMNS FROM tbl_name

DESC tbl_name

SHOW CREATE TABLE tbl_name

示例如下:

wns9778.com 9

SHOE COLUMNS FROM tbl_name

wns9778.com 10

DESC tbl_name

wns9778.com 11

SHOW CREATE TABLE tbl_name

  这种模式会将数据表创建时的源码、使用的储存引擎及编码都显示出来,但是格式会比较乱,可以在末尾添加“G”进行格式化处理,便于查看:

wns9778.com 12

SHOW CREATE TABLE tbl_nameG


啊!DeptNO字段怎么就是22了呢,不应该是从4开始吗?

5、记录的插入与查找

如果用Excel表格来举例,那么之前仅仅是完成了“列”的创建,现在要来填充“行”的内容,也就是记录,语句如下:

INSERT [INTO] tbl_name [(col_name,……)] VALUES(val,……)

语法中字段名加上了中括号,意味着可以省略,如果省略字段称,就要为所有的字段都赋值,示例如下:

wns9778.com 13

如果在省略字段名的情况下,有的字段没有赋值,系统就会报错,提示字段名与值不匹配:

wns9778.com 14

当然也可以对指定的字段名进行赋值,例如只对“用户名称”与“用户收入”进行赋值:

wns9778.com 15

注意:

  1. 字段名与赋值的类型、个数、顺序始终能一一对应;
  2. 赋值时不要超过字段所定义的长度;
  3. 如果需要插入空值,使用null;
  4. 插入日期或字符时,使用单引号(‘’)包围。

若要验证记录是否已被写入数据表,可以使用“SELECT expr,… FROM tbl_name”,来查看,关于“SELECT”的详细语法,日后会介绍到,此处先简单的使用一下:

wns9778.com 16

此处的 * 是指字段的过滤


原因:这个表之前进行过很多插入操作,数据库针对自增长字段的每次插入都会自动 1,后来删除了一部分行数据,然后重新插入的时候,数据库不会依据表中缺失的字段值进行赋值,而是在原先的基础上继续 1赋值。

6、空值与非空

当用户在网站注册账户时,可能会遇到一些必填的选项,那么对于数据表而已,同样可以设置某些字段可以为空或禁止为空,例如:

wns9778.com 17

其中“NULL”表示该字段值允许为空

创建的数据表“tb2”,用于空值与非空的测试,其中“NULL”,代表该字段值允许为空;“NOT NULL”,则表示该字段值禁止为空:

wns9778.com 18

查看数据表的基本结构,可见对于字段值是否允许为空,用户名是不允许,而用户年龄允许为空。

wns9778.com 19

之后分别输入两条字段互相为空的记录:

wns9778.com 20

第一条记录中对于用户年龄设为空值,成功计入了数据表,但是第二条用户名设为空,系统提示错误,显示“用户名”字段不允许为空。查看表中数据发现,只有第一条记录被写入:

wns9778.com 21


结果:在新插入的“哈哈系”数据行之前,其实数据库已经向表里插入过21次了,只是DeptNO字段值大于3的行数据被删除了,现在要新插入行数据的话,就会在21的基础上 1,也就是第二个表中出现的22了。

7、自动编号

在数据储存的过程中,有时需要保证某条记录的唯一性,此时可以为该字段添加“AUTO_INCREMENT”属性,即自动编号,默认情况下,起始值为1,每次的增量为1,即1、2、3、……,且必须与主键组合使用;自动编号的数据类型一定是数值型,如果使用小数类型,则必须保证小数点后的位数为0。

主键约束即“PRIMARY KEY”,可以保证数据的唯一性,每张数据表只能存在一个主键,且自动设置为“NOT NULL”。

创建数据表“tb3”,用于自动编号的测试:

wns9778.com 22

之后查看“tb3”的基本结构:

wns9778.com 23

  可见“id”字段的主键自动设为“NOT NULL”,且自动编号,意味着之后只需要向数据表中输入“用户名”即可:

wns9778.com 24

由于只输入用户姓名,因此必须标明字段“(username)”,在输入了4条记录后,查看该数据表,证明“id”字段的确自动赋值为1、2、3、4。

注意:
自动编号必须与主键一同使用,但主键并不是必须与自动编号一同使用,虽然二者都可以保证数据的唯一性。

创建数据表“tb4”,用于测试主键赋值的唯一性:

wns9778.com 25

主键写“PRIMARY KEY”或“KEY”都可以

此时向表中写入记录,注意没有自动赋值,所有字段都需要写入数据:

wns9778.com 26

  可见当“id”为6或第一次为32时,记录都被写入数据表,但第二次重复写入32时,系统提示出错,因此保证了数据的唯一性。


期望:

8、唯一约束

唯一约束即“UNIQUE KEY”,同样可以保证记录的唯一性,与主键约束不同的是:每张数据表可以存在多个唯一约束;唯一约束的字段值可以为空。

此时可能会有疑问:既然数据表中允许存在多个唯一约束,唯一约束又允许为空,相当于数据表中存在多个空值,岂不是互相矛盾?
要注意数据在存储时,即使有多个值,但最终保留的空值只有一个,因此也符合了唯一性。

创建数据表“tb5”,用于测试唯一约束的实际效果:

wns9778.com 27

再输入第二次“Tom”时,系统提示错误,证明其具有唯一性:

wns9778.com 28


  1. 在插入新数据的时候,针对自增长字段可以人为控制;
  2. 实际运用中,其实用户并不知道数据表中自增长字段缺失的是哪些值,程序需要自动提供缺失或者缺省值。

9、默认约束

默认约束即“DEFAULT”,当插入记录时,如果没有明确为字段赋值,系统则会自动赋予其默认值。
  创建数据表“tb6”,用于演示默认值的实际效果:

wns9778.com 29

ENUM即枚举值,详细可参见[数据类型](http://www.jianshu.com/p/d4324057b548)

其中对于“性别”字段设计了“男性”、“女性”、“保密”三种可选项,其中默认是“保密”。

wns9778.com 30

在输入记录时,只写入了用户名为“Tom”,因此性别为默认自动添加的“保密”。


设计

10、操作数据表的SQL语句汇总:

  • 创建数据表
    CREATE TABLE [IF NOT EXISTS] table_name(
    column_name data_type,
    column_name data_type,
    ……
    );

  • 查看数据表
    SHOE TABLES [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

  • 查看数据表结构
    SHOE COLUMNS FROM tbl_name;

    DESC tbl_name;

    SHOW CREATE TABLE tbl_nameG;

  • 插入记录
    INSERT [INTO] tbl_name [(col_name,……)] VALUES(val,……);

  • 查看记录
    SELECT expr,… FROM tbl_name;


版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

1.在插入新数据的时候,针对自增长字段可以人为控制

数据库中针对自增长字段在插入时,不可以指定显式值的。

insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')

这样插入数据会报错的,提示你“当Identity_Insert设置为off时,不能为表’tb_Department’中的标识列插入显式值”。很明显,第一个期望的解决方案就是将Identity_Insert设置on,然后执行显式值插入,最后关闭标识列插入开关。

set identity_insert tb_Department on
insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')
set identity_insert tb_Department off

执行看看能不能插入,哇哦,成功了,棒棒哒。

wns9778.com 31

2.实际运用中,用户并不知道数据表中自增长字段未使用有哪些值,程序需要自动提供缺失或者缺省值

自增长字段的值分为缺失值和缺省值(这个术语是我自己定的,为了方便描述)

缺失值:比如数据表中自增长字段的值为(1,2,3,5),则缺失值为4。要想让程序自动检索到缺失值,需要对数据表进行全面扫描,逐行判断自增长字段的值是否连续递增,只要检索到不连续的值就将对应序列的值返回,并显示在窗体上,无需用户自己输入。

缺省值:比如数据表中自增长字段的值为(1,2,3,4),则缺省值为5。假设原先有10行数据,然后将大于4的行删除后,自增长字段本身还是连续递增的,只需要找到缺省值,返回给用户。

利用SQL脚本创建存储过程实现:(注意:该实现返回当前自增长字段中第一个缺失值/缺省值,只适用于每次插入一行数据的情况)

--创建一个存储过程用于自动提取自增长字段的第一个缺失值和缺省值
create procedure NumOfDeptNOForInsert
@temp int output  --定义一个输出参数,用于返回缺失值/缺省值
as
declare @Count int   --定义一个当前表中的行数
select @Count=COUNT(1) from tb_Department   --给变量@Count赋值
declare @I int, @IsOK bit = 0,@num int = 1  --定义一个用于循环的@I变量,一个用于判断是缺失值还是缺省值的变量@IsOK,一个记录缺省值的变量@num
set @I = 1;   --变量@I赋值为1
while(@I <= @Count)   --开始循环扫描行数据
begin
    select @temp=DeptNO from tb_Department where DeptNO=@I   --检索DeptNO值=@I值的行数据
    if(@temp != @I)  --判断@I值与DeptNO值是否比对不成功
    begin
        set @temp = @I  --将@I值赋值给@temp
        set @IsOK = 0   --标记为缺失值
        break    --退出循环     
    end
    else    --判断@I值与DeptNO值是否比对成功
    begin
        set @I = @I  1  --@I 1
        set @num = @I   --将@I赋值给@num
        set @IsOK = 1   --标记为缺省值
    end 
end   

if(@IsOK =0)   --判断是缺失值还是缺省值,如果是缺失值
begin
    select @temp  --直接返回@temp
end
else    --如果是缺省值
begin
    set @temp = @num  --将@num赋值给@temp
    select @temp      --再返回@temp
end

自增长字段的连续递增插入的存储过程设计好后,首先在SQL Server中检验一下。

本文由wns9778.com发布于计算机教程,转载请注明出处:自增长字段值的连续递增实现【wns9778.com】

关键词: wns9778.com

上一篇:针对程序员的数据库原则

下一篇:没有了