数据库实验三作业及答案

实验三MySQL数据库创建与维护一．实验目的⚫了解MySQL数据库的逻辑结构和物理结构⚫进一步理解关系表及数据完整性约束的概念、索引的概念⚫掌握创建和维护数据库、数据表的方法⚫掌握数据库SQL脚本的转储方法和使用脚本重建数据库的方法二．实验环境及素材⚫MySQL和Navicat for MySQL⚫bookstore数据库设计文档（参见10.1节）三．实验内容1．创建一个名称为bookstore的数据库，支持网上书店系统的信息管理。

提示：注意，应选择utf8 - -UTF-8 Unicode字符集及utf8_general_ci排序规则。

解析：本实验练习通过“MySQL”创建数据库的方法。

注意这里没有要求存放位置，主数据库文件和日志文件的默认存放位置在：C:\ProgramData\MySQL\MySQL Server 5.x\Data下。

系统为bookstore数据库创建一个文件夹，在其中为每个表创建两个文件“表名.frm”和“表名.ibd”。

如果要存放到指定位置，需要先修改MySQL安装目录下的my.ini文件中的datadir可以指向自定义的文件夹。

然后再创建的数据库文件将存放在指定位置。

参考步骤：①在Navicat for MySQL右击左栏的连接，选择“新建数据库”菜单命令。

②在对话框中填写数据库的基本信息即可创建数据库。

注意必须选用“utf8 - -UTF-8 Unicode”字符集及“utf8_general_ci”排序规则。

③单击“确定”按钮，即创建完成bookstore数据库创建。

验证步骤：①在左栏数据库列表中可查看到bookstore数据库。

②右击bookstore数据库选择“数据库属性”快捷菜单，出现该数据库属性对话框，可以查看基本信息，这时也可对数据库的一些属性进行修改。

③在Windows中查看磁盘C:\ProgramData\MySQL\MySQL Server 5.x\Data文件夹，可看到bookstore子文件夹和其下的db.opt文件。

实验内容：实验一：数据库的操作使用Management Studio和sql语句分别完成以下操作：1.创建一个名为“SM”的数据库，数据文件初始大小为3MB，最大为50MB，数据库自动增长，增长方式按10%；日志文件初始大小为2MB，数据大小不受限制，按1MB增长。

create database smon(name='smdata',filename='e:\smdata.mdf',size=3,maxsize=50,filegrowth=10%)log on(name='smlog',filename='e:\smlog.ldf',size=2,maxsize=unlimited,filegrowth=1)2.修改数据库“SM”，将数据文件名改成“sm_data”,初始大小改成5MBalter database smmodify file( name='smdata',newname='sm_data',size=5)3.分别查看数据库“SM”，该数据库中的文件和文件组。

exec sp_helpfile smexec sp_helpfilegroup sm4.删除数据库“SM”。

drop database sm实验二：创建表1. 在数据库SM中创建学生表student,课程表course,选课表scstudent(sid,sno,clno,sname,ssex,sage,sbir)说明：sid int identity(1,1) 序号sno 为主关系键，为字符类型学号clno 字符类型，班级号sname 字符类型，并不为空ssex 字符类型，check的值的范围为男女sbir 日期类型出生日期sage int；use smcreate table student( sid int identity(1,1),sno char(10) constraint pk_st primary key,clno char(10),sname varchar(20) not null,ssex char(2) constraint ck_ssex check(ssex in('男','女')),sbir datetime,sage int)course(cno,cname,ccredits,ctno,cpno,ctime)说明：cno 字符类型，主关系键cname 字符类型，唯一键ccredits 学分，精确数值型，精确长度为2，小数位为1ctno ，cpno 字符类型ctime 整型create table course(cno char(4) constraint pk_c primary key,cname varchar(20) constaint uk_cname unique,ccredit decimal(2,1),ctno char(2),cpno char(4),ctime tinyint)sc(sno,cno,score)说明：sno+cno为主键，并且sno是student的外部键，cno是course的外部键。

