请选择 进入手机版 | 继续访问电脑版

mysql-基础sql练习

发表于 2016-08-07 13:12 显示全部楼层 17 512

表、数据准备

DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cno` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cname` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tno` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of t_course
-- ----------------------------
INSERT INTO `t_course` VALUES ('1', '3-105', '计算机导论', '825');
INSERT INTO `t_course` VALUES ('2', '3-245', '操作系统', '804');
INSERT INTO `t_course` VALUES ('3', '6-166', '数据电路', '856');
INSERT INTO `t_course` VALUES ('4', '9-888', '高等数学', '100');

-- ----------------------------
-- Table structure for `t_grade`
-- ----------------------------
DROP TABLE IF EXISTS `t_grade`;
CREATE TABLE `t_grade` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `low` int(11) NOT NULL,
  `upp` int(11) NOT NULL,
  `rank` char(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of t_grade
-- ----------------------------
INSERT INTO `t_grade` VALUES ('1', '90', '100', 'A');
INSERT INTO `t_grade` VALUES ('2', '80', '89', 'B');
INSERT INTO `t_grade` VALUES ('3', '70', '79', 'C');
INSERT INTO `t_grade` VALUES ('4', '60', '69', 'D');
INSERT INTO `t_grade` VALUES ('5', '0', '59', 'E');

-- ----------------------------
-- Table structure for `t_score`
-- ----------------------------
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sno` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cno` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `degree` decimal(10,1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES ('1', '101', '3-105', '64.0');
INSERT INTO `t_score` VALUES ('2', '103', '3-245', '86.0');
INSERT INTO `t_score` VALUES ('3', '105', '3-245', '75.0');
INSERT INTO `t_score` VALUES ('4', '107', '3-105', '91.0');
INSERT INTO `t_score` VALUES ('5', '108', '3-105', '78.0');
INSERT INTO `t_score` VALUES ('6', '109', '3-245', '68.0');
INSERT INTO `t_score` VALUES ('7', '103', '3-105', '92.0');
INSERT INTO `t_score` VALUES ('8', '105', '3-105', '88.0');
INSERT INTO `t_score` VALUES ('9', '109', '3-105', '76.0');
INSERT INTO `t_score` VALUES ('10', '101', '6-166', '85.0');
INSERT INTO `t_score` VALUES ('11', '107', '6-106', '79.0');
INSERT INTO `t_score` VALUES ('12', '108', '6-166', '81.0');

-- ----------------------------
-- Table structure for `t_student`
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sno` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sname` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ssex` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sbirthday` datetime DEFAULT NULL,
  `class` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('1', '101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `t_student` VALUES ('2', '103', '陆君', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `t_student` VALUES ('3', '105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `t_student` VALUES ('4', '107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `t_student` VALUES ('5', '108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `t_student` VALUES ('6', '109', '王芳', '女', '1975-02-10 00:00:00', '95031');

-- ----------------------------
-- Table structure for `t_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tno` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tname` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `TSEX` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tbirthday` datetime NOT NULL,
  `PROF` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `depart` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of t_teacher
-- ----------------------------
INSERT INTO `t_teacher` VALUES ('1', '804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `t_teacher` VALUES ('2', '825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `t_teacher` VALUES ('3', '831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `t_teacher` VALUES ('4', '856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');

练习及答案:

#1、 查询t_student表中的所有记录的Sname、Ssex和Class列。
SELECT sname, ssex, class FROM t_student;

#2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM t_teacher;

#3、 查询Student表的所有记录。
SELECT * FROM t_student;

#4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM t_score WHERE degree BETWEEN 60 AND 80;

#5、 查询Score表中成绩为85,86或88的记录。
SELECT * FROM t_score WHERE degree IN (85, 86, 88);

#6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM t_student WHERE class = '95031' OR ssex = '女';

#7、 以Class降序查询Student表的所有记录。
SELECT * FROM t_student ORDER BY class DESC;

#9、 查询“95031”班的学生人数。
SELECT count(class) 人数 FROM t_student WHERE class = '95031';

#10、查询Score表中的最高分的学生学号和课程号。
SELECT sno, cno FROM t_score WHERE degree = (SELECT max(degree) FROM t_score);

--  分析:
-- 	第一步:找最高分, select select max(degree) from t_score
-- 	第二步:找学号跟课程号,条件是 degree 等于第一步的结果
#11、查询‘3-105’号课程的平均分。
SELECT avg(degree) 平均分 FROM t_score WHERE cno = '3-105';

#12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
# 这题放弃,不要研究
SELECT cno, avg(degree) FROM t_score WHERE cno LIKE '3-%' GROUP BY cno HAVING count(sno) >= 5;

#13、查询最低分大于70,最高分小于90的Sno列。
SELECT sno FROM t_score GROUP BY sno HAVING min(degree) > 70 AND max(degree) < 90;

#14、查询所有学生的Sname、Cno和Degree列。
SELECT st.sname, sc.cno, sc.degree FROM t_student st, t_score sc WHERE st.sno = sc.sno;

#15、查询所有学生的Sno、Cname和Degree列。
SELECT sc.sno, co.cname, sc.degree FROM tQA_score sc, t_course co WHERE sc.cno = co.cno;

#16、查询所有学生的Sname、Cname和Degree列。
SELECT st.sname, co.cname, sc.degree FROM t_student st, t_score sc, t_course co WHERE st.sno = sc.sno AND sc.cno = co.cno;

#17、查询“95033”班所选课程的平均分。
SELECT avg(sc.degree) FROM t_student st, t_score sc WHERE st.sno = sc.sno AND st.class = '95033';

#18、现查询所有同学的Sno、Cno和rank列。
SELECT sc.sno, sc.cno, gr.rank FROM t_score sc, t_grade gr WHERE sc.degree BETWEEN gr.low AND gr.upp;

#19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT sno FROM t_score WHERE degree > (SELECT degree FROM t_score WHERE cno = '3-105' AND sno = 109) AND cno = '3-105';

-- 分析:
-- 第一步:找出选修“3-105”课程的成绩且为109号同学的成绩
-- 第二步:选修“3-105”课程 且成绩大于 第二步的返回值
#20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
#放弃
SELECT * FROM t_score WHERE degree < (SELECT max(degree) FROM t_score) GROUP BY sno HAVING count(sno) > 1;

-- 分析:
-- 第一步:找出最高分的同学
-- 第二步:选学一门以上的同学: group by sno having count(sno) > 1
-- 通过 son分组, son 大于1个说明选学了2以上科
-- 第三步:综合上面
-- 
#21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM t_score WHERE degree > (SELECT degree FROM t_score WHERE sno = '109' AND cno = '3-105');

#22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
#放弃
SELECT sno, sname, sbirthday FROM t_student WHERE YEAR (sbirthday) = (SELECT YEAR(sbirthday) FROM t_student WHERE SNO = '108');

-- 分析:
-- 	year(xx)  获取时间的年份方法
#23、查询“张旭“教师任课的学生成绩。
SELECT cno, sno, degree FROM t_score WHERE cno = (SELECT co.cno FROM t_course co, t_teacher ta WHERE co.tno = ta.tno AND ta.tname = '张旭');

#24、查询选修某课程的同学人数多于5人的教师姓名。
#放弃
SELECT tname FROM t_teacher WHERE tno IN (SELECT x.tno FROM t_course x, t_score y WHERE x.cno = y.cno GROUP BY x.tno HAVING count(x.tno) > 5);

#25、查询95033班和95031班全体学生的记录。
SELECT * FROM t_student WHERE class IN ('95033', '95031');

#26、查询存在有85分以上成绩的课程Cno.
SELECT cno, degree FROM t_score WHERE degree > 85;

#27、查询出“计算机系“教师所教课程的成绩表。
SELECT t.tno, t.tname, c.cname, s.sno, st.sname, s.degree FROM t_teacher t, t_course c, t_score s, t_student st WHERE t.tno = c.tno AND c.cno = s.cno AND s.sno = st.sno AND depart = '计算机系';

#28、查询所有教师和同学的name、sex和birthday.
SELECT sname, ssex, sbirthday FROM t_student UNION ALL SELECT tname, tsex, tbirthday FROM t_teacher;

#29、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT sname, ssex, sbirthday FROM t_student WHERE ssex = '女' UNION ALL SELECT tname, tsex, tbirthday FROM t_teacher WHERE tsex = '女';

#30、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT sno, cno, degree FROM t_score a WHERE degree < (SELECT avg(degree) FROM t_score b WHERE a.cno = b.cno);


回复 使用道具
举报
烟雨过客

发表于 2017-03-15 04:29 显示全部楼层

支持!支持!!

回复 支持 反对 使用道具
举报
青果杰杰

发表于 2017-03-10 00:59 显示全部楼层

每一个成功者都有一个开始。成功的路,始于回复。

回复 支持 反对 使用道具
举报
破葫芦

发表于 2017-03-06 07:36 显示全部楼层

回复 支持 反对 使用道具
举报
东风玖哥

发表于 2017-03-03 11:00 显示全部楼层

回复 支持 反对 使用道具
举报
pcatzj

发表于 2017-02-26 07:34 显示全部楼层

回复 支持 反对 使用道具
举报
我很认真的说

发表于 2017-02-17 22:07 显示全部楼层

前排支持!!

回复 支持 反对 使用道具
举报
金夜威尼香

发表于 2017-02-17 15:11 显示全部楼层

  ╭══╮ ┌══════┐

  ╭╯让路║═‖ 酱油专用车 ‖

  ╰⊙═⊙╯ └══⊙═⊙═~. 作为一个资深的酱油党,我们需要做的不仅仅是路过,在路过的同时 还要关心楼主,鼓励楼主,在这个冷漠的时代,给予楼主温暖。酱油党莅临的地方,不仅仅是挽尊,不仅仅是消灭零回复,酱油

回复 支持 反对 使用道具
举报
炒菜不加盐

发表于 2017-02-17 10:01 显示全部楼层

活跃活跃论坛气氛~求楼主加好友

回复 支持 反对 使用道具
举报
崇拜

发表于 2017-02-14 21:46 显示全部楼层

作为新人,在吧里不敢大声说话,也不敢得罪人,只能默默地顶完贴转身就走,不求深藏功与名,只求前排混脸熟

回复 支持 反对 使用道具
举报
12下一页

发表新文章
叶星飞

小码哥骨干成员

0

学分

968

学币

1059

积分

小码哥骨干成员

Rank: 6Rank: 6

积分
1059
Ta的主页 发消息
精华帖排行榜

精彩推荐

  • 关注小码哥教育