2025年数据库应用技术案例

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=''USE 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) ='01'GROUP 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_grade.name, 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_info.name, 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 --为了说明方便，先在学生信息表中插入一条新记录VA...