MySQL数据库原理及应用（武洪萍 第2版）（微课版）-课外拓展答案

MySQL数据库原理及应用（武洪萍第2版）（微课版）-课外拓展答案

项目2实训：设计数据库

1.实践内容

（1）“医院病房管理系统”数据库的设计。

某医院病房计算机管理中需要如下信息。

科室：科室名，科室地址，科电话，医生姓名，科室主任。

病房：病房号，床位号，所属科室名。

医生：姓名，职称，所属科室名，年龄，工作证号。

病人：病历号，姓名，性别，诊断，主管医生，病房号。

其中，一个科室有若干个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室,

但可负责多个病人的诊治，一个病人的主管医生只有一个。

参考答案：E-R图及转换成的关系模式如下所示：

病房（病房号，床位号，科室名）；

科室（科室名，科室地址，科电话，科室主任，医生姓名）；

医生（姓名，职称，年龄，工作证号，科室名）；

病人（病历号，姓名，性别，诊断，主管医生姓名，病房号）。

<2）“订单管理系统”数据库的设计。

设某单位销售产品所需管理的信息有：订单号，客户号，客户名，客户地址，产品号，产品名，产

品价咯，订购数量，订购日期。一个客户可以有多个订单，一个订单可以订多种产品。

参考答案：E-R图及转换成的关系模式如下所示：

客户（客户号，客户名，客户地址）；

订单（订单号，客户号）*

产品（产品号，产品名，产品价格）；

订购（订单号，产品号,订购数量，订购日期）。

（3）“课程安排管理系统”数据库的设计。

课程安排管理需要对课程、学生、教师和教室进行协调。每个学生最多可以同时选修5门课程，每

门课程必须安排一间教室以便学生可以去上课,一个教室在不同的时间可以被不同的班级使用：一个教

师可以教授多个班级的课程，也可以教授同一班级的多门不同的课程，但教师不能在同一时间教授多个

班级或多门课程；课程、学生、教师和教室必须匹配。

参考答案：E-R图及转换成的关系模式如下所示：

项目2课外拓展：设计网络玩具销售系统

，装具编号：＞（名称'1

一」类型）

|玩具

'价格

/..-V——.一■—―■——一

广电话）（通信地址）；所买个数;所剩数量

客户（客户编号，姓名，性别，年龄，电话，通信地址）；

玩具（玩具编号，名称，类型，价格，所剩数量）；

订购（客户编号，玩具编号，订单号，所买个数）。

项目3实训：安装MySQL5.7及NavicatforMySQL

1.实践内容及要求

（1）在Windows平台下安装与配置MySQL5.7.21版。

参考课本P73-78

（2）在服务对话框中，手动启动或者关闭MySQL服务。

参考课本P78.79

（3）使用Net命令启动或关闭MySQL服务。

启动：netstartmysq157

关闭：netstopmysql57

（4）分别用Navicat工具和命令行方式登录MySQL0

参考课本P79-80

（5）在my.ini文件中将数据库的存储位置改为D:\MYSQL\DATAo

参考课本?84-85

（6）使用配置向导修改当前密码，并使用新密码重新登录。

（7）配置Path变量，确保MySQL的相关路径包含在Path变量中。

参考课本Psi-85

项目4实训：创建和删除数据库

1.实训内容及要求

（1）创建数据库。

①使用Navicat创建学生信息管理数据库grademo

参考课本P91

②使用SQL语句创建数据库MyDBo

CREATEDATABASEMyDB；

（2）查看数据库属性。

①在Navicat中查看创建后的gradem数据库和MyDB数据库的状态，查看数据库所在的文件夹。

（略）

②利用SHOWDATABASES命令显示当前的所有数据库。

SHOWDATABASES；

（3）删除数据库。

①使用Navicat图形工具删除gradem数据库。

参考课本P93

②使用SQL语句删除MyDB数据库。

DROPDATABASEMyDB；

③利用SHOWDATABASES命令显示当前的所有数据库。

SHOWDATABASES；

项目5实训表的创建与管理

1.实训目的

（1）掌握表的基础知识。

（2）掌握使用Navicat管理工具和SQL语句创建表的方法。

（3）掌握表的维护、修改、查看、删除等基本操作方法。

2.实训内容和要求

（1）在gradem数据库中创建表5.10〜表5.14所示结构的表。

