澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站数据类型与操作数据表,操

2019-06-01 作者:数据库网络   |   浏览(73)

背景


2-1 内容回顾

MySQL默认的端口号是多少?

3306

MySQL中的超级用户叫什么?

root

创建数据库

CREATE DATABASE

修改数据库

ALTER DATABASE

删除数据库

DROP DATABASE

上节回顾

上节回顾

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

1、概述

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

澳门新萄京官方网站 1

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

澳门新萄京官方网站 2


2-2 MySQL数据类型之整型

澳门新萄京官方网站 3

澳门新萄京官方网站 4

澳门新萄京官方网站 5

2、创建数据表

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

澳门新萄京官方网站 6

创建表的语法结构如下:

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

澳门新萄京官方网站 7

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

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

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

一、数据类型

  • 数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。

一、数据类型

什么是数据类型?

  数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。

所谓数据类型,最直接的理解就是我们有些是存储数字的,而数字当中,我们有时是要存储为整数,有些可能要存储为小数,还有的可能要存储为日期、字符型,这就是我们最直观、最直接了解的数据类型。

在mysql当中,数据类型主要有以下几类:

整型

澳门新萄京官方网站 8

浮点型

澳门新萄京官方网站 9

float最高存储小数点后7位,一般选择float,double占用存储空间较大。因为为了优化,需要选择最合适的,而不一定是最大的。

日期时间型

澳门新萄京官方网站 10

各日期类型的开始时间到结束时间:
1、YEAR:1970至2069
2、TIME:-838:59:59至838:59:59
3、DATE:1000-1-1至9999-12-31
4、DATETIME: 1000-1-1 00:00:00至9999-12-31 23:59:59
5、TIMESTAMP存储范围:1970-1-1 00:00:00到2037-12-31 23:59:59

日期时间类型在实际中应用较少,因为牵扯到跨时区的问题,如果真的要使用时间类型,一般使用数字的形式表示.

字符型

澳门新萄京官方网站 11

(1)字符型:char(M) 为定长类型,eg:char(5)当我们在存储"abc"时,剩余2位用空格补齐。范围:0~255
(2)varchar(M)为变长类型,eg:varchar(5)当我们在存储"abc"时,则varchar中只存abc,不加空格。范围:0~65535

(3)1Byte == 8bit,11111111 == 2E8 == 255.

(4)enum('value1','value2',·····),只能从给定的enum枚举中选择
(5)SET('value1','value2','value3'....)就相当于一个集合,在里面做排列组合,可以多选里面的值。

一、数据类型

什么是数据类型?

  数据类型是指列、存储过程参数、表达式和局部变量的数据特征,它决定了数据的存储格式,代表了不同的信息类型。

所谓数据类型,最直接的理解就是我们有些是存储数字的,而数字当中,我们有时是要存储为整数,有些可能要存储为小数,还有的可能要存储为日期、字符型,这就是我们最直观、最直接了解的数据类型。

在mysql当中,数据类型主要有以下几类:

整型

澳门新萄京官方网站 12

浮点型

澳门新萄京官方网站 13

float最高存储小数点后7位,一般选择float,double占用存储空间较大。因为为了优化,需要选择最合适的,而不一定是最大的。

日期时间型

澳门新萄京官方网站 14

各日期类型的开始时间到结束时间:
1、YEAR:1970至2069
2、TIME:-838:59:59至838:59:59
3、DATE:1000-1-1至9999-12-31
4、DATETIME: 1000-1-1 00:00:00至9999-12-31 23:59:59
5、TIMESTAMP存储范围:1970-1-1 00:00:00到2037-12-31 23:59:59

日期时间类型在实际中应用较少,因为牵扯到跨时区的问题,如果真的要使用时间类型,一般使用数字的形式表示.

字符型

澳门新萄京官方网站 15

(1)字符型:char(M) 为定长类型,eg:char(5)当我们在存储"abc"时,剩余2位用空格补齐。范围:0~255
(2)varchar(M)为变长类型,eg:varchar(5)当我们在存储"abc"时,则varchar中只存abc,不加空格。范围:0~65535

(3)1Byte == 8bit,11111111 == 2E8 == 255.

