mysql-select查询操作

drop database if exists jxgl;create database jxgl;use jxgl;drop table if exists student;create table student (sno char(20) primary key, sname varchar(20), sage int(20), ssex char(20), sdept char(20));insert into student value ('2005001','钱横',18,'男','Cs');insert into student value ('2005002','王林',19,'女','Cs');insert into student value ('2005003','李民',20,'男','Is');insert into student value ('2005004','赵欣然',16,'女','Ma');insert into student value ('2005005','陈家国',20,'男','Ca');insert into student value ('2005006','陈以为',21,'男','Ma');insert into student value ('2005007','陈丙',22,'女','Cs');insert into student value ('2005008','陈丁',22,'男','Da');insert into student value ('2005009','陈式',18,'女','Ma');insert into student value ('2005010','陈看',19,'女','Df');insert into student value ('2005020','陈助',25,'女','Me');drop table if exists course;create table course (cno int(10),cname varchar(20),cpno int(10),ccredit int(10),primary key(cno));insert into course value (1,'数据库系统 ',5,4);insert into course (cno,cname,ccredit) value (2,'数学分析',2);insert into course value (3,'信息系统导论',1,3);insert into course value (4,'操作系统原理',6,3);insert into course value (5,'数据结构',7,4);insert into course value (6,'数据处理基础',Null,4);insert into course value (7,'C语言',6,3);insert into course value (8,'大学语文',1,2);insert into course value (9,'高等数学',2,2);insert into course value (10,'程序设计',3,3);insert into course value (11,'信号系统',4,4);insert into course value (12,'通信原理',5,4);insert into course value (13,'计算机组成原理',6,2);insert into course value (14,'手机开发',7,1);drop table if exists sc;create table sc(sno char(20),cno int(10),grade float(20),primary key(sno,cno),            foreign key(sno) references student(sno),            foreign key(cno) references course(cno));insert into sc value ('2005001',1,87);insert into sc value ('2005001',2,67);insert into sc value ('2005001',3,90);insert into sc value ('2005002',2,70);insert into sc value ('2005003',3,88);insert into sc value ('2005004',4,70);insert into sc value ('2005005',1,60);insert into sc value ('2005006',3,70);insert into sc value ('2005007',4,90);insert into sc value ('2005007',5,80);insert into sc value ('2005008',5,80);insert into sc value ('2005009',5,80);insert into sc value ('2005009',14,80);insert into sc value ('2005010',5,80);insert into sc value ('2005020',14,80);insert into sc value ('2005020',8,80);insert into sc value ('2005020',9,80);insert into sc value ('2005020',5,80);insert into sc value ('2005020',7,80);insert into sc value ('2005020',12,80);

student数据表：                                                    course数据表：

 

sc数据表：

 

 

（1）、统计有学生选修的课程门数；

         selectcount(distinct cno) as course_sum from sc;

        

 

（2）、求选修四号课程的学生的平均年龄；

         selectavg(sage) as avg_name from student, sc where student.sno = sc.sno and sc.cno ='4';

        

 

（3）、求学分为3的每门课程的学生平均成绩

         selectavg(sc.grade) as avg_grade from (select cno from course where ccredit = '3') ass join sc on s.cno = sc.cno group by sc.cno;

        

 

（4）、统计每门课程的学生选修人数，要求超过3人的课程才统计，查询结果按人数的降序排列，如果相同就按课程号升序排列。

         select cno,count(distinct sno) from sc group by cno having count(distinct sno) > 3 orderby count(distinct sno) desc, cno asc;

        

 

（5）、检索学号比王林大年龄比他小的学生姓名。

         selects1.sname from student as s1,student as s2 where s1.sno > s2.sno and s1.sage< s2.sage and s2.sname = '王林';

        

 

（6）、检索姓名以王字开头的所有学生的姓名和年龄。

         select sname,sage from student where sname like '王%';

        

 

（7）、在sc表中检索成绩为空值的学生的学号和课程号；

         select sno,sc.cno from sc where grade is null;

        

 

（8）、求年龄大于女生平均年龄的男生的姓名和年龄

         select sname,sage from student where ssex = '男' and sage > (select avg(sage) from student where ssex = '女');

        

 

（9）、求年龄大于所有女生年龄的男生的姓名和年龄；

         select sname,sage from student where ssex = '男' and sage > all(select sage from student where ssex = '女');

        

 

（10）、检索选修四门以上课程的学生总成绩（不统计不及格课程），并要求按总成绩的降序排列；

         selectsum(grade) from sc group by sno having count(cno) > 4 order by sum(grade)desc;