第三章 数据库中表的管理.ppt

第一章 数据库中表的管理 课程目标 数据类型创建表修改表删除表 数据类型 在SQLServer2005中 每个列 局部变量 表达式和参数都具有一个相关的数据类型 数据类型是一种属性 用于指定对象可保存的数据的类型 整数数据 字符数据 货币数据 日期和时间数据 二进制字符串等 数据类型 SQLServer2005中的数据类型归纳为下列类别 精确数字 近似数字 日期和时间 字符串 Unicode字符串 二进制字符串 其他数据类型 整数类型 BIT类型 可以取值为1 0或NULL的整数数据类型 MicrosoftSQLServer2005DatabaseEngine优化了bit列的存储 如果表中的列为8bit或更少 则这些列作为1个字节存储 如果列为9到16bit 则这些列作为2个字节存储 以此类推 字符串值TRUE和FALSE可以转换为以下bit值 TRUE转换为1 FALSE转换为0 Decimal和numeric类型 带固定精度和小数位数的数值数据类型 decimal p s 和numeric p s p 精度 最多可以存储的十进制数字的总位数 包括小数点左边和右边的位数 该精度必须是从1到最大精度38之间的值 默认精度为18 s 小数位数 小数点右边可以存储的十进制数字的最大位数 小数位数必须是从0到p之间的值 仅在指定精度后才可以指定小数位数 默认的小数位数为0 因此 0 s p 最大存储大小基于精度而变化 Decimal和numeric类型 Money和smallmoney Float和real 用于表示浮点数值数据的大致数值数据类型 浮点数据为近似值 因此 并非数据类型范围内的所有值都能精确地表示 float n 其中n为用于存储float数值尾数的位数 以科学记数法表示 因此可以确定精度和存储大小 如果指定了n 则它必须是介于1和53之间的某个值 n的默认值为53 datetime和smalldatetime Char和Varchar char n 固定长度 非Unicode字符数据 长度为n个字节 n的取值范围为1至8 000 存储大小是n个字节 char的SQL2003同义词为character varchar n max 可变长度 非Unicode字符数据 n的取值范围为1至8 000 max指示最大存储大小是231 1个字节 ntext 长度可变的Unicode数据 最大长度为230 1 1 073 741 823 个字符 存储大小是所输入字符个数的两倍 以字节为单位 ntext的SQL2003同义词为nationaltext text 服务器代码页中长度可变的非Unicode数据 最大长度为231 1 2 147 483 647 个字符 当服务器代码页使用双字节字符时 存储仍是2 147 483 647字节 根据字符串 存储大小可能小于2 147 483 647字节 image 长度可变的二进制数据 从0到231 1 2 147 483 647 个字节 在MicrosoftSQLServer的未来版本中将删除ntext text和image数据类型 请避免在新开发工作中使用这些数据类型 并考虑修改当前使用这些数据类型的应用程序 Nchar和Nvarchar类型 nchar n n个字符的固定长度的Unicode字符数据 n值必须在1到4 000之间 含 存储大小为两倍n字节 nvarchar n max 可变长度Unicode字符数据 n值在1到4 000之间 含 Binary和Varbinary binary n 长度为n字节的固定长度二进制数据 其中n是从1到8 000的值 存储大小为n字节 varbinary n max 可变长度二进制数据 n可以取从1到8 000的值 cursor类型 这是变量或存储过程OUTPUT参数的一种数据类型 这些参数包含对游标的引用 使用cursor数据类型创建的变量可以为空 timestamp 公开数据库中自动生成的唯一二进制数字的数据类型 timestamp通常用作给表行加版本戳的机制 存储大小为8个字节 每个数据库都有一个计数器 当对数据库中包含timestamp列的表执行插入或更新操作时 该计数器值就会增加 该计数器是数据库时间戳 一个表只能有一个timestamp列 每次修改或插入包含timestamp列的行时 就会在timestamp列中插入增量数据库时间戳值使用某一行中的timestamp列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改 如果对行进行了更改 就会更新该时间戳值 如果没有对行进行更改 则该时间戳值将与以前读取该行时的时间戳值一致 在CREATETABLE或ALTERTABLE语句中 不必为timestamp数据类型指定列名 例如 CREATETABLEExampleTable PriKeyintPRIMARYKEY timestamp sql variant 一种数据类型 用于存储SQLServer2005支持的各种数据类型 不包括text ntext image timestamp和sql variant 的值 sql variant可以用在列 参数 变量和用户定义函数的返回值中 sql variant使这些数据库对象能够支持其他数据类型的值 类型为sql variant的列可能包含不同数据类型的行 uniqueidentifier 16字节GUID uniqueidentifier数据类型的列或局部变量可通过以下方式初始化为一个值 使用NEWID函数 从xxxxxxxx xxxx xxxx xxxx xxxxxxxxxxxx形式的字符串常量转换 其中 每个x是一个在0 9或a f范围内的十六进制数字 例如 6F9619FF 8B86 D011 B42D 00C04FC964FF为有效uniqueidentifier值 table 一种特殊的数据类型 用于存储结果集以进行后续处理 table主要用于临时存储一组行 这些行是作为表值函数的结果集返回的 xml 存储XML数据的数据类型 可以在列中或者xml类型的变量中存储xml实例 自定义数据类型 SQLServer允许用户自定义数据类型 用户自定义数据类型是建立在SQLServer系统数据类型基础上的 当用户定义一种数据类型时 需要指定该类型的名称 建立在其上的系统数据类型以及是否允许为空等 自定义数据类型 使用SQLServer管理平台创建用户自定义数据类型 在SQLServer管理平台中 打开指定的服务器和数据库项 选择并展开 可编程性 类型 项 接下来用右键单击 用户自定义数据类型 选项 从弹出的快捷菜单中选择 新建 命令 出现用户定义的数据类型属性对话框 如下图 自定义数据类型 可以创建规则后添加上去如 CREATERULErange ruleAS range 1000AND range 20000 自定义数据类型 系统存储过程sp addtype为用户提供了用T SQL语句创建自定义数据类型的途径 其语法形式如下 sp addtype typename 自定义类型名称 phystype 对应的系统数据类型名称 nulltype 是否允许为空 owner 所有者 2 创建表 与创建数据库类似 创建表也可以通过ManagementStudio来创建 或者使用SQL语句创建 使用ManagementStudio创建表 利用create命令创建表 使用create命令创建表非常灵活 它允许对表设置几种不同的选项 包括表名 存放位置和列的属性等 其完整语法形式如下 CREATETABLE 数据库名 所有者 表名 列名AS计算机列表达式 n ON文件组名 DEFAULT TEXTIMAGE ON文件组名 DEFAULT 列定义的写法 其中 列定义的写法如下 列名列的数据类型 COLLATE列的排序规则 DEFAULT默认值 IDENTITY 基数 增量 NOTFORREPLICATION ROWGUIDCOL n 参数写法 数据库名 用于指定所创建表的数据库名称 所有者 用于指定新建表的所有者的用户名 表名 用于指定新建表的名称 列名 用于指定新建表的列名 参数写法 计算机列表达式 用于指定计算列的列值表达式 ON 文件组名 DEFAULT 用于指定存储表的文件组名 TEXTIMAGE ON 用于指定text ntext和image列的数据存储的文件组 列的数据类型 用于指定列的数据类型 参数写法 默认值 用于指定列的默认值的常量表达式 可以为一个常量或NULL或系统函数 IDENTITY 用于将列指定为标识列 基数 用于指定标识列的初始值 增量 用于指定标识列的增量值 参数写法 NOTFORREPLICATION 用于指定列的IDENTITY属性 在把从其他表中复制的数据插入到表中时不发生作用 即不生成列值 使得复制的数据行保持原来的列值 ROWGUIDCOL 用于将列指定为全局惟一标识行号列 rowguidcol属性不会使列值具有惟一性 也不会自动生成一个新的数据值给插入行 需要在INSERT语句中使用NEWID 函数或指定列的默认值为NEWID 函数 参数写法 COLLATE 用于指定表的校验方式 列级约束 指定列的约束规则 表级约束 指定表的约束规则 通常用于涉及多个列的约束 标识列 标识列是可以自动增加的列 在表中添加新行时 数据库引擎将为标识列提供一个唯一的增量值 标识列通常与PRIMARYKEY主键约束一起用作表的唯一行标识符 可以将IDENTITY属性分配给tinyint smallint int bigint decimal p 0 或numeric p 0 列 每个表只能创建一个标识列 不能对标识列使用绑定默认值和DEFAULT约束 计算列 计算列是指没有物理地存储在表中的虚拟列 该列由同一表中的其他列通过表达式计算得到 例如 假设表中有两列 姓 和 名 用于保存人员的姓和名 则计算列可以定义为姓名AS姓 名 约束 约束是SQLServer提供的自动保持数据库完整性的一种方法 它通过限制字段中数据 记录中数据和表之间的数据来保证数据的完整性 在SQLSERVER中 对于基本表的约束分为列约束和表约束 6种约束 在SQLServer2005中有6种约束 主键约束 primarykeyconstraint 惟一性约束 uniqueconstraint 检查约束 checkconstraint 默认约束 defaultconstraint 外部键约束 foreignkeyconstraint 空值 NULL 约束 主键 PRIMARYKEY 约束 PRIMARYKEY主键约束用于定义基本表的主键 它是惟一确定表中每一条记录的标识符 其值不能为NULL 也不能重复 以此来保证实体的完整性 主键 PRIMARYKEY 约束 PRIMARYKEY既可用于列约束 也可用于表约束 设置主键的语法形式如下 CONSTRAINT约束名PRIMARYKEY CLUSTERED NONCLUSTERED 列名 主键 PRIMARYKEY 约束 如果是在列级约束中指定主键约束 则直接在列的定义后写主键约束的代码 不需要再指定列名了 如果是在表级约束中指定主键约束 则需要指明列名 如果要将某些列的组合定义为主键 只能在表级约束中写 其语法格式如下 CONSTRAINT约束名PRIMARYKEY CLUSTERED NONCLUSTERED 列名1 列名2 主键 PRIMARYKEY 约束 主键 PRIMARYKEY 约束 以下代码建立一个SC学习选课表 定义SNO学生学号 CNO课程编号共同组成SC的主键 CREATETABLESC SNOCHAR 5 NOTNULL CNOCHAR 5 NOTNULL SCORENUMERIC 3 CONSTRAINTSC PRIMPRIMARYKEY SNO CNO 惟一性约束 惟一性约束用于指定一个或者多个列的组合值具有惟一性 以防止在列中输入重复的值 定义了UNIQUE约束的那些列称为唯一键 系统自动为唯一键建立唯一索引 从而保证了唯一键的唯一性 惟一性约束 当使用惟一性约束时 需要考虑以下几个因素 使用惟一性约束的字段允许为空值 一个表中可以允许有多个惟一性约束 可以把惟一性约束定义在多个字段上 惟一性约束用于强制在指定字段上创建一个惟一性索引 默认情况下 创建的索引类型为非聚集索引 惟一性约束 创建唯一性约束的语法形式如下 CONSTRAINT约束名UNIQUE CLUSTERED NONCLUSTERED 列名 n 惟一性约束 以下代码创建一个学生信息表 其中name字段具有惟一性 Createtablestudent idchar 8 namechar 10 sexchar 2 constraintpk idprimarykey id constraintuk identityunique name 惟一性约束 也可以在SQLServer管理平台中完成创建和修改惟一性约束的操作 方法是在列上点右链 从弹出的菜单中选择 索引 键 检查约束 检查约束对输入列或者整个表中的值设置检查条件 以限制输入值 保证数据库数据的完整性 检查约束 当使用检查约束时 应该考虑和注意以下几点 一个列级检查约束只能与限制的字段有关 一个表级检查约束只能与限制的表中字段有关 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行INSERT语句或者UPDATE语句时 检查约束将验证数据 检查约束中不能包含子查询 检查约束 创建检查约束的语法形式如下 CONSTRAINT约束名CHECK NOTFORREPLICATION 用于检查的逻辑表达式 检查约束 以下代码建立一个SC表 定义SCORE的取值范围为0到100之间 CREATETABLESC SNOCHAR 5 CNOCHAR 5 SCORENUMERIC 5 1 CONSTRAINTSCORE CHKCHECK SCORE 0ANDSCORE 100 检查约束 在ManagementStudio中创建检查约束的方法是 从右键菜单中选择 CHECK约束 默认 DEFAULT 约束 默认约束指定在插入操作中如果没有提供输入值时 则系统自动指定值 默认约束可以包括常量 函数 不带变元的内建函数或者空值 默认 DEFAULT 约束 使用默认约束时 应该注意以下几点 1 每个字段只能定义一个默认约束 2 如果定义的默认值长于其对应字段的允许长度 那么输入到表中的默认值将被截断 3 不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上 4 如果字段定义为用户定义的数据类型 而且有一个默认绑定到这个数据类型上 则不允许该字段有默认约束 默认 DEFAULT 约束 创建默认约束的语法形式如下 CONSTRAINT约束名DEFAULT约束表达式 FOR列名 以下代码为dept字段创建默认约束 使其默认值是 计算机 constraintcon deptdefault 计算机 fordept 默认 DEFAULT 约束 外键约束 外键 FOREIGNKEY 是用于建立和加强两个表数据之间的链接的一列或多列 外键约束用于强制参照完整性 外键约束 当使用外键约束时 应该考虑以下几个因素 外键约束提供了字段参照完整性 外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配 外键约束不能自动创建索引 需要用户手动创建 用户想要修改外键约束的数据 必须有对外键约束所参考表的SELECT权限或者REFERENCES权限 参考同一表中的字段时 必须只使用REFERENCES子句 不能使用外键子句 一个表中最多可以有31个外键约束 在临时表中 不能使用外键约束 主键和外键的数据类型必须严格匹配 外键约束 创建外键约束的语法形式如下 CONSTRAINT约束名FOREIGNKEY 列名 n REFERENCES参照表名 参照列名 n 外键约束 以下代码建立一个SC表 定义SNO CNO为SC的外部键 CREATETABLESC SNOCHAR 5 NOTNULLCONSTRAINTS FOREFOREIGNKEYREFERENCESS SNO CNOCHAR 5 NOTNULLCONSTRAINTC FOREFOREIGNKEYREFERENCESC CNO SCORENUMERIC 3 CONSTRAINTS C PRIMPRIMARYKEY SNO CNO 外键约束 在ManagementStudio中创建外键约束的方法是 从右键菜单中选择 关系 空值 NULL 约束 空值 NULL 约束用来控制是否允许该字段的值为NULL NULL值不是0也不是空白 更不是填入字符串的 NULL 字符串 而是表示 不知道 不确定 或 没有数据 的意思 当某一字段的值一定要输入才有意义的时候 则可以设置为NOTNULL 如主键列就不允许出现空值 否则就失去了唯一标识一条记录的作用 空值 NULL 约束只能用于定义列约束 空值 NULL 约束 创建空值 NULL 约束的语法形式如下 CONSTRAINT NULL NOTNULL 以下代码建立一个S表 对SNO字段进行NOTNULL约束 CREATETABLES SNOCHAR 10 CONSTRAINTS CONSNOTNULL SNVARCHAR 20 AGEINT SEXCHAR 2 DEFAULT 男 DEPTVARCHAR 20 空值 NULL 约束 在ManagementStudio中创建空值约束的方法是 在需要设置允许为空的列中 勾选对应的 允许空 复选框 如果不允许为空则取消该复选框的选择 如下图 3 修改表 当数据库中的表创建完成后 可以根据需要改变表中原先定义的许多选项 以更改表的结构 用户可以增加 删除和修改列 增加 删除和修改约束 更改表名以及改变表的所有者等 在ManagementStudio中修改表的属性与创建表的界面一致 这里就只讲解使用SQL语句修改表的方法 3 1修改列属性 1 修改列的数据类型 2 修改列的数据长度 3 修改列的精度 4 修改列的小数位数 5 修改列的为空性 3 1修改列属性 ALTERTABLE表名ALTERCOLUMN列名 列修改项 如以下代码创建表t1 然后将其列b从char 10 修改为char 20 CREATETABLEt1 aint bchar 10 Altertablet1Altercolumnbchar 20 3 2添加和删除列 添加列的语法如下 ALTERTABLE表名ADD列定义以下代码将列C 类型为DATETIME 加