第5章表的管理

第5章表的管理本章内容5.1SQLServer表概述5.2表的创建与维护5.3表中数据的维护5.1SQLServer表概述在SQL中,一个数据库最多可以存储20亿个表。表是数据库的实体,由行和列组成。每行最多可存储8092字节的内容,每个表最多可以有1024列。设计数据库时,需要考虑的问题：共有哪些表？每个表由哪些列组成？每列的数据类型及列宽？主键？约束与规则等？5.1SQLServer表概述因此,本章的任务就是建立表、管理表。建立表数据类型约束表的定义(Create、Drop、Alter)管理表插入数据(Insert)修改数据(Update)删除数据(Delete)5.1.1数据类型简介数据类型SQLServer支持4种基本数据类型：字符和二进制数据类型、日期时间数据类型、逻辑数据类型、数字数据类型,用于各类数据值的存储、检索和解释。5.1.1数据类型简介1、字符和二进制数据类型(1)字符数据类型字符数据类型用于存储汉字、英文字母、数字符号和其他各种符号。char数据类型varchar数据类型text数据类型SQLServer允许使用多国语言,采用Unicode标准字符集。为此SQLServer提供多字节的字符数据类型：nchar(n)、nvarchar(n)和ntext。nchar,char,varchar与nvarchar的区别char是以固定长度的,使用时往往要用trim()函数把两边的空格去掉Varchar存储变长数据,但存储效率没有Char高Nchar类型和Nvarchar,为了与其他多种字符的转换,如中文、音标等。字节的存储大小是所输入字符个数的两倍,就是说它是双字节来存储数据的char、varchar一般适用于英文和数字;Nchar、Nvarchar适用中文和其他字符,其中N表示Unicode常量,可以解决多语言字符集之间的转换问题nchar,char,varchar与nvarchar的区别问题：namechar(10)、namenchar(10)分别能存放多少个汉字？为什么？解答：namechar(10)、namenchar(10)分别能存放5个10个汉字原因：char与varchar以单字节形式存放；nchar与nvarchar以双字节形式存放；几种字符类型的最大长度Char、Varchar—最多可以存储8000个字符Nchar、Nvarchar—最多可以存储4000个字符text、ntext—最多可以存储230-1个字符5.1.1数据类型简介(2)二进制数据类型SQLServer二进制数据类型用于存储二进制数或字符串。SQLServer的3种有效二进制数据类型：binary[(n)]数据类型:默认n=1,存放空间n+4个字节,取值1～8000varbinary[(n)]数据类型:基本同上,但空间足够时取实际长度image数据类型:与next类型类似,可存1～231-1字节的二进制。通常用来存放word文档、Excel表格、图像等。5.1.1数据类型简介2、日期时间数据类型日期时间数据类型用于存储日期和时间数据。SQLServer支持两种日期时间数据类型：datetime和smalldatetime。Datetime:1753-1-1～9999-12-31Smalldatetime:1900-1-1～2079-6-65.1.1数据类型简介3、逻辑数据类型SQLServer的逻辑数据类型为bit,也称为位数据类型。适用于判断真/假的场合,长度为一个字节。取值：1、0、NULL注意事项：非0当1处理；位列不允许建索引；如果一个表不多于8个位列,合起来用一个字节存储。5.1.1数据类型简介4、数字数据类型SQLServer提供了多种方法存储数值,SQLServer的数字类型大致可分为4种基本类型。(1)整数数据类型有4种整数数据类型：int、smallint、tinyint和bigint,用于存储不同范围的值。int---存储长度4字节smallint---存储长度2字节tinyint---存储长度1字节bigint---存储长度8字节只要有可能，列尽量取整型5.1.1数据类型简介4、数字数据类型(1)整数数据类型(2)浮点数据类型浮点数据用来存储系统所能提供的最大精度保留的实数数据。由于近似的运算存在误差，因此不能用于需要固定精度的运算，如货币。float(n)----可精确到第15位小数,占8个字节的存储空间。n为指定的精度real(n)----可精确到第7位小数,占4个字节的存储空间5.1.1数据类型简介(3)精确数值数据类型精确数值数据类型用于存储有小数点且小数点后位数确定的实数。格式：decimal[(p[,s])]numeric[(p[,s])]其中,p指定精度,即小数点左边和右边可以存储的十进制数字的最大个数;s指定小数位数。例如:decimal[(5[,3])],输入87.341结果如何？5.1.1数据类型简介(4)货币数据类型除了decimal和numeric类型适用于货币数据的处理外,SQLServer还专门提供了两种货币数据类型：money和smallmoney。显示货币数据值时,小数部分仅保留2位有效数字。5.1.1数据类型简介5、其他数据类型除了以上4种基本数据类型,SQLServer还支持其他一些数据类型。(1)Timestamp：时间戳数据类型,用于自动记录插入或删除操作的时间。(2)sql_variant：可变数据类型。该类型的变量可用来存放大部分SQLServer数据类型的值,最大长度为8016字节,不支持text、ntext、timestamp和sql_variant类型。(3)table：表类型。这是一种特殊的数据类型,存储供以后处理的结果集。(4)uniqueidentifier：GUID类型(GlobalUniqueIDentifier,全局惟一标识符)。5.1.2空值和默认值空值(NULL):空值不同于空白(空字符串)或数值零,通常表示未填写、未知(Unknown)、不可用或将在以后添加的数据。默认值:是指表中数据的默认取值,默认值对象是数据库的对象不依附于具体的表对象,即默认值对象的作用范围是整个数据库。约束(1)非空值约束(NotNull)(2)默认约束(Default)(3)惟一性约束(Unique）(4)主键约束(PrimaryKey,也称主关键字约束)(5)外键约束(ForeignKey,也称为外部关键字约束)5.2表的创建与维护使用SQLServer的管理平台对表进行操作5.2表的创建与维护创建表CREATETABLE表名修改表ALTERTABLE表名删除表DROPTABLE表名5.2表的创建与维护例5-3对计算列使用表达式。CREATETABLEsalarys(姓名varchar(10),基本工资money,奖金money,总计AS基本工资+奖金)5.2表的创建与维护例5-4定义表autouser自动获取用户名称。CREATETABLEautouser(编号intidentity(1,1)NOTNULL,用户代码varchar(18),登录时间ASGetdate(),用户名ASUser_name())5.2表的创建与维护例5-5创建临时表。CREATETABLE#students(学号varchar(8),姓名varchar(10),性别varchar(2),班级varchar(10))临时表用来暂存数据,#表示本地临时表,在当前数据库内使用;##表示全局临时表,可在所有数据库在使用5.3表中数据的维护对表中数据进行维护有两种方法：使用SQLServer管理平台使用Transact-SQL语句(必须熟练掌握)Insert、Select、Update、Delete表中数据的维护:求是指维护表中的数据。4种基本操作:添加或插入新数据检索现有数据更新现有数据删除现有数据5.3表中数据的维护对表中数据进行维护方法之一使用SQLServer管理平台实际操作——在数据库中打开表直接实现插入、修改、删除问题1:使用SQLServer管理平台可以实现少量的插入,但很难实现批量的插入、有条件的大量删除与修改问题2:数据库中的数据是交给用户方进行管理的,面对用户的是用户与数据库操作的接口。也就是不能在SQLServer管理平台上操作，如何实现？——应用程序开发者用SQL语句实现5.3.1插入数据插入数据语法格式如下：INSERT[INTO]{table_name|view_name}{[(column_list)]{VALUES(value_list)|derived_table|execute_statement}}|DEFAULTVALUES5.3.1插入数据注意：Insert…Values…一次只能插入一行如果插入的数据违反约束或规则，则插入会失败插入的值可以是指定的值集,也可以是Select语句的执行得到的一行(column_list)与(value_list)中顺序必须一致(value_list)中的值必须是常量、变量或表达表,表达式中不能包含Select或Execute语句5.3.1插入数据例5-10使用简单的INSERT语句。USESalesGOINSERTSupplierVALUES('S001','华科电子有限公司','施宾彬','朝阳路56号','2636565')例5-11显式指定列列表。INSERTSales.dbo.Supplier(supplier_id,supplier_name,linkman_name,address,telephone)VALUES('S001','华科电子有限公司','施宾彬','朝阳路56号','2636565')5.3.1插入数据INSERTSales.dbo.Supplier(Supplier_id,Supplier_name,telephone)VALUES('S003','韦力电子实业公司','4561681')INSERTSales.dbo.Supplier(Supplier_name,telephone,Supplier_id)VALUES('韦力电子实业公司','4561681','S003')课堂练习(1)将一个新学生元组(学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('200215128','陈冬','男','IS',18)；课堂练习(2)将学生张成民的信息插入到Student表中。INSERTINTOStudentVALUES('200215126','张成民','男',18，'CS')(3)插入一条选课记录('200215128‘,'1')。INSERTINTOSC(Sno，Cno)VALUES('200215128','1')INSERTINTOSCVALUES('200215128','1',NULL)5.3.2修改数据修改数据语法格式：UPDATE{table_name|view_name}SET{{column_name|@variable}=expression}[,…n][FROM{table_source}[,…n]][WHEREsearch_condition]5.3.2修改数据例5-14使用简单的UPDATE语句。UPDATECustomer2SETlinkman_name='佚名',address=NULL,telephone=''例5-15在UPDATE语句中使用WHERE子句。UPDATECustomer2SETtelephone='0731-'+telephoneWHERELEN(telephone)=75.3.2修改数据例5-16在UPDATE语句中使用来自另一个表的信息UPDATESe