第3章-数据库中表的建立

1、第三章第三章 数数据库表的建立据库表的建立3.1 数据表的基本概念数据表的基本概念3.2 SQL Server 的对象命名规则的对象命名规则3.3 数据表的创建与维护数据表的创建与维护3.4 表数据的添加、修改和删除表数据的添加、修改和删除2021-12-12数据库数据库表表记录记录字段字段属性属性nameagetel.john18123marry20345bookAuth.priceEngjohn123.0toolmarry 345.5字段join18123记录3.1 数据表的基本概念数据表的基本概念设计数据库时，首先应该做什么？设计数据库时，首先应该做什么？2021-12-13确定数据库所

2、需的表确定数据库所需的表每个表中数据的类型每个表中数据的类型可以访问每个表的用户可以访问每个表的用户创建表及其对象之前做什么？创建表及其对象之前做什么？数据类型数据类型列数列数哪哪些些列允许列允许空值空值是否要使用以及何处使用约束、默认值是否要使用以及何处使用约束、默认值和规则和规则所需索引的类型所需索引的类型2021-12-14创建表的方法创建表的方法?创建一个基表创建一个基表添加数据添加数据2021-12-152021-12-16分 类数据类型说 明二进制数据类型存储非字符和文本的数据image可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合char固定长度的非 Uni

3、code 字符数据varchar可变长度非 Unicode 数据nchar固定长度的 Unicode 数据nvarchar可变长度 Unicode 数据text存储长文本信息ntext存储可变长度的长文本日期和时间日期和时间datetime 数字数据该数据仅包含数字，包括整数和浮点数int、smallint整数float、real浮点数货币数据类型十进制货币值moneybit数据类型表示是/否的数据bit存储布尔数据类型3.1 .1 系统的数据类型系统的数据类型2021-12-17binarybinary的存取速度比的存取速度比varbinaryvarbinary快，当表中快，当表中数据长度很

4、固定时，应数据长度很固定时，应考虑用考虑用binarybinary类型。类型。二二进进制制数数B Binaryinary(n)(n)V Varbinaryarbinary9(n)9(n)存储空间固定的存储空间固定的数据类型，指定数据类型，指定长度长度80008KB时，image数据2021-12-18字符数据字符数据由字母、符号和数字的任意组合组成由字母、符号和数字的任意组合组成由单引号由单引号括起来括起来字符字符数据数据char(n):char(n):列大小固定列大小固定varchar(n):varchar(n):列大小不固定列大小不固定最多为最多为80008000字节字节查询速查询速度较快

5、度较快输入字符输入字符长度长度8KB的ASCII字符，text数据2021-12-19 text和和image数据类型数据类型 text:最多为最多为231-1个符号，存文字个符号，存文字image:最多为最多为231-1个个binary数据，存数据，存图形图形 两者均是可变长度的数两者均是可变长度的数据类型据类型,占较多空间占较多空间text字段专用于存储字符（非字段专用于存储字符（非Unicode）数据，）数据，image字段专用于存储二进制字段专用于存储二进制数据，每条记录的数据，每条记录的text字段或字段或image字字段均有一个或多个存储页段均有一个或多个存储页(2KB)存放它存放

6、它的值的值 2021-12-110日期日期及时及时间型间型数据数据 datetimedatetimeSmalldatetimeSmalldatetime数字数字 分隔符分隔符 数字数字 分隔符分隔符 数字数字 时间时间 时间时间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/04d

7、ym: 15/1996/04 ymd:ymd: 1996/04/151996/04/15 2021-12-111p：精度值：精度值s:小数位小数位数数 4.5精确数精确数值数据值数据 decimal(p,s)numeric(p,s)近似数近似数值数据值数据 real 精度在精度在17之间之间float 精度在精度在815之间之间 货币数据：货币数据： money、smallmoney 加前缀，如加前缀，如 $123负货币值，加负货币值，加-123整型数据类型整型数据类型(1) bigint：占：占8字节的存储空间，存储字节的存储空间，存储数据范围为数据范围为2632631。(2) int：占：

