Studentdb

USE [master]
GO

CREATE DATABASE [studentdb] ON PRIMARY
( NAME = N'studentdb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\studentdb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'HHIT_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\studentdb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS

GO

use [studentdb]

GO


CREATE TABLE Students
(
studentID INTEGER NOT NULL PRIMARY KEY,
studentName VARCHAR(20),
);

GO
/* 学生表 */
INSERT INTO Students
(studentID, studentName)
VALUES (1, 'Susan Powell');

INSERT INTO Students
(studentID, studentName)
VALUES (2, 'Bob Dawson');

INSERT INTO Students
(studentID, studentName)
VALUES (3, 'Howard Mansfield');

INSERT INTO Students
(studentID, studentName)
VALUES (4, 'Susan Pugh');

INSERT INTO Students
(studentID, studentName)
VALUES (5, 'Joe Adams');

INSERT INTO Students
(studentID, studentName)
VALUES (6, 'Janet Ladd');

INSERT INTO Students
(studentID, studentName)
VALUES (7, 'Bill Jones');

INSERT INTO Students
(studentID, studentName)
VALUES (8, 'Carol Dean');

INSERT INTO Students
(studentID, studentName)
VALUES (9, 'Allen Thomas');

INSERT INTO Students
(studentID, studentName)
VALUES (10, 'Val Shipp');

INSERT INTO Students
(studentID, studentName)
VALUES (11, 'John Anderson');

INSERT INTO Students
(studentID, studentName)
VALUES (12, 'Janet Thomas');

GO
/* 课程表 */
CREATE TABLE Courses
(
courseID Integer NOT NULL PRIMARY KEY,
courseName VARCHAR(20),
numCredits SmallINT
);

GO

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (100, 'History', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (101, 'Math', 4);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (102,'English', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (103, 'Computer Science', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (104, 'biology', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (105, 'GIS', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (106, 'chemistry', 4);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (107, 'geography', 3);
INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (108, 'politics', 3);

INSERT INTO Courses
(courseID, courseName, numCredits)
VALUES (109, 'art', 3);
/* 部门表*/
CREATE TABLE Dp
(
DpID Integer NOT NULL PRIMARY KEY,
DpName VARCHAR(20),
Dpposition VARCHAR(20)
);
INSERT INTO Dp
(DpID, DpName, Dpposition)
VALUES (1, 'finance', '土木楼101');
INSERT INTO Dp
(DpID, DpName, Dpposition)
VALUES (2, 'educational', '土木楼301');
INSERT INTO Dp
(DpID,

DpName, Dpposition)
VALUES (3, 'publicity', '土木楼405');
INSERT INTO Dp
(DpID, DpName, Dpposition)
VALUES (4, 'science', '土木楼302');
INSERT INTO Dp
(DpID, DpName, Dpposition)
VALUES (5, 'arts', '土木楼205');
INSERT INTO Dp
(DpID, DpName, Dpposition)
VALUES (6, 'studentteach', '土木楼201');

/* 教师表*/


CREATE TABLE teachers
(
teacherID Integer NOT NULL PRIMARY KEY,
teacherName VARCHAR(20),
DpID Integer NOT NULL ,
FOREIGN KEY (DpID) REFERENCES dp(DpID)on delete cascade on update cascade

);
GO
INSERT INTO Teachers
(teacherID, teacherName, dpID)
VALUES (1001, 'Dr. Horn', 1);

INSERT INTO Teachers
(teacherID, teacherName,dpID)
VALUES ( 1002, 'Dr. Lowe', 2);

INSERT INTO Teachers
(teacherID, teacherName, dpID)
VALUES (1003, 'Dr. Engle', 3);

INSERT INTO Teachers
(teacherID, teacherName,dpID)
VALUES (1004, 'Ms. Cooke', 5);

INSERT INTO Teachers
(teacherID, teacherName,dpID)
VALUES (1005, 'Dr. Olsen', 5);

INSERT INTO Teachers
(teacherID, teacherName, dpID)
VALUES (1006, 'Ds. Scango', 6);

INSERT INTO Teachers
(teacherID, teacherName,dpID)
VALUES (1007, 'Dr. Wright', 2);
/* 上课表*/

CREATE TABLE class
(
courseID INTEGER NOT NULL ,
teacherID INTEGER NOT NULL,
tmaterials varchar(20),
PRIMARY KEY (courseID,teacherID),
FOREIGN KEY (courseID) REFERENCES Courses(courseID)on delete cascade on update cascade
,
FOREIGN KEY (teacherID) REFERENCES Teachers(teacherID)on delete cascade on update cascade

);

INSERT INTO class (courseID,teacherID, tmaterials) VALUES (101, 1001, '线性代数');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (102, 1002, '大学英语');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (105, 1003, 'GIS应用');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (103, 1004, '计算机应用');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (107, 1002, '地理应用');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (108, 1005, '现代政治');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (109, 1003, '世界艺术');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (104, 1005, '未来生物');
INSERT INTO class (courseID,teacherID, tmaterials) VALUES (106, 1005, '海洋化学');
/*成绩表*/
CREATE TABLE CS
(
courseID Integer NOT NULL ,
studentID INTEGER NOT NULL,
score integer,
PRIMARY KEY (courseID,studentID),
FOREIGN KEY (courseID) REFERENCES Courses(courseID)on delete cascade on update cascade
,
FOREIGN KEY (studentID) REFERENCES Students(studentID)on delete cascade on update cascade

);

INSERT INTO CS (courseID,studentID, score) VALUES (102, 2, 95);
INSERT INTO CS (courseID,studentID, score) VALUES (102, 3, 48);
INSERT INTO CS (courseID,studentID, score) VALUES (101, 2, 70);
INSERT INTO CS (courseID,studentID, score) VALUES (103, 5, 90);

INSERT INTO CS (courseID,studentID, score) VALUES (104, 2, 95);
INSERT INTO CS (courseID,studentID, score) VALUES (105, 1, 60);
INSERT INTO CS (courseID,studentID, score) VALUES (106, 3, 45);
INSERT INTO CS (courseID,studentID, score) VALUES (107, 7, 75);
INSERT INTO CS (courseID,studentID, score) VALUES (109, 5, 56);
INSERT INTO CS (courseID,studentID, score) VALUES (108, 11, 48);
INSERT INTO CS (courseID,studentID, score) VALUES (109, 12, 95);
INSERT INTO CS (courseID,studentID, score) VALUES (109, 11, null);
/*用户表*/
CREATE TABLE USERS
(
USERNAME VARCHAR(20) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR(20)
);
INSERT INTO USERS (USERNAME,PASSWORD) VALUES ('xing','qazwsx')
go