P95.12

CREATE DATABASE tdb;
USE tdb;
-- DROP DATABASE tdb;
-- 学生表
CREATE TABLE students(
    sid int PRIMARY KEY COMMENT "学号",
    `name` varchar(8) NOT NULL COMMENT "学生姓名",
    sage int ,
    gender int,
    dept int COMMENT "所在系所"
);

-- 教师表
CREATE TABLE teachers(
    tid int PRIMARY KEY COMMENT "工号",
    tname varchar(20) UNIQUE COMMENT "教师名",
    title varchar(20) DEFAULT '讲师' COMMENT "职称",
    base_salary double COMMENT "基础工资"
);

-- 课程表
CREATE TABLE courses(
    cid int PRIMARY KEY COMMENT "课程ID",
    cname varchar(20) COMMENT "课程名",
    start_semester varchar(20) COMMENT "开课学期",
    cteacher_id int COMMENT "任课教师ID",
    CONSTRAINT fk_ctid_tid FOREIGN KEY (cteacher_id) REFERENCES teachers(tid)
);

-- 选修表
CREATE TABLE optional_courses  (
    sid int COMMENT "学号",
    cid int COMMENT "课程ID",
    score int,
    PRIMARY KEY(sid,cid),
    
    CONSTRAINT fk_osid_sid FOREIGN KEY (sid) REFERENCES students(sid),
    FOREIGN KEY (cid) REFERENCES courses(cid)
);


-- drop table optional_courses;

-- 教师表(teachers)添加部门(dept)字段
ALTER TABLE teachers ADD COLUMN dept VARCHAR(10);

-- 学生表(students)姓名(name) 数据类型varchar(8) 字段名改为学生姓名(sname)
ALTER TABLE students CHANGE `name` sname varchar(8) NOT NULL;


-- 教师表 的教师名字(tname)段数据 从 varchar(10)-> varchar(12)
ALTER TABLE teachers MODIFY COLUMN tname VARCHAR(12) UNIQUE;

-- 删除 教师(teachers)  部门(dept) 字段
ALTER TABLE teachers DROP COLUMN dept;

-- 删除 课程(courses) 任课教师(cteacher_id) 外键约束
AlTER TABLE courses DROP FOREIGN KEY fk_ctid_tid;

-- 教师表(teachers) 按职称(title)建立普通索引 
CREATE INDEX idx_teacher_title ON teachers(title);


-- 按教师名(tname)降序建立唯一索引
CREATE UNIQUE INDEX uk_teacher_tname ON teachers(tname DESC);

-- 删除 选修表(optinal_courses)
DROP TABLE optional_courses;