CH02_数据表

本章目标

  • 使用SSMS创建数据库表
  • 使用SSMS完善表结构
  • 掌握临时表的创建和使用

数据完整性

域完整性

  1. 作用：限制此单元格是符合要求的数据
  2. 域完整性约束：
    1. 数据类型
    2. 非空约束 (not null)
    3. 默认值约束(default)
    4. check约束

实体完整性

  1. 实体：表中的唯一一行，代表一个实体，
  2. 实体完整性的作用是标识每一行数据都不重复
  3. 约束类型主要包括：
    主键约束：primary key
    唯一约束：unique
    自动增重列：auto_increment

引用完整性

  1. 外键列：是用来保存别的表中主键值列
  2. 外键约束：foreign key
  3. 作用：保证表与表之间引用的完整性，被定义为外键的列属性用于引用另一个表里的主键列属性

数据类型和约束

常用数据类型

  1. 文本类型

    这个是数据库中用的最多的，包括任意字母、符号或数字、字符的组合，主要有下面的六种：

    （1）char: 固定长度的非Unicode字符数据，最大长度为8000个字符。 char(2)

    （2）varchar: 可变长度的非Unicode数据，最大长度为8000个字符。 varchar(20)

    （3）text：存储长文本信息，最大长度为231-1（2147483647）个字符。

    （4）nchar：固定长度的Unicode数据，最大长度为4000个字符。

    **（5）nvarchar:**可变长度的Unicode数据，最大长度为4000个字符。

    （6）ntext：存储可变长度的长文本， 230-1（1073741823）个字符。

    Unicode是国际组织制定的可以容纳世界上所有文字和符号的字符编码方案。

    注意：带n的数据类型长度是不带n的两倍。比如nchar(1)和char(2)长度相同

  2. 整数类型

    整数类型，通常包括下面4个：

    （1）bigint: 占用8个字节，可表示范围：-263～263-1之间的整数。

    （2）int: 占用4个字节，可表示范围：-2³¹～2³¹-1之间的整数。

    （3）smallint: 占用2个字节，可表示范围： -215～215-1之间的整数。

    （4）tinyint: 占用1个字节，可表示范围：0 ～255之间的整数。

  3. 精确数字类型

    （1）decimal： -10³8～10³8-1之间的固定精度和小数位的数字。

    （2）numeric: 功能等同于decimal

    写法：decimal（整数，小数）和numeric（整数，小数）

    默认：如果不指定位数，默认18位整数，0位小数

  4. 浮点类型

    （1）float[(n)] 表示范围：-1.79E+308 ～ 1.79E+308（1.79乘以10的308次幂）

    n表示精度，在1-53之间取值，当n在1-24之间时，精度为7位有效数字，占用4个字节；当n在25-53之间时，精度为15位有效数字，占用8个字节。

    （2）real表示范围：-3.40E+38 ～3.40E+38占用4个字节存储空间，相当于float(24)。

  5. 日期类型

    （1）datetime：允许的范围1753-1-1至9999-1-1, datetime精确到3/100秒。

    （2）smalldatetime：允许的范围1900-1-1至2079-6-6，smalldatetime精确到1分钟。

    格式说明：分隔符数字方式：2013-08-20或08/20/2013；纯数字方式：08202013； 英文数字方式：Aug 20,2013；注意问题：日期在使用的时候需要使用单引号（‘’）括起来。

  6. 货币类型

    （1）money：货币数值介于-263与263-1之间，精确到货币单位的千分之一。

    （2） smallmoney: 货币数据介于-214748.3648与-214748.3648之间，精确到货币单位的千分之十。

  7. 位类型

    bit：表示“是/否”类型的数据。（用0，1 / true、false）。

  8. 二进制类型

    （1）binary：固定长度的二进制数据，最大长度为8000个字节。

    （2）vbinary：可变长度的二进制数据，其最大长度为8000个字节。

    （3）image：可变长度的二进制数据，其最大长度为231个字节。

    应用场合：可存储图片。

数据约束

我们知道了数据类型后，还不能马上创建数据表，就像我们盖房子一样，有了砖石瓦块不能马上施工，还得要知道怎么设计的，具体有什么要求，这样才能盖出好房子。

为什么要使用约束呢？约束就是对表中字段值是否符合数据规范的一种强制性检查。通过各种检查保证用户输入和修改的数据的正确性、统一性。数据的约束通常有下面的几种：

  1. 类型约束

    这种约束是直接通过数据类型限制的，比如考试成绩要求是整数，当我们设置一个表的字段值是整数时，你输入其他的字符串就无法输入；同理，如果约束的是时间类型，你输入整形也是不可以的。

  2. 检查约束

    比如，我们定义了年龄字段，类型是整形，那用户是不是可以写200呢？对应数值类型的肯定是没问题。但是200是不符合实际的要求，这时候我们就可以通过检查约束人的年龄是1-100或其他的范围。再比如身份证我们要求是18位，可以设置一个检查约束，要求输入的位数必须是18位，否则就报错。检查约束的形式还是很多的，大家慢慢积累。

  3. 非空约束

    顾名思义就是这个字段不能为空，插入数据的时候必须要有数据。

  4. 主键约束

    一个数据表必须要有一个主键列。比如在部门表中，我们要把DepartmentId设置为主键，首先，主键强制非空，其次能够保证不能重复，最后数据在物理存储上是遵循主键的索引。

  5. 外键约束

    先举一个例子，比如我们去银行办理银行卡，必须要保存你的身份证号，如果身份证不正确是无法办理的，因为银行的系统中使用的身份证ID是参考了国家相关数据库系统中身份证的ID，这个就是一个典型的“外键约束”。也就是银行用的身份证ID必须来自与国家系统中的身份证ID，这样就能保证你的身份证ID在任何地方使用的时候都是一样的。如果我们把国家系统身份证信息表作为主键表的话，银行卡的身份证ID就是外键表，外键约束就是这么来的。但是我们这里说的外键约束，一般都是在一个数据库内，不同表之间的关联约束。

    再比如在前面展示过的员工数据表中的DepartmentId和PostId这两个列的值就必须是来自前面主键表Department和Post中对应的字段值。如果在员工表中插入的外键值在主键表中没有，就会出现外键约束错误。外键约束在关系型数据库中非常重要。