表5.10student表的表结构

字段名称数据类型长度小数位数是否允许NULL值说明

snochar10否主码

snamevarchar8是

ssexchar2是取值：男或女

sbirthdaydate是

saddressvarchar50是

sdeptchar16是

specialityvarchar20是

表5.11course表（课程名称表）的表结构

字段名称数据类型长度小数位数是否允许NULL值说明

enochar5否主码

cnamevarchar20否

表5.12sc表（成绩表）的表结构

字段名称数据类型长度小数位数是否允许NULL值说明

snochar10否组合主码，外码

enochar5否组合主码，外码

degreedecimal41是取值rioo

表5.13teacher表（教师表）的表结构

字段名称|数据类型|长度|小数位数|是否允许NULL值|说明

tnochar3否主码

tnamevarchar8是

tsexchar2是取值：男或女

tbirthdaydate是

tdeptchar16是

表5.14teaching表（授课表）的表结构

字段名称数据类型长度小数位数是否允许NULL值说明

enochar5否组合主码，外码

tnochar3否组合主码，外码

ctermtinyint10是取值rio

（2）向表5.10〜表5.14输入数据记录，见表5.15〜表5.19。

表5.15学生关系表student

snosnamessexsbirthdaysaddresssdeptspeciality

20050101李勇男1987-01-12山东济南计算机工程系计算机应用

20050201刘晨女1988-06-04山东青岛信息工程系电子商务

20050301王敏女1989-12-23江苏苏州数学系数学

20050202张立男1988-08-25河北唐山信息工程系电子商务

表5.16课程关系表course

enocnameenocname

C01数据库C03信息工程系统

C02数学C04操作系统

表5.17成绩表sc

snoenodegree

2005010101C0192

2005010101C0285

2005010101C0388

2005020201C0290

2005020201C0380

表5.18教师表teacher

tnotnamotSGXtbirthdaysdept

101李新男1977-01-12计算机工程系

102钱军女1968-06-04计算机工程系

201王小花女1979-12-23信息工程系

202张小青男1968-08-25信息工程系

表5.19授课表teaching

enotnocterm

C011012

C021021

C032013

C042024

（3）修改表结构。

①向student表中增加“入学时间”列，其数据类型为日期时间型。

②将student表中的sdept字段长度改为20。

③将student表中的speciality字段删除。

④删除student表。

3.思考题

（1）MySQL的数据文件有几种？扩展名分别是什么？

.frm描述表结构的文件

.myd表的数据文件

,myi表数据文件中的索引文件

（2）MySQL中有哪几种整型数据类型？它们占用的存储空间分别是多少？取值范围分别是什么？

见课本101页表5.1

（3）在定义基本表语句时，NOTNULL参数的作用是什么？

指不允许为空值，该列必须输入数据

（4）主码可以建立在“值可以为NULL”的列上吗？

不能

项目6实训|：数据查询实现

一、简单查询

1.实践目的

（1）掌握SELECT语句的基本用法。

（2）使用WHERE子句进行有条件的查询。

（3）掌握使用IN和NOTIN,BETWEEN…AND和NOTBETWEEN…AND来限定查询范围的方法。

（4）掌握利用LIKE子句实现字符串匹配查询。

2.实践内容和要求

完成下面查询。

（1）查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息（用3条SQL语句）。

select*fromstudent；

select*fromcourse；

select♦fromsc；

（2）查询所有学生的学号、姓名、性别和出生日期。

selectsno,sname,ssex,sbirthdayfromstudent；

（3）查询所有课程的课程名称。

selectcnamefromcourse；

（4）查询前10门课程的课程号及课程名称。

selecteno,cnamefromcourselimit10；

（5）查询所有学生的姓名及年龄。

selectsname,year（curdate（））-year（sbirthday）fromstudent；

（6）查询所有年龄大于18岁的女生的学号和姓名。

selectsno,snamefromstudent

where（year（curdate（））-year（sbirthday））>18andssex='女'；

（7）查询所有男生的信息。

select*fromstudentwheressex='男'；

（8）查询所有任课教师的姓名和所在系别。

selectTname,deptnofromteacher；

（9）查询“电子商务”专业的学生姓名、性别和出生日期。

selectsname,ssex,sbirthdayfromstudentwherespeciality='电子商务'；

（10）查询sludenl表中的所有系名。

