SQLServer数据库应用第三章.ppt

1,第三章数据表的建立,2,本章主要内容,表的概念SQLServer的数据类型数据库中表的操作,3,数据库表记录字段属性,3.1表的概念,字段,记录,4,5,设计数据库时，首先应该做什么？,确定数据库所需的表每个表中数据的类型可以访问每个表的用户,6,创建表及其对象之前做什么？,数据类型列数那些列允许空值是否要使用以及何处使用约束、默认值和规则所需索引的类型,7,创建表的方法?,创建一个基表添加数据,8,数据类型定义了各列允许使用的数据值。,3.2SQLServer的数据类型,9,指定数据类型的方法？,使用SQLServer2005系统数据类型创建基于系统数据类型的别名数据类型从在Microsoft.NETFramework公共语言运行时创建的用户定义类型,10,需要定义数据类型的情况？,建立表字段申请局部变量申请存储过程中的局部变量转换数据类型,本章讨论的内容,11,数据类型,二进制型binary(n),varbinary(n)，image,字符型char(n)，varchar(n),日期及时间datetime,smalldatetime,精确数值型decimal(p,s),numeric(p,s)int等,近似数值型float(n)，Real,货币型money,smallmoney,特殊类型bit,timestamp,12,binary的存取速度比varbinary快，当表中数据长度很固定时，应考虑用binary类型。,二进制数,binary,varbinary,存储空间固定的数据类型，指定长度8KB时，image数据,13,字符数据,由字母、符号和数字的任意组合组成,由单引号括起来,字符数据,char(n):列大小固定,varchar(n):列大小不固定,查询速度较快,输入字符长度8KB的ASCII字符，text数据,14,text和image数据类型,text:最多为231-1个符号，存文字image:最多为231-1个binary数据，存图形,两者均是可变长度的数据类型,占较多空间,text字段专用于存储字符（非Unicode）数据，image字段专用于存储二进制数据，每条记录的text字段或image字段均有一个或多个存储页(2KB)存放它的值,15,日期及时间型数据,datetime,Smalldatetime,数字分隔符数字分隔符数字时间时间,dmy:15/04/1996,ydm:1996/15/04,myd：04/1996/15,斜杠、连字符、句号,dym:15/1996/04,ymd:1996/04/15,16,p：精度值s:小数位数4.5,17,整型数据类型,(1)bigint：占8字节的存储空间，存储数据范围为2632631。(2)int：占4字节的存储空间，存储数据范围为2312311。(3)smallint：占2字节的存储空间，存储数据范围为2152151。(4)tinyint：占1字节的存储空间，存储数据范围为0255。,18,Unicode字符数据使用Unicode数据类型，列可存储由Unicode标准定义的任何字符，包含由不同字符集定义的所有字符nvarchar列大小不固定4000个字符数,19,特殊类型数据,bit:存放1或0的逻辑类型数据，对于0以外的值，一律以1表示,User-DefinedDataType:SQLServer还允许用户依据系统数据类型自定义数据类型,20,关于自定义数据类型,sp_addtypetypename,phystype,nulltype,用户自定义数据类型的名称,SQLServer提供的数据类型名称,是否允许用户自定义数据类型为NULL值,把zipcode作为自定义数据类型,长度为6EXECsp_addtypezipcode,char(6)删除自定义的数据类型zipcodeEXECsp_droptypezipcode,21,xml数据类型,xml数据类型允许用户在SQLServer数据库中存储XML文档和片段,22,SQLSERVER对象标识符,需要命名的对象数据库表列文件组文件约束规则默认值索引视图触发器存储过程用户自定义函数服务器角色登陆全文本目录用户自定义类型,23,命名规则,1.对象的名字必须以Uunicode2.0规范定义的任意字母开头。2.正常对象的名字可多大128个字符，而临时对象的名字可多大116个字符。3.与SQLServer关键字相同或包含内嵌空格的名字必须使用双引号（“”）或方括号（）。,24,3.3数据库中表的操作,在SQLServer中创建数据库可以通过MicrosoftSQLServerManagementStudio图形化界面和在SQL查询分析器输入T-SQL语言,假定已经建好三个数据库My_DB1、My_DB2和My_DB3,25,3.3.1表的创建,createtabledatabase.owner.table_name(col_namecolumn_propertiesconstraintconstraint|,constraint,next_col_name|next_constraint),表所在的数据库,表的所有者,列名,列属性,（1）使用T-SQL,26,CREATETABLEMy_DB1.dbo.stud_info(stud_idintCONSTRAINTperid_chkNOTNULLPRIMARYKEY,namenvarchar(5)NOTNULL,birthdaydatetime,gendernchar(1),addressnvarchar(20),telcodechar(12),约束名,例：在数据库My_DB1中创建stud_info表，表中包括：学号、姓名、出身年月、性别、地址、电话号码、邮编，所在系代号，每月生活补贴等。,27,zipcodechar(6)CONSTRAINTzip_chkCHECK(zipcodeLIKE0-90-90-90-90-90-9),DeptCodetinyintCONSTRAINTDeptCode_chkCHECK(DeptCode=0andmath_score=0andengl_score=0andcomp_score=0andchem_score=0andphys_score=100),CONSTRAINTpk_chkPRIMARYKEY(year,stud_id),约束名,31,本例中的主键定义为primarykey(year,stud_id)以year和stud_id的组合作为主键,一个表只能有一个主键且值唯一,需要多列组合作为主键时要把作为主键的列用括号组合起来,32,带有参照性约束的表的创建,例：在数据库My_DB2中建一个名为device_manage的表，包括的信息有：仪器编号仪器名称仪器所属试验室的实验室名称同种仪器的数量仪器的购买价格仪器的供货商,33,CREATETABLEMy_DB2.dbo.device_manage(dev_idvarchar(15)CONSTRAINTpk_chkNOTNULLPRIMARYKEY,dev_namevarchar(20)NOTNULL,lab_idvarchar(20)NOTNULL,dev_qtyint,unit_pricemoney,supply_idvarchar(15),34,再在同一数据库中创建一张用于记录试验仪器使用情况的表device_use。信息有：实验名称实验地点实验日期试验学生学号该学生使用的仪器编号,35,假设每次实验中每个学生用且只用同一台仪器，该仪器必须为device_manage表中所记录的仪器，即表device_use中的仪器编号必须参照表device_manage中的仪器编号。,36,CREATETABLEMy_DB2.dbo.device_use(experiment_namevarchar(20)，experiment_labvarchar(20),experiment_datedatetime,stud_idint,dev_idvarchar(15)CONSTRAINTfk_chkREFERENCESdevice_manage(dev_id),37,（2）使用图形化界面,指定数据库表单击右键新建表：字段、完整性约束、表名,38,3.3.2表的删除,USEMy_DB1godroptablestud_infogo,删除stud_info表：,39,3.3.3数据库中表的修改,(1)ALTER命令,ALTERTABLEdatabase.owner.table_nameWITHNOCHECKADDcolumn_namedatatypecolumn_constraints|,table_constraint|,不指定此选项时(默认情况)，增加的约束要对现有数据进行检查，若发现违反约束，ALTERTABLE语句将中止运行,ADD允许在现存的表中增加新数据项或者一个约束,40,指定删除数据库中的约束,DROPCONSTRAINTconstraint_name|COLUMNcolumn_name,.n,DROP指定删除现存表中的一个字段或约束,指定删除数据库中的列,41,例：向表stud_info中加入字段名为dormitory的一列，以记录该学生的宿舍号。该列的属性为nvarchar(10)，缺省值为紫荆公寓8号楼,ALTERTABLEMy_DB1.dbo.stud_infoADDdormitorynvarchar(20)NULLDEFAULT(紫荆公寓8号楼),drop和add子句不要同时出现,42,值得注意的是：在删除某列时，基于该列的所有索引和约束必须首先被删除掉。,例如，如果用户运行下面的命令想删除新增加列dormitory时，将出现错误。ALTERTABLEmy_DB1.dbo.stud_infoDROPCOLUMNdormitory,由于默认值约束的存在使删除该列失败。这就需要在删除该列之前，先将其上的约束删除,可以手动将字段dormitory上的默认值删除，然后再运行上述命令,43,(2)INSERT命令,通过INSERT语句，可以向表中增添新的记录或在记录中插入部分字段的数据INSERT命令的基本语法如下：INSERTINTOtable_name(column1,column2)values(value1,value2),表名,字段列表,相应的值列表,44,向My_DB1中的stud_info表插入记录,useMy_DB1goINSERTstud_infovalues(0811,张源,12/15/1979,1,北京市海淀区100080,3,260)goINSERTstud_infovalues(970890,赵明,8/5/1979,1,上海市浦东区201700,2,260),45,输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应定义表结构时如果定义了缺省值，此时如对相应字段不输入值，则系统会用缺省值插入表中如果插入的数据不能满足约束性条件，就出错,46,由于参照性约束的存在，必须先向被参照的表中插入数据，然后再向定义了参照性约束的表中输入数据,问题：定义了参照性约束的表，输入数据时应注意些什么？,47,例：表device_manage中的第一个字段与表device_use中的最后一个字段是参照性约束的关系，被参照的表为device_manage，在表device_use中定义了参照性约束，其输入方法如下：,48,useMy_DB2goINSERTdevice_managevalues(0789,分光计,光学实验室,20,5000,北京光学仪器厂)goINSERTdevice_managevalues(0284,高能电子管,电子实验室,50,900,中科院电子所),先向device_manage表中插入记录,49,然后再向device_use表中增加记录,useMy_DB2goINSERTdevice_usevalues(原子光谱分析,化学实验室,5/20/2000,970811,0789)goINSERTdevice_usevalues(原子光谱分析,化学实验室,5/20/2000,970890,0789)go,50,由于存在参照约束关系，device_use表中的dev_id字段的值必须是device_manage表的dev_id字段中的值,INSERTdevice_usevalues(发动机振动分析,发动机实验室,3/5/2001,980609,0234),51,如何插入部分数据？,可在INSERT子句中指定列名，values子句中的数据项与指定的列相对应，未列出的列必须具有允许NULL或timestamp或IDENTITY或DEFAULT四种定义的条件之一。跳过的列将以缺省值或者用NULL填充,52,例：向dev_manage表新增一行，但只输入dev_id、dev_name、lab_id字段的值。,useMy_DB2goINSERTdevice_manage(dev_id,dev_name,lab_id)values(0201,滤光片,光学实验室),53,如何把查询结果插入到另一张表中,在INSERT语句中可用SELECT从同一张表或其他表中选择字段插入到表结构与SELECT查询返回的结果相容的表中，可插入多行,54,INSERTtable_nameinserttable_column_listSELECTselectable_column_listFROMtable_lsitWHERESearch_conditions,需插入记录的表的名称,插入到被插入表中的字段名,从检索表中取出字段名,检索条件,把表table_list中符合检索条件的记录中的column_list字段插入表table_name,55,例：查询数据库My_DB2中表device_manage中的dev_id信息，然后将查询结果插入到表device_use中的dev_id字段中,useMy_DB2INSERTintodevice_use(dev_id)selectdev_idfromdevice_manage,56,