六、SQL之查询实战

实战一：

  

-- 查询姓张的作者信息
SELECT * FROM `author` WHERE authorName LIKE '张%';

-- 查询联系电话第三为为8或9并以888结尾的作者信息
SELECT * FROM author a WHERE (a.telephone LIKE '__8%' OR a.telephone LIKE '__9%') AND a.telephone LIKE '%888';
SELECT * FROM author a WHERE a.telephone LIKE '__8%888' or a.telephone LIKE '__9%888';
SELECT * FROM author a WHERE SUBSTRING(a.telephone,3,1) in (8,9) and SUBSTRING(a.telephone,9,3)='888';

-- 查询作者的姓名、销量，并按销量降序排列
SELECT authorName,sales FROM author ORDER BY sales DESC;

实战二：

-- 查询平均成绩大于60分的学生学号和平均成绩
SELECT sid,AVG(score) FROM sc GROUP BY sid HAVING AVG(score)>60;

-- 查询所有学生的学号、姓名，选课数，总成绩
SELECT a.sid,sname,count(*),sum(score) FROM student a,sc b WHERE a.sid = b.sid GROUP BY a.sid,sname;

-- 查询出没有学过妮妮老师课程的学生学号和姓名
SELECT * FROM student WHERE sid not in (SELECT c.sid from tearcher a,course b,sc c WHERE a.tname='妮妮' AND a.tid=b.tid and b.cid=c.cid);

-- 查询出所有课程成绩小于60分的学生姓名和学号
SELECT * FROM student where sid in (SELECT sid FROM sc GROUP BY sid HAVING max(score)<60);
SELECT * FROM student where sid not in (SELECT sid FROM sc WHERE score>60);