MySQL数据库学生成绩管理系统 - 兼容Oracle的SQL语句

CREATE TABLE student( stuid INT PRIMARY KEY AUTO_INCREMENT, stuname VARCHAR(20) NOT NULL , idcard VARCHAR(11) UNIQUE , stusex CHAR(1) CHECK(stusex IN('F','M')), stuage INT , cid INT );

INSERT INTO student VALUES(NULL,'余权明','74110','F',18,1001) ,(NULL,'余权红','74111','M',19,1001) ,(NULL,'余权黄','74112','M',18,1002) ,(NULL,'博文','74113','F',16,1002) ,(NULL,'小蓝','74114','M',17,1003);

SELECT * FROM STUDENT;

CREATE TABLE classes ( cid INT PRIMARY KEY , cname VARCHAR(20) ); INSERT INTO classes VALUES(1001,'st2201'); INSERT INTO classes VALUES(1002,'st2202'); INSERT INTO classes VALUES(1003,'st2301'); INSERT INTO classes VALUES(1004,'st2302');

CREATE TABLE subjects( subid INT PRIMARY KEY, subname VARCHAR(20) UNIQUE );

INSERT INTO subjects(subid,subname) VALUES (101,'HTML');

INSERT INTO subjects(subid,subname) VALUES (102,'C语言') ,(103,'JAVA基础') ,(104,'MySQL基础');

CREATE TABLE score( scid INT PRIMARY KEY AUTO_INCREMENT, stuid INT , subid INT , score INT , CONSTRAINT fk_student FOREIGN KEY (stuid) REFERENCES student(stuid), CONSTRAINT fk_subject FOREIGN KEY (subid) REFERENCES subjects(subid) ); INSERT INTO SCORE VALUES(NULL,1,101,90) ,(NULL,1,102,60) ,(NULL,1,103,70) ,(NULL,2,101,80) ,(NULL,2,102,50) ,(NULL,2,103,40) ,(NULL,3,101,50) ,(NULL,3,102,80) ,(NULL,3,103,90) ,(NULL,4,101,90) ,(NULL,4,102,80) ,(NULL,5,101,90)