(4)enum('value1','value2',·····),只能从给定的enum枚举中选择
(5)SET('value1','value2','value3'....)就相当于一个集合,在里面做排列组合,可以多选里面的值。

现在插入一行数据

3、查看数据表

语法结构如下:

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

示例如下:

澳门新萄京官方网站 16

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

澳门新萄京官方网站 17


二、数据类型分类之整型

澳门新萄京官方网站 18

  • 无符号用UNSIGNED表示

二、数据表操作

数据表(或称表)是数据库最重要的组成部分之一,是其他对象的基础。

我们关系型数据库,其实就是一张二维表格,这个表格严谨点指的就是我们的数据表。这个二维表格,行称之为记录,列称之为字段。如果我们只创建了数据库,没有数据表,那么我们只有空的架子,并没有真正存储数据的地方,如果要存储数据,必然要设计表。

二、数据表操作

数据表(或称表)是数据库最重要的组成部分之一,是其他对象的基础。

我们关系型数据库,其实就是一张二维表格,这个表格严谨点指的就是我们的数据表。这个二维表格,行称之为记录,列称之为字段。如果我们只创建了数据库,没有数据表,那么我们只有空的架子,并没有真正存储数据的地方,如果要存储数据,必然要设计表。

澳门新萄京官方网站 19

4、查看数据表结构

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

SHOE COLUMNS FROM tbl_name

DESC tbl_name

SHOW CREATE TABLE tbl_name

示例如下:

澳门新萄京官方网站 20

SHOE COLUMNS FROM tbl_name

澳门新萄京官方网站 21

DESC tbl_name

澳门新萄京官方网站 22

SHOW CREATE TABLE tbl_name

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

澳门新萄京官方网站 23

SHOW CREATE TABLE tbl_nameG


2-3 MySQL数据类型之浮点型

下表中值得注意的是:

  1. FLOAT是单精度浮点型
  2. DOUBLE是双精度浮点型
  3. FLOAT[(M,D)],M一定大于等于D
  4. 比如FLOAT(7,2)代表小数点前面的数值有5位,小数点后面的数值有2位,一共加起来有7位,最大能表示99999.99
  5. 我们应该选择最合适而不是最大的数据类型

澳门新萄京官方网站 24

1、创建数据表(create table)

  可以通过USE命令打开某个数据库(USE 数据库名称);

  显示当前用户打开的数据库:SELECT BATABASE();

  创建数据表:CREATE TABLE [IF NOT EXISTS] table_name(
        column_name data_type,
        .........
        )

比如:

澳门新萄京官方网站 25

1、创建数据表(create table)

  可以通过USE命令打开某个数据库(USE 数据库名称);

  显示当前用户打开的数据库:SELECT BATABASE();

  创建数据表:CREATE TABLE [IF NOT EXISTS] table_name(
        column_name data_type,
        .........
        )

比如:

澳门新萄京官方网站 26

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

5、记录的插入与查找

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

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

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

澳门新萄京官方网站 27

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

澳门新萄京官方网站 28

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

澳门新萄京官方网站 29

注意:

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

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

澳门新萄京官方网站 30

此处的 * 是指字段的过滤


2-4 MySQL数据类型之日期时间型

  1. YEAR范围:1970 到2069
  2. TIME范围:-838:59:59 到 838:59:59
  3. DATE范围:1000-01-01 到 9999-12-31
  4. DATETIME范围:1000-01-01 00:00:00 到 9999-12-31 23:59:59
  5. TIMESTAMP范围:1970-01-01 00:00:00 到 2037-12-31 23:59:59

澳门新萄京官方网站 31

2、查看数据表(show tables)

澳门新萄京官方网站 32

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
SHOW TABLES; //查看当前数据库中的表
SHOW TABLES FROM mysql;//查看数据库mysql下的表
SELECT DATABASE();//查看当前数据库

注:1、查看数据表是否存在:SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr ]
2、FROM db_name 可以查看其它数据库中的表

2、查看数据表(show tables)

澳门新萄京官方网站 33

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
澳门新萄京官方网站数据类型与操作数据表,操作数据表。SHOW TABLES; //查看当前数据库中的表
SHOW TABLES FROM mysql;//查看数据库mysql下的表
SELECT DATABASE();//查看当前数据库

