数据库中表的基本操作课件.ppt

1、第四章第四章 数据库中表的基本操作数据库中表的基本操作（表）考试数据库学生数据、试题数据、教师数据、试卷数据等要存储起来学生关系模型、试题关系模型、教师关系模型、试卷关系模型第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述 v 4.2 数据库中表的创建 v 4.3 表的修改与删除 v 4.4 数据的添加与表的查看 v 4.5 案例应用举例 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述表的概述 一、一、1、表的概念、表的概念 在在SQL Server数据库中，数据库中，表定义为列的集合，与表定义为列的

2、集合，与Excel电子表格电子表格相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。条记录，而每列代表记录中的一个域。学号姓名性别出生日期系部入学时间000101张三男1980计算机2000000201李伟男1981经 济 管理2000010101王丽女1982数学2001关系名属性（列）属性（列）和属性名关系模式关系元组主键（码）学生表学生表 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述表的概述 2、SQL Server表与关系模型的对应表

3、与关系模型的对应关系模型关系模型SQL Server表表关系名关系名表名表名关系关系表表关系模式关系模式表的定义表的定义属性属性表的列或字段表的列或字段属性名属性名字段名或列名字段名或列名值值列值或字段值列值或字段值元组元组表的行或记录表的行或记录码码主键主键关系完整性关系完整性SQL Server的约束的约束第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述 3、表的设计过程中，、表的设计过程中，在在SQL Server创建表有如下限制：创建表有如下限制： 1、每个数据库里最多有、每个数据库里最多有20亿个表。亿个表。 2、每个表上最多可以创建一个

4、聚集索引和、每个表上最多可以创建一个聚集索引和249个非聚集个非聚集索引。索引。 3、每个表最多可以配置、每个表最多可以配置1024个字段。个字段。 4、每条记录最多可以占、每条记录最多可以占8060B，但不包括但不包括text字段和字段和image字段。字段。 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述表的概述 二、基本的数据类型二、基本的数据类型 在创建数据库中的表的时候，牵涉到表体结构，也就在创建数据库中的表的时候，牵涉到表体结构，也就是说牵涉到表中的字段数据的格式，所以为数据列选择数是说牵涉到表中的字段数据的格式，所以为数据列选择数据

5、类型是一项非常重要的工作。在定义了表列的数据类型据类型是一项非常重要的工作。在定义了表列的数据类型以后，它将作为一项永久的特性被保留下来，而无法改变以后，它将作为一项永久的特性被保留下来，而无法改变。所以精心选择表列的数据类型是建立性能良好的表格的。所以精心选择表列的数据类型是建立性能良好的表格的前提。前提。 数据类型包含数据类型包含8大类，及几种新的数据类型。大类，及几种新的数据类型。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 4.1 SQL Server表的概述 1、整型数据类型、整型数据类型 能够存储精确的不带小数点的数字值的方法，可以分为五大类。能够存储精确的不带小数点的

6、数字值的方法，可以分为五大类。 bit 取值范围只能是取值范围只能是0或或1，通常用作真假逻辑。，通常用作真假逻辑。 bigint 占用占用8个字节个字节 int 占用占用4个字节个字节 占用字节数决定存储范围的大小占用字节数决定存储范围的大小 smallint 占用占用2个字节个字节 tiny 占用占用1个字节个字节第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型2、数字、数字 数据类型数据类型 可以精确指定小数点两边的总位数可以精确指定小数点两边的总位数(精度精度)和小数点右面的位数和小数点右面的位数(刻度刻度). 1=精度精度=38 0=刻度

7、刻度=精度精度 numeric 唯一的区别就是在主键为唯一的区别就是在主键为indentity列，只能定义列，只能定义numeric类型类型 decimal decimal(7 , 3) 9999.994 / 9999.995 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型3、浮点数据类型、浮点数据类型 能够存储范围非常大的数字，一般应用于科学计算或者统计计算能够存储范围非常大的数字，一般应用于科学计算或者统计计算等，不要求绝对精度的运算场合。等，不要求绝对精度的运算场合。 real 存储范围占用存储范围占用4个字节，精度可达个字节，精度可达7位

