SQLserver创建表练习测验题.docx
- 文档编号:20129138
- 上传时间:2023-04-25
- 格式:DOCX
- 页数:23
- 大小:20.59KB
SQLserver创建表练习测验题.docx
《SQLserver创建表练习测验题.docx》由会员分享,可在线阅读,更多相关《SQLserver创建表练习测验题.docx(23页珍藏版)》请在冰豆网上搜索。
SQLserver创建表练习测验题
实验二创建数据库和表
/*一、创建DB_student数据库*/
/*如果DB_student数据库存在,删除它*/
Usemaster
Ifexists(selectnamefrommaster.dbo.sysdatabaseswherename='DB_student')
DropdatabaseDB_student
Go
/*以下创建DB_student数据库*/
CreatedatabaseDB_student
Onprimary
(name=DB_student_data,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\DB_student_data.mdf',
Size=5mb,
Maxsize=50mb,
Filegrowth=10%)
Logon
(name=DB_student_log,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\DB_student_data.ldf',
Size=5mb,
Maxsize=25mb,
Filegrowth=10%)
Go
/*在DB_student数据库中创建student表*/
/*打开DB_student数据库*/
useDB_student
Go
/*在DB_student数据库中,如果student表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('student')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptablestudent
Go
/*以下创建student数据库表*/
createtableStudent
(snochar(9)ConstraintPK_student_snoprimarykey,
Snamechar(20),
ssexchar
(2),
sagesmallint,
sdeptchar(20)
);
Go
/*在DB_student数据库中创建course表*/
/*在DB_student数据库中,如果course数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('course')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptablecourse
Go
/*以下创建course数据库表*/
createtablecourse
(cnochar(4)ConstraintPK_Course_cnoprimarykey,
cnamechar(40),
cpnochar(4),
ccreditsmallint,
/*ForeignKeycpnoReferencesCourse(cno)*/
);
Go
/*在DB_student数据库中创建SC表*/
/*在DB_student数据库中,如果SC数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('sc')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptablesc
Go
/*以下创建sc数据库表*/
createtablesc
(snochar(9),
cnochar(4),
gradesmallint,
ConstraintPK_sc_sno_cnoprimarykey(sno,cno),
ConstraintRFK_sc_snoForeignKey(sno)Referencesstudent(sno),
ConstraintRFK_sc_cnoForeignKey(cno)ReferencesCourse(cno)
);
Go
/*为student数据表添加数据*/
Insertintostudent
Values('200215121','李勇','男',20,'CS');
Insertintostudent
Values('200215122','刘晨','女',19,'CS');
Insertintostudent
Values('200215123','王敏','女',18,'MA');
Insertintostudent
Values('200215125','张立','男',19,'IS');
/*为course数据表添加数据*/
Insertintocourse
Values('0001','数据库','0005',4);
Insertintocourse
Values('0002','数学',null,2);
Insertintocourse
Values('0003','信息系统','0001',4);
Insertintocourse
Values('0004','操作系统','0006',3);
Insertintocourse
Values('0005','数据结构','0007',4);
Insertintocourse
Values('0006','数据处理',null,2);
Insertintocourse
Values('0007','PASCAL语言','0006',4);
Go
/*为sc数据表添加数据*/
Insertintosc
Values('200215121','0001',92);
Insertintosc
Values('200215121','0002',85);
Insertintosc
Values('200215121','0003',88);
Insertintosc
Values('200215122','0002',90);
Insertintosc
Values('200215122','0003',80);
Go
/*二、创建jiaoxuedb(教学)数据库*/
/*如果jiaoxuedb数据库存在,删除它*/
Usemaster
Ifexists(selectnamefrommaster.dbo.sysdatabaseswherename='jiaoxuedb')
Dropdatabasejiaoxuedb
Go
/*以下创建jiaoxuedb数据库*/
Createdatabasejiaoxuedb
Onprimary
(name=jiaoxuedb_data,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\jiaoxuedb_data.mdf',
Size=5mb,
Maxsize=50mb,
Filegrowth=10%)
Logon
(name=jiaoxuedb_log,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\jiaoxuedb_data.ldf',
Size=5mb,
Maxsize=25mb,
Filegrowth=10%)
Go
/*在jiaoxuedb数据库中创建S(学生表)*/
/*打开jiaoxuedb数据库*/
usejiaoxuedb
Go
/*在jiaoxuedb数据库中,如果S(学生表)存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('S')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableS
Go
/*以下创建S(学生表)数据库表*/
CreatetableS
(snochar(6)primarykey,
Snamechar(8),
Sexchar
(2),
Agetinyint,
Deptchar(10)
);
Go
/*在jiaoxuedb数据库中创建C(课程表)*/
/*在jiaoxuedb数据库中,如果C数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('C')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableC
Go
/*以下创建C(课程表)数据库表*/
createtableC
(cnochar(5)primarykey,
cnamechar(20),
chourtinyint,
ccredittinyint,
);
Go
/*在jiaoxuedb数据库中创建SC(选课表)*/
/*在jiaoxuedbt数据库中,如果sc数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('sc')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptablesc
Go
/*以下创建SC数据库表*/
createtablesc
(snochar(6),
cnochar(5),
scoresmallint,
primarykey(sno,cno)
);
Go
/*在jiaoxuedb数据库中创建T(教师表)*/
/*在jiaoxuedbt数据库中,如果T(教师表)数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('T')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableT
Go
/*以下创建T(教师表)数据库表*/
createtableT
(tnochar(6)primarykey,
Tnamechar(8),
Sexchar
(2),
Agetinyint,
Profchar(10),/*职称*/
Salsmallint,/*工资*/
Commsmallint,/*岗位津贴*/
Deptchar(10)/*所在系*/
);
Go
/*在jiaoxuedb数据库中创建TC(任课表)*/
/*在jiaoxuedbt数据库中,如果TC数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('tc')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptabletc
Go
/*以下创建TC数据库表*/
createtabletc
(tnochar(6),
cnochar(5),
primarykey(tno,cno)
);
Go
/*为S(学生表)数据表添加数据*/
Insertintos
Values('001101','宋大方','男',19,'计算机');
Insertintos
Values('002102','李王','男',20,'信息');
Insertintos
Values('991101','张彬','男',18,'计算机');
Insertintos
Values('991102','王蕾','女',19,'计算机');
Insertintos
Values('991103','张建国','男',18,'计算机');
Insertintos
Values('991104','李平方','男',18,'计算机');
Insertintos
Values('991201','陈东辉','男',19,'计算机');
Insertintos
Values('991202','葛鹏','男',21,'计算机');
Insertintos
Values('991203','潘桃芝','女',19,'计算机');
Insertintos
Values('991204','姚一峰','男',18,'计算机');
Insertintos
Values('001102','许辉','女',22,'计算机');
Insertintos
Values('001201','王一山','男',20,'计算机');
Insertintos
Values('001202','牛莉','女',19,'计算机');
Insertintos
Values('002101','李丽丽','女',19,'信息');
Go
/*为c(课程表)数据表添加数据*/
Insertintoc
Values('01001','计算机基础',60,3);
Insertintoc
Values('01002','程序设计',80,5);
Insertintoc
Values('02003','数据结构',60,6);
Insertintoc
Values('02001','数据库',80,6);
Insertintoc
Values('02002','计算机网络',60,6);
Insertintoc
Values('01003','微机原理',60,8);
Insertintoc
Values('02004','操作系统',60,6);
Insertintoc
Values('03001','软件工程',60,3);
Insertintoc
Values('03002','大型数据库',48,2);
Insertintoc
Values('03003','图像处理',48,2);
/*为sc数据表添加数据*/
Insertintosc
Values('991101','01001',88);
Insertintosc
Values('991102','01001',93);
Insertintosc
Values('991103','01001',90);
Insertintosc
Values('991101','01002',90);
Insertintosc
Values('991102','01002',98);
Insertintosc
Values('991103','01002',74);
Insertintosc
Values('991104','01002',85);
Insertintosc
Values('001201','01002',64);
Insertintosc
Values('991104','02001',33);
Insertintosc
Values('991104','01001',35);
Insertintosc
Values('991201','01001',76);
/*为T(教师表)数据表添加数据*/
Insertintot
Values('000006','许红霞','女',39,'讲师',1100,1200,'计算机');
Insertintot
Values('000007','许永军','男',57,'教授',2000,3000,'计算机');
Insertintot
Values('000008','李桂青','女',65,'教授',2000,3000,'计算机');
Insertintot
Values('000009','王一凡','女',43,'讲师',1200,1200,'计算机');
Insertintot
Values('000010','田锋','男',33,'助教',500,800,'信息');
Insertintot
Values('000001','李英','女',39,'副教授',1500,2000,'信息');
Insertintot
Values('000002','张雪','女',51,'教授',1900,3000,'信息');
Insertintot
Values('000003','张朋','男',30,'讲师',1000,1200,'计算机');
Insertintot
Values('000004','王平','女',28,'讲师',850,1200,'信息');
Insertintot
Values('000005','李力','男',47,'教授',1800,3000,'计算机');
/*为tc(任课表)数据表添加数据*/
Insertintotc
Values('000001','02001');
Insertintotc
Values('000008','02002');
Insertintotc
Values('000003','02001');
Insertintotc
Values('000011','02003');
Insertintotc
Values('000001','01001');
Insertintotc
Values('000002','01002');
Insertintotc
Values('000002','01003');
Insertintotc
Values('000004','02002');
Insertintotc
Values('000005','01001');
Insertintotc
Values('000006','01002');
Insertintotc
Values('000003','01003');
/*三、创建jxsk(教学)数据库*/
/*如果jxsk数据库存在,删除它*/
Usemaster
Ifexists(selectnamefrommaster.dbo.sysdatabaseswherename='jxsk')
Dropdatabasejxsk
Go
/*以下创建jxsk数据库*/
Createdatabasejxsk
Onprimary
(name=jxsk_data,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\jxsk_data.mdf',
Size=5mb,
Maxsize=50mb,
Filegrowth=10%)
Logon
(name=jxsk_log,
Filename='c:
\programfiles\microsoftsqlserver\mssql\data\jxsk_data.ldf',
Size=5mb,
Maxsize=25mb,
Filegrowth=10%)
Go
/*在jxsk数据库中创建S1(学生表)*/
/*打开jxsk数据库*/
usejxsk
Go
/*在jxsk数据库中,如果S1(学生表)存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('S1')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableS1
Go
/*以下创建S1(学生表)数据库表*/
CreatetableS1
(SNOchar
(2)primarykey,
SNchar(8),
SEXchar
(2),
AGEtinyint,
DEPTchar(10)
);
Go
/*在jxsk数据库中创建C1(课程表)*/
/*在jxsk数据库中,如果C1数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('C1')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableC1
Go
/*以下创建C1(课程表)数据库表*/
createtableC1
(CNOchar
(2)primarykey,
CNchar(10),/*课程名*/
CTtinyint/*课时数*/
);
Go
/*在jxsk数据库中创建SC1(选课表)*/
/*在jxskt数据库中,如果SC1数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('sc1')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptablesc1
Go
/*以下创建SC1数据库表*/
createtablesc1
(SNOchar
(2),
CNOchar
(2),
SCOREsmallint,
primarykey(SNO,CNO)
);
Go
/*在jxsk数据库中创建T1(教师表)*/
/*PROF列与COMM列之间地取值关系地CHECK约束为:
*/
/*(PROF=’教授’ANDCOMM=4000)OR(PROF=’副教授’ANDCOMM=2000)OR*/
/*(PROF=’讲师’ANDCOMM=1500)OR(PROF=’助教’ANDCOMM=1000)*/
/*在jxsk数据库中,如果T1(教师表)数据库表存在,就删除*/
ifexists(select*fromdbo.sysobjectswhereid=object_id('T1')
andOBJECTPROPERTY(id,'IsUserTable')=1)
droptableT1
Go
/*以下创建T1(教师表)数据库表*/
createtableT1
(TNOchar
(2)primarykey,
TNchar(8),/*教师名*/
SEXchar
(2),
AGEtinyint,
PROFchar(10),/*职称*/
SALsmallint,/*工资*/
COMMsmallint,/*岗位津贴*/
DEPTchar(10)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLserver 创建 练习 测验