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;
select count(birth1) from student;
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;
select avg(grade) as avg_all_course from grade where courseid = 'Dp010001';
select courseid,avg(grade)
from grade
group by courseid;
select courseid,avg(grade)
from grade
group by courseid
having avg(grade)>80;
select courseid,avg(grade)
from grade
where grade >= 60
group by courseid
having avg(grade)>80
order by avg(grade) desc;
select grade,studentid from grade where grade>=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)
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';
select distinct studentid
from student
where classid not in ('cs010901','cs010902');
select studentid,sex,classID
from student
where (classID = 'cs010901' or classID = 'cs010902' ) and sex = '女';
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 "/";
select courseid,studentid,grade
from grade
where courseid = 'Dp010001'
order by grade desc limit 3;
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
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);
select courseid as '课程号',count(studentid)
from grade
group by courseid
order by count(studentid) asc
select *from course
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
select count(studentid) as 班级人数
from student
group by classid
having count(studentid)>5
order by count(studentid) desc
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
select classid,studentid
from student
where classid in ('Cs010901','Cs010902')
select count(distinct studentid) as student_num
from grade;
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;
Processed:
0.010, SQL:
8
