项目 3 创建和管理表 表是数据库中最重要的基础对象 它包含数据库中的所有数据 其他数据库对象 例如索引和视图等 都是依赖于表而存在的 若要使用数据库来存储和组织数据 首先就需要创建表 在本项目中将通过 15 个任务来创建和管理表 主要内容包括表的设计 理解 SQL Server 2008 中的数据类型 以及创建和修改表等 任务 1 表的设计 任务描述 Office Visio 3.1 3.1
项目 3 创建和管理表 任务实现 1 7 2 3 Office Visio 3.1 3.1 7 PK 3 FK FK1 3.1 相关知识 SQL Server 一 制订表规划 1. 表要存储什么对象 2. 表中每一列的数据类型和长度 059
数据库应用 (SQL Server 2008) 1 3. 表中哪些列允许空值 NULL NULL 0 0 NULL NOT NULL 4. 是否要使用以及在何处使用约束 默认值和规则 SQL Server 2008 1 CHECK CHECK CHECK CHECK 2 DEFAULT NULL DEFAULT 3 UNIQUE UNIQUE 5. 使用何种索引以及在何处使用索引 SQL Server 6. 哪些列是主键或外键 PK FK PRIMARY KEY FOREIGN KEY 1 PRIMARY KEY NULL 2 FOREIGN KEY Microsoft Office Visio 060
项目 3 创建和管理表 二 规范化逻辑设计 SQL Server INSERT UPDATE DELETE 4 1 ID ID 2 AdventureWorks Sales.SalesOrderHeader 3 4 061
数据库应用 (SQL Server 2008) AdventureWorks Production. Product 3 PhoneNumber1 PhoneNumber2 AdventureWorks Production.Product Purchasing.Vendor Purchasing.ProductVendor ID ID 任务 2 认识 SQL Server 数据类型 任务描述 在本任务中 首先创建一个名为 学生成绩 的数据库 然后在该数据库中创建 4 个用户定义数据类型 如图 3.2 所示 任务实现 3.2 一 使用 SQL Server Management Studio 创建用户定义数据类型操作要求 : 创建一个名为 学生成绩 的数据库, 然后使用 SQL Server Management Studio 在该数据库中创建一个名为 dep_num 的用户定义数据类型 ( 可用于定义 系别编号 列 ), 该数据类型基于系统数据类型 smallint, 不允许取空值 1 SQL Server Management Studio SQL Server 2 062
项目 3 创建和管理表 3 3.2 3.2 4 3.3 dbo 3.3 5 UDDT dep_num sysname 6 smallint timestamp 7 UDDT NULL 063
数据库应用 (SQL Server 2008) 9 UDDT 二 使用 CREATE TYPE 语句创建用户定义数据类型操作要求 : 使用 CREATE TYPE 语句创建以下用户定义数据类型 (1)class_num: 基于 char 类型, 长度为 4, 非空, 可用于定义班级编号列 (2)stu_num: 基于 char 类型, 长度为 6, 非空, 可用于定义学号列 (3)name: 基于 varchar 类型, 长度为 10, 非空, 可用于定义姓名列 1 SQL Server Management Studio SQL Server 2 USE ; GO CREATE TYPE class_num FROM char(4) NOT NULL; GO CREATE TYPE stu_num FROM char(6) NOT NULL; GO CREATE TYPE name FROM varchar(10) NOT NULL; GO 3 SQLQuery3-01.sql F5 SQL 相关知识 一 数据类型概述 SQL Server SQL Server 2008 4 二 数字数据类型 1. 整数类型 bigint int smallint tinyint bit bigint 2 63 9 223 372 036 854 775 808 2 63 1 9 223 372 036 854 775 807 8 064
项目 3 创建和管理表 bit 0 1 NULL 8 bit 1 9 16 bit 2 TRUE FALSE bit TRUE 1 FALSE 0 int 2 31 2 147 483 648 2 31 1 2 147 483 647 4 smallint 2 15 32 768 2 15 1 32 767 2 tinyint 0 255 1 2. 小数类型 float(n) real decimal(p, s) numeric(p, s) money smallmoney float(n) 1.79E+308 1.79E+308 n n 1 53 n 1 24 7 4 n 25 53 15 8 real float(24) 3.40E 38 3.40E 38 4 decimal(p, s) numeric(p, s) p 1 38 p precision s scale p s decimal(10, 5) 5 5 10 decimal p 18 0 decimal numeric p p 1 9 5 p 10 19 9 p 20 28 13 p 29 38 17 money smallmoney 4 decimal money smallmoney 3 money 2 63 922 337 203 685 477.5808 2 63 1 +922 337 203 685 477.5807 8 4 4 smallmoney 214 748.3648 +214 748.3647 4 三 字符串数据类型 SQL Server 2008 9 3 Unicode 065
数据库应用 (SQL Server 2008) 1. 普通字符串 char(n) varchar(n) text char(n) Unicode n n 1 8 000 n n 1 char char n n NULL NULL n varchar(n max) Unicode n n 1 8 000 max 2 31 1 2 0 n 1 varchar varchar varchar char char varchar char varchar 8 000 varchar(max) Unicode nchar nvarchar text Unicode 2 31 1 2 147 483 647 2 147 483 647 2 147 483 647 text 8KB Microsoft text varchar(max) 2.Unicode 字符串 Unicode nchar(n) nvarchar(n) ntext Unicode nchar(n) Unicode n n 1 4 000 n n 1 nchar n 2n n nvarchar(n max) Unicode n n 1 4 000 max 2 31 1 2 n 1 nvarchar nchar nvarchar sysname 066
项目 3 创建和管理表 nvarchar(128) sysname ntext Unicode 2 30 1 1 073 741 823 2 ntext nvarchar( max) 3. 二进制数据类型 binary(n) varbinary(n) image binary(n) n n 1 8 000 n binary(1) 0x00 0xFF binary(2) 0x0000 0xFFFF varbinary(n max) n n 1 8 000 max 2 31 1 +2 n 0 binary varbinary 00 image image 2 31 1 2 147 483 647 2GB binary varbinary 8 000 varbinary(max) image varbinary(max) 四 日期和时间数据类型 SQL Server 2008 date datetime datetime2 datetimeoffset smalldatetime time time date datetime2 datetimeoffset SQL time datetime2 datetimeoffset datetimeoffset 1.date 数据类型 date 1 1 9999 12 31 3 date YYYY-MM-DD YYYY 4 0001 9999 MM 01 12 DD 01 31 1900-01-01 2.datetime 数据类型 datetime 24 1753 1 1 9999 12 31 00:00:00 23:59:59.997 8 datetime YYYY-MM-DD hh:mm:ss[.n*] YYYY 4 1753 9999 MM 01 12 DD 01 31 hh 00 23 mm 00 59 ss 00 59 n* 0 3 067
数据库应用 (SQL Server 2008) 0 999 datetime 1900-01-01 00:00:00 SET DATEFORMAT 3.datetime2 数据类型 datetime2 24 datetime datetime2 0001-01-01 9999-12-31 1 1 9999 12 31 00:00:00 23:59:59.9999999 YYYY-MM-DD hh:mm:ss[.n*] YYYY 4 0001 9999 MM 01 12 DD 01 31 hh 00 23 mm 00 59 ss 00 59 n* 0 7 0 9999999 3 6 4 5 7 8 4.datetimeoffset 数据类型 datetimeoffset 24 1 1 9999 12 31 00:00:00 23:59:59.9999999 14:00 +14:00 10 1900-01-01 00:00:00 00:00 datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+ }hh:mm] YYYY 4 0001 9999 MM 01 12 DD 01 31 hh 00 23 mm 00 59 ss 00 59 n* 0 7 0 9999999 UTC hh 14 +14 mm 00 59 + UTC 14:00 +14:00 5.smalldatetime 数据类型 smalldatetime 24 :00 smalldatetime 1900 1 1 2079 6 6 00:00:00 23:59:59 YYYY-MM-DD hh:mm:ss YYYY 4 1900 2079 MM 01 12 DD 01 31 hh 00 23 mm 00 59 ss 00 59 4 6.time 数据类型 time 24 068
项目 3 创建和管理表 00:00:00.0000000 23:59:59.9999999 time hh:mm:ss[.nnnnnnn] hh 0 23 mm 0 59 ss 0 59 n* 0 7 0 9999999 五 其他数据类型 SQL Server 2008 cursor hierarchyid sql_variant table timestamp uniqueidetifier xml 1.cursor 数据类型 OUTPUT cursor Transact-SQL cursor CREATE TABLE cursor 2.hierarchyid 数据类型 hierarchyid hierarchyid hierarchyid hierarchyid hierarchyid 3.sql_variant 数据类型 SQL Server text ntext image timestamp sql_variant sql_variant sql_variant sql_variant 8 016 4.table 数据类型 5.timestamp 数据类型 timestamp 8 timestamp binary(8) varbinary(8) timestamp 0x000000000000025A timestamp timestamp timestamp @@DBTS 069
数据库应用 (SQL Server 2008) 6.uniqueidetifier 数据类型 uniqueidentifier 16 BA9B4EF2-8F8B-4B42-8D0A-E0994AABD7FC GUID Globally Unique Identifier uniqueidentifier NEWID GUID xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx GUDI x 16 0 9 a f uniqueidentifier 7.xml 数据类型 XML xml SQL Server XML XML XML xml XML xml 2GB 六 别名数据类型 SQL Server UDDT char class_number SQL Server 2005 SQL Server 2005 SQL Server ANSI NULL model SQL Server 2008 SQL Server Management Studio CREATE TYPE DROP TYPE CREATE TYPE CREATE TYPE [schema_name.]type_name { FROM base_type [(precision[,scale])] [NULL NOT NULL] }[;] schema_name type_name base_type 070
项目 3 创建和管理表 SQL Server base_type precision scale base_type decimal numeric precision scale NULL NOT NULL NULL DROP TYPE DROP TYPE [schema_name.]type_name[;] schema_name type_name 任务 3 创建表 任务描述 在本任务中 首先创建一个名为 学生成绩 的数据库 然后使用 CREATE TABLE 语句在这个数据库中创建以下两个表 如图 3.4 所示 1 系别 表 包含两个列 系别编号 列为 dep_num 类型 设为标识列 种子和增量均为 1 系别名称 列为 varchar 类型 长度为 50 不允许为空 2 班级 表 包含 3 个列 班级编号 列为 class_num 类型 系别编号 列为 dep_num 类型 专业名称 列为 varchar 类型 长度为 50 不允许为空 3.4 071
数据库应用 (SQL Server 2008) 任务实现 1 2 SQL USE ; GO CREATE TABLE ( dep_num IDENTITY(1, 1), varchar(50) NOT NULL ); CREATE TABLE ( class_num, dep_num, varchar(50) NOT NULL ); GO 3 SQLQuery3-02.sql F5 4 相关知识 SQL Server 2008 CREATE TABLE 一 使用表设计器创建表 1 2 3.5 3.5 3 Null 3.6 072
项目 3 创建和管理表 3.6 4 5 3 4 6 7 3.7 3.7 二 使用 CREATE TABLE 语句创建表 CREATE TABLE CREATE TABLE [database_name.[schema_name]. schema_name.]table_name ({<column_definition>}[,...n])[;] <column_definition>::= column_name data_type [NULL NOT NULL] [ ] [CONSTRAINT constraint_name]default constant_expression] [IDENTITY[(seed,increment)] [ROWGUIDCOL][<column_constraint>[...n]] database_name database_name database_name schema_name SQL Server 2005 SQL Server 2008 table_name 073
数据库应用 (SQL Server 2008) # 116 table_name 128 column_name column_name 1 128 timestamp column_name column_name timestamp timestamp 1 024 type_name NULL NOT NULL CONSTRAINT PRIMARY KEY NOT NULL UNIQUE FOREIGN KEY CHECK constraint_name DEFAULT DEFAULT timestamp IDENTITY constant_expression NULL IDENTITY PRIMARY KEY IDENTITY tinyint smallint int bigint decimal(p,0) numeric(p,0) DEFAULT seed increment (1,1) ROWGUIDCOL GUID uniqueidentifier ROWGUIDCOL ROWGUIDCOL uniqueidentifier ROWGUIDCOL <column_constraint> 任务 4 在表中添加列 任务描述 在本任务中 要求在 学生成绩 数据库中执行以下操作 1 创建一个名称为 课程 的表 在该表中定义以下两个列 课程编号 列的数据类型为 int 课程名称 为 varchar 类型 长度为 20 这两个列都不允许为空 2 创建 课程 表后向该表中添加以下两个列 课程类别 列为 char 类型 长度为 8 考试类别 列为 char 类型 长度为 4 这两个列都不允许为空 任务分析 使用 CREATE TABLE 语句创建表后 可以使用 ALTER TABLE 语句向该表中添加列 要求所添加的列必须允许空值或对列创建 DEFAULT 约束 由于新创建的 课程 表目前是一个不包 074
项目 3 创建和管理表 含任何数据行的空表 因此可以对新添加的列设置 NOT NULL 任务实现 1 2 USE ; GO CREATE TABLE ( int NOT NULL, varchar(20) NOT NULL ); GO ALTER TABLE ADD char(8) NOT NULL, char(4) NOT NULL; GO 3 SQLQuery3-03.sql F5 4 相关知识 SQL Server DEFAULT DEFAULT DEFAULT CHECK FOREIGN KEY UNIQUE PRIMARY KEY DEFAULT 一 使用表设计器添加或删除表列 1 3.8 2 3.9 3 4 Tab 075