注:1、查看数据表是否存在:SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr ]
2、FROM db_name 可以查看其它数据库中的表

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

6、空值与非空

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

澳门新萄京官方网站 34

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

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

澳门新萄京官方网站 35

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

澳门新萄京官方网站 36

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

澳门新萄京官方网站 37

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

澳门新萄京官方网站 38


2-5 MySQL数据类型之字符型

  1. CHAR(M):这是定长类型,比如CHAR(5),值为ABC,但是存储的时候,会在ABC后面补齐两个空格补齐5位
  2. VARCHAR(M):这是变长类型,比如VARCHAR(5),值为ABC,存储的时候就只存ABC
  3. L x 个字节:是指最佳的存储范围
  4. ENUM:枚举值,给出选项供选择
  5. SET:集合,比如SET('A','B','C'),可以只选择A,也可以只选择B,也可以只选择BC,等于可以在这个集合中选择任意的排列组合

澳门新萄京官方网站 39

3、查看数据表结构(show columns)

澳门新萄京官方网站 40

3、查看数据表结构(show columns)

澳门新萄京官方网站 41

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

7、自动编号

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

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

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

澳门新萄京官方网站 42

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

澳门新萄京官方网站 43

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

澳门新萄京官方网站 44

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

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

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

澳门新萄京官方网站 45

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

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

澳门新萄京官方网站 46

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


2-6 MySQL创建数据表

4、记录的插入(insert)与查找(select)

澳门新萄京官方网站 47

澳门新萄京官方网站 48

4、记录的插入(insert)与查找(select)

澳门新萄京官方网站 49

澳门新萄京官方网站 50

期望:

8、唯一约束

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

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

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

澳门新萄京官方网站 51

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

澳门新萄京官方网站 52


一、数据表

  • 数据表又称表,是数据库最重要的组成部分之一,是其他对象的基础
  • 行称之为记录,列称之为字段

5、空值与非空值

 澳门新萄京官方网站 53

澳门新萄京官方网站 54

澳门新萄京官方网站 55

禁止为空的字段一定要赋值。

5、空值与非空值

 澳门新萄京官方网站 56

澳门新萄京官方网站 57

澳门新萄京官方网站 58

禁止为空的字段一定要赋值。

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

9、默认约束

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

澳门新萄京官方网站 59

ENUM即枚举值,详细可参见[数据类型]()

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

澳门新萄京官方网站 60

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


二、创建数据表

  1. 首先通过USE命令打开数据库
    USE 数据库名;
  2. 检查打开的数据库是否正确
    SELECT DATABASE();
  3. 创建数据表的语法结构
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,…
)
  • column_name是列名称,数据表有多少列需要根据项目分析事先规划好
  • data_type是数据类型, 逗号是两个字段之间的分隔符,最后一个字段不用加逗号

6、自动编号(auto_increment)

澳门新萄京官方网站 61

1、自动编号:保证记录的唯一性
2、类型必须为整型(可以是FLOAT(5,0)等,小数点后必须为0),必须和主键PRIMARY KEY组合使用
3、默认情况下,起始值为1,每次的增量为1
默认情况下,起始值为1, 每次增量为1.
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(30) NOT NULL); //报错,自动增量字段必须设置成主键

6、自动编号(auto_increment)

澳门新萄京官方网站 62

1、自动编号:保证记录的唯一性
2、类型必须为整型(可以是FLOAT(5,0)等,小数点后必须为0),必须和主键PRIMARY KEY组合使用
3、默认情况下,起始值为1,每次的增量为1
默认情况下,起始值为1, 每次增量为1.
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(30) NOT NULL); //报错,自动增量字段必须设置成主键

设计

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;


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

三、举例

  • 我们创建一个简单的数据表,包含以下几项信息:
  • 用户名:username ,用户名一般是字符型
  • 年龄:age ,年龄一般是整型,另外年龄不可能是负值,所以常设置无符号位
  • 工资:salary,工资一般设置为浮点型
mysql> CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );

7、初涉主键约束(primary key)

澳门新萄京官方网站 63

auto_increment(自动编号)字段必须定义为primary key主键,但主键不一定与auto_increment一起使用。

