表的管理数据库天津大学.ppt

1 第5章表的管理 2 本章内容 5 1SQLServer表概述5 2表的创建与维护5 3表中数据的维护 3 5 1SQLServer表概述 数据类型SQLServer支持4种基本数据类型 字符和二进制数据类型 日期时间数据类型 逻辑数据类型 数字数据类型 用于各类数据值的存储 检索和解释 1 字符和二进制数据类型 1 字符数据类型字符数据类型用于存储汉字 英文字母 数字符号和其他各种符号 char数据类型varchar数据类型text数据类型SQLServer允许使用多国语言 采用Unicode标准字符集 为此SQLServer提供多字节的字符数据类型 nchar n nvarchar n 和ntext 4 5 1SQLServer表概述 2 二进制数据类型SQLServer二进制数据类型用于存储二进制数或字符串 SQLServer的3种有效二进制数据类型 binary数据类型varbinary n 数据类型image数据类型 5 5 1SQLServer表概述 2 日期时间数据类型日期时间数据类型用于存储日期和时间数据 SQLServer支持两种日期时间数据类型 datetime和smalldatetime 3 逻辑数据类型SQLServer的逻辑数据类型为bit 适用于判断真 假的场合 长度为一个字节 6 5 1SQLServer表概述 4 数字数据类型SQLServer提供了多种方法存储数值 SQLServer的数字类型大致可分为4种基本类型 1 整数数据类型有4种整数数据类型 int smallint tinyint和bigint 用于存储不同范围的值 2 浮点数据类型浮点数据用来存储系统所能提供的最大精度保留的实数数据 7 5 1SQLServer表概述 3 精确数值数据类型精确数值数据类型用于存储有小数点且小数点后位数确定的实数 格式 decimal p s numeric p s 4 货币数据类型除了decimal和numeric类型适用于货币数据的处理外 SQLServer还专门提供了两种货币数据类型 money和smallmoney 8 5 1SQLServer表概述 5 其他数据类型除了以上4种基本数据类型 SQLServer还支持其他一些数据类型 1 Timestamp 时间戳数据类型 用于自动记录插入或删除操作的时间 注意 服务器不允许用户指定时间戳值 2 sql variant 可变数据类型 该类型的变量可用来存放大部分SQLServer数据类型的值 最大长度为8016字节 不支持text ntext timestamp和sql variant类型 3 table 表类型 这是一种特殊的数据类型 存储供以后处理的结果集 4 uniqueidentifier GUID类型 GlobalUniqueIDentifier 全局惟一标识符 9 5 1SQLServer表概述 空值 NULL 空值不同于空白 空字符串 或数值零 通常表示未填写 未知 Unknown 不可用或将在以后添加的数据 默认值是指表中数据的默认取值 默认值对象是数据库的对象不依附于具体的表对象 即默认值对象的作用范围是整个数据库 约束约束定义了关于列中允许值的规则 SQLServer通过限制列中数据 行中数据和表之间数据来保证数据的完整性 1 非空值约束 NotNull 2 默认约束 Default 3 惟一性约束 Unique 4 主键约束 PrimaryKey 也称主关键字约束 5 外键约束 ForeignKey 也称为外部关键字约束 10 5 2表的创建与维护 使用SQLServer管理平台对表进行操作使用SQLServer管理平台创建和修改表使用SQLServer管理平台设计数据库关系在SQLServer管理平台中删除表使用Transact SQL语句创建表语法格式 CREATETABLE database name owner owner table name column nameAScomputed column expression CONSTRAINTconstraint name PRIMARYKEY UNIQUE n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT 11 5 2表的创建与维护 例5 1简单的表定义 USESalesGOCREATETABLEemployee employee idchar 4 NOTNULL employee namechar 8 NOTNULL sexchar 2 NOTNULL birth datedatetimeNOTNULL hire datedatetimeNOTNULL addressvarchar 50 telephonechar 8 wagesmoney department idchar 4 NOTNULL resumetextNOTNULL 12 5 2表的创建与维护 例5 2为表指定文件组 CREATETABLESales dbo supplier supplier idchar 5 NOTNULL supplier namevarchar 50 NOTNULL linkman namevarchar 8 addressvarchar 50 telephonechar 12 NOTNULL ON PRIMARY 13 5 2表的创建与维护 例5 3对计算列使用表达式 CREATETABLEsalarys 姓名varchar 10 基本工资money 奖金money 总计AS基本工资 奖金 14 5 2表的创建与维护 例5 4定义表autouser自动获取用户名称 CREATETABLEautouser 编号intidentity 1 1 NOTNULL 用户代码varchar 18 登录时间ASGetdate 用户名ASUser name 15 5 2表的创建与维护 例5 5创建临时表 CREATETABLE students 学号varchar 8 姓名varchar 10 性别varchar 2 班级varchar 10 16 5 2表的创建与维护 使用Transact SQL语句修改表语法格式 ALTERTABLEtable name ALTERCOLUMNcolumn name new data type precision scale NULL NOTNULL ADD DROP ROWGUIDCOL ADD column nameAScomputed column expression n WITHCHECK WITHNOCHECK ADD n DROP CONSTRAINT constraint name COLUMNcolumn name n CHECK NOCHECK CONSTRAINT ALL constraint name n ENABLE DISABLE TRIGGER ALL trigger name n 17 5 2表的创建与维护 例5 6更改表以添加新列 然后再删除该列 ALTERTABLEemployeeADDemailvarchar 20 NULLGOsp helpemployeeALTERTABLEemployeeDROPCOLUMNemailGOsp helpemployee 18 5 2表的创建与维护 例5 7将表employee的列address改为varchar 150 数据类型 并且不允许为空 ALTERTABLEemployeeALTERCOLUMNaddressvarchar 150 NOTNULLGO注意 一定要确认已有的数据中列address均不为空后 才能进行此操作 19 5 2表的创建与维护 使用Transact SQL语句删除表语句格式如下 DROPTABLEtable name其中 table name是要删除的表名 注意 1 定义有外键约束的表必须先删除外键约束 才能删除 2 系统表不能使用DROPTABLE语句删除 20 5 2表的创建与维护 例5 8删除当前数据库内的表 USESalesGODROPTABLEemployee本例从当前数据库Sales中删除employee表及其数据和索引 例5 9删除另外一个数据库内的表 DROPTABLESales dbo employee本例删除Sales数据库内的employee表 可以在任何数据库内执行此操作 21 5 3表中数据的维护 对表中数据进行维护有两种方法 使用SQLServer管理平台使用Transact SQL语句插入数据语法格式如下 INSERT INTO table name view name column list VALUES value list derived table execute statement DEFAULTVALUES 22 5 3表中数据的维护 例5 10使用简单的INSERT语句 USESalesGOINSERTSupplierVALUES S001 华科电子有限公司 施宾彬 朝阳路56号 2636565 例5 11显式指定列列表 INSERTSales dbo Supplier supplier id supplier name linkman name address telephone VALUES S001 华科电子有限公司 施宾彬 朝阳路56号 2636565 23 5 3表中数据的维护 修改数据语法格式 UPDATE table name view name SET column name variable expression n FROM n WHERE 24 5 3表中数据的维护 例5 14使用简单的UPDATE语句 UPDATECustomer2SETlinkman name 佚名 address NULL telephone 例5 15在UPDATE语句中使用WHERE子句 UPDATECustomer2SETtelephone 0731 telephoneWHERELEN telephone 7 25 5 3表中数据的维护 例5 17在UPDATE语句中使用SELECT TOP语句 UPDATEGoodsSETUnit Price Unit Price 0 9FROMGoods SELECTTOP10Goods ID SUM Order Num ASTotal NumFROMSell OrderGROUPBYGoods IDORDERBYTotal NumDESC ASTotal SumWHEREGoods Goods id Total Sum Goods id 26 5 3表中数据的维护 删除数据1 DELETE语句DELETE语句可删除表或视图中的一行或多行 每一行的删除都将被记入日志 DELETE语句的语法格式如下 DELETE FROM table name view name FROM n WHERE sea 27 5 3表中数据的维护 例5 18不带参数使用DELETE命令删除所有行 USESalesGODELETECustomer2本例从Customer2表中删除所有行 注意 将DELETE语句与DROPTABLE语句的功能区分开来 例5 19带WHERE子句的DELETE语句 有条件地删除行 DELETEFROMSell OrderWHERECustom ID C0003 28 5 3表中数据的维护 例5 20在DELETE中使用联接或子查询 基于Transact SQL 使用表的联接DELETESell OrderFROMSell OrderSOINNERJOINcustomer2CONSO customer id C customer idWHEREC addressLIKE 东方市 等同于下列命令DELETESell OrderFROMSell OrderSO customer2CWHERESO customer id C customer idANDC addressLIKE 东方市 基于SQL 92标准 须使用子查询DELETEFROMSell OrderWHEREcustomer idIN SELECTcustomer idFROMcustomer2WHEREaddressLIKE 东方市 29 5 3表中数据的维护 2 TRUNCATETABLE语句语法格式 TRUNCATETABLEtable name其中table name是要清空的表的名称 例5 21使用TRUNCATETABLE语句清空表 TRUNCATETABLEcustomer2 30 本章小结 1 表的相关概念 表是数据库中数据的实际存储处所 每个表代表一个实体 表由行和列组成 每行标识实体的一个个体 每列代表实体的一个属性 2 数据类型 数据类型描述并约束了列中所能包含的数据的种类 所存储值的长度或大小 数字精度和小数位数 对数值数据类型 3 空值 未对列指定值时 该列将出现空值 空值不同于空字符串或数值零 通