mysql用一条语句将所有平均分为75分以上的学生的各门课成绩在原来基础上增加10%

本文介绍了一个具体的SQL操作案例，包括创建学生选课成绩表SC、插入成绩记录，并使用SQL更新语句来调整满足特定条件的学生的成绩。通过这个案例，读者可以了解到如何在数据库中创建表、插入数据以及执行条件更新。

创建表SC：

CREATE TABLE SC(SNO CHAR(7),
CNO CHAR(10),
GRADE SMALLINT CHECK(GRADE>0))\G

添加元素到SC：

INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512101","C03","95")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512103,"C03","51\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512101","C05","80")
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512103","C05","60")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9521101","C05","72")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9521102","C05","80")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9521103","C05","45")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9531101","C05","81")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9531102","C05","94")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512101","C03",NULL)\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9531102","C03",NULL)\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512101","C03","87")\g
INSERT INTO SC(SNO,CNO,GRADE) VALUES("9512101","C03","51")\g

显示表：

select * from SC\g

结果：

一句语句：

update SC set grade=grade*1.1 where sno in (SELECT distinct sno*(AVG(grade)>75) group BY sno)\g

结果：

完