第5章 表的管理.ppt

《第5章 表的管理.ppt》由会员分享，可在线阅读，更多相关《第5章 表的管理.ppt（66页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第5章 表的管理12本章内容5.1 SQL Server表概述表概述 5.2 表的创建与维护表的创建与维护 5.3 表中数据的维护表中数据的维护 23学习要点基本知识设计表基本数据类型自定义数据类型表操作(SSMS方式和T-SQL方式)创建表、修改表查看表、删除表记录操作使用SSMS操作记录T-SQL插入、修改、删除记录45.1SQL Server表概述定义表的结构：DDL操纵表的数据：DML在关系数据库中，每一个关系都体现为一张二维表，使用表来存储和操作数据的逻辑结构，表是数据库中最重要的数据对象。表（Table）是按照行（Row）和列（Column）的格式组织和存储数据的数据库对象。表的操

2、作：5基本知识设计表：设计表：确定需要什么样的表，各表中都应该包括哪些数据以及各个表之间的关系和存取权限等等，这个过程称之为设计表 学习要点6基本知识设计表：设计表：需确定项目表中每一列的名称。表中每一列的名称。表中每一列的数据类型和宽度。表中每一列的数据类型和宽度。表中的列中是否允许空值。表中的列中是否允许空值。表中的列是否需要约束、默认设置或规则。表中的列是否需要约束、默认设置或规则。表是否需要约束。表是否需要约束。表所需要的索引的类型和需要建立索引的列。表所需要的索引的类型和需要建立索引的列。表间的关系，即确定哪些列是主键，哪些是外键表间的关系，即确定哪些列是主键，哪些是外键 学习要点7

3、基本数据类型基本数据类型：SQL Server 2005表中的每一列都必须指出该列可存储的数据类型 以下为SQL Server 2005常用的数据类型类型名称存储数据int整型数据（整型数据（32位）位）smallint整型数据（整型数据（16位）位）Tinyint整型数据（整型数据（8位）位）Bigint整型数据（整型数据（64位）位）Float浮点精度数字浮点精度数字Money货币货币数据（数据（64位）位）smallmoney货币货币数据（数据（32位）位）bit整数数据整数数据Decimalnumeric固定精度和固定精度和小数位的数字数据小数位的数字数据real浮点精度数字数据浮点精

4、度数字数据datetime日期和日期和时间时间数据数据精确到分精确到分钟钟smalldatetime日期和日期和时间时间数据数据char固定固定长长度的非度的非Unicode字符数据字符数据varchar可可变长变长度的非度的非Unicode数据数据text可可变长变长度的非度的非Unicode数据数据nchar固定固定长长度的度的Unicode数据数据nvarchar可可变长变长度度Unicode数据数据ntext可可变长变长度度Unicode数据数据数数据据类类型型简简介介81.字符数据类型 字符数据类型是使用最多的数据类型，可以用它来存储各种字母、数字符号、特殊符号。SQL Server

5、2005支持的字符数据类型有char、varchar、text、Nchar、nvarchar、ntext等六种。前三种是非unicode字符数据，后三种是unicode字符数据。95.1.1 5.1.1 基本数据类型基本数据类型 SQL Server2005支持整型、字符型、货币型和日期时间等多种基本数据类型。2.二进制数据类型二进制数据类型SQL Server用binary、varbinary和image三种数据类型存储二进制数据。103.日期和时间数据类型日期和时间数据类型 日期和时间数据类型用于存储日期和时间的结合体，SQL Server2005支持的日期时间数据类型有datetime、

6、smalldatetime等。4.逻辑数据类型逻辑数据类型 逻辑数据类型bit占用1个字节的存储空间，其值为0或1。如果输入0或1以外的值，将被视为1。114.1.2 4.1.2 基本数据类型基本数据类型5.整型数据类型整型数据类型 整型数据类型是最常用的数据类型之一。SQL Server2005支持的整数类型有Int、smallint、bigint和tinyint四种。126.浮点数据类型浮点数据类型 浮点数据类型用于存储十进制小数，SQL Server2005支持的浮点数据类型分为real、float、decimal和numeric等四种。137.货币数据类型 货币数据类型用于存储货币值，