8、。位。 float(n) 可以用可以用n来确定科学计数法中的数据尾数的位数，范围可来确定科学计数法中的数据尾数的位数，范围可 达达53位。当位。当n的取值为的取值为124时，可达到的精度是时，可达到的精度是7位；当位；当 n的取值是的取值是2553时，精度是时，精度是15位，用位，用8个字节来存储。个字节来存储。 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型4、字符数据类型、字符数据类型 char 每个字符使用一个字节的存储空间，使用固定长度来存储字每个字符使用一个字节的存储空间，使用固定长度来存储字符，最长可容纳符，最长可容纳8000个。如果

9、实际的字符串小于给定的最大长度，则个。如果实际的字符串小于给定的最大长度，则会以空格填充，如果实际的字符串大于给定的最大长度，则截断。会以空格填充，如果实际的字符串大于给定的最大长度，则截断。 varchar 可以存储到可以存储到8000个字符的变长字符型数据，存储为实际的字个字符的变长字符型数据，存储为实际的字符串的长度。符串的长度。 text 当要存储的字符的数据量非常庞大时，则使用当要存储的字符的数据量非常庞大时，则使用text数据类型。数据类型。他的最大长度可以达到他的最大长度可以达到2G左右。左右。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的

10、数据类型5、二进制数据类型、二进制数据类型 二进制数据在二进制数据在SQL中用十六进制进行表示，前边用中用十六进制进行表示，前边用0 x标记。标记。binary 具有固定长度具有固定长度,最大长度可达最大长度可达8kb，当输入的实际数据小于定，当输入的实际数据小于定 义的长度时，后面补义的长度时，后面补0。varbinary 具有不固定的长度。具有不固定的长度。image 用于存储字节数超过用于存储字节数超过8kb的数据，例如图象数据。的数据，例如图象数据。 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型对对text、image数据类型：数据类型

11、： 1、不可使用、不可使用select命令中的命令中的order by、group by命令。命令。 2、不可作为索引文件的关键字。、不可作为索引文件的关键字。 3、也不可使用在、也不可使用在where语句中，但是对于语句中，但是对于text数据可以数据可以 使用使用where语句中的模糊查询。语句中的模糊查询。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型6、货币数据类型、货币数据类型 money 占用占用8个字节，前面个字节，前面4个表示货币的整数，后面个表示货币的整数，后面4个表示小数。个表示小数。 small money 占用占用4个字节

12、。个字节。7、日期、日期/时间数据类型时间数据类型 datetime 表示范围表示范围1753年年1月月1日日9999年年12月月31日，占用日，占用8个字节可个字节可 以精确到毫秒以精确到毫秒 small datetime 表示范围表示范围1900年年1月月1日日2079年年6月月6日，占用日，占用4个字节个字节 可以精确到分。可以精确到分。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型8、双字节数据类型、双字节数据类型 用于存储要用两个字节才能存储的双字节字符。用于存储要用两个字节才能存储的双字节字符。 nchar(n) 固定长度的双字节，可

13、以存储固定长度的双字节，可以存储14000字符。字符。 nvarchar(n) 可变长度的双字节。可变长度的双字节。 ntext(n) 可存储大量的字符。可存储大量的字符。9、SQL-variant数据类型数据类型 可以存储除可以存储除text、ntext、image以外的各种类型数据，也不可以自身以外的各种类型数据，也不可以自身转储。转储。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 掌握基本的数据类型掌握基本的数据类型10、table数据类型数据类型 可以用来定义变量或者用于用户自定义函数的返回值，一般用来存储可以用来定义变量或者用于用户自定义函数的返回值，一般用来存储从数据库

14、中提取出来得结果。从数据库中提取出来得结果。 11、图象、文本数据使用时涉及到的几个函数、图象、文本数据使用时涉及到的几个函数12、用户自定义数据类型、用户自定义数据类型 null建立语法：建立语法：sp_addtype type_name , systemtype , not null删除语法：删除语法：sp_droptype type_name nonnull第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的创建表的创建 1.使用企业管理器创建表使用企业管理器创建表 的步骤：的步骤： 1）定义表结构：给表的每一列取字段名，并确定每一列的数据）定义表结构：给表的每一列取字段名，并

15、确定每一列的数据类型、数据长度、列数据是否可以为空等。类型、数据长度、列数据是否可以为空等。 2）设置约束：设置约束是为了限制该列输入值的取值范围，以）设置约束：设置约束是为了限制该列输入值的取值范围，以保证输入数据的正确性和一致性。保证输入数据的正确性和一致性。 3）添加数据：表结构建立完成之后，就可以向表中输入数据了。）添加数据：表结构建立完成之后，就可以向表中输入数据了。 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的创建表的创建 2.使用使用T-SQL语句创建表的基本语句创建表的基本语法：语法： CREATE TABLE database_name. owner .|

