#随记SQL
学生表
USE BOOK
CREATE TABLE student – 学生信息表
(
stuID CHAR(10) primary key, – 学生编号
stuName CHAR(10) NOT NULL , – 学生名称
major CHAR(50) NOT NULL – 专业
)
图书表
CREATE TABLE book – 图书表
(
BID CHAR(10) primary key, – 图书编号
title CHAR(50) NOT NULL, – 书名
author CHAR(20) NOT NULL, – 作者
)
借书表
CREATE TABLE borrow – 借书表
(
borrowID CHAR(10) primary key, – 借书编号
stuID CHAR(10) foreign key(stuID) references student(stuID), – 学生编号
BID CHAR(10) foreign key(BID) references book(BID),-- 图书编号
T_time datetime NOT NULL, – 借出日期
B_time datetime – 归还日期
)
学生信息插入数据
INSERT INTO student(stuID,stuName,major)VALUES(‘1001’,‘林林’,‘计算机’)
INSERT INTO student(stuID,stuName,major)VALUES(‘1002’,‘白杨’,‘计算机’)
INSERT INTO student(stuID,stuName,major)VALUES(‘1003’,‘虎子’,‘英语’)
INSERT INTO student(stuID,stuName,major)VALUES(‘1004’,‘北漂的雪’,‘工商管理’)
INSERT INTO student(stuID,stuName,major)VALUES(‘1005’,‘五月’,‘数学’)
图书信息插入数据
INSERT INTO book(BID,title,author)VALUES(‘B001’,‘人生若只如初见’,‘安意如’)
INSERT INTO book(BID,title,author)VALUES(‘B002’,‘入学那天遇见你’,‘晴空’)
INSERT INTO book(BID,title,author)VALUES(‘B003’,‘感谢折磨你的人’,‘如娜’)
INSERT INTO book(BID,title,author)VALUES(‘B004’,‘我不是教你诈’,‘刘庸’)
INSERT INTO book(BID,title,author)VALUES(‘B005’,‘英语四级’,‘白雪’)
借书信息插入数据
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T001’,‘1001’,‘B001’,‘2007-12-26’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T002’,‘1004’,‘B003’,‘2008-1-5’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T003’,‘1005’,‘B001’,‘2007-10-8’,‘2007-12-25’)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T004’,‘1005’,‘B002’,‘2007-12-16’,‘2008-1-7’)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T005’,‘1002’,‘B004’,‘2007-12-22’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T006’,‘1005’,‘B005’,‘2008-1-6’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T007’,‘1002’,‘B001’,‘2007-9-11’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T008’,‘1005’,‘B004’,‘2007-12-10’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T009’,‘1004’,‘B005’,‘2007-10-16’,‘2007-12-18’)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T010’,‘1002’,‘B002’,‘2007-9-15’,‘2008-1-5’)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T011’,‘1004’,‘B003’,‘2007-12-28’,null)
INSERT INTO borrow(borrowID,stuID,BID,T_time,B_time)VALUES(‘T012’,‘1002’,‘B003’,‘2007-12-30’,null)
- 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、学生名称、图书编号、图书名称、借出日期;
select s.stuID 学生编号,s.stuName 学生名称,k.BID 图书编号,k.title 图书名称,b.T_time 借出日期
from borrow b inner join student s on s.stuID = b.stuID inner join book k on k.BID = b.BID
where T_time > ‘2007-12-15’ and T_time < ‘2008-1-8’ and s.major = ‘计算机’
–
select stuID 学生编号,(select stuName from student where stuID=borrow.stuID) 学生名称,
BID 图书编号,(select title from book where BID=borrow.BID) 图书名称,T_time 借出日期
from borrow where stuID in (select stuID from student where major=‘计算机’)
and T_time>‘2007-12-15’ and T_time<‘2008-1-8’
- 查询所有借过图书的学生编号、学生名称、专业;
select s.stuID 学生编号,s.stuName 学生名称,s.major 专业 from borrow b
inner join student s on b.stuID = s.stuID group by s.stuID
–
select stuID 学生编号,stuName 学生名称,major 专业
from student where stuID in (select stuID from borrow)
- 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;
select (select stuName from student s where s.stuID = b.stuID) 学生姓名,
k.title 图书名称,b.T_time 借出日期,b.B_time 归还日期
from book k inner join borrow b on k.BID = b.BID
where k.author = ‘安意如’
–
select (select stuName from student where stuID=borrow.stuID) 学生名称,
(select title from book where BID=borrow.BID) 图书名称,T_time 借出日期,
B_time 归还日期 from borrow where BID in (select BID from book where author=‘安意如’)
- 查询目前借书 但未归 还图书的学生名称 及未还图书数量;
select s.stuName 学生名称,count(*) 图书数量
from borrow b inner join student s on b.stuID = s.stuID
where B_time is null group by s.stuName
select (select stuName from student where stuID=borrow.stuID) 学生名称,
count(*) 借书数量 from borrow where B_time is null group by stuID
学生表
CREATE TABLE Members
(
MID char(10) primary key,
MName char(50) NOT NULL
)
课程表
CREATE TABLE F – 课程表
(
FID char(10) primary key, – 课程号
FName CHAR(50) NOT NULL – 课程名
)
成绩表
CREATE TABLE score – 学生成绩表
(
SID int primary key, – 成绩记录号
FID char(10) , – 课程号
MID char(10) , – 学生号
score int(10) – 成绩
)
课程表插入数据
INSERT INTO F(FID,FName)VALUES(‘F001’,‘语文’)
INSERT INTO F(FID,FName)VALUES(‘F002’,‘数学’)
INSERT INTO F(FID,FName)VALUES(‘F003’,‘英语’)
INSERT INTO F(FID,FName)VALUES(‘F004’,‘历史’)
学生表插入数据
INSERT INTO Members(MID,MName)VALUES(‘M001’,‘张萨’)
INSERT INTO Members(MID,MName)VALUES(‘M002’,‘王强’)
INSERT INTO Members(MID,MName)VALUES(‘M003’,‘李三’)
INSERT INTO Members(MID,MName)VALUES(‘M004’,‘李四’)
INSERT INTO Members(MID,MName)VALUES(‘M005’,‘阳阳’)
INSERT INTO Members(MID,MName)VALUES(‘M006’,‘虎子’)
INSERT INTO Members(MID,MName)VALUES(‘M007’,‘夏雪’)
INSERT INTO Members(MID,MName)VALUES(‘M008’,‘璐璐’)
INSERT INTO Members(MID,MName)VALUES(‘M009’,‘珊珊’)
INSERT INTO Members(MID,MName)VALUES(‘M010’,‘香奈儿’)
成绩表插入数据
INSERT INTO Score(FID,MID,Score)VALUES(‘F001’,‘M001’,78)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M001’,67)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M001’,89)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M001’,76)
INSERT INTO Score(FID,MID,Score)VALUES(‘F001’,‘M002’,89)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M002’,67)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M002’,84)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M002’,96)
INSERT INTO Score(FID,MID,Score)VALUES(‘F001’,‘M003’,70)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M003’,87)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M003’,92)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M003’,56)
INSERT INTO Score(FID,MID,Score)VALUES(‘F001’,‘M004’,80)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M004’,78)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M004’,97)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M004’,66)
INSERT INTO Score(FID,MID,Score)VALUES(‘F001’,‘M006’,88)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M006’,55)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M006’,86)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M006’,79)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M007’,77)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003’,‘M008’,65)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M007’,48)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004’,‘M009’,75)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002’,‘M009’,88)
- 查询各个学生语文、数学、英语、历史课程成绩
select s.MName 学生姓名,f.FName 课程,e.score 成绩 from members s
inner join score e on s.MID = e.MID
inner join f f on f.FID = e.FID
SELECT Members.MName AS 姓名,
SUM(CASE F.FName WHEN ‘语文’ THEN Score.Score END) 英语,
SUM(CASE F.FName WHEN ‘数学’ THEN Score.Score END) 数学,
SUM(CASE F.FName WHEN ‘英语’ THEN Score.Score END) 英语,
SUM(CASE F.FName WHEN ‘历史’ THEN Score.Score END) 历史
FROM Score, Members,F
WHERE F.FID = Score.FID AND Members.MID =Score.MID GROUP BY Members.MName
- 查询四门课中成绩低于70分的学生及相对应课程名和成绩。
select s.MName 学生姓名,e.score 成绩,f.FName 课程 from score e
inner join members s on e.MID = s.MID
inner join f f on f.FID = e.FID
where e.score < 70
select (select MName from Members where MID=Score.MID) 姓名,
(select FName from F where FID=Score.FID) 课程,
Score 成绩 from Score where Score<70
- 统计各个学生参加考试课程的平均分,且按平均分数由高到底排序。
select members.MName 姓名,avg(score) 平均分 from score inner join
members on members.MID = score.MID
group by members.MName order by 平均分 desc
select (select MName from Members where MID=Score.MID) 姓名,
Avg(Score) 平均分 from Score group by MID order by 平均分 desc
- 创建存储过程,分别查询参加 1、2、3、4门考试及没有参加考试的学生名单,要求显示姓名、学号。
create procedure P_stu ( in num int )
begin
select (select MName from Members where MID=Score.MID) 姓名,
MID 学号 from Score group by MID having count(*) = num;
end;
– 调用存储过程 –
CALL P_stu (4)
SELECT Members.MName AS 姓名,
SUM(CASE F.FName WHEN ‘语文’ THEN Score.Score END) 英语,
SUM(CASE F.FName WHEN ‘数学’ THEN Score.Score END) 数学,
SUM(CASE F.FName WHEN ‘英语’ THEN Score.Score END) 英语,
SUM(CASE F.FName WHEN ‘历史’ THEN Score.Score END) 历史
FROM Score, Members,F
WHERE F.FID = Score.FID AND Members.MID =Score.MID GROUP BY Members.MName
学生表 Students
create table Students(SId varchar(10) primary key,Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Students values(‘01’ , ‘巫瑜’ , ‘1990-01-01’ , ‘男’);
insert into Students values(‘02’ , ‘欧阳沨’ , ‘1990-12-21’ , ‘男’);
insert into Students values(‘03’ , ‘夏冰’ , ‘1990-12-20’ , ‘男’);
insert into Students values(‘04’ , ‘万嘞’ , ‘1990-12-06’ , ‘男’);
insert into Students values(‘05’ , ‘阿布靛’ , ‘1991-12-01’ , ‘女’);
insert into Students values(‘06’ , ‘凌鳕’ , ‘1992-01-01’ , ‘女’);
insert into Students values(‘07’ , ‘莫竹’ , ‘1989-01-01’ , ‘女’);
insert into Students values(‘09’ , ‘雾瑾’ , ‘2017-12-20’ , ‘女’);
insert into Students values(‘10’ , ‘遽然’ , ‘2017-12-25’ , ‘女’);
insert into Students values(‘11’ , ‘卿卿’ , ‘2012-06-06’ , ‘女’);
insert into Students values(‘12’ , ‘戏欸’ , ‘2013-06-13’ , ‘女’);
insert into Students values(‘13’ , ‘彬淋’ , ‘2014-06-01’ , ‘女’);
科目表 Course
create table Course(CId varchar(10) primary key,Cname nvarchar(10),TId varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
教师表 Teacher
create table Teacher(TId varchar(10) primary key,Tname varchar(10));
insert into Teacher values(‘01’ , ‘龙扉’);
insert into Teacher values(‘02’ , ‘翡霏’);
insert into Teacher values(‘03’ , ‘羽宁’);
成绩表 SCORES
create table Scores(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into Scores values(‘01’ , ‘01’ , 80);
insert into Scores values(‘01’ , ‘02’ , 90);
insert into Scores values(‘01’ , ‘03’ , 99);
insert into Scores values(‘02’ , ‘01’ , 70);
insert into Scores values(‘02’ , ‘02’ , 60);
insert into Scores values(‘02’ , ‘03’ , 80);
insert into Scores values(‘03’ , ‘01’ , 80);
insert into Scores values(‘03’ , ‘02’ , 80);
insert into Scores values(‘03’ , ‘03’ , 80);
insert into Scores values(‘04’ , ‘01’ , 50);
insert into Scores values(‘04’ , ‘02’ , 30);
insert into Scores values(‘04’ , ‘03’ , 20);
insert into Scores values(‘05’ , ‘01’ , 76);
insert into Scores values(‘05’ , ‘02’ , 87);
insert into Scores values(‘06’ , ‘01’ , 31);
insert into Scores values(‘06’ , ‘03’ , 34);
insert into Scores values(‘07’ , ‘02’ , 89);
insert into Scores values(‘07’ , ‘03’ , 98);
0 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select s1.SId,s1.score1,s2.score2 from
(select SId,score score1 from scores where CId = ‘01’) as s1,
(select SId,score score2 from scores where CId = ‘02’) as s2
where s1.SId = s2.SId and s1.score1 > s2.score2
select * from Students RIGHT JOIN (
select t1.SId, class1, class2 from
(select SId, score as class1 from scores where scores.CId = ‘01’) as t1,
(select SId, score as class2 from scores where scores.CId = ‘02’) as t2
where t1.SId = t2.SId AND t1.class1 > t2.class2
) r
on Students.SId = r.SId;
select * from (
select t1.SId, class1, class2
from
(SELECT SId, score as class1 FROM scores WHERE scores.CId = ‘01’) AS t1,
(SELECT SId, score as class2 FROM scores WHERE scores.CId = ‘02’) AS t2
where t1.SId = t2.SId and t1.class1 > t2.class2
) r
LEFT JOIN Students
ON Students.SId = r.SId;
1 查询同时存在" 01 “课程和” 02 "课程的情况
select s1.SId,s1.CId,s1.score,s2.CId,s2.score from
(select * from scores where CId = ‘01’) s1,
(select * from scores where CId = ‘02’) s2
where s1.SId = s2.SId
2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select s1.SId,s1.CId,s1.score,s2.CId,s2.score from
(select * from scores where CId = ‘01’) s1 left join
(select * from scores where CId = ‘02’) s2 on s1.SId = s2.SId
3 查询不存在" 01 “课程但存在” 02 "课程的情况
select s1.SId,s1.CId,s1.score,s2.CId,s2.score from
(select * from scores where CId = ‘01’) s1 right join
(select * from scores where CId = ‘02’) s2
on s1.SId = s2.SId where s1.CId is null
4 查询平均成绩大于等于 60 分的同学 的学生编号和学生姓名和平均成绩
select SId 学生编号,
(select Sname from students s where s.SId = scores.SId) 学生姓名,
avg(score) 平均成绩 from scores group by SId having 平均成绩 >= 60
5 查询在 SC 表存在成绩的学生信息
select s.*,e.score from students s inner join scores e on s.SId = e.SId
6 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select scores.SId 学生编号,
(select s.Sname from students s where s.SId = scores.SId) 学生姓名,
count(scores.CId) 选课总数,
sum(scores.score) 总成绩
from scores group by scores.SId
select s.SId 学生编号,s.Sname 学生姓名,
(select count(scores.CId) from scores where scores.SId = s.SId) 选课总数,
(select sum(scores.score) from scores where scores.SId = s.SId) 总成绩
from students s group by s.SId
7 查有成绩的学生信息
select s.* from students s inner join scores on s.SId = scores.SId group by s.SId
8 查询「李」姓老师的数量
select count(*) from teacher where Tname like ‘%扉%’
9 查询学过「张三」老师授课的同学的信息
select s.*,c.Cname,t.Tname,e.score from students s
inner join scores e on s.SId = e.SId
inner join Course c on e.CId = c.CId
inner join teacher t on t.TId = c.TId
where t.Tname = '龙扉
10 查询没有学全所有课程的同学的信息
select s.*,count(e.CId) 课程 from students s left join scores e on s.SId = e.SId
group by s.SId having 课程 <> 3
11 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from students s where SId in
( select SId from scores where CId in
(select e.CId from students s left join
scores e on s.SId = e.SId where s.SId = ‘01’ ) group by SId )