Mysql⾯试题：⽤⼀条SQL语句查出每门课都⼤于80分的学⽣
的姓名
⽤⼀条sql语句查询出所有课程都⼤于80分的学⽣名单：
name cource score
张三语⽂81
张三数学75
李四语⽂76
李四数学90
王五语⽂81
enableasync王五数学100
王五英语90
1SET FOREIGN_KEY_CHECKS=0;
2
3-- ----------------------------
4-- Table structure for grade
5-- ----------------------------
6DROP TABLE IF EXISTS `grade`;
7CREATE TABLE `grade` (
8  `name` varchar(255) NOT NULL,
9  `class` varchar(255) NOT NULL,
10  `score` tinyint(4) NOT NULL
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
12
13-- ----------------------------
14-- Records of grade
15-- ----------------------------
16INSERT INTO `grade` VALUES ('张三', '语⽂', '81');
eclipse设置背景颜17INSERT INTO `grade` VALUES ('张三', '数学', '75');
18INSERT INTO `grade` VALUES ('李四', '语⽂', '76');
slide安装19INSERT INTO `grade` VALUES ('李四', '数学', '90');
20INSERT INTO `grade` VALUES ('王五', '语⽂', '81');
21INSERT INTO `grade` VALUES ('王五', '数学', '100');
22INSERT INTO `grade` VALUES ('王五', '英语', '90');
23SET FOREIGN_KEY_CHECKS=1;mysql面试题学生表
查询每门课都⼤于80分的同学的姓名：
1select distinct name from grade where name not in (select distinct name from grade where score<=80);
还有⼀种简单的写法：
1select name from grade group by name having min(score)>80;
javascriptreturnfalse查询平均分⼤于80的学⽣名单：
1select name from (
2select count(*) t, sum(score) num, name from grade group by name
3 ) as a where a.num>80*t;
三角函数公式大全图表也有⼀种简单的写法：
1select name, avg(score) as sc from grade group by name having avg(score)>80;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。