8、占4字节的存储空间，存储数据字节的存储空间，存储数据范围为范围为 2312311。(3) smallint：占：占2字节的存储空间，存字节的存储空间，存储数据范围为储数据范围为2152151。(4) tinyint：占：占1字节的存储空间，存储字节的存储空间，存储数据范围为数据范围为0255。2021-12-112Unicode字符数据字符数据 使用使用Unicode数据类型，列可存储由数据类型，列可存储由Unicode标准定义的任何字符，包含由不同标准定义的任何字符，包含由不同字符集定义的所有字符字符集定义的所有字符nvarchar 列大小不固定列大小不固定 4000个字符数个字符数ncha

9、r 列大小固定列大小固定 4000个字符数个字符数2021-12-1132021-12-114特特殊殊类类型型数数据据bit:存放存放1或或0的逻辑类型数据，对于的逻辑类型数据，对于0以以外的值，一律以外的值，一律以1表示表示User-Defined DataType: SQL Server还允还允许用户依据系统数据类型自定义数据类型许用户依据系统数据类型自定义数据类型 timestamp:时间标签类型与系统时间无时间标签类型与系统时间无关关, 只是个单调增加的计数器，其值在数只是个单调增加的计数器，其值在数据库中是唯一的据库中是唯一的若有名为若有名为timestamptimestamp的字段

10、而未的字段而未定义类型定义类型, ,自动为自动为timestamptimestamp类类型，若定义为其它类型也可。型，若定义为其它类型也可。为免混乱，最好不这样做为免混乱，最好不这样做2021-12-1153.1.2用户用户自自定义数据类型定义数据类型sp_addtype typename, phystype,nulltype 用户自定用户自定义数据类义数据类型的名称型的名称 SQL Server提供的数据提供的数据类型名称类型名称 是否允许用户自是否允许用户自定义数据类型为定义数据类型为NULL值值 把把zipcode作为自定义数据类型作为自定义数据类型,长度为长度为6EXEC sp_add

11、type zipcode , char(6)删除自定义的数据类型删除自定义的数据类型zipcodeEXEC sp_droptypezipcode3.2 SQL Server 的的对象对象命名规则命名规则需要命名的对象需要命名的对象数据库数据库 表表 列列 文件组文件组 文件文件约束约束 规则规则 默认值默认值 索引索引 视图视图 触发器触发器 存储过程存储过程 用户自定义函数用户自定义函数服务器服务器 角色角色 登陆登陆 全文本目录全文本目录 用户自用户自定义类型定义类型2021-12-1161.对象的名字必须以对象的名字必须以Uunicode2.0规范定规范定义的任意字母开头。义的任意字母开

12、头。2.正常对象的名字可正常对象的名字可多多达达128个字符，而临个字符，而临时对象的名字可时对象的名字可多多达达116个字符。个字符。3.与与SQL Server关键字相同或包含内嵌空关键字相同或包含内嵌空格的名字必须使用双引号（格的名字必须使用双引号（“ ”）或方括）或方括号（号（ ）。）。 4.对象的名字中不允许出现空格或其他特对象的名字中不允许出现空格或其他特殊字符。殊字符。2021-12-1173.2 SQL Server 的对象命名规则的对象命名规则3.3 3.3 数数据表的据表的创建与维护创建与维护2021-12-118 在在SQL ServerSQL Server中创建数据库可

13、以通过中创建数据库可以通过Microsoft SQL Server Management StudioMicrosoft SQL Server Management Studio图形化界面和在图形化界面和在SQLSQL查询分析器输入查询分析器输入T-SQLT-SQL语语言言假定已经建好三个数据库假定已经建好三个数据库My_DB1My_DB1、My_DB2My_DB2和和My_DB3My_DB3 2021-12-1193.3.13.3.1表的创建表的创建 create table database.owner.table_name ( col_name column_properties con

14、straintconstraint | ,constraint ,next_col_name|next_constraint )表所在的表所在的数据库数据库 表的所表的所有者有者 列名列名列属性列属性 2021-12-120 CREATE TABLE My_DB1.dbo.stud_info(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),

