《SQLServer数据库应用第三章》由会员分享，可在线阅读，更多相关《SQLServer数据库应用第三章（60页珍藏版）》请在金锄头文库上搜索。
1、第三章第三章数据表的建立数据表的建立1SQLServer数据库应用第三章本章主要内容本章主要内容表的概念表的概念SQL Server 的数据类型的数据类型数据库中表的操作数据库中表的操作2SQLServer数据库应用第三章数据库数据库表表记录记录字段字段属性属性nameagetel.john18123marry 20345book Auth. priceEngjohn123.0toolmarry345.53.1 3.1 表的概念表的概念字段join18123记录3SQLServer数据库应用第三章DBComp. priceuserSql Ser. MS10000 Qin Hai Univ.Or
2、acleOracle 20000 Beijing Univ.Access Ms300Peoples Univ.4SQLServer数据库应用第三章设计数据库时，首先应该做什么？设计数据库时，首先应该做什么？确定数据库所需的表确定数据库所需的表每个表中数据的类型每个表中数据的类型可以访问每个表的用户可以访问每个表的用户5SQLServer数据库应用第三章创建表及其对象之前做什么？创建表及其对象之前做什么？数据类型数据类型列数列数那些列允许那些列允许空值空值是否要使用以及何处使用约束、默认值是否要使用以及何处使用约束、默认值和规则和规则所需索引的类型所需索引的类型6SQLServer数据库应用第三
3、章创建表的方法创建表的方法?创建一个基表创建一个基表添加数据添加数据7SQLServer数据库应用第三章数据类型定义了各列允许使用的数据类型定义了各列允许使用的数据值。数据值。3.2 SQL Server 3.2 SQL Server 的数据类型的数据类型8SQLServer数据库应用第三章指定数据类型的方法？指定数据类型的方法？使用使用SQL Server 2005 系统数据类型系统数据类型创建基于系统数据类型的别名数据类型创建基于系统数据类型的别名数据类型从在从在Microsoft .NET Framework公共语言运公共语言运行时创建的用户定义类型行时创建的用户定义类型9SQLServ
4、er数据库应用第三章需要定义数据类型的情况？需要定义数据类型的情况？建立表字段建立表字段申请申请局部变量局部变量申请存储过程中的局部变量申请存储过程中的局部变量转换数据类型转换数据类型本章讨论的内容10SQLServer数据库应用第三章数数据据类类型型二进制型二进制型 b binary(n),varbinary(n)，image字符型字符型 char(n) char(n)，varchar(n)varchar(n)日期及时间日期及时间 datetime datetime,smalldatetime smalldatetime 精确数值型精确数值型 decimal(p,s),numeric(p,s
5、)decimal(p,s),numeric(p,s)intint等等近似数值型近似数值型 float(n) float(n)，Real Real 货币型货币型 money money,smallmoney smallmoney 特殊类型特殊类型 bit,timestamp bit,timestamp11SQLServer数据库应用第三章binarybinary的存取速度比的存取速度比varbinaryvarbinary快，当表中快，当表中数据长度很固定时，应数据长度很固定时，应考虑用考虑用binarybinary类型。类型。二二进进制制数数binarybinaryvarbinaryvarbin
6、ary存储空间固定的存储空间固定的数据类型，指定数据类型，指定长度长度80008KB时，image数据12SQLServer数据库应用第三章字符数据字符数据由字母、符号和数字的任意组合组成由字母、符号和数字的任意组合组成由单引号由单引号括起来括起来字符字符数据数据char(n):char(n):列大小固定列大小固定varchar(n):varchar(n):列大小不固定列大小不固定最多为最多为80008000字节字节查询速查询速度较快度较快输入字符输入字符长度长度8KB的ASCII字符，text数据13SQLServer数据库应用第三章text和和image数据类型数据类型text:最多为最多
7、为231-1个符号，存文字个符号，存文字image:最多为最多为231-1个个binary数据，存图数据，存图形形两者均是可变长度的数两者均是可变长度的数据类型据类型,占较多空间占较多空间text字段专用于存储字符（非字段专用于存储字符（非Unicode）数据，数据，image字段专用于存储二进制数字段专用于存储二进制数据，每条记录的据，每条记录的text字段或字段或image字段均字段均有一个或多个存储页有一个或多个存储页(2KB)存放它的值存放它的值14SQLServer数据库应用第三章日期日期及时及时间型间型数据数据datetimedatetimeSmalldatetimeSmallda
8、tetime数字数字分隔符分隔符数字数字 分隔符分隔符数字数字时间时间时间时间dmy: 15/04/1996dmy: 15/04/1996ydm: 1996/15/04ydm: 1996/15/04mdymdy04/15/199604/15/199604-15-199604-15-19964.15.19964.15.1996mydmyd：04/1996/1504/1996/15斜杠、连字斜杠、连字符、句号符、句号dym: 15/1996/04dym: 15/1996/04ymd:ymd:1996/04/151996/04/1515SQLServer数据库应用第三章p：精度值：精度值s:小数位小
9、数位数数4.5精确数精确数值数据值数据decimal(p,s)numeric(p,s)近似数近似数值数据值数据real精度在精度在17之间之间float精度在精度在815之间之间货币数据：货币数据：money、smallmoney加前缀，如加前缀，如$123负货币值，加负货币值，加-12316SQLServer数据库应用第三章整型数据类型整型数据类型(1) bigint：占：占8字节的存储空间，存储数字节的存储空间，存储数据范围为据范围为2632631。(2) int：占占4字字节节的的存存储储空空间间，存存储储数数据据范围为范围为 2312311。(3) smallint：占：占2字节的存储
10、空间，存字节的存储空间，存储数据范围为储数据范围为2152151。(4) tinyint：占占1字字节节的的存存储储空空间间，存存储储数据范围为数据范围为0255。17SQLServer数据库应用第三章Unicode字符数据字符数据 使用使用Unicode数据类型，列可存储由数据类型，列可存储由Unicode标准定义的任何字符，包含由不同标准定义的任何字符，包含由不同字符集定义的所有字符字符集定义的所有字符nvarchar 列大小不固定列大小不固定 4000个字符数个字符数nchar 列大小固定列大小固定 4000个字符数个字符数18SQLServer数据库应用第三章特特殊殊类类型型数数据据b
11、it:存放存放1或或0的逻辑类型数据，对于的逻辑类型数据，对于0以外以外的值，一律以的值，一律以1表示表示User-DefinedDataType:SQLServer还允许还允许用户依据系统数据类型自定义数据类型用户依据系统数据类型自定义数据类型timestamp:时间标签类型与系统时间无关时间标签类型与系统时间无关,只是个单调增加的计数器，其值在数据库只是个单调增加的计数器，其值在数据库中是唯一的中是唯一的若有名为若有名为timestamptimestamp的字段而未的字段而未定义类型定义类型, ,自动为自动为timestamptimestamp类型，类型，若定义为其它类型也可。为免若定义为
12、其它类型也可。为免混乱，最好不这样做混乱，最好不这样做19SQLServer数据库应用第三章关于自定义数据类型关于自定义数据类型sp_addtypetypename,phystype,nulltype用户自定用户自定义数据类义数据类型的名称型的名称SQLServer提供的数据提供的数据类型名称类型名称是否允许用户自是否允许用户自定义数据类型为定义数据类型为NULL值值把把zipcode作为自定义数据类型作为自定义数据类型,长度为长度为6EXECsp_addtypezipcode,char(6)删除自定义的数据类型删除自定义的数据类型zipcodeEXECsp_droptypezipcode20
13、SQLServer数据库应用第三章xml数据类型数据类型xml数据类型允许用户在数据类型允许用户在SQL Server数数据库中存储据库中存储XML文档和片段文档和片段21SQLServer数据库应用第三章SQL SERVER对象标识符对象标识符需要命名的对象需要命名的对象数据库数据库 表表 列列 文件组文件组 文件文件约束约束 规则规则 默认值默认值 索引索引 视图视图 触发器触发器 存储过程存储过程 用户自定义函数用户自定义函数服务器服务器 角色角色 登陆登陆 全文本目录全文本目录 用户自用户自定义类型定义类型22SQLServer数据库应用第三章命名规则命名规则1.对象的名字必须以对象的
14、名字必须以Uunicode2.0规范规范定义的任意字母开头。定义的任意字母开头。2.正常对象的名字可多大正常对象的名字可多大128个字符，而个字符，而临时对象的名字可多大临时对象的名字可多大116个字符。个字符。3.与与SQL Server关键字相同或包含内嵌关键字相同或包含内嵌空格的名字必须使用双引号（空格的名字必须使用双引号（“ ”）或）或方括号（方括号（ ）。）。23SQLServer数据库应用第三章3.3 3.3 数据库中表的操作数据库中表的操作在在SQL ServerSQL Server中创建数据库可以通过中创建数据库可以通过Microsoft SQL Server Manageme
15、nt StudioMicrosoft SQL Server Management Studio图形化界面和在图形化界面和在SQLSQL查询分析器输入查询分析器输入T-SQLT-SQL语语言言假定已经建好三个数据库假定已经建好三个数据库My_DB1My_DB1、My_DB2My_DB2和和My_DB3My_DB324SQLServer数据库应用第三章3.3.13.3.1表的创建表的创建createtabledatabase.owner.table_name(col_namecolumn_propertiesconstraintconstraint|,constraint,next_col_nam
16、e|next_constraint)表所在的表所在的数据库数据库表的所表的所有者有者列名列名列属性列属性（1 1）使用）使用）使用）使用T-SQLT-SQL25SQLServer数据库应用第三章CREATETABLEMy_DB1.dbo.stud_info(stud_idintCONSTRAINTperid_chkNOTNULLPRIMARYKEY,namenvarchar(5)NOTNULL,birthdaydatetime,gendernchar(1),addressnvarchar(20),telcodechar(12),约约束束名名例：在数据库例：在数据库My_DB1My_DB1中创建
17、中创建stud_infostud_info表，表，表中包括：学号、姓名、出身年月、性表中包括：学号、姓名、出身年月、性别、地址、电话号码、邮编，所在系代别、地址、电话号码、邮编，所在系代号，每月生活补贴等。号，每月生活补贴等。26SQLServer数据库应用第三章zipcodechar(6)CONSTRAINTzip_chkCHECK(zipcodeLIKE0-90-90-90-90-90-9),DeptCodetinyintCONSTRAINTDeptCode_chkCHECK(DeptCode=0andmath_score=0andengl_score=0andcomp_score=0an
18、dchem_score=0andphys_score=100),CONSTRAINTpk_chkPRIMARYKEY(year,stud_id)约束名约束名30SQLServer数据库应用第三章本例中的主键定义为本例中的主键定义为primarykey(year,stud_id)以以year和和stud_id的组合作为主键的组合作为主键一个表只能一个表只能有一个主键有一个主键且值唯一且值唯一需要多列需要多列组合作为主键组合作为主键时要把作为主时要把作为主键的列用括号键的列用括号组合起来组合起来31SQLServer数据库应用第三章带有参照性约束的表的创建带有参照性约束的表的创建例：在数据库例：在
19、数据库My_DB2中建一个名为中建一个名为device_manage的表，包括的信息有：的表，包括的信息有：仪器编号仪器编号仪器名称仪器名称仪器所属试验室的实验室名称仪器所属试验室的实验室名称同种仪器的数量同种仪器的数量仪器的购买价格仪器的购买价格仪器的供货商仪器的供货商32SQLServer数据库应用第三章CREATETABLEMy_DB2.dbo.device_manage(dev_idvarchar(15)CONSTRAINTpk_chkNOTNULLPRIMARYKEY,dev_namevarchar(20)NOTNULL,lab_idvarchar(20)NOTNULL,dev_qt
20、yint,unit_pricemoney,supply_idvarchar(15)33SQLServer数据库应用第三章再在同一数据库中创建一张用于再在同一数据库中创建一张用于记录试验仪器使用情况的表记录试验仪器使用情况的表device_use。信息有：。信息有：实验名称实验名称实验地点实验地点实验日期实验日期试验学生学号试验学生学号该学生使用的仪器编号该学生使用的仪器编号34SQLServer数据库应用第三章假设每次实验中每个学生用且只用假设每次实验中每个学生用且只用同一台仪器，该仪器必须为同一台仪器，该仪器必须为device_manage表中所记录的仪器，表中所记录的仪器，即表即表devi
21、ce_use中的仪器编号必须参中的仪器编号必须参照表照表device_manage中的仪器编号。中的仪器编号。35SQLServer数据库应用第三章CREATETABLEMy_DB2.dbo.device_use(experiment_namevarchar(20)，experiment_labvarchar(20),experiment_datedatetime,stud_idint,dev_idvarchar(15)CONSTRAINTfk_chkREFERENCESdevice_manage(dev_id)36SQLServer数据库应用第三章（2）使用图形化界面）使用图形化界面指定数据
22、库指定数据库表表单击右键单击右键新建新建表：字段、完整性约束、表名表：字段、完整性约束、表名 37SQLServer数据库应用第三章3.3.2 3.3.2 表的删除表的删除USEMy_DB1godroptablestud_infogo删除删除stud_info表：表：38SQLServer数据库应用第三章3.3.3 3.3.3 数据库中表的修改数据库中表的修改(1)ALTER (1)ALTER 命令命令ALTERTABLEdatabase.owner.table_nameWITHNOCHECKADDcolumn_namedatatypecolumn_constraints|,table_con
23、straint|不指定此选项不指定此选项时时( (默认情况默认情况) )，增加的约束要增加的约束要对现有数据进对现有数据进行检查，若发行检查，若发现违反约束，现违反约束，ALTER TABLEALTER TABLE语语句将中止运行句将中止运行ADD允许在现存的表中增加新数允许在现存的表中增加新数据项或者一个约束据项或者一个约束39SQLServer数据库应用第三章指定删除指定删除数据库中数据库中的约束的约束DROPCONSTRAINTconstraint_name|COLUMNcolumn_name ,.n DROP指定删除指定删除现存表中的一个现存表中的一个字段或约束字段或约束指定删除数指定
24、删除数据库中的列据库中的列40SQLServer数据库应用第三章例：向表例：向表stud_infostud_info中加入字段名为中加入字段名为dormitory dormitory 的一列，以记录该学生的宿舍号。的一列，以记录该学生的宿舍号。该列的属性为该列的属性为nvarchar(10)nvarchar(10)，缺省值为，缺省值为紫紫荆公寓荆公寓8 8号楼号楼 ALTERTABLEMy_DB1.dbo.stud_infoADDdormitorynvarchar(20)NULLDEFAULT(紫荆公寓紫荆公寓8号楼号楼)drop和和add子句不要同时出现子句不要同时出现41SQLServer
25、数据库应用第三章值得注意的是：在删除某列时，基于该：在删除某列时，基于该列的所有索引和约束必须首先被删除掉。列的所有索引和约束必须首先被删除掉。例如，如果用户运行下面的命令想删除例如，如果用户运行下面的命令想删除新增加列新增加列dormitory时，将出现错误。时，将出现错误。ALTERTABLEmy_DB1.dbo.stud_infoDROPCOLUMNdormitory由于默认值约束的存在由于默认值约束的存在使删除该列失败。这就使删除该列失败。这就需要在删除该列之前，需要在删除该列之前，先将其上的约束删除先将其上的约束删除可以手动将字可以手动将字段段dormitorydormitory上上
26、的默认值删除，的默认值删除，然后再运行上然后再运行上述命令述命令 42SQLServer数据库应用第三章(2)INSERT命令命令 通过通过INSERTINSERT语句，可以向表中增添新语句，可以向表中增添新的记录或在记录中插入部分字段的数据的记录或在记录中插入部分字段的数据INSERTINSERT命令的基本语法如下：命令的基本语法如下：INSERT INTO table_nameINSERT INTO table_name(column1,column2)(column1,column2) values(value1,value2) values(value1,value2)表名表名字段列表
27、字段列表相应的值列表相应的值列表43SQLServer数据库应用第三章向向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)44SQLServer数据库应用第三章输入项的顺序和数据类型必须与表输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应中字段的顺序和数据类型相对应定义
28、表结构时如果定义了缺省值，定义表结构时如果定义了缺省值，此时如对相应字段不输入值，则系此时如对相应字段不输入值，则系统会用缺省值插入表中统会用缺省值插入表中如果插入的数据不能满足约束性条如果插入的数据不能满足约束性条件，就出错件，就出错45SQLServer数据库应用第三章由于参照性约束的存在，必须先向由于参照性约束的存在，必须先向被参照的表中插入数据，然后再向被参照的表中插入数据，然后再向定义了参照性约束的表中输入数据定义了参照性约束的表中输入数据问题：定义了参照性约束的表，问题：定义了参照性约束的表，输入数据时应注意些什么？输入数据时应注意些什么？46SQLServer数据库应用第三章例：
29、表例：表device_manage中的第一个字段中的第一个字段与表与表device_use中的最后一个字段是参中的最后一个字段是参照性约束的关系，被参照的表为照性约束的关系，被参照的表为device_manage，在表，在表device_use中定义中定义了参照性约束，其输入方法如下：了参照性约束，其输入方法如下：47SQLServer数据库应用第三章useMy_DB2goINSERTdevice_managevalues(0789,分光计分光计,光学实验室光学实验室,20,5000,北京光学仪器厂北京光学仪器厂)goINSERTdevice_managevalues(0284,高能电子管高能
30、电子管,电子实验室电子实验室,50,900,中科院电子所中科院电子所)先向先向device_manage表中插入记录表中插入记录48SQLServer数据库应用第三章然后再向然后再向device_use表中增加记录表中增加记录useMy_DB2goINSERTdevice_usevalues(原子光谱分析原子光谱分析,化学实验室化学实验室,5/20/2000,970811,0789)goINSERTdevice_usevalues(原子光谱分析原子光谱分析,化学实验室化学实验室,5/20/2000,970890,0789)go49SQLServer数据库应用第三章由于存在参照约束关系，由于存在
31、参照约束关系，device_use表中表中的的dev_id字段的值必须是字段的值必须是device_manage表的表的dev_id字段中的值字段中的值INSERTdevice_usevalues(发动机振动分析发动机振动分析,发动机实验室发动机实验室,3/5/2001,980609,0234)50SQLServer数据库应用第三章如何插入如何插入部分数据部分数据？可在可在INSERT子句中指定列子句中指定列名，名，values子句中的数据项子句中的数据项与指定的列相对应，未列出与指定的列相对应，未列出的列必须具有允许的列必须具有允许NULL或或timestamp或或IDENTITY或或DEF
32、AULT四种定义的条件四种定义的条件之一。跳过的列将以缺省值之一。跳过的列将以缺省值或者用或者用NULL填充填充51SQLServer数据库应用第三章例：向例：向dev_manage表新增一行，但只表新增一行，但只输入输入dev_id、dev_name、lab_id字段的字段的值。值。use My_DB2goINSERT device_manage (dev_id,dev_name,lab_id) values(0201,滤光片滤光片,光学实验室光学实验室)52SQLServer数据库应用第三章如何把查询如何把查询结果插入到结果插入到另一张表中另一张表中在在INSERTINSERT语句中可用语
33、句中可用SELECTSELECT从同一张表或从同一张表或其他表中选择字段插其他表中选择字段插入到表结构与入到表结构与SELECTSELECT查询返回的结果相容查询返回的结果相容的表中，可插入多行的表中，可插入多行53SQLServer数据库应用第三章INSERTtable_nameinserttable_column_listSELECTselectable_column_listFROMtable_lsitWHERESearch_conditions需插入记录需插入记录的表的名称的表的名称插入到被插入到被插入表中插入表中的字段名的字段名从检从检索表索表中取中取出字出字段名段名检索条件检索条件
34、把表把表table_list中符合检索中符合检索条件的记录中的条件的记录中的column_list字段插入表字段插入表table_name54SQLServer数据库应用第三章例：查询数据库例：查询数据库My_DB2中表中表device_manage中的中的dev_id信息，然后将查询结果插入到表信息，然后将查询结果插入到表device_use中的中的dev_id字段中字段中useMy_DB2INSERTintodevice_use(dev_id)selectdev_idfromdevice_manage55SQLServer数据库应用第三章(3)UPDATE语句语句用用UPDATE可以改变表
35、中现存行的数据。可以改变表中现存行的数据。UPDATEtable_nameSETcolumn1=modified_value1,column2=modified_values,WHEREcolumn1=value1,column2=value2指定要更新数指定要更新数据的表名据的表名指定要更新指定要更新的列及该列的列及该列改变后的值改变后的值指定被更新的记录指定被更新的记录所应满足的条件所应满足的条件56SQLServer数据库应用第三章把上例中在表把上例中在表device_use中新添加的记中新添加的记录进行修改以方便以后的使用录进行修改以方便以后的使用useMy_DB2UPDATEdevi
36、ce_useSETexperiment_name=激光波长测定激光波长测定,experiment_date=6/5/2000,stud_id=970811WHERE(stud_idisNULLanddev_id=0201)57SQLServer数据库应用第三章表表device_use中字段中字段dev_id=0201且字段且字段stud_id为空的记录被更改为为空的记录被更改为如下图所示的最后一行的内容如下图所示的最后一行的内容58SQLServer数据库应用第三章(4)DELETE语句语句DELETE子句将删除一行或多行记录子句将删除一行或多行记录DELETEFROMtable_nameWHEREcolumn1=value1,column2=value2WHERE子句用来指定删除行的条件子句用来指定删除行的条件59SQLServer数据库应用第三章例例:删除数据库删除数据库My_DB1中表中表stud_score中的所有记录中的所有记录USEMy_DB1DELETEfromstud_score60SQLServer数据库应用第三章