7、在使用货币数据类型时，应在数据前加上货币符号。SQL Server2005支持money和smallmoney两种。148.其它数据类型其它数据类型 SQL Server2005中包含了一些用于数据存储的特殊数据类型。Timestamp：时间戳数据类型，用于自动记录插入或删除操作的时间。注意：服务器不允许用户指定时间戳值。sql_variant：可变数据类型。该类型的变量可用来存放大部分SQL Server数据类型的值，最大长度为8016字节，不支持text、ntext、timestamp和sql_variant类型。table：表类型。这是一种特殊的数据类型，存储供以后处理的结果集。uniq

8、ueidentifier：GUID类型(Global Unique IDentifier,全局惟一标识符)。15自定义数据类型：自定义数据类型：用户定义数据类型是基于 SQL Server 2005 中的系统基本数据类型的 创建【例5-1】创建此类型时必须提供三个参数名称新数据类型所依据的系统数据类型为空性（数据类型是否允许空值）删除【例5-2】学习要点创建、删除16创建自定义类型基本语句格式基本语句格式【例5-1】创建用户自定义数据类型USE StudentEXEC sp_addtype birthday,VARCHAR(14),NOT NULL学习要点创建、删除sp_addtype 用户数

9、据类型名用户数据类型名,系统数据类型系统数据类型 ,为空性为空性 17基本语句格式基本语句格式【例5-2】删除用户定义的数据类型 birthday USE studentEXEC sp_droptype birthdaysp_droptype 用户数据类型名用户数据类型名注意：如果在表定义内使用某个用户定义的数据类型，或者将某个规则或默认值绑定到这种数据类型，那么就不能除去它 学习要点创建、删除删除自定义类型185.1 SQL Server表概述5.1.2 空值和默认值空值和默认值空值(NULL)空值不同于空白空值不同于空白(空字符串空字符串)或数值零，通常表示未填写、或数值零，通常表示未填写

10、、未知未知(Unknown)、不可用或将在以后添加的数据。、不可用或将在以后添加的数据。默认值是指表中数据的默认取值，默认值对象是数据库的对象不依附于具体的表对象，即默认值对象的作用范围是整个数据库。195.1 SQL Server表概述5.1.3 约束约束 约约束束定定义义了了关关于于列列中中允允许许值值的的规规则则，SQL Server通通过过限限制制列中数据、行中数据和表之间数据来保证数据的完整性。列中数据、行中数据和表之间数据来保证数据的完整性。非空值约束(Not Null)默认约束(Default)惟一性约束(Unique）主键约束(Primary Key，也称主关键字约束)外键约束

11、(Foreign Key，也称为外部关键字约束)205.2 5.2 表的创建与维护表的创建与维护 数据表是数据库中最重要的对象，是相关联的行列数据的集合，整个数据库中的数据都是物理存储在各个数据表中的。数据表的主要内容包括：1)表的名字，每个表都必须有一个名字。表名必须遵循SQL Server2005 的命名规则，且最好能够使表名准确表达表格的内容。2)表中各列的名字和数据类型，包括基本数据类型及自定义数据类型。3)表的主码和外码信息。4)表中哪些列允许为空。5)表中哪些列需要索引。6)表中哪些列需要绑定约束对象、默认值对象或规则对象。215.2 表的创建与维护 使用使用SQL Server管

12、理平台对表进行操作管理平台对表进行操作使用SQL Server管理平台创建和修改表 使用SQL Server管理平台设计数据库关系 在SQL Server管理平台中删除表 使使2122表操作创建表创建表SSMS方式Step 1:服务器组选中数据库右键新建表学习要点step1、step2、step323创建表创建表SSMS方式Step 2:输入列名、数据类型、长度和为空性等项目 学习要点step1、step2、step3表操作24创建表创建表SSMS方式Step 3:点击窗口标题栏上的或工具栏上按钮 输入表名 学习要点step1、step2、step3T-SQL方式表操作255.2 表的创建与维

