Mysql⾯试题:⽤⼀条SQL语句查出每门课都⼤于80分的学⽣
的姓名
⽤⼀条sql语句查询出所有课程都⼤于80分的学⽣名单:
name cource score
张三语⽂81
张三数学75
李四语⽂76
java程序设计教程西安电子科技大学出版社李四数学90
王五语⽂81
selenium和scrapy哪个好王五数学100
王五英语90
javabean的定义和使用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
mysql面试题sql
13-- ----------------------------
14-- Records of grade
15-- ----------------------------
16INSERT INTO `grade` VALUES ('张三', '语⽂', '81');
17INSERT INTO `grade` VALUES ('张三', '数学', '75');
18INSERT INTO `grade` VALUES ('李四', '语⽂', '76');
19INSERT INTO `grade` VALUES ('李四', '数学', '90');
20INSERT INTO `grade` VALUES ('王五', '语⽂', '81');
21INSERT INTO `grade` VALUES ('王五', '数学', '100');
22INSERT INTO `grade` VALUES ('王五', '英语', '90');format造句
23SET FOREIGN_KEY_CHECKS=1;
查询每门课都⼤于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;
查询平均分⼤于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小时内删除。