数据库与数据管理技术PPT课件

4 2SQLServer中的数据库和表的操作 SQLServer简介数据库的创建与修改表的管理和使用数据库完整性设计 SQLServer简介 SQLServer是微软公司推出的一个关系型数据库管理系统 支持多种操作系统平台 性能可靠 易于使用 SQLServer提供两种方式来管理数据库 第一种方式是通过 ManagementStudio 提供的图形管理界面创建 修改和删除数据库对象 第二种方式使用T SQL语句来管理数据库对象 T SQL语句的语法规则复杂和严格 非专业人员不易使用 实验室SQLServerd的使用1 启动SQLServerConfigurationManager 一般情况下 右击SQLServer SQLEXPERESS 选择 启动 即可顺利启动此服务 少部分机器出现启动SQLEXPRESS服务不成功 需先选中SQLServer SQLEXPERESS 点右键 选择 属性 再将登录身份设置为内置帐户 本地系统 再执行 启动 命令即可 2 运行SQLServer系统启动SQLExpress服务后 执行SQLServerManagementStudio 在服务器名称下拉组合框中 选择 从本地服务器的数据库引擎中选择SQLEXPRESS服务 备注 其默认的数据库服务是2000版本的 我们需要切换至2005版本的SQLEXPRESS服务 1 几个基本概念 1 数据库与数据表 每个数据库都有一个唯一标识 这个标识就是数据库名称 是数据库的逻辑文件名 数据库名称必须符合SQLServer标识符规则 而且在数据库中的数据库名称中必须是唯一的 每个数据库实例中可以建立若干数据表 2 数据文件 用来存放数据的文件 SQLServer将数据库映射为一组操作系统文件 数据和日志信息从不混合在相同的文件中 而且每个文件仅在一个数据库中使用 文件组是命名的文件集合 用于帮助数据布局和管理任务 例如备份和还原操作 4 2 1数据库的创建与修改 1 主数据文件 Primaryfile 用来存储数据库的数据和数据库的启动信息 每个数据库必须并且只有一个主数据文件 其扩展名为 MDF 2 次要数据文件 Secondaryfile 用来存储数据库的数据 使用辅助数据库文件可以扩展存储空间 如果数据库用一个主数据文件和多个辅助数据文件来存放数据 并将它们放在不同的物理磁盘上 数据库的总容量就是这几个磁盘容量的和 辅助数据文件的扩展名为 NDF SQLServer的数据库文件的三种类型 3 日志文件 Transactionlog 用来存放数据库的事务日志 凡是对数据库进行的增 删 改等操作 都会记录在事务日志文件中 当数据库被破坏时可以利用事务日志文件恢复数据库的数据 每个数据库至少要有一个事务日志文件 事务日志文件的扩展名为 LDF 数据库 数据库对象及文件关系图 3 数据文件组 文件组是SQL另一种形式的容器 文件位置可以很灵活 可以将数据文件存储在不同的地方 然后用文件组把他们作为一个单元来管理 例如 你可以将主数据文件放在一个地方然后将次要数据文件 aa1 ndf aa2 ndf aa3 ndf 如果需要 可将他们放在三个不同的磁盘 如D盘 E盘 F盘 上 然后创建一个文件组 将所有的文件指定到此文件组 数据库与操作系统文件之间的映射 4 系统数据库 数据库分为两类 系统数据库和用户数据库 当安装完成后 系统会自动创建5个系统数据库 其中4个在SQLServerManagementStudio中可见 master model tempb msdb 还有1个逻辑上不单独存在 隐藏的系统数据库Resource master数据库 该数据库记录SQLserver的所有的服务器系统信息 注册账户和密码以及所有的系统设置信息等大量对对系统至关重要的信息 是系统的关键性所在 所以他一旦受到破坏 可能会导致这个系统的瘫痪 model数据库 为用户提供了模板和原型 包含了每一用户数据库所需要的系统表 它的定制结构可以被更改 因为每当用户创建新的数据库时 都是复制model数据库的模板 所以所作的所有更改都将反映到用户数据库当中 tempdb数据库 此数据库保存所有的临时性表和临时存储过程 并满足任何其他的临时存储要求 tempdb数据库是全局资源 在每次启动时都重新创建 因此该数据库在系统启动时总是空白的 msdb数据库 此数据库供SQLserver代理程序调度报警和作业调度等活动 Resource数据库 是一个只读的数据库 它包含了SQLServer中的所有系统对象 系统对象在物理上保存在Resource数据库文件中 在逻辑上显示在每个数据库的sys架构中 2 启动与关闭SQLServer的服务 开始 控制面板 管理工具 服务右击SQLServer MSSQLSERVER 单击启动 停止 3 SQLServerManagementStudio SSMS 数据管理平台 的使用 1 启动SQLServerManagementStudio开始 所有程序 MicrosoftSQLServer2008R2 SQLServerManagementStudio 2 SQLServerManagementStudio工作环境 SQLServerManagementStudio是一个集成开发环境 提供了访问 配置 管理和维护SQLServer的工具 默认情况下主窗口中展现对象资源管理器子窗口 菜单栏和工具栏 工具栏包含标准和查询设计器工具条 通过 查看 菜单中的 其他窗口 菜单项可以打开其它子窗口 通过 查看 菜单中的 工具栏 菜单项可以显示或隐藏其它工具栏 1 对象资源管理器 对象资源管理器采用树形结构表示数据库实例的拥有对象和它们之间的从属关系 对象的根节点就是实例本身 树中每个节点代表一个数据库对象 叶节点是最小的数据库对象 2 SQLQuery标签页 一个查询编辑器 用于编写Transact SQL MDX XMLA XML SQLServer2005MobileEdition查询和SQLCMD命令 对象资源管理器 工具栏 医生基本情况表 SQLQuery标签页 4 数据库创建与修改 数据库设计构建E R模型构建逻辑模型E R模型转换为数据库的逻辑模型 并规范化设计 物理模型设计与实现 医生数据库概念模型设计通过实际调查 需求分析和系统分析 确定数据库中的基础实体 在医院中 对人员的管理是各项业务运作的基础 因此 该数据库需要3个基本实体 医生 科室 专家 从人员管理和门诊排班的需求出发 由医院是由各部门组成 每个部门是由人员组成 门诊排班由医生组成门诊专家的管理结构关系 可以确定医生数据库中有基础数据表 科室表 医生表 专家表 例 建立一个医生管理系统 其中医生 科室和专家分别为三个实体 逻辑模型设计 确定数据表的结构根据每张表要存储的实体的属性 建立表的结构描述 按照最小冗余度原则确定各表的属性 由3个实体的E R图对应有医生数据库3张表的逻辑结构 三个实体中 各自有不同的属性 且各实体之间存在一定的联系 医生关系模式 医生工号 姓名 性别 出生日期 最高学历 职务 照片 科室代码 科室关系模式 科室代号 科室名称 人数 成立时间 专家关系模式 专家工号 职称 门诊时间 挂号费 专长 基础数据表逻辑结构的描述 专家表结构 医生表结构 科室表结构 主关键字 医生关系模式 医生工号 姓名 性别 出生日期 最高学历 职务 照片 科室代码 专家关系模式 专家工号 职称 门诊时间 挂号费 专长 科室关系模式 科室代号 科室名称 人数 成立时间 启动SQLServerManagementStudio在 ManagementStudio 管理控制台 新建数据库 窗口定义 医生数据库 在 对象资源管理器 窗口中 右击 数据库 节点 选择 新建数据库 菜单项 在弹出的 新建数据库 窗口定义数据库名称 设置数据文件存放位置 单击 确定 按钮 1 创建数据库 2 修改数据库 修改数据库在 对象资源管理器 窗口中 右击 医生数据库 节点 选择 属性 菜单项 在弹出 数据库属性 医生数据库 窗口修改数据库的属性和添加数据库文件 3 删除数据库 删除数据库在 医生数据库 节点上单击鼠标右键 在弹出的快捷菜单中 单击 删除 菜单项 在弹出的 删除对象 对话框右侧的 删除对象 列表中选中 医生数据库 单击 确定 按钮 删除成功后将自动关闭对话框 5 分离和附加数据库 为了将数据库从一个实例 服务器或应用转到另一个实例 服务器或应用 需要将数据库进行分离与附加操作 1 分离数据库 右击 医生数据库 节点 任务 分离 确定 2 附加数据库 右击 数据库 节点 附加 选择数据文件 确定 确定 4 2 2数据表的管理和使用 数据库中数据的组织层次 字段是数据库中有意义的最小的数据单位 SQLServer表与关系模型的对应 1 表的基础知识 1 数据类型1 字符数据类型2 日期和时间数据类型3 精确数字数据类型4 近似数字数据类型5 货币数据类型6 二进制数据类型7 专用数据类型8 NULL 2 SQLServer表的类型 按照数据存储的时间分类 可以分为永久表和临时表 永久表的数据建立后 除非人工删除 否则一直保存 在master model msdb系统数据表和用户数据库中建立的表都是永久表 临时表的数据只在数据库运行期间临时保存数据 在tempdb数据库建立的表为临时表 按照表的用途分类 可以分为系统表和用户表两类 用户表是用户创建的 用于开发各种数据库应用系统的表 通常用户创建的表就是永久的用户表 系统表是维护SQLServer服务器和数据库正常工作的数据表 每个数据库都会建立很多系统表 系统表不允许用户进行更改 只能由数据库系统自行维护 SQLServer中的系统表是只读的 这样就可以避免用户对系统表的破坏 3 索引 索引是对数据库表中一列或多列的值进行排序的一种结构 使用索引可以快速访问数据库表中的特定信息 索引提供指向存储在表的指定列中的数据值的指针 然后根据用户指定的排序顺序对这些指针排序 数据库使用索引的方式是 搜索索引以找到特定值 然后顺指针找到包含该值的行 4 约束 约束是确保数据库中的数据按所需要的形式存在的一种方法 约束可用来定义数据格式的规则 确保数据唯一性 保证数据准确性 保证多表间的列的完整性 在SQLServer中共有5种约束 非空 NotNULL 这种约束用来迫使用户一定要在表中指定列中输入一个值 每个表中可以有多个非空约束 检查 Check 检查约束用来指定一个布尔操作 用来限制输入到表中的值 唯一性 Unique 唯一性约束用来列中只能输入一个唯一的值 即该列不能有重复值 但可以为空 主键 PrimaryKey 主键约束用来建立一列或多列的组合来唯一标识每条记录 主键可以保证实体完整性 设为主键的字段 其值不能重复 也不能为空 并且一张表中只能有一个主键 外键 ForeignKey 外键约束用来在两张表中建立一个链接 当一个表中作为主键的一列被添加到另一个表中 链接就建立了 外键约束的主要功能是阻止用户键入在另一张表中没有相关行的数据到表中 创建表的步骤 1 定义表结构 给表的每一列取字段名 并确定每一列的数据类型 数据长度 列数据是否可以为空等 2 设置约束 设置约束是为了限制该列输入值的取值范围 以保证输入数据的正确性和一致性 3 添加数据 表结构建立完成之后 就可以向表中输入数据了 2 表的创建 ManagementStudio 中的表设计器由表和列属性两个标签页组成 表标签页的名称随着表的名字而变化 新建表的默认名称为 dbo Table 1 在表标签页中可以定义各列的列名 数据类型和允许空3个主要的属性 列属性标签页中包括其它列属性的设置 其中 默认值或绑定 是指当向表中插入或修改记录时 如果未对列赋值 系统会自动为此列赋予此处定义的默认值 说明 是对此列的注释描述 上部网格中列出了表中的所有字段 以及它们的列名 数据类型和是否允许为空 在上部网格中还可以设置某个字段为主关键字 下部标签显示上部网格中被选中的字段的属性和说明 下部的标签被分成两部分 用于定义表的各种不同属性 表设计器 列名是可编辑的文本框 中文版SQLServer允许输入中文列名 数据类型是下拉列表框 用来选择列的数据类型 以及编辑char varchar类型数据的长度和numeric decimal类型数据的精度 允许空是复选框 选中表示该列在插入和修改记录时可以不赋值 否则必须赋予明确的取值 1 在 对象资源管理器 窗口 展开已建立的数据库节点 右击 表 节点 单击快捷菜单的 新建表 N 命令项 2 系统在 ManagementStudio 窗口的右边打开名为 dbo Table 1 的标签页 表示未被保存 同时工具栏中自动增加表设计器工具条 在主菜单中自动增加表设计器的菜单项 在 列名 列中输入列名 在 数据类型 下拉列表中选择数据类型并且修改长度或精度 在 允许空 复选框中选择该列是否允许空值 在 列属性 标签页中输入各列的说明 3 单击标准工具条中 保存 按钮 将会弹出 选择名称 对话框 在文本框输入表的名字 4 单击 确定 按钮 完成表的创建 此时在 数据库的表节点下增加了一个子节点 表 同时相应的 表 标签页名称变为 dbo 表 3 修改表结构 1 工具 选项 Designers 表设计器和数据库设计器观察 表选项 中 阻止保存要求重新创建表的更改 前的复选框 2 在 对象资源管理器 窗口 展开 数据库节点 右击 dbo 表 节点 单击快捷菜单的 设计 选项 3 修改表结构 修改完成后 保存结果 4 如果要删除表 则右击选定的表 单击快捷菜单的 删除 D 命令项 4 记录的操作 1 添加或修改记录展开 数据库节点 右击 dbo 表 节点 单击快捷菜单的 编辑前200行 选项 在标签页单元格中输入和编辑记录 2 删除记录用鼠标右键单击记录行最左边灰色方格 弹出快捷菜单 单击 删除 选项 会弹出提示删除信息对话框 单击 是 按钮删除所选的记录 3 浏览记录展开 数据库节点 右击 dbo 表 节点 单击快捷菜单的 选择前1000行 选项 4 2 3数据库完整性设计 1 数据完整性SQLServer将数据完整性分为实体完整性 域完整性 引用完整性和用户定义完整性 1 实体完整性的对象是表中的行 记录 将行定义为表中的唯一实体 实体完整性通过定义表的 主键 或 唯一索引 来实现 2 域完整性的对象是表的列 属性 指特定列取值的有效范围 域完整性可通过为列声明数据类型 为列定义默认值 规则和约束来实现 3 引用完整性的对象是表与表之间的关系 以主键与外键之间的关系为基础 引用完整性确保键值在所有表中是一致的 4 用户定义完整性 SQLServer允许数据库使用者根据应用处理的需求编写规则 默认和约束来保证数据的完整性 2 主键和外键 概念回顾主键是唯一能够区分表中每一行记录的一个或多个列 同一个表中不能存在完全相同的两条记录 外键是表的一个或多个列 是其它表的主键 1 创建 移除主键 展开 数据库