Demo entry 6789012

1

   

Submitted by anonymous on Apr 24, 2019 at 15:04
Language: SQL. Code size: 15.2 kB.

A管理员:
1.	添加比赛级别

INSERT INTO `level`(Ltype, LGrade) VALUES ('A', '国家级');

2.	删除比赛级别

DELETE FROM `level` WHERE `level`.LID=1

3.	修改比赛级别

UPDATE `level` SET `level`.LType='C', `level`.LGrade='省级'
WHERE `level`.LID=1

4.	查询比赛级别

SELECT * FROM `level`;
SELECT * FORM `level` WHERE `level`.LID=1;

5.	添加比赛奖项

INSERT INTO `prize`(prize.LID, prize.Score, prize.Award)
VALUES ('1', 20, '特等奖');

6.	删除比赛奖项

DELETE FROM prize WHERE prize.PID=1

7.	查询比赛奖项

SELECT PID, prize.LID, Ltype, LGrade, Award, Score
FROM prize, `level`
WHERE prize.LID=`level`.LID;

SELECT PID, prize.LID, Ltype, LGrade, Award, Score
FROM prize, `level`
WHERE prize.LID=`level`.LID AND prize.PID=1;

8.	修改比赛奖项

UPDATE prize
SET prize.LID={newLID}, prize.Award={newAward}, prize.Score={newScore}
WHERE prize.PID={curPID}

9.	增加比赛项目;

INSERT INTO `contest`(COname, Sponsor, Place, Date, LID)
VALUES ('第九届“蓝桥杯”全国软件和信息技术专业人才大赛全国总决赛', '工业和信息化部人才交流中心', '北京', '2018-05-12', '4');

10.	删除比赛项目;

DELETE FROM contest WHERE COID=5

11.	修改比赛项目

UPDATE contest
SET COname={newCOname}, Sponsor={newSponsor}, Place={newPlace}, Date={newDate},LID={newLID}
WHERE COID={curCOID}

12.	通过名称查询比赛项目

SELECT COID, COname, Sponsor, Place, Date, `level`.LID, LType, LGrade
FROM contest, `level`
WHERE contest.LID=`level`.LID AND COname LIKE '%蓝桥%'
ORDER BY Date DESC

13.	通过时间查询比赛项目

SELECT COID, COname, Sponsor, Place, Date, `level`.LID, LType, LGrade
FROM contest, `level`
WHERE contest.LID=`level`.LID AND Date BETWEEN '2018-1-1' AND '2018-6-1'
ORDER BY Date DESC

14.	通过级别查询比赛项目

SELECT COID, COname, Sponsor, Place, Date, `level`.LID, LType, LGrade
FROM contest, `level`
WHERE contest.LID=`level`.LID AND contest.LID=4
ORDER BY Date DESC

15.查询全部比赛项目
SELECT COID, COname, Sponsor, Place, Date, `level`.LID, LType, LGrade
FROM contest, `level`
WHERE contest.LID=`level`.LID
ORDER BY Date DESC

16.	查询全部学生申请信息;

select application.AID '编号', contest.COname '比赛', application.Tname '队伍名', application.PDisc '奖项描述',`level`.LGrade '比赛等级', `level`.LType '比赛类别',prize.Award '获奖等级',teacher.TEname '指导老师',application.AStatus '状态'
from application, prize, `level`, teacher, contest
where application.COID = contest.COID and application.TEID = teacher.TEID and
application.PID = prize.PID and prize.LID = `level`.LID
ORDER BY application.AID DESC

SELECT applicationteam.AID, student.SID, Sname, CLname
FROM applicationteam, student, `class`, application
WHERE applicationteam.AID=application.AID AND student.SID=applicationteam.SID AND `class`.CLID=student.CLID AND application.AID=3

17.	根据审核状态查询学生申请信息;

select application.AID '编号', contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别',prize.Award '获奖等级',teacher.TEname '指导老师',application.AStatus '状态'
from application, prize, `level`, teacher, contest
where application.COID = contest.COID and application.TEID = teacher.TEID and
application.PID = prize.PID and prize.LID = `level`.LID AND AStatus='0'

18.	审核学生申请;

通过审核
UPDATE application SET AStatus='1'
WHERE AID=2

触发器
DROP TRIGGER IF EXISTS `AuditPass`;
CREATE TRIGGER `AuditPass`
AFTER UPDATE ON application
FOR EACH ROW
BEGIN
 	IF (old.AStatus='0') AND (new.AStatus='1') THEN
	INSERT INTO team (team.Tname, team.TEID, team.COID, team.PID, team.PDisc)
	VALUES (new.Tname, new.TEID, new.COID, new.PID, new.PDisc);

	INSERT INTO STT (STT.TID, STT.SID)
	(
		SELECT LAST_INSERT_ID() AS 'TID', applicationteam.SID FROM applicationteam WHERE applicationteam.AID=new.AID
	);
 	END IF;
END;

拒绝审核
UPDATE application SET AStatus='2'
WHERE AID=2

