数据库应用技术---案例.doc

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 studentGOSELECT 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(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_, stud_info.zipcode, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id = stud_grade.stud_id/* 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */USE studentGOSELECT teacher_info.teacher_id, teacher_, lesson_info.course_nameFROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id)/* 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的学生基本信息和成绩分数。 */USE studentGOSELECT *FROM stud_info INNER JOIN stud_grade ON 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_info.markFROM stud_grade INNER JOIN stud_info ON 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甘肃省兰州市,590000,573)SELECT stud_info.stud_id,stud_,stud_grade.course_idFROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_,stud_grade.course_id/* 学生信息表stud_info右外连接学生成绩表stud_grade */USE studentGOSELECT stud_info.stud_id,stud_,stud_grade.course_idFROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_,stud_grade.course_id/* 教师信息表teacher_info全外连接课程信息表lesson_info */USE studentGOSELECT lesson_info.course_name, teacher_, teacher_info.teacher_idFROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_idORDER BY lesson_info.course_name, teacher_, teacher_info.teacher_id/* 查询学生成绩表stud_grade中与学号为“0401020103”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */USE studentGOSELECT a.stud_id, , a.course_id, a.gradeFROM stud_grade a, stud_grade bWHERE a.course_id=b.course_id AND a.stud_id0401020103 AND b.stud_id=0401020103/* 查询与学号为“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_info WHERE stud_id =0401030101)/* 在学生成绩表中查询课程类型为“考试”的学生学号、姓名、成绩 */USE studentGOSELECT stud_id,name,gradeFROM stud_gradeWHERE course_id IN (SELECT course_id FROM lesson_info WHERE course_type =N考试)/* 查询课程号为“0401010106”的多媒体技术这门课的成绩在80至89分的学生的学号、姓名 */USE studentGOSELECT stud_id,nameFROM stud_infoWHERE EXISTS (SELECT * FROM stud_grade WHERE stud_grade.stud_id = stud_info.stud_id AND (grade BETWEEN 80 AND 89) AND course_id=0401010106)/* 查询所学专业同为“计算机控制技术”或年龄为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, birthday, getdate()=212、 STUDENTcreate database studentgoUSE studentGOCREATE TABLE teacher_info (teacher_id CHAR(6) NOT NULL, name NVARCHAR(4) NOT NULL, gender NCHAR(1), age INT, tech_title NVARCHAR(5), telephone VARCHAR(12), salary DECIMAL(7,2), course_id CHAR(10) );USE studentGOCREATE TABLE teach_schedule(course_id CHAR(10) NOT NULL, course_time DATETIME, course_week CHAR(2), room_id CHAR(6), deptcode CHAR(2), teacher_id CHAR(6)USE studentGOCREATE TABLE stud_info ( stud_id CHAR(10) NOT NULL, name NVARCHAR(4) NOT NULL, birthday DATETIME, gender NCHAR(1), address NVARCHAR(20), telcode CHAR(12), zipcode CHAR(6), mark DECIMAL(3,0) )USE studentGOCREATE TABLE stud_grade(stud_id CHAR(10) NOT NULL, name NVARCHAR(4) NOT NULL, course_id CHAR(10), grade DECIMAL(4,1)USE studentGOCREATE TABLE staffroom_info ( jysh_id CHAR(4) not null, jysh_name NVARCHAR(10), jysh_type NCHAR(2), jysh_leader NVARCHAR(4) )USE studentGOCREATE TABLE specialty_code ( speccode CHAR(6), specname NVARCHAR(10) )USE studentGOCREATE TABLE lesson_info(course_id CHAR(10) NOT NULL, course_name NVARCHAR(12) NOT NULL, course_type NCHAR(2) NOT NULL, course_time INT NOT NULL, course_mark DECIMAL(3,1)USE studentGOCREATE TABLE dept_code ( deptcode CHAR(2), deptname NVARCHAR(10)USE studentGOCREATE TABLE classroom_info(room_id CHAR(6) NOT NULL, room_name NVARCHAR(8), room_type NVARCHAR(5), room_device NVARCHAR(10), room_size DECIMAL(3,0)USE studentGOINSERT INTO teacher_info VALUES(010101,N刘娜,N女,34,N讲师1418,0401010102);INSERT INTO teacher_info VALUES(010106,N王吉林,N男,32,N讲师1418,0401010103);INSERT INTO teacher_info VALUES(010102,N邵云鹏,N男,45,N教授1458,0401010105);INSERT INTO teacher_info VALUES(010104,N赵一欧,N女,26,N助教1380,0401010101);INSERT INTO teacher_info VALUES(010105,N王小悦,N女,35,N讲师1448,0401010104);INSERT INTO teacher_info VALUES(010103,N孙乐多,N男,27,N助教1380,0401010106);USE studentGOINSERT INTO teach_schedule VALUES(0401010101,08-30-2004,15,120703,01,010104);INSERT INTO teach_schedule VALUES(0401010102,08-30-2004,15,120704,01,010101);INSERT INTO teach_schedule VALUES(0401010103,08-30-2004,13,120705,01,010106);INSERT INTO teach_schedule VALUES(0401010104,08-30-2004,10,120706,01,010105);INSERT INTO teach_schedule VALUES(0401010105,08-30-2004,19,120707,01,010102);INSERT INTO teach_schedule VALUES(0401010106,08-30-2004,14,120708,01,010103);USE studentGOINSERT INTO STUD_INFO (STUD_ID,NAME,BIRTHDAY,GENDER, ADDRESS,TELCODE,ZIPCODE,MARK) VALUES (0401010811,N张源,12-05-1986,N男,N北京市海淀区100080,560);INSERT INTO STUD_INFO (STUD_ID,NAME,BIRTHDAY,GENDER, ADDRESS,TELCODE,ZIPCODE,MARK)VALUES (0401020201,N赵明,08-06-1986,N男,N上海市浦东区201700,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401030101,N王刚,01-02-1986,N男,N天津市南开区300000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401050201,N陈红,10-25-1986,N女,N武汉市汉口区430000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401040112,N孙强,06-07-1986,N男,N重庆市沙坪坝400000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK)VALUES (0401020103,N李伟,09-01-1986,N男,N北京市大兴县102600,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401010634,N钱昆,12-06-1986,N男,N广州市海珠区510000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401030213,N郑芳,08-09-1986,N女,N江苏省南京市210000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401010102,N袁飞,03-11-1986,N男,N湖南省长沙县410000,560);INSERT INTO STUD_INFO (STUD_ID ,NAME ,BIRTHDAY ,GENDER , ADDRESS ,TELCODE ,ZIPCODE ,MARK) VALUES (0401040123,N孔荣,05-31-1986,N男,N云南省昆明市650000,600);USE studentGOINSERT INTO stud_grade VALUES(0401010811,N张源,0401010102,90);INSERT INTO stud_grade VALUES(0401020201,N赵明,0401010105,89);INSERT INTO stud_grade VALUES(0401030101,N王刚,0401010103,87);INSERT INTO stud_grade VALUES(0401050201,N陈红,0401010101,91);INSERT INTO stud_grade VALUES(0401040112,N孙强,0401010104,83);INSERT INTO stud_grade VALUES(0401020103,N李伟,0401010106,86);INSERT INTO stud_grade VALUES(0401010634,N钱昆,0401010106,78);INSERT INTO stud_grade VALUES(0401030213,N郑芳,0401010106,95);INSERT INTO stud_grade VALUES(0401010102,N袁飞,0401010106,95);INSERT INTO stud_grade VALUES(0401040123,N孔荣,0401010106,83);INSERT INTO stud_grade VALUES(0401050127,N张军,0401010106,84);USE studentGOINSERT INTO staffroom_info VALUES (0101,N计算机应用,N专业,N王二毛);INSERT INTO staffroom_info VALUES (0102,N计算机网络,N专业,N李四冲);INSERT INTO staffroom_info VALUES (0103,N计算机软件,N专业,N赵一生);INSERT INTO staffroom_info VALUES (0104,N计算机管理,N专业,N汪三洋);USE studentGOINSERT INTO specialty_code VALUES (040101,N计算机应用技术);INSERT INTO specialty_code VALUES (040102,N计算机网络技术);INSERT INTO specialty_code VALUES (040103,N计算机控制技术);INSERT INTO specialty_code VALUES (040104,N多媒体技术);INSERT INTO specialty_code VALUES (040105,N计算机软件技术);INSERT INTO specialty_code VALUES (040106,N计算机通信技术);INSERT INTO specialty_code VALUES (040107,N计算机管理技术);USE studentGOINSERT INTO lesson_info VALUES(0401010101,N计算机导论,N考查,30,1.5);INSERT INTO lesson_info VALUES(0401010102,NJava程序设计,N考试,60,3.5);INSERT INTO lesson_info VALUES(0401010103,N微型计算机原理,N考试,60,3.5);INSERT INTO lesson_info VALUES(0401010104,NIT市场营销,N考查,30,1.5);INSERT INTO lesson_info VALUES(0401010105,N网络互联设备与配置,N考查,60,2.0);INSERT INTO lesson_info VALUES(0401010106,N多媒体技术,N考查,60,3.0);USE studentGOINSERT INTO dept_code VALUES (01,N计算机工程系);INSERT INTO dept_code VALUES (02,N管理工程系);INSERT INTO dept_code VALUES (03,N机电工程系);INSERT INTO dept_code VALUES (04,N食品工程系);INSERT INTO dept_code VALUES (05,N轻化工程系);INSERT INTO dept_code VALUES (06,N通信工程系);INSERT INTO dept_code VALUES (07,N外语工程系);USE studentGOINSERT INTO classroom_info VALUES(120703,N微机组装与维护,N实训,N微机、投影仪,40);INSERT INTO classroom_info VALUES(120704,N计算机网络,N实验,N交换机、路由器等,40);INSERT INTO classroom_info VALUES(120705,N数据库,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120706,N软件设计,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120707,N多媒体,N计算机机房,N微机、投影仪,60);INSERT INTO classroom_info VALUES(120708,N,N普通,N白板、投影仪,120);3、 第五章教学案例/5.1* 查询全部课程的详细信息。T-SQL语句：*/USE studentGOSELECT * FROM lesson_info/5.2* 在学生基本信息表中查询所有女生的学号、姓名、出生日期的语句：*/USE studentGOSELECT stud_id 学号, name AS 姓名, 出生日期=birthdayFROM stud_infoWHERE gender=N女/5.3* 查询学生的学号、姓名、考试成绩的语句：*/USE studentGOSELECT stud_info.stud_id, stud_, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id=stud_grade.stud_id/5.4* 将学生的学号、姓名、性别的查询结果作为新建临时表的语句：*/USE studentGOSELECT stud_id, name, genderINTO new_stud_infoFROM stud_infoWHERE gender =N男/*5.5 查询性别为“女”的学生的姓名、电话、地址和邮编的语句：*/USE studentGOSELECT name, address, telcode, zipcodeFROM stud_infoWHERE gender=N女/*5.6 列出姓“郑”、姓名为两个汉字的学生学号、姓名，性别，入学成绩的语句：*/USE studentGOSELECT stud_id, name, birthday, gender, mark FROM stud_info WHERE name LIKE N郑_/* 5.7查询教师职称为“助教”，或为“讲师”，或为“副教授”的教师编号、姓名、职称及工资的语句：*/USE studentGOSELECT teacher_id, name, tech_title, salaryFROM teacher_infoWHERE tech_title IN (N助教, N讲师, N副教授)/*5.8 求“Java程序设计”课程平均成绩的语句：*/USE studentGOSELECT AVG (grade) FROM stud_grade WHERE course_id=0401010102/*5.9 查询所有男生学号、姓名和年龄，并按出生日期进行排列(升序)的语句：*/USE studentGOSELECT stud_id 学号, name 姓名, year(getdate()-year(birthday) 年龄, birthday 出生日期FROM stud_infoWHERE gender = N男ORDER BY birthday ASC/* 5.10统计计算机工程系各个专业的学生的平均入学成绩的语句：*/USE studentGOSELECT substring(stud_id,5,2) 专业编号, avg(mark) 平均入学成绩FROM stud_infoWHERE substring(stud_id,3,2) =01GROUP BY substring(stud_id,5,2)/* 5.11在教师信息表中，按职称分组统计“讲师”的平均年龄的语句：*/USE studentGOSELECT tech_title, avg(age)FROM teacher_infoGROUP BY tech_titleHAVING tech_title = N讲师/*5.12 对teacher_info中职称为“讲师”的工资，生成汇总行和明细行的语句：*/USE studentGOSELECT tech_title, salaryFROM teacher_infoWHERE tech_title = N讲师ORDER BY tech_titleCOMPUTE sum(salary)/* 5.13查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩 */USE studentGOSELECT stud_info.stud_id, stud_, stud_info.zipcode, stud_grade.gradeFROM stud_info, stud_gradeWHERE stud_info.stud_id = stud_grade.stud_id/*5.14 在FROM子句中定义内连接查询每门课程名称及其该门课的任课老师的姓名、编号 */USE studentGOSELECT teacher_info.teacher_id, teacher_, lesson_info.course_nameFROM lesson_info INNER JOIN teacher_info ON (lesson_info.course_id = teacher_info.course_id)/* 5.15 在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的学生基本信息和成绩分数。 */USE studentGOSELECT *FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id/*5.16 stud_info和stud_grade采用自然连接以限制结果集的冗余列数据 */USE studentGOSELECT stud_grade.*,stud_info.telcode,stud_info.markFROM stud_grade INNER JOIN stud_info ON stud_grade.stud_id=stud_info.stud_idORDER BY stud_grade.stud_id/* 5.17学生成绩表stud_grade左外连接学生信息表stud_info */USE studentGOINSERT INTO stud_info -为了说明方便，先在学生信息表中插入一条新记录VALUES (0401010704,N王一明,03/03/1986,N男,N甘肃省兰州市,590000,573)SELECT stud_info.stud_id,stud_,stud_grade.course_idFROM stud_info LEFT OUTER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_,stud_grade.course_id/* 5.18学生信息表stud_info右外连接学生成绩表stud_grade */USE studentGOSELECT stud_info.stud_id,stud_,stud_grade.course_idFROM stud_grade RIGHT OUTER JOIN stud_info ON stud_info.stud_id=stud_grade.stud_idORDER BY stud_info.stud_id,stud_,stud_grade.course_id/* 5.19教师信息表teacher_info全外连接课程信息表lesson_info */USE studentGOSELECT lesson_info.course_name, teacher_, teacher_info.teacher_idFROM lesson_info FULL OUTER JOIN teacher_info ON lesson_info.course_id = teacher_info.course_idORDER BY lesson_info.course_name, teacher_, teacher_info.teacher_id/* 5.20查询学生成绩表stud_grade中与学号为“0401020103”的学生所学的课程相同的学生的学号、姓名、课程号、成绩 */USE studentGOSELECT a.stud_id, , a.course_id, a.gradeFROM stud_grade a, stud_grade bWHERE a.course_id=b.course_id AND a.stud_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_info WHERE stud_id =0401030101)/* 5.22在学生成绩表中查询课程类型为“考试”的学生学号、姓名、成绩 */USE studentGOSELECT stud_id,name,gradeFROM stud_gradeWHERE course_id IN (SELECT course_id FROM lesson_info WHERE course_type =N考试)/* 5.23查询课程号为“0401010106”的多媒体技术这门课的成绩在80至89分的学生的学号、姓名 */USE studentGOSELECT stud_id,nameFROM stud_infoWHERE EXISTS (SELECT * FROM stud_grade WHERE 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, birthday, 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/* 基于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 建立计算机系(学号第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) */USE StudentGOCREATE VIEW schedule_viewASSELECT lesson.course_name, , classroom.room_name, schedule.course_week, schedule.course_time, schedule.course_idFROM classroom_info classroom, teacher_info