selectdistinctsdeptfromstudent；

<11）查询“C01”课程的开课学期。

selectctermfromteachingwherecno='c01.'；

（12）查询成绩在80〜90分的学生学号及课程号。

selectofromscwheredegreebetween80and90；

（13）查询在1970年1月1日之前出生的男教师信息。

select*fromteacherwhereyear（Tbirthday<1970）andTsex='男'；

（14）输出有成绩的学生学号。

selectdistinctsnofromscwheredegreeisnotnull；

（15）查询所有姓“刘”的学生信息。

select♦fromstudentwheresname；

（16）查询生源地不是山东省的学生信息。

select*fromstudentwheresaddresslike'_省％'andsaddressnotlike'%山东省％’;

（17）查询成绩为79分、89分或99分的记录。

select*fromscwheredegreein（79,89,99）；

或者select*fromscwheredegree=79ordegree=89ordegree=99；

（18）查询名字中第2个字是“小”字的男生的学生姓名和地址。

selectsname,saddressfromstudentwheresnamelike'_小％'andssex='男

（19）查询名称以“计算机，开头的课程名称。

selectcnamefromcoursewherecnamelike'计算机/_%'escape*/';

（20）查询计算机工程系和软件工程系的学生信息。

select*fromstudentwheresdept='计算机工程系'orsdept='软件工程系'；

或者select*fromstudentwheresdeptin（'计算机工程系'，'软件工程系'）；

3.思考题

（1）LIKE的通配符有哪些？分别代表什么含义？

%：任意多个字符

_：单个字符

（2）知道学生的出生日期，如何求出其年龄？

用当前的年份减去学生的出生年份，其中，CURDATEO函数返回当前的系统日期和时间，YEARO

函数返回指定日期的年部分的整数C

（3）关键字ALL和DISTINCT有什么不同的含义？

ALL：表示输出所有记录，包括重复值

DISTINCT：表示在查询结果中去掉重复值

（4）IS能用“=”来代替吗？

不能

（5）数据的范围除了可以利用BETWEEN…AND运算符表示外，能否用其他方法表示？怎样表示？

能

二、分组与排序

1.实践目的

（1）掌握聚集函数的使用方法。

（2）掌握利用GROUPBY子句对查询结果分组。

（3）掌握利用ORDERBY子句对查询结果排序。

（4）掌握SELECT命令的灵活应用。

2.实践内容和要求

完成下面查询。

(1)统计有学生选修的课程的门数。

selectcount(distincteno)fromsc；

(2)计算“C01”课程的平均成绩。

selectavg(degree)fromscwherecno=,c01'；

(3)查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列。

selectsno,degreefromscwherecno='c03'orderbydegreedesc；

(4)查询各个课程号及相应的选课人数。

selecteno,count(*)fromscgroupbyeno；

(5)统计每门课程的选课人数和最高分。

selecteno,count(*),max(degree)fromscgroupbyeno；

(6)统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。

selectsno,count(♦),sum(degree)fromscgroupbysnoorderbycount(♦)desc；

(7)查询选修了3门以上课程的学生学号。

selectsnofromscgroupbysnohavingcount(♦)>3；

(8)查询成绩不及格的学生学号及课程号，并按成绩降序排列。

selectsno,enofromscwheredegree<60orderbydegreedesc；

(9)查询至少选修一门课程的学生学号。

selectsnofromscgroupbysnohavingcount(♦)>=1；

或：selectdistinctsnofromsc；

(10)统计输出各系学生的人数。

selectsdept,count(♦)fromstudentgroupbysdept；

(11)统计各系学生人数。

selectsdept,count(♦)fromstudentgroupbysdept；

(12)统计各系的男、女生人数。

selectsdept,ssex,count(♦)fromstudentgroupbysdept,ssex；

(13)统计各班级的学生人数。

selectclassno,count(*)fromstudentgroupbyclassno；

(14)统计各班的男、女生人数。

selectclassno,ssex,count(*)fromstudentgroupbyclassno,ssex；

(15)统计各系的老师人数,并按人数降序排序.

selectdeptno,count(*)fromteachergroupbydeptnoorderbycount(*)asc;

(16)统计不及格人数超过10人的课程号。

selecteno,count(*)fromscwheredegree<60groupbyenohavingcount(*)>10；

(17)查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序。

select*fromstudent

