mysql基本练习（一）

不念不忘少年蓝@ 2023-06-25 06:28 56阅读 0赞

#（1）查询所有班级的班级编号，班级名称和所属学院
    select classno,classname,institute
    from class
    #（2）查询所有学院的名称
    select institute
    from class 
    #（3）查询消除重复元组
    select distinct institute
    from class
    #（4）查询所有列
    select classno,classname,grade,institute
    from class
    #（5）查询所有班级的全部信息
    select *
    from class
    #（6）给属性列取别名
    select institute 所属学院,classno 班级编号,classname 班级名称
    from class
    #（7）使用AS关键字来取别名
    select institute as 所属学院,classno as 所属编号,classname as 班级名称
    from class
    #（8）查询每门课程的课程号，课程名以及周课时（周课时为课时数除以16），并将课程名中大写字母改为小写字母输出
    select courseno 课程号,lower(coursename)课程名,coursehour/16 as 周课时
    from course
    #选择运算
    #WHERE子句中可以实现关系代数中的选择运算，用于查询满足选择条件的元组，这是查询中设计最多的一类查询
    #（9）查询2015年的班级编号，班级名称和所属学院
    select classno,classname,institute
    from class
    where grade=2015
    #范围查询
    #（10）在学生Student表中查询年龄大于或者等于19的同学学号，姓名和出生日期
    select studentno,studentname,birthday
    from student
    where year(now())-year(birthday)>=19
    #（11）在选课score表中查询成绩不在80~90分之间的同学学号，课程号和相应的成绩
    select studentno,courseno,score
    from score
    where score not between 80 and 90
    #集合查询
    IN可以用于查询属性值在某个集合内的元组，NOT IN 可用于查询属性值不在某个集合内的元组
    IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合
    #（12）在选课score表中查询选修了001,005或者003课程的同学学号，课程号和相应的成绩
    select studentno,courseno,score
    from score
    where courseno in('001','005','003')
    #（13）在学生student表中查询籍贯既不是南昌也不是上海的同学姓名，籍贯和所属班级编号
    select studentname,native,classno
    from student
    where native not in('南昌','上海')
    #空值查询
    SQL支持空值查询，空值表示未知或者不确定的值，空值表示null，is null可用于查询属性值为空值，
    is not null 可用于查询属性值不为空值，这里的IS不能用'='替代
    #（14）在课程Course表中查询先修课程为空值的课程信息
    select *
    from course
    where priorcourse is null
    #（15）在课程course表中查询有先修课程的课程信息
    select *
    from course
    where priorcourse is not null
    #（16）在班级class表中查询班级名称中含有会计的班级信息
    select *
    from class
    where classname like '%会计%'
    #（17）在学生student 表中查询所有姓王且全名为3个汉字的同学学号和姓名
    select studentno,studentname
    from student
    where studentName like '王__'
    #（18）在学生student表中查询名字不含有"福"的同学学号和姓名
    select studentno,studentname
    from student
    where studentname not like '%福%'
    #（19）在学生student表中查询蒙古族的
    select studentno,studentname
    from student
    where nation like '蒙古族'
    #（19）在学生student表中查询蒙古族的
    select studentno,studentname
    from student
    where nation = '蒙古族'
    #（20）在班级class表中查询班级名称中含有'16#_'符号的班级名称，如果将#字符作为换码字符，则该查询可以改写为
    select classname
    from class
    where classname like '%16#_%' escape '#'
    #逻辑运算
    #（21）在选课score表中查询选修001,005,或者003课程的同学学号，课程号和相应成绩
    select studentno,courseNo,score
    from score
    where courseno='001' or courseno='005' or courseno='003'
    #（22）在Student表中查询1998年出生且民族为‘汉族’的同学学号，姓名和出生日期
    select studentno,studentname,birthday
    from student
    where year(birthday)=1998 and nation='汉族'
    #（23）在学生student表中查询籍贯既不是南昌也不是上海的同学姓名，籍贯和所属班级编号
    select studentname,native,classno
    from student
    where native!='上海' and native!='南昌'
    #（24）在选课score表中查询成绩在80~90分之间的同学学号，课程号和相应的成绩
    select studentno,courseno,score
    from score
    where score>=80 and score<=90
    #（25）在选课score表中查询成绩不在80~90分之间的同学学号，课程号和相应的成绩
    select studentno,courseno,score
    from score
    where score<80 or score>90
    #（26）在学生student表中查询籍贯既不是南昌也不是上海的同学姓名，籍贯和所属班级编号，并且按照籍贯的降序排序输出
    select studentname,native,classno
    from student
    where native!='南昌' and native!='上海'
    order by native DESC
    #（27）在学生student表中查询‘女’学生的学号，姓名，所属班级编号和出生日期，并且按照班级编号的升序，出生日期的月份降序排序输出
    select studentno,studentname,classno,birthday
    from student
    where sex='女'
    order by classNo,month(birthday) DESC
    #（28）查询1999年出生的‘女’同学的基本信息
    select studentno,studentname,birthday
    from (select * from student where sex='女') as a
    where year(birthday)=1999
    #该查询等价于下面的查询:
    select studentno,studentname,birthday
    from student
    where year(birthday)=1999 and sex='女'
    #（29）聚合查询
    #查询学生总人数
    select count(*)
    from score
    #带列名的输出结果
    select count(*) 学生人数
    from score
    #（30）查询所有选课的学生的人数
    （有重复元组）
    select count(studentno) 学生人数
    from score
    （无重复元组）
    select count(distinct studentno) 学生人数
    from score
    #（31）查询学号为1500003同学所选课程的平均分
    select avg(score) 平均分
    from score
    where studentno ='1500003'
    #分组聚合
    #查询每个同学的选课门数，平均分和最高分
    #（32）该查询结果按学号studentno进行分组，将具有相同的studentno值的元组作为一组，然后对每组进行相应的计数，求平均值，和求最大值
    sselect studentno,count(*)门数,avg(score)平均分,max(score)最高分
    from score
    group by studentno
    #（33）查询平均分在80分以上的每个同学的选课门数，平均分和最高分
    select studentno 学号,count(*) 选课门数,avg(score) 平均分,max(score) 最高分
    from score
    group by studentNo
    having avg(score)>=80 
    #连接查询
    #等值与非等值连接
    #等值连接
    #（34）查找会计学院全体同学的学号，姓名，籍贯，班级编号和所在班级名称
    select studentno,studentname,native,student.classno,classname
    from student,class
    where student.classNo=class.classno and institute='会计学院'
    #（35）查找会计学院全体同学的学号，姓名，籍贯，班级编号和所在班级名称
    select studentno,studentname,native,a.classno,classname
    from student a,class b
    where a.classNo=b.classno and institute='会计学院'
    #（36）查找会计学院全体同学的学号，姓名，籍贯，班级编号和所在班级名称
    select studentno,studentname,native,a.classno,classname
    from student as a,class as b
    where a.classNo=b.classno and institute='会计学院'
    #（37）查找选修了课程名称为‘计算机原理’的同学学号，姓名
    select a.studentno,studentname
    from student a,course b,score c
    where b.courseno=c.courseno and c.studentno=a.studentno and b.coursename='计算机原理'
    #（38）查找同时选修了编号为001和002课程的同学学号，姓名，课程号和相应成绩，并且按学号排序输出
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    from student a ,score b,(select * from score where courseno='002')c 
    where b.courseno='001' and a.studentno=b.studentno and a.studentno=c.studentno
    order by a.studentno
    （38）查找同时选修了编号为001和002课程的同学学号，姓名，课程号和相应成绩，并且按学号排序输出
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    from student a ,(select * from score where courseno='001')b,(select * from score where courseno='002')c 
    where a.studentno=b.studentno and a.studentno=c.studentno
    order by a.studentno
    （39）查询获得的总学分（注：只有成绩合格才能获得该课程的学分）大于或者等于28的同学的学号，姓名和总学分，并且学号排序输出
    #由于本例子中的输出结果中需要同时包含学号和姓名，因此group by子句也需要a.studentno,studentName进行聚合。
    select a.studentno,studentname,sum(creditHour)
    from student a ,course b,score c
    where a.studentno=c.studentno and c.courseno=b.courseno and score>=60
    group by a.studentno,studentName
    HAVING sum(creditHour)>=28
    order by a.studentno
    （40）自然连接
    select studentno,a.courseno,score,coursename,credithour,coursehour,priorcourse
    from score a,course b
    where a.courseno=b.courseno
    （41）自表连接
    #查找同时选修了编号为001和002课程的同学学号，姓名，课程号和相应成绩，并且按学号排序输出
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    from student a,score b,score c
    where b.courseno='001' and c.courseno='002'
    and b.studentno=c.studentNo and a.studentno=b.studentNo
    order by a.studentno
    （42）在学生表student中查找与“李宏冰”同学在同一个班中的同学姓名，班级编号和出生日期
    select a.studentname,a.classno,a.birthday
    from student a ,student b
    where b.studentName='李宏冰' and a.classNo=b.classNo
    （43）外连接
    #在一般的外连接中，只有满足连接条件的元组才能被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的
    select classname,institute,studentno,studentname
    from class a,student b
    where a.classNo=b.classNo and grade=2015
    order by className
    （44）左外连接
    select classname,institute,studentno,studentname
    from class a LEFT OUTER JOIN student b on a.classno=b.classNo
    where grade=2015
    ORDER by classname,studentno
    （44）右外连接
    select classname,institute,studentno,studentname
    from class a RIGHT OUTER JOIN student b on a.classno=b.classNo
    where grade=2015
    ORDER by classname,studentno
    （45）全外连接
    select classname,institute,studentno,studentname
    from class a FULL OUTER JOIN student b on a.classno=b.classNo
    where grade=2015
    ORDER by classname,studentno
    #（46）嵌套子查询
    #使用IN子查询
    select studentname
    from student a 
    where a.studentno in (select score.studentno from score)
    #（47）查询选修过课程名中包含“系统”的课程的同学学号，姓名和班级编号
    select studentno,studentname,classno
    from student
    where studentno in (select studentno from score where courseno in 
    (select courseNo from course where coursename like '%系统%'))
    #（48）查找同时选修过计算机原理和高等数学两门课程的同学学号，姓名以及该同学所选修的所有课程的
    课程名和相应成绩，按照学号（升序），成绩（降序）排序输出
    select a.studentno,studentname,coursename,score
    from student a,course b,score c
    where a.studentno=c.studentNo and b.courseNo=c.courseNo 
    and a.studentNo in (select studentNo from score where courseNo in (select courseno from course where coursename='计算机原理' ))
    and a.studentNo in (select studentNo from score where courseNo in (select courseno from course where coursename='高等数学' ))
    ORDER BY a.studentNo,score desc
    #（50）查找同时选修过计算机原理和高等数学两门课程的同学学号，姓名以及该同学所选修的所有课程的
    课程名和相应成绩，按照学号（升序），成绩（降序）排序输出
    select a.studentno,studentname,coursename,score
    from student a,course b,score c
    where a.studentno=c.studentNo and b.courseNo=c.courseNo 
    and a.studentno in (select studentno from score x,course y where x.courseno=y.courseno and courseName='计算机原理')
    and a.studentno in (select studentno from score x,course y where x.courseno=y.courseno and coursename='高等数学')
    and (coursename='高等数学' or coursename='计算机原理')
    order by a.studentNo,score desc
    #（51）使用比较运算符的子查询
    select studentno,courseno,score
    from score
    where score>ALL(select score from score where courseno='002')
    #（52）查询成绩最高分的学生的学号，课程号和相应成绩
    select studentno,courseno,score
    from score
    where score=(select max(score) from score)
    #important !!! WHERE子句中不可以直接使用聚合函数
    #（53）查询年龄小于“计算机科学和技术16-01班”某个同学年龄的所有同学的学号，姓名，年龄
    select studentno,studentname,year(now())-year(birthday) as age
    from student
    where birthday>any(select birthday from student a,class b where classname='计算机科学与技术16-01班' and a.classno=b.classno)
    #使用存在量词EXISIS的子查询
    #（54）查询选修了‘计算机原理’课程的同学姓名以及所在的班级编号
    select studentname 学生姓名,classno 班级编号
    from student x
    where EXISTS(select studentno from score where courseno in (select courseno from course where coursename='计算机原理'))