mysql練習題

?導入hellodb.sql生成數據庫
?(1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡
MariaDB [hellodb]> select Name,Age from students where Age>25 and Gender=’M’;
?(2) 以ClassID為分組依據，顯示每組的平均年齡
MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID;
?(3) 顯示第2題中平均年齡大于30的分組及平均年齡
MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID having avg_age>30;
?(4) 顯示以L開頭的名字的同學的信息
MariaDB [hellodb]> select * from students where Name like ‘L%’;
MariaDB [hellodb]> select * from students where Name rlike ‘^L’;
?(5) 顯示TeacherID非空的同學的相關信息
MariaDB [hellodb]> select * from students where TeacherID is NOT NULL;
?(6) 以年齡排序后，顯示年齡最大的前10位同學的信息
MariaDB [hellodb]> select * from students order by age desc limit 10;
?(7) 查詢年齡大于等于20歲，小于等于25歲的同學的信息
MariaDB [hellodb]> select * from students where age>=20 and age<=25;
MariaDB [hellodb]> select * from students where age between 20 and 25;

?導入hellodb.sql，以下操作在students表上執行
?1、以ClassID分組，顯示每班的同學的人數
MariaDB [hellodb]> select classid,count(name) from students group by classid;
?2、以Gender分組，顯示其年齡之和
MariaDB [hellodb]> select gender,sum(age) from students group by gender;
?3、以ClassID分組，顯示其平均年齡大于25的班級
select classid from students group by classid having avg(age) > 25;
?4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和
MariaDB [hellodb]> select gender,sum(age) from (select age,gender from students where age>25) as t group by gender;
?5、顯示前5位同學的姓名、課程及成績
MariaDB [hellodb]> select name,course,score from (select * from students limit 5) as t,courses,scores where t.stuid=scores.stuid and scores.courseid=courses.courseid;
?6、顯示其成績高于80的同學的名稱及課程；
select name,course,score from students,(select * from scores where score > 80) as s,courses where students.stuid=s.stuid and s.courseid=courses.courseid;
?7、求前8位同學每位同學自己兩門課的平均成績，并按降序排列
select name,a from (select * from students limit 8) as s,(select stuid,avg(score) as a from scores group by stuid) as c where s.stuid=c.stuid order by a desc;
?8、顯示每門課程課程名稱及學習了這門課的同學的個數
select course,count(name) from (select name,course from students,courses,scores where students.stuid=scores.stuid and scores.courseid=courses.courseid) as a group by course;
select courses.course,count(stuid) from scores left join courses on scores.courseid=courses.courseid group by scores.courseid;
?9、顯示其年齡大于平均年齡的同學的名字
MariaDB [hellodb]> select name from students where age > (select avg(age) from students);
?10、顯示其學習的課程為第1、2，4或第7門課的同學的名字
MariaDB [hellodb]> select name from students,(select distinct stuid from (select * from scores where courseid in (1,2,4,7)) as a) as b where b.stuid=students.stuid;
select students.name from students,(select distinct stuid from scores where courseid in (1,2,4,7))as s where s.stuid=students.stuid;
?11、顯示其成員數最少為3個的班級的同學中年齡大于同班同學平均年齡的同學
MariaDB [hellodb]> select student.name,student.age,student.classid,second.avg_age from (select students.name as name ,students.age as age,students.classid as classid from students left join (select count(name) as num,classid as classid from students group by classid having num>=3) as first on first.classid=students.classid) as student,(select avg(age) as avg_age,classid as classid from students group by classid) as second where student.age>second.avg_age and student.classid=second.classid;
?12、統計各班級中年齡大于全校同學平均年齡的同學
select name,age,classid from students where age > (select avg(age) as a from students);