数据表

表的sql语句

SET FOREIGN_KEY_CHECKS=0  
-- ----------------------------  
-- Table structure for grade  
-- ----------------------------  
DROP TABLE IF EXISTS grade;  
CREATE TABLE grade (  
      name varchar(255) NOT NULL, 
     class varchar(255) NOT NULL, 
     score tinyint(4) NOT NULL 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   
   -- ---------------------------- 
    -- Records of grade 
    -- ---------------------------- 
INSERT INTO grade VALUES ('张三', '语文', '81'); 
INSERT INTO grade VALUES ('张三', '数学', '75'); 
INSERT INTO grade VALUES ('李四', '语文', '76'); 
INSERT INTO grade VALUES ('李四', '数学', '90'); 
INSERT INTO grade VALUES ('王五', '语文', '81'); 
INSERT INTO grade VALUES ('王五', '数学', '100');
INSERT INTO grade VALUES ('王五', '英语', '90'); 
SET FOREIGN_KEY_CHECKS=1;

1.查询出所有科目都大于80分学生的姓名

select distinct name 
from grade 
where name not in (select distinct name from grade where score<=80);

简单写法：

select name from grade group by name having min(score)>80;

2.查询平均分大于80分的学生

select name from ( 
    select count() t, sum(score) num, name from grade group by name   
) as a where a.num>80;

简单写法：

select name, avg(score) as sc from grade group by name having avg(score)>80;

github：https://github.com/liukai90/liukai90.github.io/blob/master/sql/查询所有课程都大于80分的学生姓名.md