• --数据查询-多表连接查询



    --查询所有同学的成绩信息,要求输出学生姓名和成绩
    use students_new
    go
    select s_name,score from t_score,t_student
    (文件名) (文件)
    where t_student.s_number=t_score.s_number
    (文件名.文件)
    (两张表的连接条件)
    --查询所有课程的成绩信息,要求输出课程名称和成绩
    select c_name,score from t_course,t_score
    where t_course.c_number=t_score.c_number
    --查询“高等数学”课程的平均分
    select avg(score) as 平均分 from t_course,t_score
    where c_name='高等数学' and t_course.c_number=t_score.c_number
    --查询所有成绩信息,要求输出学生姓名、课程名称成绩
    select s_name,c_name,score from t_course,t_score,t_student
    where t_course.c_number=t_score.c_number and t_score.s_number=t_student.s_number
    --取别名法
    select s.s_number,c.c_name,s.score from t_course as c,t_score as s,t_student as ts
    where c.c_number=s.c_number and s.s_number=ts.s_number
    --查询"张小航"同学"高等数学"课程的成绩
    select s.score from t_score as s,t_student as stu,t_course as c where s_name='张小航'and c_name='高等数学'
    and c.c_number=s.c_number and s.s_number=stu.s_number
    --查询每位同学的最高分,要求输出学生姓名和最高分
    select MAX(score),s_name from t_score as s,t_student as stu
    where s.s_number=stu.s_number group by s_name
    --查询选修了"高等数学"课程的学生姓名和性别
    select s_name,sex from t_score as s,t_student as stu,t_course as c where c_name='高等数学'
    and c.c_number=s.c_number and s.s_number=stu.s_number
    --查询"高等数学"课程不及格的学生信息,并按成绩升序排列输出
    select score,s_number,s.sex,s.birthday,s.polity from t_score as s,t_course as c,s_student as stu
    where c_name='高等数学'and score<='60' and c.c_number=s.c_number and s.s_number=stu.s_number order by score

  • 相关阅读:
    alpha冲刺9
    alpha冲刺8
    alpha冲刺7
    alpha冲6
    随堂小测-同学录
    alpha冲刺5
    任务3
    任务2
    任务1
    网站用户行为分析
  • 原文地址:https://www.cnblogs.com/lijingxiang/p/11314062.html
Copyright © 2020-2023  润新知