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

--sql基本操作

--创建数据库

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)

--创建索引。