15、约约束束名名 例：在数据库例：在数据库My_DB1My_DB1中创建中创建stud_infostud_info表表，表中包括：学号、姓名、出身年月、，表中包括：学号、姓名、出身年月、性别、地址、电话号码、邮编，所在系性别、地址、电话号码、邮编，所在系代号，每月生活补贴等。代号，每月生活补贴等。2021-12-121zipcode char(6) CONSTRAINT zip_chk CHECK(zipcode LIKE0-90-90-90-90-90-9), DeptCode tinyint CONSTRAINT DeptCode_chk CHECK(DeptCode=0 and math_s

16、core=0 and engl_score=0 and comp_score=0 and chem_score=0 and phys_score=100),CONSTRAINT pk_chk PRIMARY KEY(year,stud_id) 约束名约束名2021-12-125本例中的主键定义为本例中的主键定义为primary key(year,stud_id)以以year和和stud_id的组合作为主键的组合作为主键一个表只能一个表只能有一个主键有一个主键且值唯一且值唯一需要多列需要多列组合作为主键组合作为主键时要把作为主时要把作为主键的列用括号键的列用括号组合起来组合起来2021-12-1

17、263.3.3.3.2 2 数据库中表的修改数据库中表的修改 (1)ALTER (1)ALTER 命令命令 ALTER TABLE database.owner.table_nameWITH NOCHECKADDcolumn_name datatype column_constraints |,table_constraint |不指定此选项不指定此选项时时( (默认情况默认情况) )，增加的约束，增加的约束要对现有数据要对现有数据进行检查，若进行检查，若发现违反约束发现违反约束，ALTER TABLEALTER TABLE语句将中止运语句将中止运行行ADD允许在现存的表中增加新数允许在现存的

18、表中增加新数据项或者一个约束据项或者一个约束 2021-12-127例：向表例：向表stud_infostud_info中加入字段名为中加入字段名为dormitory dormitory 的一列，以记录该学生的宿舍的一列，以记录该学生的宿舍号。该列的属性为号。该列的属性为nvarchar(10)nvarchar(10)，缺省值，缺省值为为紫荆公寓紫荆公寓8 8号楼号楼 ALTER TABLE My_DB1.dbo.stud_infoADD dormitory nvarchar(20) NULL DEFAULT(紫荆公寓紫荆公寓8号楼号楼)2021-12-128指定删除指定删除数据库中数据库中的

19、约束的约束 DROP CONSTRAINTconstraint_name| COLUMN column_name ,.n DROP指定删除指定删除现存表中的一个现存表中的一个字段或约束字段或约束指定删除数指定删除数据库中的列据库中的列 drop和和add子子句句不不要同时出现要同时出现2021-12-129值得注意的是：在删除某列时，基于该：在删除某列时，基于该列的所有索引和约束必须首先被删除掉列的所有索引和约束必须首先被删除掉。例如，如果用户运行下面的命令想删除例如，如果用户运行下面的命令想删除新增加列新增加列dormitory时，将出现错误。时，将出现错误。use mydbalter ta

20、ble stud_infodrop column dormitory由于默认值约束的存在由于默认值约束的存在使删除该列失败。这就使删除该列失败。这就需要在删除该列之前，需要在删除该列之前，先将其上的约束删除先将其上的约束删除可以手动将字可以手动将字段段dormitorydormitory上上的默认值删除的默认值删除，然后再运行，然后再运行上述命令上述命令 2021-12-1303.3.3.3.3 3 表的删除表的删除 USE My_DB1godrop table stud_infogo 注意：注意：不能使用该命名删除数据库中不能使用该命名删除数据库中的系统表。的系统表。语法格式：语法格式：dr

