时间戳

首页 > 技术文章 > MySQL基础-手写SQL常见试题

MySQL基础-手写SQL常见试题

yangms 2021-02-01 23:54 原文

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、面试题：怎么把这样一个

 查成这样一个结果

 

创建表和准备数据：

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;

 

 

参考：SQL查询每门科目的成绩大于80的学生名字

　　　SQL经典面试题及答案_komtao520的博客

 

 

推荐阅读