SQLServer数据库基础课程(总复习课件).ppt

SQLServer数据库教程从基础到应用 2015 09 05 学习目标 掌握数据库相关基础知识掌握简单的T SQL语句 第0章数据库基础 什么是数据库数据库是指长期储存在计算机内的 有组织的 可共享的数据集合 数据库中的数据按一定的数据模型组织 描述和储存 具有较小的冗余度 较高的数据独立性和易扩展性 并可为各种用户共享 数据库由数据库管理系统 DBMS 统一管理 数据的插入 修改和检索均要通过数据库管理系统进行 数据管理员 DBA 负责创建 监控和维护整个数据库 数据库模型 层次模型网状模型关系模型关系模型是目前最为流行的一种数据模型 用二维表格结构表示实体集 数据库对象 表 由行和列组成每列又称为一个字段 每列的标题称为字段名一行数据称为一个或一条记录一个数据库表由一条或多条记录组成 没有记录的表称为空表 每个表中通常都有一个主关键字 也叫主键 用于唯一地确定一条记录 主键不允许空值 不能存在具有相同的主键值的两个行 字段名 记录 主键 概念数据模型与逻辑数据模型转换 E R图同关系模式转换1 1的联系转换为关系模式 bjb 班级编号 院系 专业 人数 bzb 学号 姓名 syb 学号 班级编号 注意 联系关系模式的属性包括包括联系属性 以及各实体的主码 主码选实体集任意一方主码 1 1 联系 实体 属性 概念数据模型与逻辑数据数据模型转换 1 n联系E R图转换为关系模式 bjb 班级编号 专业 人数 xsb 学号 姓名 性别 专业 syb 学号 班级编号 n 1 概念数据模型逻辑数据数据模型转换 n n联系E R图转换为关系模式 xsb 学号 姓名 性别 专业 kcb 课程编号 课名 学期 学时 学分学分 学时 cjb 学号 课程编号 成绩 1 1 n n 成绩 使用SQL语句创建数据库 一 创建数据库1 利用对象资源管理器创建用户数据库 界面方式 1 选择 开始 程序 MicrosoftSQLServer2012 打开SQLServerManagementStudio 2 使用 Windows身份验证 连接到SQLServer2012数据库实例 3 展开SQLServer实例 右击 数据库 然后在弹出的快捷菜单中选择 新建数据库存 命令 打开 新建数据库 对话框 4 在 新建数据库 对话框中 可以定义数据库的名称 数据文件和日志文件的逻辑名称和路径 文件组 初始大小和增长方式等 输入数据库名称 pxscj student P 29 图 使用SQL语句创建数据库 2 使用T SQL语句创建数据库 命令方式 在SQLServerManagementStudio中 单击标准工具栏的 新建查询 按钮 启动SQL编辑器窗口 在光标处输入T SQL语句 单击 执行 按钮 SQL编辑器就提交用户输入的T SQL语句 然后发送到服务器执行 并返回执行结果 P 35 页例1 创建一个只含一个数据文件和一个事务日志文件的数据库 createdatabasestudent1on name student1 dat filename d student dat mdf size 10 maxsize 50 filegrowth 20 logon name student1 log filename d student log ldf size 4 maxsize 25 filegrowth 5 回顾课本36页例2 7 创建数据库 练习 1 写出创建产品销售数据库cpxs 初始大小为10MB 最大为50MB 增长方式为20 日志文件名初始为4MB 最大为25MB 增长方式为5MB 存储路径为 D SQLServer 2012 data createdatabasecpxson name cpxs dat filename D SQLServer 2012 cpxs dat mdf size 10MB maxsize 50MB filegrowth 20 logon name cpxs log filename D SQLServer 2012 cpxs log ldf size 4MB maxsize 25MB filegrowth 5MB 数据库修改 删除 对数据库可修改以下几项 增加或删除数据文件增加或删除日志文件增加或删除文件组改变数据文件的大小和增长方式改变日志文件的大小和增长方式重命名数据库 数据库修改 删除 界面方式 对象资源管理器 窗口 点击所要修改的数据库 右击鼠标选择 属性 打开 数据库属性 窗口1 修改文件属性 数据库修改 删除 界面方式 数据库修改 删除 界面方式 2 增加辅助数据文件 数据库修改 删除 界面方式 3 增加文件组 输入文件组名称 使用SQL语句修改 删除数据库 命令方式ALTERDATABASE修改数据库ADDFILE FILEGROUP 增加文件 文件组 MODIFYFILE更改文件属性REMOVEFILE文件名删除数据文件DROPDATABASE删除数据库 使用SQL语句修改 删除数据库 课本例子 P38 40页例2 8例2 10例2 11例2 12例2 13 练习 1 修改数据库pxscj的属性 将日志文件的初始大小改为15MB 增长方式改为15 2 为数据库pxscj添加文件组pgroup 并为此文件组添加一个数据文件 大小为25MB 存储路径为 D data 表的创建和操作 表结构 表创建和操作 界面方式 表创建和操作 界面方式 表创建和操作 命令方式 用SQL语句创建如下两个基本表 学生表 Student 课程表 Course 结构如下所示Student表 表创建和操作 CreatetableStudent Snochar 7 primarykey Snamechar 10 notnull Ssexchar 2 check Ssex 男 orSsex 女 Sageintcheck Sagebetween15and45 Sdeptvarchar 20 default 计算机系 表创建和操作 Course表结构 表创建和操作 Createtablecourse Cnochar 10 primarykey Cnamevarchar 20 notnull Ccreditintcheck Scteditbetween0and50 Semesterintcheck Semester 0 Periodintcheck Period 0 表创建和操作 修改表结构 P57页例3 3例3 4P59页图操作表数据 界面方式 表创建和操作 命令方式操作表数据 向表xsb中插入记录INSERTINTOxsbVALUERS 201502 王海 1 19990501 电子商务 50 NULL 201504 李华 1 19990604 电子商务 NULL NULL INSERTINTOxsb 学号 姓名 性别 专业 VALUERS 201502 王海 1 电子商务 表创建和操作 在xsb1中生成计算机专业学生的xsb2 1 INSERTINTOxsb2SELECT FROMxsb1WHERE专业 计算机 2 SELECT INTOxsb2FROMxsb1WHERE专业 计算机 表创建和操作 练习 1 由cjb生成成绩表大于90分的cjb11 INSERTINTOcjb1SELECT FROMcjbWHERE成绩 90分2 SELECT INTOcjb1FROMcjbWHERE成绩 90分 表创建和操作 删除符合条件记录 DELETEFROMxsbWHERE总学分 0GO 数据库的查询 常用术语 关键码 超键在关系模式中 能唯一标识元组的属性集称为超键 SuperKey 候选键如果一个属性集能唯一标识元组 且又不含有多于属性 那么这个属性集被称为候选键 CandidateKey 主键关系模式中用户正在使用的候选键称之为主键 PrimaryKey 外键如果关系模式R中某属性集是其他模式的候选键 那么该属性对模型R而言是外键 数据库对象 外键 存在于B表中的A表的主键 数据库对象 索引 Index 索引是根据指定的数据库表列建立起来的顺序 它提供了快速访问数据的途径 并且可监督表的数据 使其索引所指向的列中的数据不重复 视图 View 是一个虚拟的表 在数据库中并不实际存 视图是由查询数据库表产生的 视图可以用来控制用户对数据的访问 并能简化数据的显示 即通过视图只显示那些需要的数据信息 触发器 Trigger 触发器是一个用户定义的SQL事务命令的集合 当对一个表进行插入 更改 删除时 这组命令就会自动执行 存储过程 StoredProcedure 存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句 经编译后存储在数据库中的SQL程序 SQLServer关系数据库简介 SQLServer是由Microsoft开发和推广的关系数据库管理系统 最初是由Microsoft Sybase和Ashton Tate三家公司共同开发的 并于1988年推出了第一个OS 2版本 SQLServer近年来不断更新版本 1996年 Microsoft推出了SQLServer6 5版本 1998年 SQLServer7 0版本和用户见面 2000年 SQLServer2000推出 2005年 微软发布了SQLServer20052008年 微软发布了最新的SQLServer2008 SQLServer常见版本 企业版 EnterpriseEdition 标准版 StandardEdition 个人版 PersonalEdition 开发者版 DeveloperEdition 安装 运行SQLServer2000的软件需求 SQLServer2000企业版必须运行于安装WindowsNTServerEnterpriseEdition4 0或者Windows2000AdvancedServer以及更高版本的操作系统下 SQLServer2000标准版必须运行于安装WindowsNTServerEnterpriseEdition4 0 WindowsNTServer4 0 Windows2000Server以及更高版本的操作系统下 SQLServer2000个人版可在多种操作系统下运行 如可运行于Windows9x WindowsNT4 0或Windows2000的服务器版或工作站版的操作系统下 SQLServer2000开发者版可运行于上述Windows9x以外的所有操作系统下 SQLServer安装 SQLServer系统数据库简介 Master数据库是SQLServer系统最重要的数据库 它记录了SQLServer系统的所有系统信息 这些系统信息包括所有的登录信息 系统设置信息 SQLServer的初始化信息和其它系统数据库及用户数据库的相关信息 Model数据库是所有用户数据库和Tempdb数据库的模板数据库 它含有Master数据库所有系统表的子集 这些系统数据库是每个用户定义数据库需要的 Msdb数据库是代理服务数据库 为其警报 任务调度和记录操作员的操作提供存储空间 Tempdb是一个临时数据库 默认大小是8M 它为所有的临时表 临时存储过程及其它临时操作提供存储空间 Pubs和Northwind数据库是两个示例数据库 SQLServer系统表简介 1 sysobjects表SQLServer的主系统表sysobjects出现在每个数据库中 它对每个数据库对象含有一行记录 2 syscolumns表系统表syscolumns出现在master数据库和每个用户自定义的数据库中 它对基表或者视图的每个列和存储过程中的每个参数含有一行记录 3 sysindexes表系统表sysindexes出现在master数据库和每个用户自定义的数据库中 它对每个索引和没有聚簇索引的每个表含有一行记录 它还对包括文本 图像数据的每个表含有一行记录 SQLServer系统表简介 4 sysusers表系统表sysusers出现在master数据库和每个用户自定义的数据库中 它对整个数据库中的每个WindowsNT用户 WindowsNT用户组 SQLServer用户或者SQLServer角色含有一行记录 5 sysdatabases表系统表sysdatabases对SQLServer系统上的每个系统数据库和用户自定义的数据库含有一行记录 它只出现在master数据库中 6 sysdepends表系统表Sysdepends对表 视图和存储过程之间的每个依赖关系含有一行记录 它出现在master数据库和每个用户自定义的数据库中 7 sysconstraints表系统表sysconstraints对使用CREATETABLE或者ALTERTABLE语句为数据库对象定义的每个完整性约束含有一行记录 它出现在master数据库和每个用户自定义的数据库中 SQLServer工具和实用程序 企业管理器服务管理器查询分析器事件探查器导入和导出数据联机帮助文档 企业管理器 EnterpriseManager 服务管理器 ServiceManagerManager 查询分析器 QueryAnalyzer 事件探查器 导入和导出数据 联机帮助文档 注册一个新的数据库 链接服务器 备份数据库 还原数据库 分离数据库 附加数据库 数据库维护计划 生成数据库结构脚本 标准SQL语言 SQL StructuredQueryLanguage 语言最早是由Boyce和Chamberlin在1974年提出 起初称之为SEQUEL StructuredEnglishQueryLanguage 设计目的是为了便于对SystemR数据库系统存储数据进行操作 1986年ANSI批准SQL语言作为关系型数据库语言的标准 1987年ISO也通过了这一标准 目前 几乎所有的关系型数据库管理系统都支持SQL语言 Transact SQL语言 数据定义语言 DDL DataDefinitionLanguage 包括创建数据库表结构 创建视图和创建表的索引 以及删除已经存在的表 视图和索引等 数据定义语句的SQL动词是CREATE DROP和ALTER 数据操纵语言 DML DatamanipulationLanguage 主要用于创建数据库表后 查询 输入 删除和修改表中的数据 SQL动词是SELECT INSERT DELETE和UPDATE 数据控制语言 DCL DataControlLanguage 包括数据库事务管理功能和数据保护功能 对数据库提供完整性约束控制 控制数据库的安全性 提供了多用户并发控制和恢复的功能 支持事务提交和回滚等 SQL动词是GRANT和REVOKE 系统存储过程 SystemStoredProcedure 存储过程是SQL语句和流程控制语句的预编译集合 是封装重复任务操作的方法 SQLServer的对象命名约定 三段式名字标识对象 前两者可以省略 数据库名默认是当前数据库 所有者默认式数据库的所有者dbo 如 Pubs dbo authorsNorthwind dbo scNorthwind dbo sc grade 创建练习数据库和表 使用企业管理器添加数据库 创建练习数据库和表 使用企业管理器分别添加表 c s sc 创建练习数据库和表 使用企业管理器添加各个表的记录 c s sc Select语句 查询课程表 查询学生表查询课程表 只显示课程名和教师名 别名AS 使用别名是增加select语句的可读性 语法是 数据表名as数据表别名如 Selecte 员工编号 e 员工姓名from员工表aseSame p teacherfromcasp Union 将两个或两个以上的查询结果合并为一个结果集 语法 Select语句Union all Select语句默认情况下 union将从最终结果集中删除重复的记录 如果希望保留所有记录 使用all关键字 条件查询 Select from表名where查询条件例如 Select fromswheresname wang 模糊查询 Like关键字的提供使我们的模糊查询有了基础 Like关键字后提供了 下划线和 百分号这两种通配符 多表查询 联接查询 Select froms sc得到一个非常大的结果集 s表和sc表的笛卡尔积Select froms scwheres s sc s Select froms sc cwheres s sc s andc c sc c Selectsname cname gradefroms sc cwheres s sc s andc c sc c 查询参加了 缺陷管理 课程考试的同学 Selectsnamefroms sc cwheres s sc s andc c sc c andcname 缺陷管理 多表查询 嵌套查询 查询wang同学的考试成绩 Select fromscwheres selects fromswheresname wang 查询参加了 缺陷管理 课程考试的同学 Select fromscwherec selectc fromcwherecname 缺陷管理 Selectsnamefromswheres in Selects fromscwherec selectc fromcwherecname 缺陷管理 联接查询vs 嵌套查询 Groupby SelectsexfromsgroupbysexSelectcount sexfromsgroupbysexSelectsname sum grade count froms scwheres s sc s groupbysname Having 帮助我们对需要分组的数据做一个过滤 例如 Selectsname sum grade count froms scwheres s sc s groupbysnamehavingsname chen 这样我们就可以把chen的记录过滤掉 Orderby ORDERBY是一个可选的子句 它允许你根据指定要orderby的列来以上升或者下降的顺序来显示查询的结果 例如 ASC AscendingOrder 这个是缺省的DESC DescendingOrderSelect fromscorderbygradedescSelecttop3 fromscorderbygradedesc Join 内联接 INNERJOIN 左联接 LEFTJOIN 右联接 RIGHTJOIN 全联接 FULLJOIN 内联接 INNERJOIN 内联接会返回两个表中所匹配的所有的行 假如Employees中的某些行与Orders中的行不匹配 那些行就不会被列出来 左联接 LEFTJOIN 左联接会从第一个表 Employees 那里返回所有的行 即使在第二个表 Orders 中没有匹配的行 假如Employees中的某些行没有在Orders中可匹配的行 这些行也会被列出来 右联接 RIGHTJOIN 右联接会从第二个表格 Orders 那里返回所有的行 即使在第一个表格中没有可匹配的行 假如Orders中的某些行没有在Employees可匹配的行 那些行也会被列出 全联接 FULLJOIN 全联接会从两个表格 Orders 那里返回所有的行 即使在第一个表格或第二个中没有可匹配的行 Insert语句 INSERTINTOs s sname age sex VALUES S101 jiang 39 M Insertintostudent s sname age sex selects sname age sexfroms Delete语句 DELETEFROMstudentdeletefromstudentwheresname jiang Update语句 UPDATEstudentSETsname chennengji WHEREsname chen andage 22 UPDATEstudentSETage age 1WHEREsname chennengji 事务 事务是单个的工作单元 如果某一事务成功 则在该事务中进行的所有数据更改均会提交 成为数据库中的永久组成部分 如果事务遇到错误且必须取消或回滚 则所有数据更改均被清除 事务操作的语法 BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTIONS