数据库中表的建立

第三章数据库表的建立3.1数据表的根本概念3.2SQLServer的对象命名规那么3.3数据表的创立与维护3.4表数据的添加、修改和删除2024/1/162数据库—表—记录—字段—属性nameagetel.john18123marry20345bookAuth.priceEngjohn123.0toolmarry345.5字段join18123记录3.1数据表的根本概念设计数据库时，首先应该做什么？2024/1/163确定数据库所需的表每个表中数据的类型可以访问每个表的用户创立表及其对象之前做什么？数据类型列数哪些列允许空值是否要使用以及何处使用约束、默认值和规那么所需索引的类型2024/1/164创立表的方法?创立一个基表添加数据2024/1/1652024/1/166分类数据类型说明二进制数据类型存储非字符和文本的数据image可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合char固定长度的非Unicode字符数据varchar可变长度非Unicode数据nchar固定长度的Unicode数据nvarchar可变长度Unicode数据text存储长文本信息ntext存储可变长度的长文本日期和时间日期和时间datetime数字数据该数据仅包含数字，包括整数和浮点数int、smallint整数float、real浮点数货币数据类型十进制货币值moneybit数据类型表示是/否的数据bit存储布尔数据类型3.1.1系统的数据类型2024/1/167binary的存取速度比varbinary快，当表中数据长度很固定时，应考虑用binary类型。二进制数Binary(n)Varbinary9(n)存储空间固定的数据类型，指定长度<8000存储空间可变化的数据类型最多为8000字节>8KB时，image数据2024/1/168字符数据由字母、符号和数字的任意组合组成由单引号‘’括起来字符数据char(n):列大小固定varchar(n):列大小不固定最多为8000字节查询速度较快输入字符长度<n时，自动补空字符>8KB的ASCII字符，text数据2024/1/169text和image数据类型text:最多为231-1个符号，存文字image:最多为231-1个binary数据，存图形两者均是可变长度的数据类型,占较多空间text字段专用于存储字符〔非Unicode〕数据，image字段专用于存储二进制数据，每条记录的text字段或image字段均有一个或多个存储页(2KB)存放它的值2024/1/1610日期及时间型数据datetimeSmalldatetime数字分隔符数字分隔符数字

[时间][时间]dmy:15/[0]4/[19]96ydm:[19]96/15/[0]4mdy[0]4/15/[19]96[0]4-15-[19]964.15.[19]96myd：[0]4/[19]96/15斜杠、连字符、句号dym:15/[19]96/[0]4

ymd:

[19]96/[0]4/15

2024/1/1611p：精度值s:小数位数

4.5精确数值数据decimal[(p[,s])]numeric[(p[,s])]近似数值数据real精度在1－7之间float精度在8－15之间货币数据：money、smallmoney加＄前缀，如$123负货币值，加＄-123整型数据类型(1)bigint：占8字节的存储空间，存储数据范围为－263~263－1。(2)int：占4字节的存储空间，存储数据范围为－231~231－1。(3)smallint：占2字节的存储空间，存储数据范围为－215~215－1。(4)tinyint：占1字节的存储空间，存储数据范围为0~255。2024/1/1612Unicode字符数据

使用Unicode数据类型，列可存储由Unicode标准定义的任何字符，包含由不同字符集定义的所有字符nvarchar列大小不固定<4000个字符数nchar列大小固定<4000个字符数ntext>4000个字符数2024/1/16132024/1/1614特殊类型数据bit:存放1或0的逻辑类型数据，对于0以外的值，一律以1表示User-DefinedDataType:SQLServer还允许用户依据系统数据类型自定义数据类型timestamp:时间标签类型与系统时间无关,只是个单调增加的计数器，其值在数据库中是唯一的若有名为timestamp的字段而未定义类型,自动为timestamp类型，若定义为其它类型也可。为免混乱，最好不这样做2024/1/1615用户自定义数据类型sp_addtypetypename,phystype[,nulltype]用户自定义数据类型的名称SQLServer提供的数据类型名称是否允许用户自定义数据类型为NULL值把zipcode作为自定义数据类型,长度为6EXECsp_addtypezipcode,’char(6)’删除自定义的数据类型zipcodeEXECsp_droptype zipcode3.2SQLServer的对象命名规那么需要命名的对象数据库表列文件组文件约束规那么默认值索引视图触发器存储过程用户自定义函数效劳器角色登陆全文本目录用户自定义类型2024/1/16161.对象的名字必须以Uunicode2.0标准定义的任意字母开头。2.正常对象的名字可多达128个字符，而临时对象的名字可多达116个字符。3.与SQLServer关键字相同或包含内嵌空格的名字必须使用双引号〔“〞〕或方括号〔[]〕。4.对象的名字中不允许出现空格或其他特殊字符。2024/1/16173.2SQLServer的对象命名规那么3.3数据表的创立与维护2024/1/1618在SQLServer中创立数据库可以通过MicrosoftSQLServerManagementStudio图形化界面和在SQL查询分析器输入T-SQL语言假定已经建好三个数据库My_DB1、My_DB2和My_DB3

2024/1/1619表的创立createtable[[database.]owner.]table_name

({col_namecolumn_properties[constraint[…constraint]]|[[,]constraint]}[[,]{next_col_name|next_constraint}…])表所在的数据库表的所有者列名列属性〔1〕使用T-SQL2024/1/1620

