SQL4-表结构与数据类型

1、福建师范大学信息技术学院表结构与数据类型数据类型1约束2表结构设计3福建师范大学信息技术学院数据类型v关系的所有属性都需要用数据类型加以描述，目的是为了给不同的数据分配合适的空间，确定合适的存储形式。v SQL Server 2008提供了丰富的系统数据类型，包含字符串、Unicode字符串、二进制数据、位、整数数据、浮点数、日期和时间数据、货币数据等。福建师范大学信息技术学院数据类型v系统数据类型福建师范大学信息技术学院数据类型SQL Server 2008的整数数据类型数据类型范围存储bigint-263263-18字节int-231231-14字节smallint-215215-12字节

2、tinyint02551字节bit0或11位v精确数字类型 整数数据类型福建师范大学信息技术学院数据类型v精确数字类型 精确的小数数据类型SQL Server 2008的小数数据类型数据类型说明decimal(p,s)p（精度），总位数，138，默认为18；s（小数位数），0p，默认为0；0spnumeric(p,s)福建师范大学信息技术学院数据类型v精确数字类型 货币数据类型数据类型范围存储money-263263-18字节，精确到货币单位的1%smallmoney-215215-14字节，精确到货币单位的1%福建师范大学信息技术学院数据类型v精确数字类型 近似数据类型数据类型范围存储flo

3、at(n)-1.79E+308+-1.79E+308取决于n，当n取值为124之间，存储空间为4字节；当n取值在2553之间，存储空间为8字节real-3.40E+383.40E+384字节，相当于float(24)福建师范大学信息技术学院数据类型v精确数字类型 日期和时间数据类型数据类型范围存储date从0001-1-1到9999-12-31格式：yyyy-mm-dd3字节datetime从1753-1-1到9999-12-31格式：yyyy-mm-dd hh:mm:ss8字节smarlldatetime从1900-1-1到2079-12-31格式：yyyy-mm-dd hh:mm:ss4字

4、节福建师范大学信息技术学院数据类型v精确数字类型 字符数据类型数据类型说明char(n)定长字符串，每个字符占用1字节存储空间，存储空间为n字节，n介于08000之间整数varchar(n)最大长度为n的可变长字符串，存储空间根据实际字符串所占字节数text专门用于存储数量庞大的变长字符数据，最大存储空间231-1字节福建师范大学信息技术学院数据类型v精确数字类型 unicode字符数据类型数据类型说明nchar(n)存储双字节字符，存储空间为字符数*2（字节）；含义同字符数据类型nvarchar(n)ntext福建师范大学信息技术学院数据类型v精确数字类型 二进制字符数据类型数据类型说明bi

5、nary固定长度为n字节的二进制字符串，n介于08000之间整数varbinary最大长度为n字节的可变长二进制字符串image用于存储超过8000字节的数据，所占空间为0231-1福建师范大学信息技术学院课堂演练_数据类型v为以下信息，定制正确的数据类型 电话号码一般使用什么数据类型存储? 年龄信息一般使用什么数据类型存储? 薪水一般使用什么数据类型存储? 地址一般用什么数据类型？ 生日一般用什么数据类型？ 身高用什么数据类型？ 图片用什么类型存储？定长字符型 char(n)整型 int货币型 money可变长 varchar(n)日期型 datetime小数类型 numeric(5,1)图

6、片存储有两种方式路径存储：选用varchar(n)图片存储：选用image（二进制存储）福建师范大学信息技术学院数据类型v用户自定义数据类型 用户自定义数据类型是在SQL Server系统数据类型基础上定义的，定义时需要指定该类型的名称、建立在其上的系统数据类型以及是否允许为空值(NULL)等。自定义数据类型定义以后，使用方式同系统数据类型。 系统存储过程sp_addtype可用来创建用户自定义数据类型。其语法格式如下：sp_addtype新数据类型,系统数据类型 , null_type福建师范大学信息技术学院数据类型例2-6 在学籍管理数据库SM中创建用户自定义数据类型studentNo，s

7、tudentNo基于的系统数据类型是固定长度字符数据类型，长度为5，不允许为空。 在Microsoft SQL Server Management Studio中执行以下代码： 运行结果如下图所示。福建师范大学信息技术学院约束v在设计表时，对于表和表中的某些列要进行完整性约束条件定义，以实现实体完整性、参照完整性和用户定义的完整性。v SQL Server提供了一些在列上强制完成数据完整性的约束机制，包括非空值约束（NOT NULL）、唯一性约束（UNIQUE）、主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）、检查约束（CHECK）和默认值约束（DEFAULT）。福建师

8、范大学信息技术学院约束v （1）非空约束（NOT NULL） 空值（NULL）是当前不知道、不确定或无法填入的值。空值不能理解为0或空格。例如，某个学生选修某门课程的成绩是0和NULL具有不同的含义。成绩为0表示该学生该课程已经有了成绩，而为NULL则表明该学生此门课程的成绩还没有被填入。这是两个不同的概念。 当某列的取值不能为空值时，则可以在列级完整性约束定义中设置为非空值约束(NOT NULL)。其定义格式为：CONSTRAINT NOT NULLv 注意：NOT NULL约束只能定义在列级完整性约束，不允许作为一个表级完整性约束来使用。福建师范大学信息技术学院约束v （2）唯一性约束（U