创建数据库表

数据类型

主键

主键（Primary Key）由表中的一个或者多个字段构成，用来唯一标识表中的每一行记录。可以将主键类比为学号、身份证号、车牌号或者 ID。

主键必须包含唯一值，换句话说，所有记录的主键都不能出现相同的值。此外，主键必须是一个具体的值，不能是 NULL 值。

当主键包含多个字段时，又称为复合键（Composite Primary Key）

在设计主键时，应使用尽可能少的字段，这不但能减少存储空间，还能提升查询性能。主键包含的字段越少，所需要的存储空间就越小，就性能而言，更少的数据意味着更快速地处理。

NULL/NOT NULL

在 SQL 中，关键字 NULL 用来表示缺失的值，也即空值，或者没有值。NULL 值不等同于零值，也不等同于包含空格的字段，理解这一点非常重要。

表的字段默认允许存放 NULL 值，这意味着，您在插入记录或者更新记录时，可以不为该字段指定值，此时该字段将存储 NULL 值。

默认情况下，表的字段可以包含 NULL 值，如果您不希望某个字段出现 NULL 值，那么可以在该字段上添加 NOT NULL 约束（非空约束），此时就必须给该字段指定一个具体的值，不能留空。

注意，NULL 不等于没有数据，而是表示数据是未知的。

默认值

SQL DEFAULT 约束用于给字段指定一个默认值，当使用 INSERT INTO 语句向表中插入数据时，如果没有为该字段提供具体的值，那么就使用这个默认值。

自动标识列

MySQL 中使用序列的最简单方法是为某一列添加 AUTO_INCREMENT 约束。AUTO_INCREMENT 会在新记录插入表中时生成一个唯一的整数，这些整数是自动增长的，用户还可以指定增长的步长（默认为 1）。

注意事项：

  • 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，并且该字段的类型必须是整数，习惯上将主键设置为 AUTO_INCREMENT。
  • 在插入数据或者更新数据时，一般将 AUTO_INCREMENT 字段留空，因为数据库引擎会自动管理它的值。

在插入或者更新记录时，如果为 AUTO_INCREMENT 字段明确地指定了一个值，则会出现两种情况：

  • 如果指定的值和已有的编号重复，则出现错误信息，操作失败，因为 AUTO_INCREMENT 字段的值必须是唯一的。
  • 如果指定的值大于已有的编号，则操作成功，该值被插入/更新到表中，下一个编号将从这个新值开始递增；也就是说，AUTO_INCREMENT 并不要求编号是连续的，可以跳过一些编号。

建立外键引用关系

SQL 外键（Foreign Key）用于将两个表连接在一起，让两个表的数据保持同步。

外键由表中的一个字段或者多个字段构成，一个表的外键用来指向另一个表的主键（Primary Key）。包含外键的表称为从表，被指向的表称为主表。从表的数据受到主表的约束，向从表中插入或者更新数据时，外键的值必须存在于主表的主键中。

建立检查约束

约束（Constraint）是指表的数据列必须强行遵守的规则，这些规则用于限制插入表中的数据类型，这样能够确保每份数据的准确定和可靠性。

约束可以是列级别，也可以是表级别；列级约束仅作用于某一列，而表级约束则作用于整张表。

下面是 SQL 常用的一些约束：

建表

如下图所示，建立4张表：

课后作业

1.建库：BookManager,包含以下表：

(1)图书表

书号：统一字符编码定长类型，长度为6，主键。

书名：统一字符编码可变长类型，长度为30，非空。

作者：普通编码定长字符串类型，长度为10,非空。

出版日期：小日期时间类型

价格：定点小数，小数部分为1位，整数部分为3位。

(2)书店表

书店编号：统一字符编码定长类型，长度为6，主键。

店名：统一字符编码可变长类型，长度为30，非空。

电话：普通编码定长字符型，长度为8。

地址：普通编码可变长字符型，长度为40。

邮政编码：普通编码定长字符类型，长度为6。

(3)图书销售表

书号：统一字符编码定长类型，长度为6，非空。

书店编号：统一字符编码长度类型，长度为6，非空。

销售日期：小日期时间类型，非空。

销售数量：小整型，大于等于1.

主键为(书号、书店编号，销售日期)