SQL Server 数据库基本操作语句总结

–创建数据库

create database Studets

–创建表

create table student ( sno char(5), sname char(20), ssex char(2), sage smallint, sdept char(15) )

create table course ( cno char(3), cname char(30), cpno char(3), ccredit smallint )

create table sc ( sno char(5), cno char(3), grade int )

–查看表信息

select * from student select sno as 学号 from student select * from course select * from sc

–修改表

–插入列

alter table student add  scome  datetime

–修改列的字段类型 alter table student alter column scome  char(50)

–删除 –删除列

alter table student drop column scome

–删除表 drop table student drop table course drop table sc

–完整性约束实现

–sno 非空唯一，ssex检查约束， sage默认大小

create table student ( sno char(5) not null unique, sname char(20), sex char(2), sage smallint default 20, sdept char(15), constraint sex check(sex in(‘男’,’女’)), )

–删除表的约束 alter table student drop  constraint ssex

–添加字段约束 alter table student add constraint ssex check(sex in(‘男’,’女’))

–添加主键约束 alter table student add constraint PK_SNO primary key(sno) create table course ( cno char(3) not null unique, cname char(30), cpno char(3), ccredit smallint )

–关联表主键已经存在，可以如下操作添加主键和外键约束

alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFERENCES sc(cno)

create table sc

(

sno char(5) foreign key references student(sno),

cno char(3) foreign key references course(cno),

grade int,

constraint PK_SC primary key(sno,cno)

)

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]

–创建sc后，通过如下修改主外键

alter table sc add constraint PK_SC primary key(sno,cno),

constraint FK_SNO foreign key(sno) references student(sno),

constraint FK_CNO foreign key(cno) references course(cno)

–创建索引。