实验三数据更新、视图、权限管理实验3.1 数据更新1 实验内容(1) 使用INSERT INTO语句插入数据，包括插入一个元组或将子查询的结果插入到数据库中两种方式。

(2) 使用SELECT INTO语句，产生一个新表并插入数据。

(3) 使用UPDATE语句可以修改制定表中满足WHERE子句条件的元组，有三种修改的方式：修改某个元组的值；修改多个元组的值；带子查询的修改语句。

(4) 使用DELETE语句删除数据：删除某一个元组的值；删除多个元组的值；带子查询的删除语句。

2 实验步骤在数据库School上按下列要求进行数据更新可在SQL代码前加下面两句SQL语句，用于确保当前使用的是School数据库Use Schoolgo(1)使用SQL语句向STUDENTS表中插入元组（编号：12345678 名字：LiMing EMAIL:LM@年级：2002）。

Insert into STUDENTS values('12345678','LiMing','LM@','2002')(2)对每个课程，求学生的选课人数和学生的最高成绩，并把结果存入数据库。

使用SELECTINTO和INSERT INTO两种方法实现。

Insert into:create table Courses_maxScore(cid char(20),Count_courses int,maxScore int)insert into Courses_maxScore (cid,Count_courses,maxScore)select cid,count(*)as Count_courses,max(score)as maxScore from CHOICES group by cidselect*from Courses_maxScoreSelect into:select cid,Count_courses,maxScore into cnCourses_maxScore from Courses_maxScoreselect*from cnCourses_maxScore(3)在STUDENTS表中使用SQL语句将姓名为LiMing.的学生的EMAIL改为LM@。

实验三数据库数据的维护实验实验要求1.用Transact-SQL语句、数据导入、SQL Server Management Studio（企业管理器）输入的方法对所给定的8张表的数据输入到数据库中。

自行决定每张表的数据导入办法，但每种方法各征对二或三张表第一种：用SQL语句执行，如果一条一条信息Insert的话，工作量大，而且之后的题目也有执行，这里就不重复了。

如果用总导入语句，如下图所示，但由于某种原因服务器拒绝了我的请求，我还不知道怎么解决，望老师指点。

第二种：用企业管理器导入，快捷便利，过程如下。

图结果所示2.用Transact-SQL向Course表中插入一条记录，course_name为空，看运行的结果。

由于Course_name有not null约束，所以插入一条course_name为空的语句会报错。

语句如下：3.用Transact-SQL修改Course表中credit为5的记录，将其credit改为7, credit小于4的改为2,看运行的结果由于credit受到1<=credit<=6约束，所以，改为7的语句无法执行。

而改为四的语句不违反完整性约束，所以语句执行成功。

4.删除一条学生记录，看运行结果，对运行结果进行分析。

由于student_course中的student_id参照了student中的student_id，所以不能删除student中的记录。

5.用Transact-SQL完成将编号为dep04_b001的课程的选修信息插入到一个新的选课信息表中。

建立新表：6.用Transact-SQL完成删除单片机原理课程的选课信息，分析运行结果。

完成实验报告，包括实验内容、实验步骤、实验结果等。

实验名称：数据库的查询实验一、实验目的和要求（1）掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。

（2）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

（3）熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

二、实验内容和原理1. P74第5题2. 用Transact-SQL语句表示下列操作．在学生-课程库中实现其数据查询操作：（1）求数学系学生的学号和姓名。

（2）求选修了高等数学的学生学号、姓名和成绩。

（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

（4）求选修课程C1且成绩在80～90分之间的学生学号、姓名及成绩，并将成绩乘以系数0.8输出。

（5）求数学系或计算机系姓张的学生的信息。

（6）求缺少了成绩的学生的学号和课程号。

（7）求C1课程的成绩高于张三的学生学号和成绩。

（8）求其他系中比计算机系学生年龄都小的学生。

（9）查询选修了全部课程的学生的姓名。

