项目四数据表的操作.ppt

数据库课件 上节课任务实训 创建客户订单管理系统数据库Goods 逻辑名Goods data 物理名D 客户订货管理系统 Goods mdf 初始大小8MB 最大容量500MB 增长速度2MB 建立一个辅助文件 逻辑名Good dat 物理名D 客户订货管理系统 Goods1 ndf 初始大小6MB 最大容量200MB 增长速度10 数据库事务日志逻辑名Goods log 物理名D 客户订货管理系统 Goods ldf 初始大小5MB 最大容量50MB 增长速度1MB 项目四数据表的操作 3 1任务描述本章完成项目的第3个任务 在大学生选课管理数据库Student中 完成如下操作 1 创建数据库的5个数据表 学生信息表 教师信息表 课程信息表 教师教课信息表 学生选课信息表 2 输入这5个数据表的记录内容 3 向有关数据表中添加新的记录 4 调整有关课程的学时量和收费标准 5 删除有关学生的记录和有关教师的记录 表的基本概念 表是对现实世界的抽象描述中 将概念数据模型转换成结构数据模型的产物 是关系模型的主要元素 SQLServer2008中支持的表是关系模型中表的实现和具体化 表的基本概念 实体 记录 行属性 字段 列 3 2SQLServer数据类型 表达式和常用函数 3 2 1SQLServer数据类型1 整型数据 1 bigint 可以存储从 263到263 1范围之间的所有整数 2 int 可以存储从 231到231 1范围之间的所有整数 3 smallint 可以存储从 215到215 1范围之间的所有整数 4 tinyint 可以存储从0到255范围之间的所有整数 2 浮点型数据 1 real 可以存储正的或者负的十进制数值 2 float n 可以精确到第15位小数 3 decimal p s 可以提供小数所需要的实际存储空间 其中p为十进制数的最大位数 p是从1到38的整数 默认值为18 s为小数的最大位数 s是从0到p的整数 默认值为0 4 numeric p s 同decimal p s 3 字符型数据 1 char n 为固定长度存储字符串的数据类型 2 varchar n 为可变长度存储字符串的数据类型 3 nchar n 存储固定长度的Unicode字符数据 4 nvarchar n 存储可变长度的Unicode字符数据 4 日期时间型数据 1 date 用于存储从公元0001年1月1日至9999年12月31日的日期型数据 格式为 YYYY MM DD 2 time 用于存储时间数据 格式为 hh mm ss 3 datetime2 用于存储日期和时间的结合体 格式为 YYYY MM DD hh mm ss 4 datetime 用于存储日期和时间的结合体 5 smalldatetime 与datetime数据类型类似 但其日期时间范围较小 它存储从1900年1月1日到2079年6月6日内的日期时间 3 2 1SQLServer数据类型 5 文本型数据 1 text 用于存储大量文本数据 2 ntext 与text数据类型类似 存储在其中的数据通常是直接能输出到显示设备上的字符 即存储Unicode字符数据 6 货币型数据 1 money 用于存储货币值 2 smallmoney 与money数据类型类似 其存储范围为 214748 3648到214748 3647 7 位数据bit 称为位数据类型 其数据有两种取值 0和1 长度为1字节 8 二进制型数据 1 binary n 用于存储固定长度的二进制数据 2 varbinary n 用于存储可变长度的二进制数据 3 image 用于存储可变长度的二进制数据 常用于存储图形类数据 比如照片 图片等 3 2 1SQLServer数据类型 3 2 2SQLServer中的常量 SQLServer主要有以下几种类型的常量 整型常量 例如 34 890实型常量 例如 56 78 789 675 1 2E 5字符型常量 例如 abCFR12 张大友 日期型常量 例如 1987 6 23 或者 6 23 1987 5 数据表字段专用的逻辑表达式 1 判断字段的取值范围 字段名between例如 工资between2590and5600 字段名notbetween例如 工资notbetween2590and5600 2 判断字段的离散取值 字段名in 例如 姓名in 张大有 李芳芳 吴军 孙晓丽 字段名notin 例如 姓名notin 张大有 李芳芳 吴军 3 判断字符型字段相匹配 字段名like 字符串 例如 姓名like 王 职称like 工程师 字段名notlike 字符串 例如 姓名notlike 王 4 判断字段是否为空值 字段名isnull 例如 中文成绩isnull 字段名isnotnull 例如 中文成绩isnotnull 3 2 3SQLServer中的表达式 3 2 4SQLServer中的常用函数 1 数学函数常用的数学函数有 1 abs 数学表达式 返回指定数学表达式值的绝对值 2 rand 返回 0 1 之内一个随机数 3 sqrt 数学表达式 返回指定数学表达式值的算术平方根 2 字符函数常用的字符函数有 1 lower 字符串 将指定字符串中的大写字母转为小写字母 2 upper 字符串 将指定字符串中的小写字母转为大写字母 3 len 字符串 返回指定字符串的长度 4 left 字符串 n 返回指定字符串的前n个字符组成的字符串 5 right 字符串 n 返回指定字符串的后n个字符组成的字符串 3 日期函数常用的日期函数有 1 getdate 返回当前系统日期和时间 2 year 日期 返回指定日期中的年 3 month 日期 返回指定日期中的月 4 day 日期 返回指定日期中的天 5 datediff day 日期1 日期2 返回日期2与日期1之间相差的天数 4 转换函数cast 表达式as目标类型 将指定表达式的值转为指定的类型 并返回转后的值 3 2 4SQLServer中的常用函数 3 3SQLServer数据表 1 数据表的概述表是关系模型中表示实体的方式 是用来组织和存储数据 具有行列结构的数据库对象 表是由定义的列数和可变的行数组成的逻辑结构 以二维表格形式显示 2 数据表的组成数据表有表名确定 其由数据表结构和数据表内容两部分组成 3 数据表结构数据表结构包括 表由几列 几个字段 组成以及每个字段的字段名 字段类型 字段大小和字段约束的定义等 字段约束包括 该字段是否定义为主键 是否允许取空值 取值应满足的条件 取值是唯一 字段的默认值和是否定义为外键等 4 数据表内容数据表内容是指该表中所有记录的集合 如表3 1 表3 2 3 3SQLServer数据表 数据表的三个键 用来标识表中记录的列或列的组合称为关键字 分为主键 唯一键和外键主键 每个表只能定义一个主键 它是表中记录的标识 主键列可以由一个或多个列组成 主键值不能为空 主键值不重复 若主键值是多列组成时 某一列的值可以重复 但多列组合后的值不能重复 image和text类型的列不能作主键 例如 销售人员表的 工号 列是该表的主键 数据表的三个键 唯一键唯一键是表中没有被选为主键的关键字 它限定了除主键以外的列或多列值的不重复 同样保证了数据的唯一性 和主键的区别如下 每个表可以有多个唯一键 唯一键的列值可以为空 但只能有一个空 例如 销售人员表的 电话 列 可以作为该表的唯一键 即只要有号码则号码不同 数据表的三个键 外键外键在另一个表中是关键字 外键是用来建立数据库中多个表之间的关联的 外键列可以由一列或多列组成 外键列的取值可以为空 可以重复 但必须是它所引用列 参照列 的值之一 引用列必须是其所在表的主键或唯一键 例如 销售人员表的 部门号 列 可以作为该表的外键 它所引用的列在部门表中是主键 并且销售人员表和部门人员表中对应行的该列值相等 3 4创建SQLServer数据表 3 4 1利用SQLServerManagementStudio创建数据表1 在 对象资源管理器 窗口中展开 数据库 节点 再展开新建表所属的数据库名 比如Student 右击其 表 节点 出现弹出单 如图3 1所示 2 在弹出菜单中执行 新建表 命令 打开 表设计器 窗口 如图3 2所示3 可右击指定的列名 在出现的弹出菜单中 执行 设置主键 命令设置该指定列为主键 4 定义好所有列之后 单击工具栏上的 保存 按钮 保存表 完成表的创建 如图3 3所示 3 4 1利用SQLServerManagementStudio创建数据表 3 3SQLServer数据表 3 4 2利用SQL命令创建数据表 语法格式 CREATETABLE 数据库名 架构名 新建的表名 字段名字段类型 NULL NOTNULL DEFAULT字段的默认值表达式 PRIMARYKEY UNIQUE FOREIGNKEY REFERENCES主键表 主键表的主键列 CHECK 字段的约束条件表达式 n 例3 1 创建上述数据库student中的学生成绩表sgrade1 上机实践 UsestudentGoCREATETABLEsgrade1 xhchar 6 PRIMARYKEY xmvarchar 8 NOTNULL xbchar 2 NOTNULLDEFAULT 男 rxsjdateNOTNULL szxvarchar 20 NOTNULL bjvarchar 20 NOTNULL zwdecimal 4 1 CHECK zw 0andzw 0andyw 0andsx 100 3 4 2利用SQL命令创建数据表 3 5数据表的修改与删除 3 5 1利用SQLServerManagementStudio修改数据表1 修改数据表 1 在 对象资源管理器 窗口中 展开 数据库 节点 再展开要修改的表所属的数据库名 比如Student 再展开其 表 节点 右击要修改的表名称 出现弹出菜单 如图3 4所示 2 在弹出菜单中执行 设计 命令 进入 表设计器 窗口 可以利用图形化工具完成增加 删除字段和修改有关字段的属性 还可以右击任意列名 通过弹出菜单中的有关约束设置命令 如图3 5所示 3 5 1利用SQLServerManagementStudio修改数据表 2 数据表的删除 重命名与查看属性 1 在 对象资源管理器 窗口中展开 数据库 节点 再展开其 表 节点 右击要删除或重命名或查看属性的表名称 出现弹出菜单 如图3 6所示 2 执行弹出菜单中的 删除 命令 可删除数据表 3 执行弹出菜单中的 重命名 命令 可重命名数据表 4 执行弹出菜单中的 属性 命令 可查看数据表的属性 3 5 1利用SQLServerManagementStudio修改数据表 3 5 2利用SQL命令修改数据表 1 查看数据表的信息语法格式 EXECsp help table name 2 向数据表中添加一列语法格式 ALTERTABLE表名ADD字段名字段类型 NULL NOTNULL DEFAULT字段的默认值表达式 PRIMARYKEY UNIQUE FOREIGNKEY REFERENCES主键表 主键表的主键列 CHECK 字段的约束条件表达式 3 5 2利用SQL命令修改数据表 上机实操 例3 2 于上述学生成绩表sgrade中 添加一年龄字段nl 取值范围为18岁到25岁 UsestudentGoALTERTABLEsgradeADDnltinyintNOTNULLCHECK nl 18andnl 25 Go 3 修改数据表中某一列的类型或大小或是否取空值语法格式 ALTERTABLE表名ALTERCOLUMN字段名修改后字段类型 NULL NOTNULL 例3 3 于上述学生成绩表sgrade中 将字段xm的宽度改为8UseStudentGoALTERTABLEsgradeALTERCOLUMNxmchar 8 Go 3 5 2利用SQL命令修改数据表 4 删除数据表中的某一列语法格式 ALTERTABLE表名DROPCOLUMN列名Go 例3 4 于上述学生成绩表sgrade中 删除字段nlUseStudentGoALTERTABLEsgradeDROPCOLUMNnlGo 3 5 2利用SQL命令修改数据表 上机实操 用SQL语句实现 1 于上述学生成绩表sgrade中 添加一 民族 字段mz字符类型 长度8 默认为 汉族 2 于上述学生成绩表sgrade中 将字段mz的宽度改为123 于上述学生成绩表sgrade中 删除字段mz 1 于上述学生成绩表sgrade中 添加一 民族 字段mz字符类型 长度8 默认为 汉族 ALTERTABLEsgradeADDmzchar 8 NOTNULLdefault 汉族 2 于上述学生成绩表sgrade中 将字段mz的宽度改为12ALTERTABLEsgradeALTERCOLUMNxmchar 8 3 于上述学生成绩表sgrade中 删除字段mzALTERTABLEsgradeDROPCOLUMNmz 5 删除数据表语法格式 DROPTable表名 例3 5 删除数据库Student中的数据表sgrade1 UseStudentGoDROPTablesgrade1Go 3 5 2利用SQL命令修改数据表 6 复制数据表结构语法格式 Select字段名表 Into目的数据表From源数据表Where0 1 例3 6 在上述数据库Student中 复制数据表sgrade的表结构成为一新表sgrade2 要求只复制xh xm zw yw sx这5个字段 UseStudentGoSelectxh xm zw yw sxIntosgrade2FromsgradeWhere0 1Go 3 5 2利用SQL命令修改数据表 3 6创建数据表的约束 3 6 1约束的类型1 PRIMARYKEY 主键 约束主键约束实现数据的实体完整性 它是唯一标识 2 UNIQUE 唯一 约束唯一约束实现数据的实体完整性 限制表的非主键列中不允许输入重复值 3 NOTNULL 非空 约束非空约束实现数据的域完整性 它用于设定某列值不能为空 4 CHECK 检查 约束检查约束强制域完整性 可以接受的数据范围 5 DEFAULT 默认 约束默认约束强制域完整性 它为表中某列建立一个默认值 6 FOREIGNKEY 外键 约束外键是指一个表中的一列或列组合 它虽不是该表的主键 但却是另外一个表的主键 维护两表之间数据的一致性关系 3 6 2利用SQLServerManagementStudio建立约束 1 创建主键约束 1 打开 表设计器 对话框 2 在表设计器对话框中 选定要设置主键的列或多个列组合 比如 学号 课程号 注意 选定多个列时 可按住Ctrl键进行选定 然后于选定的列上右击 出现弹出菜单 执行菜单中的 设置主键 命令即可 如图3 7所示 2 创建唯一约束 1 打开 表设计器 对话框 在表设计对话框中右击任意字段 执行弹出菜单中的 索引 键 命令 打开 索引 键 对话框 2 在索引 键对话框中 单击 添加 按钮 系统给出默认的唯一约束名称 IX 课程表 显示在 选定的主 唯一键或索引 列表中 如图3 8所示 3 6 2利用SQLServerManagementStudio建立约束 3 创建检查约束 1 打开 表设计器 对话框 于表设计对话框中右击任意字段 执行弹出菜单中的 CHECK约束 命令 打开 CHECK约束 对话框 2 在CHECK约束对话框中 单击 添加 按钮 系统给出默认的CHECK约束名 CK 学生表 显示在 选定的CHECK约束 列表如图3 11所示 3 6 2利用SQLServerManagementStudio建立约束 3 选择CHECK约束名 CK 学生表 可在其右侧的 属性 窗口中 通过 标识 项中的 名称 属性 修改约束名称 通过单击 常规 项中的 表达式 属性 在其对应的文本输入框中 输入约束条件表达式 或者通过其右侧出现的 按钮 打开 CHECK约束表达式 对话框 在其中输入约束条件表达式 如图3 12所示 3 6 2利用SQLServerManagementStudio建立约束 4 创建外键约束 1 打开 表设计器 对话框 于表设计对话框中右击任意字段 执行弹出菜单中的 关系 命令 打开 外键关系 对话框 2 在外键关系对话框中 单击 添加 按钮 系统给出默认的外键约束名 FK 教师教课表 教师教课表 显示在 选定的关系 列表中 如图3 13所示 3 6 2利用SQLServerManagementStudio建立约束 3 6 3利用SQL命令建立约束 1 设置数据表的主键语法格式 ALTERTABLE表名ADDCONSTRAINT约束名PRIMARYKEY 字段 2 设置字段的默认值语法格式 ALTERTABLE表名ADDCONSTRAINT约束名DEFAULT表达式FOR字段3 设置字段只取唯一值 不取重复值语法格式 ALTERTABLE表名ADDCONSTRAINT约束名UNIQUE 字段 3 6 3利用SQL命令建立约束 4 设置字段取值限制的约束条件语法格式 ALTERTABLE表名ADDCONSTRAINT约束名CHECK 字段条件表达式 5 设置数据表的外键语法格式 ALTERTABLE表名ADDCONSTRAINT约束名 FOREIGNKEY 外键列 REFERENCES对应的主键表 该主键表的主键列 3 6 4查看和删除约束 1 利用SQLServerManagementStudio查看约束 略 2 利用SQL命令查看约束语法格式 EXECsp helptext约束名3 利用SQLServerManagementStudio删除约束 略 4 利用SQL命令删除表的约束语法格式 ALTERTABLE表名DROPCONSTRAINT约束名 3 7为数据表创建IDENTITY列 1 打开 表设计器 窗口 2 在表设计器窗口中 添加一列 列名 比如为ID 数据类型为int 然后 在其下面的 列属性 窗口中 展开 标识规范 属性 设置其 是标识 属性值为 是 修改其 标识种子 属性值 比如100 其 标识增量 属性值 比如1 这样即可创建一个IDENTITY列 如图3 16所示 3 8更新数据表的内容 3 8 1利用SQLServerManagementStudio创建表内容1 利用SQLServerManagementStudio创建表和内容 步骤略 由学生参照课本66页完成 2 利用SQLServerManagementStudio查看和更新表内容 步骤略 由学生参照课本66 69页完成 3 8 2利用SQL命令更新表内容 1 向数据表中添加单个记录语法格式 Insertinto表名 字段名列表 Values 字段值列表 其中 字段值列表 中的各个字段值表达式与 字段名列表 中的各个字段 从个数 类型和顺序必须一一对应 各个字段名之间和各个字段值之间都是用逗号隔开 若 字段名列表 缺省 指数据表的所有字段 3 8 2利用SQL命令更新表内容 例3 14 在上述数据库Teaching的学生成绩表sgrade中添加3条记录 UseStudentGoInsertintosgradevalues 100011 孙阳 女 2009 9 1 计算机系 09网络 89 56 76 8 Insertintosgradevalues 100013 解晓东 男 2008 9 1 电气系 08机电 98 7 67 76 Insertintosgradevalues 100019 张大名 男 2007 9 1 机械系 07制造 80 50 70 Go 2 修改数据表中的记录内容语法格式 UPDATE表名SET字段 表达式 n WHERE条件表达式 其中 WHERE条件表达式 缺省 指数据表的所有记录 3 8 2利用SQL命令更新表内容 例3 15 在上述数据库Student的学生成绩表sgrade中 把中文 英文和数学都不及格的学生记录的中文增加5分 英文提高10 数学改为及格 UseStudentGoUpdatesgradeSetzw zw 5 yw 1 1 yw sx 60Wherezw 60andyw 60andsx 60Go 3 8 2利用SQL命令更新表内容 3 用一个 或多个 表的内容来更新另一个表的内容语法格式 UPDATE目的表SET字段 表达式 n From源表 n Where表之间的连接条件表达式 and其他条件表达式 3 8 2利用SQL命令更新表内容 例3 16 设数据库Teaching中有如下三个数据表 3 8 2利用SQL命令更新表内容 其中各个字段名的意义为 xh 学号 xm 姓名 szx 所在系 zw 中文 yw 英文 sx 数学 jsj 计算机基础 请把表sgrade1和表sgrade2的内容分别汇总到表total grade中 并同时计算出每个学生的中文 英文 数学 计算机基础4门课的总分填到字段sum中 3 8 2利用SQL命令更新表内容 UseStudentGoUpdatetotal gradeSetzw sgrade1 zw yw sgrade1 yw sx sgrade2 sx jsj sgrade2 jsj sum zw yw sx jsjFromsgrade1 sgrade2Wheretotal grade xh sgrade1 xhANDtotal grade xh sgrade2 xhGo 4 删除数据表中的记录语法格式 DELETEFROM表名 Where条件表达式 例3 17 在数据库Student的学生成绩表sgrade中 将入学4年或者4年以上的学生记录删除 UseStudentGoDeleteFromsgradeWhereyear getdate year rxsj 4Go5 清空数据表内容语法格式 TRUNCATETABLE表名 3 8 2利用SQL命令更新表内容 3 9任务实现 1 创建数据库Student中的各个数据表 以创建学生信息表为例 1 创建学生信息表UseStudentGoCreateTablestab xhchar 10 PRIMARYKEY xmvarchar 8 NOTNULL xbchar 2 NOTNULLDEFAULT 男 csrqdateNOTNULL rxsjdateNOTNULL ssxvarchar 20 NOTNULL bjvarchar 20 NOTNULL dhchar 11 Go 3 9任务实现 2 输入数据库Student中各个数据表的内容利用SQLServerManagementStudio输入数据库Student中的各个数据表的记录内容 略 学生自己完成 3 向数据库Student中的有关数据表中添加新的记录4 调整数据库Student中有关课程的学时量和收费标准5 从数据库Student中删除有关学生记录和有关教师记录 3 9任务实现