16、owner. table_name ( | PRIMARY KEY | UNIQUE ,.n ) := column_name data_type COLLATE DEFAULT constant_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION .n 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的创建表的创建 语法注释：语法注释： l l database_name：指定创建的表所在的数据库指定创建的表所在的数据库 l l owner：指定表的所有者指定表的所有者 l l table_name：指定新

17、建表的名称指定新建表的名称 l l column_definition：表中字段的定义表达式表中字段的定义表达式 l l column_name：表中的字段名表中的字段名 l l data_type：字段的数据类型字段的数据类型 l l seed：自动标识的开始值自动标识的开始值 l l increment：自动编号的步长自动编号的步长 l l CREATE TABLE：创建表语句的关键字创建表语句的关键字 l l PRIMARY KEY：主键约束主键约束 l l UNIQUE ：字段唯一性约束字段唯一性约束 l l DEFAULT：字段的默认值约束字段的默认值约束 l l IDENTITY

18、：自动编号标识自动编号标识第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的创建表的创建例例:创建带有参照约束的学生表，学生表的表结构定义如下表所示。创建带有参照约束的学生表，学生表的表结构定义如下表所示。“学号学号”字段为学生表的主键，字段为学生表的主键，“班级代码班级代码”字段为学生表的外字段为学生表的外键，它必须参照班级表中的键，它必须参照班级表中的“班级代码班级代码”字段的值。字段的值。字段名字段数据类型长度是否为空约束学号char12否主键姓名char8是 性别char2是 出生日期datetime8是 入学时间datetime8是 班级代码char9否外键系部代码cha

19、r2是 专业代码char2是 v 表的创建表的创建创建创建“专业专业”表的代码如下：表的代码如下： USE StudentGOCREATE TABLE 专业专业(专业代码专业代码 char(2) not null,专业名称专业名称 Varchar(20) not null,系部代码系部代码 char(2) not null)GO创建创建“班级班级”表的代码如下：表的代码如下：USE StudentGOCREATE TABLE 班级班级(班级代码班级代码 char(9) constraint pk_bjdm primary key, 班级名称班级名称 Varchar(20) not null,

20、专业代码专业代码 char(2) not null, 系部代码系部代码 char(2) not null, 备注备注 Varchar(50)GO第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的创建表的创建创建创建“学生学生”表的代码如下：表的代码如下： USE STUDENTGOCREATE TABLE 学生学生 (学号学号 char(12) CONSTRAINT pk_xh PRIMARY KEY, 姓名姓名 char(8), 性别性别 char(2), 出生日期出生日期 datetime, 入学时间入学时间 datetime, 班级代码班级代码 char(9) CONSTRA

21、INT fk_bjdm REFERENCES 班级班级(班班级代码级代码), 专业代码专业代码 char(2), 系部代码系部代码 char(2)GO第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的修改表的修改使用企业管理器修改表使用企业管理器修改表 1)打开企业管理器，选择相应的数据库，展开表节点。打开企业管理器，选择相应的数据库，展开表节点。 2)在企业管理器的明细窗格中，右击要修改的表，在弹出的快在企业管理器的明细窗格中，右击要修改的表，在弹出的快捷菜单中选择捷菜单中选择“设计表设计表”命令，打开表设计器。命令，打开表设计器。 3)在表设计器中修改各字段的定义，如字段名、字

22、段类型、字在表设计器中修改各字段的定义，如字段名、字段类型、字段长度、是否为空等。段长度、是否为空等。 4)添加删除字段。添加删除字段。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的修改表的修改语法：语法：ALTER TABLE table_name ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL | ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN colu

23、mn ,.n 语法：语法： := column_name data_type DEFAULT constant_expression WITH VALUES | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUIDCOL COLLATE .n 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的修改表的修改语法注释：语法注释： l l ALTER COLUMN：修改表列属性的子句修改表列属性的子句 l l ADD：增加列或约束的子句增加列或约束的子句 l l DROP COLUMN：删除表列的子句删除表列的子句 l l t