21、op table 表名表名删删除除stud_info表：表： 2021-12-1313.3.3.3.3 3 表表的的重命名重命名 将数据库将数据库My_DB1中的表中的表stud_info改名为改名为stud1USE My_DB1gosp_rename stud_info, stud1Go 语法格式：语法格式：sp_rename oldname,newname2021-12-1323.3.4 4. .1 1 表中添加数据表中添加数据 通过通过INSERTINSERT语句，可以向表中增添新语句，可以向表中增添新的记录或在记录中插入部分字段的数据的记录或在记录中插入部分字段的数据INSERTINS

22、ERT命令的基本语法如下：命令的基本语法如下：INSERT INTO table_nameINSERT INTO table_name(column1,column2)(column1,column2) values(value1,value2) values(value1,value2)表名表名 字段列表字段列表 相应的值列表相应的值列表 3.3.4 4表表数据的添加、修改和删除数据的添加、修改和删除2021-12-133向向My_DB1中的中的stud_info表插入记录表插入记录 use My_DB1goINSERT stud_info values(0844,张源张源, 12/15/1

23、979, 1, 北京市海淀区北京市海淀区, 100080,3, 260)goINSERT stud_info values(9434,赵明赵明, 8/5/1979,1,上海市浦东区上海市浦东区 201700,2, 260)3.3.4 4. .1 1 表中添加数据表中添加数据 2021-12-134输入项的顺序和数据类型必须与输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应表中字段的顺序和数据类型相对应定义表结构时如果定义了缺省值定义表结构时如果定义了缺省值，此时如对相应字段不输入值，则，此时如对相应字段不输入值，则系统会用缺省值

24、插入表中系统会用缺省值插入表中如果插入的数据不能满足约束性如果插入的数据不能满足约束性条件，就出错条件，就出错3.3.4 4. .1 1 表中添加数据表中添加数据 2021-12-135如何把查如何把查询结果询结果插入到另一张表插入到另一张表中中 在在INSERTINSERT语句中可用语句中可用SELECTSELECT从同一张表或从同一张表或其他表中选择字段插其他表中选择字段插入到表结构与入到表结构与SELECTSELECT查询返回的结果相容查询返回的结果相容的表中，可插入多行的表中，可插入多行3.3.4 4. .1 1 表中添加数据表中添加数据 2021-12-136INSERT table

25、_name inserttable_column_listSELECT selectable_column_listFROM table_lsitWHERE Search_conditions 需插入记录需插入记录的表的名称的表的名称 插入到被插入到被插入表中插入表中的字段名的字段名 从检从检索表索表中取中取出字出字段名段名 检索条件检索条件 把表把表table_list中符合检中符合检索条件的记录中的索条件的记录中的column_list字段插入表字段插入表table_name 3.3.4 4. .1 1 表中添加数据表中添加数据 2021-12-137例：查询数据库例：查询数据库My_DB

26、1中表中表stud_info中的中的stu_id信息，然后将查询结果插入到信息，然后将查询结果插入到表表stud_score中的中的stu_id字段中字段中use My_DB1INSERT into stud_score(stu_id)select stu_id from stud_info 3.3.4 4. .1 1 表中添加数据表中添加数据 2021-12-138用用UPDATE可以改变表中现存行的数据可以改变表中现存行的数据。UPDATE table_nameSET column1=modified_value1,column2=modified_values,WHERE column1=value1,column2=value2 指定要更新数指定要更新数据的表名据的表名 指定要更新指定要更新的列及该列的列及该列改变后的值改变后的值 指定被更新的记录指定被更新的记录所应满足的条件所应满足的条件 3.3.4 4. .2 2修改表中的数据修改表中的数据 2021-12-139把上例中在把上例中在表表stud_info中中新添加的记新添加的记录进行修改以方便以后的使用录进行修改以方便以后的使用 use MyDBUPDATE stud_info