(stud_idintCONSTRAINTperid_chkNOTNULLPRIMARYKEY,namenvarchar(5)NOTNULL,birthdaydatetime,gendernchar(1),addressnvarchar(20),telcodechar(12),约束名例：在数据库My_DB1中创立stud_info表，表中包括：学号、姓名、出身年月、性别、地址、号码、，所在系代号，每月生活补贴等。2024/1/1621zipcodechar(6)CONSTRAINTzip_chkCHECK(zipcodeLIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),DeptCodetinyintCONSTRAINTDeptCode_chkCHECK(DeptCode<100),salarymoneyDEFAULT260)约束名约束名2024/1/1622例：在My_DB1数据库中创立一张表，包含：学年度、学号，数学、英语、计算机、化学和物理等成绩本例是一个多字段内容组合作为主键的例子。创立的新表名字为stud_score：2024/1/1623CREATETABLEMy_DB1.dbo.stud_score(yearintNOTNULL,stud_idintNOTNULL,math_scorenumeric(4,1) CHECK(math_score>=0andmath_score<=100),engl_scorenumeric(4,1) CHECK(engl_score>=0andengl_score<=100),2024/1/1624comp_scorenumeric(4,1) CHECK(comp_score>=0andcomp_score<=100),chem_scorenumeric(4,1) CHECK(chem_score>=0andchem_score<=100),phys_scorenumeric(4,1) CHECK(phys_score>=0andphys_score<=100),CONSTRAINTpk_chk PRIMARYKEY(year,stud_id))约束名2024/1/1625本例中的主键定义为primarykey(year,stud_id)以year和stud_id的组合作为主键一个表只能有一个主键且值唯一需要多列组合作为主键时要把作为主键的列用括号组合起来2024/1/16263.3.2

数据库中表的修改

(1)ALTER命令

ALTERTABLE[[database.]owner.]table_name[WITHNOCHECK]ADD{[column_namedatatype[column_constraints]|[[,]table_constraint]]}…

|不指定此选项时(默认情况)，增加的约束要对现有数据进行检查，假设发现违反约束，ALTERTABLE语句将中止运行ADD允许在现存的表中增加新数据项或者一个约束2024/1/1627例：向表stud_info中参加字段名为dormitory的一列，以记录该学生的宿舍号。该列的属性为nvarchar(10)，缺省值为’紫荆公寓8号楼’ADDdormitorynvarchar(20)NULLDEFAULT(‘紫荆公寓8号楼’)2024/1/1628指定删除数据库中的约束DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name

}[,...n]

DROP指定删除现存表中的一个字段或约束指定删除数据库中的列drop和add子句不要同时出现2024/1/1629值得注意的是：在删除某列时，基于该列的所有索引和约束必须首先被删除掉。例如，如果用户运行下面的命令想删除新增加列dormitory时，将出现错误。usemydbaltertablestud_infodropcolumndormitory由于默认值约束的存在使删除该列失败。这就需要在删除该列之前，先将其上的约束删除可以手动将字段dormitory上的默认值删除，然后再运行上述命令2024/1/16303.3.3

表的删除

USEMy_DB1godroptablestud_infogo注意：不能使用该命名删除数据库中的系统表。语法格式：droptable表名删除stud_info表：2024/1/16313.3.3

表的重命名

将数据库My_DB1中的表stud_info改名为stud1USEMy_DB1gosp_rename‘stud_info’,’stud1’Go语法格式：sp_rename‘oldname’,’newname’2024/1/16323.4.1

表中添加数据

通过INSERT语句，可以向表中增添新的记录或在记录中插入局部字段的数据INSERT命令的根本语法如下：INSERT[INTO]table_name[(column1,column2…)]values(value1,value2…)表名字段列表相应的值列表3.4表数据的添加、修改和删除2024/1/1633向My_DB1中的stud_info表插入记录useMy_DB1goINSERTstud_infovalues(0844,'张源','12/15/1979','1','北京市海淀区',,'100080',3,260)goINSERTstud_infovalues(9434,'赵明','8/5/1979','1', '上海市浦东区',, '202300',2,260)3.4.1

表中添加数据

2024/1/1634输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应定义表结构时如果定义了缺省值，此时如对相应字段不输入值，那么系统会用缺省值插入表中如果插入的数据不能满足约束性条件，就出错3.4.1

表中添加数据

2024/1/1635如何把查询结果插入到另一张表中在INSERT语句中可用SELECT从同一张表或其他表中选择字段插入到表结构与SELECT查询返回的结果相容的表中，可插入多行3.4.1

表中添加数据

2024/1/1636INSERTtable_name inserttable_column_listSELECTselectable_column_listFROMtable_lsitWHERESearch_conditions需插入记录的表的名称插入到被插入表中的字段名从检索表中取出字段名检索条件

把表table_list中符合检索条件的记录中的column_list字段插入表table_name3.4.1

表中添加数据

2024/1/1637例：查询数据库My_DB1中表stud_info中的stu_id信息，然后将查询结果插入到表stud_score中的stu_id字段中useMy_DB1INSERTintostud_score(stu_id)selectstu_idfromstud_info

3.4.1

表中添加数据

2024/1/1638用UPDATE可以改变表中现存行的数据。UPDATEtable_nameSETcolumn1=modified_value1[,column2=modified_values[,…]][WHERE[column1=value1][,column2=value2]指定要更新数据的表名指定要更新的列及该列改变后的值指定被更新的记录所应满足的条件3.4.2修改表中的数据

2024/1/1639把上例中在表stud_info中新添加的记录进行修改以方便以后的