SHOW ENGINES;
SELECT VERSION();/*==============================================================*//* DBMS name: MySQL 5.0 *//* Created on: 2019/8/8/周四 11:32:18 *//*==============================================================*/
DROP TABLE IF EXISTS CLASSES;
DROP TABLE IF EXISTS Departement;
DROP TABLE IF EXISTS Major;
DROP TABLE IF EXISTS competition;
DROP TABLE IF EXISTS loginlog;
DROP TABLE IF EXISTS record;
DROP TABLE IF EXISTS team;
DROP TABLE IF EXISTS usersti;/*==============================================================*//* Table: CLASSES *//*==============================================================*/
CREATE TABLE CLASSES
(
classid VARCHAR(20) NOT NULL COMMENT '班级编号',
majorid VARCHAR(20) NOT NULL,
classname VARCHAR(20) COMMENT '班级名称',
grade VARCHAR(10) NOT NULL COMMENT '年级,入学年份',
PRIMARY KEY (classid));
ALTER TABLE CLASSES COMMENT '班级实体';/*==============================================================*//* Table: Departement *//*==============================================================*/
CREATE TABLE Departement
(
departmentid VARCHAR(20) NOT NULL COMMENT '院系编号',
departmentname VARCHAR(50) NOT NULL COMMENT '院系名称',
PRIMARY KEY (departmentid));
ALTER TABLE Departement COMMENT '院系实体';/*==============================================================*//* Table: Major *//*==============================================================*/
CREATE TABLE Major
(
majorid VARCHAR(20) NOT NULL,
departmentid VARCHAR(20) NOT NULL COMMENT '院系编号',
majorname VARCHAR(50) NOT NULL,
PRIMARY KEY (majorid));
ALTER TABLE Major COMMENT '专业实体';/*==============================================================*//* Table: competition *//*==============================================================*/
CREATE TABLE competition
(
competitionid NUMERIC(8,0) NOT NULL COMMENT '赛事编号',
competitionname VARCHAR(50) NOT NULL COMMENT '赛事名称',
competitiondate DATE NOT NULL COMMENT '赛事举办时间',
levels VARCHAR(10) NOT NULL COMMENT '赛事级别',
competitiontype VARCHAR(10) NOT NULL COMMENT '赛事类型',
competitiondesc VARCHAR(1000) COMMENT '赛事说明',
PRIMARY KEY (competitionid));
ALTER TABLE competition COMMENT '赛事实体';/*==============================================================*//* Table: loginlog *//*==============================================================*/
CREATE TABLE loginlog
(
id NUMERIC(8,0) NOT NULL,
realname VARCHAR(10) NOT NULL,
logintime TIMESTAMP NOT NULL,
loginip VARCHAR(40) NOT NULL,
PRIMARY KEY (id));
ALTER TABLE loginlog COMMENT '登录日志';/*==============================================================*//* Table: record *//*==============================================================*/
CREATE TABLE record
(
recordid VARCHAR(50) NOT NULL COMMENT '编号',
competitionid NUMERIC(8,0) COMMENT '赛事编号',
ranks VARCHAR(30) NOT NULL COMMENT '获奖等级',
teamid VARCHAR(20) NOT NULL COMMENT '小组编号',
teamname VARCHAR(100) NOT NULL COMMENT '小组名称',
PRIMARY KEY (recordid));
ALTER TABLE record COMMENT '获奖记录';/*==============================================================*//* Table: team *//*==============================================================*/
CREATE TABLE team
(
teamkey VARCHAR(40) NOT NULL COMMENT '小组的主键',
competitionid NUMERIC(8,0) COMMENT '赛事编号',
teamid VARCHAR(20) NOT NULL COMMENT '小组编号',
teamname VARCHAR(100) NOT NULL COMMENT '小组名称',
studentid VARCHAR(10) NOT NULL COMMENT '学号',
studentname VARCHAR(10) NOT NULL COMMENT '学生姓名',
sex VARCHAR(5) NOT NULL COMMENT '学生性别',
mobile VARCHAR(20) COMMENT '手机号',
email VARCHAR(50) COMMENT '邮箱',
roleflag VARCHAR(2) NOT NULL COMMENT '是不是队长的标志',
teachername VARCHAR(50) NOT NULL COMMENT '指导老师',
departmentname VARCHAR(50) NOT NULL COMMENT '院系名称',
major VARCHAR(20) NOT NULL COMMENT '专业名称',
grade VARCHAR(10) NOT NULL COMMENT '年级,入学年份',
classname VARCHAR(20) NOT NULL COMMENT '班级名称',
userrealname VARCHAR(10) NOT NULL COMMENT '录入人',
inputtime TIMESTAMP NOT NULL COMMENT '录入时间',
PRIMARY KEY (teamkey));
ALTER TABLE team COMMENT '参赛团队实体';
ALTER TABLE team ADD UNIQUE KEY(teamname);
ALTER TABLE team DROP INDEX teamname;
ALTER TABLE team MODIFY classname VARCHAR(20) AFTER major;
ALTER TABLE team CHANGE major majorname VARCHAR(20);/*==============================================================*//* Table: usersti *//*==============================================================*/
CREATE TABLE usersti
(
userid VARCHAR(50) NOT NULL,
username VARCHAR(30) NOT NULL,
realname VARCHAR(10) NOT NULL,
userpsw VARCHAR(100) NOT NULL,
userrole VARCHAR(10) NOT NULL,
PRIMARY KEY (userid));
ALTER TABLE usersti COMMENT '用户';
ALTER TABLE CLASSES ADD CONSTRAINT FK_Major_Classes FOREIGN KEY (majorid)
REFERENCES Major (majorid) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE Major ADD CONSTRAINT FK_department_major FOREIGN KEY (departmentid)
REFERENCES Departement (departmentid) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE record ADD CONSTRAINT FK_Relationship_4 FOREIGN KEY (competitionid)
REFERENCES competition (competitionid) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE team ADD CONSTRAINT FK_Relationship_3 FOREIGN KEY (competitionid)
REFERENCES competition (competitionid) ON DELETE RESTRICT ON UPDATE RESTRICT;
SELECT *
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
INNER JOIN team
ON team.`teamid` = record.`teamid`;
INSERT INTO classes VALUES(0,(SELECT majorid FROM major WHERE majorname ='人工智能'),'智能一班','2015');
SELECT * FROM major WHERE majorid =1;
SELECT majorname, classname,grade
FROM classes
INNER JOIN major
ON classes.`majorid` = major.`majorid`
WHERE 1=1 AND classname ='智能一班';
SELECT * FROM classes LIMIT 2,2;
SELECT COUNT(*) FROM classes;
SELECT COUNT(*) FROM classes
INNER JOIN major
ON classes.`majorid` = major.`majorid`
WHERE 1=1 AND classname LIKE '%能%';
SELECT majorname, classname,grade
FROM classes
INNER JOIN major
ON classes.`majorid` = major.`majorid`
WHERE 1=1 AND classname LIKE '%能%'
LIMIT 2,2;
SELECT *
FROM classes
INNER JOIN major
ON classes.`majorid` = major.`majorid`;
UPDATE classes SET majorid=(SELECT majorid FROM major WHERE majorname ='人工智能'),classname ='稚嫩',grade ='2016' WHERE classid=1;
ROLLBACK;
SELECT *
FROM competition
WHERE 1=1 AND competitionname LIKE '%大%'
AND DATE_FORMAT(competitiondate,'%Y-%m-%d') BETWEEN '2019-01-01' AND '2020-01-31'
LIMIT 2,2;-- 数据总数
SELECT COUNT(*)
FROM competition
WHERE 1=1 AND competitionname LIKE '%大%'
AND DATE_FORMAT(competitiondate,'%Y-%m-%d') BETWEEN '2019-01-01' AND '2020-01-31';
SELECT COUNT(*)
FROM competition
WHERE 1=1 AND competitionname LIKE '%大%'
AND competitiondate >=DATE_FORMAT('2019-01-01','%Y-%m-%d')
AND competitiondate <=DATE_FORMAT('2020-01-02','%Y-%m-%d');
SELECT * FROM competition WHERE DATE_FORMAT(competitiondate,'%Y-%m-%d') BETWEEN '2019-01-01' AND '2020-01-31';
SELECT username,realname,userrole
FROM usersti
WHERE 1=1
AND username LIKE '%ad%'
AND realname LIKE '%%'
AND userrole ='普通用户';
UPDATE usersti SET
userpsw =123456
WHERE userid=8;
INSERT INTO team VALUES(0,(SELECT competitionid FROM competition WHERE competitionname ='中国大学生软件设计大赛'),'a8','穿越火线','162052240','韩洋','男','15503609387','279801800@qq.com','是','张三丰','计算机工程系','人工智能','智能三班','2016','鲁智深',NOW());-- 级联操作显示专业名称，班级名称，赛事名称
-- 根据输入的队伍编号，直接填写队伍名称，指导老师
SELECT * FROM team INNER JOIN competition ON team.`competitionid` = competition.`competitionid` WHERE 1=1 AND competitionname LIKE '%设计%' LIMIT 0,100;
SELECT competitionid FROM competition WHERE competitionname ='中国大学生软件设计大赛';
SELECT classes.*
FROM classes
INNER JOIN major
ON classes.`majorid` = major.`majorid`
WHERE major.`majorname`='人工智能' AND grade ='2016';
SELECT * FROM usersti WHERE username ='花和尚' AND userpsw ='123456';-- 班级表信息 2015-2018级
-- classid，majorid，classname，grade
INSERT INTO classes VALUES('1720511','1','智能一班','2015');
INSERT INTO classes VALUES('1720512','1','智能二班','2015');
INSERT INTO classes VALUES('1520521','2','物工一班','2015');
INSERT INTO classes VALUES('1520522','2','物工二班','2015');
INSERT INTO classes VALUES('1520541','4','软工一班','2015');
INSERT INTO classes VALUES('1520542','4','软工二班','2015');
INSERT INTO classes VALUES('1520551','5','网工一班','2015');
INSERT INTO classes VALUES('1520552','5','网工二班','2015');
INSERT INTO classes VALUES('1520561','6','计算机科学一班','2015');
INSERT INTO classes VALUES('1520562','6','计算机科学二班','2015');
INSERT INTO classes VALUES('1620511','1','智能一班','2016');
INSERT INTO classes VALUES('1620512','1','智能二班','2016');
INSERT INTO classes VALUES('1620521','2','物工一班','2016');
INSERT INTO classes VALUES('1620522','2','物工二班','2016');
INSERT INTO classes VALUES('1620541','4','软工一班','2016');
INSERT INTO classes VALUES('1620542','4','软工二班','2016');
INSERT INTO classes VALUES('1620551','5','网工一班','2016');
INSERT INTO classes VALUES('1620552','5','网工二班','2016');
INSERT INTO classes VALUES('1620561','6','计算机科学一班','2016');
INSERT INTO classes VALUES('1620562','6','计算机科学二班','2016');
INSERT INTO classes VALUES('1720511','1','智能一班','2017');
INSERT INTO classes VALUES('1720512','1','智能二班','2017');
INSERT INTO classes VALUES('1720521','2','物工一班','2017');
INSERT INTO classes VALUES('1720522','2','物工二班','2017');
INSERT INTO classes VALUES('1720541','4','软工一班','2017');
INSERT INTO classes VALUES('1720542','4','软工二班','2017');
INSERT INTO classes VALUES('1720551','5','网工一班','2017');
INSERT INTO classes VALUES('1720552','5','网工二班','2017');
INSERT INTO classes VALUES('1720561','6','计算机科学一班','2017');
INSERT INTO classes VALUES('1720562','6','计算机科学二班','2017');
INSERT INTO classes VALUES('1820511','1','智能一班','2018');
INSERT INTO classes VALUES('1820512','1','智能二班','2018');
INSERT INTO classes VALUES('1820521','2','物工一班','2018');
INSERT INTO classes VALUES('1820522','2','物工二班','2018');
INSERT INTO classes VALUES('1820541','4','软工一班','2018');
INSERT INTO classes VALUES('1820542','4','软工二班','2018');
INSERT INTO classes VALUES('1820551','5','网工一班','2018');
INSERT INTO classes VALUES('1820552','5','网工二班','2018');
INSERT INTO classes VALUES('1820561','6','计算机科学一班','2018');
INSERT INTO classes VALUES('1820562','6','计算机科学二班','2018');
UPDATE classes SET classid='521' WHERE classname='数媒一班';
SELECT * FROM classes;-- 院系表信息
-- departmentid，departmentname
INSERT INTO departement VALUES('05','计算机工程系');
SELECT * FROM departement;
SELECT departmentid,departmentname FROM departement;
SELECT departmentname,majorname
FROM departement,major
WHERE departement.departmentid=major.departmentid;
SELECT departmentname,majorname
FROM departement,major
WHERE departement.departmentid=major.departmentid AND majorid="2";-- 专业表信息
-- majorid，departmentid，majorname
INSERT INTO major VALUES('1','05','人工智能');
INSERT INTO major VALUES('2','05','物联网工程');
INSERT INTO major VALUES('4','05','软件工程');
INSERT INTO major VALUES('5','05','网络工程');
INSERT INTO major VALUES('6','05','计算机科学与技术');
SELECT *
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
INNER JOIN team
ON team.`teamid` = record.`teamid`
WHERE 1=1
AND teachername="裘千尺"
ORDER BY competitiondate DESC
LIMIT 1,10;
UPDATE major SET majorname='人工智能科技' WHERE majorid='1';
SELECT * FROM major;
SELECT majorid,departmentname,majorname
FROM departement,major
WHERE departement.departmentid=major.departmentid AND majorid='1';
SELECT majorid,departmentname,majorname
FROM departement
INNER JOIN major
ON departement.departmentid=major.departmentid
WHERE 1=1 AND majorname LIKE '%工%';
DESC team;
SELECT *
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE 1=1 AND teamname LIKE '%火%'
LIMIT 2,2;
UPDATE team SET inputtime =NOW() WHERE teamkey =22;
SELECT teamkey FROM team WHERE teamid ="a8" AND teamname ="穿越火线";-- 获奖记录 赛事名称，获奖队伍，年级，专业，班级，队伍人员 获奖等级，获奖类型
SELECT *
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
INNER JOIN team
ON team.`teamid` = record.`teamid`
WHERE 1=1 AND studentid ='162052201'
LIMIT 0,10;
SELECT * FROM usersti WHERE userid ="1" AND userpsw ="123456";-- competition.competitionname,record.`teamname`,team.grade,team.`majorname`,team.`classname`,team.`studentname`,team.`studentid`,competition.`levels`,competition.`competitiontype`
-- 用户表信息
-- userid，username，realname，userpsw，userrole
INSERT INTO usersti VALUES('0001','黑旋风','李逵','111','普通用户');
INSERT INTO usersti VALUES('0002','及时雨','宋江','222','管理员');
INSERT INTO usersti VALUES('0003','小李广','花荣','333','普通用户');
INSERT INTO usersti VALUES('0004','行者','武松','444','普通用户');
INSERT INTO usersti VALUES('0005','花和尚','鲁智深','555','管理员');
INSERT INTO usersti VALUES('0006','豹子头','林冲','666','普通用户');-- 赛事表信息
--competitionid(自增),competitionname,competitiondate,levels,competitiontypt,competitiondesc
INSERT INTO competition VALUES(001,'中国大学生软件设计大赛','2019-06-15','国家级','A类','中国大学生软件设计大奖赛是中国教育部举办的全国性软件设计比赛');
INSERT INTO competition VALUES(002,'中国高校蓝桥杯软件设计大赛','2019-09-02','国家级','A类','蓝桥杯软件设计大奖赛是中国教育部，协同外企举办的全国性软件设计比赛');
INSERT INTO competition VALUES(003,'山西省创新软件设计大赛','2019-01-01','省级','B类','山西省创新软件设计大奖赛是山西省教育厅举办的省级软件设计比赛');
INSERT INTO competition VALUES(004,'太原市高校联盟星球杯软件设计大赛','2019-12-12','校级','C类','太原市高校联盟星球杯软件设计大赛是太原市高校联盟举办的市级软件设计比赛');
INSERT INTO competition VALUES(005,'太原工业学院软件设计大赛','2020-01-01','院级','其他类','太原工业学院软件设计大赛是太原工业学院计算机工程系举办的院级软件设计比赛');
INSERT INTO competition VALUES(006,'中国大学生软件设计大赛','2016-06-15','国家级','A类','中国大学生软件设计大奖赛是中国教育部举办的全国性软件设计比赛');-- 参赛人员表
-- teamkey,competitionid,teamid,teamname,studentid,studentname,sex,mobile,email,roleflag,teachername,departmentname,majorname,classname,grade,userrealname,inputtime
-- 队伍1人员
INSERT INTO team VALUES('1',001,'a1','无敌妇女','162052201','张无忌','男','15503609301','279801800@qq.com','1','张三丰','计算机工程系','物联网工程','物工二班','2016','鲁智深',NOW());
INSERT INTO team VALUES('2',001,'a1','无敌妇女','162052202','李铁花','男','15503603333','379801800@qq.com','0','张三丰','计算机工程系','物联网工程','物工二班','2016','鲁智深',NOW());
INSERT INTO team VALUES('3',001,'a1','无敌妇女','162056103','王翠花','女','15503604444','479801800@qq.com','0','张三丰','计算机工程系','计算机科学与技术','计算机科学一班','2016','鲁智深',NOW());
INSERT INTO team VALUES('4',001,'a1','无敌妇女','162055204','邱大锤','男','15503605555','579801800@qq.com','0','张三丰','计算机工程系','网络工程','网工二班','2016','鲁智深',NOW());-- 队伍2人员
INSERT INTO team VALUES('5',001,'a2','无敌壮汉','162054102','杨过','男','15503609302','279801800@163.com','1','裘千尺','计算机工程系','软件工程','软工一班','2016','鲁智深',NOW());
INSERT INTO team VALUES('6',001,'a2','无敌壮汉','162054103','杨大过','男','15503608524','679801800@163.com','0','裘千尺','计算机工程系','软件工程','软工一班','2016','鲁智深',NOW());
INSERT INTO team VALUES('7',001,'a2','无敌壮汉','162055110','杨小过','女','15503609877','779801800@163.com','0','裘千尺','计算机工程系','网络工程','网工一班','2016','鲁智深',NOW());
INSERT INTO team VALUES('8',001,'a2','无敌壮汉','162052210','张过','男','15503607989','879801800@163.com','0','裘千尺','计算机工程系','物联网工程','物工二班','2016','鲁智深',NOW());-- 队伍3人员
INSERT INTO team VALUES('9',002,'b1','鸡你太美','162056101','花无缺','男','15503603692','979801800@163.com','1','裘千尺','计算机工程系','计算机科学与技术','计算机科学一班','2016','宋江',NOW());
INSERT INTO team VALUES('10',002,'b1','鸡你太美','162056202','迟重瑞','男','15503601591','119801800@163.com','0','裘千尺','计算机工程系','计算机科学与技术','计算机科学二班','2016','宋江',NOW());
INSERT INTO team VALUES('11',002,'b1','鸡你太美','162052201','张无忌','男','15503609301','279801800@qq.com','0','裘千尺','计算机工程系','物联网工程','物工二班','2016','宋江',NOW());
INSERT INTO team VALUES('12',002,'b1','鸡你太美','162052210','张过','男','15503607989','879801800@163.com','0','裘千尺','计算机工程系','物联网工程','物工二班','2016','宋江',NOW());-- 队伍4人员
INSERT INTO team VALUES('13',003,'c1','火星计划','162056135','李驰','男','15503601231','971801800@163.com','1','苏菲菲','计算机工程系','计算机科学与技术','计算机科学一班','2016','宋江',NOW());
INSERT INTO team VALUES('14',003,'c1','火星计划','172054235','迟帅','男','15503601232','112801800@163.com','0','苏菲菲','计算机工程系','软件工程','软工二班','2017','宋江',NOW());
INSERT INTO team VALUES('15',003,'c1','火星计划','172052201','周三','男','15503601233','273801800@qq.com','0','苏菲菲','计算机工程系','物联网工程','物工二班','2017','宋江',NOW());
INSERT INTO team VALUES('16',003,'c1','火星计划','182052210','周四','男','15503601234','874801800@163.com','0','苏菲菲','计算机工程系','物联网工程','物工二班','2018','宋江',NOW());-- 队伍5人员
INSERT INTO team VALUES('17',006,'d1','坚强绿帽','152055240','王春','男','13503609301','279801811@qq.com','1','孔玲德','计算机工程系','网络工程','网工二班','2015','鲁智深',NOW());
INSERT INTO team VALUES('18',006,'d1','坚强绿帽','152055241','李秋','男','13503603333','379801811@qq.com','0','孔玲德','计算机工程系','网络工程','网工二班','2015','鲁智深',NOW());
INSERT INTO team VALUES('19',006,'d1','坚强绿帽','152056140','张夏','女','13503604444','479801811@qq.com','0','孔玲德','计算机工程系','计算机科学与技术','计算机科学一班','2015','鲁智深',NOW());
INSERT INTO team VALUES('20',006,'d1','坚强绿帽','152055242','韩冬','女','13503605555','579801811@qq.com','0','孔玲德','计算机工程系','网络工程','网工二班','2015','鲁智深',NOW());-- 获奖记录表
--recordid(自增),competitionid,ranks,teamid,teamname
INSERT INTO record VALUES('1',001,'一等奖','a1','无敌妇女');
INSERT INTO record VALUES('2',001,'二等奖','a2','无敌壮汉');
INSERT INTO record VALUES('3',002,'特等奖','b1','鸡你太美');
INSERT INTO record VALUES('4',003,'一等奖','c1','火星计划');
INSERT INTO record VALUES('5',006,'三等奖','d1','坚强绿帽');-- 登录信息表
--id(自增主键),realname,logintime,loginip
INSERT INTO loginlog VALUES('1','鲁智深',NOW(),'192.168.1.162');-- 参加过大赛的队伍信息
SELECT DISTINCT(teamname)
FROM team
WHERE teamid ='d1';-- 本系参赛人数：
-- 获奖人数： 获奖率：
-- 参赛人数最多专业： 获奖率：
-- 参赛人数最多年级： 获奖率
-- 本系参赛人数：
SELECT teamid,COUNT(DISTINCT(studentid)) number FROM team GROUP BY teamid;--23
SELECT teamid,COUNT(DISTINCT(studentid)) number FROM team;
SELECT COUNT(*) FROM team;--25
SELECT COUNT(DISTINCT(studentid)) number FROM team;-- 获奖人数
SELECT teamid,COUNT(*) number
FROM team
WHERE teamid IN (SELECT record.teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`)
GROUP BY teamid;--20-- 获奖人数视图
CREATE VIEW winnernumber AS
(SELECT teamid,COUNT(*) number
FROM team
WHERE teamid IN (SELECT record.teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`));
SELECT number FROM winnernumber;-- 获奖率 83.3%
SELECT B.number/A.number*100 AS rate
FROM
(SELECT teamid,COUNT(DISTINCT(studentid)) number FROM team) A
LEFT JOIN
(SELECT teamid,COUNT(*) number
FROM team
WHERE teamid IN (SELECT record.teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`)) B
ON A.teamid = B.teamid;
SELECT B.number/A.number*100 AS rate
FROM
(SELECT teamid,COUNT(DISTINCT(studentid)) number FROM team) A
LEFT JOIN
winnernumber B
ON A.teamid = B.teamid;-- 参赛人数最多专业： 获奖率：
SELECT majorname,COUNT(DISTINCT(studentid)) number
FROM team
GROUP BY majorname;-- 专业最多的人 --6
SELECT MAX(A.number) number
FROM
(SELECT majorname,COUNT(DISTINCT(studentid)) number
FROM team
GROUP BY majorname) A;-- 人数最多的专业，和最多的人 软工 6
SELECT MAX(A.majorname) majorname
FROM
(SELECT majorname,COUNT(DISTINCT(studentid)) number
FROM team
GROUP BY majorname) A;-- 该专业获奖率0.330%
SELECT (SELECT MAX(A.number) number FROM (SELECT majorname,COUNT(DISTINCT(studentid)) number FROM team GROUP BY majorname) A)/winnernumber.`number`*100 AS rate
FROM winnernumber;-- 参赛人数最多年级： 获奖率
SELECT * FROM grade_number_limit LIMIT 1;-- grade 2016 number 14
CREATE VIEW grade_number_limit AS
(SELECT grade,COUNT(DISTINCT(studentid)) number
FROM team
GROUP BY grade
ORDER BY number DESC);-- 获奖率 70%
SELECT (SELECT number FROM grade_number_limit LIMIT 1)/winnernumber.`number`*100 AS rate
FROM winnernumber;-- 比赛分级统计
SELECT levels,COUNT(*) number
FROM competition
GROUP BY levels;-- 获得级别的学生数量
SELECT levels,COUNT(*) number
FROM competition
LEFT JOIN
record
ON competition.`competitionid`=record.`competitionid`
GROUP BY levels;-- 获奖的队伍 和 赛事
SELECT teamid,competition.competitionid
FROM competition
INNER JOIN
record
ON competition.`competitionid`=record.`competitionid`;
CREATE VIEW winnerteam AS
(SELECT teamid,competition.competitionid
FROM competition
INNER JOIN
record
ON competition.`competitionid`=record.`competitionid`);
SELECT levels,COUNT(*)
FROM winnerteam
INNER JOIN
competition
ON winnerteam.`competitionid` = competition.`co mpetitionid`
GROUP BY levels;-- 获将的人数 按级别 参与
SELECT levels,majorname,COUNT(*) number
FROM team
INNER JOIN
competition
ON team.`competitionid`=competition.`competitionid`
GROUP BY levels,majorname;-- 各专业获得各级别的人数
SELECT levels,majorname,COUNT(*) number
FROM team
LEFT JOIN
competition
ON team.`competitionid`=competition.`competitionid`
LEFT JOIN
record
ON record.`teamid`=team.`teamid`
GROUP BY levels,majorname;
SELECT levels,COUNT(*) number
FROM team
INNER JOIN
competition
ON team.`competitionid`=competition.`competitionid`
INNER JOIN
record
ON record.`teamid`=team.`teamid`
GROUP BY levels;-- 全部专业
SELECT majorname
FROM major;
SELECT major.majorname,IFNULL(国家级,0) 国家级,IFNULL(省级,0) 省级,IFNULL(市级,0) 市级,IFNULL(校级,0) 校级,IFNULL(院级,0) 院级
FROM major
LEFT JOIN
(SELECT majorname,COUNT(CASE WHEN levels='国家级' THEN 1 END) AS 国家级,COUNT(CASE WHEN levels='省级' THEN 1 END) AS 省级,COUNT(CASE WHEN levels='市级' THEN 1 END) AS 市级,COUNT(CASE WHEN levels='校级' THEN 1 END) AS 校级,COUNT(CASE WHEN levels='院级' THEN 1 END) AS 院级
FROM team
LEFT JOIN
competition
ON team.`competitionid`=competition.`competitionid`
LEFT JOIN
record
ON record.`teamid`=team.`teamid`
WHERE record.competitionid="1"
GROUP BY majorname) M
ON major.`majorname`= M.majorname;-- 获奖类型人数统计
SELECT ranks,COUNT(*) number
FROM team
INNER JOIN
record
ON record.`teamid` = team.`teamid`
WHERE grade="2017" AND classname="软工二班"
GROUP BY ranks;-- 按指导老师统计：指定指导老师，显示其指导的队伍、获奖情况。按年份倒序排列
SELECT competitiondate,COUNT(*) number
FROM
INNER JOIN
record
ON record.`teamid` = team.`teamid`
WHERE grade="2017" AND classname="软工二班"
GROUP BY ranks;
SELECT DISTINCT competitiondate,record.teamid,record.teamname
FROM team
RIGHT JOIN record
ON team.`teamid` = record.`teamid`
RIGHT JOIN competition
ON team.`competitionid`= competition.`competitionid`
WHERE teachername ='裘千尺'
ORDER BY competitiondate DESC;-- 各年级参赛人数
SELECT grade,COUNT(*) number
FROM team
WHERE grade="2016"
GROUP BY grade;-- 各班级参与人数
SELECT classname,COUNT(*) number
FROM team
WHERE grade="2017" AND classname="软工二班"
GROUP BY classname;-- 各专业参与人数
SELECT majorname,COUNT(*) number
FROM team
WHERE grade="2017" AND majorname="软件工程"
GROUP BY majorname;
SELECT majorname,COUNT(CASE WHEN ranks='一等奖' THEN 1 END) AS 一等奖,COUNT(CASE WHEN ranks='二等奖' THEN 1 END) AS 二等奖,COUNT(CASE WHEN ranks='特等奖' THEN 1 END) AS 特等奖,COUNT(CASE WHEN ranks='优秀奖' THEN 1 END) AS 优秀奖,COUNT(CASE WHEN ranks='三等奖' THEN 1 END) AS 三等奖
FROM team
LEFT JOIN
competition
ON team.`competitionid`=competition.`competitionid`
LEFT JOIN
record
ON record.`teamid`=team.`teamid`
WHERE record.competitionid="1"
GROUP BY majorname;
SELECT major.majorname,IFNULL(U,0) U,IFNULL(V,0) V,IFNULL(W,0) W,IFNULL(X,0) X,IFNULL(Y,0) Y
FROM major
LEFT JOIN
(SELECT majorname,COUNT(CASE WHEN ranks='一等奖' THEN 1 END) AS U,COUNT(CASE WHEN ranks='二等奖' THEN 1 END) AS V,COUNT(CASE WHEN ranks='三等奖' THEN 1 END) AS W,COUNT(CASE WHEN ranks='特等奖' THEN 1 END) AS X,COUNT(CASE WHEN ranks='优秀奖' THEN 1 END) AS Y
FROM team
LEFT JOIN
competition
ON team.`competitionid`=competition.`competitionid`
LEFT JOIN
record
ON record.`teamid`=team.`teamid`
WHERE record.competitionid="1"
GROUP BY majorname) M
ON major.`majorname`= M.majorname;-- 按姓名、班级、赛事名称、参赛时间、获奖等级、指导教师查询某个学生的参赛记录；一个学生可能有多个参赛记录，
-- 按班级和姓名查询学生信息
SELECT * FROM team WHERE studentname ='张无忌' AND classname ='物工二班';
SELECT team.*,competitionname
FROM team
INNER JOIN competition
ON competition.`competitionid`= team.`competitionid`
WHERE studentname ='张无忌' AND classname ='物工二班';-- 按赛事名称查询出学生信息
SELECT * FROM team
WHERE competitionid =(SELECT competitionid FROM competition WHERE competitionname ='中国大学生软件设计大赛');-- 按参赛日期
SELECT * FROM team
WHERE competitionid =(SELECT competitionid FROM competition WHERE competitiondate ='2019-09-02');-- 按获奖等级
SELECT * FROM team
WHERE teamid =(SELECT teamid FROM record WHERE ranks ='一等奖');-- 按指导老师
SELECT * FROM team
WHERE teachername ='裘千尺';-- 按姓名、班级查询某个学生的参赛记录；一个学生可能有多个参赛记录，赛事名称，参赛时间，获奖等级，指导教师。
-- 不使用视图查询
SELECT competitionname,competitiondate,ranks,teachername
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`
WHERE studentname ='张无忌' AND classname ='物工二班';-- 创建三表联立视图
CREATE VIEW competitionrecord AS
(SELECT competitionname,competitiondate,ranks,teachername,studentname,classname
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`);-- 三表连立，创建competitionrecord视图
SELECT competitionname,competitiondate,ranks,teachername
FROM competitionrecord
WHERE competitionrecord.studentname ='张无忌' AND competitionrecord.classname ='物工二班';-- 按指导老师统计：指定指导老师，显示其指导的队伍、获奖情况。按年份倒序排列。
SELECT team.teamid,team.teamname,levels,ranks,competitiondate
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`
WHERE teachername ='裘千尺'
ORDER BY competitiondate DESC;-- 创建该视图
CREATE VIEW conpetition_teacher AS
(SELECT team.teamid,team.teamname,levels,ranks,competitiondate,teachername
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`);-- 使用视图
SELECT teamid,teamname,levels,ranks,competitiondate
FROM conpetition_teacher
WHERE teachername ='裘千尺'
ORDER BY competitiondate DESC;-- 按年级、专业、班级统计总参赛和获奖情况：
-- 比如：2016级参加过比赛的学生人数；2017级网络工程专业参加比赛人数；2018级计科一班参赛学生人数；
-- 系统能看到具体学生清单，参加的比赛，获得的奖项，获奖名单等；可导出Excel报表。
--2016级参加过比赛的学生人数
SELECT COUNT(DISTINCT (studentid)) snumber
FROM team
WHERE grade ='2016';-- 清单
SELECT studentid,studentname,departmentname,majorname,classname,grade,team.teamname,competitionname,levels,ranks
FROM team
INNER JOIN competition
ON team.`competitionid`= competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`
WHERE grade ='2016';-- 创建视图
CREATE VIEW studentlisting AS
(SELECT studentid,studentname,departmentname,majorname,classname,grade,team.teamname,competitionname,levels,ranks
FROM team
INNER JOIN competition
ON team.`competitionid`= competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`);-- 使用视图查询
SELECT studentid,studentname,departmentname,majorname,classname,grade,teamname,competitionname,levels,ranks
FROM studentlisting
WHERE grade='2016';--2016级网络工程专业参加比赛人数；
SELECT COUNT(DISTINCT (studentid)) snumber
FROM team
WHERE grade ='2016' AND majorname ='网络工程';-- 清单
SELECT studentid,studentname,departmentname,majorname,classname,grade,teamname,competitionname,levels,ranks
FROM studentlisting
WHERE grade='2016' AND majorname ='网络工程';--2016级计科一班参赛学生人数
SELECT COUNT(DISTINCT (studentid)) snumber
FROM team
WHERE grade ='2016' AND classname ='计算机科学一班';-- 清单
SELECT studentid,studentname,departmentname,majorname,classname,grade,teamname,competitionname,levels,ranks
FROM studentlisting
WHERE grade='2016' AND classname ='计算机科学一班';-- 按大赛统计：1.指定大赛，统计各年级参赛人数、各专业、各班级参赛人数，用图表展现，点击图标可以显示出具体数据。统计的数据能导出Excel报表。
--1.指定大赛，统计各年级参赛人数、各专业、各班级参赛人数
-- 参加'中国大学生软件设计大赛'的总人数
SELECT COUNT(DISTINCT(studentid)) snumebr
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛';-- 参加'中国大学生软件设计大赛'，2016级的参赛人数
SELECT COUNT(DISTINCT(studentid)) snumebr
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND grade ='2016';-- 参加'中国大学生软件设计大赛'，2017级的参赛人数
SELECT COUNT(DISTINCT(studentid)) snumebr
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND grade ='2017';-- 参加'山西省创新软件设计大赛’ 某班的情况
SELECT grade,COUNT(classname) classpernum
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛' AND classname ='物工二班';-- 参加'山西省创新软件设计大赛’,各年级参赛人数
SELECT grade,COUNT(*) pernum
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛'
GROUP BY grade;-- 参加'山西省创新软件设计大赛’,2017级，各班参赛人数
SELECT grade,classname,COUNT(*) pernum
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛'
GROUP BY classname,grade
ORDER BY grade;-- 参加'中国大学生软件设计大赛,各专业参赛人数
SELECT majorname,COUNT(*) pernum
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛'
GROUP BY majorname;--2. 指定大赛，统计获奖情况。包括：总获奖队伍，总获奖人数，年级获奖情况、班级获奖情况、专业获奖情况。
SELECT teamid,COUNT(*) teamnum
FROM record
GROUP BY teamid;-- 指定大赛，统计获得奖的队伍数
SELECT COUNT(record.competitionid) number
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛';-- 指定大赛，总获奖人数
-- 获得该赛奖的队伍。
SELECT record.teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛';-- 统计该这些队伍的总人数
SELECT COUNT(*) number
FROM team
WHERE teamid IN (SELECT record.teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛');-- 指定大赛，统计年级获奖情况
SELECT grade,COUNT(*) number
FROM team
WHERE teamid IN
(SELECT teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛')
GROUP BY grade;-- 指定大赛，统计班级获奖情况
SELECT grade,classname,COUNT(*) number
FROM team
WHERE teamid IN
(SELECT teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛')
GROUP BY grade,classname;-- 指定大赛，统计专业获奖情况
SELECT majorname,COUNT(*) number
FROM team
WHERE teamid IN
(SELECT teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='山西省创新软件设计大赛')
GROUP BY majorname;-- 比如：指定一个大赛，查看2016级网络工程专业获得一等奖、二等奖等人数和清单。统计结果可导出Excel，在网页上用eCharts图表呈现。
SELECT ranks,COUNT(*) number
FROM team
WHERE teamid IN
(SELECT teamid
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛')
AND majorname ='网络工程'
AND grade ='2016'
GROUP BY ranks;-- 统计获得一等奖，二等奖的人数
SELECT ranks,COUNT(*) number
FROM record
INNER JOIN team
ON record.`teamid` = team.`teamid`
GROUP BY ranks;-- 统计某大赛，获得一等奖，二等奖的人数
SELECT ranks,COUNT(*) number
FROM record
INNER JOIN team
ON record.`teamid` = team.`teamid`
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛'
GROUP BY ranks;-- 统计某大赛，查看2016级获得一等奖、二等奖等人数
SELECT ranks,COUNT(*) number
FROM record
INNER JOIN team
ON record.`teamid` = team.`teamid`
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND grade ='2016'
GROUP BY ranks;---- 人数
-- 统计某大赛，查看2016级网络工程专业获得一等奖、二等奖等人数和清单
SELECT majorname, ranks,COUNT(*) number
FROM record
INNER JOIN team
ON record.`teamid` = team.`teamid`
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND grade ='2016'
GROUP BY ranks;-------- 清单
-- 统计某大赛，查看2016级网络工程专业获得一等奖、二等奖等人的清单
SELECT ranks,studentname,mobile,email,classname
FROM record
INNER JOIN team
ON record.`teamid` = team.`teamid`
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND grade ='2016' AND majorname ='网络工程';--2. 年度对比。
-- 设定年份时间段，指定比赛名称，显示每年参加该比赛的学生人数、获奖人数、获奖情况。
-- 设定年份时间段，指定比赛名称，显示每年参加该比赛的学生人数
SELECT COUNT(DISTINCT(studentid)) number
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛' AND competitiondate >='2016-01-01' AND competitiondate <='2020-01-01';-- 设定年份时间段，指定比赛名称，显示每年参加该比赛的获奖人数、
SELECT COUNT(DISTINCT(studentid)) number
FROM team
INNER JOIN competition
ON team.`competitionid` = competition.`competitionid`
INNER JOIN record
ON team.`teamid` = record.`teamid`
WHERE competitionname ='中国大学生软件设计大赛'
AND competitiondate >='2016-01-01' AND competitiondate <='2020-01-01';-- 设定年份时间段，指定比赛名称，显示每年参加该比赛的获奖情况。
SELECT ranks,record.teamid,record.teamname,competitiondate
FROM record
INNER JOIN competition
ON record.`competitionid` = competition.`competitionid`
WHERE competitionname ='中国大学生软件设计大赛'
AND competitiondate >='2016-01-01' AND competitiondate <='2020-01-01';-- 按年级、专业、班级统计总参赛和获奖情况：
-- 比如：2016级参加过比赛的学生人数；
--2017级网络工程专业参加比赛人数；
--2018级计科一班参赛学生人数； 系统能看到具体学生清单，参加的比赛，获得的奖项，获奖名单等；
--2016级参加过比赛的学生人数；
SELECT grade,COUNT(DISTINCT(studentid)) number
FROM team
WHERE grade ='2016'
GROUP BY grade;--2017级软件工程专业参加比赛人数；
SELECT grade,COUNT(DISTINCT(studentid)) number
FROM team
WHERE grade ='2017' AND majorname ='软件工程'
GROUP BY grade;--2018级物工二班参赛学生人数；
SELECT grade,COUNT(DISTINCT(studentid)) number
FROM team
WHERE grade ='2018' AND classname ='物工二班'
GROUP BY grade;
SELECT departmentname,majorname
FROM departement,major
WHERE departement.`departmentid`=major.`departmentid`;
COMMIT;
SELECT * FROM loginlog WHERE realname ='智障';
SELECT * FROM loginlog;
SELECT * FROM usersti WHERE username='admin' AND userpsw ='e10adc3949ba59abbe56e057f20f883e';
DELETE FROM classes WHERE classid=23;
ROLLBACK;
MySQL建表及查询练习SHOW ENGINES;SELECT VERSION();/*==============================================================*//* DBMS name: MySQL 5.0 *//* Created on: ...
6659
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
