use master
go
--判断数据库是否存在，存在则删除
if exists(select * from sysdatabases where name='MySchool') --判断MySchool是否存在
drop DATABASE MySchool --如果数据库存在则删掉数据库
GO
--创建数据库
create DATABASE MySchool
on
PRIMARY --创建主数据库文件
(
name='MyScool',
filename='E:\DataBase\Test\MySchool\MySchool.mdf',
size=5MB,
Maxsize=20MB,
FileGrowth=1MB
)
log on --创建日志文件
(
name='MySchoolLog',
filename='E:\DataBase\Test\MySchool\MySchool.ldf',
size=5MB,
maxsize=20MB,
FileGrowth=1MB
)
GO
use MySchool
go
--------------------------创建表----------------------------------
--创建表Student
IF EXISTS (SELECT * FROM sysobjects WHERE name='Student' )
DROP TABLE Student
GO
create table Student
(
StudentNo nvarchar(50) not null,
LoginPwd nvarchar(20) not null,
StudentName nvarchar(50) not null,
Sex char(2) not null,
GradeId int not null,
Phone nvarchar(20) not null,
Address nvarchar(255) null,
BornDate datetime null,
Email nvarchar(50) null
)
GO
--创建表Subject
IF exists (select * from sysobjects where name='Subject')
Drop table Subject
Go
create table Subject
(
SubjectId int identity(1,1) not null,
SubjectName nvarchar(20) not null,
ClassHour int not null,
GradeId int not null
)
go
--创建表Result
If exists(select * from sysobjects where name='Result')
drop table Result
GO
create table Result
(
Id int identity(1,1) not null,
StudentNo nvarchar(50) not null,
SubjectId int not null,
StudentResult int not null,
ExamDate smalldatetime not null
)
go
--创建年级表Grade
if exists(select * from sysobjects where name='Grade')
drop table Grade
go
create table Grade
(
GradeId int identity(1,1) not null,
GradeName nvarchar(50) not null
)
------------------------------------------------------------------
-------------------------为表添加约束-----------------------------
--为年级表Grade表添加约束
alter table Grade --主键约束
add constraint PK_Grade_GradeId Primary key(GradeId)
--为Student表添加约束
alter table Student --主键约束
add constraint PK_Student_StudentNo Primary key(StudentNo)
alter table Student --长度约束
add constraint CK_Student_LoginPwd check( len (LoginPwd)>=6)
alter table Student --密码默认约束
add constraint DF_Student_LoginPwd default ('123456') for LoginPwd
alter table Student --地址默认约束
add constraint DF_Student_Address default ('学生宿舍') for Address
alter table Student --检查约束
add constraint CK_Student_sex check (sex = ('男') or sex = ('女'))
alter table Student --外键约束
add constraint FK_Student_GradeId
foreign key (GradeId) references Grade(GradeId)
go
alter table Student --Email约束
add constraint CK_Student_Email check (email like ('%@%'))
--为Subject表添加约束
alter table Subject --主键约束
add constraint PK_Student_SubjectId Primary key(SubjectId)
alter table Subject --大于0
add constraint CK_Subject_ClassHour check (ClassHour > 0)
--为Result表添加约束
alter table Result --主键约束
add constraint PK_Result_Id Primary key(Id)
alter table [dbo].[Result] --必须在0~100之间
add constraint CK_Result_StudentResult check(StudentResult > 0 and StudentResult < 100)
exec sp_helpconstraint 'Student' --查询所有约束
----------------------添加数据------------------------------------
--为Grade表添加数据
insert Grade(GradeName) --使用insert...select语句一次插入多行数据
select 'S1' union
select 'S2' union
select 'Y2'
--为Student表添加数据
insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email)
values('S1201302001','zhangsan','张三','男',1,'01062768866','解放路','1991-1-1','[email protected]')
insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)
values('S1201302002','lisi89','李四','男',1,'13812345678','长江路路','1996-2-1')
insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)
values('S1201302003',default,'王五','男',1,'13912345678',default,'1995-5-3')
insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email)
values('S1201302004','wanglili','王丽丽','女',1,'1311245678',default,'1994-6-2','[email protected]')
insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)
values('S1201302005',default,'张峰','男',1,'1321234678',default,'1991-8-8')
------------------------------------------------------------------
use MySchool
go
-------------------------修改-------------------------------------
--根据学号修改地址
update Student
set Address = '山东省济南市文化路1号院'
where StudentNo = 'S1201302004'
--根据学号修改年级
update Student
set GradeId=2
where StudentNo='S1201302005'
--根据课程修改学时
update Subject
set ClassHour='55'
where SubjectName='走进Java编程世界'
--根据考试时间加分
update Result
set StudentResult=StudentResult+5
where StudentResult<60
--根据学号修改学生在特定时间考试的某一科分数
update Result
set StudentResult='55'
where StudentNo='S1201302002' and ExamDate='2013-10-18' and SubjectId=2
--将电子邮件为空的学生统一修改为"未知@"
update Student
set Email='未知@'
where Email is null
------------------------------------------------------------------
use MySchool
go
------------将表数据导向另一个不存在的表--------------------------
--通讯录表Address_IS(使用insret select语句,该表必须事先存在)
create table Address_IS
(
Id int Primary key identity(1,1) not null,
SName nvarchar(50) not null,
Phone nvarchar(255) not null,
Address nvarchar(255) null,
Email nvarchar(50) null
)
insert into Address_IS(SName,Phone,Address,Email)
select Student.StudentName,Student.Phone,Student.Address,Student.Email
from Student
--通讯录表Address_SI(使用select into语句,该表不能预先存在)
Select Student.StudentName,Student.Phone,Student.Address,Student.Email
into Address_SI
from Student
------------------------------------------------------------------
use MySchool
go
--------------------删除记录--------------------------------------
--删除1997年7月1日之后入学的学生
delete from Student
where BornDate>'1997-7-1'
------------------------------------------------------------------
use MySchool
go
------------向Subject表插入数据-----------------------------------
insert Subject(SubjectName, ClassHour, GradeId)
select '走进Java编程世界',40,1 union
select 'HTML和CSS网页技术',60,1 union
select 'C#语言和数据库技术',70,1
------------------------------------------------------------------
use MySchool
go
--------------------增加成绩纪录----------------------------------
insert Result(StudentNo, SubjectId, StudentResult, ExamDate)
select 'S1201302001',1,80,'2013-9-13' union
select 'S1201302002',1,45,'2013-9-13' union
select 'S1201302001',2,90,'2013-10-18' union
select 'S1201302002',2,60,'2013-10-18'
------------------------------------------------------------------
use MySchool
go
if exists(select * from sysobjects where name='Grade')
drop table Grade
if exists(select * from sysobjects where name='Result')
drop table Result
if exists(select * from sysobjects where name='Student')
drop table Student
if exists(select * from sysobjects where name='Subject')
drop table Subject
go
if exists(select * from databases where name='MySchool')
drop database MySchool
go