[MySQL]学生管理系统的设计：数据表的创建(外加存储过程的创建)

数据表与关系表的创建：

#此代码块每个DROP语句都是为了确保数据库或数据表是否已被创建，如果有，则删除重建
DROP DATABASE IF EXISTS gradem;
CREATE DATABASE IF NOT EXISTS gradem;
USE gradem;

DROP TABLE IF EXISTS student;
#学生表student的创建
CREATE TABLE IF NOT EXISTS student
(
    sno        CHAR(10) NOT NULL PRIMARY KEY,
    sname      VARCHAR(8),
    ssex       CHAR(2),
    sbirthday  DATETIME,
    saddress   VARCHAR(50),
    sdept      CHAR(16),
    speciality VARCHAR(20)
) ENGINE = InnoDB COMMENT '学生表';

DROP TABLE IF EXISTS course;
#课程表course的创建
CREATE TABLE IF NOT EXISTS course
(
    cno   CHAR(5)     NOT NULL PRIMARY KEY,
    cname VARCHAR(20) NOT NULL
) ENGINE = InnoDB COMMENT '课程表';

DROP TABLE IF EXISTS sc;
#成绩表sc的创建，同时也是student与course的关系表
CREATE TABLE IF NOT EXISTS sc
(
    sno    CHAR(10) NOT NULL,
    cno    CHAR(5)  NOT NULL,
    degree DECIMAL(4, 1),
    PRIMARY KEY (sno, cno),
    CONSTRAINT FOREIGN KEY f_skey1 (sno) REFERENCES student (sno),
    CONSTRAINT FOREIGN KEY f_skey2 (cno) REFERENCES course (cno)
) ENGINE = InnoDB COMMENT '学生与课程的关系表';

DROP TABLE IF EXISTS teacher;
#教师表teacher的创建
CREATE TABLE IF NOT EXISTS teacher
(
    tno       CHAR(3) NOT NULL PRIMARY KEY,
    tname     VARCHAR(8),
    tsex      CHAR(2),
    tbirthday DATE,
    tdept     CHAR(16)
) ENGINE = InnoDB COMMENT '教师表';


DROP TABLE IF EXISTS teaching;
#授课安排表teaching的创建，同时也是教师表与课程表的关系表
CREATE TABLE teaching
(
    cno   CHAR(5) NOT NULL,
    tno   CHAR(3) NOT NULL,
    cterm TINYINT(1),
    PRIMARY KEY (cno, tno),
    CONSTRAINT FOREIGN KEY f_tkey1 (cno) REFERENCES course (cno),
    CONSTRAINT FOREIGN KEY f_tkey2 (tno) REFERENCES teacher (tno)
) ENGINE = InnoDB COMMENT '教师与学生的关系表';

[附加]存储过程的创建：

 如果需要一个存储过程的话，可以参考以下代码块创建，也可忽略掉以下代码块。

#下面是有关以上5个数据表存储过程的创建，如果不需要可忽略掉。
DROP PROCEDURE IF EXISTS studentAdd;
DELIMITER /
CREATE PROCEDURE studentAdd(IN s_sno CHAR(10), IN s_sname VARCHAR(8), IN s_sex CHAR(2), IN s_sbirthday DATETIME,
                            IN s_saddress VARCHAR(50), IN s_sdept CHAR(16), IN s_speciality VARCHAR(20))
BEGIN
    INSERT INTO student (sno, sname, ssex, sbirthday, saddress, sdept, speciality)
    VALUES (s_sno, s_sname, s_sex, s_sbirthday, s_saddress, s_sdept, s_speciality);
END /
DELIMITER ;


DROP PROCEDURE IF EXISTS courseAdd;
DELIMITER /
CREATE PROCEDURE courseAdd(
    IN c_cno CHAR(5),
    IN c_cname VARCHAR(20))
BEGIN
    INSERT INTO course (cno, cname) VALUE (c_cno, c_cname);
END /
DELIMITER ;


DROP PROCEDURE IF EXISTS scAdd;
DELIMITER /
CREATE PROCEDURE scAdd(IN s_sno CHAR(10),
                       IN s_cno CHAR(5),
                       IN s_degree DECIMAL(4, 1))
BEGIN
    INSERT INTO sc (sno, cno, degree) VALUE (s_sno, s_cno, s_degree);
END /
DELIMITER ;


DROP PROCEDURE IF EXISTS teacherAdd;
DELIMITER /
CREATE PROCEDURE teacherAdd(IN t_tno CHAR(3),
                            IN t_tname VARCHAR(8),
                            IN t_tsex CHAR(2),
                            IN t_tbirthday DATE,
                            IN t_tdept CHAR(16))
BEGIN
    INSERT INTO teacher (tno, tname, tsex, tbirthday, tdept) VALUE (t_tno, t_tname, t_tsex, t_tbirthday, t_tdept);
END /
DELIMITER ;


DROP PROCEDURE IF EXISTS teachingAdd;
DELIMITER /
CREATE PROCEDURE teachingAdd(
    IN t_cno CHAR(5),
    IN t_tno CHAR(3),
    IN t_cterm TINYINT(1))
BEGIN
    INSERT INTO teaching (cno, tno, cterm) VALUE (t_cno, t_tno, t_cterm);
END /
DELIMITER  ;