第3讲管理表.ppt

第3讲管理表 哈尔滨师范大学恒星学院 本讲主要内容 表的基础知识 用SSMS管理表 用T SQL语言管理表 常用工具简介 1 2 3 数据库创建后 就可以创建表来存储数据 通常是存储在永久表中 不过 用户也可以建立临时表 表会一直存储在数据库文件内 直到被删除为止 只要用户拥有适当权限 就可以使用它们 一 表的基础知识 表是由行和列组成的 创建表的过程主要就是定义表的列的过程 与表有关的两个常用概念是 字段和记录 其中记录是表的一行 是表所塑造的单一对象的实体 而字段是表中的列 描述一条记录要有若干个列 列代表对象的属性 例如 姓名 住址等 在SQLServer中 每个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 也就是可以定义1024个字段 但是很少有哪个设计良好的关系数据库表会包含25个以上的列 它们绝大多数都只包含少数的列 每个列有自己的属性 常用且重要的三个属性是 列名 数据类型和约束 SQLServer系统内的对象在命名时可以使用中文 但要尽量避免这样做 以免引发工具程序或前端应用程序的无谓错误 对象的命名必须符合系统的命名规则 比如名称标识符含字母在1 128个 临时表的名称在116个字符内 特殊字符的名称要放到分隔符 或 内等等 命名规则 数据类型是数据的格式 表定义中的每个列必须有数据类型 数据操作中 SQLServer自动进行数据检验 常用数据类型主要有 整数型 int smallint bigint浮点型 numeric float n real字符型 char n varchar n nchar n 日期时间型 smalldatetime date time 数据类型 SQLServer提供了两种方法创建数据库表 第一种方法是通过图形化界面创建即使用 SQLServerManagementStudio 简记为SSMS 创建表 它提供了一个前端的 填充式的表设计器以简化表的设计工作 另一种方法是利用T SQL语句中的CREATE命令创建表 二 用SSMS管理表 在SSMS中 展开要建表的数据库 比如pubs 右键单击 表 节点 在弹出的菜单中选择 新建表 打开表设计器 如下图 注意在下方列属性视图中设置主键 非空 标识列 默认值 创建表 例建立表student存放学生的信息 idchar 7 namechar 10 sexchar 2 birthdaysmalldatetime birth placevarchar 50 classchar 10 练习 用T SQL命令CREATE创建表 CREATETABLEstudent idchar 7 namechar 10 sexchar 2 birthdaysmalldatetime birth placevarchar 50 classchar 10 三 用T SQL语言管理表 哈尔滨师范大学恒星学院 修改表 1 启动SSMS后 展开SQLServer实例和数据库 2 右击要修改的表 然后从弹出的快捷菜单中选择 设计 命令 3 在 表设计器 中 可以新增列 删除列和修改列的名称 数据类型 长度 是否允许为空等 4 当完成修改表的操作后 单击工具栏上的 保存 按钮 利用T SQL语句修改数据表修改数据表的语法格式如下 ALTERTABLEtable name ALTERCOLUMNcolumn namenewdatatype precision scale NULL NOTNULL ADD n DROP CONSTRAINT constraint name COLUMNcolumn name n 哈尔滨师范大学恒星学院 删除表 1 启动SSMS后 展开SQLServer实例和数据库 2 右击要修改的表 然后从弹出的快捷菜单中选择 删除 打开 删除对象 对话框 3 在 删除对象 对话框中 显示出删除对象的属性信息 单击 确定 按钮 注意 删除表只能够删除用户表 不能够删除系统表 删除表一旦操作完成 表中数据也一并被删除 而且是无法恢复的 利用T SQL语句删除数据表删除数据表的语法格式如下 DROPTABLEtable name n 其中 table name为所要删除的表的名称 删除表只能够删除用户表 不能够删除系统表 完整性与约束 完整性数据完整性是数据库设计方面一个非常重要的问题 数据完整性代表数据的正确性 一致性与可靠性 实施完整性的目的在于确保数据的质量 在SQLServer中 根据数据完整性措施所作用的数据库对象和范围不同 可以将数据完整性分类为实体完整性 域完整性和参照完整性等 1 实体完整性实体完整性把数据表中的每行看作一个实体 它要求所有行都具有唯一标识 在SQLServer中 可以通过建立PRIMARYKEY约束 UNIQUE约束 唯一索引 以及列IDENTITY属性等措施来实施实体完整性 2 域完整性域完整性要求数据表中指定列的数据具有正确的数据类型 格式和有效的数据范围 域完整性通过默认值 FOREIGNKEY CHECK等约束 以及默认 规则等数据库对象来实现 3 参照完整性参照完整性维持被参照表和参照表之间的数据一致性 在SQLServer中 参照完整性通过主键与外键或唯一键与外键之间的关系来实现 通过建立FOREIGNKEY约束来实施 在被参照表中 当其主键值被其他表所参照时 该行不能被删除 也不允许改变 在参照表中 不允许参照不存在的主键值 约束语句是表定义语句的重要组成部分 通过约束机制 可使数据库引擎强制控制数据内容的完整性 约束语句可以在列或表两个级别完成 例如 将PRIMARYKEY约束可以作用在列上 若PRIMARYKEY约束结合多个字段 则只能在表级别建立 使用时 约束优先于DML触发过程 规则和默认 约束 notnull非空约束 不允许空值 primarykey主键约束 识别表的记录 主键值唯一 非空 check检查约束 限制列值范围 操作数据时 系统自动验证数据 unique唯一性约束 值唯一 该约束可建在表上 可用于有空值的列 default默认值约束 默认的值 常用的默认值getdate newid foreignkey外键约束 该键值参照另一表键值 另一表 引用表 的值必须存在唯一 约束的种类 哈尔滨师范大学恒星学院 使用表设计器创建PrimaryKey约束在表设计器中 选择需要设置主键的列 如需要设置多个列为主键 则选中所有需要设置为主键的所有列 单击鼠标右键 然后从弹出的快捷菜单中选择 设置主键 命令 完成主键设置 这时主键列的左边会显示 黄色钥匙 图标启动 如何修改和删除PrimaryKey约束呢 建立约束 使用T SQL语句创建PRIMARYKEY约束创建主键约束的语法形式如下 CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED column name n 其中 CLUSTERED NONCLUSTERED表示所创建的UNIQUE约束是聚集索引还是非聚集索引 默认为CLUSTERED聚集索引 哈尔滨师范大学恒星学院 使用表设计器创建Unique约束在表设计器中可以创建 修改和删除Unique约束 要点 选中列 按右键 选 索引 键 I 如何创建Check约束呢 建立约束 使用T SQL语句创建UNIQUE约束创建唯一性约束的语法形式如下 CONSTRAINTconstraint name UNIQUE CLUSTERED NONCLUSTERED 其中 CLUSTERED NONCLUSTERED表示所创建的UNIQUE约束是聚集索引还是非聚集索引 默认为NONCLUSTERED非聚集索引 哈尔滨师范大学恒星学院 CHECK约束CHECK约束用于限制输入到一列或多列的值的范围 从逻辑表达式判断数据的有效性 也就是一个列的输入内容必须满足CHECK约束的条件 否则 数据无法正常输入 从而强制数据的域完整性 建立约束 使用T SQL语句创建CHECK约束创建检查约束的语法形式如下 CONSTRAINTconstraint name CHECK check expression 其中 check expression为检查表达式 使用表设计器创建DEFAULT约束在表设计器中可以创建 修改和删除DEFAULT约束 其操作步骤如下 在表设计器中 选择需要设置DEFAULT值的列 在下面 列属性 的 默认值或绑定 栏中输入默认值 然后单击工具栏中的 保存 按钮 即完成DEFAULT约束的创建 使用T SQL语句创建DEFAULT约束创建默认值约束的语法形式如下 CONSTRAINTconstraint name DEFAULTconstraint expression其中 constraint expression为默认值 哈尔滨师范大学恒星学院 使用表设计器建立ForeignKey约束外键 ForeignKey 用于建立和加强两个表 主表与从表 的一列或多列数据之间的链接 当数据添加 修改或删除时 通过外键约束保证它们之间数据的一致性 定义表之间的参照完整性是先定义主表的主键 再对从表定义外键约束 ForeignKey约束要求列中的每个值在所引用的表中对应的列中都存在 建立约束 哈尔滨师范大学恒星学院 在表设计器的空白处单击鼠标右键选择 关系 弹出 外键关系 对话框 单击 添加 按钮可以添加外键 单击 表和列规范 右侧的按钮 弹出 表和列 对话框 选择相应的主键表和外键表及对应的主键和外键 单击 确定 按钮返回 外键关系 对话框 在 标识 的名称栏中填写外键名称即可创建外键约束 要删除外键 只要在该对话框左侧栏中选择对应的外键名称 单击 删除 按钮即可 建立约束 使用T SQL语句创建FOREIGNKEY约束创建外键约束的语法形式如下 CONSTRAINTconstraint name FOREIGNKEY column name n 定义外键 REFERENCESreferenced table name ref column n n 参数说明如下 referenced table name是FOREIGNKEY约束引用的表的名称 column name是FOREIGNKEY约束所引用的表中的某列 创建表时给出列的约束 CREATETABLEstudent1 idchar 7 primarykeyclustered namechar 10 notnull sexchar 2 check sexin F M birthdaysmalldatetime birth placevarchar 50 null classchar 10 USEpubsCREATETABLEdbo 客户 客户编号intprimarykey 客户名称char 50 notNull 住址char 50 生日smalldatetime 电话int 建立日期smalldatetimedefaultGetDate constraintchk phonecheck 电话like 010 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 constraintchk idcheck 客户编号between0and10000 例题 下列代码测试 建立日期 字段是否输入默认值 USEpubsINSERT客户 客户编号 客户名称 生日 VALUES 1001 Candy 1980 04 22 GOSELECT FROM客户 CREATETABLE产品 产品编号intprimarykey 产品名称varchar 30 CREATETABLE订单 订单编号int 产品编号intforeignkeyreferences产品 产品编号 订单数量int 订单日期datetimedefaultGetdate 下面的范例使用订单表与产品表来说明如何用代码建立 外键 首先 产品表有 主键 才可用订单表的 产品编号 字段为参考来建立 外键 外键 不一定要参考另一个表的 主键 也可以定义为参考其他表中unique条件约束的列 外键 可包含Null值 同时 复合 外键 的任何列包含了Null值 将会略过数据验证工作 若要确保输入的数据都符合 外键 要求 可对所有参与的列指定非空 例题在执行创建产品信息表的操作时 指定产品编号为主键值 并且创建一个聚集索引 createtableproducts idchar 10 notnull namechar 20 notnull pricemoneydefault0 01 quantitysmallintnullconstraintpk idprimarykeyclustered id 增加 删除和修改列 例题在雇员信息表中增加 删除和修改字段的SQL语句的程序清单如下 CREATETABLEemployees idchar 8 primarykey namechar 20 notnull departmentchar 20 null memochar 30 null ageintnull ALTERTABLEemployeesADDsalaryintnullDROPcolumnageALTERcolumnmemovarchar 200 null 若要修改表的定义并在现有的表中新增列 操作必须在该列允许Null值或包含Default约束的条件下进行 由于新的列被加入表中 数据库引擎会针对表中的每条现有数据将值插入该列内 因此 当列被加入表中时 替该列加入Default定义会很有用或者指定该列允许Null值 不满足这些条件 将返回错误信息 如果需要增加非空列呢 增加新列的限制 用户可以删除现有表中的列 但具有下列特性的列将无法删除 用于索引的列 用于CHECK FOREIGNKEY UNIQUE或PRIMARYKEY条件约束的列 与Default定义建立关联 或关联至默认对象的列 关联至规则的列 注册成全文检索支持的列 作为表的全文检索索引键的列 删除列的限制 查看表格的定义 查看表格中的数据 查看表格与其它数据库对象的依赖关系 利用系统存储过程sp help查看表的信息例如sp helptitlessp helpdbpubsexecsp helpdb 查看表 DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引 触发器 约束 许可对象 其语法形式如下 DROPTABLEtable name table name使