在D盘创建数据表

--在D盘创建学生表
CREATE DATABASE DB_STUDENTS
ON
(NAME='STUDENT_1_MDF',FILENAME='D:/DB_STUDENTS/DB_STUDENTS_1_1.MDF',SIZE=5MB,FILEGROWTH=50%,MAXSIZE=2GB),
FILEGROUP DB_STUDENTS_NDF
(NAME='STUDENT_1_NDF',FILENAME='D:/DB_STUDENTS/DB_STUDENTS_1_1.NDF',SIZE=5MB,FILEGROWTH=20%,MAXSIZE=2GB)
LOG ON
(NAME='STUDENT_1_LOG',FILENAME='D:/DB_STUDENTS/DB_STUDENTS_1_LOG.LDF');

--修改事物日志文件大小
ALTER DATABASE DB_STUDENTS MODIFY FILE (NAME='STUDENT_1_LOG',SIZE=5MB);

--修改数据文件大小
ALTER DATABASE DB_STUDENTS MODIFY FILE (NAME='STUDENT_1_MDF',SIZE=10MB,FILEGROWTH=20%);

--文件组的维护(增加文件组)
ALTER DATABASE DB_STUDENTS ADD FILEGROUP DB_STUDENTS_FG_2;

--在文件组中增加数据文件
ALTER DATABASE DB_STUDENTS
ADD FILE
(NAME='STUDENT_2_NDF',FILENAME='D:/DB_STUDENTS/DB_STUDENTS_1_2.NDF',SIZE=5MB,FILEGROWTH=20%,MAXSIZE=UNLIMITED)
TO FILEGROUP DB_STUDENTS_FG_2

--专业表
CREATE TABLE TB_MAJOR
(
 MAJ_KEY UNIQUEIDENTIFIER NOT NULL
  CONSTRAINT PK_TB_MAJOR PRIMARY KEY, --专业KEY
 MAJ_NAME NVARCHAR(20) NOT NULL   --专业名称
)

--修改表中字段的键值
ALTER TABLE TB_MAJOR ALTER COLUMN MAJ_KEY UNIQUEIDENTIFIER NOT NULL
  CONSTRAINT PK_TB_MAJOR PRIMARY KEY;

ALTER TABLE TB_MAJOR DROP CONSTRAINT PK_TB_MAJOR;

--班级表
create table TB_CLASSES
(
 CLS_KEY UNIQUEIDENTIFIER NOT NULL
  CONSTRAINT PK_TB_CLASSES PRIMARY KEY, --班级KEY
 CLS_ID NVARCHAR(10) NOT NULL
  CONSTRAINT UQ_TB_CLASSES_CLS_ID UNIQUE, --班级编号
 CLS_NAME NVARCHAR(20) NOT NULL,    --班级名称
 MAJ_KEY UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_TB_CLASSES_MAJ_KEY_TB_MAJOR_MAJ_KEY
   FOREIGN KEY REFERENCES TB_MAJOR(MAJ_KEY),   --专业KEY
 DIRCTORY NVARCHAR(20) NULL     --班主任姓名
)

INSERT INTO TB_CLASSES(CLS_KEY,CLS_ID,CLS_NAME,MAJ_KEY,DIRCTORY) VALUES (NEWID(),'20070101','MICROSOFT_ONE_CLASSES','71533B23-5D01-4AE5-9DBC-859C17465D82','张浩');
INSERT INTO TB_CLASSES(CLS_KEY,CLS_ID,CLS_NAME,MAJ_KEY,DIRCTORY) VALUES (NEWID(),'20070102','MICROSOFT_TWO_CLASSES','71533B23-5D01-4AE5-9DBC-859C17465D82','郭姗姗');
INSERT INTO TB_CLASSES(CLS_KEY,CLS_ID,CLS_NAME,MAJ_KEY,DIRCTORY) VALUES (NEWID(),'20070103','MICROSOFT_THREE_CLASSES','71533B23-5D01-4AE5-9DBC-859C17465D82','高洪福');

 

