INSERT INTO Book(bid, bName, price, bTypeId) VALUES(2, ‘射雕英雄传‘, 39.62, 10);INSERT INTO Book(bid, bName, price, bTypeId) VALUES(3, ‘天龙八部‘, 50.02, 10);INSERT INTO Book(bid, bName, price, bTypeId) VALUES(4, ‘雪花女神‘, 103.00, 10);INSERT INTO Book(bid, bName, price, bTypeId) VALUES(5, ‘小李飞刀‘, 129.00, 10);-- 创建视图 CREATE OR REPLACE VIEW my_book_viewASSELECT b.bname, bt.name FROM Book bINNER JOIN BookType btON b.bTypeId = bt.idWHERE price > 100;SELECT * FROM my_book_view;-- 修改视图，实现查询价格在90‐120之间的书名和价格CREATE OR REPLACE VIEW my_book_viewASSELECT bname, price FROM BookWHERE price BETWEEN 30 AND 120;-- 第二种方法ALTER VIEW my_book_viewASSELECT bname, price FROM BookWHERE price BETWEEN 90 AND 130;-- 6. 删除刚才创建的视图DROP VIEW IF EXISTS my_book_view;-- 创建考上Peking University的学生的视图CREATE OR REPLACE VIEW beida (id,name,mark,sch)AS SELECT stu_mark.s_id,stu_mark.s_name,stu_mark.mark, sign.s_sign_schFROM stu_mark ,signWHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=41 AND sign.s_sign_sch=‘peking university‘;-- 2. 创建考上Tsinghua University的学生的视图CREATE VIEW qinghua (id,name,mark,sch)AS SELECT stu_mark.s_id, stu_mark.s_name, stu_mark.mark, sign.s_sign_schFROM stu_mark ,signWHERE stu_mark.s_id=sign.s_id AND stu_mark.mark>=40 AND sign.s_sign_sch=‘TsinghuaUniversity‘;-- 3. Xiaowang的成绩在录入的时候因录入错误多录了50分，对其成绩进行更正uPDATE stu_mark SET mark=mark-50 WHERE stu_mark.s_name =‘Xiaowang‘;-- 2. 函数-- 1. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩-- 这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注-- 意，这里必须要给计算得到的AVG结果一个alias.（AS ss）-- 得到学生信息的时候既可以用join也可以用一般的联合搜索-- 第一种写法select student.SId,sname,ss from student,(select SId, AVG(score) as ss from scGROUP BY SIdHAVING AVG(score)> 60)rwhere student.sid = r.sid;-- 第二种写法select Student.SId, Student.Sname, r.ss from Student right join(select SId, AVG(score) AS ss from scGROUP BY SIdHAVING AVG(score)> 60)r on Student.SId = r.SId;-- 2. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和select student.sid, student.sname,r.coursenumber,r.scoresumfrom student,(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from scgroup by sc.sid)rwhere student.sid = r.sid;-- 3. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select student.sid, student.sname, AVG(sc.score) from student,scwherestudent.sid = sc.sid and sc.score<60group by sc.sid having count(*)>1;-- 4. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select * from scleft join (select sid,avg(score) as avscore from scgroup by sid)ron sc.sid = r.sidorder by avscore desc;-- 5. 查询 1990 年出生的学生名单select *from studentwhere YEAR(student.Sage)=1990;-- 6. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, coursewhere sc.cid = course.cidgroup by sc.cidorder by average desc,cid asc;-- 7. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩select student.sid, student.sname, AVG(sc.score) as aver from student, scwhere student.sid = sc.sidgroup by sc.sidhaving aver > 85;-- 8. 查询本周过生日的学生select *from studentwhere WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());-- 9. 查询下周过生日的学生select *from studentwhere WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;-- 10. 查询本月过生日的学生select *from studentwhere MONTH(student.Sage)=MONTH(CURDATE());-- 11. 查询下月过生日的学生select *from studentwhere MONTH(student.Sage)=MONTH(CURDATE())+1;

-- 1. 查询没学过"张三"老师讲授的任一门课程的学生姓名select * from student where student.sid not in( select sc.sid from sc where sc.cid in(select course.cid from course where course.tid in( select teacher.tid from teacher wheretname = "张三" ) ) );-- 2. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select * from sc left join ( select sid,avg(score) as avscore from sc group by sid )r onsc.sid = r.sid order by avscore desc;-- 3. 科成绩进行排序，并显示排名， Score 重复时保留名次空缺-- 这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交） 用sc中的score和-- 自己进行对比，来计算“比当前分数高的分数有几个”。select a.cid, a.sid, a.score, count(b.score)+1 as rank from sc as a left join sc as b ona.score<b.score and a.cid = b.cid group by a.cid, a.sid,a.score order by a.cid, rank ASC;-- 4. 查询各科成绩前三名的记录select * from sc where ( select count(*) from sc as a where sc.cid = a.cid andsc.score<a.score )< 3 order by cid asc, sc.score desc;-- 5. 嵌套查询列出同名的全部学生的信息select * from student where sname in ( select sname from student group by snamehaving count(*)>1 );-- 6. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course where sc.cid =course.cid group by sc.cid order by average desc,cid asc;-- 7. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩(having也可以用来截取结果表，-- 在这里就先得到平均成绩总表，再截取AVG大于85的即可.)select student.sid, student.sname, AVG(sc.score) as aver from student, sc wherestudent.sid = sc.sid group by sc.sid having aver > 85;-- 8. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数select student.sname, sc.score from student, sc, course where student.sid = sc.sid andcourse.cid = sc.cid and course.cname = "数学" and sc.score < 60;