1、1、 selectUSE studentGOSELECT stud_id, name, birthday, gender, mark FROM stud_info WHERE name LIKE N郑_USE studentGOSELECT teacher_id, name, tech_title, salaryFROM teacher_infoWHERE tech_title IN (N助教, N讲师, N副教授)USE studentGOSELECT AVG (grade) FROM stud_grade WHERE course_id=0401010102USE studentGOSEL
2、ECT stud_id 学号, name 姓名, year(getdate()-year(birthday) 年龄, birthday 出生日期FROM stud_infoWHERE gender = N男ORDER BY birthday ASCSE studentGOSELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩FROM stud_infoWHERE substring(stud_id,3,2) =01GROUP BY substring(stud_id,5,2)USE studentGOSELECT tech_title, avg
3、(age)FROM teacher_infoGROUP BY tech_titleHAVING tech_title = N讲师USE studentGOSELECT tech_title, salaryFROM teacher_infoWHERE tech_title = N讲师ORDER BY tech_titleCOMPUTE sum(salary)/* 查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩 */USE studentGOSELECT stud_info.stud_id, stud_grade.name, stud_info.zipcode, stud_grad
4、e.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id = stud_grade.stud_id/* 在 FROM 子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */USE studentGOSELECT teacher_info.teacher_id, teacher_info.name, lesson_info.course_nameFROM lesson_info INNER JOIN teacher_infoON (lesson_info.course_id = teacher_info.course_id)
5、/* 在 stud_info 与 stud_grade 中按学号 stud_id 进行等值连接，以查询所有参加考试的学生基本信息和成绩分数。 */USE studentGOSELECT *FROM stud_info INNER JOIN stud_gradeON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id/* stud_info 和 stud_grade 采用自然连接以限制结果集的冗余列数据 */USE studentGOSELECT stud_grade.*,stud_info.telcode,stud_in
6、fo.markFROM stud_grade INNER JOIN stud_infoON stud_grade.stud_id=stud_info.stud_idORDER BY stud_grade.stud_idUSE studentGOINSERT INTO stud_info -为了说明方便，先在学生信息表中插入一条新记录VALUES (0401010704,N王一明 ,03/03/1986,N男,N 甘肃省兰州市,0621-2221837,590000,573)SELECT stud_info.stud_id,stud_info.name,stud_grade.course_idF
7、ROM stud_info LEFT OUTER JOIN stud_gradeON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 学生信息表 stud_info 右外连接学生成绩表 stud_grade */USE studentGOSELECT stud_info.stud_id,stud_info.name,stud_grade.course_idFROM stud_grade RIGHT OUTER JOIN stud_infoON
8、 stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_info.name,stud_grade.course_id/* 教师信息表 teacher_info 全外连接课程信息表 lesson_info */USE studentGOSELECT lesson_info.course_name, teacher_info.name, teacher_info.teacher_idFROM lesson_info FULL OUTER JOIN teacher_infoON lesson_info.course_i
9、d = teacher_info.course_idORDER BY lesson_info.course_name, teacher_info.name, teacher_info.teacher_id/* 查询学生成绩表 stud_grade 中与学号为“0401020103”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */USE studentGOSELECT a.stud_id, a.name, a.course_id, a.gradeFROM stud_grade a, stud_grade bWHERE a.course_id=b.course_id AND a.stud
10、_id0401020103 AND b.stud_id=0401020103/* 5.21 查询与学号为“0401030101”的学生同在计算机应用技术专业(学号 stud_id 中第 5 位和第 6 位为“专业编号”) 学习的所有学生的学号、姓名、性别及电话号码 */USE studentGOSELECT stud_id, name, gender, telcodeFROM stud_infoWHERE substring(stud_id,5,2) = (SELECT substring(stud_id,5,2)FROM stud_infoWHERE stud_id =0401030101)
11、/* 5.22 在学生成绩表中查询课程类型为“考试”的学生学号、姓名、成绩 */USE studentGOSELECT stud_id,name,gradeFROM stud_gradeWHERE course_id IN (SELECT course_id FROM lesson_infoWHERE course_type =N考试)/* 5.23 查询课程号为“0401010106”的多媒体技术这门课的成绩在 80 至 89 分的学生的学号、姓名 */USE studentGOSELECT stud_id,nameFROM stud_infoWHERE EXISTS (SELECT * F
12、ROM stud_gradeWHERE stud_grade.stud_id = stud_info.stud_id AND(grade BETWEEN 80 AND 89) AND course_id=0401010106)/* 5.24 查询所学专业同为“计算机控制技术”或年龄为 21 岁的所有学生的姓名 */USE studentGOSELECT stud_id,name FROM stud_info WHERE substring(stud_id,5,2) =03UNIONSELECT stud_id,name FROM stud_info WHERE DATEDIFF(year, b
13、irthday, getdate()=214、 第 6 章案例/* 6.1 针对表 stud_info 创建一个简单视图 */USE studentGOCREATE VIEW stud_view2ASSELECT stud_id, name, address, telcode, zipcodeFROM stud_infoselect * from stud_view2/*6.2 使用 WITH ENCRYPTION 加密选项为表 stud_info 创建视图 */USE studentGO/* 查看 stud_info 表中的所有数据。 */SELECT * FROM stud_infoGO/
14、* 基于 stud_info，创建视图 stud_view3。*/CREATE VIEW stud_view3WITH ENCRYPTIONASSELECT stud_id as 学号, name as 姓名, address as 地址,telcode as 电话号码, zipcode as 邮政编码FROM stud_info WHERE mark = 560GOsp_helptext stud_view3sp_helptext stud_view2/* 查看新建视图 stud_view3 中的所有数据。*/SELECT * FROM stud_view3/*6.3 建立计算机系(学号第
15、34 位为“01”)学生的视图，并要求进行修改和插入操作时仍需保证视图只有计算机系的学生 */CREATE VIEW stud_computerASSELECT stud_id, name, genderFROM stud_infoWHERE substring(stud_id,3,2)=01WITH CHECK OPTIONSELECT * FROM stud_computer/* 6.4 建立课室(classroom_info)、教师(teacher_info)、课程(lesson_info)、课程安排表(teach_schedule)相互对照的视图(schedule_view) */US
16、E StudentGOCREATE VIEW schedule_viewASSELECT lesson.course_name, teacher.name,classroom.room_name, schedule.course_week,schedule.course_time, schedule.course_idFROM classroom_info classroom, teacher_info teacher, lesson_info lesson, teach_schedule scheduleWHERE classroom.room_id=schedule.room_idAND
17、teacher.teacher_id=schedule.teacher_idAND lesson.course_id=schedule.course_idSELECT * FROM schedule_view/*6.5 修改视图 stud_view2 定义 */USE studentGO/* 显示修改前视图 stud_view2 的内容。*/SELECT * FROM stud_view2GOALTER VIEW stud_view2ASSELECT stud_id, name, gender, markFROM stud_infoWHERE mark = 90) SET p_90=p_90+
18、1ELSE IF(p_grade = 80) SET p_80=p_80+1ELSE IF (p_grade = 70) SET p_70=p_70+1ELSE IF (p_grade = 60) SET p_60=p_60+1ELSE SET p_others=p_others+1IF(FETCH_STATUS = 0) GOTO LOOPPRINT STR(p_100)+,+STR(p_90)+,+STR(p_80)+,PRINT STR(p_70)+,+STR(p_60)+,+STR(p_others)CLOSE course_cursor - 关闭游标 */DEALLOCATE course_cursor - 释放游标 */