wheresdept='软件工程系'andssex='男'orderbysbirthdayasc,saddressdesc；

3.思考题

(1)聚集函数能否直接使用在SELECT子句、HAYING子句、WHERE子句、GROUPBY子句中？

可以直接用在SELECT子句、HAVING子句、GROUPBY子句中

不能用在WHERE子句中

(2)WHERE子句与HAVING子句有何不同？

HAVING条件作用于结果组，选择满足条件的结果组；

WHERE条件作用于被查询的表，从中选择满足条件的记录。

(3)对杳询结果进行重新排序时,必须指定排序方式吗？

不用，可以安照默认的排序方式

<4）在对数据进行分组统计数据时，能不能按照多个字段进行分组？

能

三、多表连接查询

1.实践目的

（1）掌握SELECT语句在多表查询中的应用。

（2）掌握多表连接的几种连接方式及应用。

（3）能够灵活运用多表连接查询解决实际问题。

2.实践内容和要求

完成下面查询。

（1）查询计算机工程系女学生的学生学号、姓名及考试成绩。

selecta.sno,sname,degreefromstudenta,scb

wherea.sno=b.snoandsdept='计算机工程系'andssex='女'；

（2）查询“李勇”同学所选课程的戌绩。

selecteno,degreefromstudenta,scbwherea.sno=b.snoandsname='李勇'；

（3）查询“李新”老师所授课程的课程名称。

selectcnamefromteachera,teachingb,coursec

wherea.Tno=b.tnoandb.cno=c.enoandTname=’李新'；

（4）查询女教师所授课程的课程号及课程名称。

selectb.eno,cnamefromteachera,teachingb,coursec

wherea.Tno=b.tnoandb.cno=c.enoandTsex='女'；

（5）查询至少选修一门课程的女学生姓名。

selectsnamefromstudenta,scbwherea.sno=b.snoandssex='女

（6）查询姓“王”的学生所学的课程名称。

selectcnamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandsnamelike'王％’；

<7）查询选修“数据库”课程且成绩在80〜90分的学生学号及成绩。

selecta.sno,degreefromsca,courseb

wherea.cno=b.enoandcname='数据库原理及应用'anddegreebetween80and90；

（8）查询课程成绩及格的男同学的学生信息及课程号与成绩。

selecta.*.eno.degreefromstudenta,scb

wherea.sno=b.snoanddegree>60andssex=,男'；

（9）查询选修“C04”课程的学生的平均年龄。

selectavg（year（curdate（））-year（sbirthday））fromstudenta,scb

wherea.sno=b.snoandcno=*c04,；

（10）查询选修课程名为“数学”的学生学号和姓名。

selecta.sno,snamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandcname='高等数学'；

<11）查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。

selectb.tno,sno,degreefromteachera,teachingb,scc

wherea.Tno=b.tnoandb.cno=c.enoandTname='钱军

（12）查询在第3学期所开课程的课程名称及学生的成绩。

selectcname,degreefromteachinga,courseb,scc

wherea.cno=b.enoandb.cno=c.enoandcterm=3;

（13）查询“C02”课程不及格的学生信息。

selecta.*fromstudenta,scb

wherea.sno=b.snoandcno=,c02*anddegree<60；

<14）查询软件系成绩在90分以上的学生姓名、性别和课程名称。

selectsname,ssex,cnamefromstudenta,scb,coursec

wherea.sno=b.snoandb.cno=c.enoandsdept二'软件工程系'anddegree>90；

（15）查询同时选修了“C04”和“C02”课程的学生姓名和成绩。

selectsname.b.degree,c.degreefromstudenta,scb,scc

wherea.sno=b.snoanda.sno=c.snoandb.sno=c.snoandb.cno='c04'andc.cno=,c02*；

3.思考题

（1）指定一个较短的别名有什么好处？

简化表名，一个查询语句里可能会多次用到别名，所以较短的别名便于应用

（2）内连接与外连接有什么区别？

内连接中，只有在两个表中匹配的行才能在结果中集中出现；

在外连接中，可以只限制一个表，而对另一个表不加限制（所有的行都出现在结果集中）。

四、嵌套查询

1.实践目的

（1）掌握嵌套查询的使用方法。

（2）掌握相关子查询与嵌套子查询的区别。

（3）掌握带IN谓词的子查询的使用方法。

