学生课程数据库（包含部分视图授权索引）

--创建数据库studentRD
create database studentRD
--使用数据库
use studentRD
--创建基本表

--drop database studentRD

--外键关联（参照完整性约束）
--foreign key(id) references outTable(id) on delete cascade on update cascade
--联合主键与外键关联须用以下格式
--CONSTRAINT 外键约束名 foreign key(roomNo,roomId) REFERENCES 外键依赖表/既联合主键表 (roomNo,roomId),


--1.学生表 student
create table student
(
	stuId char(9) not null unique,
	primary key (stuId),
	stuName nvarchar(4) not null,
	stuSex char(2) not null check(stuSex IN('男','女')),
	stuBirthday datetime,
	IDNumber char(18) unique,
	stuAddress nvarchar(32),
	stuPhone char(11) check(stuPhone like  '1[3578][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
	stuPostal char(6),
	stuPolitic nvarchar(4),
	stuResume text,
	retire char(2) check(retire IN('是','否')) default '否',
	suspension char(2) check(suspension IN('是','否')) default '否',
	roomNo nvarchar(6),
	roomId char(3),
	bedId smallint,
	CONSTRAINT FK_DORM FOREIGN KEY (roomNo,roomId) REFERENCES dorm(roomNo,roomId),
    classId char(6) foreign key references _class(classId)
)

SELECT * FROM student

--drop table student

--2.系表

create table department
(
	deptId char(4) primary key,
	deptName nvarchar(10),
	deptHead nvarchar(4),
	office nvarchar(10),
	deptPhone char(11) check(deptPhone like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
SELECT * FROM department

--3.班级表

create table _class
(
	classId char(6) primary key,
	className nvarchar(10),
	major nvarchar(10),
	classNumber smallint check(classNumber>=20 and classNumber<=70),
	IntoDate datetime,
	classRoom nvarchar(6),
	classHead nvarchar(4),
	monitor nvarchar(4),
	deptId char(4),
	foreign key (deptId) references department(deptId) on delete cascade on update cascade
)
SELECT * FROM _class

--4.课程表
create table lesson
(
	lessonId char(4) primary key,
	lessonName nvarchar(10),
	term nvarchar(4) check(term IN ('第一学期','第二学期')),
)
SELECT * FROM lesson

--5.教师表

create table teacher
(
	teacherId char(9) unique,
	primary key (teacherId),
	teacherName nvarchar(4),
	teacherSex char(2) not null check(teacherSex IN('男','女')),
	teaBirthday datetime,
	title nvarchar(10),
	deptId char(4),
	foreign key (deptId) references department(deptId) on delete cascade on update cascade
)
--6.宿舍表

create table dorm
(
	roomNo nvarchar(6),
	roomId char(3),
	primary key (roomNo,roomId),
	roomSex char(2) check(roomSex IN('男','女')),
	bedNumber smallint check(bedNumber=4 or bedNumber=6)
)

SELECT * FROM dorm

--7.选修表

create table otherLesson
(
	stuId char(9) not null unique,
	lessonId char(4),
	mark float check(mark>=0 and mark<=100),
	primary key (stuId,lessonId),
	foreign key (stuId) references student(stuId) on delete cascade on update cascade,
	foreign key (lessonId) references lesson(lessonId) on delete cascade on update cascade
)

SELECT * FROM otherLesson

--8.讲授表 

create table profess
(
	teacherId char(9),
	lessonId char(4),
	primary key (teacherId,lessonId),
	foreign key (teacherId) references teacher(teacherId) on delete cascade on update cascade,
	foreign key (lessonId) references lesson(lessonId) on delete cascade on update cascade
)

--创建视图
create view view_stuSexMan 
as select stuId,stuName,roomNo from student where stuSex='男'
select * from view_stuSexMan

--新建用户授权、移除授权
create login user1 with password='123456'

create user user1 for login user1

GRANT select,insert,update,delete ON teacher TO user1 WITH GRANT OPTION

REVOKE insert,delete ON teacher FROM user1 CASCADE

--创建索引
CREATE UNIQUE INDEX stuId_index ON student(stuId)

CREATE INDEX stuName_index ON student(stuName)