#课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`CNO` char(5) NOT NULL,
`CNAME` varchar(10) NOT NULL,
`TNO` int(10) NOT NULL,
PRIMARY KEY (`CNO`)
) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for score
-- ----------------------------
#成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`SNO` varchar(3) NOT NULL,
`CNO` varchar(5) NOT NULL,
`DEGREE` decimal(10,1) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;
#学生表
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`SNO` int(10) NOT NULL,
`SNAME` varchar(4) NOT NULL,
`SSEX` tinyint(2) NOT NULL,
`SBIRTHDAY` datetime DEFAULT NULL,
`CLASS` varchar(5) DEFAULT NULL,
PRIMARY KEY (`SNO`)
) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;
#教师表
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`TNO` varchar(3) NOT NULL,
`TNAME` varchar(4) NOT NULL,
`TSEX` tinyint(2) NOT NULL,
`TBIRTHDAY` datetime NOT NULL,
`PROF` varchar(6) DEFAULT NULL,
`DEPART` varchar(10) NOT NULL,
PRIMARY KEY (`TNO`)
) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;
以课程号升序、成绩降序查询成绩表的所有记录。
SELECT * from score ORDER BY cno asc, degree DESC;
查询“95031”班的学生人数。
SELECT COUNT(CLASS) as 9501班 FROM student WHERE class="95033";
查询‘3-105’号课程的平均分
SELECT AVG(degree) FROM score WHERE CNO="3-105";
SELECT avg(degree) 平均分 from score GROUP BY cno HAVING cno = '3-105';
查询成绩表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT CNO,avg(degree) from score GROUP BY CNO HAVING COUNT(*)>5 and CNO like "3%";
查询最低分大于70，最高分小于90的学生编号 列
SELECT SNO from score GROUP BY SNO HAVING min(DEGREE)>70 and MAX(DEGREE)<90;
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT stu.* from student stu LEFT JOIN score sc ON sc.SNO=stu.SNO and cno="3-105" and sc.DEGREE>
(SELECT degree FROM score WHERE CNO="3-105" AND SNO=109 )
SELECT score.* from score INNER JOIN
(SELECT sno ,MAX(degree) as maxdegree from score GROUP BY sno HAVING COUNT(*) >1) temp
on score.sno = temp.sno and score.degree < temp.maxdegree;
查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列
SELECT sno,sname,sbirthday FROM student WHERE YEAR(SBIRTHDAY)=
(SELECT YEAR(SBIRTHDAY) from student WHERE SNO=107);
查询“张旭“教师任课的学生成绩
SELECT * from score WHERE cno in (SELECT cno from course WHERE tno = (SELECT tno from teacher WHERE tname ='张旭'));
查询出“计算机系“教师所教课程的成绩表。
SELECT * from score WHERE cno in (SELECT CNO from course WHERE TNO in (SELECT tno from teacher WHERE DEPART = '计算机系'));
查询“计算机系”与“电子工程系“不同职称的教师的名字和职称。
SELECT tname ,prof from teacher WHERE DEPART = '计算机系' and prof not in (SELECT prof from teacher WHERE DEPART = '电子工程系')
UNION
SELECT tname ,prof from teacher WHERE DEPART = '电子工程系' and prof not in (SELECT prof from teacher WHERE DEPART = '计算机系');
查询所有教师和同学的名字、性别和生日.
SELECT sname as name , ssex as sex , sbirthday as birthday from student
UNION ALL #万一老师和学生姓名 性别 出生日相同呢 所以加了ALL
SELECT tname , tsex , tbirthday as birthday from teacher;
