SQL Server数据库创建基础

一

/*创建数据库*/

create database XSGL

on primary

(

name=xsgl1,

filename='d:/xsgl1.mdf',

size=3,

maxsize=100,

filegrowth=1

),

filegroup xinjian/*创建自定义文件组*/

(

name=xsgl2,

filename='d:/xsgl2.ndf',

size=3,

maxsize=50,

filegrowth=1

)

log on

(

name=xsgl_log,

filename='d:/xsgl_log.ldf',

size=2,

maxsize=100,

filegrowth=2

)

二

/*在XSGL 数据库中创建表*/

use XSGL/*在数据库中创建表*/

go

create table Student

(

Sno char(10) constraint pk_Sno primary key,/*创建主键约束*/

Sname char(8),

Sex char(2) constraint ch_sex check(Sex in ('男','女')),/*创建检查约束，限制输入的范围*/

Sbirthday datetime,

Sdep char(20)

)

create table Cource

(

Cno char(10) constraint pk_Cno primary key,

Cname char(30) constraint un_Cnome unique,

Ccredit real

)

create table Score

(

Sno char(10)constraint fk_Sno foreign key references Student(Sno),/*创建外键约束*/

Cno char(10) constraint fk_Cno foreign key references Cource(Cno),/*创建外键约束*/

Grade real constraint ch_Grade check(Grade>0 and Grade<100)/*创建检查约束，限制输入的范围*/

)

三

/*修改表的属性*/

use XSGL

go

alter table Score

alter column Sno char(10) not null

alter table Score

alter column Cno char(10) not null/*将Score中的Sno，Cno中的两列改为不允许为空*/

四

/*将同一表中的多列设为主键*/

use XSGL

alter table Score

add constraint pk_Sno_Cno primary key(Sno,Cno)/*将Score表中的Sno,Cno两列设为主键*/

五

/*增加或删除表中的一个字段*/

use XSGl

go

alter table Student

add meno varchar(200)/*给表增加一个新字段*/

/****************/

use XSGL

alter table Student

drop column meno/*删除Student中的meno字段*/

六

/*向表中插入数据*/

use XSGL

insert into Student

values('1001','张三','男','1989-12-29','三好学生')

insert into Student

values('1002','张婉','女','1990-5-3','三好学生')

insert into Student

values('1003','李四','男','1985-5-6','优秀班干部')

insert into Student

values('1004','王浩','男','1868-9-24','班主任')/*向表中插入数据*/

/******************************/

use XSGL

insert into Cource

values('10001','语文','56.5')

insert into Cource

values('10002','数学','78.6')

insert into Cource

values('10003','英语','65.36')/*向表中插入数据*/

/*************************/

use XSGL

go

insert into Score

values('1001','10001','56.5')

insert into Score

values('1002','10002','59.94')

insert into Score

values('1003','10003','65.6')/*向表中插入数据*/