（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。

（11）查询每一门课的间接先行课（即先行课的先行课）。

三、实验环境硬件：计算机软件：Windows 2000和SQL Server 2000四、算法描述及实验步骤1.P74第五题(1)求供应工程j1零件的供应商号码sno；在查询分析器的命令窗口中输入：select distinct snofrom spjwhere jno='j1'(2)求供应工程j1零件p1的供应商号码sno；在查询分析器的命令窗口中输入：select distinct snofrom spjwhere jno='j1' and pno='p1'(3) 求供应工程j1零件为红色的供应商号码sno；在查询分析器的命令窗口中输入：select distinct snofrom spj,pwhere jno='j1' and spj.pno=p.pno and color='红'(4)求没有使用天津供应商生产的红色零件的工程号jno；在查询分析器的命令窗口中输入：select distinct j.jnofrom spj,s,p,jwhere j.jnonot in(select distinct j.jnofrom spj,s,p,jwhere spj.pno=p.pno and spj.sno=s.sno and spj.jno=j.jno and s.city='天津' and color='红' )(5)求至少用了供应商s1所供应的全部零件的工程号jno。

评分 签名 日期

实验报告

课程名称 数据结构 实验名称 数据表的管理(二) 专业班级 信管1001 姓 名 学 号 实验日期 实验地点 第2页 共 4页

