MySQL基础-手写SQL常见试题
1、SQL查询每门科目的成绩大于80的学生名字
sql-所有科目都及格的学生
其实这个和每门科目的成绩大于80的学生名字是一样的。
创建表和准备数据:
CREATE TABLE `grade` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `course` varchar(255) DEFAULT NULL, `score` int(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO grade values(1,"aa","数学",75); INSERT INTO grade values(2,"aa","语文",82); INSERT INTO grade values(3,"aa","英语",56); INSERT INTO grade values(4,"bb","数学",90); INSERT INTO grade values(5,"bb","语文",80); INSERT INTO grade values(6,"bb","英语",82); INSERT INTO grade values(7,"cc","数学",88); INSERT INTO grade values(8,"cc","语文",60);
grade表如下所示:
思路一:
先找到小于80分的学生姓名,再查询的学生姓名不在这里面的就行了。
SELECT DISTINCT name FROM grade WHERE name not in( SELECT DISTINCT name FROM grade WHERE score < 80 );
思路二:
对学生姓名group by分组,之后having进行过滤,过滤条件为最小分数大于等于80。
SELECT name FROM grade GROUP BY name HAVING min(score) >= 80;
2、 删除除了自动编号不同, 其他都相同的学生冗余信息
学生表 如下:
自动编号 学号 姓名
课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
创建表和准备数据:
CREATE TABLE `student_table` ( `auto_no` int(11) NOT NULL AUTO_INCREMENT, `student_no` varchar(255) DEFAULT NULL, `student_name` varchar(255) DEFAULT NULL, `course_no` varchar(255) DEFAULT NULL, `course_name` varchar(255) DEFAULT NULL, `score` int DEFAULT NULL, PRIMARY KEY (`auto_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO student_table values(1,"2005001","张三","0001","数学",69); INSERT INTO student_table values(2,"2005002","李四","0001","数学",89); INSERT INTO student_table values(3,"2005001","张三","0001","数学",69);
正确写法:
DELETE FROM student_table WHERE auto_no NOT IN ( SELECT t.* FROM( SELECT min(auto_no) FROM student_table GROUP BY student_no,student_name,course_no,course_name,score ) t );
错误语句,在Mysql中group by分组后需要再嵌套一层,否则会保如下异常信息:
1093 - You can't specify target table 'student_table' for update in FROM clause
DELETE FROM student_table WHERE auto_no NOT IN ( SELECT min(auto_no) FROM student_table GROUP BY student_no,student_name,course_no,course_name,score );
3、用一条sql语句显示所有可能的比赛组合
一个叫team的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球队,现在四个球队进行比赛,用一条sql语句显示所有可能的比赛组合.
创建表和准备数据:
CREATE TABLE `team` ( `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO team values("a"),("b"),("c"),("d");
自连接实现:
SELECT t1.name,t2.name FROM team t1,team t2 WHERE t1.name < t2.name;
4、面试题:怎么把这样一个
![](https://img2020.cnblogs.com/blog/1478011/202103/1478011-20210328002349658-118597457.png)
查成这样一个结果
创建表和准备数据:
CREATE TABLE `year_month_amount` ( `year` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of year_month_amount -- ---------------------------- INSERT INTO `year_month_amount` VALUES ('1991', '1', '1.1'); INSERT INTO `year_month_amount` VALUES ('1991', '2', '1.2'); INSERT INTO `year_month_amount` VALUES ('1991', '3', '1.3'); INSERT INTO `year_month_amount` VALUES ('1991', '4', '1.4'); INSERT INTO `year_month_amount` VALUES ('1992', '1', '2.1'); INSERT INTO `year_month_amount` VALUES ('1992', '2', '2.2'); INSERT INTO `year_month_amount` VALUES ('1992', '3', '2.3'); INSERT INTO `year_month_amount` VALUES ('1992', '4', '2.4');
答案结果:
select year, (select amount from year_month_amount m where month=1 and m.year=ot.year) as m1, (select amount from year_month_amount m where month=2 and m.year=ot.year) as m2, (select amount from year_month_amount m where month=3 and m.year=ot.year) as m3, (select amount from year_month_amount m where month=4 and m.year=ot.year) as m4 from year_month_amount ot group by year
5、查询课程是否通过
原表:
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
创建表和准备数据:
CREATE TABLE `course_table` ( `courseid` int(8) NOT NULL AUTO_INCREMENT, `coursename` varchar(255) DEFAULT NULL, `score` int(16) DEFAULT NULL, PRIMARY KEY (`courseid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `course_table` VALUES (1, 'java', 70); INSERT INTO `course_table` VALUES (2, 'oracle', 90); INSERT INTO `course_table` VALUES (3, 'xml', 40); INSERT INTO `course_table` VALUES (4, 'jsp', 30); INSERT INTO `course_table` VALUES (5, 'servlet', 80);
解法一:使用if
select courseid, coursename ,score ,if(score>=60, 'pass','fail') as mark from course_table;
解法二:使用case when
SELECT courseid,coursename,score, CASE WHEN score >= 60 AND score <= 100 THEN 'pass' WHEN score < 60 AND score >=0 THEN 'fail ' END as mark FROM course_table;