13、护 使用使用Transact-SQL语句创建表语句创建表语法格式：语法格式：CREATE TABLEdatabase_name.owner.|owner.table_name(|column_name AS computed_column_expression|:=CONSTRAINT constraint_name|PRIMARY KEY|UNIQUE,.n )ON filegroup|DEFAULT TEXTIMAGE_ON filegroup|DEFAULT 26创建表创建表T-SQL方式基本语句格式如下：示例【例5-3】CREATE TABLE 表名表名（列名列名数据类型数据类型列级完

14、整性约束条件列级完整性约束条件，列名列名 数据类型数据类型 列级完整性约束条件列级完整性约束条件.，表级完整性约束条件表级完整性约束条件）5.2 表的创建与维护27【例例5-3】创建一个名为创建一个名为“information”的表的表 Use SalesGOCREATE TABLE information (s_no CHAR(11)PRIMARY KEY,s_name CHAR(8)not null,s_sex CHAR(2),s_birth datetime,s_address VARCHAR(30),s_class CHAR(10)学习要点T-SQL方式28【例例5-4为表指定文件组。

15、为表指定文件组。CREATE TABLE Sales.dbo.information (s_no CHAR(11)PRIMARY KEY,s_name CHAR(8)not null,s_sex CHAR(2),s_birth datetime,s_address VARCHAR(30),s_class CHAR(10)ON PRIMARY将该表放置在PRIMARY文件组中29【例例5-5对计算列使用表达式。对计算列使用表达式。CREATE TABLE Salarys (姓名姓名 varchar(10),基本工资基本工资 money,奖金奖金 money,总计总计 AS 基本工资基本工资+奖金

16、奖金)该列为计算列，不能手动赋值或修改30【例例5-6自动获取字段值。自动获取字段值。CREATE TABLE users (编号编号 identity(1,1)NOT NULL,用户代码用户代码 varchar(10),登陆时间登陆时间 AS Getdate(),用户名用户名 AS User_name()利用函数自动获取值31表名前面加表名前面加#表示：本地临时表表示：本地临时表表名前面加表名前面加#表示：全局临时表表示：全局临时表【例例5-7创建临时表。创建临时表。CREATE TABLE#student (学号 varchar(8)PRIMARY KEY,姓名 varchar(10),性

17、别 varchar(2),班级varchar(10)32修改表修改表SSMS方式Step 1:服务器组选中数据库 选中表右键设计表学习要点step1、step2表操作33修改表修改表SSMS方式Step 2:点击窗口标题栏上的或工具栏上按钮 保存 学习要点step1、step2 T-SQL方式表操作34修改表修改表T-SQL方式基本语句格式如下：示例【例5-813】添加列、修改列、删除列添加、删除约束重命名表ALTER TABLE 表名表名ALTER COLUMN列名列名 新数据类型新数据类型ADD 新列名新列名数据类型数据类型完整性约束完整性约束DROP完整性约束名完整性约束名 列名列名 学

18、习要点step1、step2T-SQL方式表操作35示例添加列、修改列、删除列添加列、修改列、删除列【例5-8】要在Information 表中添加一个长为 20 个字符，名为S_major的类型为CHAR的列【例5-9】将Information表中的s_birth数据类型改为CHAR型，且宽度为10【例5-10】在Information表中删除列S_major ALTER TABLE Information ADD S_major CHAR（20）ALTER TABLE Information ALTER COLUMN s_birth CHAR(10)ALTER TABLE Informat

19、ion DROP COLUMN S_major学习要点T-SQL方式36添加、删除约束添加、删除约束【例5-11】为Information表中S_name列添加唯一性约束【例5-12】删除Information表中S_name的唯一性约束 ALTER TABLE Information ADD UNIQUE(S_name)ALTER TABLE Information DROP UNIQUE(S_name)学习要点T-SQL方式示例37重命名表：重命名表：基本语句格式：sp_rename 当前表名当前表名,新表名新表名【例5-13】将表information改名为infosp_rename i

20、nformation,info学习要点T-SQL方式示例38表操作查看表查看表企业管理器方式方法：服务器组选中数据库选中表右键学习要点T-SQL方式39查看表查看表T-SQL方式基本语句基本语句：sp_help 功能：功能：sp_help系统存储过程查看数据库对象的结构系统存储过程查看数据库对象的结构示例：示例：【例例5-14】查看表查看表information信息信息USE studentEXEC sp_help information学习要点SSMS表操作40删除表删除表企业管理器方式Step1 :服务器组选中数据库选中表右键学习要点step1、step2表操作41删除表删除表企业管理器方

21、式Step2学习要点step1、step2T-SQL方式表操作42删除表删除表T-SQL方式基本语句：DROP TABLE 注意：定义有外键约束的表必须先删除外键约束，才能删除。系统表不能使用DROP TABLE语句删除。表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心 学习要点step1、step2T-SQL方式表操作43例例5-16 删除当前数据库内的表。删除当前数据库内的表。USE SalesGODROP TABLE employee本例从当前数据库Sales中删除employee表及其数据和索引

22、。例例5-17 删除另外一个数据库内的表。删除另外一个数据库内的表。DROP TABLE Sales.dbo.employee本例删除Sales数据库内的employee表。可以在任何数据库内执行此操作。44编辑记录编辑记录企业管理器方式直接在下图中所示的表格中输入、修改和删除表中的记录。记录操作完成后，根据提示保存操作结果则完成表中记录的操作 学习要点SSMST-SQL方式5.3表表中中数数据据的的维维护护451、插入数据、插入数据T-SQL方式示例：插入所有列【例5-18】插入指定列【例5-19】INSERT INTO 表名表名（属性列属性列1，属性列属性列2.VALUES（常量常量1，常

23、量常量2.）学习要点SSMST-SQL方式5.3表表中中数数据据的的维维护护INSERT INTO 表名表名（属性列属性列1，属性列属性列2.SELECT 语句语句46示例插入所有列：插入所有列：【例5-18】将一个新学生记录插入到information表中（20031201001,王玉梅,女,1986-5-18,湖南株洲,电商031）INSERT INTO Information VALUES（20031201001,王玉梅王玉梅,女女,1986-5-18,湖南株洲湖南株洲,电商电商031）学习要点插入记录47插入指定列：插入指定列：【例5-19】插入一个学生记录的指定字段(20021003

24、010,刘奇,男)INSERT INTO Information（S_no，S_name，S_sex）VALUES（20021003010,刘奇刘奇,男男）学习要点插入记录示例当插入值少于列的个数或插入值与列的顺序不同时，需要显式指定列名48INSERT INTO kaoping_table(id,name)SELECT st_id,st_name FROM studentGO【例5-20】创建一个人员考评表kaoping_table，该表包括3个字段：编号id，数据类型为char(10)；姓名name，数据类型为varchar(10)；评价pingjia，数据类型为varchar(10)。将

25、学生表student中的学号和姓名数据插入到考评表kaoping_table中。CREATE TABLE kaoping_table(id char(10),name varchar(10),pingjia varchar(10)GO插入查询的结果：插入查询的结果：49插入数据（续）注意：注意：INSERTVALUES语句一次只能向表中插入一条记录。当向表中所有列插入数据时，列名可以省略不写，但列值必须与表中定义的列名顺序一致。建议写出列名。当向表中插入数据的顺序与列顺序不同时，必须写列名。当向表中某些列插入数据，某些列不插入数据时，必须写列名。INSERT语句不能为计算列、标识列指定列值。5

26、0【例5-20】将数据装载到带有标识符的表CREATE TABLE customer(id bigint NOT NULL IDENTITY(0,1),name varchar(10),address varchar(50)GOINSERT INTO customer(name,address)VALUES(张三张三,中山东路中山东路29号号)GOSET IDENTITY_INSERT customer ONINSERT INTO customer(id,name,address)VALUES(100,张三张三,中山东路中山东路29号号),插入带有标识符的表：插入带有标识符的表：允许标识列的手

27、动插入允许标识列的手动插入允许系统自动为允许系统自动为新行生成标识值新行生成标识值515.3表表中中数数据据的的维维护护2、修改数据、修改数据T-SQL方式示例：修改单条记录【例5-21】修改多条记录【例5-22】修改所有记录【例5-23】UPDATE 表名表名 SET 列名列名=表达式表达式，列名列名=表达式表达式.FROM 表名表名WHERE 条件条件；学习要点修改记录指定更新的数据来自指定更新的数据来自一个或多个表或视图一个或多个表或视图52示例修改单条记录：修改单条记录：【例5-21】学生“周天”的家由“广东广州”搬到“湖南株洲”，则通过以下语句对其基本信息进行更新 UPDATE In

28、formation SET S_address=湖南株洲湖南株洲WHERE S_name=周天周天学习要点修改记录53修改多条记录：修改多条记录：【例5-22】将班级“信息041”改为“信息042”，可以通过以下语句来实现。UPDATE Information SET S_class=信息信息042WHERE S_class=信息信息041学习要点修改记录示例54修改所有记录：修改所有记录：【例5-23】将班级为“20021001”课程号为“1003c”，的成绩统一设置为75 UPDATE Score SET grade=75WHERE left(S_no,7)=20021001 and C_

29、no=1003c学习要点修改记录示例55使用来自另一个表的信息：使用来自另一个表的信息：【例5-24】从Sell_order表和Goods表中的数据计算每个销售订单的收费。UPDATE Sell_order SET cost=Sell_order.order_Num*Goods.priceFROM Sell_order,GoodsWHERE Sell_order.goods_id=Goods.goods_id学习要点修改记录示例565.3表表中中数数据据的的维维护护删除数据删除数据T-SQL方式 DELETE 语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。示例：删除指定记录【例

30、5-25】删除所有记录【例5-26】DELETEFROM 表名表名WHERE 条件条件学习要点删除记录57删除指定记录：删除指定记录：【例5-25】假设学号为20031001001的学生中途因故辍学，则需要在学生基本信息表中删除该记录 DELETE FROM Information WHERE S_no=20031001001学习要点删除记录示例58删除所有记录：删除所有记录：【例5-26】删除所有的学生课程成绩记录 DELETE FROM Score学习要点删除记录示例59删除所有记录：删除所有记录：快速清空表，而不会把每一行的删除操作都记入日志。TRUNCATE TABLE 表名表名注意：

31、注意：比较比较TRUNCATE TABLE、DROP TABLE、DELETE的区别的区别学习要点删除记录示例60课堂练习课堂练习【练习练习5-15-1】在在studentstudent数据库中创建别名数据类型数据库中创建别名数据类型namename，基于系统数据类型基于系统数据类型varchar(10)varchar(10)，允许空值。，允许空值。USE studentGOCREATE TYPE name FROM varchar(10)NULLGO61【练习练习5-25-2】创建班级表创建班级表classclass，表结构参见下表，不包括约束信息。，表结构参见下表，不包括约束信息。列名列名

32、数据类型数据类型空值与否空值与否约束约束列名含义列名含义cl_idchar(2)notnull主键主键班号班号cl_namevarchar(20)notnull惟一键惟一键班名班名cl_roomvarchar(20)null教室教室cl_dtidchar(2)null外键外键所属系号所属系号62USE studentGOCREATE TABLE teacher(th_id char(6)PRIMARY KEY,th_name varchar(10)not null,th_gender char(2)null CHECK(th_gender=男男 or th_gender=女女),th_posi

33、tionchar(6)null,th_type char(4)null DEFAULT 专职专职)GO63课堂练习课堂练习【例例5-35-3】修改学生表修改学生表studentstudent，向表中增加，向表中增加2 2列。增加列。增加“民族民族”列，列名列，列名为为st_nationst_nation，数据类型为，数据类型为char(2)char(2)，允许空值；增加，允许空值；增加“籍贯籍贯”列，列名为列，列名为st_nativest_native，数据类型为，数据类型为char(6)char(6)，允许空值。，允许空值。【例例5-45-4】向学生表向学生表studentstudent的部

34、分列中插入一条记录。学号：的部分列中插入一条记录。学号：20050101022005010102，姓名：张意，出生日期：，姓名：张意，出生日期：1989-12-11989-12-1，性别：男，班号：，性别：男，班号：0101。USE studentGOINSERT INTO student(st_id,st_name,st_birth,st_gender,st_clid)VALUES(2005010102,张意张意,1989-12-1,男男,01)GO64USE studentGOCREATE TABLE kaoping_table(id char(10),name varchar(10),p

35、ingjia varchar(10)GOINSERT INTO kaoping_table(id,name)SELECT st_id,st_name FROM studentGO【例例5-55-5】创建一个人员创建一个人员考评表考评表kaoping_tablekaoping_table，该表包括，该表包括3 3个字段：个字段：编号编号idid，数据类型为，数据类型为char(10)char(10)；姓名；姓名namename，数据类型为，数据类型为varchar(10)varchar(10)；评价；评价pingjiapingjia，数据类型为，数据类型为varchar(10)varchar(1

36、0)。将学生。将学生表表studentstudent中的学号和中的学号和姓名数据插入到考评姓名数据插入到考评表表kaoping_tablekaoping_table中。中。65【例例5-65-6】修改学生表修改学生表studentstudent中的一行记录，将学号值为中的一行记录，将学号值为“20050101012005010101”的学生的出生日期改为的学生的出生日期改为“1989-10-101989-10-10”，电话改为，电话改为“010_84981234010_84981234”。USE studentGOUPDATE student SET st_birth=1989-10-10,s

37、t_tel=010_84981234 WHERE st_id=2005010101GO66（1）表的相关概念：表的相关概念：表是数据库中数据的实际存储处所，每个表代表一表是数据库中数据的实际存储处所，每个表代表一个实体。表由行和列组成，每行标识实体的一个个体，每列代表实体个实体。表由行和列组成，每行标识实体的一个个体，每列代表实体的一个属性。的一个属性。（2）数据类型数据类型：数据类型描述并约束了列中所能包含的数据的种类、所：数据类型描述并约束了列中所能包含的数据的种类、所存储值的长度或大小、数字精度和小数位数（对数值数据类型）。存储值的长度或大小、数字精度和小数位数（对数值数据类型）。（3）

38、空值空值：未对列指定值时，该列将出现空值。空值不同于空字符串或：未对列指定值时，该列将出现空值。空值不同于空字符串或数值零，通常表示未知。空值会对查询命令或统计函数产生影响，应数值零，通常表示未知。空值会对查询命令或统计函数产生影响，应尽量少使用空值。尽量少使用空值。（4）约束约束：约束是数据库自动保持数据完整性的机制，它是通过限制列：约束是数据库自动保持数据完整性的机制，它是通过限制列中数据、行中数据和表之间数据来保持数据完整性。中数据、行中数据和表之间数据来保持数据完整性。SQL Server 2005支持支持Not Null、Default、Check、Primary Key、Foreign Key、Unique 6种约束。关于约束的操作将在第种约束。关于约束的操作将在第8章详细介绍。章详细介绍。（5）可以使用）可以使用SQL Server管理平台和管理平台和Transact-SQL语句创建表并语句创建表并对表对表进行维护，包括修改和删除等操作。进行维护，包括修改和删除等操作。（6）可以使用）可以使用SQL Server管理平台和管理平台和Transact-SQL语句语句对表中数据进对表中数据进行编辑，包括插入、更新和删除等操作。行编辑，包括插入、更新和删除等操作。本章小结本章小结