Mysql 第三章：数据表中数据的查询

1.查询三次作业的成绩都在80分以上的学号、课程号

desc  work_info;

select cno,sno

from work_info

where score1>80 and score2>80,score3>80;

Empty set (0.00 sec)

select cno,sno from work_info where score1>=80 and score2>=80 and score3>=80;

2.查询姓张的学生的学号、姓名和专业班级。

desc  student_info;

select sno,sname,s_class from student_info where sname like '张%';

3.查询05级的男生信息。

select * from student_info;

select * from student_info where s_class like '%05' and ssex='男';

多关系的连接查询

4. 查询“赵欣”同学选修了课程的课程号和课程名。

select work_info.cno,cname from student_info,course_info,work_info where(student_info.sno=work_info.sno)and(work_info.cno=course_info.cno) and (sname='赵欣') ;

5. 查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。

 select student_info.sno, avg(score1), avg(score2), avg(score3) from student_info left join work_info on student_info.sno = work_info.sno group by student_info.sno having count(cno) >= 3;

6. 查询没有作业成绩的学号和课程号。

select sno,cno from work_info where score1 is null or score2 is null or score3 is null ;

7. 查询选修了“数据结构”课程学生的学号和姓名。

select student_info.sno,student_info.sname from student_info,course_info,work_info where (student_info.sno=work_info.sno)and(work_info.cno=course_info.cno) and (cname='数据结构') ;

8. 查询于兰兰的选课信息，列出学号、姓名、课程名。

select student_info.sno,student_info.sname,cname from student_info,course_info,work_info where (student_info.sno=work_info.sno)and(work_info.cno=course_info.cno) and (sname='于兰兰') ;