SQL数据库复习

创建数据库

create database Students
on primary
(
name="Students_data",
filename="d:\Students_data.mdf",
size=5,
maxsize=50,
filegrowth=1)
log on
(
name="Students_log",
filename="d:\Students_log.ldf",
size=1,
filegrowth=10%
)
go

创建表

USE Students
go
create table StuInfo
(
StuID int not null,
StuName char(10) not null,
StuSex bit not null,
StuAge int not null,
)
go
create table StuExam
(
CourseNO int not null,--课程号
StuID int not null,
Score int not null,
)
go
create table CourseInfo
(
CourseNO int not null,
CourseName char(20) not null,
Marks int not null
)
go

建立约束

alter table StuInfo
add constraint PK_StuIN primary key(StuID)
alter table StuInfo
add constraint DF_StuSex default(1) for StuSex
alter table StuInfo
add constraint CK_StuAge check (StuAge>=15 and StuAge<=50)
go 
alter table CourseInfo
add constraint PK_CourseNO primary key(CourseNO)
alter table CourseInfo
add constraint UQ_CourseName unique (CourseName)
alter table CourseInfo
add constraint CK_Marks check (Marks>=1 and Marks<=5)
alter table CourseInfo
add constraint DF_Marks default(1) for Marks
go
alter table StuExam
add constraint FK_Course Foreign Key(CourseNO) references CourseInfo(CourseNO)
alter table StuExam
add constraint FK_StuID Foreign Key(StuID) references StuInfo(StuID)
alter table StuExam
add constraint CK_Score check (Score>=0 and Score<=100)
go

插入

insert into StuInfo(StuID,StuName,StuSex,StuAge) values(1,'猪八戒',1,20)
insert into CourseInfo(CourseNO,CourseName,Marks) values(1,'心理学',3)
insert into StuExam(CourseNO,StuID,Score)values(1,1,75)
insert into StuInfo(StuID,StuName,StuSex,StuAge) values(2,'张三',0,20)
insert into CourseInfo(CourseNO,CourseName,Marks) values(2,'数学',3)
insert into StuExam(CourseNO,StuID,Score)values(1,2,85)
insert into StuInfo(StuID,StuName,StuSex,StuAge) values(3,'理石',0,20)
insert into CourseInfo(CourseNO,CourseName,Marks) values(3,'计算机',4)
insert into StuExam(CourseNO,StuID,Score)values(2,1,65)


insert StuInfo(StuID,StuName,StuSex,StuAge) 
select 4,'孙悟空',1,20 union
select 5,'沙僧',1,20 union
select 6,'悟净',1,20 
go

查询

select * from StuInfo
select * from StuExam
select * from CourseInfo
go
select * from StuInfo where StuSex=0
go


select StuID from StuExam group by StuID having min(score)>=60


select StuID from StuExam group by StuID having count(StuID)>=2


select StuID from StuInfo where StuID not in(select StuInfo.StuID From StuInfo,StuExam where StuInfo.StuID=StuExam.StuID)


select StuExam.StuID from StuInfo,StuExam where StuInfo.StuID=StuExam.StuID group by StuExam.StuID having Sum(score)>100

修改

update StuExam set Score=Score+5 where Score between 65 and 75

删除

delete from StuExam where Score<=80

Alter Table 用法：

ALTER table StuExam add Stu int
ALTER TABLE StuExam change StuExam to Atu
ALTER TABLE StuExam modify Stu char
ALTER table StuExam  alter column Stu char


alter table StuExam drop Stu