SQLServer数据库应用第三章.ppt

1,第三章数据表的建立,2,本章主要内容,表的概念SQL Server 的数据类型数据库中表的操作,3,数据库表记录字段属性,3.1 表的概念,字段,记录,4,5,设计数据库时，首先应该做什么？,确定数据库所需的表每个表中数据的类型可以访问每个表的用户,6,创建表及其对象之前做什么？,数据类型列数那些列允许空值是否要使用以及何处使用约束、默认值和规则所需索引的类型,7,创建表的方法?,创建一个基表添加数据,8,数据类型定义了各列允许使用的数据值。,3.2 SQL Server 的数据类型,9,指定数据类型的方法？,使用SQL Server 2005 系统数据类型创建基于系统数据类型的别名数据类型从在Microsoft.NET Framework公共语言运行时创建的用户定义类型,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,存储空间固定的数据类型，指定长度8000,存储空间可变化的数据类型,8KB时，image数据,13,字符数据,由字母、符号和数字的任意组合组成,由单引号括起来,字符数据,char(n):列大小固定,varchar(n):列大小不固定,查询速度较快,输入字符长度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-Defined DataType:SQL Server还允许用户依据系统数据类型自定义数据类型,20,关于自定义数据类型,sp_addtype typename,phystype,nulltype,用户自定义数据类型的名称,SQL Server提供的数据类型名称,是否允许用户自定义数据类型为NULL值,把zipcode作为自定义数据类型,长度为6EXEC sp_addtype zipcode,char(6)删除自定义的数据类型zipcodeEXEC sp_droptypezipcode,21,xml数据类型,xml数据类型允许用户在SQL Server数据库中存储XML文档和片段,22,SQL SERVER对象标识符,需要命名的对象数据库 表 列 文件组 文件约束 规则 默认值 索引 视图 触发器 存储过程 用户自定义函数服务器 角色 登陆 全文本目录 用户自定义类型,23,命名规则,1.对象的名字必须以Uunicode2.0规范定义的任意字母开头。2.正常对象的名字可多大128个字符，而临时对象的名字可多大116个字符。3.与SQL Server关键字相同或包含内嵌空格的名字必须使用双引号（“”）或方括号（）。,24,3.3 数据库中表的操作,在SQL Server中创建数据库可以通过Microsoft SQL Server Management Studio图形化界面和在SQL查询分析器输入T-SQL语言,假定已经建好三个数据库My_DB1、My_DB2和My_DB3,25,表的创建,create table database.owner.table_name(col_name column_properties constraintconstraint|,constraint,next_col_name|next_constraint),表所在的数据库,表的所有者,列名,列属性,（1）使用T-SQL,26,(stud_id int CONSTRAINT perid_chk NOT NULL PRIMARY KEY,name nvarchar(5)NOT NULL,birthday datetime,gender nchar(1),address nvarchar(20),telcode char(12),约束名,例：在数据库My_DB1中创建stud_info表，表中包括：学号、姓名、出身年月、性别、地址、电话号码、邮编，所在系代号，每月生活补贴等。,27,zipcode char(6)CONSTRAINT zip_chk CHECK(zipcode LIKE0-90-90-90-90-90-9),DeptCode tinyint CONSTRAINT DeptCode_chk CHECK(DeptCode100),salary money DEFAULT 260),约束名,约束名,28,例：在My_DB1数据库中创建一张表，包含：学年度、学号，数学、英语、计算机、化学和物理等成绩,本例是一个多字段内容组合作为主键的例子。创建的新表名字为stud_score：,29,CREATE TABLE My_DB1.dbo.stud_score(year int NOT NULL,stud_id int NOT NULL,math_score numeric(4,1)CHECK(math_score=0 and math_score=0 and engl_score=100),30,comp_score numeric(4,1)CHECK(comp_score=0 and comp_score=0 and chem_score=0 and phys_score=100),CONSTRAINT pk_chk PRIMARY KEY(year,stud_id),约束名,31,本例中的主键定义为primary key(year,stud_id)以year和stud_id的组合作为主键,一个表只能有一个主键且值唯一,需要多列组合作为主键时要把作为主键的列用括号组合起来,32,带有参照性约束的表的创建,例：在数据库My_DB2中建一个名为device_manage的表，包括的信息有：仪器编号仪器名称仪器所属试验室的实验室名称同种仪器的数量仪器的购买价格仪器的供货商,33,CREATE TABLE My_DB2.dbo.device_manage(dev_id varchar(15)CONSTRAINT pk_chk NOT NULL PRIMARY KEY,dev_name varchar(20)NOT NULL,lab_id varchar(20)NOT NULL,dev_qty int,unit_price money,supply_id varchar(15),34,再在同一数据库中创建一张用于记录试验仪器使用情况的表device_use。信息有：实验名称实验地点实验日期试验学生学号该学生使用的仪器编号,35,假设每次实验中每个学生用且只用同一台仪器，该仪器必须为device_manage表中所记录的仪器，即表device_use中的仪器编号必须参照表device_manage中的仪器编号。,36,CREATE TABLE My_DB2.dbo.device_use(experiment_name varchar(20)，experiment_lab varchar(20),experiment_date datetime,stud_id int,dev_id varchar(15)CONSTRAINT fk_chk REFERENCES device_manage(dev_id),37,（2）使用图形化界面,指定数据库表单击右键新建表：字段、完整性约束、表名,38,3.3.2 表的删除,USE My_DB1godrop table stud_infogo,删除stud_info表：,39,3.3.3 数据库中表的修改,(1)ALTER 命令,ALTER TABLE database.owner.table_nameWITH NOCHECKADDcolumn_name datatype column_constraints|,table_constraint|,不指定此选项时(默认情况)，增加的约束要对现有数据进行检查，若发现违反约束，ALTER TABLE语句将中止运行,ADD允许在现存的表中增加新数据项或者一个约束,40,指定删除数据库中的约束,DROP CONSTRAINTconstraint_name|COLUMN column_name,.n,DROP指定删除现存表中的一个字段或约束,指定删除数据库中的列,41,例：向表stud_info中加入字段名为dormitory 的一列，以记录该学生的宿舍号。该列的属性为nvarchar(10)，缺省值为紫荆公寓8号楼,ADD dormitory nvarchar(20)NULL DEFAULT(紫荆公寓8号楼),drop和add子句不要同时出现,42,值得注意的是：在删除某列时，基于该列的所有索引和约束必须首先被删除掉。,例如，如果用户运行下面的命令想删除新增加列dormitory时，将出现错误。DROP COLUMN dormitory,由于默认值约束的存在使删除该列失败。这就需要在删除该列之前，先将其上的约束删除,可以手动将字段dormitory上的默认值删除，然后再运行上述命令,43,(2)INSERT命令,通过INSERT语句，可以向表中增添新的记录或在记录中插入部分字段的数据INSERT命令的基本语法如下：INSERT INTO table_name(column1,column2)values(value1,value2),表名,字段列表,相应的值列表,44,向My_DB1中的stud_info表插入记录,use My_DB1goINSERT stud_info values(0811,张源,12/15/1979,1,北京市海淀区,010-64572345,100080,3,260)goINSERT stud_info values(970890,赵明,8/5/1979,1,上海市浦东区,021-64897232,201700,2,260),45,输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应定义表结构时如果定义了缺省值，此时如对相应字段不输入值，则系统会用缺省值插入表中如果插入的数据不能满足约束性条件，就出错,46,由于参照性约束的存在，必须先向被参照的表中插入数据，然后再向定义了参照性约束的表中输入数据,问题：定义了参照性约束的表，输入数据时应注意些什么？,47,例：表device_manage中的第一个字段与表device_use中的最后一个字段是参照性约束的关系，被参照的表为device_manage，在表device_use中定义了参照性约束，其输入方法如下：,48,use My_DB2goINSERT device_manage values(0789,分光计,光学实验室,20,5000,北京光学仪器厂)goINSERT device_manage values(0284,高能电子管,电子实验室,50,900,中科院电子所),先向device_manage表中插入记录,49,然后再向device_use表中增加记录,use My_DB2goINSERT device_use values(原子光谱分析,化学实验室,5/20/2000,970811,0789)goINSERT device_use values(原子光谱分析,化学实验室,5/20/2000,970890,0789)go,50,由于存在参照约束关系，device_use表中的dev_id字段的值必须是device_manage表的dev_id字段中的值,INSERT device_use values(发动机振动分析,发动机实验室,3/5/2001,980609,0234),51,如何插入部分数据？,可在INSERT子句中指定列名，values子句中的数据项与指定的列相对应，未列出的列必须具有允许NULL或timestamp或IDENTITY或DEFAULT四种定义的条件之一。跳过的列将以缺省值或者用NULL填充,52,例：向dev_manage表新增一行，但只输入dev_id、dev_name、lab_id字段的值。,use My_DB2goINSERT device_manage(dev_id,dev_name,lab_id)values(0201,滤光片,光学实验室),53,如何把查询结果插入到另一张表中,在INSERT语句中可用SELECT从同一张表或其他表中选择字段插入到表结构与SELECT查询返回的结果相容的表中，可插入多行,54,INSERT table_name inserttable_column_listSELECT selectable_column_listFROM table_lsitWHERE Search_conditions,需插入记录的表的名称,插入到被插入表中的字段名,从检索表中取出字段名,检索条件,把表table_list中符合检索条件的记录中的column_list字段插入表table_name,55,例：查询数据库My_DB2中表device_manage中的dev_id信息，然后将查询结果插入到表device_use中的dev_id字段中,use My_DB2INSERT into device_use(dev_id)select dev_id from device_manage,56,(3)UPDATE语句,用UPDATE可以改变表中现存行的数据。UPDATE table_nameSET column1=modified_value1,column2=modified_values,WHERE column1=value1,column2=value2,指定要更新数据的表名,指定要更新的列及该列改变后的值,指定被更新的记录所应满足的条件,57,把上例中在表device_use中新添加的记录进行修改以方便以后的使用,use My_DB2UPDATE device_useSET experiment_name=激光波长测定,experiment_date=6/5/2000,stud_id=970811WHERE(stud_id is NULL and dev_id=0201),58,表device_use中字段dev_id=0201且字段stud_id为空的记录被更改为如下图所示的最后一行的内容,59,(4)DELETE语句,DELETE子句将删除一行或多行记录DELETE FROM table_nameWHERE column1=value1,column2=value2WHERE子句用来指定删除行的条件,60,例:删除数据库My_DB1中表stud_score中的所有记录,USE My_DB1DELETE from stud_score,61,例：删除数据库My_DB2中表device_use中学号为空的记录,Use My_DB2DELETE from device_useWHERE stud_id is NULL,