数据库技术与应用复习资料

数据库技术与应用复习资料数据库技术与应用复习资料数据库技术与应用复习资料xxx公司数据库技术与应用复习资料文件编号：文件日期：修订次数：第1.0次更改批准审核制定方案设计，管理制度使用SQL语句创建数据表&数据操作Insert、Update、Delete利用Transact-SQL语句创建表booksales的代码。USEtest01GOCREATETABLEbooksales( book_idnchar(6)NOTNULL, sellnumintNOTNULL, selldatedatetimeNOTNULL)ONPRIMARY利用insert语句为表booksales添加数据：INSERTINTObooksalesVALUES('m00011',7,20/12/2008)利用update语句为表booksales更新数据：UPDATEbooksalesSETsellnum=11WHEREbook_id='m00011'利用delete语句删除表booksales的数据：DELETEFROMbooksalesWHEREbook_id='m00011'Transact-SQL语句基础1–数据完整性将teaching数据库中score表的studentno列设置为引用表student的外键。ALTERTABLEScoreADDCONSTRAINTFK_score_studentFOREIGNKEY(studentno)REFERENCESstudent(studentno)将teaching数据库中class表的classname创建UNIQUE约束。ALTERTABLEclassADDCONSTRAINTUQ_classUNIQUE(classname) 执行如下插入语句，查看提示信息 INSERTINTOclassVALUES(‘090602’,’计算机0902’,’计算机学院’,’马文斐’)3.为teaching数据库中student表的birthday列创建CHECK约束，规定学生的年龄在17-25岁之间。 ALTERTABLEstudent ADDCONSTRAINTCK_birthdayCHECK(YEAR(GETDATE())-YEAR(birthday))BETWEEN17AND25 执行如下插入语句，查看提示信息 INSERTINTOstudent(studentno,sname,sex,birthday,classno)VALUES(‘08’,’张源’,’男’,’1983-04-05’,’090501’) 提示：表达式YEAR(GETDATE())-YEAR(birthday)数据检索11）SELECT…FROM…2）条件WHERE3）生成新表INTO4）比较运算符（数值类型、字符串、日期时间，YEAR()）5）模糊查询LIKE6）逻辑运算符ANDORNOT7）检索一定范围的值BETWEEN…AND…8）利用列表值检索IN9）改变列名AS10）排序ORDERBY11）消除重复行DISTINCT12）输出前n行TOPnTOPnPERCENT13）分组GROUPBY筛选HAVING14）聚合函数15）附加行汇总值COMPUTE16）多表连接INNERJOIN17）使用子查询1.查询course表中所有的记录。SELECT*FROMcourse2.查询student表中女生的人数。SELECT*FROMstudentWHEREsex='女'查询teacher表中每一位教授的教师号、姓名和专业名称。SELECTteacherno,tname,majorFROMteacherWHEREprof='教授'利用现有的表生成新表，新表中包括学号、课程号和总评成绩。其中：总评成绩=final*+usually*SELECT,sname,courseno,final*+usually*AS总评成绩INTOzongpingFROMstudent,scoreWHERE=GOSELECT*FROMzongping查询student表中所有年龄大于20岁的男生的姓名和年龄。SELECTsname,Year(GetDate())-Year(birthday)ASageFROMstudentWHEREYear(GetDate())-Year(birthday)>20GO查询计算机学院教师的专业名称。SELECTmajorAS专业名称FROMteacherWHEREdepartment='计算机学院'GO查询Email使用126邮箱的所有学生的学号、姓名和电子邮箱地址。SELECTstudentno,sname,EmailFROMstudentWHEREEmailLike'%%'GO查询score表中选修’c05109’或’c05103’课程，并且课程期末成绩在90~100分之间的学号和期末成绩。SELECT*FROMscoreWHERE((coursenoin('c05109','c05103'))and(finalbetween90and100))查询student表中所有学生的基本信息，查询结果按班级号classno升序排列，同一班级中的学生按入学成绩point降序排列。SELECT*FROMstudentORDERBYclassnoASC,pointDESC查询选修’c05109’课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。（提示：TOP5）SELECTTOP5studentno,courseno,finalFROMscoreWHEREcourseno='c05109'ORDERBYfinalDESC数据检索2–Groupby、Having、Computeby1.按性别分组，求出student表中每组学生的平均年龄。SELECTsex,AVG(YEAR(GETDATE())-YEAR(birthday))ASageFROMstudentGROUPBYsex2.查询各班学生的人数。SELECTclassno,COUNT(*)AS人数FROMstudentGROUPBYclassnoORDERBYclassno3.查询各课程期末成绩的最高分和最低分。SELECTcourseno,MAX(final)AS最高成绩,MIN(final)AS最低成绩FROMscoreGROUPBYcourseno4.查询教两门及以上课程的教师编号、任课班级数。SELECTteacherno,COUNT(courseno)AS班级数FROMteach_classGROUPBYteachernoHAVINGCOUNT(courseno)>=25.查询课程编号以’c05’开头、被3名及以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。SELECTcourseno,COUNT(studentno)AS选修人数,AVG(final)AS平均分FROMscoreWHEREcoursenoLIKE'c05%'ANDfinalISNOTNULLGROUPBYcoursenoHAVINGCOUNT(studentno)>=3ANDAVG(final)>=75ORDERBYAVG(final)DESCSQL语句的高级应用1–内连接、外连接1.查询所有班级的期末成绩平均分，并按照平均分降序排列。表：score,student2joinSELECTclassno,AVG(final)AS班级平均分FROMstudentINNERJOINscoreON=WHEREfinalISNOTNULLGROUPBYclassnoORDERBYAVG(final)DESC2.查询两门及以上课程的期末成绩超过80分的学生的姓名及其平均成绩。表：student,scoreSELECT,sname,AVG(final)AS平均分FROMstudentJOINscoreON=WHEREfinalISNOTNULLandfinal>=80GROUPBY,snameHAVINGCOUNT(*)>=2SQL语句的高级应用2–使用子查询3.查询入学考试成绩最高的学生的学号、姓名和入学成绩。=SELECTstudentno,sname,pointFROMstudentWHEREpoint=(SELECTMAX(point)FROMstudent)4.查询所有教授’c05127’号课程的教师信息。InSELECT*FROMteacherWHEREteachernoIN(SELECTteachernoFROMteach_classWHEREcourseno='c05127')视图与索引使用SQL语言1.创建一个视图v_teacher，查询所有“计算机学院”的教师信息,添加WITHCHECKOPTION选项。CREATEVIEWv_teacherASSELECT*FROMteacherWHEREdepartment='计算机学院'WITHCHECKOPTIONGOSELECT*FROMv_teacher2.创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排列。CREATEVIEWv_avgstuASSELECTTOP100,sname,avg(final)AS'平均分'FROMstudentJOINscoreON=GROUPBY,snameORDERBYavg(final)GOSELECT*FROMv_avgstu3.通过视图v_teacher向基表teacher中分别插入数据(‘05039’,‘张馨月’,’计算机应用’,’讲师’,’计算机学院’)和(‘06018’,‘李诚’,’机械制造’,’副教授’,’机械学院’)，并查看插入数据情况。INSERTINTOv_teacherVALUES('05039','张馨月','计算机应用','讲师','计算机学院')GOSELECT