数据库笔记5

#求每个班级中大于等于30岁的各年龄段对应的人数

#且只输出符合条件人数大于等于3个的班级号，年龄和人数

select classid,2021-year(birth1) as age,count(*)

from student

WHERE 2021-year(birth1)>=30

group by classid,age

having count(*)>=3

where ---行筛选

1.条件符

从grade表中查询成绩大于等于60小于等于80的详细成绩信息

select *

from grade

where grade BETWEEN 60 and 80;

从grade表中查询成绩大于60小于80的详细成绩信息

select *

from grade

where grade>60 and grade<80;

从grade表中查询成绩小于等于60或者大于等于80的详细成绩信息

select *

from grade

where grade<=60 or grade>=80

查找Cs010901班和Cs010902班的女同学

select *

from student

where (classid ="Cs010901" or classid ="Cs010902") and sex="女"

select *

from student

where classid in("Cs010901" ,"Cs010902") and sex="女"

查找不是Cs010901班和Cs010902班的同学

select *

from student

where classid not in ("Cs010901" ,"Cs010902")

2.模糊查询

输出姓王的同学的相关信息

select *

from student

where studentname like "王%" # = 是精准查询

#查找参考书中以“数据库技术与应用_”开头的课程相关信息

select *

from course

where bookname like "数据库技术与应用\_%"

3.

#查找”Dp010001“号课程前三名的成绩信息

#查找”Dp010001“号课程的所有成绩信息查找出来

select *

from grade

where courseid="Dp010001"

order by grade desc

limit 0,3

#排序

order by 列名 [asc|desc]

#截取

limit m,n m->从第几行开始截取 n->一共截取多少行

执行SQL语句，查询grade表中所有记录，

先按照课程号进行升序排序，

每个课程内部按照分数进行降序排序

select *

from grade

order by courseid asc,grade desc

多表：

查询每条成绩对应的学号，学生姓名，课程号，成绩

select student.studentid,studentname,courseid,grade

from student,grade

where student.studentid=grade.studentid