编辑代码

CREATE DATABASE TEST;
USE TEST;
CREATE TABLE S
(

SNO VARCHAR(6) PRIMARY KEY ,

SN VARCHAR(10) NOT NULL ,

SD VARCHAR(16),

SB DATE,

SEX VARCHAR(2) DEFAULT '男',

CHECK (SEX IN ('男', '女')) 

); 

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s01', '王玲', '计算机', '2000-06-30', '女');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s02', '李渊', '计算机', '1995-03-23', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s03', '罗军', '计算机', '1995-08-12', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s04', '赵泽', '计算机', '1997-09-12', '女');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s05', '许若', '自动化', '1999-06-27', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s06', '王仙华', '自动化', '1996-05-20', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s07', '朱祝', '自动化', '1998-07-10', '女');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s20', '李国民', '数学', '1999-12-31', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s21', '陈浩然', '计算机', '2000-10-15', '男');

INSERT INTO S(SNO, SN, SD, SB, SEX) values('s31', '王浩', '计算机', '1999-10-15', '男');









CREATE TABLE C

(

CNO VARCHAR(6) PRIMARY KEY,

CN VARCHAR(10),

PC VARCHAR(6),

FOREIGN KEY (PC) REFERENCES C(CNO)

);

INSERT INTO C(CNO, CN, PC) values('C01', '高等数学', NULL);

INSERT INTO C(CNO, CN, PC) values('C02', '数据结构', 'C01');

INSERT INTO C(CNO, CN, PC) values('C03', '操作系统', 'C02');

INSERT INTO C(CNO, CN, PC) values('C04', '数据库', 'C03');

INSERT INTO C(CNO, CN, PC) values('C05', '作战指挥', 'C04');

INSERT INTO C(CNO, CN, PC) values('C06', '离散数学', 'C01');

INSERT INTO C(CNO, CN, PC) values('C07', '信息安全', 'C06');

INSERT INTO C(CNO, CN, PC) values('C08', '大学英语', NULL);

INSERT INTO C(CNO, CN, PC) values('C11', 'C语言程序设计', NULL);









CREATE  TABLE   SC

(  SNO  VARCHAR(6),

   CNO   VARCHAR(6),

   GRADE DEC(4,1),

   PRIMARY KEY (SNO, CNO),  

   FOREIGN KEY (SNO) REFERENCES S(SNO),

   FOREIGN KEY (CNO) REFERENCES C(CNO),

   CHECK (GRADE BETWEEN 0 AND 100)

);



INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C06', 90.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s04', 'C11', 87.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C03', 79.5);

INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C05', 88.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s05', 'C07', 90.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s06', 'C03', 88.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s07', 'C11', 88.0);

INSERT INTO SC(SNO, CNO, GRADE) values('s21', 'C05', NULL);

INSERT INTO SC(SNO, CNO, GRADE) values('s31', 'C01', NULL);

SELECT SNO AS 学号,timestampdiff(year,SB,Curdate())+1 AS 年龄 FROM S;
SELECT SN,SB FROM S WHERE SB BETWEEN '1998-01-01' AND '1999-12-31';
SELECT SNO,SN,SD FROM S WHERE SD NOT IN('数学','计算机');
SELECT SNO,CNO FROM SC WHERE GRADE IS NULL;
SELECT SNO,AVG(GRADE) FROM SC GROUP BY SNO HAVING AVG(GRADE)>80;
SELECT SD FROM S WHERE SEX '男' GROUP BY HAVING COUNT(*)>2;