主键自动为NOT NULL,也就是说必须要为主键赋值。但如果主键选择了AUTO_INCREMENT,那么不需要手动赋值。
auto_increment必须和primary key一起使用,反之则没有这个必要。primary key不允许重复赋值。 对于给了auto_increment属性的字段名,也会给一个unsigned的限制,避免它为负数 自动编号的记录会从1依次递增 1 定义主键时,可以使用key 也可以使用primary key 使用主键约束的字段 自动会变成 not null约束 定义了主键的字段名的列值不允许相同.

7、初涉主键约束(primary key)

澳门新萄京官方网站 64

auto_increment(自动编号)字段必须定义为primary key主键,但主键不一定与auto_increment一起使用。

主键自动为NOT NULL,也就是说必须要为主键赋值。但如果主键选择了AUTO_INCREMENT,那么不需要手动赋值。
auto_increment必须和primary key一起使用,反之则没有这个必要。primary key不允许重复赋值。 对于给了auto_increment属性的字段名,也会给一个unsigned的限制,避免它为负数 自动编号的记录会从1依次递增 1 定义主键时,可以使用key 也可以使用primary key 使用主键约束的字段 自动会变成 not null约束 定义了主键的字段名的列值不允许相同.

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

2-7 MySQL查看数据表

8、初涉唯一约束(unique key)

澳门新萄京官方网站 65

它与主键的区别:主键是一张数据表只能有一个,不能为空,但是唯一约束可以存在多个,而且可以存在空值。

注意:唯一约束,可以有空值,但只能有一个空值。

8、初涉唯一约束(unique key)

澳门新萄京官方网站 66

它与主键的区别:主键是一张数据表只能有一个,不能为空,但是唯一约束可以存在多个,而且可以存在空值。

注意:唯一约束,可以有空值,但只能有一个空值。

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

一、查看数据表列表

  • 查看数据表列表语法结构:
SHOW TABLES [FROM db_name]
[LIKE 'pattern' | WHERE expr]
  • 比如查看当前数据库的数据表列表:
    SHOW TABLES;

  • 不仅可以查看当前数据库下的数据表列表,还可以查看其它数据库的数据表列表,比如查看mysql数据库中所有的数据表列表:
    SHOW TABLES FROM mysql;

9、初涉默认约束(default)

澳门新萄京官方网站 67

9、初涉默认约束(default)

澳门新萄京官方网站 68

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

2-8 MySQL查看数据表结构

查看数据表结构
SHOW COLUMNS FROM tbl_name;

比如查看名为tb1这个数据表的结构
SHOW COLUMNS FROM tb1;
将会显示2-6节中创建的tb1的结构

值得注意的是,我们需要正确的区分查看数据表和查看数据表结构
下面分别是查看数据表结构和数据表显示的内容:

mysql> SHOW COLUMNS FROM tb1;
 ---------- --------------------- ------ ----- --------- ------- 
| Field    | Type                | Null | Key | Default | Extra |
 ---------- --------------------- ------ ----- --------- ------- 
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
 ---------- --------------------- ------ ----- --------- ------- 
3 rows in set (0.01 sec)

mysql> SHOW TABLES;
 ---------------- 
| Tables_in_test |
 ---------------- 
| tb1            |
 ---------------- 
1 row in set (0.00 sec)

三、总结

澳门新萄京官方网站 69

进入指定数据库 use db_name;
创建数据库表 create table [if not exists] table_name(columns_name type,columns_name type ..);
查看数据库表 show tables;(show tables from db_name like table_name)
查看数据表结构 show columns from table_name;
插入记录 insert [into] table_name [(column_name,...)]values (val,...);
查找记录 select exp,.. from table_name;
自动编号 auto_increment;auto_increment必须和primary key一起使用
唯一约束 unique key
默认约束 default

三、总结

澳门新萄京官方网站 70

进入指定数据库 use db_name;
创建数据库表 create table [if not exists] table_name(columns_name type,columns_name type ..);
查看数据库表 show tables;(show tables from db_name like table_name)
查看数据表结构 show columns from table_name;
插入记录 insert [into] table_name [(column_name,...)]values (val,...);
查找记录 select exp,.. from table_name;
自动编号 auto_increment;auto_increment必须和primary key一起使用
唯一约束 unique key
默认约束 default

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

2-9 MySQL记录的插入与查找

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

