数据库面试----学生、老师、课程、选课表常用sql示例
请先看看六大范式详解
1——建表
(1)学生表 student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(20) DEFAULT NULL,
`sage` int(11) DEFAULT NULL,
`ssex` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');
(2)老师表 teacher
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '贺高');
INSERT INTO `teacher` VALUES ('3', '杨艳');
INSERT INTO `teacher` VALUES ('4', '周磊');
(3)课程表 course
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) DEFAULT NULL,
`cname` varchar(255) DEFAULT NULL,
`tid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');
INSERT INTO `course` VALUES ('5', '物理', '3');
(4)选课表 sc
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) DEFAULT NULL,
`cid` int(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '60');
INSERT INTO `sc` VALUES ('1', '2', '78');
INSERT INTO `sc` VALUES ('1', '3', '67');
INSERT INTO `sc` VALUES ('1', '4', '58');
INSERT INTO `sc` VALUES ('2', '1', '79');
INSERT INTO `sc` VALUES ('2', '2', '81');
INSERT INTO `sc` VALUES ('2', '3', '92');
INSERT INTO `sc` VALUES ('2', '4', '68');
INSERT INTO `sc` VALUES ('3', '1', '91');
INSERT INTO `sc` VALUES ('3', '2', '47');
INSERT INTO `sc` VALUES ('3', '3', '88');
INSERT INTO `sc` VALUES ('3', '4', '56');
INSERT INTO `sc` VALUES ('4', '2', '88');
INSERT INTO `sc` VALUES ('4', '3', '90');
INSERT INTO `sc` VALUES ('4', '4', '93');
INSERT INTO `sc` VALUES ('5', '1', '46');
INSERT INTO `sc` VALUES ('5', '3', '78');
INSERT INTO `sc` VALUES ('5', '4', '53');
INSERT INTO `sc` VALUES ('6', '1', '35');
INSERT INTO `sc` VALUES ('6', '2', '68');
INSERT INTO `sc` VALUES ('6', '4', '71');
2——sql示例
1 查询所有同学的学号、姓名、选课数、总成绩
SELECT a.sid,a.sname,b.num,b.total FROM student a,
(SELECT sid,COUNT(1) num,SUM(score) total FROM sc GROUP BY sid) b
WHERE a.sid = b.sid;
or
SELECT a.sid,a.sname,COUNT(sc.cid),SUM(score) FROM student a LEFT JOIN sc ON a.sid = sc.sid GROUP BY a.sid,a.sname
2 查询没学过“叶平”老师课的同学的学号、姓名;
SELECT student.sid,student.sname FROM student WHERE student.sid not in
(SELECT sid FROM sc,course,teacher WHERE sc.cid = course.cid and course.tid = teacher.tid and teacher.tname ='叶平');
3 查询学过编号“1”并且也学过编号“2”课程的同学的学号、姓名;
SELECT student.sid,student.sname FROM sc,student WHERE sc.sid = student.sid and sc.cid = '1' AND EXISTS
(SELECT * FROM sc sc2 WHERE sc2.sid = sc.sid and sc2.cid = '2')
or
SELECT s.sid,s.sname FROM student s,sc a,sc b WHERE a.sid = b.sid AND a.sid = s.sid AND a.cid = '1' and b.cid = '2'
4 查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT sid,sname FROM student WHERE sid in (
SELECT sc.sid FROM teacher t,course c,sc WHERE t.tid = c.tid AND c.cid = sc.cid AND t.tname = '叶平'
GROUP BY sc.sid HAVING COUNT(sc.cid) = (SELECT COUNT(1) FROM course c,teacher t WHERE c.tid = t.tid AND t.tname = '叶平')
)
5 查询没有学全所有课的同学的学号、姓名;
SELECT sid,sname FROM student WHERE sid in (
SELECT sc.sid FROM sc GROUP BY sc.sid HAVING COUNT(1)<(SELECT count(1) from course)
)
or
SELECT sc.sid,sname FROM sc,student WHERE sc.sid = student.sid GROUP BY sc.sid,sname
HAVING COUNT(1)<(SELECT count(1) from course)
6 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
SELECT sid,sname from student WHERE sid in (
SELECT sc.sid FROM sc WHERE sc.sid !='1' AND sc.cid in (
SELECT sc.cid FROM sc WHERE sc.sid = '1')
)
or
SELECT distinct s.sid,s.sname from student s,sc WHERE s.sid = sc.sid AND s.sid != '1'
AND sc.cid in (SELECT cid FROM sc WHERE sc.sid = '1')
7 查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT sid,sname from student WHERE sid in (
SELECT sid from (
SELECT * from sc WHERE sc.sid != '1' AND EXISTS
(SELECT * FROM sc sc2 WHERE sc.cid = sc2.cid AND sc2.sid = '1')
) a GROUP BY sid HAVING COUNT(1)=(SELECT count(1) FROM sc WHERE sid = '1' )
)