9、NIQUE） 唯一性约束（UNIQUE）用于限定基本表上的某个列或某些列的组合在不同元组中的取值不能相同（空值除外）。UNIQUE约束既可以作为列级约束，也可以作为表级约束。 UNIQUE约束定义格式为：CONSTRAINT UNIQUE 使用UNIQUE约束时，要注意以下几点。 如果没有为它指定聚簇索引或非聚簇索引，默认情况下会创建一个非聚簇索引。 每个UNIQUE约束自动生成一个索引，UNIQUE约束的数量不能导致表中索引的数量超过249个非聚簇索引和1个聚簇索引。 可以在列级或表级定义多个UNIQUE约束。当用作列级约束时，UNIQUE只作用于其所在的列，无须指定列名表。当用作表级约束时

10、，可以约束多个列的组合。福建师范大学信息技术学院约束v （3）主键约束（PRIMARY KEY） 用于定义基本表的主键，以实现实体完整性规则。如果某个列或列组合被定义为主键，那么该列或列组合唯一地标识表中的一个元组。其定义格式为：CONSTRAINT PRIMARY KEY 注意：使用PRIMARY KEY约束时，有以下几点要求。 每一个表仅能定义一个PRIMARY KEY约束。 主键值不可为空。 主键值不可重复。如果PRIMARY KEY约束建立在多列上，其中某一列上的数据可以重复，但是多列的值不能重复。 image和text类型的列不能定义PRIMARY KEY约束。 同唯一性约束一样，作

11、为列级约束时可以省略列名表。福建师范大学信息技术学院约束v（4）外键约束（FOREIGN KEY） 主要用来维护两个表之间数据的一致性，它是保证系统参照完整性的手段。外键的取值，要么为空值，要么是引用表的某个主键或唯一性键的值。外键约束既可以作为列级约束，也可以作为表级约束。其定义格式为：CONSTRAINT FOREIGN KEY REFERENCES ON DELETE ON UPDATE 福建师范大学信息技术学院约束v（5）检查约束（CHECK） 可以定义插入或修改某个元组时，元组应满足的约束条件，通常用于限定某个列的取值范围或与其他列的关系。其定义格式为：CONSTRAINT CHEC

12、K 条件表达式是由列名、SQL所支持的运算符和函数等构成的逻辑表达式。CHECK约束既可以作为列级约束，也可以作为表级约束。作为列级约束，每一列只能有一个CHECK约束，但可以用逻辑运算符AND（与）或OR（或)等构成复合条件。福建师范大学信息技术学院约束v（5）检查约束（CHECK）1、约束某列的取值区间（between.and ） check(a between. and .)2、约束某列的取值情况（or/in） check(a=. Or a=.) 或者 check(a in(.,.)3、约束某列的取值形式(like) check(a like 0-90-90-90-9)4、约束某字符列的

13、长度 例如：check(len(a)=18)通通 配配 符符 含含 义义 % % 可以匹配任意类型和长度的字符可以匹配任意类型和长度的字符 _ _任意单个字符任意单个字符 指定一定范围内的任意单个字符指定一定范围内的任意单个字符 不在指定范围内的任意单个字符不在指定范围内的任意单个字符 福建师范大学信息技术学院约束v（6） DEFAULT默认值约束 作为列级约束，使用DEFAULT可以为某一列指定默认值，当用户插入或修改元组时，在没有为该列赋值的情况下，可以用指定的默认值填入该列。其定义格式为：CONSTRAINT DEFAULT 注意： 每个列只能定义一个DEFAULT约束。 如果定义的默认

14、值大于其允许的长度，那么输入到列的默认值将被截断。 DEFAULT约束表达式不能参照表中的其他列、其他表、视图或存储过程。福建师范大学信息技术学院表结构设计v 设计表的实质就是设计表的结构，设置表和列的属性。创建表之前，应该考虑一下两个问题：一是数据库中要存放哪些数据；二是这些数据如何规划分到表中。即先要确定表的名字、属性，同时确定表所包含的列名、列的数据类型、长度、是否为空值、约束条件、默认值设置、规则等，这些属性构成表的结构。福建师范大学信息技术学院表结构设计福建师范大学信息技术学院表的创建基本表的创建，用CREATE TABLE语句实现，其语法格式如下： CREATE TABLE （ 列

15、级完整性约束条件 , 列级完整性约束条件 ,）表结构设计好，就可以在数据库中创建表。通常一个数据库还能有多张表，以存储用户需要的各类数据。方法一：使用SSMS对象资源管理器创建方法二：使用SQL语句创建福建师范大学信息技术学院表的创建v例1：用CREATE TABLE语句创建studb数据库中的学生表Student，记录学生的基本信息，要求姓名不能为NULL，并约束性别的取值范围。福建师范大学信息技术学院表的创建v例2：用CREATE TABLE语句创建studb数据库中的Course表，记录课程的基本信息，要求课程号为主键，课程名唯一，每门课的学分默认为4。福建师范大学信息技术学院表的创建v

16、 例3 用CREATE TABLE语句创建SC表，记录学生选课的基本信息，并设置外键约束。福建师范大学信息技术学院表的修改和删除v表建立以后，可以根据需要对它的结构进行修改。修改的内容包括修改列属性，如列名、数据类型、数据长度等，还可以在表结构中添加或删除列、添加或删除约束、禁用或启用约束等。基本表的修改可以使用ALTER TABLE语句福建师范大学信息技术学院表的修改和删除例4 用SQL向表Student中增加一列Email。ALTER TABLE Student ADD Email VARCHAR(40)例5 在Student表中SName列上设置唯一约束。ALTER TABLE StudentADD