数据库高级查询 代码

create database one20210611 default charset=utf8;

use one20210611;

CREATE TABLE studentinfo (
StudentID char(10) DEFAULT NULL,
StudentName varchar(20) DEFAULT NULL,
Gender varchar(2) DEFAULT NULL,
Birthday date DEFAULT NULL,
ClassID int(4) DEFAULT NULL,
BeginYear year(4) DEFAULT NULL,
Phone varchar(11) DEFAULT NULL,
Province varchar(20) DEFAULT NULL,
City varchar(20) DEFAULT NULL,
Email varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table teacher (
id int,
teachername VARCHAR(10),
hiredate DATE,
gender char

);

create table classinfo (
id int, # 班级编号
classname varchar(10),
gradeid int, # 年级编号
beginyear varchar(10) # 开始年份

);

create table subject (
id int ,
subjectname varchar(10),
teacherid int

);

create table exam (
id int,
exam int,
subjectid int,
studentid int,
remark varchar(10) # 备注
);

create table grade(
id int ,
gradename varchar(10),
major varchar(20)
);

INSERT INTO one20210611.classinfo(id, classname, gradeid, beginyear) VALUES (2001, ‘20级1班’, 20, ‘2020’);
INSERT INTO one20210611.classinfo(id, classname, gradeid, beginyear) VALUES (2002, ‘20级2班’, 20, ‘2020’);

INSERT INTO one20210611.exam(id, exam, subjectid, studentid, remark) VALUES (1, 98, 101, 1, ‘无’);
INSERT INTO one20210611.exam(id, exam, subjectid, studentid, remark) VALUES (2, 89, 102, 1, NULL);
I