mysql的增删改查例子_MySQL_增删改查实例

实现如下的学生信息管理系统：

对应的表

学生基本信息表

实现如下的学生信息管理系统：

对应的表

学生基本信息表(表一)

学号 姓名 性别 班级 年龄

0001 mark male 1 9

0002 tom male 1 8

0003 lily female 1 9

0004 lilei male 2 9

0005 david male 2 8

0006 lucy female 2 9

0007 wangwu male 2 10

成绩表(表二)

序号 学号 学科 成绩

1 0001 shuxue 59

2 0002 shuxue 67

3 0003 shuxue 80

4 0004 shuxue 71

5 0005 shuxue 62

6 0006 shuxue 91

7 0007 shuxue 57

8 0001 yuwen 87

9 0002 yuwen 84

10 0003 yuwen 91

11 0004 yuwen 97

12 0005 yuwen 81

13 0006 yuwen 83

14 0007 yuwen 76

要求：创建如上的表，并把相应地数据插入到对应的表中

CREATE DATABASE students_info; # 建立学生信息数据库

USE students_info; # 使用学生信息数据库

#建立学生基本信息表(建立表一)

CREATE TABLE stu_base(id INT,student_ID VARCHAR(20) NOT NULL PRIMARY KEY,NAME VARCHAR(20),sex VARCHAR(10),class INT,age INT);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(1,'0001','mark','male',1,9);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(2,'0002','tom','male',1,8);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(3,'0003','lily','female',1,9);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(4,'0004','lilei','male',2,9);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(5,'0005','david','male',2,8);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(6,'0006','lucy','female',2,9);

INSERT INTO stu_base(id,student_ID,NAME,sex,class,age) VALUES(7,'0007','wangwu','male',2,10);

建立学生成绩表(表二)

USE students_info; #使用学生信息数据库

#创建学生成绩表

CREATE TABLE stu_scores(id INT NOT NULL PRIMARY KEY,student_ID VARCHAR(30),SUBJECT VARCHAR (10),score INT);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(1,'0001','shuxue',59);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(2,'0002','shuxue',67);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(3,'0003','shuxue',80);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(4,'0004','shuxue',71);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(5,'0005','shuxue',62);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(6,'0006','shuxue',91);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(7,'0007','shuxue',57);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(8,'0001','yuwen',87);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(9,'0002','yuwen',84);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(10,'0003','yuwen',91);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(11,'0004','yuwen',97);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(12,'0005','yuwen',81);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(13,'0006','yuwen',83);

INSERT INTO stu_scores(id,student_ID,SUBJECT,score) VALUES(14,'0007','yuwen',76);

2、把学号为007的语文成绩修改成81分

UPDATE stu_scores SET score='81' WHERE id = 14;

3、删除0007的语文成绩

UPDATE stu_scores SET score=NULL WHERE id = 14;

4、查询1班的数学平均成绩

SELECT AVG(score) FROM stu_scores score WHERE SUBJECT ='shuxue' AND student_ID IN (SELECT student_ID FROM stu_base WHERE class = 1);

5、查询数学平均成绩

SELECT AVG(score) FROM stu_scores score WHERE SUBJECT = 'shuxue';

6、查询数学得分最高的学生的班级

SELECT class FROM stu_base WHERE student_ID IN (SELECT student_ID FROM stu_scores WHERE SUBJECT='shuxue'

AND score IN(SELECT MAX(score) FROM stu_scores WHERE SUBJECT = 'shuxue'));

7、查询没有语文成绩的学生的姓名

SELECT NAME FROM stu_base WHERE student_ID IN (SELECT student_ID FROM stu_scores WHERE score IS NULL);

8、查询语文分数小于60分的总人数

SELECT COUNT(student_ID) FROM stu_scores WHERE SUBJECT = 'yuwen' AND score <60;

9、查询学号为005的学生的平均成绩

SELECT AVG(score) FROM stu_scores WHERE student_ID= 0005;