Mysql学习---SQL测试题之表结构

创建表结果和数据准备[直接执行即可]

 /*

 Navicat MySQL Data Transfer

 Source Server         : ftl1012

 Source Server Version : 50617

 Source Host           : localhost:3306

 Source Database       : test_python

 Target Server Type    : MYSQL

 Target Server Version : 50617

 File Encoding         : 65001

 Date: 2017-12-30 13:12:57

 */

 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

 -- ----------------------------

 INSERT INTO `class` VALUES ('', '三年二班');

 INSERT INTO `class` VALUES ('', '三年三班');

 INSERT INTO `class` VALUES ('', '一年二班');

 INSERT INTO `class` VALUES ('', '二年九班');

 -- ----------------------------

 -- 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

 -- ----------------------------

 INSERT INTO `course` VALUES ('', '生物', '');

 INSERT INTO `course` VALUES ('', '物理', '');

 INSERT INTO `course` VALUES ('', '体育', '');

 INSERT INTO `course` VALUES ('', '美术', '');

 -- ----------------------------

 -- 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

 -- ----------------------------

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 INSERT INTO `score` VALUES ('', '', '', '');

 -- ----------------------------

 -- 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

 -- ----------------------------

 INSERT INTO `student` VALUES ('', '男', '', '理解');

 INSERT INTO `student` VALUES ('', '女', '', '钢蛋');

 INSERT INTO `student` VALUES ('', '男', '', '张三');

 INSERT INTO `student` VALUES ('', '男', '', '张一');

 INSERT INTO `student` VALUES ('', '女', '', '张二');

 INSERT INTO `student` VALUES ('', '男', '', '张四');

 INSERT INTO `student` VALUES ('', '女', '', '铁锤');

 INSERT INTO `student` VALUES ('', '男', '', '李三');

 INSERT INTO `student` VALUES ('', '男', '', '李一');

 INSERT INTO `student` VALUES ('', '女', '', '李二');

 INSERT INTO `student` VALUES ('', '男', '', '李四');

 INSERT INTO `student` VALUES ('', '女', '', '如花');

 INSERT INTO `student` VALUES ('', '男', '', '刘三');

 INSERT INTO `student` VALUES ('', '男', '', '刘一');

 INSERT INTO `student` VALUES ('', '女', '', '刘二');

 INSERT INTO `student` VALUES ('', '男', '', '刘四');

 -- ----------------------------

 -- 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

 -- ----------------------------

 INSERT INTO `teacher` VALUES ('', '张磊老师');

 INSERT INTO `teacher` VALUES ('', '李平老师');

 INSERT INTO `teacher` VALUES ('', '刘海燕老师');

 INSERT INTO `teacher` VALUES ('', '朱云海老师');

 INSERT INTO `teacher` VALUES ('', '李杰老师');

经验： join, 临时表的学习[in 的使用，右边只能输出一个]

-- 查询平均成绩大于60分的同学的学号和平均成绩,按照平均成绩降序排序操作score张表关联student查姓名

方案一[FTL]：

select * from (select student_id, avg(num) from score group by student_id having avg(num) > 60) as T left join student on student.sid = T.student_id;

方案二：

select score.student_id, student.sname, avg(num),max(num),min(num) from score LEFT JOIN student on student.sid = score.student_id

GROUP BY score.student_id having avg(num) > 60 order by avg(num) desc;

Mysql学习---SQL测试题之表结构

-- 查询所有同学的学号、姓名、选课数、总成绩；

SELECT  a.sid, a.sname,

SUM(s.num) as zongchengji, count(s.course_id) as xuankeshu from score s

LEFT JOIN student a on a.sid = s.student_id  GROUP BY s.student_id

Mysql学习---SQL测试题之表结构

-- 查询姓“李”的老师的个数；

方案一[FTL]：select count(tname) from teacher where tname like '李%';

方案二：select count(1) from teacher where tname like '李%';

Mysql学习---SQL测试题之表结构

-- 查询没学过“李平”老师课的同学的学号、姓名；

方案一[FTL]：局限性：不能查其他表中的内容

   -- 没学过  ==> not int ==> sid not in 成绩表

   -- 利用成绩表查找课程信息 ==>关联老师的信息

   -- 李平老师  ==> where tname like '李平'

select student.sid, student.sname from student where sid not in

(

   select student_id from score LEFT JOIN

    course on score.course_id = course.cid

   LEFT JOIN teacher on teacher.tid = course.teacher_id

  where tname like '李平%'

);

方案二：

思路：

    先查到“李平老师”老师教的所有课ID

    获取选过课的所有学生ID

    学生表中筛选

