表和数据
/*年纪表*/
CREATE TABLE IF NOT EXISTS `grade`(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT'年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT'年级名称',
PRIMARY KEY(`GradeID`)
)ENGINE INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*成绩表*/`grade`
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`StudentNo` INT(4) NOT NULL COMMENT'学号',
`SubjectNo` INT(4) NOT NULL COMMENT'课程编号',
`ExamDate` DATETIME NOT NULL COMMENT'考试日期',
`StudentResult`INT(4) NOT NULL COMMENT'考试成绩',
KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
/*学生表*/
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`StudentNo` INT(4) NOT NULL COMMENT'学号',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT'学生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT'性别,取0或者1',
`GradeID` INT(11) DEFAULT NULL COMMENT'年级编号',
`Phone` VARCHAR(50) NOT NULL COMMENT'联系电话,允许为空 可选输入',
`Address` VARCHAR(255) NOT NULL COMMENT'地址 允许为空 可选输入',
`BornDate` DATETIME DEFAULT NULL COMMENT'出生日期',
`Emile` VARCHAR(50) NOT NULL COMMENT'邮箱账号 允许为空 可选输入',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT'身份证号',
PRIMARY KEY(`StudentNo`),
/*唯一索引*/
UNIQUE KEY `IdentityCard`(`IdentityCard`),
KEY `Emile` (`Emile`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Address`,`BornDate`,`Emile`,`IdentityCard`)
VALUES('113','124','王五','0',3,'333','高新','2020-8-20','@126.com','333333333333333333');
`subject`
/*课程表*/
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT'课程编号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT'课程名称',
`ClassHour` INT(4) DEFAULT NULL COMMENT'学时',
`GradeId` INT(4) DEFAULT NULL COMMENT'年级编号',
PRIMARY KEY(`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
排序
--排序 :升序ASC 降序DESC
SELECT stu.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `result` AS r
LEFT JOIN `subject` AS s
ON r.`SubjectNo`=s.`SubjectNo` --左连接返回result表中匹配的所有数据
LEFT JOIN `student` AS stu
ON r.`StudentNo`=stu.`StudentNo`
WHERE r.`StudentNo`=stu.`StudentNo`
ORDER BY `StudentResult` DESC --降序排列
分页
为什么要分页?
- 缓解数据库压力
- 提高用户体验
--分页 每页显示5条
--语法 LIMIT 起始值 页面的大小
--网页显示 总的页数 页面的大小
SELECT stu.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `result` AS r
LEFT JOIN `subject` AS s
ON r.`SubjectNo`=s.`SubjectNo`
LEFT JOIN `student` AS stu
ON r.`StudentNo`=stu.`StudentNo`
WHERE r.`StudentNo`=stu.`StudentNo`
ORDER BY `StudentResult` DESC
LIMIT 0,5
--第一页 LIMIT 0,5
--第二页 LIMIT 5,5
--第n页 LIMIT (n-1)*pagesize,n*pagesize
--[pagesize 页面大小,n 当前页 总页数=总数据数/页面大小]
查询成绩排名前十,课程编号不等以21的学生
SELECT stu.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `result` AS r
LEFT JOIN `subject` AS s
ON r.`SubjectNo`=s.`SubjectNo`
LEFT JOIN `student` AS stu
ON r.`StudentNo`=stu.`StudentNo`
WHERE r.`SubjectNo`<>21
ORDER BY `StudentResult` DESC
LIMIT 0,10
语法:limit(起始值,页面大小)