第7章 管理表.doc

第7章 管理表教学目标l 理解表的特点、类型和创建表时需要考虑的因素l 熟练掌握创建和修改表技术l 不仅要理解标识符列的作用、特点和类型l 理解已分区表的特点和作用7.1 概述 7.1.1 表的特点 l 定义：表是用来组织和存储数据、具有行列结构的数据库对象。l 特点： 代表实体，有唯一的名字 由行和列组成 行和列的顺序是不重要的Student表:7.1.2 表的类型 表分为4种类型，即普通表、已分区表、临时表和系统表。7.1.3 设计表时应该考虑的因素 l 因素一，该表将要存储的数据对象，并且绘制出ER图。 l 因素二，表中将要包含的列，这些列的数据类型、精度等属性是什么？l 因素三，哪些列允许空值，哪些列不允许空值？l 因素四，是否使用主键，在何处使用主键？l 因素五，是否使用约束、默认值、规则，以及在何处使用这些对象？l 因素六，是否使用外键，在何处使用外键？l 因素七，是否使用索引，在何处使用索引，使用什么样的索引？7.2 创建和修改表 介绍:学校管理数据库Student:学生表列名类型特征备注snoChar(10)主键学号Class:班级表列名类型特征备注clsnoChar(10)主键班级编号teacher:教师表列名类型特征备注tnoChar(10)主键教师编号course:课程表列名类型特征备注cnoChar(10)主键课程编号Score:成绩表列名类型特征备注Id编号sno外键学号Cno外键课程号score成绩Mk_score补考成绩Tcc:授课表列名类型特征备注Idint主键编号date时间tno外键教师编号clsno外键班级编号cno外键课程编号value评价7.2.1创建表 l 1 在MS中完成student表的创建。l 2 使用CREATE TABLE语句创建表。l 语法：create table 表名(列名1 类型 约束 ,列名2 类型 约束 ,列名3 类型 约束 ,列名n 类型 约束)-教师表if exists(select name from sysobjectswhere name=teacher and type=u)drop table teacher-teacher表create table teacher(tno char(10) primary key ,-教师编号tname varchar(20) not null, -教师姓名title varchar(20), -职位birthdate datetime ,-出生日期hirdate datetime ,-受聘日期quitdate datetime,-离职时间uptno char(10) ,-上级领导gender char(2),-性别photo image,-照片notes ntext -备注)-班级表if exists(select name from sysobjectswhere name=class and type=u)drop table class-class 表格create table class(clsno char(10) primary key,-班级编号clsname char(20) ,-班级名称credate datetime,-创建日期leavedate datetime,-毕业时间teacher char(10),-班主任clstype varchar(20),-班级类型notes text, -备注numbers int -班级人数)-学生表if exists(select name from sysobjectswhere name=student and type=u)drop table student-student表create table student(sno char(10) primary key ,-学号sname varchar(20) not null,-姓名idcard char(18) ,-身份证gender char(2) ,-性别birthdate datetime ,-出生日期homeaddress varchar(100),-出生地phone varchar(20),-联系电话email varchar(50),-电子邮箱qq varchar(10) ,-qq号码hoby varchar(20),-爱好classid char(10) -班级)-课程表if exists(select name from sysobjectswhere name=course and type=u)drop table course-course表create table course( cno char(10) primary key,-课程编号cname varchar(50) ,-课程名称 c1_times int ,-理论课时 c2_times int ,-实践课时 ctype char(10),-学历课还是技能课nots text -备份)-学生成绩表if exists(select name from sysobjectswhere name=score and type=u)drop table scorecreate table score(id int IDENTITY(1,1) NOT NULL,-编号sno char(10) ,-学号cno char(10),-课程编号score decimal(5, 2) ,-成绩mk_score decimal (5, 2)补考成绩)-教师授课表if exists(select name from sysobjectswhere name=tcc and type=u)drop table tcccreate table tcc ( id nchar (10) ,-编号 date datetime ,-授课日期 tno char (10) ,-教师编号 clsno char (10) ,-班级编号 cno char (10) ,-课程编号 evalue varchar (50) -评价) -用户表CREATE TABLE users-用户表(id int IDENTITY(1,1) NOT NULL,-用户编号username varchar(50) ,-用户名password int NULL-密码)创建带有计算列的表格(1) SSMS中完成(2) 代码完成CREATE TABLE orders1 ( ord_id int NULL, goodsname varchar (50) , price money NULL, num int NULL, total AS ( price * num ) PERSISTED)创建自增列的表格（1） SSMS中完成（2） 代码完成CREATE TABLE orders2 ( ord_id int identity(1,2) not null, goodsname varchar (50) , price money NULL, num int NULL, total AS ( price * num ) PERSISTED)创建临时表临时表的前面以#或者#开头（1） SSMS中完成（2） 代码完成create table #test(a int , b int)对于约束的内容查看课本相关章节，后续会介绍。7.2.2修改表1增加列 表创建之后，用户可以根据需要使用ALTER TABLE语句来修改表的结构。语法：ALTER TABLE 表名 ADD字段名 数据类型(长度) null| not null default 默认值 identity注意：（1）新增加字段时可以同时设置空值约束、默认值约束和标识列。（2）表中的列名必须唯一（3）若不允许为空时则必须给新增加的列指定默认值，否则语句执行错误。ALTER TABLE 只允许添加满足下述条件的列: 列可以包含空值；或者列具有指定的DEFAULT 定义；或者要添加的列是标识列或时间戳列；或者，如果前几个条件均未满足，则表必须为空以允许添加此列。不能将列f添加到非空表test中，因为它不满足上述条件。例题1：创建测试表test，包含一个整形列a-创建表testcreate table test(a int)-（1）为表test添加新列b，类型为整形，可以为空alter table testadd b int-（2）为表test添加新列c，类型varchar(20),不允许空alter table testadd c varchar(20) not null给表中插入一行数据后再执行此操作：insert into test values(1,2,aaa);alter table testadd d varchar(20) not null消息4901，级别16，状态1，第1 行出现错误，思考为什么？如何改正？alter table testadd d varchar(20) not null default hehe-（3）为表test添加2列e和f,都是varchar(20)类型,允许空alter table testadd e varchar(20),f varchar(20)-（4）添加计算列,g=c+d+e。此处+的含义是字符串连接。alter table testadd g as c+d+e-（4）添加标识列alter table testadd h int identity(1001,1)看下面代码alter table testadd i int identity(1001,1) default 3消息1754，级别16，状态0，第1 行不能在具有IDENTITY 属性的列上创建默认值。表test，列i。消息1750，级别16，状态0，第1 行无法创建约束。请参阅前面的错误消息。为什么？2删除列 语法：alter table drop column 字段名 , n 如果要删除的列上建有约束，需要先删除约束删除约束的方法：（1）通过sp_helpconstraint 表名找到要删除约束的名字（2）alter table 表名 drop 约束名-(1)删除列alter table testdrop column f-(2)删除多列alter table testdrop column d,e-(3)删除带有约束列alter table testdrop column d消息5074，级别16，状态1，第1 行对象DF_test_d_239E4DCF 依赖于列d。消息4922，级别16，状态9，第1 行由于一个或多个对象访问此列，ALTER TABLE DROP COLUMN d 失败-先删除d上的默认约束,找到约束名sp_helpconstraint test注意：约束名随机生成，不同机器名称不同。-删除约束alter table testdrop DF_test_d_239E4DCF -删除列alter table testdrop column d7.2.3 修改列 使用alter column子句可修改字段的数据类型、长度、是否允许为空值等属性。语法： ALTER TABLE 表名 alter column 字段名 数据类型(长度)null|not null-(1)将class表中班级名称的长度改为20alter table classalter column clsname varchar(20)-(2)将教师姓名长度改为，并且非空alter table teacheralter column tname varchar(20) not null注意：（1）如果表中没有数据，对表中列的数据类型的更改是任意的。如果表中包含数据，更改后的类型必须同原类型兼容。（2）将一个原来允许为空值的列改为不允许为空时，必须保证表中已有记录中该列没有空值。（3）改变数据类型时，如果原来设置了默认值约束，一般应解除或删除约束后再修改-(3)将student中简历改名为简介语法：sp_rename 表名.原列名,新列名sp_rename student.简历,简介7.2.4 表格重命名 sp_rename 当前表名,新表名sp_rename test,te7.2.5 查看表的信息 -查看表格基本信息sp_help 表名sp_help student-查看表格占用空间信息sp_spaceused 表名sp_spaceused class-查看表格依赖关系sp_depends 表名sp_depends class7.2.6 删除表 l 删除表就是将表中数据和表的结构从数据库中永久性地去除。表被删除之后，就不能再恢复该表的定义。或者drop table testdrop table tb7.3 已分区表 l 如果一个表中包含了大量的、以多种不同方式使用的数据，且一般的查询不能按照预期的成本完成，则应该考虑使用已分区表。已分区表是指按照数据水平方式分区，将数据分布于一个数据库的多个不同的文件组中。在对数据进行查询或更新时，这些已分区表将被视为独立的逻辑单元。创建分区表的主要步骤创建分区表的主要步骤包括第一，创建分区函数，指定如何分区；第二，创建分区方案，指定分区函数的分区在文件组上的位置；第三，创建使用分区方案的表。l 可以使用CREATE PARTITION FUNCTION语句创建分区函数。 l 可以使用CREATE PARTITION SCHEME语句创建分区方案。 7.4 上机实验 l 在sales数据库中创建表下列表：（1）在sales数据库中通过SSMS创建表departments.(部门表)列名类型是否为空说明备注dnoChar(6)no主键编号dnameVarchar(20)no部门名称dnumintyes人数locateVarchar(20)yes地址（2）在sales数据库中通过SSMS创建表employees.(职工)列名类型是否为空说明备注EnoChar(6)no主键编号enamevarChar(8)no姓名SexChar(2)no性别IdChar(18)yes身份证deptnoChar(4)yes外键部门号Up_enoChar(6)yes外键上级领导编号TelVarchar(15)yes电话AddrVarchar(50)yes地址（3）在sales中使用命令