MySQL练习

1、创建如下的表，并把相应地数据插入到对应的表中

creat DATABASE homework;
USE homework
CREATE TABLE stu(id INT(4)ZEROFILL, NAME VARCHAR(15),sex VARCHAR(15),class INT(5),age INT(5));
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0001,“Mark”,“male”,1,9);
SELECTFROM stu;
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0002,“tom”,“male”,1,8);
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0003,“lily”,“female”,1,9);
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0004,“lilei”,“male”,2,9);
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0005,“david”,“male”,2,8);
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0006,“lucy”,“female”,2,9);
INSERT INTO stu(id ,NAME,sex,class,age)VALUES(0007,“wangwu”,“male”,2,10);
SELECTFROM stu;
2、创建成绩表：

CREATE TABLE sco(number INT(4),id INT(4)ZEROFILL,SUBJECT VARCHAR(15),score INT(6));
INSERT INTO sco(number,id,SUBJECT,score)VALUES(1,0001,“shuxue”,59);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(2,0002,“shuxue”,67);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(3,0003,“shuxue”,80);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(4,0004,“shuxue”,71);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(5,0005,“shuxue”,62);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(6,0006,“shuxue”,91);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(7,0007,“shuxue”,57);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(8,0001,“yuwen”,87);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(9,0002,“yuwen”,84);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(10,0003,“yuwen”,91);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(11,0004,“yuwen”,97);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(12,0005,“yuwen”,81);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(13,0006,“yuwen”,83);
INSERT INTO sco(number,id ,SUBJECT,score)VALUES(14,0007,“yuwen”,76);
SELECT*FROM sco;
3、把学号为007的语文成绩修改成81分
UPDATE sco SET score =81 WHERE number = 14;
4、删除0007的语文成绩
UPDATE sco SET score =NULL WHERE number = 14;
5、查询1班的数学平均成绩
SELECT AVG(score)FROM sco score WHERE SUBJECT=“shuxue” AND id IN (SELECT id FROM stu WHERE class=1);
6、查询数学平均成绩
SELECT AVG(score)FROM sco score WHERE SUBJECT=“shuxue”
7、查询数学得分最高的学生的班级
SELECT class FROM stu WHERE id IN(SELECT id FROM sco WHERE sco.SUBJECT =“shuxue”
AND sco.scoreIN (SELECT MAX(score)FROM sco WHERE SUBJECT=“shuxue”));

8、查询没有语文成绩的学生的姓名
SELECT NAME FROM stu WHERE id IN (SELECT id FROM sco WHERE score IS NULL);
9、查询语文分数小于60分的总人数
SELECT COUNT(id) FROM sco WHERE SUBJECT=“yuwen” AND score <60;
10、查询学号为005的学生的平均成绩
SELECT AVG (score) FROM sco WHERE id=0005;