（4）掌握带比较运算符的子查询的使用方法。

（5）掌握带ANY或ALL谓词的子查询的使用方法。

（6）了解带EXISTS谓词的子查询的使用方法。

2.实践内容和要求

完成下面查询。

<1）查询“李勇”同学所选课程的成绩。

selecteno,degreefromsc

wheresnoin（selectsnofromstudentwheresname二’李勇'）；

（2）查询“李新”老师所授课程的课程名称。

selectcnamefromcourse

wherecno=（selectenofromteachingwheretno=（selectTnofromteacherwhereTname=r

李新'））；

<3）查询女教师所授课程的课程号及课程名称。

selecteno,cnamefromcourse

whereenoin（selectenofromteachingwheretnoin（selectTnofromteacherwhereTsex=,

女'））；

（4）查询姓“王”的学生所学的课程名称。

selectcnamefromcourse

whereenoin（selectenofromscwheresnoin（selectsnofromstudentwheresnamelike

'王%'））;

（5）查询“C02”课程不及格的学生信息。

select*fromstudent

wheresnoin（selectsnofromscwherecno=*c02,anddegree<60）;

（6）查询选修“数据库”课程且成绩在80〜90分的学生学号及成绩。

selectsno,degreefromsc

whereeno=（selectenofromcoursewherecname='数据库原理及应用'）anddegreebetween

80and90；

(7)查询选修“C04”课程的学生的平均年龄。

selectavg(year(curdatc0)-year(sbirthday))fromstudent

wheresnoin(selectsnofromscwherecno=1c041)；

(8)查询选修课程名为“数学”的学生学号和姓名。

selectsno,snamefromstudent