--学生表
CREATE TABLE TB_STUDENTS
(
 STU_KEY UNIQUEIDENTIFIER NOT NULL
  CONSTRAINT PK_TB_STUDENTS PRIMARY KEY,  --学生KEY
 CLS_KEY UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_TB_STUDENTS_CLS_ID_TB_CLASSES_CLS_ID
  FOREIGN KEY REFERENCES TB_CLASSES(CLS_KEY), --班级KEY
 STU_ID NVARCHAR(12) NOT NULL
  CONSTRAINT UQ_TB_STUDENTS_STU_ID UNIQUE, --学生ID
 STU_NAME NVARCHAR(20) NOT NULL,     --学生名称
 STU_GENDER NVARCHAR(1) NOT NULL CONSTRAINT CK_TB_STUDENTS_STU_GENDER
  CHECK (STU_GENDER IN('男','女')),    --学生性别
 STU_BIRTHDATE DATETIME NULL      --出生日期
)

select * from TB_CLASSES;

INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200701001','迟伟','男','1988-01-01');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200701002','迟晓燕','女','1987-02-02');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200701003','迟明超','男','1986-03-03');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200701004','周封建','男','1985-04-04');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200701005','石振祥','男','1984-05-05');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200702001','张荣贵','男','1988-01-01');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200702002','邓波','女','1987-02-02');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200702003','王森','男','1986-03-03');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200702004','卢少军','男','1985-04-04');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200702005','唐伟','男','1984-05-05');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200703001','姜玉','女','1988-01-01');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200703002','马青','女','1987-02-02');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200703003','刘凡','男','1986-03-03');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200703004','江南','男','1985-04-04');
INSERT INTO TB_STUDENTS(STU_KEY,CLS_KEY,STU_ID,STU_NAME,STU_GENDER,STU_BIRTHDATE) VALUES(NEWID(),'FA0078B7-0BE4-4ABE-8464-46E60E135011','200703005','江北','男','1984-05-05');

--科目表
CREATE TABLE TB_SUBJECT
(
 SUB_KEY UNIQUEIDENTIFIER NOT NULL
  CONSTRAINT PK_TB_SUBJECT_SUB_KEY PRIMARY KEY, --科目KEY
 SUB_NAME NVARCHAR(20) NOT NULL,      --科目名称
 SUB_CUTOFFSCORES FLOAT NOT NULL DEFAULT 60,   --及格线
 SUB_EXCELLENT FLOAT NOT NULL DEFAULT 85,   --优秀线
 SUB_CREDIT FLOAT NOT NULL DEFAULT 0.2    --学分
)

ALTER TABLE TB_SUBJECT ALTER COLUMN SUB_NAME NVARCHAR(50);

INSERT INTO TB_SUBJECT(SUB_KEY,SUB_NAME) VALUES(NEWID(),'MICROSOFT SQL SERVER 2005');
INSERT INTO TB_SUBJECT(SUB_KEY,SUB_NAME) VALUES(NEWID(),'MICROSOFT VISUAL C#');
INSERT INTO TB_SUBJECT(SUB_KEY,SUB_NAME) VALUES(NEWID(),'MICROSOFT VISUAL C++');
INSERT INTO TB_SUBJECT(SUB_KEY,SUB_NAME) VALUES(NEWID(),'MICROSOFT VISUAL STIDIO');

--科目表
CREATE TABLE TB_SCORES
(
 STU_KEY UNIQUEIDENTIFIER NOT NULL CONSTRAINT TB_SCORES_STU_KEY_TB_STUDENTS_STU_KEY
  FOREIGN KEY REFERENCES TB_STUDENTS(STU_KEY),     --学生KEY
 SUB_KEY UNIQUEIDENTIFIER NOT NULL CONSTRAINT TB_SCORES_SUB_KEY_TB_SUBJECT_SUB_KEY
  FOREIGN KEY REFERENCES TB_SUBJECT(SUB_KEY),      --科目KEY
 SCORE FLOAT NOT NULL CONSTRAINT CK_TB_SCORES_SCORE CHECK(SCORE>=0), --成绩
 CONSTRAINT PK_TB_SCORES PRIMARY KEY (STU_KEY,SUB_KEY)    --学生KEY,科目KEY
)

INSERT INTO TB_MAJOR(MAJ_KEY,MAJ_NAME) VALUES (NEWID(),'软件开发');
INSERT INTO TB_MAJOR(MAJ_KEY,MAJ_NAME) VALUES (NEWID(),'建筑');
INSERT INTO TB_MAJOR(MAJ_KEY,MAJ_NAME) VALUES (NEWID(),'会计学');
INSERT INTO TB_MAJOR(MAJ_KEY,MAJ_NAME) VALUES (NEWID(),'工商管理');
INSERT INTO TB_MAJOR(MAJ_KEY,MAJ_NAME) VALUES (NEWID(),'机械');