HappyYouDBSSQL2008实验1.doc

浙江工商大学计算机与信息工程学院实验（1）课程名称： 数据库原理 指导教师： 韩培友 实验目的、实验原理和内容：一、实验目的：数据库与数据表的设计与实现。二、实验原理：熟练掌握逻辑结构、逻辑结构和物理结构设计的理论和方法。三、实验内容及要求：1、 熟练掌握设计和创建数据库与数据表的方法。2、分别使用管理工作室和命令语句方式设计并创建指定的数据库与数据表。要求：撰写预备知识、步骤、程序框图、调试好的程序及存在的问题。 具体试验内容：1 SQL Server管理工作室的启动2 利用SQL Server对象资源管理器创建数据库2.1 使用对象资源管理器创建数据库2.2 使用对象资源管理器创建数据表2.3 编辑数据表2.4 编辑数据库3 数据库的分离与附加3.1 分离数据库3.2 附加数据库4 利用SQL Server查询分析器创建数据库和数据表5 数据表内容的编辑5.1 使用SQL Server 管理工作室的对象资源管理器编辑数据表的内容5.2 利用SQL Server 查询分析器编辑数据表的内容实验1 数据库设计实验11 实验内容与要求1实验名称：设计数据库（3学时课外准备）2实验目的：熟练掌握数据库设计的基本技术；熟练掌握数据库设计的每个步骤及其任务和实施方法，理解数据库系统的概念和特点。实验实验原理：DBS需求分析、概念结构、逻辑结构和物理结构设计的理论和方法。4实验内容：结合实际需要，自选微型数据库应用项目进行系统分析和数据库设计。例如：学籍管理系统、图书管理系统、材料管理系统、销售信息系统等。具体内容如下：（1）对应用项目进行需求分析，形成数据流图和数据字典。（2）设计应用项目的概念结构，绘制ER图。（3）设计应用项目的逻辑结构，生成关系模式集合，进行完整性设计和3NF规范化。（4）设计应用项目的外模式，定义视图。（5）设计应用项目的物理结构，确定物理环境，确定索引。（6）系统功能设计。5实验要求：按照实验设计模板，撰写实验报告。具体内容包括：应用项目的内容详述、设计方法、数据流图、数据字典、ER图、关系模式（数据表及其属性）、完整性设计内容（主键、外键及其参照关系）、3NF规范化过程、视图的定义内容、索引内容、操作要求、处理方法、处理流程，系统功能模块图等。6实验思考：对设计进行评价，分析合理和不足之处，给出改进方案、注意事项等。实验12 实验范例1实验实例：已知商品销售数据库ComInfo的关系分别为商品、商店和职工，相应的信息分别为品号、品名、厂商、厂址、单价、数量；店号、店名、店址、电话；工号、姓名、性别、年龄、电话；一个商店可以销售多个商品，一个商品可以在多个商店销售，同时需要给出销售额；一个职工只能在一个商店工作，一个商店可以有多个职工，同时需要给出工资和聘期。职工之间存在领导关系。设计商品销售数据库系统。2实验方法：使用SA设计DFD；使用描述法描述数据字典；使用ER方法绘制ER图；使用ISBL表示关系模式；使用流程图绘制功能模块图；概念模型使用自底向上的方法，即自顶向下需求分析，自底向上设计概念结构。实验实验步骤（1）通过需求分析，形成的商品销售的数据流图如图1所示。数据字典描述如下：1）职工表工号：字符型,6位,主键,非空,取值=0,9,A,Z ,大写字母开头,其它为数字。姓名：字符型，8位，非空。性别：字符型，2位，非空，取值=男，女。年龄：整型，2位，非空，取值=16，96。电话：字符型，12位，取值=0，9。商品 信息职工职工表销售表商店表销售统计登陆商品销售信息商品表商店商品商品 信息商店 信息登陆商店商店 信息职工 信息登陆职工职工 信息登陆职工聘用 信息聘用信息销售 信息统计 结果图1 商品销售数据流图2）商店表店号：字符型，6位，主键，非空，取值=0，9，S,S字母开头，其它为数字。店名：字符型，22位，非空。店址：字符型，36位。电话：字符型，12位，取值=0，9。3）商品表品号：字符型，6位，主键，非空，取值=0，9，P,P字母开头，其它为数字。品名：字符型，22位，非空。厂商：字符型，22位，非空。厂址：字符型36，非空。单价：实数，9位（2位小数）。数量：整型，6位。4）联系属性销售额：实数，12位（2位小数）。职务补：实数，9位（2位小数）。聘期：实数，3位（1位小数）。工资：实数，9位（2位小数）。经理：字符型，6位，非空，取值=0,9，A,Z,大写字母开头，其它为数字。（2）概念结构设计的商品销售ER图如图2所示。商品职工商店销售隶属店名店号电话店址品号品名厂商厂地单价工号姓名性别年龄电话销售额聘期工资领导职务补数量图实验2 商店销售E-R图（3）商品销售的关系模型如下：商品（品号，品名，厂商，厂址，单价，数量）商店（店号，店名，店址，电话）职工（工号，姓名，性别，年龄，电话，店号，工资，聘期，职务补，经理）销售（店号，品号，销售额）（4）完整性设计和3NF规范化完整性设计：满足数据字典的基本要求；职工的外键店号参照商店的店号，职工的外键经理参照职工自身的工号（自联系）；销售的外键店号参照商店的店号，销售的外键品号参照商品的品号。3NF规范化：商品销售的关系模型满足3NF。（5）定义商店销售商品的视图ComView，要求按照商店名、商品名、销售额、经理姓名，给出经理经营商店销售商品的销售信息。（6）物理结构设计：目前标准配置的计算机系统、SQL Server 2008企业版 、一种高级语言；索引使用SQL Server 2008的默认索引机制。（7）系统功能设计：商店、商品、职工、销售信息的添加、修改、查询、删除以及基本的信息统计功能等。（8）验证设计结果：对设计中的重要内容和重要环节进行认真的验证，分析合理和不足之处、给出改进方案、注意事项，确保设计内容满足系统要求。实验2 数据库建立实验21 实验内容与要求1实验名称：建立数据库（3学时课外准备）2实验目的：熟练掌握使用SQL Server 2008的管理工作室（SQL Server Management Studio，SSMS）及其对象资源管理器、查询编辑器、Transact-SQL语句建立数据库及其数据表的结构、记录内容、索引等，同时能够添加、修改和删除。实验实验原理：SSMS的基本操作；Transact-SQL语法；关系代数基本理论。4实验内容：根据选定的微型数据库应用项目，分别使用SSMS的对象资源管理器和查询编辑器（Transact-SQL语句）创建数据库及其数据表。具体内容：0）SQL Server 2008的安装（课外实验）。1）SQL Server 管理工作室（SQL Server Management Studio，SSMS）的启动和退出。2）利用SSMS的对象资源管理器创建数据库。 使用对象资源管理器编辑数据库。 使用对象资源管理器编辑数据表结构。 使用对象资源管理器编辑数据表记录。3）利用SSMS的查询编辑器创建数据库。 使用查询编辑器编辑数据库。 使用查询编辑器编辑数据表结构。 使用查询编辑器编辑数据表记录。5实验要求：按照实验设计模板，撰写实验报告。具体内容包括：预备知识、操作流程、实验步骤、核心程序、存在问题等。6实验思考：分析对象资源管理器和查询编辑器创建数据库的优点和缺点、给出针对实际应用如何合理配合使用这两种方法及其注意事项等。实验22 实验范例1实验实例：商品销售数据库ComInfo及其职工表、商店表、商品表和销售表。2实验方法：SSMS和Transact-SQL。实验实验步骤：（0）安装SQL Server 20081）开始 控制面板 用户账户 设置用户密码 重新启动计算机。技巧：SQL Server 2008要求用户必须拥有用户名和密码。2）运行SQL Server 2008的安装程序，在安装中心GUI中，单击左侧的“安装”，然后单击右侧的“全新SQL Server独立安装或者向现在安装添加程序”。安装程序在对计算机系统进行检测、安装Microsoft .Net Framework 3.5和基本设置后，应该出现如图3所示的GUI。然后单击“确定”，继续安装。3）在产品密钥GUI（如图4所示）中，输入产品序列号，然后单击“下一步（N）”。 图3 安装程序环境支持GUI 图4 产品密钥GUI4）在图5的许可条款GUI中，勾选“我接收许可条款（A）”，单击“下一步（N）”。5）在安装程序支持文件界面（如图6所示）中，单击“安装”。 图5 许可条款GUI 图6 安装程序支持文件GUI6）在安装程序支持规则GUI（如图7所示）中，单击“下一步（N）”。技巧：如果Windows防火墙处于打开状态，则“Windows防火墙”选项是警告状态，这时只需关闭防火墙，然后单击“重新运行（R1）”进行重新测试。7）在功能选择界面（如图8所示）中，单击“全选（A）”，单击“下一步（N）”。8）在图9的实例配置界面中，选择“默认实例（D）”或者“命名实例（A）”。本例选择“命名实例（A）”，然后在“命名实例（A）”右侧输入实例名称：HappySQL；在“实例ID（I）”右侧输入实例ID：HappySQL；在“命名根目录（A）”右侧输入目录位置：C:Program FilesMicrosoft SQL Server（或者单击右侧的“”，选择目录）；单击“下一步（N）”。9）在磁盘空间要求界面（如图10所示）中，单击“下一步（N）”。10）在服务器配置GUI（如图11所示）中，在每项服务的右侧输入（或者选择）合法的帐户名和密码，并选择相应的启动类型，然后单击“下一步（N）”。技巧1：在服务器配置界面中，需要为每一项服务指定一个合法的帐户名（包括帐户名和密码），否则安装可能无法继续。 图7 安装程序支持规则GUI 图8 功能选择GUI 图9 实例配置GUI 图10 磁盘空间要求GUI技巧2：在选择帐户名时，可以单击“帐户名”右侧的下拉列表按钮，并在下拉列表中选择“”；然后在如图12所示的选择用户界面中，单击“高级（A）”，再在如图13所示的查找用户GUI中，单击“立即查找（N）”，在下方的列表中选则需要的账户，并单击“确定”，选中的帐户名会出现图12的列表中，最后单击“确定”，最终的服务器配置参考GUI如图11所示。11）在数据库引擎配置GUI（如图14所示）中，选择身份验证模式：Windows身份验证模式（W）或者混合模式（SQL Server身份验证和Windows身份验证）（M）；本利使用前者；对于后者，需要输入密码。在“指定SQL Server管理员” 的下方指定一个合法用户，具体可以通过单击“添加（A）”，添加指定用户；或者单击“添加当前用户（C）”，添加当前用户；或者单击“删除（C）”，删除选定用户。完成配置后单击“下一步（N）”。12）在Analysis Services配置GUI（如图15所示）中，单击“添加（A）”，添加指定用户；或者单击“添加当前用户（C）”，添加当前用户；或者单击“删除（C）”，删除选定用户。完成配置后单击“下一步（N）”。13）在Reporting Services配置GUI（如图16所示）中，选择“安装本机配置默认模式（I）”，单击“下一步（N）”。 图11 服务器配置GUI 图 3.12 选择用户GUI 图13 查找用户GUI 图14 数据库引擎配置GUI 图15 分析服务GUI 图16 Reporting Services配置GUI14）在使用情况报告GUI（如图17所示）中，勾选相应选项，单击“下一步（N）”。15）在安装规则GUI（如图18所示）中，单击“下一步（N）”。 图17 错误和使用情况报告GUI 图18 安装规则GUI16）在准备安装GUI（如图19所示）中，单击“安装（I）”进入安装状态。在安装进度GUI（如图20所示）中，单击“下一步（N）”。在安装完成GUI中，单击“关闭”。最后，关闭SQL Server安装中心。 图19 准备安装GUI 图20 安装进度GUI（1）SSMS的启动和退出。1）开始程序Microsoft SQL Server 2008SQL Server Management Studio。2）在如图21所示界面的“服务器类型（T）”右侧的下拉列表中选择服务器类型。本例选择：数据库引擎。在“身份验证（A）”右侧的下拉列表中选择身份验证方式。本例选择：Windows身份验证；如果选择SQL Server身份验证，则还需要在其下方输入用户名和密码。在“服务器名称（S）”右侧的下拉列表中选择：（如图22所示），然后选择相应的数据引擎。本例选择：ZJSU-HAPPYYOU，单击确定。该GUI设置后的界面如图21所示。3）在如图21所示的界面中，单击连接（C），连接数据库服务器，并且启动SQL Server管理工作室（如图23所示）。在启动的SSMS中，就进行创建数据库、数据表和视图等数据库管理操作。退出SSMS：文件退出；或者单击关闭“”按钮。 图21 服务器连接GUI 图22 选择数据库服务器（2）利用SSMS的对象资源管理器创建数据库1）使用对象资源管理器编辑数据库在如图23所示的界面中，右击：“数据库”（如图24所示），然后选择：“新建数据库（N）”（如图25所示）。 图23 SQL Server 管理工作室 图24 对象资源管理器建立数据库在“数据库名称（N）：”右侧输入数据库的名称：ComInfo，则在“数据库文件（F）：”的下方给出了数据库文件和相应日志文件的逻辑名称依次：ComInfo.MDF和ComInfo_log.LDF，其默认存储位置为：C:Program FilesMicrosoft SQL Server MSSQL10. MSSQLSERVERMSSQLDATA；如果需要指定数据库的存储位置，可以单击“路径”右侧的“”进行选择。其它属性，也可以根据需要进行设置。最后单击“确定”。在如图23所示的SSMS界面中，右击需要编辑的数据库（例如：ComInfo），然后选择“重命名”、“删除”、“刷新”和“属性”等进行相应的编辑。具体用法如下： 选择“重命名”：修改数据库的名称。选择“属性”：查看数据库的基本属性。 选择“删除”：可以删除数据库，方法是在弹出的窗口中单击“确定”。 选择“刷新”：可以刷新SSMS，即在SSMS上重新刷新对数据库和数据表的修改。注意：删除数据库时，将删除该数据库的所有数据表。图25 新建数据库2）使用对象资源管理器编辑数据表结构 在如图23所示的SSMS中，展开数据库ComInfo，右击：“表”（如图26所示），然后选择：“新建表（N）”（如图27所示）。 图26 管理工作室的新建数据表GUI 图27 新建表如图27所示，在“表-dbo.Table_1”标签的下方依次输入设计好的数据项的名称以及相应属性。具体输入方法如下： 列名下方：输入数据项的名称。如：工号，姓名，性别，年龄。 数据类型下方：选择相应的数据类型。如：Char(6)，int等。对于字符型数据，还需要数据的宽度；对于decimal(18,0)类型数据，还需要数据的宽度和小数位数，其它类型的数据，只需按照相同的方法处理即可。 允许空下方：选择该数据是否允许为空值。 在任意行上，右击（如图27所示），选择“插入列（M）”，可以插入一个新数据项及其相应属性。选择“删除列（N）”，可以删除一个新数据项及其相应属性。 在任意行上，右击（如图27所示），选择“设置主键”，可以把该属性设置为主键；如果需要把多个数据项设置成组合主键，则只需先选择多行，然后做同样处理；如果取消主键，只需在设置主键的行上，右击（参考图27），选择“移去主键（Y）”。 选择“CHECK约束（O）”，可以设置该属性的自定义约束（如图28所示）。单击“添加（A）”或者“删除（D）”，添加默认自定义约束或者删除已定义约束；在“表达式”右侧直接填写自定义约束表达式，或者单击右侧的“.”,则弹出的窗口中（如图29所示）填写自定义约束表达式，然后单击“确定”，在图28中单击“关闭”。 图28 设置CHECK约束 图29 填写CHECK约束表达式如图27所示，单击工具栏的保存按钮，在弹出的“选择名称”窗口中输入：职工（如图30所示），单击“确定”。图30 保存数据表使用同样的方法创建数据表：商店、商品和销售。在创建职工的过程中，参照约束和外键的创建方法如下：在如图27所示的SSMS中，选择“关系”（如图31所示），单击“添加（A）”或者“删除（D）”，添加或者删除参照关系。本例单击“添加（A）”添加一个默认参照关系，然后选择“表和列规范”，并单击右侧“.”（如图32）。在图32中，设置职工的外键店号及其对应的主键表商店和参照主键店号。具体方法：在“主键表（P）：”下选择“商店”，并在商店的下方选择参照主键店号；然后在“外键表：”下方“职工”的下方选择外键“店号”（参照图32）；单击确定。在图32中，设置职工的自参照外键店号及其对应的主键表职工和参照主键工号。具体方法：在“主键表（P）：”下选择“职工”，并在职工的下方选择参照主键工号；然后在“外键表：”下方“职工”的下方选择外键“经理”（参照图32）；单击确定。建立结果如图31所示，最后单击关闭。同理建立销售表中店号和品号两个外键的参照键束。 图31 设置外键关系 图32 设置外键属性在如图23所示的SSMS中，依次展开数据库和相应的数据表，在需要编辑的数据表上右击（如图33所示），然后选择“设计”、“重命名”和“删除”等对数据表进行相应的编辑。具体用法如下：选择“设计”：可以修改数据表的数据项的属性，方法同上。选择“重命名”：可以修改数据表的名称。选择“删除”：可以删除数据表，方法是在弹出的窗口中单击“确定”。3）使用对象资源管理器编辑数据表记录在如图33所示的SSMS中，依次展开数据库和相应的数据表，在需要编辑的数据表上右击，然后选择“编辑前200行（E）”（如图34所示），编辑表记录的方法如下： 图33 编辑数据表 图34 编辑数据表记录修改数据表内容：在数据表设计器中，可以直接对表的内容进行修改。向数据表添加新内容：在数据表设计器中，当编辑最后一个元组的数据时，生成器会在其下方自动添加一个新元组行，因此可以通过该新行添加新元组及其内容。删除数据表的元组：在要删除元组的所在行上，右击，然后选择“删除”，在弹出的对话窗口中选择“是（Y）”进行删除，或者“否（N）”取消删除。注意：在编辑数据表时，如果数据出现违约时，则DBMS将会启动违约机制进行检查，并自动作相应的处理，或者输出提示信息。（3）利用SSMS的查询编辑器创建数据库在图23的SSMS界面中，单击工具栏的“新建查询（N）”，启动查询编辑器如图35所示，然后直接使用Transact-SQL语句创建数据库、数据表和视图等。具体方法如下：图35 查询编辑器编辑GUI在查询编辑器的编辑器页面SQLQuery1.sql中，直接输入SQL语句。单击分析按钮“”，检查分析SQL语句是否正确。单击调试按钮“”，对SQL的程序模块或者存储过程进行调试。单击执行按钮“！执行（X）”，运行查询编辑器中的语句序列。1）使用查询编辑器编辑数据库。建立数据库 - 建立默认环境数据库ComInfo CREATE DATABASE ComInfo - 建立指定环境数据库ComInfo，首先需要建立目录D:Data CREATE DATABASE ComInfo ON (NAME=ComInfoDbf,FILENAME=D:DataComInfo.mdf) LOG ON (NAME=ComInfoLog,FILENAME=D:DataComInfo.ldf)提示：“-”开头的为注释语句。打开数据库：在使用数据库之前，必须先打开数据库。 USE ComInfo删除数据库：在删除数据库之前，必须先关闭数据库，方法是打开一个新数据库；在删除数据库之后，会删除数据库的所有数据表。 DROP DATABASE ComInfo技巧：如果在建立数据库时，希望删除已经存在的同名数据库，可以使用如下语句： IF DB_ID (ComInfo) IS NOT NULL BEGIN USE MASTER DROP DATABASE ComInfo END2）使用查询编辑器编辑数据表结构。建立数据表结构 -建立商店数据表 CREATE TABLE 商店( 店号 CHAR(6) PRIMARY KEY CHECK (店号 LIKE S0-90-90-90-90-9), 店名 CHAR(22) NOT NULL,店址 CHAR(36) NOT NULL, 电话 CHAR(12) UNIQUE CHECK (电话 LIKE 0-90-90-90-90-90-90-90-90-90-90-90-9) -建立商品数据表 CREATE TABLE 商品( 品号 CHAR(6) PRIMARY KEY CHECK (品号 LIKE P0-90-90-90-90-9), 品名 CHAR(22) NOT NULL,厂名 CHAR(22) NOT NULL, 厂址 CHAR(36) NOT NULL,单价 DECIMAL(9,2),数量 INT) -建立职工数据表 CREATE TABLE 职工(工号 CHAR(6) PRIMARY KEY CHECK (工号 LIKE A-Z0-90-90-90-90-9), 姓名 CHAR(8) NOT NULL, 性别 CHAR(2) NOT NULL CHECK (性别=男 OR 性别=女), 年龄 INT NOT NULL CONSTRAINT SAge16_96 CHECK (年龄=16 AND 年龄=96), 电话 CHAR(12) NOT NULL CHECK (电话 LIKE 0-90-90-90-90-90-90-90-90-90-90-90-9), 职务补 FLOAT,工资 REAL,聘期 DECIMAL(3,1) DEFAULT 6, 经理 CHAR(6) CHECK (经理 LIKE A-Z0-90-90-90-90-9) REFERENCES 职工(工号), 店号 CHAR(6) CHECK (店号 LIKE S0-90-90-90-90-9) REFERENCES 商店(店号) -建立销售数据表 CREATE TABLE 销售( 店号 CHAR(6) CHECK (店号 LIKE S0-90-90-90-90-9), 品号 CHAR(6) CHECK (品号 LIKE P0-90-90-90-90-9), 销售额 DECIMAL(12,2), PRIMARY KEY (店号,品号), FOREIGN KEY (店号) REFERENCES 商店(店号) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (品号) REFERENCES 商品(品号) ON DELETE CASCADE ON UPDATE CASCADE)修改数据表结构 -给职工添加“工作日期”数据项，数据类型为日期型。 ALTER TABLE 职工 ADD 工作日期 DATE -在职工中，给电话增加唯一性约束 ALTER TABLE 职工 ADD CONSTRAINT Un_Phone UNIQUE(电话) 或者 ALTER TABLE 职工 ADD UNIQUE(电话) -在职工中，删除电话的唯一性约束。 ALTER TABLE 职工 DROP CONSTRAINT Un_Phone -在职工中，修改职工工作日期的数据类型为DATETIME ALTER TABLE 职工 ALTER COLUMN 工作日期 DATETIME -在职工中，删除数据项“工作日期” ALTER TABLE 职工 DROP COLUMN 工作日期思考：给职工添加“工作日期”数据项，数据类型为日期型，默认值为当前日期。提示：ALTER TABLE 职工 ADD 工作时间 DATE DEFAULT GETDATE()。删除数据库 DROP TABLE 职工技巧：如果在建立数据表时，希望删除已经存在的同名数据表，可以使用如下语句： IF DB_ID (ComInfo) IS NOT NULL BEGIN USE ComInfo IF OBJECT_ID(职工) IS NOT NULL DROP TABLE 职工 END3）使用查询编辑器编辑数据表内容。 -给商店添加纪录 INSERT INTO 商店 VALUES( S00001,华润超市文一店,杭州市文一路66号,01