SQL serer 创建表练习.doc

实验二 创建数据库和表 /* 一、创建DB_student数据库 */ /*如果DB_student数据库存在，删除它*/ Use master If exists (select name from master.dbo.sysdatabases where name=DB_student) Drop database DB_student Go /* 以下创建DB_student数据库*/ Create database DB_student On primary (name=DB_student_data, Filename=c:\program files\microsoft sql server\mssql\data\DB_student_data.mdf, Size=5mb, Maxsize=50mb, Filegrowth=10%) Log on (name=DB_student_log, Filename=c:\program files\microsoft sql server\mssql\data\DB_student_data.ldf, Size=5mb, Maxsize=25mb, Filegrowth=10%) Go /* 在DB_student数据库中创建student表 */ /*打开DB_student数据库*/ use DB_student Go /*在DB_student数据库中，如果student表存在，就删除*/ if exists ( select * from dbo.sysobjects where id=object_id(student) and OBJECTPROPERTY(id,IsUserTable)=1) drop table student Go /*以下创建student 数据库表*/ create table Student ( sno char(9) Constraint PK_student_sno primary key, Sname char(20), ssex char(2), sage smallint, sdept char(20) ); Go /* 在DB_student数据库中创建course表 */ /*在DB_student数据库中，如果course数据库表存在，就删除*/ if exists ( select * from dbo.sysobjects where id=object_id(course) and OBJECTPROPERTY(id,IsUserTable)=1) drop table course Go /*以下创建course 数据库表*/ create table course ( cno char(4) Constraint PK_Course_cno primary key, cname char(40), cpno char(4), ccredit smallint, /*Foreign Key cpno References Course (cno)*/ ); Go /* 在DB_student数据库中创建SC表 */ /*在DB_student数据库中，如果SC数据库表存在，就删除*/ if exists ( select * from dbo.sysobjects where id=object_id(sc) and OBJECTPROPERTY(id,IsUserTable)=1) drop table sc Go /*以下创建sc 数据库表*/ create table sc ( sno char(9), cno char(4), grade smallint, Constraint PK_sc_sno_cno primary key (sno,cno), Constraint RFK_sc_sno Foreign Key (sno) References student (sno), Constraint RFK_sc_cno Foreign Key (cno) References Course (cno) ); Go /* 为student数据表添加数据 */ Insert into student Values (200215121,李勇,男,20,CS); Insert into student Values (200215122,刘