一、插入记录

插入记录语法结构
INSERT [INTO] tbl_name[(col_name,...)] VALUES(val,...);
tbl_name:表名字。 col_name:列名字。 VALUES:值

如果省略列名,需要对所有的字段都赋值
INSERT tb1 VALUES('Tom',25,7863.25);

也可以只给部分字段赋值,比如
INSERT tb1(username,salary) VALUES('John',4500.69);

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

二、记录查找

记录查找语句
SELECT expr,... FROM tbl_name;
比如:(这里的*是字段的过滤)
SELECT * FROM tb1;
显示如下:

mysql> SELECT * FROM tb1;
 ---------- ------ --------- 
| username | age  | salary  |
 ---------- ------ --------- 
| Tom      |   25 | 7863.25 |
| John     | NULL | 4500.69 |
 ---------- ------ --------- 
2 rows in set (0.00 sec)

澳门新萄京官方网站 71

2-10 MySQL空值与非空

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

一、空值与非空

  • NULL,字段值可以为空
  • NOT NULL,字段值禁止为空

我们首先创建一个表:

mysql> CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );

然后查看这个字段的结构:

mysql> SHOW COLUMNS FROM tb2;
 ---------- --------------------- ------ ----- --------- ------- 
| Field    | Type                | Null | Key | Default | Extra |
 ---------- --------------------- ------ ----- --------- ------- 
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
 ---------- --------------------- ------ ----- --------- ------- 

可以看到username的NULL是NO,是不为空的,假如我们设置username为NULL:

mysql> INSERT tb2 VALUES(NULL,26);
ERROR 1048 (23000): Column 'username' cannot be null

可以看到报错提示不允许username为NULL

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

2-11 MySQL自动编号

AUTO_INCREMENT

  • 自动编号,且必须与主键组合使用
  • 默认情况下,起始值为1,每次的增量为1,依次递增。

所以该字段数据类型一定是数值型:整数,浮点数都可以,浮点数小数位数必须为0
举例:

mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

可以看到报错了,这是因为自动编号字段必须设置成主键才可以,关于主键的介绍请看下节

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

2-12 MySQL初涉主键约束

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

一、PRIMARY KEY

  • 主键约束

  • 每张数据表只能存在一个主键

  • 关于联合主键和复合主键的解释,请查看复合主键与联合主键

  • 主键保证记录的唯一性

  • 主键自动为NOT NULL

  • AUTO_INCREMENT必须和主键一起使用,但是主键不一定必须和AUTO_INCREMENT使用

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

二、设置主键

我们将上节中的id定义成主键

mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

可以看到,创建成功了,主键可以写PRIMARY KEY,也可以直接写KEY

现在再来看一下数据表结构:

mysql> SHOW COLUMNS FROM tb3;
 ---------- ---------------------- ------ ----- --------- ---------------- 
| Field    | Type                 | Null | Key | Default | Extra          |
 ---------- ---------------------- ------ ----- --------- ---------------- 
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | NO   |     | NULL    |                |
 ---------- ---------------------- ------ ----- --------- ---------------- 
2 rows in set (0.01 sec)

可以看到,id不能为空,因为主键自动为NOT NULL,Key被定义成了PRI,Extra被定义成了自动编号,则写入记录的时候id不需要专门赋值了,我们插入三条记录:

mysql> INSERT tb3(username) VALUES('Tom');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT tb3(username) VALUES('John');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb3(username) VALUES('Zoro');
Query OK, 1 row affected (0.00 sec)

然后查看一下记录:

mysql> SElECT * FROM tb3;
 ---- ---------- 
| id | username |
 ---- ---------- 
|  1 | Tom      |
|  2 | John     |
|  3 | Zoro     |
 ---- ---------- 
3 rows in set (0.00 sec)

可以看到id被自动编号成了1、2、3

我们创建另外一张表,这张表只有主键,没有AUTO_INCREMENT

mysql> CREATE TABLE tb4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

现在再来看一下数据表结构:

mysql> SHOW COLUMNS FROM tb4;
 ---------- ---------------------- ------ ----- --------- ------- 
| Field    | Type                 | Null | Key | Default | Extra |
 ---------- ---------------------- ------ ----- --------- ------- 
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | NO   |     | NULL    |       |
 ---------- ---------------------- ------ ----- --------- ------- 
