SQL Server 常用语句(example)
1.1建立数据库
create database Student_info;
1.2 建立数据库并配置相关参数
use master
go
create database Student_info
on primary
(
name = 'Student_info_data', --数据库逻辑文件名称
filename = 'F:\sqlserversamples\Student_info_data.mdf', --数据库物理文件名称
size = 20MB, --数据文件初始大小
maxsize = 300MB, --数据文件大小的最大值
filegrowth = 5MB --数据文件增长增量
)
log on
(
name = 'Student_info_log', --日志逻辑文件名称
filename = 'F:\sqlserversamples\Student_info_log.ldf', --日志物理文件名称
size = 5MB, --日志文件初始大小
maxsize = 50MB, --日志文件大小最大值
filegrowth = 1MB --日志文件增长增量
)
2．查看、验证创建的数据库
sp_helpdb Student_info;
3. 修改数据库的属性
3.1 修改数据文件
use master
go
alter database Student_info
modify file
(
name = Student_info_data,
size = 30MB,
maxsize = 300MB,
filegrowth = 5%
)
go
3.2修改日志文件
use master
go
alter database Student_info
modify file
(
name = Student_info_log,
size = 20MB,
maxsize = 30MB,
filegrowth = 6%
)
go
4. 数据库的分离及附加
use master
go
sp_detach_db Student_info
go
create database Student_info
on primary
(filename = 'F:\sqlserversamples\Student_info_data.mdf')
for attach
go
4.1 分离数据
use master
go
sp_detach_db Student_info
go
4.2附加
exec sp_attach_db@dbname = N'Student_info',
@filename1 = N'F:\sqlserversamples\Student_info_data.mdf',
@filename2 = N'F:\sqlserversamples\Student_info_log.ldf'
5.创建表
use Student_info
go
create table Student
(
Sno char(8) primary key,
Sname varchar(8) not null,
Sex char(2) default '男' notnull,
Birth smalldatetime not null,
Classno char(3) not null,
Entrance_date smalldatetime not null,
Home_addr varchar(40),
);
create table Course
(
Cno char(3)primary key,
Cname varchar(20) not null,
Total_perior smallint,
Credit tinyint,
constraint Check_course check (Total_perior>0 and Credit between 0 and 6),
);
create table SC
(
Sno char(8),
Cno char(3),
Grade tinyint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno),
check (Grade between 0 and 100),
);
6. 在Student表中添加列Stature和Postcode
alter table Student add Stature numeric(4,2);
alter table Student add constraint check_stature check(Stature<3);
或
alter table Student add Stature numeric(4,2)check(Stature<3);
alter table Student add Postcode char(6) check (Postcode like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]');
7.删除列Stature
alter table Student drop constraint check_stature;
alter table Student drop column Stature;
NOTE: 以上仅为本人参考书本及网上材料后的整理归纳，参考教材为雷景生、叶文珺和楼越焕编著的《数据库原理及应用（第2版）》。后续会整理对表的增加、修改、删除和查询数据。
