时间:2021年4月13日11:12:17
以下写查询语句
查询男生的姓名和编号
select SNAME,SNO from student
where SEX='男';
查询姓张的同学信息
select*from student
where SNAME like '张%';
查询年龄19,21,22的学生信息
select * from student
where AGE = 19 or AGE = 21 or AGE = 22;
select *
from student
where age in (19,21,22);
查询年龄19-22的学生信息
select * from student
where AGE >= 19 and AGE <= 22;
select * from student
where AGE between 19 and 22;
查询年龄19的学生信息性别为男
select * from student
where AGE =20 and SEX = '男' ;
查询每个人选修的课程
select cname
from course
where cpno is null;
查询每个同学的姓名和出生年份
select sname, 2021-age
from student;
查询学生的人数
count查询行
select count(*)
from student;
在这一行,每行都有值
查询有先行课的课程数量
course查询的列数,如果是列查询该列有多少非值
查询学生的年龄有哪些
distinct 删除重复值
得到查询结果,修饰查询当中重复行
查询不同年龄的学生人数
注: 在group,只能那些分组的列只能单独出现在group后面,但可以出现在函数参数中
查询每个选课的学生的平均分
select sno,avg(grade)
from sc
group by sno;
查询选课表中没门课的选课人数
通过cno进行分组
select cno,count(*)
from sc
group by cno;
查询每个同学的额最高分和最低分
select max(grade),min(grade)
from sc
group by grade;
查询不同性别不同年龄的学生人数
先根据性别在根据年龄
select sex,age,count(*)
from student
group by sex,age;
查询平均分数大于80的学生学号
select sno,avg(grade)
from sc
group by sno
having avg(grade)>85;
查询选修人数大于2的课程编号
select cno,count(*)
from sc
group by cno
having count(*)>2;
选课表根据成绩的降序排序
select *
from sc
order by grade desc;
根据每个同学的平均成绩降序排序
先查询出每个同学的平均成绩 在根据成绩降序排序
select sno,avg(grade)
from sc
group by sno
order by avg (grade);
select sno,avg(grade) savg
from sc
group by sno
order by savg desc;
根据性别的升序年龄的降序进行排序
select *
from student
order by sex, age desc;