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_view
AS
SELECT b.bname, bt.name
FROM Book b
INNER JOIN BookType bt
ON b.bTypeId
= bt.id
WHERE price
> 100;
SELECT * FROM my_book_view;
-- 修改视图，实现查询价格在90‐120之间的书名和价格
CREATE OR REPLACE VIEW my_book_view
AS
SELECT bname, price
FROM Book
WHERE price
BETWEEN 30 AND 120;
-- 第二种方法
ALTER VIEW my_book_view
AS
SELECT bname, price
FROM Book
WHERE 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_sch
FROM stu_mark ,
sign
WHERE 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_sch
FROM stu_mark ,
sign
WHERE stu_mark.s_id
=sign.s_id
AND stu_mark.mark
>=40 AND sign.s_sign_sch
=‘Tsinghua
University‘;
-- 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 sc
GROUP BY SId
HAVING AVG(score)
> 60
)r
where student.sid
= r.sid;
-- 第二种写法
select Student.SId, Student.Sname, r.ss
from Student
right join(
select SId,
AVG(score)
AS ss
from sc
GROUP BY SId
HAVING AVG(score)
> 60
)r on Student.SId
= r.SId;
-- 2. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid,
sum(sc.score)
as scoresum,
count(sc.cid)
as coursenumber
from sc
group by sc.sid)r
where student.sid
= r.sid;
-- 3. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid, student.sname,
AVG(sc.score)
from student,sc
where
student.sid = sc.sid
and sc.score
<60
group by sc.sid
having count(
*)
>1;
-- 4. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc
left join (
select sid,
avg(score)
as avscore
from sc
group by sid
)r
on sc.sid
= r.sid
order by avscore
desc;
-- 5. 查询 1990 年出生的学生名单
select *
from student
where YEAR(student.Sage)
=1990;
-- 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 的所有学生的学号、姓名和平均成绩
select student.sid, student.sname,
AVG(sc.score)
as aver
from student, sc
where student.sid
= sc.sid
group by sc.sid
having aver
> 85;
-- 8. 查询本周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)
=WEEKOFYEAR(CURDATE());
-- 9. 查询下周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)
=WEEKOFYEAR(CURDATE())
+1;
-- 10. 查询本月过生日的学生
select *
from student
where MONTH(student.Sage)
=MONTH(CURDATE());
-- 11. 查询下月过生日的学生
select *
from student
where 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 where
tname = "张三" ) ) );
-- 2. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc left join ( select sid,avg(score) as avscore from sc group by sid )r on
sc.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 on
a.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 and
sc.score<a.score )< 3 order by cid asc, sc.score desc;
-- 5. 嵌套查询列出同名的全部学生的信息
select * from student where sname in ( select sname from student group by sname
having 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 where
student.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 and
course.cid = sc.cid and course.cname = "数学" and sc.score < 60;
SQL 索引 视图
标签：课程 英雄 HERE join 根据 rank cname color rda
