如图表创建数据库。
create table class( cid int auto_increment primary key, caption char(20) )engine=innodb default charset=utf8; create table student( sid int auto_increment primary key, sname char(10), gender char(10), class_id int not null, CONSTRAINT fk_cla_cid FOREIGN key (class_id) REFERENCES class(cid) )engine=innodb default charset=utf8; select * from student,class where student.class_id = class.cid; create table teacher( tid int auto_increment primary key, tname char(20) )engine=innodb default charset=utf8; create table course( cid int auto_increment primary key, cname char(10), teach_id int not null, CONSTRAINT fk_tea_tid FOREIGN key (teach_id) REFERENCES teacher(tid) )engine=innodb default charset=utf8; select * from course,teacher where course.teach_id = teacher.tid; create table score( sid int auto_increment primary key, student_id int not null, course_id int not null, number int, unique uq_stu_course (student_id,course_id), CONSTRAINT fk_score_stu FOREIGN key (student_id) REFERENCES student(sid), CONSTRAINT fk_score_course FOREIGN key (course_id) REFERENCES course(cid) )engine=innodb default charset=utf8;
1.查询所有成绩大于60分的人；
select * from score where number >60; +-----+------------+-----------+--------+ | sid | student_id | course_id | number | +-----+------------+-----------+--------+ | 3 | 2 | 2 | 100 | +-----+------------+-----------+--------+
2.查询每个老师有几个学生；
mysql> select teach_id,count(cname) from course group by teach_id; +----------+--------------+ | teach_id | count(cname) | +----------+--------------+ | 1 | 2 | | 2 | 1 | +----------+--------------+
3.查询老师名字与课程的关联（连表）；
mysql> select * from course left join teacher on course.teach_id = teacher.tid; +-----+--------+----------+------+--------+ | cid | cname | teach_id | tid | tname | +-----+--------+----------+------+--------+ | 1 | 生物 | 1 | 1 | 波多 | | 2 | 体育 | 1 | 1 | 波多 | | 3 | 物理 | 2 | 2 | 苍空 | +-----+--------+----------+------+--------+
4.查询男女生人数；
mysql> select gender,count(sid) from student group by gender; +--------+------------+ | gender | count(sid) | +--------+------------+ | 女 | 2 | | 男 | 1 | +--------+------------+
5.查看平均成绩大于50的人的信息；
mysql> select grade.student_id,student.sname,grade.av from (select student_id,avg(number)as av from score group by student_id having avg(number) > 50) as grade -> left join student on grade.student_id = student.sid; +------------+--------+----------+ | student_id | sname | av | +------------+--------+----------+ | 1 | 钢弹 | 59.5000 | | 2 | 铁锤 | 100.0000 | +------------+--------+----------+
6.查询所有同学的学号、姓名、选课数、总成绩；
mysql> select student.sid,student.sname,count(1),sum(number) from score left join student on student.sid = score.student_id group by score.student_id; +------+--------+----------+-------------+ | sid | sname | count(1) | sum(number) | +------+--------+----------+-------------+ | 1 | 钢弹 | 2 | 119 | | 2 | 铁锤 | 1 | 100 | +------+--------+----------+-------------+
7.查询没学过“波多”老师课的同学的学号、姓名；
mysql> select student.sid,student.sname from (select score.student_id as iid from score where course_id not in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group by score.student_id)as i left join student on i.iid = student.sid; +------+--------+ | sid | sname | +------+--------+ | 1 | 钢弹 | | 2 | 铁锤 | +------+--------+ #select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空'，这个老师的所有课的id， 接着是所有没选这个老师课的学生id的分组列表，最后和学生表连表得到结果
mysql> select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空'; +-----+ | cid | +-----+ | 3 | +-----+ 这是苍空老师教的课； mysql> select score.student_id from score where course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group by score.student_id; Empty set (0.00 sec)这是从成绩表查询谁上了苍空老师的课， select student.sid,student.sname from student where sid not in (select score.student_id from score where course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group by score.student_id); 在学生表中排出这几个人就是没选他的课的人了。
8.查询“生物”课程比“体育”课程成绩高的所有学生的学号；
mysql> select A.student_id from -> (select * from score left join course on score.course_id = course.cid where course.cname='生物') as A -> inner join -> (select * from score left join course on score.course_id = course.cid where course.cname='体育') as B -> on A.student_id = B.student_id where A.number>B.number; +------------+ | student_id | +------------+ | 1 | +------------+
9.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
mysql> select score.student_id,student.sname from score left join student -> on score.student_id=student.sid -> where score.course_id =1 or score.course_id =2 group by student_id having count(course_id)>1; +------------+--------+ | student_id | sname | +------------+--------+ | 1 | 钢弹 | +------------+--------+
10.查询学过“波多”老师所教的所有课的同学的学号；
select cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多'; 波多老师上课的id mysql> select student_id from score where course_id in -> (select cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多') -> group by student_id having count(course_id)= -> (select count(cid) from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多'); +------------+ | student_id | +------------+ | 1 | +------------+ 成绩表里上过他的课的人都列出来，然后进行分组，学过他的课与他带的课一样多就是要查询的学生id
11.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号；
mysql> select A.student_id from -> (select * from score where course_id=1) as A -> inner join -> (select * from score where course_id=2) as B -> on A.student_id = B.student_id where A.number>B.number; +------------+ | student_id | +------------+ | 1 | +------------+
12.查询有课程成绩小于61分的同学的学号、姓名；
mysql> select student.sid,student.sname from student where student.sid in -> (select student_id from score where number<61 group by student_id); +-----+--------+ | sid | sname | +-----+--------+ | 1 | 钢弹 | +-----+--------+
13.查询没有学全所有课的同学的学号、姓名；
mysql> select student_id,student.sname from score left join student on score.student_id = student.sid -> group by student_id having count(1)<(select count(1) from course); +------------+--------+ | student_id | sname | +------------+--------+ | 1 | 钢弹 | | 2 | 铁锤 | +------------+--------+
14.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
mysql> select score.student_id,student.sname from score -> left join student on score.student_id = student.sid -> where student_id !=1 and course_id in (select course_id from score where student_id = 1) -> group by student_id; +------------+--------+ | student_id | sname | +------------+--------+ | 2 | 铁锤 | +------------+--------+
15.查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
mysql> select score.student_id,student.sname from score -> left join student on score.student_id = student.sid -> where student_id !=2 and course_id in (select course_id from score where student_id = 2) -> group by student_id -> having count(course_id)=(select count(course_id) from score where student_id = 2); +------------+--------+ | student_id | sname | +------------+--------+ | 1 | 钢弹 | +------------+--------+
16.查询和“002”号的同学学习的课程完全相同的其他同学学号;
mysql> select student_id from score where student_id in ( -> select student_id from score where student_id !=2 group by student_id having count(1) =(select count(1) from score where student_id = 2)) -> and course_id in (select course_id from score where student_id = 2) group by student_id having count(1)=(select count(1) from score where student_id = 2); Empty set (0.00 sec) and之前找到和2上课一样所有的id，and之后这个人上的所有课的id也要和2同学一样 select count(1) from score where student_id = 2;#2同学上的课个数 select student_id from score where student_id !=2 group by student_id having count(1) =(select count(1) from score where student_id = 2); #和2上课数量一样多的同学
17.删除学习“波多”老师课的score表记录；
delete from score where course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '波多'); 取出老师上的所有课的id再记录表中删除
18.向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) from student where sid not in ( select student_id from score where course_id = 2);
19.按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩;
mysql> select sc.student_id, -> (select number from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as '生物', -> (select number from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as '物理', -> (select number from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as '体育', -> avg(sc.number) -> from score as sc -> group by student_id asc; +------------+--------+--------+--------+----------------+ | student_id | 生物 | 物理 | 体育 | avg(sc.number) | +------------+--------+--------+--------+----------------+ | 1 | 60 | NULL | 59 | 59.5000 | | 2 | NULL | NULL | 100 | 100.0000 | +------------+--------+--------+--------+----------------+
20.查询各科成绩最高和最低的分；
mysql> select student_id,max(number),min(number) from score group by student_id; +------------+-------------+-------------+ | student_id | max(number) | min(number) | +------------+-------------+-------------+ | 1 | 60 | 59 | | 2 | 100 | 100 | +------------+-------------+-------------+
在此基础上如果我想要让低于60分的都为0显示，需要用到sql的判断语句；
mysql> select student_id,max(number),min(number),min(number)+1,case when min(number)<60 then 0 else min(number) end as 成绩 from score group by student_id; +------------+-------------+-------------+---------------+--------+ | student_id | max(number) | min(number) | min(number)+1 | 成绩 | +------------+-------------+-------------+---------------+--------+ | 1 | 60 | 59 | 60 | 0 | | 2 | 100 | 100 | 101 | 100 | +------------+-------------+-------------+---------------+--------+
21.按各科平均成绩从低到高和及格率的百分数从高到低顺序；
mysql> select course_id, avg(number) as avgnum,sum(case when score.number > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc; +-----------+---------+---------+ | course_id | avgnum | percent | +-----------+---------+---------+ | 1 | 60.0000 | 0.0000 | | 2 | 79.5000 | 50.0000 | +-----------+---------+---------+ #大于60分标记成1否则为0，count(1)或者用sum（1）这是单独摆成一列为这科考试的总人数，相当于但都有一列全都为1，然后除左边的合格人数1除以右边总人数就是及格率了。
22.课程平均分从高到低显示；
mysql> select avg(if(isnull(score.number),0,score.number)),teacher.tname from score -> left join course on score.course_id = course.cid -> left join teacher on teacher.tid = course.teach_id -> group by score.course_id; +----------------------------------------------+--------+ | avg(if(isnull(score.number),0,score.number)) | tname | +----------------------------------------------+--------+ | 60.0000 | 波多 | | 79.5000 | 波多 | +----------------------------------------------+--------+ if（isnull（A）B,C）就是sql的三目运算A为空则结果为B，否则为C
23.查询各科成绩前三名的记录;
mysql> select * from -> ( -> select -> student_id, -> course_id, -> number, -> 1, -> (select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 0,1) as first_num, -> (select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 1,1) as third_num -> from -> score as s1 -> ) as T -> where T.number > T.third_num; +------------+-----------+--------+---+-----------+-----------+ | student_id | course_id | number | 1 | first_num | third_num | +------------+-----------+--------+---+-----------+-----------+ | 2 | 2 | 100 | 1 | 100 | 59 | +------------+-----------+--------+---+-----------+-----------+ select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 0,1#第一名并列的成绩并排序
24.查询每门课程被选修的学生数;
select course_id, count(1) from score group by course_id; +-----------+----------+ | course_id | count(1) | +-----------+----------+ | 1 | 1 | | 2 | 2 | +-----------+----------+
查询出只选修了一门课程的全部学生的学号和姓名;
mysql> select student.sid, student.sname,count(1) from score -> left join student on score.student_id = student.sid -> group by student_id having count(1) = 1; +------+--------+----------+ | sid | sname | count(1) | +------+--------+----------+ | 2 | 铁锤 | 1 | +------+--------+----------+
25.查询男生、女生的人数；
mysql> select gender,count(1) from student group by student.gender; +--------+----------+ | gender | count(1) | +--------+----------+ | 女 | 2 | | 男 | 1 | +--------+----------+
26.查询姓“钢”的学生名单；
mysql> select sname from student where sname like '钢%'; +--------+ | sname | +--------+ | 钢弹 | +--------+
27.查询同名同姓学生名单，并统计同名人数；
mysql> select sname,count(1) from student group by sname having count(1)>1; Empty set (0.00 sec)
28.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列;
select course_id,avg(if(isnull(number), 0 ,number)) as avg from score group by course_id order by avg asc,course_id desc;
29.查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
mysql> select student_id,sname,avg(if(isnull(number), 0 ,number)) as avg from score left join student on score.student_id = student.sid group by student_id having avg >85; +------------+--------+----------+ | student_id | sname | avg | +------------+--------+----------+ | 2 | 铁锤 | 100.0000 | +------------+--------+----------+ 先列出所有的平均值，再判断取出
30.查询课程名称为“体育”，且分数低于60的学生姓名和分数;
mysql> select student.sname,score.number from score -> left join course on score.course_id = course.cid -> left join student on score.student_id = student.sid -> where score.number < 60 and course.cname = '体育'; +--------+--------+ | sname | number | +--------+--------+ | 钢弹 | 59 | +--------+--------+
31.查询选修“波多”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select course.cid from course left join teacher on course.teach_id = teacher.tid where tname='波多'; +-----+ | cid | +-----+ | 1 | | 2 | +-----+ 先找出波多老师教授的课程 mysql> select sname,number from score -> left join student on score.student_id = student.sid -> where score.course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where tname='波多') order by number desc limit 1; +--------+--------+ | sname | number | +--------+--------+ | 铁锤 | 100 | +--------+--------+
32.查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id,s2.course_id,s1.number,s2.number from score as s1, score as s2 where s1.number = s2.number and s1.course_id != s2.course_id;