数据表的创建和管理精品文稿.ppt

《数据表的创建和管理精品文稿.ppt》由会员分享，可在线阅读，更多相关《数据表的创建和管理精品文稿.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据表的创建和管理第1页，本讲稿共39页学习要点：q表的概念q表的创建、修改和删除q添加、更新与删除表中的数据q数据完整性的概念及实施方法 第2页，本讲稿共39页3.1 表的概念n表是数据的集合，是用来存储数据和操作数据的逻辑结构。n表是由行和列组成的，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性。n在特定表中，列名必须是唯一的，但相同的列名可以在数据库中的不同表中使用。nSQL Server中的每个表中最多允许有1024列，每行最多允许有8060字节的用户数据。行和列的次序是任意的。第3页，本讲稿共39页3.2 3.2 数据类型数据类型 第一大类:整数数据bit:b

2、it:bit数据类型代表0,1或NULL,就是表true,false.占用1byte.int:int:以4个字节来存储正负数.可存储范围为:-（-2147483648至2147483647）.smallint:smallint:以2个字节来存储正负数.存储范围为:-（-32768至32767）tinyint:tinyint:是最小的整数类型,仅用1字节,范围:0至 255 bigintbigint：以8个字节存储，是SQL Server 2000引入的全新的数据类型，可以存储范围：（-263到263-1）第4页，本讲稿共39页第二大类:精确数值数据 u numeric:numeric:表示的数

3、字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化.格式写为numeric(p，s)例如：numeric(10，4)，表示共有10位数，其中整数6位，小数4位。u decimal:decimal:和numeric类似。u 区别：numeric类型的列可以带有IDENTITY关键字（唯一标识符）。第5页，本讲稿共39页第三大类:近似浮点数值数据 float:float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308.real:real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38 第四大类:日期时间数据 dat

4、atime:datatime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节.smalldatetime:smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.第6页，本讲稿共39页第五大类:字符串数据 char（n）:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上.例如：char（10）Varchar（n）:变长字符型数据,最短为1字节,最长为8000个字节,尾部的空白会去掉.text:长专门用于存储数量庞大的变长字符数据，,最长可以存放2G（231-1

5、个字符）的数据.第7页，本讲稿共39页第六大类:Unincode字符串数据 nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存双字节字符,nchar(m)中的m代表的含义是用来定义字符数据的最大长度.nvarchar:可变长度,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节.ntext:可变长度,最短为1字节,最长为230-1个unicode数据.尾部的空白会去掉,储存一个字符需要2个字节.第8页，本讲稿共39页第七大类:货币数据类型 money:记录金额范围为:-92233720368577.5808至922337203

6、68577.5807.需要8 个字节.smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.第八大类:标记数据 timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.第9页，本讲稿共39页第九大类:二进制码字符串数据 binary:固定长度的二进制码字符串字段,最短为1,最长为8000.varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉image:为可变长度的二进制码字符串,最长2G.第10页，本讲稿共39页3.3 表结构的建立和删除1、表的建立