2 rows in set (0.00 sec)

可以看到,id不能为空,因为主键自动为NOT NULL,Key被定义成了PRI,并且不能相同,我们插入三条记录:

mysql> INSERT tb4 VALUES(4,'Tom');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT tb4 VALUES(22,'John');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT tb4 VALUES(4,'John');
ERROR 1062(23000):Duplicate entry '4' for key 'PRIMARY'

第三次赋值时报错了,可以看出主键不能赋值相同

然后查看一下记录:

mysql> SElECT * FROM tb4;
 ---- ---------- 
| id | username |
 ---- ---------- 
|  4 | Tom      |
| 22 | John     |
 ---- ---------- 
2 rows in set (0.00 sec)
  • 由上可以说明主键的字段是可以赋值的,另外主键不能赋值相同
--创建一个存储过程用于自动提取自增长字段的第一个缺失值和缺省值
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

2-13 MySQL初涉唯一约束

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

一、前言

因为主键一张数据表只能有一个,如果想保证唯一性,可以使用UNIQUE KEY(唯一约束)

declare @temp int --定义输出参数
exec dbo.NumOfDeptNOForInsert @temp  --调用储存过程
print @temp  --打印输出参数

二、UNIQUE KEY

  • 唯一约束
  • 唯一约束可以保证记录的唯一性
  • 唯一约束的字段可以为空值(NULL)(这里比较难以理解:因为既然可以为空值,那么2条或者2条以上的记录此字段可以都为空值,都为空值就是相同的,岂不是违背了唯一性?其实这里存储的时候,多个空值只会保存一个空值,所以并不违背唯一性)
  • 每张数据表可以存在多个唯一约束
  •  缺失值的检验:

三、创建唯一约束

我们创建一个既有主键约束,又有唯一约束的数据表:

mysql> CREATE TABLE tb5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

查看一下数据表的结构:

mysql> SHOW COLUMNS FROM tb5;
 ---------- ---------------------- ------ ----- --------- ---------------- 
| Field    | Type                 | Null | Key | Default | Extra          |
 ---------- ---------------------- ------ ----- --------- ---------------- 
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
 ---------- ---------------------- ------ ----- --------- ---------------- 
3 rows in set (0.01 sec)

可以看到,id为主键约束,username为唯一约束,我们写入一些记录:

mysql> INSERT tb5(username,age) VALUES('TOM',22);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT tb5(username,age) VALUES('TOM',22);
ERROR 1062 (23000): Duplicate entry 'TOM' for key 'username'

可以发现username不能重复

澳门新萄京官方网站 72             澳门新萄京官方网站 73

2-14 MySQL初涉默认约束

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

一、DEFAULT

  • 默认约束
  • 当插入记录时,如果没有明确为字段赋值,则自动赋予默认值
  • 缺省值的检验:

二、DEFAULT举例

我们创建一个数据表:

mysql> CREATE TABLE tb6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );

查看一下数据表结构:

mysql> SHOW COLUMNS FROM tb6;
 ---------- ---------------------- ------ ----- --------- ---------------- 
| Field    | Type                 | Null | Key | Default | Extra          |
 ---------- ---------------------- ------ ----- --------- ---------------- 
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
 ---------- ---------------------- ------ ----- --------- ---------------- 
3 rows in set (0.01 sec)

可以看到sex字段的默认值为3

我们插入一条记录,仅设置username,不设置sex
mysql> INSERT tb6(username) VALUES('Tom');

然后查看一下记录:

mysql> SELECT * FROM tb6;
 ---- ---------- ------ 
| id | username | sex  |
 ---- ---------- ------ 
|  1 | Tom      | 3    |
 ---- ---------- ------ 
1 row in set (0.00 sec)

发现即使我们没有设置Tom的性别,但是sex默认被赋值成了3

澳门新萄京官方网站 74            澳门新萄京官方网站 75

调用存储过程,找到的第一个缺省值为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字段连续递增。

 澳门新萄京官方网站 76澳门新萄京官方网站 77

2.缺省值:

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

 澳门新萄京官方网站 78澳门新萄京官方网站 79

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

一.总结

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

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站数据类型与操作数据表,操

关键词: