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 sunfengjiao;

USE sunfengjiao;

CREATE TABLE Basic_information(student_number VARCHAR(4),name1 VARCHAR(10),sex VARCHAR(10),class INT,age INT);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0001','mark','male',1,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0002','tom','male',1,8);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0003','lily','female',1,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0004','lilei','male',2,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0005','david','male',2,8);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0006','lucy','female',2,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0007','wangwu','male',2,10);

CREATE TABLE student_scores(num INT,student_number VARCHAR(4),SUBJECT VARCHAR(20),scores INT);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (1,'0001','shuxue',59);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (2,'0002','shuxue',67);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (3,'0003','shuxue',80);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (4,'0004','shuxue',71);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (5,'0005','shuxue',62);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (6,'0006','shuxue',91);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (7,'0007','shuxue',57);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (8,'0001','yuwen',87);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (9,'0002','yuwen',84);

INSERTINTO student_scores(num,student_number,SUBJECT,scores) VALUE (10,'0003','yuwen',91);

INSERTINTO student_scores(num,student_number,SUBJECT,scores) VALUE (11,'0004','yuwen',97);

INSERTINTO student_scores(num,student_number,SUBJECT,scores) VALUE (12,'0005','yuwen',81);

INSERTINTO student_scores(num,student_number,SUBJECT,scores) VALUE (13,'0006','yuwen',83);

INSERTINTO student_scores(num,student_number,SUBJECT,scores) VALUE (14,'0007','yuwen',76);

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

UPDATE student_scores SET scores=81 WHERE student_number='0007' AND SUBJECT='yuwen';

删除0007的语文成绩

UPDATE student_scores SET scores=NULL WHERE student_number='0007' AND SUBJECT='yuwen';

查询1班的数学平均成绩

SELECT AVG(scores) FROM basic_information b,student_scores s WHERE b.student_number=s.student_number AND SUBJECT='shuxue' AND class=1;

查询数学平均成绩

SELECT AVG(scores) FROM student_scores WHERE SUBJECT='shuxue';

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

SELECT class FROM basic_information,student_scores WHERE basic_information.student_number=student_scores.student_number AND SUBJECT='shuxue' AND scores IN(SELECT MAX(scores) FROM student_scores WHERE SUBJECT='shuxue');

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

SELECT class FROM basic_information,student_scores WHERE basic_information.student_number=student_scores.student_number AND SUBJECT='shuxue' AND scores IN(SELECT MAX(scores) FROM student_scores WHERE SUBJECT='shuxue');

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

SELECT name1 FROM basic_information,student_scores WHERE basic_information.student_number=student_scores.student_number AND SUBJECT='yuwen' AND scores IS NULL;

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

SELECT COUNT(scores) FROM student_scores WHERE SUBJECT='yuwen' AND scores < 60;

SELECT AVG(scores) FROM student_scores WHERE student_number=0005;

附完整sql语句：

CREATE DATABASE sunfengjiao;

USE sunfengjiao;

CREATE TABLE Basic_information(student_number VARCHAR(4),name1 VARCHAR(10),sex VARCHAR(10),class INT,age INT);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0001','mark','male',1,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0002','tom','male',1,8);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0003','lily','female',1,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0004','lilei','male',2,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0005','david','male',2,8);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0006','lucy','female',2,9);

INSERT INTO Basic_information(student_number,name1,sex,class,age) VALUE ('0007','wangwu','male',2,10);

CREATE TABLE student_scores(num INT,student_number VARCHAR(4),SUBJECT VARCHAR(20),scores INT);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (1,'0001','shuxue',59);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (2,'0002','shuxue',67);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (3,'0003','shuxue',80);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (4,'0004','shuxue',71);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (5,'0005','shuxue',62);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (6,'0006','shuxue',91);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (7,'0007','shuxue',57);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (8,'0001','yuwen',87);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (9,'0002','yuwen',84);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (10,'0003','yuwen',91);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (11,'0004','yuwen',97);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (12,'0005','yuwen',81);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (13,'0006','yuwen',83);

INSERT INTO student_scores(num,student_number,SUBJECT,scores) VALUE (14,'0007','yuwen',76);

UPDATE student_scores SET scores=81 WHERE student_number='0007' AND SUBJECT='yuwen';

UPDATE student_scores SET scores=NULL WHERE student_number='0007' AND SUBJECT='yuwen';

SELECT AVG(scores) FROM basic_information b,student_scores s WHERE b.student_number=s.student_number AND SUBJECT='shuxue' AND class=1;

SELECT AVG(scores) FROM student_scores WHERE SUBJECT='shuxue';

SELECT class FROM basic_information,student_scores WHERE basic_information.student_number=student_scores.student_number AND SUBJECT='shuxue'

AND scores IN(SELECT MAX(scores) FROM student_scores WHERE SUBJECT='shuxue');

SELECT name1 FROM basic_information,student_scores WHERE basic_information.student_number=student_scores.student_number AND SUBJECT='yuwen' AND scores IS NULL;

SELECT COUNT(scores) FROM student_scores WHERE SUBJECT='yuwen' AND scores < 60;

SELECT AVG(scores) FROM student_scores WHERE student_number=0005;