wheresnoin(selectsnofromscwherecno=(selectenofromcoursewherecname=1高等

数学'))；

(9)查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。

selectc.eno,sno,degreefromteachingb,scc

whereb.cno=c.enoandTno=(selectTnofromteacherwhereTname='钱军')；

(10)查询在第3学期所开课程的课程名称及学生的成绩。

selectcname,degreefromcoursea,scb

wherea.cno=b.enoandb.enoin(selectenofromteachingwherecterm=3)；

(11)查询与“李勇”同一个系的同学姓名。

selectsnamefromstudent

wheresdeptin(selectsdeptfromstudentwheresname=’李勇’)；

(12)查询学号比“刘晨”同学大，而出生日期比她小的学生姓名。

selectsnamefromstudent

wheresno>all(selectsnofromstudentwheresname='刘晨')

andyear(curdate())-year(sbirthday)<al1(selectyear(curdate())-year(sbirthday)from

studentwheresname='刘晨')；

(13)查询出生日期大于所有女同学出生日期的男同学的姓名及系别。

selectsname,sdeptfromstudent

wheresbirthday>all(selectsbirthdayfromstudentwheressex='女')

andssex=*男*；

(14)查询成绩比该课程平均成绩高的学生的学号及成绩。

selectsno,degreefromscawhere

degree>(selectavg(degree)fromscbwhereb.cno=a.eno)；

(15)查询不讲授“C01”课的教师姓名。

selectTnamefromteacher

whereTnonotin(selecttnofromteachingwherecno='c017);

(16)查询没有选修“C02”课程的学生学号及姓名。

selectsno,snamefromstudent

wheresnonotin(selectsnofromscwherecno=1c02')；

(17)查询选修了“数据库”课程的学生学号、姓名及系别。

selectsno,sname,sdeptfromstudent

wheresnoin(selectsnofromsc

wherecno=(selectenofromcoursewherecname='数据库原理及应用'))；

3.思考题

(1)IN与“=”在什么情况下作用相同？

子查询返回值唯一时

(2)使用存在量词[NOT]EXISTS的嵌套查询时，何时外层查询的WHER3条件为真？何时为假？

子查询有满足条件的记录时为真.反之.条件为假

（3）当既能用连接查询又能用嵌套查询时，应该选择哪种查询较好？为什么？

嵌套查询执行速度比较快，执行效率高

（4）子查询一般分为几种？

两种：相关子查询和不相关子查询

（5）相关子查询的执行过程是什么？

1、子查询为外部查询的每一个元组执行一次，外部查询将子查询引用列的值传给子查询。

2、如果子查询的任何行与其匹配，外部查询则取此行放入结果表。

3、再回到1追到处理完外部表的每一行。

五、数据更新

1.实训目的

（1）掌握利用INSERT语句实现对表数据的插入操作。

（2）掌握利用UPDATE语句实现对表数据的修改操作。

（3）掌握利用DELETE语句实现对表数据的删除操作。

2.实践内容和要求

利用SELECTINTO…语句备份student、sc、course这3个表，备份表名自定。

（1）向student表中插入记录（"2005010203","张静"，"女「1981-3-21",”软件H程系"，”软件技

术。

insertintostudent（sno,sname,sbirthday,ssex,sdept,speciality）

values（'20050203','张静'，'1981—3-21女'，'CS','电子商务'）；

（2）插入学号为“2005010302”、姓名为“李四”的学生信息。

insertintostudent（sno,sname）values（'20050302‘，'李四'）；

（3）把计算机工程系的学生记录保存到表TS中（TS表已存在，表结构与student表相同）。

InsertintoTSselect*fromstudentwheresdept='计算机工程系'

（4）将学号为“2005010202”的学生姓名改为“张华”，系别改为“电子工程系”，专业改为“电

子应用技术”。

updatestudentsetsname二'张华'，sdept='CS',speciality'多媒体技术'

wheresno=,200502021；

（5）将“李勇”同学的专业改为“计算机信息管理”。

updatestudentsetspecialit产'计算机信息管理'wheresname='李勇'；

（6）删除学号为“2005010302”的学4记录.

deletefromstudentwheresno=,20050302,；

（7）删除“计算机工程系”所有学生的选课记录。

deletefromscwheresnoin（selectsnofromstudentwheresdept='计算机工程系'）；

（8）删除sc表中尚无成绩的选课记录。

deletefromsc_copywheredegreeisnull；

（9）把“刘晨”同学的选修记录全部删除。

deletefromscwheresno=（selectsnofromstudentwheresname='刘晨’）；

3.思考题

（1）如何从备份表中恢复3个表？

可以利用insertinto〈表名〉select*from〈备份表名）语句

（2）DROP语句和DELETE语句的本质区别是什么？

drop命令是删除表

delete命令是删除表里面的记录，表还存在

（3）利用INSERT、UPDATE和DELETE语句可以同时对多个表进行操作吗？

不可以

课外拓展内容参考答案:

(l)select*fromToyswherecToyNamelike'%Racer%'；

(4)select♦shopperwherevFirstNamelike's%'；

(3)selectdistinctcStatefromRecipient;

(4)selectcToyName,cCategoryIDfromToys

(5)selectcOrderNo,cToylD,vDescriptionfromOrderDetaila,Wrapperb

Wherea.cWrapperId=b.cWrapperld

(6)selectcToyName,cBrandld,cCategoryIdfromToys；

(7)selecta.vFirstName,a.vLastName,a.vAddress,b.vFirstName,b.vLastName.b.vAddress

fromShoppera,Recipientb,Ordersc

wherea.cShopperId=c.cShopperldandb.cOrderNo=c.cOrderNo

(8)selectcToyName,b.cCartldfromToysaleftjoinShoppingCartbona.cToyId=b.cToyld

(9)insertintoPremiumToysselect*fromToyswheremToyRate>20

(10)selectvFirstName,vLastName,vAddress,cCityfromShopper；

selectvFirstName,vLastName,vAddress,cCityfromRecipient；

(11)selectcToyNamefromToyswheremToyRate=(selectmax(mToyRate)fromToys)

(12)selectmShippingChargesfromOrderswherecShippingModeId=*01'

(13)selectcOrderNofromOrdersorderbymTotalCostdesclimit3

(14)select*fromToyswheremToyRatebetween10and20

(15)selectvLastName,vFirstName,vEmailldfromShopperwherecState

in('California'Illinois*)；

(16)selectcOrderNo,dOrderDate,cShopperld,mTotalCostfromOrders

WheredOrderDate='2001-05-20*andmTotalCost>75

(17)selectcToyNamefromToyswherecCategoryId=,002*andmToyRate<20

(18)select*fromOrders

(19)select♦fromShopperwherecStateO*Texas'

(20)selectcToyName,mToyRatefromToysorderbymToyRatedesc

(21)selectcToyNamefromToyswheremToyRate<20orderbymToyRate

(22)selectcOrderNo,cShopperld,mTotalCostfromOrdersorderbymTotalCostasc

(23)selectcount(distinctcToyld)fromOrderDetail

(24)selectmax(mToyRate),min(mToyRate),avg(mToyRate)fromToys

(25)selectsum(mTotalCost)fromOrders

(26)selectcOrderNo,mToyCostfromOrderDetail

(27)selectcOrderNo,mToyCostfromOrderDetailwheremToyCost>50

(28)selectcToyldfromPickOfMonthwhereiYear=,2000'orderbyiTotalSoldlimit5

(29)selectcOrderNo,a.cToyld,mToyRatefromToysa,OrderDetailbwherea.cToyId=b.cToyld；

selectmToyCostfromOrderDetail；

selectsum(mToyCost)fromOrderDetail；

(30)selectcToyName,vToyDescription,mToyRatefromToys；

(31)selectcOrderNo,dShipmentDate,dActualDeliverDate,dActualDeliverDate-dShipmentDateas

DaysinTransitfromShipment

(32)selectcOrderNo.dActualDeliverDate-dShipmentDateasDaysinTransitfromShipmentwhere

cOrderNo=,000009'

项目7实训：索引、视图的创建与管理使用

1.实训目的

（1）理解索引的概念与类型。

（2）掌握创建、更改、删除索引的方法。

<3）掌握维护索引的方法。

（4）理解视图的概念及优点。

（5）掌握视图的创建、修改和删除。

（6）掌握使用视图来访问数据的方法。

2.实训内容及要求

（1）使用Navicat图形工具创建、管理索引。

①在数据库test下创建workinfo表。创建表的同时在id字段上创建名为index_id的唯一性索弓I,

而且以降序的格式排列。workinfo表结构内容如表7.1所示。

表7.1workinfo表结构内容

字段名字段描述数据类型主键外键非空唯一自增

id编号INT(IO)是否是是是

name职位名称VARCHAR(20)否否是否否

type职位类别VARCHAR(IO)否否否否否

address工作地址VARCIIAR(5O)否否否否否

wages工资INT否否否否否

contents工作内容INTYTEXT否否否否否

extra附加信息TEXT否否否否否

②创建索引。为name字段创建索引index_name,在type和address字段上创建名为index_t的

组合索引。

createindexindex_nameonwworkinfo(sname);

createindexindex_tonworkinfo(type,address)；

③在extra字段上创建名为index_ext的全文索引。

createspatialindexindex_extonworkinfo(extra)；

④删除索引。删除workinfo表的唯一性索引index_id。

dropindexindex_idonworkinfo；

（2）使用SQL语句创建、管理索引。

①利用createtabic语句在test数据库中创建数据表writers,其表结构内容如表7.2所示。创

建表的同时在w_id字段上添加名称为uniqidx的唯一索引。

表7.2writers表结构内容

字段名字段描述数据莞型主键外键非空唯一自增

w_id编号INT(IO)是否是是是

w_name作者姓名VARCHAR(20)否否是否否

waddress作者地址VARCIIAR(50)否否否否否

w_age年龄INT否否是否否

w_note说明TEXT否否否否否

②使用altertable语句在w_name字段上建立名称为nameidx的普通索引。

altertablewritersaddindexnameidx(w_name)

③使用createindex语句在w_address和w_age字段上建立名称为multiidx的组合索引。

createindexmultiidxonwriters(w_address,w_age)

©使用createindex语句在w_note字段上建立名称为ftidx的全文索引。

createfulltcxtindexftidxonwriters(w_notc)

⑤删除索引。利用altertable语句将全文索引ftidx删除，利用dropindex语句将nameidx索

引删除。

altertablewritersdropindexftidx；

dropindexnameidxonwreites;

(3)使用SQL语句创建、管理视图。

①创建视图。

a.创建一个名为sc_viewl的视图，从数据库gradem的sc表中查询出成绩大于90分的所有学生选

修课程成绩的信息。

createviewsc_viewlasselect*fromscwheredegree>90；

b.创建一个名为sc_view2的视图，从数据库gradem的sc表中查询出成绩小于80分的所有学生的

学号.课程号、成绩等信息。

createviewsc_view2

asselectsno,eno,degreefromscwheredegree<80；

c.创建一个名为sc_view3的视图，由数据库gradem的student、course、sc表创建一个显示

“20070303”班学生选修课程(包括学生姓名、课程