向数据库输入数据ppt课件.ppt

任务 了解SQLServer2005支持的数据类型 创建自定义数据类型 设计JXGL数据库表结构 应用SSMS和T SQL语言两种方法 创建 修改JXGL数据库中的表 向表中输入数据 对表中数据进行简单管理 3 1T SQL语言的数据类型3 2设计 高职院校教学管理系统 中表的结构3 3创建表3 4管理表3 5操作表中的数据3 6知识进阶 第3章向数据库输入数据 创建和管理SQLServer2005数据库表 本章学习目标了解T SQL语言支持的基本数据类型创建用户自定义数据类型使用SSMS创建 修改数据库表使用CREATETABLE与ALTERTABLE语句创建和修改数据库表使用SSMS方式和T SQL语言向表中输入新数据 删除 更新已有数据 第3章创建和管理数据库表 第3章创建和管理数据库表 3 1T SQL语言的数据类型 任务 了解T SQL语言支持的基本数据类型 掌握用户自定义数据类型的方法 3 1T SQL语言的数据类型 SQL语言提供了25种系统数据类型 还支持用户根据需要创建基于系统数据类型的自定义数据类型 这些数据类型T SQL可用 1 系统数据类型SQLServer2005中的系统数据类型归纳为下列类别 精确数值 近似数值 日期时间 货币 字符串 Unicode字符串 二进制字符串和其他数据类型 系统提供的数据类型 参考 SQLServer联机丛书 中的 SQLserver语言参考 Transact SQL参考 数据类型 精确和近似数值数据类型固定长度和可变长度字符数据类型大型数据值 textntextvarchar max nvarchar max varbinary max image 使用系统数据类型应注意的问题 精确整数型数据bit型数据当列为8bit或更少时 以1个字节存储 为9 16bit时 以2个字节存储 近似数值型数据该类型用于存储小数点不固定的数值 一般不用于存储货币 字符型数据char型字符串在输入长度大于定义长度时 会被截断 而小于定义长度时 会以空格填充 varchar和text型的存储空间会根据实际字符串长度而变化 Unicode字符型数据Unicode统一字符编码标准用于支持英语语种的字符数据 用两个字节存储一个字符 输入该类型数据时需在字符前加前导标识符N 但表中不会存储该标识符 2 用户自定义数据类型 别名数据类型 当多个表必须在一个列中存储相同类型的数据 而您必须确保这些列具有相同的数据类型 长度和为空性时 可以使用用户自定义数据类型即别名类型 别名类型是基于SQLServer2005中提供的系统数据类型 允许进一步细化数据类型在特定数据库中定义在数据库内必须有唯一的名称 创建别名数据类型时 必须提供下列参数 Name新数据类型基于的系统数据类型为空性 数据类型是否允许空值 如果未明确定义为空性 系统将基于数据库或连接的ANSINULL默认设置进行指定 创建别名数据类型的方法 使用SSMS 在数据库中依次展开 可编程性 类型 用户定义数据类型 使用T SQL语句 别名数据类型 创建别名数据类型SQL语句格式 CREATETYPE schema name type name FROMbase type precision scale NULL NOTNULL 参数说明 schema name别名数据类型或用户定义类型所属架构的名称 type name别名数据类型或用户定义类型的名称 类型名称必须符合标识符的规则 别名数据类型 base type别名数据类型所基于的数据类型 由SQLServer提供 precision对于decimal或numeric 指示可保留的十进制数字位数的最大值 包括小数点左边和右边的数字 scale对于decimal或numeric 指示十进制数字的小数点右边最多可保留多少位 它必须小于或等于精度值 NULL NOTNULL指定此类型是否可容纳空值 如果未指定 则默认值为NULL 使用Transact SQL创建别名数据类型 创建名为postcode的别名数据类型 指定其数据类型为char型 长度为6 可空 CREATETYPEdbo postcodeFROMchar 6 NULL 任务 规范存储数据的数据库环境 设计JXGL数据库中表的结构 3 2设计 高职院校教学管理系统 中表的结构 3 2设计 高职院校教学管理系统 中表的结构 创建用户数据库后 还无法将数据保存在数据库中 即此时的数据库是一个空库 必须在数据库中创建用来存放数据的 容器 这个 容器 就是表 表是是用来存储和操纵数据的逻辑结构 开发一个数据库系统需要经过一个 两次抽象 的过程 在第一次抽象过程中 要分析所开发的数据库系统包含哪些实体 分析每个实体应该至少包含的属性 以及实体之间的联系 并使用E R图将抽象结果表示出来 第二次抽象过程 需要将E R图转换为数据模型即数据库表 每个属性就是表中的每个列或字段 学生 主键 学号外键 班号 班级 主键 班号外键 系号 包含 1 n 系部 主键 系号 包含 1 n 教师 主键 教师编号外键 系号 包含 1 n 课程 主键 课号外键 教师编号教材编号 排课 1 n 成绩 n m 外键 课号外键 学号 教材 主键 教材编号 外键 教师编号班级编号课程编号 15 任务 使用SSMS及T SQL的CREATETABLE语句创建JXGL数据库表 3 3 1使用SSMS创建表3 3 2使用T SQL语句创建表 3 3创建表 依据不同的分类标准 SQLServer2005中的表有以下分类 按照表中数据存储的时间分为永久表和临时表 永久表一旦创建 除非人工删除 否则一直存在 临时表中的数据只在数据库运行期间临时存在 退出连接数据库后 表也将从内存中删除 按照表的用途分为系统表和用户表 系统表用来维护SQLServer服务器工作 存储系统级信息 用户表一般为永久表 由用户创建 用于开发数据库应用系统 例 使用SSMS创建数据库JXGL中的student info表 3 3 1使用SSMS创建表 创建表的T SQL语句是CREATETABLE 基本语法如下 CREATETABLEtable name column namedata type column constraint column namedata type column constraint 3 3 2使用T SQL语句创建表 其中 table name 表名称 表名必须符合标识符规则 最多包含128个字符 在数据库中必须唯一 column name 列的名称 data type 指定列的数据类型 column constraint 列级约束 对应列采取的限制措施 如非空性 默认值 唯一性 完整性等 关于约束的概念将在任务四中介绍 table constraint 表级约束 当完整性约束涉及该表的多个属性列时 必须定义在表级上 这些定义连同对应的列约束被存储在系统中 当对数据库进行相关操作时 系统自动检查操作的合法性 1 例3 2 使用CREATETABLE语句创建表teacher info USEJXGLGOCREATETABLEteacher info teac idchar 8 PRIMARYkey teac namenvarchar 20 NOTNULL dep idchar 2 NOTNULLDEFAULT J genderbitNOTNULLDEFAULT 1 professionnvarchar 20 DEFAULT N 副教授 telephonevarchar 14 thome addrnvarchar 50 NOTNULLDEFAULT N 不详 tzipcodecode GO 3 3 2使用T SQL语句创建表 在创建表时 某些列定义为NULL或NOTNULL 这两种定义指明了是否在该列中输入数据 如果一个列中有NULL值 允许空值 意味着该列中可以没有数据 具有NULL值的列是一种特殊的数据状态 它既不是数值 也不是字符 而意味着一种未知状态 例如 在student info表中的home addr列设置为NULL值 意味着当不明确某一学生的家庭住址时 可以暂时处于空的状态 相当于输入 地址不详 表中的主键不能为NULL值 NULL值 空值 的作用 1 标识列 IDENTITY列 标识列是这样的列 给定一个初始值 称为种子 给定一个递增值 称为步长 则每一个列值总是由前一行该列的值加上步长得来 定义标识列要注意以下问题 1 一个表只能定义一个标识列 2 标识列的数据类型只能是int smallint或tinyint型 或者是小数位数为0的decimal numeric类型 3 6 2关于标识列和计算列 3 需要指定标识的种子和步长值 若不指定 二者默认值均为1 4 标识列的值不能人工输入 由系统按种子和步长值自动产生 标识列的值不能被更新 5 标识列不允许空值 也不能指定默认值 6 标识列可以定义为表的主键 标识列的定义列名数据类型IDENTITY 标识种子 标识增量 2 计算列计算列指其值由同一表中的其他列的值或值的表达式计算得来 计算列的值不能人工输入 也不能人工更新 除非用户指定 否则计算列是没有存储在表中的虚拟列 当在查询中引用计算列时 都将重新计算该列的值 如果计算列涉及的列的值发生变化 将自动更新计算列的值 创建表时 在 表设计器 窗口的 列编辑 区中指定某列 在 列属性编辑区 中展开 计算所得的列规范 列表 在 公式 栏中输入计算公式 3 6 2关于标识列和计算列 使用SQL语句定义计算列 列名as计算列表达式使用CREATETABLE语句创建包含计算列的表 代码如下CREATETABLEtable 1 a1intNOTNULL b1intNOTNULL c1asa1 b1 GO 任务 使用SSMS及T SQL的ALTERTABLE语句 DROPTABLE语句修改表结构或删除用户表 3 4 1使用SSMS管理表3 4 2使用T SQL管理表 3 4管理表 1 查看表在 对象资源管理器 中查看该表的常规 权限和扩展属性 2 修改表 1 在 对象资源管理器 中选择某个表 在该窗口中对表进行修改 如添加 删除列 更改列名 数据类型 列宽度 2 在 对象资源管理器 中选择某个表 打开 表属性 对话框 修改表的权限属性值 3 4 1使用SSMS管理表 提示 修改表结构时 一是尽量在没有数据操作的情况下进行 二是修改包含数据的表结构时要保证修改前后数据类型的兼容性 否则会造成数据丢失 3 删除表在 对象资源管理器 中选择某个表 打开 删除对象 对话框 确认被删除对象后 单击 确定 按钮 4 重命名表在 对象资源管理器 中选择某个表 在表名位置上直接输入新的表名即可重命名该表 3 4 2使用T SQL管理表 1 查看表使用系统存储过程sp helptable name可以查看指定表的所有属性信息 execsp helpteacher info 2 修改表的结构 ALTERTABLE 数据库名 架构名 表名 ALTERCOLUMN列名新数据类型 长度 NULL NOTNULL ADD DROPCOLUMN列名 n 修改表的结构 useJXGLGOaltertablestudent infoaltercolumnmarknumeric 5 1 goaltertablestudent infoaddaddressvarchar 30 postcodechar 6 goaltertablestudent infoDROPcolumnaddressgo 3 删除表 DROPTABLE表名 1 修改列 例3 3 修改表book info中的列book name 数据类型改为nvarchar型 列宽度为20 代码如下 ALTERTABLEbook infoALTERCOLUMNbook namenvarchar 20 GO 举例 2 删除列及其约束 当列包含约束时 不能直接进行修改或删除 必须首先删除约束 例3 5 删除表book info中的列book id 代码如下 USEJXGLGOALTERTABLEbook infoDROPPK book infoGOALTERTABLEbook infoDROPCOLUMNbook idGO 3 添加新列及其约束 例3 6 向表book info中添加一个新列price 数据类型为numeric型 精度为5 小数位数为2 设置非空属性 默认值为100 代码如下 USEJXGLGOALTERTABLEbook infoADDpricenumeric 5 2 NOTNULLDEFAULT 100 00 GO 4 添加约束 例3 7 修改课程信息表course info 将列course id设置为表的主键 代码如下 USEJXGLGOALTERTABLEcourse infoADDCONSTRAINTpk course infoPRIMARYKEY course id GO 提示 一个ALTERTABLE语句只能修改一列 但可以添加或删除多个列 只需在各列名之间用逗号 隔开 要修改或删除带约束的列 必须首先删除约束 3 删除表使用DROPTABLE语句删除表 例3 8 使用DROPTABLE语句 删除数据库JXGL中的表classroom info 代码如下 USEJXGLGODROPTABLEclassroom infoGO 提示 表一旦被删除 表的结构定义 数据 全文索引 约束和索引都将从数据库中永久删除 原来存放表及其索引的存储空间被释放给其他数据库对象 任务 使用SSMS及T SQL的INSERTINTO语句向表中输入数据 使用SSMS及T SQL的DELETE UPDATE语句删除 更新表中的数据 3 5 1使用SSMS操作表中的数据3 5 2使用T SQL操作表中的数据 3 5操作表中的数据 提示 对标识列 允许为空的列 有默认值的列 计算列 可以不输入具体的值 系统会为这些列自动赋值 其余的列必须输入数据 否则系统会提示错误 表的某些列可能会带有某种约束条件 输入 修改数据时 必须符合这些设置的条件 如果不小心输入了不符合要求的数据 系统会弹出提示信息框提示用户输入的数据非法 3 5 1使用SSMS操作表中的数据 1 使用INSERT语句输入数据 格式 INSERT INTO column namelist VALUES valueslist selectstatement 其中 columnlist 要输入数据的列的清单 valueslist 要向表中输入的数据的清单 selectstatement 通过查询向当前表中输入数据的查询语句 使用INSERT语句输入数据时应注意以下问题 当向表中所有列输入数据时 columnlist子句可以缺省 若缺省该子句 数据清单中数据的顺序一定与表中列的顺序一致 当要输入数据的表中包含有标识列 允许为空的列 设置默认值的列时 可以不输入该列的值 当向字符型 日期型列输入数据时 要以单引号引起来 1 向表中输入一行数据 例3 9 向表teacher info插入一条合法记录 USEJXGLGOINSERTINTOteacher info teac id teac name depart id gender profession telephone thome addr tzipcode VALUES TD009 N 董卫刚 TD 1 N 讲师N 济南市燕子山路号 250014 GO 注意 columnlist子句缺省时 数据清单中数据的顺序和数目一定与表中列的顺序和数目一致 代码如下 INSERTINTOteacher infoVALUES TG0013 N 汪虹 TG 1 N 副教授N 济南市解放路 250014 GO 42 2 输入一行中的部分列 当表中包含设置了默认值及允许为null值的列时 可不输入数据 但列名清单不能缺省 例3 10 向表teacher info插入一条合法记录 USEJXGLGOINSERTINTOteacher info teac id teac name dep id gender profession 列名清单只列出需要输入数据的列VALUES TJ0110 N 姜艳 J 0 N 教授 输入清单中列出的部分列的数据GO 43 2 使用DELETE语句删除表中的数据行 基本语法如下 DELETE FROM table nameWHEREsearch condition其中 参数search condition是一个逻辑表达式 用来描述被删除的数据应该满足的条件 当该子句缺省时 表 或视图 中的数据都将被删除 但表结构 视图定义 仍然存在于数据库中 练习 向表department info中插入一条记录 各列的值依次为 D 电子工程系 温佳亮 USEJXGLGOINSERTINTOdepartment info dep name dep head VALUES 电子工程系 温佳亮 GO 例3 12 删除表book info中书价 book price 高于30元钱的书的信息 代码如下 DELETEFROMbook infoWHEREbook price 30GO 提示 使用delete语句删除数据时 是以行为单位进行删除 删除行时 系统不会提供确认信息 所以用户使用该语句要谨慎 当缺少WHERE子句时 删除整张表 例3 13 删除表book info中所有书的信息USEJXGLGODELETEbook infoGO 3 使用UPDATE语句更新表中的数据 使用UPDATE语句可以修改表中的数据 基本语法如下 UPDATEtable nameSETcolumn name express WHERElogical express 其中 SET子句 指定要被更新的列及其新的值 WHERE子句 指定修改条件 只有满足条件的那些行的列值才被修改为SET子句指定的数据 express 新数据可以是常量或表达式 例3 14 将表student grade中所有同学的成绩提高5分 USEJXGLGOUPDATEstudent gradeSETgrade grade 5GO 1 更新表中所有行的某一列 例3 15 将表student grade中学号为J07072141的同学所有课程的成绩提高5分 代码如下 USEJXGLGOUPDATEstudent gradeSETgrade grade 5WHEREstud id J07072141 GO 2 更新表中某些行的某一列 例3 16 将表course info中课程类型为 考试 的科目 学分提高0 5分 周学时增加10 取整 代码如下 USEJXGLGOUPDATEcourse infoSETcredit