表的管理数据库天津大学.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语句创建表语法格式：CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|:=CONSTRAINTconstraint_name|PRIMARYKEY|UNIQUE,.n)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|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)ONPRIMARY,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_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD|DROPROWGUIDCOL|ADD|column_nameAScomputed_column_expression,.n|WITHCHECK|WITHNOCHECKADD,.n|DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,.n|CHECK|NOCHECKCONSTRAINTALL|constraint_name,.n|ENABLE|DISABLETRIGGERALL|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语句插入数据语法格式如下：INSERTINTOtable_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表中数据的维护,修改数据语法格式：UPDATEtable_name|view_nameSETcolumn_name|variable=expression,.nFROM,.nWHERE,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语句中使用SELECTTOP语句。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表中数据的维护,删除数据1DELETE语句DELETE语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。DELETE语句的语法格式如下：DELETEFROMtable_name|view_nameFROM,.nWHEREsea,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表中数据的维护,2TRUNCATETABLE语句语法格式：TRUNCATETABLEtable_name其中table_name是要清空的表的名称。例5-21使用TRUNCATETABLE语句清空表。TRUNCATETABLEcustomer2,30,本章小结,（1）表的相关概念：表是数据库中数据的实际存储处所，每个表代表一个实体。表由行和列组成，每行标识实体的一个个体，每列代表实体的一个属性。（2）数据类型：数据类型描述并约束了列中所能包含的数据的种类、所存储值的长度或大小、数字精度和小数位数（对数值数据类型）。（3）空值：未对列指定值时，该列将出现空值。空值不同于空字符串或数值零，通常表示未知。空值会对查询命令或统计函数产生影响，应尽量少使用空值。（4）约束：约束是数据库自动保持数据完整性的机制，它是通过限制列中数据、行中数据和表之间数据来保持数据完整性。SQLServer2005支持NotNull、Default、Check、PrimaryKey、ForeignKey、Unique6种约束。关于约束的操作将在第8章详细介绍。（5）可以使用SQLServer管理平台和Transact-SQL语句创建表并对表进行维护，包括修改和删除等操作。（6）可以使用SQLServer管理平台和Transact-SQL语句对表中数据进行编辑，包括插入、更新和删除等操作。,