SQL：第二课

[color=blue]第二章：[/color][img]http://dl.iteye.com/upload/attachment/0083/3149/d6c44cf3-5926-3038-9be7-c6ea90c57dca.png[/img]

[color=blue]第三章：[/color][img]http://dl.iteye.com/upload/attachment/0083/3151/3239ceba-d200-3812-b8db-f9088e34f00b.png[/img]

[color=blue]第二章作业：简易大学教学管理数据库
[/color]
-- 建立Instant数据库
CREATE DATABASE instant;

-- 连接到Instant数据库
USE instant;

-- 创建教授表
CREATE TABLE i_professor(
pk_professorID INT PRIMARY KEY NOT NULL,
p_name VARCHAR(50) NOT NULL
);

-- 创建课程表
CREATE TABLE i_course(
pk_courseID INT PRIMARY KEY NOT NULL,
c_name VARCHAR(50),
c_credits INT -- 学分
);

-- 创建教室表
CREATE TABLE i_room(
pk_roomID INT PRIMARY KEY NOT NULL,
r_comments VARCHAR(50) , -- 教室名称
r_capacity INT -- 教室可以容纳的人数
);

-- 创建班级表
CREATE TABLE i_class(
pk_classID INT PRIMARY KEY NOT NULL,

fk_c_courseID INT NOT NULL, -- 建立外键
CONSTRAINT fk1_cl FOREIGN KEY (fk_c_courseID)
REFERENCES i_course(pk_courseID), -- 建立外键约束

fk_c_professorID INT NOT NULL,
CONSTRAINT fk2_cl FOREIGN KEY (fk_c_professorID)
REFERENCES i_professor(pk_professorID),

fk_c_roomID INT NOT NULL,
CONSTRAINT fk3_cl FOREIGN KEY (fk_c_roomID)
REFERENCES i_room(pk_roomID),

c_time VARCHAR(50)
);

-- 创建学生表
CREATE TABLE i_student(
pk_studentID INT PRIMARY KEY NOT NULL,
s_name VARCHAR(50)
);

-- 创建考试信息表
CREATE TABLE i_exam(
pk_examID INT PRIMARY KEY NOT NULL,

fk_e_courseID INT NOT NULL,
CONSTRAINT fk1_ex FOREIGN KEY (fk_e_courseID)
REFERENCES i_course(pk_courseID),

fk_e_professorID INT NOT NULL,
CONSTRAINT fk2_ex FOREIGN KEY (fk_e_professorID)
REFERENCES i_professor(pk_professorID),

e_sustainedon DATETIME,-- 考试时间

e_comments VARCHAR(255)-- 本次考试的说明
);

-- 创建学生参加班级表
CREATE TABLE i_enrollment(
pk_enrollmentID INT PRIMARY KEY NOT NULL,

fk_e_studentID INT NOT NULL,
CONSTRAINT fk1_en FOREIGN KEY (fk_e_studentID)
REFERENCES i_student(pk_studentID),

fk_e_classID INT NOT NULL,
CONSTRAINT fk2_en FOREIGN KEY (fk_e_classID)
REFERENCES i_class(pk_classID),

e_enrolldeon DATETIME,-- 学生参加班级的时间

e_grade INT -- 班级得分
);

-- 创建学生考试信息表
CREATE TABLE i_studentExam(
pk_fk_studentID INT PRIMARY KEY NOT NULL,
CONSTRAINT fk1_st FOREIGN KEY (pk_fk_studentID)
REFERENCES i_student(pk_studentID),

fk_f_examID INT NOT NULL,
CONSTRAINT fk2_st FOREIGN KEY (fk_f_examID)
REFERENCES i_exam(pk_examID),

f_mark INT NOT NULL, -- 课程考试分数

f_ifpassed DATETIME,

f_comments VARCHAR(225) -- 考试情况评价
);

-- 删除Instant数据库
DROP DATABASE instant;

[color=blue]第三章作业：练习创建各类约束和默认值[/color]
CREATE DATABASE company;

USE company;

CREATE TABLE department(
pk_deptID INT PRIMARY KEY NOT NULL,
d_deptname VARCHAR(50) NOT NULL,
d_describe VARCHAR(500)
);

CREATE TABLE employee(
pk_employeeID INT PRIMARY KEY NOT NULL,
fk_e_deptID INT NOT NULL,
CONSTRAINT fk_em FOREIGN KEY (fk_e_deptID)
REFERENCES department(pk_deptID),
e_employeeName VARCHAR(50) NOT NULL,
e_gender CHAR(2) NOT NULL DEFAULT '男',
e_birthday DATETIME NOT NULL,
e_idcard CHAR(18) NOT NULL UNIQUE,
e_degree VARCHAR(12) NOT NULL,
e_graduateSchool VARCHAR(50) NOT NULL,
e_graduteDate DATETIME,
e_specialty VARCHAR(50) NOT NULL,
e_telephone VARCHAR(200) NOT NULL,
e_salay SMALLINT,
e_allowance DECIMAL(18,2),
e_skill VARCHAR(2000)
);

-- 必须要先删除建立外键约束的表,才能再删被指向的表
DROP TABLE employee;

DROP TABLE department;

DROP DATABASE company;