19.	按学号查询某一学生的申请信息;
SELECT application.AID '编号', contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别',prize.Award '获奖等级',teacher.TEname '指导老师',application.AStatus '状态', student.SID '学号', student.Sname '学生姓名'
FROM application, prize, `level`, teacher, contest, applicationteam, student
WHERE application.COID = contest.COID and application.TEID = teacher.TEID and application.AID=applicationteam.AID AND applicationteam.SID=student.SID
AND application.PID = prize.PID and prize.LID = `level`.LID AND applicationteam.SID='201601060824'

20.	增删改查学生信息;

20-1
INSERT INTO `student` VALUES ('201601060101', '脆脆鲨', '男', '1997', '370202199702022563', '2016', '3');

20-2
DELETE FROM `student` WHERE SID='201601060101'

20-3
UPDATE student
SET Sname={Sname}, Ssex={newSex}, Sbirthyear={newSbirthyear},
SIdentity={newSIdentity}, Enrollmentyear={newEnrollmentyear},
CLID={newCLID}
WHERE SID={curSID}

20-4
SELECT SID, Sname, Ssex, Sbirthyear, SIdentity, Enrollmentyear, class.CLID, CLname
FROM student, class
WHERE student.CLID=class.CLID
ORDER BY student.SID

20-5按学号
SELECT SID, Sname, Ssex, Sbirthyear, SIdentity, Enrollmentyear, class.CLID, CLname
FROM student, class
WHERE student.CLID=class.CLID AND student.SID={curSID}

20-6按姓名
SELECT SID, Sname, Ssex, Sbirthyear, SIdentity, Enrollmentyear, class.CLID, CLname
FROM student, class
WHERE student.CLID=class.CLID AND student.Sname={curSname}
ORDER BY student.SID

20-7按班级
SELECT SID, Sname, Ssex, Sbirthyear, SIdentity, Enrollmentyear, class.CLID, CLname
FROM student, class
WHERE student.CLID=class.CLID AND student.Sname={curSname} AND class.CLID={curCLID}
ORDER BY student.SID

20-8SELECT SID, Sname, Ssex, Sbirthyear, SIdentity, Enrollmentyear, class.CLID, CLname
FROM student, class
WHERE student.CLID=class.CLID AND student.Sname={curSname} AND class.CLname={curCLname}
ORDER BY student.SID

21.	增伤改查老师信息;

21-1
INSERT INTO `teacher` VALUES ('1', '丁长青', '女', '讲师', '2');

21-2
DELETE FROM `teacher` WHERE TEID='201601060101'

21-3
UPDATE teacher
SET TEname={newTEname}, TEsex={newTEsex}, Title={newTitle}, MID={newMID}
WHERE TEID={curTEID}



21-4全部
SELECT TEID, TEname, TEsex, Title, teacher.MID, Mname, major.CID, college.Cname
FROM teacher, major, college
WHERE teacher.MID=major.MID AND major.CID=college.CID

21-5按工号
SELECT TEID, TEname, TEsex, Title, teacher.MID, Mname, major.CID, college.Cname
FROM teacher, major, college
WHERE teacher.MID=major.MID AND major.CID=college.CID AND teacher.TEID=1

21-6按姓名
SELECT TEID, TEname, TEsex, Title, teacher.MID, Mname, major.CID, college.Cname
FROM teacher, major, college
WHERE teacher.MID=major.MID AND major.CID=college.CID AND teacher.TEname='丁长青'

21-7按专业
SELECT TEID, TEname, TEsex, Title, teacher.MID, Mname, major.CID, college.Cname
FROM teacher, major, college
WHERE teacher.MID=major.MID AND major.CID=college.CID AND major.MID=1

22.	增删改查学院信息

22-1
INSERT INTO `college` VALUES ('1', '计算机科学与工程学院');

22-2
DELETE FROM `college` WHERE CID=1

22-3
UPDATE college
SET Cname={newCname}
WHERE CID={curCID}

22-4
SELECT * FROM college

23.	增删改查专业信息
23-1
INSERT INTO `major`() VALUES ('计算机科学与技术', '1');

23-2
DELETE FROM major WHERE major.MID=1

23-3
UPDATE major
SET Mname={newMname}, CID={newCID}
WHERE MID={curMID}


23-4全部
SELECT MID, college.CID, Mname, Cname
FROM major, college
WHERE major.CID=college.CID

23-5按专业名
SELECT MID, college.CID, Mname, Cname
FROM major, college
WHERE major.CID=college.CID AND Mname={curMame}

23-6按专业编号
SELECT MID, college.CID, Mname, Cname
FROM major, college
WHERE major.CID=college.CID AND major.MID={curMID}

23-7按学院
SELECT MID, college.CID, Mname, Cname
FROM major, college
WHERE major.CID=college.CID AND major.CID={curCID}

24.	增删改查班级信息
24-1
INSERT INTO `class` VALUES ('2016', '软件工程2016-1', '2');

24-2
DELETE FROM class WHERE CLID={curCLID}

24-3
UPDATE class
SET Grade={newGrade}, CLname={newCLname}, MID={newCLname}
WHERE CLID={curCLID}


