Mysql练习_mysql练习题
一、數據準備
/*
數據導入：
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、查詢所有的課程的名稱以及對應的任課老師姓名
--
-- 2、查詢學生表中男女生各有多少人
--
-- 3、查詢物理成績等于100的學生的姓名
--
-- 4、查詢平均成績大于八十分的同學的姓名和平均成績
--
-- 5、查詢所有學生的學號，姓名，選課數，總成績
--
-- 6、 查詢姓李老師的個數
--
-- 7、 查詢沒有報李平老師課的學生姓名
--
-- 8、 查詢物理課程比生物課程高的學生的學號
--
-- 9、 查詢沒有同時選修物理課程和體育課程的學生姓名
-- 11、查詢選修了所有課程的學生姓名
-- 10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
--
-- 12、查詢李平老師教的課程的所有成績記錄
--
-- 13、查詢全部學生都選修了的課程號和課程名
--
-- 14、查詢每門課程被選修的次數
--
-- 15、查詢之選修了一門課程的學生姓名和學號
--
-- 16、查詢所有學生考出的成績并按從高到低排序(成績去重)
--
-- 17、查詢平均成績大于85的學生姓名和平均成績
--
-- 18、查詢生物成績不及格的學生姓名和對應生物分數
--
-- 19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
--
-- 20、查詢每門課程成績最好的前兩名學生姓名
--
-- 21、查詢不同課程但成績相同的學號，課程號，成績
--
-- 22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；
--
-- 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
--
-- 24、任課最多的老師中學生單科成績最高的學生姓名
三、不唯一答案
-- 1、查詢所有的課程的名稱以及對應的任課老師姓名
select cname,tname from course inner join teacher on course.teacher_id = teacher.tid
-- 2、查詢學生表中男女生各有多少人
select gender,count(gender) from student group by gender
-- 3、查詢物理成績等于100的學生的姓名
-- 第四步：與學生表拼接，拿到學生姓名
select student.sid,gender,sname,num from student inner join(
select * from (
-- 第一步：查詢分數為100的表
select * from score where num = 100 )as t1
-- 第三步：條件篩選出物理分數100的表
where t1.course_id = (
-- 第二步：查詢物理的id
select cid from course where cname = '物理')
# 拼接
) as t2 on t2.student_id = student.sid
-- 4、查詢平均成績大于八十分的同學的姓名和平均成績
-- 第二步：拼表
select sname, num from student inner join (
-- 第一步：以學生id為分組，篩選分數表中的成績
select student_id,avg(num)as 'num' from score group by student_id having avg(num)>80
)as t1 on student.sid = t1.student_id
-- 5、查詢所有學生的學號，姓名，選課數，總成績
-- 第三步：拼表篩選
select t1.student_id,t2.sname,t1.數量,t1.總成績 from (
-- 第二部篩選分數表，并計數
select student_id,count(num)as '數量',sum(num)as '總成績' from score group by student_id)as t1
inner join (
-- 第一步：篩選學生表
select sid,sname from student)as t2 on t1.student_id = t2.sid
-- 6、 查詢姓李老師的個數
select count(tname)as '姓李的數量' from teacher where tname like '李%'
-- 7、 查詢沒有報李平老師課的學生姓名
-- 第四步：根據學生id取反拿到沒有選擇李平老師課程的學生
select sname from student where sid not in (
-- 第三步：拿到選擇李平老師課程的學生id
select distinct student_id from score where course_id in (
-- 第二步：根據老師id拿到老師教的課程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老師的id
select tid from teacher where tname = '李平老師')))
-- 8、 查詢物理課程比生物課程高的學生的學號
-- 第五步：拼接兩張表
select * from (
-- 第二步：查詢物理的所有成績
select student_id as '學生物理id',num as '物理成績' from score where course_id = (
-- 第一步：查詢物理的id
select cid from course where cname = '物理' ))as t1
inner join (
-- 第四步：查詢生物的所有成績
select student_id as '學生生物id',num as '生物成績' from score where course_id = (
-- 第三步：查詢生物的id
select cid from course where cname = '生物' ))as t2
on t1.學生物理id = t2.學生生物id
-- 第六步：篩選物理大于生物
where 物理成績 > 生物成績
-- 9、 查詢沒有同時選修物理課程和體育課程的學生姓名
-- 第四步：根據拿到的學號從學生表中獲取想要的數據
select sid,sname from student where sid in (
-- 第三步：以學號為組，計數為1，意思是物理體育只有一門，篩選得到學號
select t1.student_id from (
-- 第一步，簡單粗暴，直接把相關的表拼起來
select * from score inner join course on score.course_id = course.cid
-- 第二步，篩選課程
where cname in ('物理','體育')
)as t1
group by t1.student_id
having count(student_id)=1
)
-- 10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
-- 第四步：和班級表拼接，拿到想要的字段
select caption,sname,掛科數 from class inner join (
-- 第三步：與學生表拼接，拿出想要的字段
select * from student inner join (
-- 第一步：篩選出小于60分的所有成績名單
select student_id,count(student_id)as'掛科數' from score where num < 60
-- 第二步: 以學生為分組，查看這個學生60分以下出現了多少次，就是多少門不及格
group by student_id having count(student_id)>=1
-- 拼接
)as t1 on student.sid = t1.student_id
) as t2 on class.cid = t2.class_id
-- 11、查詢選修了所有課程的學生姓名
-- 第四步：和學生表拼接，拿到想要的字段
select sname,課程數 from student inner join (
-- 第二步：以學生為分組，計算所有學生選擇的課程數
select student_id,count(student_id)as'課程數' from score group by student_id
-- 第三步：篩選學生選擇的課程數=課程總數
having count(student_id) = (
-- 第一步：拿到所有課程的id
select count(cid)as'總課程數' from course )
-- 拼接
)as t1 on student.sid = t1.student_id
-- 12、查詢李平老師教的課程的所有成績記錄
-- 第三步：在與分數表拼接，拿到想要的字段信息
select tname,student_id,cname,num from score inner join (
-- 第二步：課程表和李平老師信息表拼接
select * from course inner join (
-- 第一步：拿到李平老師的信息
select * from teacher where tname = '李平老師'
-- 拼接
)as t1 on course.teacher_id = t1.tid
-- 拼接
)as t2 on score.course_id = t2.cid
-- 13、查詢全部學生都選修了的課程號和課程名
-- 第二步：在和課程表拼接起來，選擇想要的字段
select student_id,sname,gender,cid,cname,num from course inner join (
-- 第一步：把分數表和學生表拼起來，選擇想要的字段
select score.student_id,student.gender,student.sname,score.course_id,score.num from score inner join student on score.student_id = student.sid
-- 拼接
)as t1 on course.cid = t1.course_id
-- 14、查詢每門課程被選修的次數
第二步：篩選過的表與課程表拼接，獲取課程名
select cid,cname, 被選次數 from course inner join (
-- 第一步：以課程id為組，查詢被選的次數
select course_id,count(course_id)as'被選次數' from score group by course_id
) as t1 on course.cid = t1.course_id
-- 15、查詢只選修了一門課程的學生姓名和學號
-- 第二步：拼接學生表，獲取信息
select sid,sname,選課門數 from student inner join(
-- 第一步：以學生為分組，查詢選課數為1的
select student_id,count(student_id)as '選課門數' from score group by student_id
having count(student_id) = 1
-- 拼接
) as t1 on student.sid = t1.student_id
-- 16、查詢所有學生考出的成績并按從高到低排序(成績去重)
-- 第二步：和學生表拼接，獲取想要的字段信息
select sid,sname,總成績 from student inner join (
-- 第一步：查詢所有學生的總成績
select student_id,sum(num)as '總成績' from score group by student_id
-- 拼接
)as t1 on student.sid = t1.student_id
-- 降序排名
order by 總成績 desc
-- 17、查詢平均成績大于85的學生姓名和平均成績
-- 第二步：和學生表拼接，獲取想要的字段
select sname,avg as '平均成績' from student inner join (
-- 第一步：以學生id為分組 查詢大于85分的信息
select student_id,avg(num)as 'avg' from score group by student_id having avg(num)>85
) as t1 on student.sid = t1.student_id
-- 18、查詢生物成績不及格的學生姓名和對應生物分數
-- 第四步：拼接學生表獲取想要的信息
select sname,num as '不及格生物分數' from student inner join (
-- 第二步：查詢所有的生物成績
select * from score where course_id = (
-- 第一步：查詢生物的id
select cid from course where cname = '生物')
-- 第三步：篩選分數不及格的
and num<60
)as t1 on student.sid = t1.student_id
-- 19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
select sname,平均值 from student inner join (
-- 第五步：根據所有學生的平均成績選出最大成績，排序，取第一名
select t2.student_id,平均值 from (
-- 第四步：求出李平老師所有學生的平均成績
select t1.student_id ,avg(num)as'平均值' from (
-- 第三步：拿到選擇李平老師課程的學生id
select * from score where course_id in (
-- 第二步：根據老師id拿到老師教的課程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老師的id
select tid from teacher where tname = '李平老師'))
-- 接第四步
)as t1 group by t1.student_id
-- 接第五步
)as t2 order by 平均值 desc limit 1
)as t3 on student.sid = t3.student_id;
-- 20、查詢每門課程成績最好的前兩名學生姓名
-- 錯誤思路：按照課程id分組，排序，去前兩名。分組之后，組里面的數據不能取出來
-- 正確思路：把每一課的成績分別拎出來，分別取前兩名。最后合并表
# 最最后：和學生表拼接，拿到學生姓名
select student.sid,student.sname,t1.num from student inner join (
# 最后一步，用union all,不去重連接所有表
# 生物前兩名
-- 第二步：查看生物的所有成績
(select * from score where course_id in (
-- 第一步：獲取所有生物的id號
select cid from course where cname= '生物'
)
-- 第三步：排序,并只顯示前兩條
order by num desc limit 2)
union all
# 物理前兩名
-- 第二步：查看生物的所有成績
(select * from score where course_id in (
-- 第一步：獲取所有生物的id號
select cid from course where cname= '物理'
)
-- 第三步：排序,并只顯示前兩條
order by num desc limit 2)
union all
# 體育前兩名
-- 第二步：查看生物的所有成績
(select * from score where course_id in (
-- 第一步：獲取所有生物的id號
select cid from course where cname= '體育'
)
-- 第三步：排序,并只顯示前兩條
order by num desc limit 2)
union all
# 美術前兩名
-- 第二步：查看生物的所有成績
(select * from score where course_id in (
-- 第一步：獲取所有生物的id號
select cid from course where cname= '美術'
)
-- 第三步：排序,并只顯示前兩條
order by num desc limit 2)
) as t1 on student.sid = t1.student_id
-- 21、查詢不同課程但成績相同的學號，課程號，成績
-- 第二步：再次和分數表拼接，獲取學號
select student_id,course_id,t1.num,相同數量 from score inner join (
-- 第一步：以分數為分組，篩選出分數重復的
select num,count(num)as'相同數量' from score group by num having count(num) >=2
)as t1 on score.num = t1.num
-- 22、查詢沒學過“李平”老師課程的學生姓名以及選修的課程名稱；
-- 第四步：根據學生id取反拿到沒有選擇李平老師課程的學生
select sname from student where sid not in (
-- 第三步：拿到選擇李平老師課程的學生id
select distinct student_id from score where course_id in (
-- 第二步：根據老師id拿到老師教的課程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老師的id
select tid from teacher where tname = '李平老師')))
-- 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
# 這題考小學語文
-- 第三步：在學生表中篩選出學生
select sid,sname from student where sid in (
-- 第二步：查詢選修了學號為1的學生，選修過的課程的學生id
select student_id from score where course_id in (
-- 第一步：查詢學號為1的同學，選修的課程id
select course_id from score where student_id = 1
))
-- 24、任課最多的老師中學生單科成績最高的學生姓名
# 這題考小學語文
-- 實現思路：1.獲取授課老師最多的老師id
-- 2.查看該老師教授的課程id
-- 3.查詢教授課程對應的所有分數
-- (難點)4.從查詢出的分數表，選出最大值的分數
-- (難點,分數相同)5.拿到最大分數再到第3分數表中，查詢一致的分數對應的所有信息
-- 6.再和學生表拼接，拿到學生姓名
# 這是第3步篩選之后的表
select * from score where course_id in (
-- 第四步：查看該老師教授的課程id
select cid from course where teacher_id in(
-- 第三步：獲取任課數量最多的老師id
select t1.teacher_id from (
-- 第一步：在課程表當中，給選擇的課程數量計數
select teacher_id,count(teacher_id)as '數量' from course group by teacher_id
-- 第二步：排序
order by 數量 desc limit 1
)as t1
))
# 這是最大分數
select max(num) from score where course_id in (
-- 第四步：查看該老師教授的課程id
select cid from course where teacher_id in(
-- 第三步：獲取任課數量最多的老師id
select t3.teacher_id from (
-- 第一步：在課程表當中，給選擇的課程數量計數
select teacher_id,count(teacher_id)as '數量' from course group by teacher_id
-- 第二步：排序
order by 數量 desc limit 1
)as t3
))
# 最終答案
# 與學生表拼接
select student.sid,student.sname,t4.num from student inner join (
# 獲取最大分數對應的信息
select * from (
select * from score where course_id in (
-- 第四步：查看該老師教授的課程id
select cid from course where teacher_id in(
-- 第三步：獲取任課數量最多的老師id
select t1.teacher_id from (
-- 第一步：在課程表當中，給選擇的課程數量計數
select teacher_id,count(teacher_id)as '數量' from course group by teacher_id
-- 第二步：排序,獲取最大數量
order by 數量 desc limit 1
)as t1
))
)as t2 where t2.num = (
-- 第五步：查詢分數表，教授課程對應的所有分數
# 這里獲取最大值分數
select max(num) from score where course_id in (
-- 第四步：查看該老師教授的課程id
select cid from course where teacher_id in(
-- 第三步：獲取任課數量最多的老師id
select t3.teacher_id from (
-- 第一步：在課程表當中，給選擇的課程數量計數
select teacher_id,count(teacher_id)as '數量' from course group by teacher_id
-- 第二步：排序,獲取最大數量
order by 數量 desc limit 1
)as t3
)))
)as t4 on student.sid = t4.student_id
總結
以上是生活随笔為你收集整理的Mysql练习_mysql练习题的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mongodb 查询 mysql_Mon
- 下一篇： python3怎么使用pyrex_Win