Access和SQLServer数据库基础.ppt

第7章Access和SQLServer数据库基础 本章内容 关系型数据库 Access2003使用基础 SQLServer2008使用基础 在VisualStudio中创建SQLServer数据库 本章重点 SQLServer2008使用基础 包括数据库和表的创建 常用T SQL语句的使用 存储过程 数据库的分离和附加 7 1关系型数据库 1 关系型数据库的分类为了更有效地使用关系型数据库 通常将其设计为不同的类型 而且在具体创建数据库的过程中还应遵循一定的规则 以使数据库中的数据更便于查询和使用 关系型数据库一般可分为两类 一类是桌面数据库 如Access FoxPro Excel等 另一类是客户 服务器数据库 如SQLServer Oracle等 2 表 记录和字段表 Table 由若干用于描述客观对象多个特征的记录 Record 也称为行Row 组成 表中每一列 Column 表示客观对象的同一特征 也称为字段 Field 举例见表7 1 表7 2和表7 3 3 关键字关键字是表中某个或多个字段 可以是唯一的 也可以是非唯一的 唯一关键字可以指定为主关键字 用来唯一标识一个记录 例如 学号 可以指定为主关键字 也称为 主键 因为它唯一地标识了一个学生基本情况记录 4 索引为了更快地访问数据 大多数数据库都使用关键字对表进行索引 也就是按关键字对数据库进行排序 并建立一张索引表 每个索引输入项都指向该记录在数据库中的行 5 关系数据库中可以包含多张表 表与表之间可以用不同的方式相互关联 这样做可以充分利用数据库中现有数据 减少数据的冗余 7 2常用数据库产品简介 1 MicrosoftSQLServer数据库管理系统2 Oracle数据库管理系统3 MicrosoftAccess数据库管理系统4 MySQL数据库管理系统 7 3Access2003使用基础 7 3 1创建Access数据库Access的数据库是一个容器 用于存储数据库应用系统的其他对象 Access数据库文件的扩展名是 mdb 创建一个Access数据库 就创建了一个以扩展名为 mdb的文件 并存储了所有数据库子对象管理的细节 例7 1 创建一个Student数据库 7 3 2Access表1 表结构表结构主要包括 字段名称 数据类型和字段属性设置 1 字段名称字段即表的列 字段名称即表的列名称 字段名称是用来标识列的 字段名称可以是英文名称 也可以是汉语名称 但都得符合Access数据库对象命名的规则 字段名称可以包含1 64个字符 字段名称可以包含字母 数字 汉字和空格 以及其他特殊符号 除了 惊叹号 方括号 之外 字段名称不能使用前导空格或控制符 即ASCII码值为0 31的字符 字段名称不能以空格字符开头 2 数据类型可以根据不同字段的需要 选择合适的数据类型 Access提供了10种数据类型 见表7 4 3 字段属性字段属性不是必选的 字段属性分为两组 常规 和 查阅 不同的数据类型 每个分组的选项不一样 常规 属性 常规 选项卡 常用的 常规 属性有以下几种 字段大小只有当字段的数据类型是文本和数字时 字段大小属性才可以设置 如果是文本型字段 则字段大小用来设定文本的长度 取值在1 255之间 默认值是50 如果是数字型字段 则字段又可以选择多种子类型 见表7 5 格式 格式 属性用于对不同数据类型字段使用不同的设置 没有默认值 见表7 6 输入法模式输入法模式属性仅针对文本数据类型的字段有效 有3个选项 随意 输入法开启 输入法关闭 分别表示 保持原汉字输入法状态 启动汉字输入法和关闭汉字输入法 默认值是输入法开启 输入掩码输入掩码属性可以使数据的输入变得更为容易 并且可以控制用户在文本框类型控件中的输入值 定义输入掩码属性需要用到多种字符 见表7 7 标题标题属性值用以取代字段名称在显示表中数据时显示的内容 也就是给字段名称起了个别名 没有默认值 例如 若表的字段名称是 name 它的标题属性值为 姓名 则在显示记录时 该字段的显示名称为 姓名 默认值默认值属性可以在用户没有输入数据时 自动获取指定数据 没有默认值 例如 性别字段可以设置默认值属性为 男 当用户没有输入性别数据时 系统将自动赋值 男 有效性规则有效性规则属性用于指定对输入到字段中的数据的特殊规定取值 如果输入的数据违反该规则 则会向用户提示出错信息 不允许用户继续操作 没有默认值 例如 性别字段的有效性规则可以设置为 男 Or 女 这样 用户只能在该字段中输入 男 或 女 不能输入其他值 有效性文本当用户输入的数据违反有效性规则时 需要向用户提示有效性文本设置的信息 有效性文本属性和有效性规则属性原则上应该一起设置 没有默认值 例如 性别字段的有效性文本可以设置为 性别只能是男或女 如果用户输入的数据违反规则 则会向用户提示这个出错信息 必填字段必填字段属性取值只有 是 和 否 两个选项 若选择 是 则表示该字段可以不输入任何数据 若选择 否 则表示该字段必须输入数据 默认值为 否 允许空字符串允许空字符串属性取值也只有 是 和 否 两个选项 若选择 是 则表示该字段可以为空 若选择 否 则表示该字段必须输入数据 不能为空 默认值为 是 索引索引是数据库随机检索的常用手段 它实际上就是记录的关键字与其相应地址的对应表 使用索引可以加快查询速度 并能保证行的唯一性 若字段设置有索引属性 则记录将以该字段的升序或降序显示 索引属性取值有 无 有 有重复 和 有 无重复 3个选项 当选择 无 则该字段没有索引 当选择 有 有重复 则在该字段创建一个非唯一性索引 即该字段取值可以重复 当选择 有 无重复 则在该字段创建一个唯一性索引 即该字段取值不可以重复 默认值是为 无 Unicode压缩Unicode压缩属性取值也仅有 是 和 否 两个选项 当选择 是 表示该字段可以存储和显示多种语言的文本 当选择 否 表示该字段不能存储和显示多种语言的文本 默认值是为 是 小数位数如果字段数据类型是数字 则有小数位数属性 可以让用户定义数字的小数的位数 当然了数字数据必须首先设置为单精度型或双精度型 该属性才有意义 默认值是为 自动 查阅 属性 查询 选项卡 查阅 属性用于对指定数据类型设定窗体显示控件类型 只有文本 数字 是 否这3种数据类型可以设定窗体显示控件类型 可以将这些数据类型的显示设定为文本框 列表框 组合框或复选框 2 创建表3 修改表结构4 设置主键5 向表中输入记录6 设置关系 7 4SQLServer2008使用基础 7 4 1创建SQLServer数据库1 数据库文件SQLServer2008数据库有3种类型的文件 1 主数据文件 Primary 2 二级数据文件 Secondary 3 事务日志文件 2 创建数据库在SQLServerManagementStudio中创建数据库主要有两种方式 一种是在对象资源管理器中创建数据库 另一种是在 查询窗口 中执行T SQL语句创建数据库 1 在对象资源管理器中创建数据库可以在对象资源管理器中的图形界面环境下创建数据库 例7 4 下面以创建名为TestDB数据库为例 介绍在SQLServerManagementStudio中使用对象资源管理器创建数据库的过程 创建数据库的T SQL语句是CREATEDATABASE 其基本语法格式如下 CREATEDATABASEdatabase name ONPRIMARY NAME logical file name FILENAME os file name LOGON NAME logical file name FILENAME os file name 3 删除数据库执行删除数据库操作将从SQLServer实例中删除数据库 并删除该数据库使用的物理磁盘文件 不能删除系统数据库 1 在对象资源管理器中删除数据库 2 在查询编辑器中用T SQL语句删除数据库基本语法格式如下 DROPDATABASEdatabase name 参数说明如下 database name 指定要删除的数据库的名称 例如 要删除TestDB数据库 使用下面代码 DROPDATABASETestDB 7 4 2表的操作1 数据类型类别SQLServer中的数据类型可以归纳为下列类别 精确数值 近似数值 日期和时间 字符串 Unicode字符串 二进制字符串及其他数据类型 在SQLServer中 根据其存储特征不同 某些数据类型被指定为属于下列各组 大值数据类型 varchar max nvarchar max 和varbinary max 大型对象数据类型 text ntext image varchar max nvarchar max varbinary max 和xml 2 创建表在当前打开的数据库中创建表 1 在对象资源管理器中创建表使用SQLServerManagementStudio中的对象资源管理器创建表 2 在查询编辑器中用T SQL语句创建表创建表的T SQL语句是CREATETABLE 其基本语法格式如下 CREATETABLEtable name column nametype name PRIMARYKEY UNIQUE NULL NOTNULL column nametype name NULL NOTNULL 例7 7 用T SQL语句 在StudentDB数据库中按表7 2创建表Course 用T SQL语句在StudentDB数据库中创建Course表的步骤如下 在SQLServerManagementStudio中 单击工具栏中的 新建查询 按钮 在右侧窗格中显示 查询分析器 在查询编辑器中输入如下代码 USEStudentDB 打开数据库StudentDBGOCREATETABLECourse 表名 CourseIDchar 4 PRIMARYKEYNOTNULL 课程编号 主键 非空CourseNamenchar 10 课程名Periodsmallint 学时Creditsmallint 学分 GO 单击查询编辑器上方的 执行 按钮或按F5键 执行创建的表代码 在对象资源管理器中可以看到 StudentDB数据库下已经创建的Course表 单击查询编辑器右上角的 关闭 按钮 关闭查询编辑器 3 修改表 1 修改表结构在对象资源管理器中 右键单击需要修改表的表名 显示快捷菜单 如图7 31所示 从快捷菜单中执行 设计 命令 右侧窗格将显示表设计视图 可以像创建表一样输入表各列的属性 2 修改表名在对象资源管理器中 右键单击需要修改表的表名 从快捷菜单中执行 重命名 命令 则该表名处出现插入点光标 允许输入新的表名 4 删除表在 对象资源管理器 中 右键单击需要删除的表名 从快捷菜单中执行 删除 命令 将删除该表 7 4 3记录的操作记录操作包括向表中插入新记录 修改记录和删除记录 1 通过表记录视图操作记录2 用T SQL添加记录将新行添加到表中的T SQL语句是INSERTINTO 其基本语法格式如下 INSERTINTOtable name column name column name VALUES expression expression 例7 8 用T SQL语句 在StudentDB数据库中 按表7 2中的数据向表Course中添加行 用T SQL语句在StudentDB数据库中向Course表添加记录的步骤如下 在SQLServerManagementStudio中 单击工具栏中的 新建查询 按钮 在右侧窗格中显示查询分析器 在 查询编辑器 中输入如下代码 USEStudentDB 打开数据库StudentDBGOINSERTINTOCourse 指定列名 添加一行 CourseID CourseName Period Credit VALUES 1102 英语1 80 6 GOINSERTINTOCourse 按表中列的顺序 添加3行VALUES 3205 数据结构 70 5 3206 数据库技术 80 6 3208 C 60 4 SELECT FROMCourse 显示Course表中的所有记录GO 3 用T SQL修改记录使用UPDATE语句可以按照某个条件修改表中的现有行的列的值 将新行添加到表中的T SQL语句是UPDATE 其基本语法格式如下 UPDATEtable nameSETcolumn name expression column name expression WHEREsearch condition 例7 9 用T SQL语句 在StudentDB数据库中 修改表StudentInfo Course中的数据 在下面的代码示例中 将StudentInfo表中对符合指定学号的行 更改出生日期和Email列的值 UPDATEStudentInfoSETDateOfBirth 1991 10 28 Email Lisp WHEREStudentID 200902603006 执行的结果如图7 37所示 在下面示例代码中 没有指定WHERE子句 将更新所有行 UPDATECourseSETPeriod Period 10 4 用T SQL删除记录使用DELETE语句可以按照某个条件删除表中的行 将新行添加到表中的T SQL语句是UPDATE 其基本语法格式如下 DELETEFROMtable name WHEREsearch condition 例7 10 使用T SQL语句从StudentMark表中删除Score列的值小于60的所有行 USEStudentDB 打开数据库StudentDBGODELETEFROMStudentMarkWHEREScore 60 7 4 4查询的操作1 SELECT语句查询操作用于从数据库中检索行 并从一个或多个表中选择一个或多个行或列 使用SELECT语句实现查询操作 SELECT语句主要用于从数据库中返回需要的数据集 虽然SELECT语句的完整语法较复杂 但其基本语法格式如下 SELECTselect list INTOnew table FROMtable source list WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC 2 WHERE GROUPBY和HAVING子句的处理顺序以下步骤列出带WHERE子句 GROUPBY子句和HAVING子句的SELECT语句的处理顺序 FROM子句返回初始结果集 WHERE子句排除不满足搜索条件的行 GROUPBY子句将选定的行收集到GROUPBY子句中各个唯一值的组中 选择列表中指定的聚合函数可以计算各组的汇总值 此外 HAVING子句排除不满足搜索条件的行 3 数据库查询需要注意的问题 1 字符串中的单引号SQL语句中的字符串 字符型常量 一定要用单引号 括起来 例如 SELECT FROMStudentInfoWHEREWHEREStudentIDLIKE 200902602 如果要查询的关键字本身含有单引号 则要用两个连续的单引号表示一个单引号 2 数据库空值的处理在程序中 p str 与p strISNull是不同的 在读取数据库时 如果返回值是Null 则把它赋值给String型的变量时就会出错 需要另外判断它是否为空值 为了减少出错 在设计数据库时尽量指定字段为非空并指定默认值 3 ORDERBY子句的使用对查询结果使用ORDERBY子句进行排序时 用来排序的列必须在查询中列出 例如 SELECTStudentID StudentNameFROMStudentInfoGROUPBYStudentID 4 多表连接查询时的问题在对数据库中的多张表查询时 若有字段名重名 则一定要指定该字段的表名或别名 4 SELECT语句示例 1 使用简单FROM子句 例7 11 以下示例从StudentDB数据库的StudentInfo Course StudentMark表中检索数据 在查询编辑器中可以执行代码 查看运行结果 下面语句返回StudentDB数据库的StudentInfo表中的所有行 未指定WHERE子句 和所有列 使用 USEStudentDB 打开数据库StudentDBGOSELECT FROMStudentInfo GO 下面语句返回StudentInfo表的所有行 未指定WHERE子句 和列子集 StudentID StudentName 此外 还添加了一个列标题 并按StudentID升序排列显示 SELECTStudentID StudentNameAS 学生姓名 FROMStudentInfoORDERBYStudentIDASC 下面语句仅返回StudentInfo表中包含1991年6月1日以后出生的女生的那些行 SELECT FROMStudentInfoWHEREDateOfBirth 1991 6 1 ANDSex 女 使用SELECTINTO创建表 下面语句将在StudentDB数据库中创建一个名为 Stu的临时表 临时表中的行满足StudentID列值的前9位是200902602的条件 SELECT INTO Stu 创建名为 Stu的临时表FROMStudentInfoWHEREStudentIDLIKE 200902602 SELECT 显示临时表中的所有行 所有列FROM Stu GODROPTABLE Stu 删除临时表GO 等价关联查询 从StudentInfo Course StudentMark这3个表中选择6列 创建tempStudentMark表 返回多表查询等价关联的行 在查询编辑器中执行结果如图7 39所示 SELECTStudentMark StudentID StudentInfo StudentName StudentMark CourseID Course CourseName StudentMark Score Course CreditINTOtempStudentMarkFROMStudentMarkJOINStudentInfoONStudentMark StudentID StudentInfo StudentIDJOINCourseONStudentMark CourseID Course CourseID GOSELECT 显示生成表中的所有行 所有列FROMtempStudentMark GODROPTABLEtempStudentMark 为了能多次执行 删除生成的表GO 下面语句按产品StudentID将StudentMark表分组 每个学生返回一行 并显示考试门数和平均成绩 在查询编辑器中的执行结果如图7 40所示 SELECTStudentID COUNT StudentID AS 考试门数 AVG Score AS 平均成绩 FROMStudentMarkGROUPBYStudentID 如果要在查询结果显示学号对应的学生姓名 则要采用多表连接 代码如下 SELECTm StudentID i StudentName COUNT m StudentID AS 考试门数 AVG m Score AS 平均成绩 FROMStudentMarkm m为StudentMark别名INNERJOINStudentInfoiONm StudentID i StudentID i为StudentInfo的别名GROUPBYm StudentID i StudentNameORDERBYm StudentID 下面语句显示带聚合函数的HAVING子句 该子句按StudentID将StudentMark表中的行进行分组 并查找平均成绩Score大于等于85的学号 SELECTStudentID AVG Score AS 平均成绩 FROMStudentMarkGROUPBYStudentIDHAVINGAVG Score 85 7 4 5存储过程的操作1 存储过程简介2 使用SQLServerManagementStudio管理存储过程 1 创建存储过程 例7 12 在StudentDB数据库中创建用于显示所找学生信息的存储过程up FindStudentInfo 2 运行存储过程 例7 13 运行存储过程up FindStudentInfo 3 使用T SQL管理存储过程 1 创建存储过程可以使用T SQL语句CREATEPROCEDURE来创建存储过程 其基本语法格式如下 CREATEPROCEDUREprocedure name parameterdata type default OUTPUT READONLY parameterdata type default OUTPUT READONLY AS 2 执行存储过程执行存储过程的基本语法格式如下 EXEC EXECUTE procedure name parameter name value 例7 14 设计一个存储过程 返回所有学生信息 该存储过程不使用任何参数 编写存储过程uspGetAllStudentInfo 代码如下 USEStudentDB GOCREATEPROCEDUREuspGetAllStudentInfoASSELECT FROMStudentInfoORDERBYStudentIDASC GO 例7 15 用存储过程实现把新添加的课程保存到Course表中 课程