图片 39

(三)操作数据表

背景


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

1、概述

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

图片 1

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

图片 2


图片 3

2、创建数据表

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

图片 4

创建表的语法结构如下:

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

图片 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]

示例如下:

图片 6

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

图片 7


图片 8

4、查看数据表结构

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

SHOE COLUMNS FROM tbl_name

DESC tbl_name

SHOW CREATE TABLE tbl_name

示例如下:

图片 9

SHOE COLUMNS FROM tbl_name

图片 10

DESC tbl_name

图片 11

SHOW CREATE TABLE tbl_name

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

图片 12

SHOW CREATE TABLE tbl_name\G


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

5、记录的插入与查找

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

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

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

图片 13

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

图片 14

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

图片 15

注意:

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

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

图片 16

此处的 * 是指字段的过滤


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

6、空值与非空

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

图片 17

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

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

图片 18

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

图片 19

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

图片 20

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

图片 21


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

7、自动编号

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

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

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

图片 22

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

图片 23

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

图片 24

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

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

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

图片 25

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

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

图片 26

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


期望:

8、唯一约束

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

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

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

图片 27

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

图片 28


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

9、默认约束

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

图片 29

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

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

图片 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_name\G;

  • 插入记录
    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

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

图片 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中检验一下。

declare @temp int --定义输出参数
exec dbo.NumOfDeptNOForInsert @temp  --调用储存过程
print @temp  --打印输出参数
  •  缺失值的检验:

图片 32             图片 33

调用存储过程看看缺失的第一个值是不是5,结果跟预期一样。

  • 缺省值的检验:

图片 34           
图片 35

调用存储过程,找到的第一个缺省值为7,结果跟预期的一样。

 实践

前面的分析设计做好后,当然就是运用于实践了,主要是编写获取自增长字段的缺失值/缺省值的方法: 

        /// <summary>
        /// 获取自增长字段的第一个缺失值或者缺省值
        /// </summary>
        /// <returns>缺失值/缺省值</returns>
        private int GetDeptNO()
        {
            string cmdText = @"NumOfDeptNOForInsert";
            SqlParameter[] parameters =
            {
                new SqlParameter("@temp",SqlDbType.Int)
            };
            parameters[0].Direction = ParameterDirection.Output;
            int deptNO = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.StoredProcedure, cmdText,parameters);
            return deptNO;
        }

程序整体的UI设计和编码在博文《数据库操作类SqlHelper》中都已经讲述,这里就不在反复讲了。相对于之前来说,需要更改代码的地方为“增加”按钮的点击处理程序和InsertData()方法: 

        private void tsbInsert_Click(object sender, EventArgs e)
        {
            cmdType = CmdType.Insert;
            //将gbDept控件设置可用,textbox控件设为可用,并将Text属性清空
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = true;
            this.txtDeptName.Text = string.Empty;
            //显示即将插入的DeptNO值
            this.lbDeptNO.Text = GetDeptNO().ToString();
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        private void InsertData()
        {
            //判断系部名称是否为空
            if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim()))
            {
                MessageBox.Show("系部名称不能为空!");
                return;
            }
            //定义插入数据的SQL脚本,其中set identity_insert tb_Department on/off主要是为了能让自增长主键连续有序地插入
            string cmdText = @"set identity_insert tb_Department on 
                               insert into tb_Department(DeptNO,DeptName) values(@DeptNO,@DeptName)
                               set identity_insert tb_Department off";
            ////定义插入数据的Sql脚本
            //string cmdText = @"insert into tb_Department(DeptName) values(@DeptName)";
            //SQL脚本参数设置
            SqlParameter[] parameters =
            {
                new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text),
                new SqlParameter("@DeptName",(object)this.txtDeptName.Text.Trim())
            };
            //执行插入,并返回受影响的行数
            int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
            //判断是否插入成功,并提示
            if (rows > 0)
            {
                //更新datagridview控件的数据
                LoadData();
                //显示即将插入的DeptNO值
                this.lbDeptNO.Text = GetDeptNO().ToString();
                //将系部名称设为空
                this.txtDeptName.Text = string.Empty;
                MessageBox.Show("插入成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("插入失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            //执行增删改操作
            switch (cmdType)
            {
                case CmdType.Insert:
                    InsertData();
                    break;
                case CmdType.Delete:
                    DeleteData();
                    break;
                case CmdType.Update:
                    UpdateData();
                    break;
            }
        }

结果

UI和编码完成后,调试程序是很关键的,能从调试的过程中重现整个功能的思路,也能找到一些问题所在,修复bug,然后重编码。废话不多说看结果吧:

1.缺失值:

假设一开始的tb_Department表如下图所示,理论上缺失的值为(4,5,7),现在往表里插入新值,看看结果如何。点击“增加”按钮,窗体下方系部编号自动出现第一个缺失值4,系部名称我们设置为“嘟嘟系”,提交添加成功后,系部编号会自动显示下一个缺失值5。为了后面的缺省值的结果,我们再增加缺失值(5,7)两行数据,使DeptNO字段连续递增。

 图片 36图片 37

2.缺省值:

当DeptNO字段连续递增时,如下图所示,点击“增减”按钮,窗体下方的系部编号成功地提取到第一个缺省值9,系部名称输入“物理系”,提交增加成功后,系部编号会自动显示下一个缺省值10。

 图片 38图片 39

调试结果显示我们提出的需求已经得到解决。

一.总结

本文主要针对数据表中自增长字段的插入问题进行讲解,不管表中的数据增删过多少次,程序总是能提供自增长字段的缺失值或者缺省值用于新数据行的插入,从而实现插入自增长字段值的连续递增特性。