--在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(),'机械');