select * from student where sid not in (

    select DISTINCT student_id from score where score.course_id in (

        select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'

    )

)

Mysql学习---SQL测试题之表结构

-- 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

方案一[FTL]：

   -- 选出课程为1或者2的课 ==> 关联student表查询

SELECT sid,sname from student where sid in

(select DISTINCT student_id from score where score.course_id in

(select cid from course where cid in (1,2)))

方案二：

SELECT sid, sname from

(SELECT student_id, count(student_id) FROM

(SELECT student_id, course_id FROM score WHERE course_id = 1 OR course_id = 2) AS B

GROUP BY student_id HAVING count(student_id) > 1

) as C LEFT JOIN student on student.sid = C.student_id;

方案三：

select student_id,sname from

 (select student_id,course_id from score where course_id = 1 or course_id = 2) as B left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1;

Mysql学习---SQL测试题之表结构

-- 查询没有学全所有课的同学的学号、姓名；

-- 查询没有学全所有课的同学的学号、姓名；

   -- 所有课  ==> count(1) from course

   -- 同学的学号、姓名 ==> sid, sname from student

   -- 没有学全所有课   ==> count(course_id)

方案二:

SELECT student.sid, sname from student LEFT JOIN score on score.student_id = student.sid

group by student_id HAVING count(course_id) = (select count(1) from course)

Mysql学习---SQL测试题之表结构

-- 查询学过“李平老师“所教的所有课的同学的学号、姓名；

-- 查询学过“李平老师“所教的所有课的同学的学号、姓名；

-- 找到李平老师教过的课程ID

方案一[FTL]:

SELECT sid, sname FROM student where sid in (SELECT student_id FROM score WHERE course_id IN (SELECT cid FROM course LEFT JOIN teacher ON teacher_id = tid WHERE teacher.tname LIKE '%李平%' ))

方案二:

select sid, sname from (select student_id from score where course_id in (select cid from course left join teacher on course.teacher_id = teacher.tid  where teacher.tname='李平老师') group by student_id)

as B left JOIN student on student.sid = B.student_id

Mysql学习---SQL测试题之表结构

-- 查询所有课程成绩小于60分的同学的学号、姓名；

-- 查询所有课程成绩小于60分的同学的学号、姓名；

方案一[FTL]:

select * from (

select student_id, num from score LEFT JOIN student on student.sid = score.student_id  where num < 60 ORDER BY num asc ) as B LEFT JOIN student on B.student_id = student.sid

Mysql学习---SQL测试题之表结构

-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

方案一[FTL_有错误，未解决]

select student.sid, sname from

(SELECT * from score where course_id in (

	select course_id from score where student_id = 1

)) as B LEFT JOIN student on B.sid = student.sid

方案二：

   思路：

        获取 001 同学选择的所有课程

        获取课程在其中的所有人以及所有课程

        根据学生筛选，获取所有学生信息

        再与学生表连接，获取姓名

select student_id, sname, count(course_id) from score

LEFT JOIN student on student.sid = score.student_id where student_id != 1 and course_id in (

select course_id from score where course_id = 1) GROUP BY student_id

Mysql学习---SQL测试题之表结构

-- 查询至少学过学号为“001”同学所有课数目相同的其他同学学号和姓名

-- 查询至少学过学号为“001”同学所有课数目相同的其他同学学号和姓名

  -- 001 所学习的课程

  -- 其他人的课 >= 001里面的课

方案一[FTL]：

select sid, sname from

(select student_id, course_id from score where course_id in

(select course_id from score where student_id = 1) group by student_id

HAVING count(1) = (select count(1) from score WHERE student_id = 1) ) as B

LEFT JOIN student on student.sid = B.student_id GROUP BY student_id

Mysql学习---SQL测试题之表结构

-- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；【高难度】

-- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；【高难度】

   -- 002号学习的课程

   -- count(1) == 002的课程count(1)

   -- left JOIN student 拼接姓名和学号

-- 数量相同学生ID

select student_id, course_id, count(1) from score GROUP BY student_id having count(1) =

(select count(1) from score where student_id = 2 )

-- 课程相同

select student_id, course_id from score where course_id in

(select DISTINCT course_id from score where student_id = 2) GROUP BY student_id

-- 综合完成

 select student_id,sname from score left join student on score.student_id = student.sid where student_id in (select student_id from score  where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)

Mysql学习---SQL测试题之表结构

-- 删除学习“叶平”老师课的score表记录；

delete from score where course_id in (

select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '叶平')

[更多参考] http://www.cnblogs.com/wupeiqi/articles/5729934.html

[更多参考] http://www.cnblogs.com/wupeiqi/articles/5748496.html

秒客网

Mysql学习---SQL测试题之表结构

相关文章