《第5章-表的管理1课件》由会员分享，可在线阅读，更多相关《第5章-表的管理1课件（30页珍藏版）》请在金锄头文库上搜索。
1、7/23/20241第第5章章 表的管理表的管理17/23/20242本章内容本章内容n5.1 SQL Server表概述表概述 n5.2 表的创建与维护表的创建与维护 n5.3 表中数据的维护表中数据的维护 27/23/202435.1 SQL Server表概述表概述n 数据类型数据类型 SQL Server支持支持4种基本数据类型：字符和二进制数据类型、日期时种基本数据类型：字符和二进制数据类型、日期时间数据类型、逻辑数据类型、数字数据类型，用于各类数据值的存储、检间数据类型、逻辑数据类型、数字数据类型，用于各类数据值的存储、检索和解释。索和解释。 1字符和二进制数据类型字符和二进制数据
2、类型(1)字符数据类型字符数据类型字符数据类型用于存储汉字、英文字母、数字符号和其他各种符号。字符数据类型用于存储汉字、英文字母、数字符号和其他各种符号。char类型：类型：定义形式为定义形式为 char(n),n=18000,默认长度为默认长度为1varchar类型：类型：定义形式为定义形式为 varchar(n),n=18000,默认长度为默认长度为1 text类型类型 ：用于存储数据量大（超过用于存储数据量大（超过8000字符字符 ）而变长的文本）而变长的文本注：注：输入的字符型数据要用单撇号或双撇号括起来输入的字符型数据要用单撇号或双撇号括起来37/23/202445.1 SQL Se
3、rver表概述表概述(2)二进制数据类型二进制数据类型SQL Server二进制数据类型用于存储二进制数或字符串。二进制数据类型用于存储二进制数或字符串。 SQL Server的的3种有效二进制数据类型：种有效二进制数据类型： binary (n)数据类型数据类型 varbinary(n)数据类型数据类型 image数据类型数据类型 ：与与text类型类似类型类似注：输入二进制数据时要用撇号括起或用注：输入二进制数据时要用撇号括起或用0x开头的两个开头的两个 十六进制数（构成一个字节）。十六进制数（构成一个字节）。47/23/202455.1 SQL Server表概述表概述2日期时间数据类型
4、日期时间数据类型日期时间数据类型用于存储日期和时间数据。日期时间数据类型用于存储日期和时间数据。SQL Server支持两种日期时间数据类型：支持两种日期时间数据类型：datetime类型：存储类型：存储1753年年1月月1日日 9999年年12月月31日的日日的日期和时间数据，精确到期和时间数据，精确到3/100s,日期和时间各占日期和时间各占4个字节。个字节。smalldatetime类型：存储类型：存储1900年年1月月1日日 2079年年6月月6日的日的日期和时间数据，精确到分日期和时间数据，精确到分,日期和时间共占日期和时间共占4个字节。个字节。 3逻辑数据类型逻辑数据类型SQL S
5、erver的逻辑数据类型为的逻辑数据类型为bit，适用于判断真，适用于判断真/假的场假的场合，长度为一个字节。取值为合，长度为一个字节。取值为1、0或或NULL 。非零数据当。非零数据当1处理。处理。57/23/202465.1 SQL Server表概述表概述4数字（值）数据类型数字（值）数据类型SQL Server提供了多种方法存储数值，提供了多种方法存储数值，SQL Server的数的数字类型大致可分为字类型大致可分为4种基本类型。种基本类型。(1)整数数据类型整数数据类型有有4种整数数据类型：种整数数据类型：int、smallint、tinyint和和bigint，用，用于存储不同范围
6、的值。分别占于存储不同范围的值。分别占4、2、1、8个字节个字节(2)浮点数据类型浮点数据类型浮点数据用来存储系统所能提供的最大精度保留的实数浮点数据用来存储系统所能提供的最大精度保留的实数数据数据 。包括。包括float类型，类型，real类型，分别占类型，分别占8和和4个字节，精度个字节，精度为为15和和7位小数。位小数。浮点数据类型不能用于需要固定精度的运算，如货币运浮点数据类型不能用于需要固定精度的运算，如货币运算（近似运算存在误差）算（近似运算存在误差）67/23/202475.1 SQL Server表概述表概述 (3)精确数值数据类型精确数值数据类型精确数值数据类型用于存储有小数
7、点且小数点后位数确精确数值数据类型用于存储有小数点且小数点后位数确定的实数定的实数 。格式：格式： decimal(p, s) 和和 numeric(p, s)其中：其中：p为精度，即存储的最大数，为精度，即存储的最大数，s位小数位数位小数位数 两种格式基本相同。两种格式基本相同。(4)货币数据类型货币数据类型除了除了decimal和和numeric类型适用于货币数据的处理外，类型适用于货币数据的处理外，SQL Server还专门提供了两种货币数据类型：还专门提供了两种货币数据类型：money和和smallmoney。输入数据前要加。输入数据前要加“$” ,否则当成浮点数或拒绝否则当成浮点数或
8、拒绝77/23/202485.1 SQL Server表概述表概述5其他数据类型其他数据类型除了以上除了以上4种基本数据类型，种基本数据类型，SQL Server还支持其他一些数据类型。还支持其他一些数据类型。(1)Timestamp：时间戳数据类型，用于自动记录插入或删除操作的：时间戳数据类型，用于自动记录插入或删除操作的时间。时间。注意：服务器不允许用户指定时间戳值。注意：服务器不允许用户指定时间戳值。(2)sql_variant：可变数据类型。该类型的变量可用来存放大部分：可变数据类型。该类型的变量可用来存放大部分SQL Server数据类型的值，最大长度为数据类型的值，最大长度为801
9、6字节，不支持字节，不支持text、ntext、timestamp和和sql_variant类型。类型。(3)table：表类型。这是一种特殊的数据类型，存储供以后处理的：表类型。这是一种特殊的数据类型，存储供以后处理的结果集。结果集。(4)uniqueidentifier：GUID类型类型(Global Unique IDentifier, 全局惟全局惟一标识符一标识符)。 87/23/202495.1 SQL Server表概述表概述n空值空值(NULL)空值不同于空白空值不同于空白(空字符串空字符串)或数值零，通常表示未填写、未知或数值零，通常表示未填写、未知(Unknown)、不可用或
10、将在以后添加的数据。、不可用或将在以后添加的数据。 n默默认认值值是是指指表表中中数数据据的的默默认认取取值值，默默认认值值对对象象是是数数据据库库的的对对象象不不依依附于具体的表对象，即默认值对象的作用范围是整个数据库。附于具体的表对象，即默认值对象的作用范围是整个数据库。n约束约束约约束束定定义义了了关关于于列列中中允允许许值值的的规规则则，SQL Server通通过过限限制制列列中中数数据据、行中数据和表之间数据来保证数据的完整性。行中数据和表之间数据来保证数据的完整性。 (1) 非空值约束非空值约束(Not Null) ：插入数据时不能为空：插入数据时不能为空(2) 默认约束默认约束(
11、Default) ：插入数据时没给此列值时，取默认值：插入数据时没给此列值时，取默认值(3) 惟一性约束惟一性约束(Unique ）：不能有两行相同的非空值）：不能有两行相同的非空值(4) 主键约束主键约束(Primary Key，也称主关键字约束，也称主关键字约束) ：不能有两行相同：不能有两行相同的主键值且主键不能为空值的主键值且主键不能为空值(NULL)(5) 外键约束外键约束(Foreign Key，也称为外部关键字约束，也称为外部关键字约束) ：和参照表的：和参照表的主键值匹配。外键常用于标示表之间的关系。主键值匹配。外键常用于标示表之间的关系。97/23/2024105.2 表的创
12、建与维护表的创建与维护n 使用使用SQL Server管理平台对表进行操作管理平台对表进行操作使用使用SQL Server管理平台创建和修改表管理平台创建和修改表 使用使用SQL Server管理平台设计数据库关系管理平台设计数据库关系 在在SQL Server管理平台中删除表管理平台中删除表 n使用使用Transact-SQL语句创建表语句创建表语法格式：语法格式：CREATE TABLEdatabase_name.owner.|owner. table_name ( | column_name AS computed_column_expression |:=CONSTRAINT cons
13、traint_name | PRIMARY KEY | UNIQUE , . n ) ON filegroup|DEFAULT TEXTIMAGE_ON filegroup | DEFAULT 创建表的数据库，默认为当前库。创建表的数据库，默认为当前库。Owner是表所有者名字是表所有者名字列定义列定义定义计算列定义计算列表的约束定义表的约束定义指定文件组指定文件组107/23/2024115.2 表的创建与维护表的创建与维护例例5-1 简单的表定义。简单的表定义。USE SalesGOCREATE TABLE employee ( employee_id char(4) NOT NULL,
14、employee_name char(8) NOT NULL, sex char(2) NOT NULL, birth_date datetime NOT NULL, hire_date datetime NOT NULL, address varchar(50), telephone char(8), wages money, department_id char(4) NOT NULL, resume text NOT NULL)117/23/2024125.2 表的创建与维护表的创建与维护例例5-2 为表指定文件组。为表指定文件组。CREATE TABLE Sales.dbo.suppl
15、ier ( supplier _id char(5) NOT NULL, supplier _name varchar(50) NOT NULL, linkman_name varchar(8), address varchar(50), telephonechar(12) NOT NULL)ON PRIMARY 在数据库在数据库Sales中建表，中建表，supplier所有者为所有者为dbo表放在主文件组中表放在主文件组中127/23/2024135.2 表的创建与维护表的创建与维护例例5-3 对计算列使用表达式。对计算列使用表达式。CREATE TABLE salarys( 姓名姓名 va
16、rchar(10), 基本工资基本工资 money, 奖金奖金 money, 总计总计 AS 基本工资基本工资+奖金奖金)“总计总计”中的数据由中的数据由“基本工资基本工资+奖金奖金”计算得到计算得到137/23/2024145.2 表的创建与维护表的创建与维护例例5-4 定义表定义表autouser自动获取用户名称。自动获取用户名称。CREATE TABLE autouser 该值自动增长该值自动增长 初值，步长初值，步长( 编号编号 int identity(1,1) NOT NULL, 用户代码用户代码 varchar(18), 登录时间登录时间 AS Getdate(), 由函数自动获
17、取由函数自动获取 用户名用户名 AS User_name()147/23/2024155.2 表的创建与维护表的创建与维护例例5-5 创建临时表。创建临时表。CREATE TABLE #students( 学号学号 varchar(8), 姓名姓名 varchar(10), 性别性别 varchar(2), 班级班级 varchar(10)创建临时表，表名前创建临时表，表名前加加#号。号。 数据库关闭数据库关闭后被删除后被删除157/23/2024165.2 表的创建与维护表的创建与维护n使用使用Transact-SQL语句修改表语句修改表语法格式：语法格式：ALTER TABLE table
18、_name ALTER COLUMN column_name new_data_type (precision , scale) NULL|NOT NULL | ADD|DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression , . n | WITH CHECK | WITH NOCHECK ADD , . n | DROP CONSTRAINT constraint_name | COLUMN column_name , . n |CHECK | NOCHECK CONSTRAINT ALL | constraint
19、_name , . n | ENABLE | DISABLE TRIGGER ALL | trigger_name , . n167/23/2024175.2 表的创建与维护表的创建与维护例例5-6 更改表以添加新列，然后再删除该列。更改表以添加新列，然后再删除该列。ALTER TABLE employeeADD email varchar(20) NULLGOsp_help employeeALTER TABLE employeeDROP COLUMN emailGOsp_help employee177/23/2024185.2 表的创建与维护表的创建与维护例例5-7 将表将表employ
20、ee的列的列address改为改为varchar(150)数据类数据类型，并且不允许为空。型，并且不允许为空。ALTER TABLE employeeALTER COLUMN address varchar(150) NOT NULLGO注意：注意：一定要确认已有的数据中列一定要确认已有的数据中列address均不为空后，才能进均不为空后，才能进行此操作。行此操作。 187/23/2024195.2 表的创建与维护表的创建与维护n使用使用Transact-SQL语句删除表语句删除表语句格式如下：语句格式如下：DROP TABLE table_name其中，其中，table_name是要删除的表
21、名。是要删除的表名。注意：注意：(1) 定义有外键约束的表必须先删除外键约束，才能删除。定义有外键约束的表必须先删除外键约束，才能删除。(2) 系统表不能使用系统表不能使用DROP TABLE语句删除。语句删除。197/23/2024205.2 表的创建与维护表的创建与维护例例5-8 删除当前数据库内的表。删除当前数据库内的表。USE SalesGODROP TABLE employee本例从当前数据库本例从当前数据库Sales中删除中删除employee表及其数据和索表及其数据和索引。引。例例5-9 删除另外一个数据库内的表。删除另外一个数据库内的表。DROP TABLE Sales.dbo
22、.employee本例删除本例删除Sales数据库内的数据库内的employee表。可以在任何数据表。可以在任何数据库内执行此操作。库内执行此操作。207/23/2024215.3 表中数据的维护表中数据的维护对表中数据进行维护有两种方法：对表中数据进行维护有两种方法：使用使用SQL Server管理平台管理平台 使用使用Transact-SQL语句语句 n插入数据插入数据 语法格式如下：语法格式如下：INSERT INTO table_name | view_name ( column_list ) VALUES ( value_list ) | derived_table | execut
23、e_statement | DEFAULT VALUES217/23/2024225.3 表中数据的维护表中数据的维护例例5-10 使用简单的使用简单的INSERT语句。语句。USE SalesGOINSERT Supplier VALUES (S001,华科电子有限公司华科电子有限公司,施宾彬施宾彬 ,朝阳朝阳路路56号号,2636565)例例5-11 显式指定列列表。显式指定列列表。INSERT Sales.dbo.Supplier (supplier_id,supplier_name, linkman_name, address, telephone)VALUES (S001 ,华科电子
24、有限公司华科电子有限公司,施宾彬施宾彬 ,朝阳朝阳路路56号号,2636565)227/23/2024235.3 表中数据的维护表中数据的维护n修改数据修改数据语法格式：语法格式：UPDATE table_name|view_name SET column_name|variable=expression ,.n FROM ,.n WHERE 237/23/2024245.3 表中数据的维护表中数据的维护例例5-14 使用简单的使用简单的UPDATE语句。语句。UPDATE Customer2 SET linkman_name=佚名佚名, address=NULL, telephone=例例5
25、-15 在在UPDATE语句中使用语句中使用WHERE子句。子句。UPDATE Customer2 SET telephone=0731-+telephone WHERE LEN(telephone)=7247/23/2024255.3 表中数据的维护表中数据的维护例例5-17 在在UPDATE 语句中使用语句中使用SELECTTOP语句。语句。UPDATE Goods SET Unit_Price=Unit_Price*0.9 FROM Goods, (SELECT TOP 10 Goods_ID, SUM(Order_Num) AS Total_Num FROM Sell_Order GR
26、OUP BY Goods_ID ORDER BY Total_Num DESC ) AS Total_Sum WHERE Goods.Goods_id=Total_Sum.Goods_id257/23/2024265.3 表中数据的维护表中数据的维护n删除数据删除数据 1DELETE 语句语句DELETE 语句可删除表或视图中的一行或多行，每一行语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。的删除都将被记入日志。DELETE语句的语法格式如下：语句的语法格式如下：DELETE FROM table_name|view_name FROM ,.n WHERE sea 267/23
27、/2024275.3 表中数据的维护表中数据的维护例例5-18 不带参数使用不带参数使用DELETE命令删除所有行。命令删除所有行。USE SalesGODELETE Customer2本例从本例从Customer2表中删除所有行。表中删除所有行。注意：注意：将将DELETE语句与语句与DROP TABLE语句的功能区分开来。语句的功能区分开来。例例5-19 带带WHERE子句的子句的DELETE语句，有条件地删除语句，有条件地删除行。行。DELETE FROM Sell_Order WHERE Custom_ID=C0003277/23/2024285.3 表中数据的维护表中数据的维护例例5
28、-20 在在DELETE中使用联接或子查询。中使用联接或子查询。- 基于基于Transact-SQL，使用表的联接，使用表的联接DELETE Sell_Order FROM Sell_Order SO INNER JOIN customer2 C ON SO.customer_id=C.customer_id WHERE C.address LIKE 东方市东方市%- 等同于下列命令等同于下列命令DELETE Sell_Order FROM Sell_Order SO, customer2 C WHERE SO.customer_id=C.customer_id AND C.address L
29、IKE 东方市东方市%- 基于基于SQL-92标准，须使用子查询标准，须使用子查询DELETE FROM Sell_Order WHERE customer_id IN ( SELECT customer_id FROM customer2 WHERE address LIKE 东方市东方市%)287/23/2024295.3 表中数据的维护表中数据的维护2TRUNCATE TABLE语句语句语法格式：语法格式：TRUNCATE TABLE table_name其中其中table_name是要清空的表的名称。是要清空的表的名称。例例5-21 使用使用TRUNCATE TABLE语句清空表。语句
30、清空表。TRUNCATE TABLE customer2297/23/202430本章小结本章小结（1）表的相关概念：表是数据库中数据的实际存储处所，每个表代）表的相关概念：表是数据库中数据的实际存储处所，每个表代表一个实体。表由行和列组成，每行标识实体的一个个体，每列代表实表一个实体。表由行和列组成，每行标识实体的一个个体，每列代表实体的一个属性。体的一个属性。（2）数据类型：数据类型描述并约束了列中所能包含的数据的种类、）数据类型：数据类型描述并约束了列中所能包含的数据的种类、所存储值的长度或大小、数字精度和小数位数（对数值数据类型）。所存储值的长度或大小、数字精度和小数位数（对数值数据类
31、型）。（3）空值：未对列指定值时，该列将出现空值。空值不同于空字符）空值：未对列指定值时，该列将出现空值。空值不同于空字符串或数值零，通常表示未知。空值会对查询命令或统计函数产生影响，串或数值零，通常表示未知。空值会对查询命令或统计函数产生影响，应尽量少使用空值。应尽量少使用空值。（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语句对表中数据语句对表中数据进行编辑，包括插入、更新和删除等操作。进行编辑，包括插入、更新和删除等操作。 30
