自己练习sql 和分析。
545D960C-2BC5-4877-B979-5EADEDCE235E.png
表结构和数据
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
表结构和数据
1.查询课程成绩大于60
SELECT * FROM score WHERE num > 60
2.查询每个老师任的几门课程
SELECT teacher_id , COUNT(1) FROM course GROUP BY teacher_id
3.查询老师任课程和且要显示老师名称
SELECT co.cid, co.cname , co.teacher_id, te.tname FROM course co LEFT JOIN teacher te on co.teacher_id = te.tid
4.查询每个学生对应的班级信息
SELECT st.sid , st.gender,st.sname,cl.cid,cl.caption FROM student st LEFT JOIN class cl on st.class_id = cl.cid
5.查询学生表里的男女个数是多少啊？
SELECT gender, COUNT(sid) FROM student GROUP BY gender
6.查询平均成绩大于60分的同学的学号和平均成绩
SELECT sc.student_id , AVG(num) as avg_num FROM score sc GROUP BY sc.student_id HAVING avg_num > 60
7.查询平均成绩大于60分的同学的信息和平均成绩
SELECT st.sid ,st.sname , st.gender, b.avg_num FROM student st RIGHT JOIN
(SELECT sc.student_id , AVG(num) as avg_num FROM score sc GROUP BY sc.student_id HAVING avg_num > 60) B
ON st.sid = b.student_id
8.查询所有同学的学号、姓名、选课数、总成绩
SELECT st.sid, st.sname, COUNT(sc.student_id) , SUM(sc.num) FROM student st
LEFT JOIN score sc on st.sid = sc.student_id GROUP BY st.sid
9.查询姓“李”的老师的个数
SELECT COUNT(tid) FROM teacher WHERE tname like '李%'
10.查询没学过“李平老师”老师课的同学的学号、姓名
SELECT tid FROM teacher WHERE tname = '李平老师'
-- 李平老师所任课的课程ID
SELECT cid FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
-- 学过李平老师 的学生
SELECT sc.student_id FROM score sc WHERE sc.course_id IN(
SELECT cid FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
) GROUP BY sc.student_id
-- 再找没有学过李平老师的学生
SELECT st.sid ,st.sname FROM student st WHERE st.sid not in (
SELECT sc.student_id FROM score sc WHERE sc.course_id IN(
SELECT cid FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
) GROUP BY sc.student_id
)
11.查询“生物”课程比“物理”课程成绩高的所有学生的学号
-- 生物
SELECT score.student_id,score.course_id,score.num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '生物'
-- 物理
SELECT score.student_id,score.course_id,score.num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '物理'
SELECT B.student_id FROM
(SELECT score.student_id,score.course_id,score.num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '生物') A
INNER JOIN
(SELECT score.student_id,score.course_id,score.num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '物理') B
ON A.student_id = B.student_id
WHERE A.num > B.num
11.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECT st.sid, st.sname FROM student st LEFT JOIN
score sc
ON st.sid = sc.student_id
WHERE sc.course_id = '1' or sc.course_id = '2' GROUP BY student_id
12.查询学过“李平老师”所教的所有课的同学的学号、姓名
-- 李平老师 所教的课程ID
SELECT cid FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师'
-- 上过李平老师所有的课 的同学ID
SELECT sc.student_id FROM score sc WHERE sc.course_id IN (
SELECT cid FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师'
) GROUP BY sc.student_id HAVING COUNT(sc.course_id) =(SELECT COUNT(cid) FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师')
-- 最后连表显示同学姓名和学号
SELECT st.sid ,st.sname FROM student st,
(SELECT sc.student_id FROM score sc WHERE sc.course_id IN(SELECT co.cid FROM course co LEFT JOIN teacher ON teacher.tid = co.teacher_id WHERE teacher.tname = '李平老师')
GROUP BY sc.student_id
HAVING COUNT(sc.course_id) =(SELECT COUNT(cou.cid) FROM course cou LEFT JOIN teacher ON teacher.tid = cou.teacher_id WHERE teacher.tname = '李平老师')) B
WHERE st.sid = B.student_id
13.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
（SELECT sc1.student_id,sc1.num FROM score sc1 WHERE sc1.course_id = '1'A,
(SELECT sc2.student_id,sc2.num FROM score sc2 WHERE sc2.course_id = '2')B
SELECT st.sid , st.sname FROM student st,
(SELECT sc1.student_id,sc1.num FROM score sc1 WHERE sc1.course_id = '1')A,
(SELECT sc2.student_id,sc2.num FROM score sc2 WHERE sc2.course_id = '2')B
WHERE st.sid = A.student_id and st.sid = B.student_id AND A.num > B.num
- 查询有课程成绩小于60分的同学的学号、姓名
SELECT * FROM score sc WHERE sc.num < 60
SELECT st.sid ,st.sname FROM student st WHERE st.sid IN(
SELECT sc.student_id FROM score sc WHERE sc.num < 60 GROUP BY sc.student_id
)
15.查询没有学全所有课的同学的学号、姓名
SELECT st.sid ,st.sname FROM student st ,
(SELECT sc.student_id , COUNT(1) FROM score sc GROUP BY sc.student_id HAVING COUNT(1) < (SELECT COUNT(1) FROM course)) A
WHERE st.sid = A.student_id
16.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
SELECT st.sid ,st.sname FROM student st,
(SELECT sc.student_id FROM score sc WHERE sc.student_id != '1' AND sc.course_id IN(
SELECT cc.course_id FROM score cc WHERE cc.student_id = '1'
) GROUP BY sc.student_id) B
WHERE st.sid = B.student_id
17.查询至少学过学号为“001”同学所有课的其他同学学号和姓名
SELECT st.sid ,st.sname FROM student st,
(SELECT sc.student_id FROM score sc WHERE sc.student_id != '1' AND sc.course_id IN(
SELECT cc.course_id FROM score cc WHERE cc.student_id = '1'
) GROUP BY sc.student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id = '1' ) ) B
WHERE st.sid = B.student_id
18.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT st.sid ,st.sname FROM student st,
( SELECT score.student_id FROM score WHERE score.student_id IN (
SELECT scc.student_id FROM score scc WHERE scc.student_id != '02' GROUP BY scc.student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score sc WHERE sc.student_id = '02')
)
AND score.course_id IN ( SELECT sc.course_id FROM score sc WHERE sc.student_id = '02')
GROUP BY score.student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM score WHERE score.student_id = '02')) B
WHERE st.sid = B.student_id
19.删除学习“李平老师”老师课的score表记录
SELECT cid FROM course WHERE course.teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
DELETE FROM score WHERE cid IN(
SELECT cid FROM course WHERE course.teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
)
20.向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩
insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
from student where sid not in (
select student_id from score where course_id = 2
)
21.按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分
SELECT
sc.student_id,
(SELECT num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '生物' AND score.student_id = sc.student_id) as '生物',
(SELECT num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '物理' AND score.student_id = sc.student_id) as '物理',
(SELECT num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE course.cname = '体育' AND score.student_id = sc.student_id) as '体育',
COUNT(sc.course_id) AS '有效课程数',
AVG(sc.num) AS '有效平均分'
FROM score sc GROUP BY sc.student_id ORDER BY AVG(num) ASC
22.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT course_id ,MAX(num),MIN(num) FROM score GROUP BY score.course_id
23.按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT course_id,AVG(num) ,SUM(CASE WHEN num > 60 THEN 1 ELSE 0 END)/SUM(1)*100 as percent FROM score GROUP BY course_id ORDER BY percent DESC
24.课程平均分从高到低显示（现实任课老师）
SELECT score.course_id,AVG(IF(isnull(score.num),0,score.num)) as score_avg,course.cname, teacher.tname FROM score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN teacher ON teacher.tid = course.teacher_id
GROUP BY score.course_id
25、***查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT score.sid , score.course_id ,score.num ,B.NO1,B.NO2 FROM score
LEFT JOIN
(SELECT
sc.sid,
(SELECT num FROM score sc1 WHERE sc1.course_id = sc.course_id ORDER BY num DESC LIMIT 0,1) as NO1,
(SELECT num FROM score sc1 WHERE sc1.course_id = sc.course_id ORDER BY num DESC LIMIT 3,1)as NO2
FROM score sc ) B
ON score.sid = B.sid
WHERE score.num <= B.NO1 and score.num >= B.NO2
26.查询每门课程被选修的学生数
SELECT score.course_id ,COUNT(score.student_id) FROM score GROUP BY score.course_id
27.查询出只选修了一门课程的全部学生的学号和姓名
SELECT student.sid ,student.sname FROM student,
(SELECT score.student_id FROM score GROUP BY score.student_id HAVING COUNT(score.course_id) =1) B
WHERE student.sid = B.student_id
28.查询男生、女生的人数
SELECT * FROM
(SELECT COUNT(1)as man FROM student WHERE student.gender ='男') A,
(SELECT COUNT(1)as wemen FROM student WHERE student.gender ='女') B
29.查询姓“张”的学生名单
SELECT * FROM student WHERE student.sname LIKE '张%'
30.查询同名同姓学生名单，并统计同名人数
SELECT student.sname ,COUNT(student.sname) FROM student GROUP BY student.sname
31.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT score.course_id ,AVG(if(isnull(num),0 ,num)) as avg_score FROM score GROUP BY score.course_id ORDER BY avg_score ASC , score.course_id DESC
32.查询平均成绩大于65的所有学生的学号、姓名和平均成绩
SELECT student.sid,student.sname , B.avg_csore FROM student,
(SELECT score.student_id, AVG(num) as avg_csore FROM score GROUP BY score.student_id HAVING avg_csore > 65) B
WHERE student.sid = B.student_id
33.查询课程名称为“物理”，且分数低于60的学生姓名和分数
SELECT student.sname,score.num FROM score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE course.cname = '物理' AND score.num < 60
34.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
SELECT student.sid,student.sname , score.num FROM student
LEFT JOIN score ON score.student_id = student.sid
WHERE score.num > 65
35.求选了课程的学生人数
第一种
SELECT COUNT(DISTINCT student_id) FROM score
第二种
SELECT COUNT(1) FROM
(SELECT score.student_id FROM score GROUP BY score.student_id) B
36.查询选修“李平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT student.sname ,score.num FROM score
LEFT JOIN student ON student.sid = score.student_id
WHERE score.course_id IN(
SELECT cid FROM course
LEFT JOIN teacher ON teacher.tid = course.teacher_id
WHERE teacher.tname = '李平老师'
)
ORDER BY score.num DESC LIMIT 1
37.查询各个课程及相应的选修人数
SELECT course.cname,COUNT(1) FROM course
LEFT JOIN score ON score.course_id = course.cid
GROUP BY course.cid
- **查询不同课程但成绩相同的学生的学号、课程号、学生成绩
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;
39.检索至少选修两门课程的学生学号
select student_id from score group by student_id having count(student_id) > 1
40.查询全部学生都选修的课程的课程号和课程名
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);
41.查询没学过“李平”老师讲授的任一门课程的学生姓名
select student_id,student.sname from score
left join student on score.student_id = student.sid
where score.course_id not in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
)
group by student_id
42.查询两门以上不及格课程的同学的学号及其平均成绩
select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2
43.检索“2”课程分数小于60，按分数降序排列的同学学号
select student_id from score where num< 60 and course_id = 2 order by num desc;
44.删除“2”同学的“1”课程的成绩
delete from score where course_id = 1 and student_id = 2
网友评论