教学数据库teachingpm设计与SQL语句实现

1. 数据库介绍

本教学数据库名为teachingpm，用于存储和管理高校教学相关信息，包含以下数据表：

• SpecialityInfo (专业信息表)
• StudentInfo (学生信息表)
• CourseInfo (课程信息表)
• ScoreInfo (成绩信息表)
• TeacherInfo (教师信息表)
• LectureInfo (授课信息表)

2. 表结构设计

2.1 SpecialityInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | SpecialityID | CHAR(6) | × | 主键 | 无 | 专业代码 | | Specialityname | CHAR(16) | √ | | 无 | 专业名称 |

2.2 StudentInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | StudentID | CHAR(6) | × | 主键 | 无 | 学号 | | Sname | CHAR(8) | × | | 无 | 姓名 | | Ssex | CHAR(2) | × | | '男' | 性别 | | Sbirthday | DATE | × | | 无 | 出生日期 | | Native | CHAR(8) | √ | | 无 | 籍贯 | | Tc | TINYINT | √ | | 无 | 总学分 | | SpecialityID | CHAR(6) | × | 外键 | 无 | 专业代码 |

2.3 CourseInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | CourseID | VARCHAR(4) | × | 主键 | 无 | 课程号 | | Coursename | VARCHAR(16) | × | | 无 | 课程名 | | Credit | TINYINT | √ | | 无 | 学分 |

2.4 ScoreInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | StudentID | VARCHAR(6) | × | 主键 | 无 | 学号 | | CourseID | VARCHAR(4) | × | 主键 | 无 | 课程号 | | Grade | TINYINT | √ | | 无 | 成绩 |

2.5 TeacherInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | TeacherID | CHAR(6) | × | 主键 | 无 | 教师编号 | | Tname | CHAR(8) | × | | 无 | 姓名 | | Tsex | CHAR(2) | × | | '男' | 性别 | | Tbirthday | DATE | × | | 无 | 出生日期 | | Native | CHAR(8) | √ | | 无 | 籍贯 | | Title | CHAR(12) | √ | | 无 | 职称 | | School | CHAR(12) | √ | | 无 | 学院 |

2.6 LectureInfo表

| 列名 | 数据类型 | 允许null值 | 键 | 默认值 | 说明 | |---|---|---|---|---|---| | TeacherID | VARCHAR(6) | × | 主键 | 无 | 教师编号 | | CourseID | VARCHAR(4) | × | 主键 | 无 | 课程号 | | Location | VARCHAR(10) | √ | | 无 | 上课地点 |

3. SQL语句示例

3.1 创建数据库

CREATE DATABASE teachingpm;

3.2 创建StudentInfo表

CREATE TABLE StudentInfo (
    StudentID CHAR(6) NOT NULL PRIMARY KEY,
    Sname CHAR(8) NOT NULL UNIQUE,
    Ssex CHAR(2) NOT NULL DEFAULT '男',
    Sbirthday DATE NOT NULL,
    Native CHAR(8),
    Tc TINYINT CHECK (Tc >= 0 AND Tc <= 100),
    SpecialityID CHAR(6) NOT NULL,
    FOREIGN KEY (SpecialityID) REFERENCES SpecialityInfo(SpecialityID)
);

3.3 查看StudentInfo表结构

DESC StudentInfo;

3.4 复制StudentInfo表

CREATE TABLE StudentInfo1 AS SELECT * FROM StudentInfo;

3.5 添加列

ALTER TABLE StudentInfo ADD COLUMN StuNo INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

3.6 修改列

ALTER TABLE StudentInfo1 CHANGE COLUMN Native City VARCHAR(8) DEFAULT '北京';

3.7 删除列

ALTER TABLE StudentInfo DROP COLUMN StuNo;

3.8 重命名表

ALTER TABLE StudentInfo1 RENAME TO StudentInfo2;

3.9 删除表

DROP TABLE StudentInfo2;

3.10 创建StudentInfo1表（表级约束）

CREATE TABLE StudentInfo1 (
    StudentID CHAR(6) NOT NULL,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) NOT NULL DEFAULT '男',
    Sbirthday DATE NOT NULL,
    Native CHAR(8),
    Tc TINYINT,
    SpecialityID CHAR(6) NOT NULL,
    CONSTRAINT pk_StudentID PRIMARY KEY (StudentID),
    CONSTRAINT uc_Sname UNIQUE (Sname),
    CONSTRAINT fk_SpecialityID FOREIGN KEY (SpecialityID) REFERENCES SpecialityInfo(SpecialityID),
    CONSTRAINT chk_Tc CHECK (Tc >= 0 AND Tc <= 100)
);