MySQL单表查询命令代码练习

create database Test;
# 向已有数据中插入数值
update student set HomeAddr = "武汉市洪山区" where StudentID = "St0111040001";
select HomeAddr from student where StudentID = "St0111040001";

#查看有多少个同学选过课
select count(distinct studentid) as student_num from grade;

#统计学生人数
select count(*) from student; #*时，null也参与计算行总数
select count(birth1) from student;  #具体列时，该列null值不参与计算行总数

#查看所有同学所有课程的平均成绩
select avg(grade) as avg_all_course from grade;

#查看某一门课程的平均成绩
select avg(grade) as avg_course from grade;

SELECT courseID, COUNT(*) FROM   grade GROUP BY courseID;

# 输出dp0000001的平均成绩
select avg(grade) as avg_all_course from grade where courseid = 'Dp010001';


#输出每门课程的平均成绩
select courseid,avg(grade)
from grade
group by courseid;

# 输出平均成绩大于80的课程
select courseid,avg(grade)
from grade
group by courseid
having avg(grade)>80;

# 输出平均成绩大于80的课程(每门课60分以下的同学不参与平均成绩的计算)
select courseid,avg(grade)
from grade
where grade >= 60
group by courseid
having avg(grade)>80
order by avg(grade) desc;




#查询出成绩大于等于90的同学学号
select grade,studentid from grade where grade>=90;

#查询出成绩大于60小于90的同学学号
select grade,studentid from grade where grade between 60 and 90;

select distinct studentid from grade where grade>60 and grade<90;





# ``````````````````````````````````````````````````````````````````````````````````````
#方法一:
select studentid 
from student
where studentid not in (select studentid
from grade
where courseid in ('Dp010001','Dp010002'))

#方法二
select studentid
from student a
where not exists(select studentid
from grade
where courseid in ('Dp010001','Dp010002') and studentid=a.studentid)



#查询选修了Dp010001或者Dp010003课程的同学学号
select a.studentid,a.courseid,b.courseid
from grade a,grade b
where a.studentid = b.studentid and a.courseid = 'Dp010001' and b.courseid = 'Dp010003';


#查询没有选修Dp010001或者Dp010002课程的同学学号
select distinct studentid
from student
where classid not in ('cs010901','cs010902');


#查找Cs010901班和Cs010902班的女同学
select studentid,sex,classID
from student 
where (classID = 'cs010901' or classID = 'cs010902' ) and sex = '女';
-- and优先级大于or，可以用括号调整


#查找没有班长的班级号
select  classid,monitor
from class
where monitor is null;

# 查找有班长的班级号和班级人数
select  classid,studentnum,monitor
from class
where monitor is not null;

#查找大连的同学学号和姓名
select studentid,studentname,homeaddr
from student
where HomeAddr like '大连市%';

#查找姓名第二个字为雨的同学
select studentid,studentname
from student
where studentname like '_雨%';

#查找书名中以“数据库技术与应用_”开头的书
select courseid,bookname
from course
where bookname like '数据库技术与应用\_%';		#当遇到_划线的时候，添加一个本来拥有的实际意义，加一个"\"
#用其他的字符来替代转义字符
where bookname like '数据库技术与应用\_%' escape "/";


#查询选修了Dp010001且成绩为前三名的同学的学号及对应的成绩。
select courseid,studentid,grade
from grade
where courseid = 'Dp010001'
order by grade desc limit 3;

#查询选修了Dp010001且成绩为第三名的同学的学号及对应的成绩。
select courseid,studentid,grade
from grade
where courseid = 'Dp010001'
order by grade desc limit 2,1;


#查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select studentid,classid,year(birth1),2020-year(birth1)
from student
order by classid,2020-year(birth1) desc 

# 查询总共有多少个班级？查询有班长的班级有多少个？通过student表查询每个班级各有多少个人？
select count(classid) as '班级数',count(monitor) as '有班长的班级个数'
from class;
where monitor is not null;
select classid as '班级',count(studentid) as '班级人数'
from student
group by classid



#查询每个班中各年份出生的人数是多少？
select classid as '班级号',year(birth1) as '出生年月',count(*) as '人数'
from student
group by classid,year(birth1)
order by classid,year(birth1);


#统计每门课程的学生选修人数（超过2人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select courseid as '课程号',count(studentid)
from grade
group by courseid
order by count(studentid) asc



-- ##################################################################################--------
#实验题
#查询所有课程的详细信息
select *from course


#查询所有学生的学号，姓名，出生年月(格式YYYY-MM)，显示时使用别名出生年月
select studentid,studentname,DATE_FORMAT(birth1,'%Y-%c') as '出生年月'
from student;


#求姓王的同学和姓张的同学学生号，学生名，班级号
select studentid,studentname,classid
from student
where studentname like '王%' or studentname like '张%' 


#查询每门课程每个学年的平均分，输出课程号，学年，以及平均分数
select courseid,avg(grade)
from grade
group by courseid


#查询每个学生选择的课程数并显示出这几门课的课程号，以及他的最高分，最低分
select max(grade) as '最高分',min(grade) as '最低分',courseid as '课程号',count(courseid) as '人数'
from grade
group by courseid


#通过student表计算每个班有多少人，找出大于5人的班级号和其班级人数，并按照班级人数降序排序显示。
select count(studentid) as 班级人数
from student
group by classid
having count(studentid)>5
order by count(studentid) desc


#筛选出每个同学大于70分的成绩，输出有2门课超过70分的学生号
select studentid
from grade
group by studentid
having count(grade>70) >= 2 

select studentid,courseid,grade 
from grade 
where grade>=70 
group by studentid 
having count(courseid)>=2


#.查询Cs010901和Cs010902 班同学的学号
select classid,studentid
from student
where classid  in ('Cs010901','Cs010902')

#查询有多少个同学选过课程（在grade表中出现的学号即认为选过课)
select count(distinct studentid) as student_num 
from grade;


#建立一个表addr，有两列数据，第一列为proaddr（存放市名),
#第二列为num（存放每个省学生人数)，通过student表查询每个市有多少个人，
#没有填写地址的不进行计算，并将查询结果填写到addr当中。
create table addr(
proaddr varchar(80) comment '省市名',
num int comment '人数'
);
drop table addr;

insert into addr(num,proaddr)
select count(HomeAddr),substring(HomeAddr,1,position('市' in HomeAddr)) as city 
from student
where HomeAddr regexp '(佛山市)|(武汉市)|(成都市)|(广州市)|(鞍山市)|(宜宾市)|(大连市)|(汕头市)|(上海市)|(深圳市)'
group by city

select * from addr;