数据库

create database test2009
go
use test2009
go
create table Orders
(
OrderID	char(5) not null,
OrderName	varchar(30) not null,
OrderDate smalldatetime not null,
RequiredDate smalldatetime null,
Amount Numeric(12,2) default (0) for [Amount],
constraint PK_Orders primary key(OrderID)
)
go
create table Product
(
ProductID char(5) not null,
ProductName varchar(50) not null,
SupplierName varchar(50) not null,
constraint PK_Product primary key(ProductID)
)
create table OrderDetails
(
OrderID char(5) not null,
ProductID char(5) not null,
UnitPrice Numeric(8,2) not null,
Quantity Smallint not null,
constraint PK_OrderDetails primary key(OrderID,ProductID),
constraint fk_orderID foreign key(OrderID) references Orders(OrderID),
constraint fk_productID foreign key(ProductID) references Product(ProductID)
)
go
create index idx_ordername on Orders(OrderName)
go
create view vw_statistics
as
select ProductID,sum(Quantity) 销售数量,SUM(Quantity*UnitPrice) 销售金额
from OrderDetails
group by ProductID
go







create database test2008
go
use test2008 
go
create table Department
(
DeptID char(6) not null,
DeptName varchar(50) null,
constraint PK_Department primary key(DeptID)
)
go
create table Class
(
ClassNo char(6) not null,
ClassName varchar(50) null,
AllNum int null check(AllNum<=30),
DeptID char(6) null,
constraint PK_Class primary key(ClassNo),
)
go
create table Student
(
SNo char(8) not null,
SName varchar(50) not null,
sex char(2) null,
ClassNo char(6) null,
constraint PK_Student primary key(SNo),
)
go
alter table Student
add constraint FK_Student_Class foreign key(ClassNo) references Class(ClassNo) on delete cascade 
alter table Class
add constraint FK_Class_Department foreign key(DeptID) references Department(DeptID) on delete cascade 
go
insert into Department values('D01','理学院')
insert into Department values('D02','经管学院')
insert into Department values('D03','体育学院')
insert into Department values('D04','信息学院')
insert into Department values('D05','软件学院')
go

insert into Class values('C01','信息与计算科学','28','D01')
insert into Class values('C02','光电信息','27','D01')
insert into Class values('C03','软件一班','22','D05')
insert into Class values('C04','计算机科学','26','D04')
insert into Class values('C05','会计一班','28','D02')
insert into Class values('C06','体育一班','16','D03')
insert into Class values('C07','软件二班','25','D05')
go
insert into Student values('S01','张三','男','C01')
insert into Student values('S02','张三','女','C04')
insert into Student values('S03','张三','男','C05')
insert into Student values('S04','张三','男','C06')
insert into Student values('S05','张三','男','C07')
insert into Student values('S06','张三','女','C03')
insert into Student values('S07','张三','女','C02')
insert into Student values('S08','张三','男','C04')
insert into Student values('S09','张三','女','C02')
insert into Student values('S10','张三','男','C03')
insert into Student values('S11','张三','男','C01')
insert into Student values('S12','张三','女','C07')
insert into Student values('S13','张三','男','C05')
insert into Student values('S14','张三','女','C06')
insert into Student values('S15','张三','男','C02')
insert into Student values('S17','张三','女','C01')
insert into Student values('S18','张三','男','C04')
insert into Student values('S19','张三','男','C03')
insert into Student values('S20','张三','女','C06')
insert into Student values('S21','张三','男','C07')
go

Alter table student
ADD column JoinYear char(4) 




Create database test2014
go
use test2014
go
create table TEACHERS
(
TNo char(6) not null,
TName varchar(50) not null,
Dept varchar(50) null,
ReNo char(6) not null,
constraint PK_TEACHERS primary key (TNo)
)
go
Create table REMUNERATIONS
(
ReNo char(6) not null,
Salary Numeric(8,2) not null,
Constraint PK_REMUNERATIONS primary key(ReNo)
)
go
create table COURSES
(
CNo char(6) not null,
CName varchar(50) not null,
CTime int null,
TNo char(6) not null,
constraint PK_COURSES primary key(CNo),
constraint FK_COURSES_TEACHERS foreign key(TNo) references TEACHERS(TNo)
)
go
insert into TEACHERS values('T01','张三','计算机','Z01')
insert into TEACHERS values('T02','李四','物理','Z02')
insert into TEACHERS values('T03','王五','数学','Z02')
insert into TEACHERS values('T04','赵六','计算机','Z03')
go
insert into REMUNERATIONS values('Z01','80')
insert into REMUNERATIONS values('Z02','60')
insert into REMUNERATIONS values('Z03','40')
go
insert into COURSES values('C01','数据库','64','T01')
insert into COURSES values('C02','大学物理','56','T02')
insert into COURSES values('C03','高等数学','80','T03')
insert into COURSES values('C04','操作系统','60','T04')
insert into COURSES values('C05','线性代数','50','T03')
insert into COURSES values('C06','微积分','48','T03')
insert into COURSES values('C07','数据结构','60','T04')
go

orders.doc
数据库设计文档

基本信息：
计算机全名：WIN-20161229QGK\SQLEXPRESS
数据库名称：test2014
Windows登录方式
连接字符串：“server=WIN-20161229QGK\SQLEXPRESS;database=test2014;integrated security=true;”
数据库创建：
Create database test2014
go
use test2014
go
create table TEACHERS
(
TNo char(6) not null,
TName varchar(50) not null,
Dept varchar(50) null,
ReNo char(6) not null,
constraint PK_TEACHERS primary key (TNo)
)
go
Create table REMUNERATIONS
(
ReNo char(6) not null,
Salary Numeric(8,2) not null,
Constraint PK_REMUNERATIONS primary key(ReNo)
)
go
create table COURSES
(
CNo char(6) not null,
CName varchar(50) not null,
CTime int null,
TNo char(6) not null,
constraint PK_COURSES primary key(CNo),
constraint FK_COURSES_TEACHERS foreign key(TNo) references TEACHERS(TNo)
)
go
insert into TEACHERS values(‘T01’,‘张三’,‘计算机’,‘Z01’)
insert into TEACHERS values(‘T02’,‘李四’,‘物理’,‘Z02’)
insert into TEACHERS values(‘T03’,‘王五’,‘数学’,‘Z02’)
insert into TEACHERS values(‘T04’,‘赵六’,‘计算机’,‘Z03’)
go
insert into REMUNERATIONS values(‘Z01’,‘80’)
insert into REMUNERATIONS values(‘Z02’,‘60’)
insert into REMUNERATIONS values(‘Z03’,‘40’)
go
insert into COURSES values(‘C01’,‘数据库’,‘64’,‘T01’)
insert into COURSES values(‘C02’,‘大学物理’,‘56’,‘T02’)
insert into COURSES values(‘C03’,‘高等数学’,‘80’,‘T03’)
insert into COURSES values(‘C04’,‘操作系统’,‘60’,‘T04’)
insert into COURSES values(‘C05’,‘线性代数’,‘50’,‘T03’)
insert into COURSES values(‘C06’,‘微积分’,‘48’,‘T03’)
insert into COURSES values(‘C07’,‘数据结构’,‘60’,‘T04’)
go

readme.txt

1.附加数据库文件test2014.mdf
2.sql文件夹下为数据库所有的操作
3.Orders.doc为数据库设计文档
4.修改DBHelperSQL.cs文件下的连接字符串使之适合你的计算机
5.backupinfo.bak为数据库备份文件
6.工作环境SQL Server R2 2008 ,VS2010
7.连接字符串："server=<你的计算机全名>;database=test2014;integrated security=true;"