24、able_name：需要修改表的表格名称需要修改表的表格名称 l l column_name：希望增加的字段名希望增加的字段名 l l data_type：需要增加的字段的数据类型名需要增加的字段的数据类型名 l l collation_name：排序规则名排序规则名 v 例：表的修改例：表的修改在学生表中增加在学生表中增加“家庭住址家庭住址”： USE Student GO ALTER TABLE 学生学生 ADD 家庭住址家庭住址 Varchar(30) nullGO将将“家庭住址家庭住址”字段属性修改代码如下：字段属性修改代码如下： USE StudentGOALTER TABLE 学生

25、学生 ALTER COLUMN 家庭住址家庭住址 Varchar(50) nullGO第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的删除表的删除使用企业管理器删除表使用企业管理器删除表操作步骤如下：操作步骤如下： 1)打开企业管理器，选择相应的数据库并展开表节点。打开企业管理器，选择相应的数据库并展开表节点。 2)在企业管理器的明细窗格中，右击要删除的表，在弹出的快捷在企业管理器的明细窗格中，右击要删除的表，在弹出的快捷菜单中选择菜单中选择“删除删除”命令，出现命令，出现 除去对象对话框时，单击除去对象对话框时，单击“全部除全部除去去”按钮即可删除表。按钮即可删除表。使用使用D

26、ROP TABLE语句删除表语句删除表USE STUDENTGODROP TABLE 专业专业GO第四章第四章 数据库中表的基本操作数据库中表的基本操作v 表的查看表的查看1)使用企业管理器查看使用企业管理器查看 在企业管理器，右击需要查看结构的表，在弹出的快捷菜单中选择在企业管理器，右击需要查看结构的表，在弹出的快捷菜单中选择“属性属性”命令，打开表属性对话框，。单击命令，打开表属性对话框，。单击“常规常规”和和“全文索引全文索引”标签查标签查看表信息。看表信息。2)使用系统存储过程使用系统存储过程sp_help查看查看 语法格式：语法格式：EXECUTE sp_help 表名表名 例如，查

27、看例如，查看“班级班级”表的结构，可以使用下列语句：表的结构，可以使用下列语句： EXECUTE sp_help 班级班级 第四章第四章 数据库中表的基本操作数据库中表的基本操作v 查看表中的数据查看表中的数据1）使用企业管理器查看表中的数据）使用企业管理器查看表中的数据 在企业管理器中，右击需要查看数据的表，弹出快捷菜单，单击在企业管理器中，右击需要查看数据的表，弹出快捷菜单，单击“打打开表开表返回所有的行返回所有的行”命令，打开查询设计器的结果窗口查看数据命令，打开查询设计器的结果窗口查看数据. 2）使用查询分析器查看表中的数据）使用查询分析器查看表中的数据 打开查询分析器，在打开查询分析

28、器，在“对象浏览器对象浏览器”中选择相应的数据库中选择相应的数据库，展开数据展开数据库目录和用户表目录，右击要查看的表，从弹出快捷菜单中选择库目录和用户表目录，右击要查看的表，从弹出快捷菜单中选择“打开打开”命命令，就会打开表窗口，在此窗口中用户可以查看、修改和删除表中的数据。令，就会打开表窗口，在此窗口中用户可以查看、修改和删除表中的数据。第四章第四章 数据库中表的基本操作数据库中表的基本操作v 向表中的添加数据向表中的添加数据1）在企业管理器中，展开相关数据库节点，单击）在企业管理器中，展开相关数据库节点，单击“表表”图标，在明图标，在明细窗格中右击需要添加数据的表，弹出快捷菜单，单击细窗

