sql in interview for a job
1、mysql下建表及插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/* Navicat MySQL Data Transfer Source Server : mysql Source Server Version : 50640 Source Host : localhost:3306 Source Database : test Target Server Type : MYSQL Target Server Version : 50640 File Encoding : 65001 Date: 2018-11-22 18:24:08 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for cource -- ---------------------------- DROP TABLE IF EXISTS `cource`; CREATE TABLE `cource` ( `cno` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程编号', `cname` varchar(20) DEFAULT NULL COMMENT '课程名称', PRIMARY KEY (`cno`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='课程表'; -- ---------------------------- -- Records of cource -- ---------------------------- INSERT INTO `cource` VALUES ('1', '语文'); INSERT INTO `cource` VALUES ('2', '数学'); INSERT INTO `cource` VALUES ('3', '英语'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sno` int(3) unsigned DEFAULT NULL COMMENT '学生编号', `cno` int(3) unsigned DEFAULT NULL COMMENT '课程编号', `score` int(3) unsigned DEFAULT NULL COMMENT '考试成绩' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生成绩表'; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '63'); INSERT INTO `score` VALUES ('1', '2', '61'); INSERT INTO `score` VALUES ('2', '1', '80'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sno` int(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生编号', `sname` varchar(20) DEFAULT NULL COMMENT '学生姓名', `sage` int(3) unsigned DEFAULT NULL COMMENT '学生年龄', PRIMARY KEY (`sno`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='学生表'; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '周杰伦', '18'); INSERT INTO `student` VALUES ('2', '周润发', '18'); INSERT INTO `student` VALUES ('3', '吴孟达', '25'); INSERT INTO `student` VALUES ('4', '刘德华', '25'); INSERT INTO `student` VALUES ('5', '李连杰', '29');
2、查询语文成绩比数学成绩高的所有学生的编号
分析思路:
(1)在学生表上添加两个字段,分别为数学成绩字段和语文成绩字段
(2)把成绩表分成两个表,语文表和数学表
书写过程:
(1)语文表
select sno, score from score where cno = 1
(2)数学表
select sno, score from score where cno = 2
(3)结果
select sno, sname, score_chinese, score_math from
(
select s.sno, s.sname, score_chinese from student s
left join (select sno sno_chinese, score score_chinese from score where cno = 1) chinese
on s.sno = chinese.sno_chinese
) s_chinese
left join (select sno sno_math, score score_math from score where cno = 2) math
on s_chinese.sno = math.sno_math
where score_chinese > score_math
3、查询所有学生的学号、姓名、选课数、总成绩
分析思路:
(1)在学生表上添加两个字段,分别为选课数和总成绩
(2)把成绩表按学生编号分组,并计算出count(*)和sum(score)
书写过程:
(1)分组并统计
select sno, count(*), sum(score) from score group by sno
(2)结果
select s.sno, s.sname, select_count, select_sum from student s
left join (select sno sno_count_sum, count(*) select_count, sum(score) select_sum from score group by sno) count_sum
on s.sno = count_sum.sno_count_sum
4、查询没有学完所有课程的学生学号、姓名
分析思路:把成绩表按学生编号并count(*)
select sno, count(cno) cno_count from score group by sno having cno_count > 1