24-4查询全部
SELECT CLID, Grade, CLname, class.MID, Mname, major.CID, Cname
FROM class, major, college
WHERE class.MID=major.MID AND college.CID=major.CID

24-5按班级名称查询
SELECT CLID, Grade, CLname, class.MID, Mname, major.CID, Cname
FROM class, major, college
WHERE class.MID=major.MID AND college.CID=major.CID WHERE CLname={curCLname}

24-6按专业查询
SELECT CLID, Grade, CLname, class.MID, Mname, major.CID, Cname
FROM class, major, college
WHERE class.MID=major.MID AND college.CID=major.CID WHERE major.MID={curMID}





T
1.查询本人指导的全部队伍的比赛成绩信息(TEID =  2);
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级'  ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID
order by prize.Score desc

2.查询队伍成员
SELECT student.SID '学号', student.Sname '姓名', student.Ssex '性别', major.Mname '专业', class.CLname '班级'
from team, stt, student, class, major
where team.TID = 16 and stt.TID = team.TID and stt.SID = student.SID and student.CLID = class.CLID and class.MID = major.MID

3.通过比赛名称查询本人指导的比赛成绩信息;
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and contest.COname LIKE '%软件%' order by prize.Score desc

4.通过日期查询本人指导的比赛的成绩信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and contest.Date between '2019-01-01' and '2019-10-03'
order by prize.Score desc

5.通过比赛类别查询本人指导的比赛信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and `level`.LGrade = '国家级' and `level`.LType = 'B'
order by prize.Score desc

6.通过比赛奖项查询本人指导的比赛信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID  and prize.Award = '一等奖'
order by prize.Score desc

7.通过学号查询本人指导的某个学生的比赛成绩信息;
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' 
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and teacher.TEID = 2 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID
order by prize.Score desc



S
学生:
1.查询全部学生本人比赛成绩(计算成绩换算分数,列出排名)
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' , teacher.TEname '指导老师'
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID
order by prize.Score desc

视图
CREATE VIEW stusumview(sid, sname, clid, mid, ssum) AS (SELECT student.SID, student.Sname, student.CLID, class.MID, SUM(prize.Score)
from student, stt, team, prize, class
where student.SID = stt.SID and stt.TID = team.TID and prize.PID = team.PID and class.CLID = student.CLID
GROUP BY student.SID)

班级内排名:
(全部学生)
SELECT sname,ssum,clid, mid, rank
FROM
(
SELECT stusumview.*,IF(@p=mid,@r:=@r+1,@r:=1) AS rank,@p:=mid
FROM stusumview,(SELECT @p:=NULL,@r:=0)r
ORDER BY mid,ssum DESC
)s;

(学号为201601060824的学生)
SELECT sname,ssum,clid, mid, rank
FROM
(
SELECT stusumview.*,IF(@p=mid,@r:=@r+1,@r:=1) AS rank,@p:=mid
FROM stusumview,(SELECT @p:=NULL,@r:=0)r
ORDER BY mid,ssum DESC
)s where sid = 201601060824;



2.通过名称查询学生个人的比赛的成绩信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' , teacher.TEname '指导老师'
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and contest.COname LIKE '%蓝桥%'
order by prize.Score desc


3. 通过日期查询学生个人的比赛的成绩信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' , teacher.TEname '指导老师'
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and contest.Date between '2019-04-01' and '2019-04-03'
order by prize.Score desc


4. 通过比赛类别查询学生个人的比赛信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' , teacher.TEname '指导老师'
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID and `level`.LGrade = '国家级' and `level`.LType = 'B'
order by prize.Score desc

5. 通过奖项查询学生个人比赛信息
SELECT team.Tname '队伍' ,contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别', prize.Award '获奖等级' ,prize.Score '分数' ,contest.Date '时间', contest.Place '地点' , teacher.TEname '指导老师'
from stt,team,prize,contest,`level`,teacher
where stt.SID = 201601060824 and stt.TID = team.TID and prize.PID = team.PID and contest.COID = team.COID and `level`.LID = contest.LID and teacher.TEID = team.TEID  and prize.Award = '特等奖'
order by prize.Score desc

6. 提交添加比赛成绩记录的申请
INSERT INTO application(COID, TEID, Tname, PID, PDisc) VALUES (1, 1, 'nihao', 1, 'wu')
INSERT INTO applicationTeam(AID, SID) VALUES (last_insert_id(), '111')

7. 查询全部记录申请状态
select application.AID '编号', contest.COname '比赛',`level`.LGrade '比赛等级', `level`.LType '比赛类别',prize.Award '获奖等级',teacher.TEname '指导老师',application.AStatus '状态'
from application, applicationteam, student, prize, `level`, teacher, contest
where application.COID = contest.COID and application.TEID = teacher.TEID and application.PID = prize.PID and prize.LID = `level`.LID and student.SID = applicationteam.SID and application.AID = applicationteam.AID and student.SID = '201601060866'

8. 撤销未审核的申请记录;
DELETE FROM application
where application.AID = '1' and application.AStatus = '0'

This snippet took 0.03 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).