29、格中右击需要添加数据的表，弹出快捷菜单，单击“打开表打开表返返回所有的行回所有的行”命令，打开查询设计器的结果窗口命令，打开查询设计器的结果窗口. 2）查询设计器的表中可以输入新记录，也可以修改和删除已经输入）查询设计器的表中可以输入新记录，也可以修改和删除已经输入的记录。的记录。3）使用）使用INSERT INTO 语句添加数据语句添加数据第四章第四章 数据库中表的基本操作数据库中表的基本操作4、5案例应用举例案例应用举例use studentgocreate table 系部系部(系部代码系部代码 char(2) constraint pk_xbdm primary key,系部名称系部名

30、称 varchar(30) not null,系主任系主任 char(8)go第四章第四章 数据库中表的基本操作数据库中表的基本操作use studentgocreate table 专业专业(专业代码专业代码 char(4) constraint pk_zydm primary key,专业名称专业名称 varchar(20) not null,系部代码系部代码 char(2) constraint fp_zyxbdm references 系部系部(系部代码系部代码)go第四章第四章 数据库中表的基本操作数据库中表的基本操作use studentgocreate table 班级班级(班级

31、代码班级代码 char(9) constraint pk_bjdm primary key,班级名称班级名称 varchar(20),专业代码专业代码 char(4) constraint fp_bjzydm references 专业专业(专业代码专业代码),系部代码系部代码 char(2) constraint fp_bjxbdm references 系部系部(系部代码系部代码),备注备注 varchar(5)go第四章第四章 数据库中表的基本操作数据库中表的基本操作USE STUDENTGOCREATE TABLE 学生学生 (学号学号 char(12) CONSTRAINT pk_x

32、h PRIMARY KEY, 姓名姓名 char(8), 性别性别 char(2), 出生日期出生日期 datetime, 入学时间入学时间 datetime, 班级代码班级代码 char(9) CONSTRAINT fk_xsbjdm REFERENCES 班级班级(班级代码班级代码), 专业代码专业代码 char(4) constraint fp_xszydm references 专业专业(专业代码专业代码), 系部代码系部代码 char(2) constraint fp_xsxbdm references 系部系部(系部代码系部代码)GO第四章第四章 数据库中表的基本操作数据库中表的基

33、本操作USE STUDENTGOCREATE TABLE 课程课程 (课程号课程号 char(4) CONSTRAINT pk_kc PRIMARY KEY, 课程名课程名 char(20) not null, 备注备注 varchar(50) GO第四章第四章 数据库中表的基本操作数据库中表的基本操作USE STUDENTGOCREATE TABLE 教师教师 (教师编号教师编号 char(12) CONSTRAINT pk_jsbh PRIMARY KEY, 姓名姓名 char(8) not null, 性别性别 char(2), 出生日期出生日期 datetime, 学历学历 char(

34、10), 职务职务 char(10), 职称职称 char(10), 系部代码系部代码 char(2) constraint fp_jsxbdm references 系部系部(系部代码系部代码), 专业专业 char(2), 备注备注 varchar(50) GO第四章第四章 数据库中表的基本操作数据库中表的基本操作USE STUDENTGOCREATE TABLE 教学计划教学计划 (课程号课程号 char(4) CONSTRAINT pk_jxjhkch references 课程课程(课程号课程号), 专业代码专业代码 char(4) constraint pk_jxjhzydm re

35、ferences 专业专业(专业代码专业代码), 专业学级专业学级 char(4) not null, 课程类型课程类型 char(8), 开学学期开学学期 tinyint, 学分学分 tinyint, 起始周起始周 tinyint, 结束周结束周 tinyint, 教材编号教材编号 char(6), 备注备注 char(20) GO第四章第四章 数据库中表的基本操作数据库中表的基本操作USE STUDENTGOCREATE TABLE 教师任课教师任课 (教师编号教师编号 char(12) CONSTRAINT fk_jsrkjsbh references 教师教师(教师编号教师编号), 课

36、程号课程号 char(4) CONSTRAINT pk_jsrkkch references 课程课程(课程号课程号), 专业代码专业代码 char(4), 专业学级专业学级 char(4), 学年学年 char(4), 学期学期 tinyint, 学生数学生数 smallint, 学时数学时数 smallint, 酬金酬金 smallint, 起始周起始周 tinyint, 结束周结束周 tinyint, ) GO第四章第四章 数据库中表的基本操作数据库中表的基本操作USE STUDENTGOCREATE TABLE 课程注册课程注册 (注册号注册号 bigint identity(0100

37、00000,1) not for replication constraint pk_zch primary key, 学号学号 char(12) constraint fk_kczcxh references 学生学生(学号学号), 课程号课程号 char(4) CONSTRAINT fk_kczckch references 课程课程(课程号课程号), 教师编号教师编号 char(12) CONSTRAINT fk_kczcjsbh references 教师教师(教师编号教师编号), 专业代码专业代码 char(4) constraint fk_kczczydm references 专业专业(专业代码专业代码), 专业学级专业学级 char(4), 选课类型选课类型 char(4), 学年学年 char(4), 学期学期 tinyint, 收费否收费否 bit, 注册注册 bit, 成绩成绩 tinyint, 学分学分 tinyint ) GO