正文描述:《数据库实验二 通过sql语句创建与管理数据表》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、实验二通过SQL语句创建与管理数据表一、实验目的(1)掌握查询分析器的使用。(2)掌握通过SQL语句创建表的方法。(3)掌握通过SQL语句修改表结构的方法。(4)掌握通过SQL语句添加、修改、删除表数据的方法。二、实验内容1、通过SQL语句删除表用SQL语句在数据库StudentStudent_info中删除实验一创建的studentStudent表、courseCourse表、SC表。droptableSC_20103322droptableStudent_20103322droptableCourse_201
2、033222、通过SQL语句创建表用SQL语句在数据库StudentStudent_info中创建实验一中的studentStudent表、courseCourse表、SC表,结构如实验一中表2、表3、表4所示表2:createtableStudent_20103322(Snochar(8)notnullprimarykey,--学号loanapprovalandpostcreditapprovalofficer/atalllevelsinaccordancewithcreditapprovalrules,lic
3、ensingandeventualexerciseofcreditdecisionpowerofpersonsorinstitutions.Reviewfindingsandreviewcomments,accordingtotheBank'screditSnamevarchar(8)notnull,--学生姓名Sexchar(2)notnulldefault'男',--性别Birthsmalldatetimenotnull,--出生年月Classnochar(3)notnull,--班级号Entrance_da
4、tesmalldatetimenotnull,--入学时间Homeaddrvarchar(40)notnull,--家庭住址)表3:createtableCourse_20103322(Cnochar(3)notnullprimarykey,--课程号Cnamevarchar(20)notnull,--课程名称Total_periorsmallint,--总学时Credittinyint,--学分check(Total_perior>3andCredit>0andCredit<=6))loanapprovalan
5、dpostcreditapprovalofficer/atalllevelsinaccordancewithcreditapprovalrules,licensingandeventualexerciseofcreditdecisionpowerofpersonsorinstitutions.Reviewfindingsandreviewcomments,accordingtotheBank'scredit表4:createtableSC_20103322(primarykey(Sno,Cno),Snochar(
6、8)notnullforeignkeyreferencesStudent_20103322(Sno),--学号Cnochar(3)notnullforeignkeyreferencesCourse_20103322(Cno),--课程号Gradetinyint,--成绩check(Grade>=0andGrade<=100))loanapprovalandpostcreditapprovalofficer/atalllevelsinaccordancewithcreditapprovalrules,licensi
7、ngandeventualexerciseofcreditdecisionpowerofpersonsorinstitutions.Reviewfindingsandreviewcomments,accordingtotheBank'scredit3、通过SQL语句管理表结构(1)添加和删除列a.给studentStudent表增加身高(以米单位)statureStature列,类型为numeric(4,2),允许为空值,且身高值需小于3.0米。altertableStudent_20103322addStatu
8、renumeric(4,2),constraintck_Staturecheck(Stature<3.0)b.给studentStudent表增加所在系Sdept列,字符型,长度2,不允许为空值。altertableStudent_20103322addSdeptchar(8)notnullc.给studentStudent表增加邮政篇码Postcode列,字符型,长度为
显示全部收起