SQLserver创建表练习测验题.docx

1、SQLserver创建表练习测验题实验二 创建数据库和表/* 一、创建DB_student数据库*/*如果DB_student数据库存在，删除它*/Use masterIf 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 filesmicrosof

2、t sql servermssqldataDB_student_data.mdf,Size=5mb,Maxsize=50mb,Filegrowth=10%)Log on (name=DB_student_log, Filename=c:program filesmicrosoft sql servermssqldataDB_student_data.ldf, Size=5mb, Maxsize=25mb, Filegrowth=10%)Go/*在DB_student数据库中创建student表*/*打开DB_student数据库*/use DB_student Go /*在DB_student

3、数据库中，如果student表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(student)and OBJECTPROPERTY(id,IsUserTable)=1)drop table studentGo /*以下创建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

4、/*在DB_student数据库中创建course表*/*在DB_student数据库中，如果course数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(course)and OBJECTPROPERTY(id,IsUserTable)=1)drop table courseGo /*以下创建course 数据库表*/create table course ( cno char(4) Constraint PK_Course_cno primary key, cname char(40), cpno

5、 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 scGo /*以下创建sc 数据库表*/create table sc ( sno char(9),cno cha

6、r(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 studentValues (200215121,李勇,男,20,CS);Insert into studentValues (2002151

7、22,刘晨,女,19,CS);Insert into studentValues (200215123,王敏,女,18,MA);Insert into studentValues (200215125,张立,男,19,IS);/*为course数据表添加数据*/Insert into courseValues (0001,数据库,0005,4);Insert into courseValues (0002,数学,null,2);Insert into courseValues (0003,信息系统,0001,4);Insert into courseValues (0004,操作系统,0006

8、,3);Insert into courseValues (0005,数据结构,0007,4);Insert into courseValues (0006,数据处理,null,2);Insert into courseValues (0007,PASCAL语言,0006,4);Go/*为sc数据表添加数据*/Insert into scValues (200215121,0001,92);Insert into scValues (200215121,0002,85);Insert into scValues (200215121,0003,88);Insert into scValues

9、(200215122,0002,90);Insert into scValues (200215122,0003,80);Go/*二、创建jiaoxuedb(教学)数据库*/*如果jiaoxuedb数据库存在，删除它*/Use masterIf exists (select name from master.dbo.sysdatabases where name=jiaoxuedb) Drop database jiaoxuedbGo/*以下创建jiaoxuedb数据库*/Create database jiaoxuedbOn primary(name=jiaoxuedb_data,Filen

10、ame=c:program filesmicrosoft sql servermssqldatajiaoxuedb_data.mdf,Size=5mb,Maxsize=50mb,Filegrowth=10%)Log on (name=jiaoxuedb_log, Filename=c:program filesmicrosoft sql servermssqldatajiaoxuedb_data.ldf, Size=5mb, Maxsize=25mb, Filegrowth=10%)Go/*在jiaoxuedb数据库中创建S（学生表）*/*打开jiaoxuedb数据库*/use jiaoxue

11、dbGo /*在jiaoxuedb数据库中，如果S（学生表）存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(S)and OBJECTPROPERTY(id,IsUserTable)=1)drop table SGo /*以下创建S(学生表)数据库表*/Create table S(sno char(6) primary key,Sname char(8),Sex char(2),Age tinyint,Dept char(10);Go/*在jiaoxuedb数据库中创建C（课程表）*/*在jiaoxuedb

12、数据库中，如果C数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(C)and OBJECTPROPERTY(id,IsUserTable)=1)drop table CGo /*以下创建C（课程表） 数据库表*/create table C( cno char(5) primary key, cname char(20), chourtinyint, ccredit tinyint,);Go/*在jiaoxuedb数据库中创建SC（选课表）*/*在jiaoxuedbt数据库中，如果sc数据库表存在，就

13、删除*/if exists ( select * from dbo.sysobjects where id=object_id(sc)and OBJECTPROPERTY(id,IsUserTable)=1)drop table scGo /*以下创建SC 数据库表*/create table sc ( sno char(6) , cno char(5),score smallint,primary key (sno,cno);Go/*在jiaoxuedb数据库中创建T（教师表）*/*在jiaoxuedbt数据库中，如果T（教师表）数据库表存在，就删除*/if exists ( select

14、* from dbo.sysobjects where id=object_id(T)and OBJECTPROPERTY(id,IsUserTable)=1)drop table TGo/*以下创建T（教师表） 数据库表*/create table T ( tno char(6) primary key, Tname char(8), Sex char(2),Age tinyint,Prof char(10), /*职称*/Sal smallint, /*工资*/Comm smallint, /*岗位津贴*/Dept char(10) /*所在系*/);Go/*在jiaoxuedb数据库中创

15、建TC（任课表）*/*在jiaoxuedbt数据库中，如果TC数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(tc)and OBJECTPROPERTY(id,IsUserTable)=1)drop table tcGo /*以下创建TC 数据库表*/create table tc ( tno char(6) , cno char(5),primary key (tno,cno);Go/*为S（学生表）数据表添加数据*/Insert into sValues (001101,宋大方,男,19,计算机

16、);Insert into sValues (002102,李王,男,20,信息);Insert into sValues (991101,张彬,男,18,计算机);Insert into sValues (991102,王蕾,女,19,计算机);Insert into sValues (991103,张建国,男,18,计算机);Insert into sValues (991104,李平方,男,18,计算机);Insert into sValues (991201,陈东辉,男,19,计算机);Insert into sValues (991202,葛鹏,男,21,计算机);Insert in

17、to sValues (991203,潘桃芝,女,19,计算机);Insert into sValues (991204,姚一峰,男,18,计算机);Insert into sValues (001102,许辉,女,22,计算机);Insert into sValues (001201,王一山,男,20,计算机);Insert into sValues (001202,牛莉,女,19,计算机);Insert into sValues (002101,李丽丽,女,19,信息);Go/*为c（课程表）数据表添加数据*/Insert into cValues (01001,计算机基础,60,3);I

18、nsert into cValues (01002,程序设计,80,5);Insert into cValues (02003,数据结构,60,6);Insert into cValues (02001,数据库,80,6);Insert into cValues (02002,计算机网络,60,6);Insert into cValues (01003,微机原理,60,8);Insert into cValues (02004,操作系统,60,6);Insert into cValues (03001,软件工程,60,3);Insert into cValues (03002,大型数据库,48

19、,2);Insert into cValues (03003,图像处理,48,2);/*为sc数据表添加数据*/Insert into scValues (991101,01001,88);Insert into scValues (991102,01001,93);Insert into scValues (991103,01001,90);Insert into scValues (991101,01002,90);Insert into scValues (991102,01002,98);Insert into scValues (991103,01002,74);Insert int

20、o scValues (991104,01002,85);Insert into scValues (001201,01002,64);Insert into scValues (991104,02001,33);Insert into scValues (991104,01001,35);Insert into scValues (991201,01001,76);/*为T（教师表）数据表添加数据*/Insert into tValues (000006,许红霞,女,39,讲师,1100,1200,计算机);Insert into tValues (000007,许永军,男,57,教授,20

21、00,3000,计算机);Insert into tValues (000008,李桂青,女,65,教授,2000,3000,计算机);Insert into tValues (000009,王一凡,女,43,讲师,1200,1200,计算机);Insert into tValues (000010,田锋,男,33,助教,500,800,信息);Insert into tValues (000001,李英,女,39,副教授,1500,2000,信息);Insert into tValues (000002,张雪,女,51,教授,1900,3000,信息);Insert into tValues

22、 (000003,张朋,男,30,讲师,1000,1200,计算机);Insert into tValues (000004,王平,女,28,讲师,850,1200,信息);Insert into tValues (000005,李力,男,47,教授,1800,3000,计算机);/*为tc（任课表）数据表添加数据*/Insert into tcValues (000001,02001);Insert into tcValues (000008,02002);Insert into tcValues (000003,02001);Insert into tcValues (000011,020

23、03);Insert into tcValues (000001,01001);Insert into tcValues (000002,01002);Insert into tcValues (000002,01003);Insert into tcValues (000004,02002);Insert into tcValues (000005,01001);Insert into tcValues (000006,01002);Insert into tcValues (000003,01003);/* 三、创建jxsk(教学)数据库 */*如果jxsk数据库存在，删除它*/Use m

24、asterIf exists (select name from master.dbo.sysdatabases where name=jxsk) Drop database jxskGo/*以下创建jxsk数据库*/Create database jxskOn primary(name=jxsk_data,Filename=c:program filesmicrosoft sql servermssqldatajxsk_data.mdf,Size=5mb,Maxsize=50mb,Filegrowth=10%)Log on (name=jxsk_log, Filename=c:program

25、 filesmicrosoft sql servermssqldatajxsk_data.ldf, Size=5mb, Maxsize=25mb, Filegrowth=10%)Go/*在jxsk数据库中创建S1（学生表）*/*打开jxsk数据库*/use jxskGo /*在jxsk数据库中，如果S1（学生表）存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(S1)and OBJECTPROPERTY(id,IsUserTable)=1)drop table S1Go /*以下创建S1(学生表)数据库表*/

26、Create table S1(SNOchar(2) primary key,SNchar(8),SEXchar(2),AGE tinyint,DEPTchar(10);Go/*在jxsk数据库中创建C1（课程表）*/*在jxsk数据库中，如果C1数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(C1)and OBJECTPROPERTY(id,IsUserTable)=1)drop table C1Go /*以下创建C1（课程表） 数据库表*/create table C1( CNO char(2)

27、 primary key,CNchar(10), /* 课程名*/CTtinyint /* 课时数*/);Go/*在jxsk数据库中创建SC1（选课表）*/*在jxskt数据库中，如果SC1数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(sc1)and OBJECTPROPERTY(id,IsUserTable)=1)drop table sc1Go /*以下创建SC1 数据库表*/create table sc1 ( SNO char(2) ,CNO char(2),SCORE smallint,

28、primary key (SNO,CNO);Go/*在jxsk数据库中创建T1（教师表）*/*PROF列与COMM列之间地取值关系地CHECK约束为：*/*(PROF=教授AND COMM=4000) OR (PROF=副教授AND COMM=2000)OR */* (PROF=讲师AND COMM=1500)OR(PROF=助教AND COMM=1000) */*在jxsk数据库中，如果T1（教师表）数据库表存在，就删除*/if exists ( select * from dbo.sysobjects where id=object_id(T1)and OBJECTPROPERTY(id,IsUserTable)=1)drop table T1Go/*以下创建T1（教师表）数据库表*/create table T1 ( TNOchar(2) primary key, TNchar(8), /*教师名*/ SEX char(2),AGEtinyint,PROF char(10), /*职称*/SAL smallint, /*工资*/COMMsmallint, /*岗位津贴*/DEPT char(10)