一、实验目的 1. 复习数据表的创建与修改的操作方法 2. 熟悉索引的类型和用途 3. 掌握索引的创建与修改方法 二、实验内容 1．修改数据表 2．创建索引 3．修改索引 4．删除索引 三、实验环境 Windows XP操作系统、Microsoft SQL Server2005 四、相关知识 索引是依赖数据表建立的，一个数据表的存储包括两个组成部分，一部分是用来存放数据的数据页，另一部分是用来存放索引的索引页。通常索引页比数据页的数据量要小得多，当进行数据查询时，SQL Server 先去搜索索引页，从中找到所需的数据指针，再通过指针从数据页中读取数据。索引提供指针以指向存储在表中指定列的数据值，然后根据指定排序次序排列这些指针。合理地利用索引，将大大提高数据库的检索速度和数据库的性能。但是，享受索引带来的好处是有代价的，一是带索引的表在数据库中会占据更多的空间，二是为了维护索引，对数据进行插入、修改、删除等操作的命令所花费的时间会更长些。因此，在设计和创建索引时，要确保对性能的提高程度大于在存储空间和处理资源方面所付出的代价。 1. 索引的创建 数据的索引可以在“Microsoft SQL Server Management Studio”的【对象资源管理器】 中展开【数据库】，然后再展开要在其中建表的数据库文件，再展开【表】和相应的数据表，然后在【索引】上右击鼠标，选择【新建索引】，在新建索引对话框中设置【索引名称】、【索引类型】、【唯一】及【索引键列】，就可以完成索引的创建，或者在查询编辑器中输入CREATEINDEX 语句也可以实现给表创建索引。 建立索引的常用命令格式为： CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX ON ([][,[] ]„)； 其中，指定要建索引的基本表名字，索引可以建立在该表的一列或多列上，各 列名之间用逗号分隔，用指定索引值的排列次序，升序：ASC，降序：DESC。缺省 值为ASC。 UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。 CLUSTERED 表示要建立的索引是聚集索引。 NONCLUSTERED 表示要建立的索引为非聚集索引。 注意： （1）每张表只能有一个聚集索引，并且应该最先建立。 （2）创建索引所需要的空间来自用户数据库，所以要保证要有足够的空间创建聚 集索引。 （3）为一个表的创建主键字段和UNIQUE 字段约束后所会自动生成相应的索引。 （4）默认设置时不是建立聚集索引，而是建立的非聚簇索引。一个表中最多可以 创建249 个非聚集索引。 建立索引时，不选参数CLUSTERED，则建立的是非聚集索引，表的物理顺序与索引顺 序不一致，表中的数据没有按照索引的顺序而排列，索引是由具有层次性的索引页面组成的。 2. 索引的修改 T-SQL 语言中提供了ALTER INDEX 语句来修改索引，其主要代码为： ALTER INDEX {index_name|ALL} --指定索引名或所有索引 ON[database_name].table_or_view name --指定数据表或视图 {REBUILD --重新生成索引 [ [ WITH (PAD_INDEX = { ON | OFF } --设置是否使用索引填充 | FILLFACTOR = fillfactor --设置填充因子大小 | SORT_IN_TEMPDB = { ON | OFF } --是否在tempdb 数据库中存储临时排序结果 | IGNORE_DUP_KEY = { ON | OFF } --是否忽略重复的值 | STATISTICS_NORECOMPUTE = { ON | OFF } --设置是否自动计算统计信息 第3页 共 4页

实验三表操作实验目的：1.掌握使用字段属性中的查阅字段。

2.学会导入外部数据。

3.学会查找、替换。

实验内容：在本地磁盘D盘新建文件夹，并以“学号姓名”命名，实验所有操作，请在该文件夹内完成。

（如果该文件夹已存在并有上次实验的内容，请清除。

将下载的“实验三数据库”拷贝到“学号姓名”文件夹中进行实验操作。

）1、在“实验三”数据库中，创建“学生”表。

表结构：其中：a.主键：也称主关键字(primary key)，是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。

注意：一个表只能有一个主键，但是主键可以是一个字段或包含多个字段的集合，并且主关键字的列不能包含空值。

操作步骤：1）选择“创建”选项卡中的“表格”选项组内的“表设计”按钮，打开“表设计视图”。

2）打开表的设计视图，按上方表结构所示的内容，在字段名称列中输入字段名称，在数据类型列选择相应的数据类型。

3）单击“保存”按钮，保存表名称为“学生”，弹出“是否定义主键”对话框，选择“否”。

4）打开“学生”表，单击“开始”选项卡上“视图”选项组中的“设计视图”按钮，切换到“学生”表的设计视图，做进一步的修改。

5）分别选择学号、姓名字段，按要求修改字段大小。

6）选择“性别”字段，在下面字段属性区域，选择“字段大小”，输入2；输入默认值为“男”。

7）选择“年龄”字段，在下面字段属性区域，选择“字段大小”，点击右边向下三角形，选择“字节”；输入默认值为“21”；选择“有效性规则”，输入括号内的规则（>=20 and <=70 或between 20 and 70），注意：必须在英文输入法状态输入小于号。

如图3-1所示。

图3-1 “年龄”的字段属性8）添加主键，鼠标左键点击“学号”字段前方字段选择区，选中“学号”字段，选择“表格工具/设计”选项卡，选择“工具”选项组中的“主键”选项，即可在学号字段前方添加主键标识，如图3-2所示。

图3-2 添加主键9）保存。

数据库练习三答案统计查询统计查询是指使用关键字配合select语句查询时，在查询后的结果中出现了不是原始数据表中数据的记录信息，这些记录信息是根据需求，借助不同关键字对原始数据表中数据信息做计算处理而得到的。

1 聚合函数查询2 查询结果排序排序操作可以使用order by语句实现，它可以出现在from语句或者where 语句的后面，语法格式如下：order by 字段名1 [ , 字段名2?] [ asc | desc ]3.查询结果分组小计分组小计语句group by可以出现在from语句或者where语句后面，语法格式如下： group by 字段名列表 [ having 条件表达式]? 后面。

? ?课堂题目：1、统计“课程表〞中总的课程数，总学分，最高学分，最低学分，平均学分〔无列名〕select COUNT(*),SUM(学分),MAX(学分),MIN(学分) from 课程表having语句对分组小计后的结果做进一步的条件筛选，跟随在group by 通常在select语句后面加聚合函数来表示求值小计。

出现在select语句后的字段名，要么放在聚合函数中，要么放在group by语句后面，否那么将提示错误。

2、统计“课程表〞中总的课程数，总学分，最高学分，最低学分，平均学分〔加列名，三种方法〕select COUNT(*)as '总课程数',SUM(学分)as'总学分',MAX(学分)as '最高学分',MIN(学分)as'最低学分' from 课程表3. 查询“课程表〞中学分最高的前三条记录。

〔降序 desc〕select top 3 * from 课程表order by 学分 desc4. 查询“课程表〞中学分最低的前三条记录中的‘课程名称’和‘学分’。

〔升序 asc 默认〕select top 3 课程名称,学分 from 课程表order by 学分 desc5. “选课成绩表〞中,查询结果按照’学生编号’分类，统计出每个学生的成绩总成绩和平均成绩。

万常选数据库实验参考答案SET DATEFORMAT YMDGO--选择数据库USE OrderDBGO--实验一：简单查询--1.1 查询所有业务部门的员工姓名、职务、薪水。

SELECT employeeName,headShip,salaryFROM Employee--1.2 查询名字中含有“有限”的客户姓名和所在地。

SELECT customerName,addressFROM Customer--1.3 查询出姓“王”并且姓名的最后一个字为“成”的员工。

SELECT *FROM EmployeeWHERE employeeName LIKE '王%成'--1.4 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。

SELECT employeeName AS 姓名, department AS 所属部门, headShip AS 职称, address AS 住址, CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' END AS 性别FROM Employee--1.5 在表sales中挑出销售金额大于等于5000元的订单。

SELECT *FROM OrderMasterWHERE orderSum>5000--1.6 选取订单金额最高的前10%的订单数据。

SELECT TOP(10) PERCENT *FROM OrderMasterORDER BY orderSum--或SELECT TOP 10 PERCENT orderNo,SUM(quantity*price) AS totalPrice FROM OrderDetailGROUP BY orderNOORDER BY SUM(quantity*price) DESC--1.7 查询出职务为“职员”或职务为“科长”的女员工的信息。

沈阳工程学院学生实验报告（课程名称：数据库系统原理）实验题目：数据查询（一）班级学号姓名日期201年月日地点指导教师一、实验目的掌握SQL查询命令：主要针对单表查询。

二、实验环境Oracle10g数据库系统。

三、实验内容与要求使用Select命令完成下列数据查询。

简单查询：⑴查询全体顾客的编号和姓名。

⑵查询全部商品的详细记录。

⑶查询顾客的姓名和出生年份。

⑷查询购买了商品的顾客的编号。

⑸查询职业（g_work）为“教师”的顾客的姓名。

⑹查询“沈阳市”年龄在20岁以下的顾客的编号和年龄。

⑺查询年龄在20—22岁的顾客的编号。

⑻查询“沈阳”、“大连”、“北京”的顾客信息。

⑼查询姓"王"的顾客的信息。

⑽查询购买商品编号为“02”的顾客的编号和购买数量，结果按顾客编号升序，购买数量降序排列。

⑾查询顾客总人数。

⑿查询购买商品编号为“02”的一次最高购买量。

⒀查询购买2次和2次以上商品的顾客编号。

四、实验过程及结果分析⑴查询全体顾客的编号和姓名，如图1所示。

select guestid,guestnamefrom guest;图1⑵查询全部商品的详细记录,如图2所示。

select *from goods;图2⑶查询顾客的姓名和出生年份,如图3所示。

Select guestname,2016-g_agefrom guest;图3⑷查询购买了商品的顾客的编号，如图4所示。

Select distinct guestidFrom purchase;图4⑸查询职业（g_work）为“教师”的顾客的姓名,如图5所示。

Select guestnameFrom guestWhere g_work='教师';图5⑹查询“沈阳市”年龄在20岁以下的顾客的编号和年龄，如图6所示。

Select guestid,g_ageFrom guestWhere g_age<20 and g_addr='沈阳';图6⑺查询年龄在20—22岁的顾客的编号,如图7所示。