7、 表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。n在SQL Server 2005中，一个数据库中最多可以创建20亿个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。第11页，本讲稿共39页一般的SQL建表语句形式nCREATE TABLE 表名 表约束n(列名1 数据类型 缺省值1，列约束1n 列名2 数据类型 缺省值2，列约束2 n 列名n 数据类型 缺省值n，列约束n）第12页，本讲稿共39页例例3-1 3-1 创建三张表，表结构如下：创建三张表，表结构如下：列名描

8、述数据类型约束默认值SnoSnameSsexSageSdept 学号姓名性别年龄所在系字符型，长度为7字符型，长度为10字符型，长度为2微整数字符型，长度20主码非空男Student表结构第13页，本讲稿共39页Course表结构列名说明数据类型约束默认值CnoCnameCcreditCSemester 课程号课程名学分学期字符型，长度为10字符型，长度为20微整数微整数主码非空 3第14页，本讲稿共39页 列名说明数据类型约束SnoCnoGrade学号课程号成绩字符型，长度为7字符型，长度为10浮点数主码，引用Student的外码主码，引用Course的外码Sc表结构第15页，本讲稿共39页

9、CREATE TABLE Student (Sno CHAR(7)PRIMARY KEY，Sname CHAR(10)NOT NULL,Ssex CHAR(2)DEFAULT 男,Sage TINYINT,Sdept CHAR(20),)第16页，本讲稿共39页CREATE TABLE Course (Cno CHAR(10)PRIMARY KEY，Cname CHAR(20)NOT NULL,Ccredit TINYINT DEFAULT 3,Semester TINYINT,)第17页，本讲稿共39页CREATE TABLE Sc (Sno CHAR(7)，Cno CHAR(10),Gra

10、de FLOAT，PRIMARY KEY（Sno，Cno），FOREIGN KEY（Sno）REFERENCES Student（Sno），FOREIGN KEY（Cno）REFERENCES Course（Cno）第18页，本讲稿共39页n2、表的删除 删除表的命令一般形式nDROP TABLE 表名n例3-2 删除教师表 DROP TABLE teacher第19页，本讲稿共39页3.4 表结构的扩充和修改 1、修改表的命令一般形式 ALTER TABLE 表名 ADD 列名 数据类型 NULL/NOT NULL|ALTER COLUMN 列名 数据类型 NULL|NOT NULL|DRO

11、P COLUMN 列名 第20页，本讲稿共39页n例3-3 在student中增加住址列 ALTER TABLE student ADD address CHAR（50）n例3-4 修改student表中的sdept字段，将数据类型长度为30 ALTER TABLE student ALTER COLUMN sdept char(30)n例3-5 把Student表中的Sdept列删除。ALTER TABLE Student DROP COLUMN Sdept第21页，本讲稿共39页2添加主关键字 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(主键

12、列)3、删除主关键字 ALTER TABLE 表名 DROP CONSTRAINT 约束名3.补充定义外来关键字 ALTER TABLE 表名-1 ADD CONSTRAINT 约束名 FOREIGN KEY(外来关键字名)REFERENCES(表名-2)5删除外来关键字 ALTER TABLE 表名 DROP 外来关键字名第22页，本讲稿共39页例3-6 补充定义student表的主关键字。ALTER TABLE Student ADD CONSTRAINT pk_student PRIMARY KEY(Sno)例3-7、删除course表中的主关键字。ALTER TABLE course

13、DROP pk_course例3-8、修改sc表，在sno字段上创建外键约束。ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY(sno)REFERENCES student(sno)第23页，本讲稿共39页例3-9、删除sc表中sno字段上的外键约束 ALTER TABLE sc DROP fk_sc_student例3-10、向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Scomedate DATETIME例3-11、将年龄的数据类型改为半字长整数。ALTER TABLE

14、 Student ALTER COLUMN Sage SMALLINT第24页，本讲稿共39页3.5 数据完整性n数据完整性是指存储在数据库中的数据的一致性和准确性。数据完整性技术是衡量数据库质量好坏的重要标准。在Microsoft SQL Server系统中，提供了一系列实现数据完整性的方法，这些方法主要包括主键约束、外键约束、非空约束、惟一约束、默认值约束、检查约束以及规则和默认对象。第25页，本讲稿共39页分类：q实体完整性 q参照完整性q域完整性n 实体完整性又称为行完整性。这里的实体是指表中的记录，一个实体就是表的一条记录。实体完整性要求在表中不能存在完全相同的记录，而且每条记录都要

15、具有一个非空且不重复的主键值。第26页，本讲稿共39页参照完整性：一般指多个实体或关系表之间的关联关系，在关系数据库中用外码（Foreign key）来实现参照完整性。域完整性也称用户定义的完整性或语义完整性。用户定义的完整性实际上就是指明关系中属性的取值范围，也就是属性的域，即限制关系中属性的取值类型及取值范围，防止属性的值与应用语义矛盾。第27页，本讲稿共39页n约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的途径。n约束分类：q主键约束q外键约束q唯一值约束q检查约束q默认约束第28页，本讲稿共39页1、唯一性约束（UNIQUE）nUNIQUE 用来限制不受主

16、键约束的列上的数据的唯一性。n一个表可以放置多个UNIQUE约束n语法：nCONSTRAINT constraint_name UNIQUE(列名)第29页，本讲稿共39页例3-12 为student表添加身份证号列id，并为id列添加唯一值约束。ALTER TABLE student ADD id BIGINT CONSTRAINT unq_id UNIQUE注：UNIQUE约束列允许有NULL值，但最多只能有一个NULL值，否则认为重复。例3-13 为course表的cname列添加唯一值约束。ALTER TABLE course ADD CONSTRAINT unq_cname UNIQ

17、UE(cname)第30页，本讲稿共39页2、检查约束（CHECK）nCHECK 约束用来指定某列的可取值范围。通过限制输入值强制控制域的完整性。n语法：nCONSTRAINT constraint_name CHECK(检查条件)例3-14 限制student表中的ssex列只能添入值为男或女。ALTER TABLE studentADD CONSTRAINT check_ssex CHECK(ssex=男 or ssex=女)第31页，本讲稿共39页3、默认约束（DEFAULT）nDEFAULT约束用于给表中指定列赋予一个常量值（默认值）。n语法：nCONSTRAINT 约束名 DEFAU

18、LT 默认值 FOR 列名n例3-15 为student中的sage列添加默认值“19”。ALTER TABLE student ADD CONSTRAINT default_sage DEFAULT 19 FOR sage第32页，本讲稿共39页上机实践（独立完成实验）实验目的：本章实验主要目的是学习正确创建表；掌握修改表结构的方法；正确删除表。实验内容：自定义数据类型。分别用企业管理器和查询分析器创建表。分别用企业管理器和查询分析器修改表。分别用企业管理器和查询分析器删除表。第33页，本讲稿共39页 实验题目：题目一：在学生数据库student中创建学生个人信息数据表studentinfo

19、 题目要求分析：studentinfo数据表中包含以下信息：学号、姓名、性别、年龄、班级、地址以及联系方式等，如下表所示。第34页，本讲稿共39页字段名称字段名称 字段说明字段说明 数据类型数据类型 长度长度属性属性student_id 学号学号CHAR8主键主键student_name 姓名姓名 CHAR10不允许空不允许空student_sex 性别性别CHAR2允许空允许空year_old 年龄年龄TINYINT允许空允许空address 地址地址CHAR80允许空允许空student_region 籍贯籍贯CHAR10允许空允许空student_phone 联系电话联系电话 CHAR1

20、3允许空允许空第35页，本讲稿共39页 题目二：修改个人信息数据表studentinfo，完成如下操作：删除year_old字段。向该表中添加一列，列名为email，数据类型为char，长度40，允许空。n题目要求分析：根据题目一创建的个人信息数据表studentinfo，可以使用企业管理器和Transact-SQL语句来实现。第36页，本讲稿共39页 题目三：删除个人信息数据表studentinfo。题目要求分析：将题目一中创建的个人信息数据表studentinfo删除，可以使用企业管理器和Transact-SQL语句来轻松实现。第37页，本讲稿共39页 选作题目 创建图书数据库book，并

21、将数据文件和事务日志以1MB的幅度增长，将文件大小限制为2000MB。在book数据库中创建图书信息数据表，表名为“bookinfo”，表的数据结构如下表所示，数据表中包括图书的基本信息，如编号、书名、书作者和书价等，要求编号、书名、书作者不允许为空，并且将书价的默认值设置为20，同时为“bookinfo”表添加主码约束，其主码列为：book_id。第38页，本讲稿共39页字段名称字段名称 字段说明字段说明 数据类型数据类型 长度长度约束约束默认值属默认值属性性 book _id 编号编号 CHAR10主码主码book _name 书名书名 CHAR30不允许空不允许空book _author 书作者书作者 CHAR10不允许空不允许空book _price 书价书价 SMALLMONEY 允许空允许空20 book _publisher 出版社出版社 CHAR30允许空允许空book _date 出版日期出版日期 SMALLDATETIME 允许空允许空book _synopsis部门部门 VARCHAR 100允许空允许空book_remark 备注备注 VARCHAR 100允许空允许空第39页，本讲稿共39页