第7章表数据操作

1、网络数据库技术 第7章表数据操作 第第7章章 数据表和表数据数据表和表数据 网络数据库技术 第7章表数据操作 7.1 表的创建、修改和删除 一、 SQL server 支持的数据类型 1）整数型：bigint ,int,smallint ,tinyint 2)精确数值型：整数部分+小数部分，分为：decimal和 numeric两类 3）浮点型:foat 和real两类 4）货币型：money 和smallmoney 5)位型：相当于 其他语言中的逻辑型数据，只存储0和1 6)字符型：varchar 和char 网络数据库技术 第7章表数据操作 7)unicode 字符型 8）文本型 ：tex

2、t 和ntext 9）二进制型 10）日期时间类型：datetime 和 smalldatetime 2.创建表 首先要设计表 （1）空值(null)概念 空值表示通常表示未知、不可用或将 以后添加的数据。表的关键字不允许为空值。 （2）学生情况表的创建 表结构 网络数据库技术 第7章表数据操作 二、表 表是SQL server 中最主要的数据库对象，它是用来 存储和操作数据的一种逻辑结构，由行和列组成。 1 表结构 组成表的各列的名称及数据类型 2 记录 表中的一行 3 字段 构成记录的每个数据项 4 关键字 表中记录的某一字段或字段组合能唯一 表示记录，该字段或字段组合成为关键字。 网络数

3、据库技术 第7章表数据操作 通过管理器创建表通过管理器创建表XS的操作步骤的操作步骤 网络数据库技术 第7章表数据操作 三、使用CREATE TABLE 创建表 1示例：设已经创建数据库XSCJ ，现在 该数据库中需创建学生情况表XS， Kc(kch,kcm) xs_kc(xh,kch,chj) Xs(xh,xm,zhy,xb,chsh,zxf,bzh) 网络数据库技术 第7章表数据操作 use xscj create table xs (学号 char(6) not null, 姓名 char(8) not null, 专业名 char(10) null, 性别 char(2) defaul

4、t 男 not null, 出生时间 smalldatetime not null, 总学分 tinyint null, 备注 text null ) go 网络数据库技术 第7章表数据操作 2.Create table 语句的语法： create table 表名 （ column_name datatype|identity|not null|null） 例： Create table card (卡号 decimal(6,0) identity(10000,1) 姓名 char(8) not null, 专业名 char（10）null ) 网络数据库技术 第7章表数据操作 create

5、 table 完整的语法结构：完整的语法结构： create table database_name.schema_name| schema_name.table_name ( | column_name as computed_column_expression |) xscj.xs 网络数据库技术 第7章表数据操作 四、使用ALTER TABLE 修改表 Alter table 语句的基本语法结构语句的基本语法结构 ALTER TABLE table_name alter column column_name new_datatime_type(precision,scale) not n

6、ull |null |add ,n |dropconstraint constraint_name |column column_name,n 网络数据库技术 第7章表数据操作 1 修改已有列的属性：ALTER COLUMN 列名 2 向表中增加新列：ADD 3 删除表中列：DROP COLUMN 例 在表XS中增加一个新列奖学金等级列 use xscj alter table xs add 奖学金等级 tinyint null go 网络数据库技术 第7章表数据操作 例 ：在XS表中删除名为奖学金等级的列 use xscj alter table xs drop column 奖学金等级 g

7、o 例：修改表XS中已有列的属性：将名为“姓名”的列长度由原来的8该为10， 将“出生时间”的列的数据类型由原来的smalldatetime改为datetime. use xscj alter table xs alter column 姓名 char(10) alter table xs alter column 出生时间 datetime go 六、使用DROP TABLE删除表 drop table 表名 网络数据库技术 第7章表数据操作 （3）列的identity属性 对任何表都可创建包含系统所生成序号值的一个标识列，该 序号值唯一标识表中的一行，可以作为键值。每个表只能 有一列作为标

8、识列，int,numeric,smallint,bigin OR tinyint, 定义标识列可指定其种子（起始）值和增量值， 建立数据库library ,在其中创建card表，表结构如所示 3 修改表 通过管理器和T-SQL语言两种方法来进行： 更改表名，增加列，删除列，修改已有列的属性 4删除表 网络数据库技术 第7章表数据操作 创建数据库和表以后，需要对表中的数创建数据库和表以后，需要对表中的数 据进行操作，包括插入、删除和修改。据进行操作，包括插入、删除和修改。 有两种方法：一是通过有两种方法：一是通过SQL Server 2005管理器，一是通过管理器，一是通过T-SQL语句。语句。

9、 网络数据库技术 第7章表数据操作 7.2 界面操作表数据界面操作表数据 一、插入记录一、插入记录 添加记录在表尾添加记录在表尾 二、删除记录二、删除记录 三、修改记录三、修改记录 网络数据库技术 第7章表数据操作 7.3 命令操作表数据（命令操作表数据（T-SQL语句）语句） 一、通过一、通过INSERT 语句插入表数据语句插入表数据 向表中插入数据可以使用向表中插入数据可以使用INSERT语句。语句。INSERT 最基本的格式为：最基本的格式为： INSERT into 表名表名(column1,column2,) VALUES（constant1,constant2,） 该语句的功能是向

10、指定的表中加入由该语句的功能是向指定的表中加入由VALUES指定指定 的各列值的行的各列值的行 例：向例：向XSCJ数据库的表数据库的表XS中插入一行：中插入一行： 001112 罗林林罗林林 计算机计算机 0 1/30/1980 0:0:0 40 可以使用如下的可以使用如下的T-SQL语句：语句： 网络数据库技术 第7章表数据操作 USE xscj INSERT INTO XSCJ.dbo. XS() VALUES(001112,罗林林罗林林,计算计算 机机,0,1/30/1980 0:0:0,40,null) Go 例例 ：用如下语句建立表：用如下语句建立表test CREATE TABL

11、E test ( xm char(20) not null, zy varchar(30) default(计算机计算机), nj tinyint not null ) 网络数据库技术 第7章表数据操作 用用INSERT 语句向表中插入一条记录：语句向表中插入一条记录： INSERT INTO test(xm,nj) values( 王林王林,3) 例：例： 创建表创建表XS2 CREATE TABLE XS2 (num char(10) not null, name char(10) not null, specialty char(10) null ) 可用如下的可用如下的INSERT语句

12、向语句向XS2中插入数据：中插入数据： Insert into xs2 select xh,xm,zhy from xs1 Where zhy=生工生工 查询结果：查询结果：select * from xs2 网络数据库技术 第7章表数据操作 二、使用二、使用DELETE或或TRUNCATE TABLE 语句删除数据语句删除数据 1. 使用使用delete 语句删除数据语句删除数据 delete 语句的功能是从表中删除行，其基本语法格式为：语句的功能是从表中删除行，其基本语法格式为： delete from table_name?view_name where Example:将将XSCJ数据

13、库的表数据库的表XS中总学分小于中总学分小于39的行删的行删 除：除： USE XSCJ DELETE FROM XS WHERE 总学分总学分39 go 网络数据库技术 第7章表数据操作 2. 使用使用TRUNCATE TABLE语句删除表语句删除表 数据数据 使用该语句将删除指定表中的所有数使用该语句将删除指定表中的所有数 据，语法格式为：据，语法格式为： TRuNCATE TABLE name 网络数据库技术 第7章表数据操作 3.使用使用UPDATE 语句修改数据语句修改数据 UPDATE 语句可以用来修改表中的数据语句可以用来修改表中的数据 行，其最基本语法格式如下：行，其最基本语法

14、格式如下： UPDATE table_name ? view_name SET column_name=expression ? DEFAULT ? NULL,n WHERE 网络数据库技术 第7章表数据操作 例：将例：将XSCJ 数据库的数据库的XS表中学号为表中学号为001110 的学生的备注列值改为的学生的备注列值改为“三好生三好生”： USE XSCJ UPDATE XS SET 备注备注=三好生三好生 WHERE 学号学号=001110 GO 网络数据库技术 第7章表数据操作 例例 将将XS表中的所有学生的总学分都增加表中的所有学生的总学分都增加10个学分。个学分。 USE XSCJ

15、 UPDATE XS SET 总学分总学分=总学分总学分+10 例例 将姓名为将姓名为“王林王林”的同学的专业改为的同学的专业改为“材料工材料工 程程”，备注改为，备注改为“转专业学习转专业学习”，学号改为，学号改为001230 Use xscj Update xs set xh=001230, zhy=材料工程材料工程, bzh=转专业学习转专业学习 where xm=王林王林 网络数据库技术 第7章表数据操作 7.4 数据完整性和约束数据完整性和约束 7.4.1 数据完整性的分类数据完整性的分类 数据完整性是指数据库中的数据在逻辑上的一致性和数据完整性是指数据库中的数据在逻辑上的一致性和

16、准确性准确性.一般包括四种一般包括四种. 1 域完整性域完整性 又称列完整性又称列完整性,指给定列的输入的有效性指给定列的输入的有效性.可以通过定可以通过定 义相应的规则、约束、默认值对象等方法实现。义相应的规则、约束、默认值对象等方法实现。 2 实体完整性实体完整性 又称行的完整性，要求表中有一个键，其值不能为空且又称行的完整性，要求表中有一个键，其值不能为空且 能唯一地表示对应的实体。通过索引、能唯一地表示对应的实体。通过索引、UNIQUE约束约束 primary key约束或约束或identity属性可实现数据属性可实现数据 的完整性的完整性. 网络数据库技术 第7章表数据操作 3 参照

17、完整性参照完整性 参照完整性又称引用完整性参照完整性又称引用完整性.参照完整性保证主表参照完整性保证主表 (被参照表被参照表)中的数据与从表中的数据与从表(参照表参照表)中数据的一中数据的一 致性致性.通过定义外键通过定义外键(外码外码)与主键与主键(主码主码)之间或之间或 外键与唯一键之间的对应关系来实现参照完整外键与唯一键之间的对应关系来实现参照完整 性性.参照完整性确保键值在所有表中一致参照完整性确保键值在所有表中一致. Xs(xh,xm,nl,zhy),kc(kch,kcm),cj(xh,kch,cj) 4.用户定义完整性用户定义完整性 网络数据库技术 第7章表数据操作 7.4.2 约

18、束的类型约束的类型 1.check约束约束 2. default约束约束 3. primary key约束约束 4.foreign key约束约束 5.unique约束约束 网络数据库技术 第7章表数据操作 7.4.3 完整性的实现完整性的实现 一、域完整性的实现一、域完整性的实现 可以通过定义约束和规则等来实现域完整性可以通过定义约束和规则等来实现域完整性 1. check约束的定义与删除约束的定义与删除 check约束是字段输入内容的验证规则约束是字段输入内容的验证规则,表示一个字段表示一个字段 的输入内容必须满足的输入内容必须满足check约束的条件约束的条件,若不满足若不满足, 则数据

19、无法正常输入则数据无法正常输入. 1)通过通过SQL Server Manangement Studio创建与删除创建与删除 check约束约束 2)利用利用sql语句在创建表时创建语句在创建表时创建check约束约束 网络数据库技术 第7章表数据操作 语法格式语法格式: create table table_name (column_name datatype not null/null default constraint_expression check_name check (logical_expression) n) 网络数据库技术 第7章表数据操作 例例 在学生数据库中创建在学生

20、数据库中创建books,其中包括所有的约束其中包括所有的约束 定义定义 use xscj create table books (book_id smallint, book_name varchar(50) not null, book_desc varchar(50) not null, max_lvl tinyint not null check (max_lvl =250) ) Go 网络数据库技术 第7章表数据操作 use xscj create table books1 (book_id smallint, book_name varchar(50) not null, book_

21、desc varchar(50) not null, max_lvl tinyint not null constraint check_m check (max_lvl =0 and 成绩成绩=100) 4)利用利用SQL语句删除语句删除check约束约束 alter table table_name drop constraint check_name 网络数据库技术 第7章表数据操作 例例 删除删除xscj数据库中的数据库中的xs_kc表的成绩字段的表的成绩字段的check 约束约束 use xscj alter table xs_kc drop constraint cj_constr

22、aint go 网络数据库技术 第7章表数据操作 2.规则对象的定义、使用与删除规则对象的定义、使用与删除 （1）定义规则对象）定义规则对象 （2）将规则对象邦定到列或用户自定义数据类型）将规则对象邦定到列或用户自定义数据类型 1）利用）利用SQL命令定义规则并绑定到自定义类型或列命令定义规则并绑定到自定义类型或列 网络数据库技术 第7章表数据操作 规则对象的定义规则对象的定义 create rule rule_name as condition_expression 绑定绑定 sp_bindrule rulename=rule, objectname=object_name ,futureo

23、nly= futureonly_flag 网络数据库技术 第7章表数据操作 例例 创建一个规则，并绑定到表创建一个规则，并绑定到表KC的课程号列，用于限制课的课程号列，用于限制课 程号的输入范围程号的输入范围 use xscj go Create rule kc_rule as rang like 1-50-90-9 go Use xscj exec sp_bindrule kc_rule,kc.kch go 网络数据库技术 第7章表数据操作 例例2 创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规 则中列出的值则中列出的

24、值 use xscj go Create rule list_rule as list1 in (C语言语言,微机原理微机原理,离散数学离散数学) go Use xscj exec sp_bindrule list_rule,kc.kcm go 网络数据库技术 第7章表数据操作 例例3 定义一个用户数据类型定义一个用户数据类型course_num,然后将前面定义的规则然后将前面定义的规则kc_rule绑绑 定到定到course_num上，最后定义表上，最后定义表kc1，其课程号的数据类型为，其课程号的数据类型为course_num。 use xscj exec sp_addtype cours

25、e_num,char(3),not null exec sp_bindrule kc_rule,course_num Go use xscj create table kc1(kch course_num, kcm char(30) not null, kkxq tinyint, xsh tinyint, xf tinyint) 网络数据库技术 第7章表数据操作 use xscj if exists(select name from sysobjects where name=kc_rule and type=r) begin exec sp_unbindrule kc.kch drop ru

26、le kc_rule end go 网络数据库技术 第7章表数据操作 二、二、 实体完整性的实现实体完整性的实现 通过选择一列或多列做主键可实现表的实体完整性。通过选择一列或多列做主键可实现表的实体完整性。 一个表只能有一个一个表只能有一个primary key约束约束,且且primary key 约束中的列不能为空值约束中的列不能为空值. 如果要确保一个表中的非主键列不输入重复值如果要确保一个表中的非主键列不输入重复值,应在该应在该 列上定义唯一约束列上定义唯一约束(unique 约束约束). Primary key 与与 unique 约束的主要区别如下约束的主要区别如下: (1)一个表只

27、能创建一个一个表只能创建一个primary key 约束约束,但可以对不但可以对不 同的列创建若干个同的列创建若干个unique约束约束. 网络数据库技术 第7章表数据操作 (2) primary key约束列不能为空值约束列不能为空值,unique约束能为约束能为 空值空值 (3)创建创建 primary key约束时约束时,系统自动产生索引系统自动产生索引,索引索引 的默认类型为聚集索引的默认类型为聚集索引,创建创建unique约束时约束时,系统自动系统自动 创建一个创建一个unique 索引索引,索引类型为非聚集索引索引类型为非聚集索引. 网络数据库技术 第7章表数据操作 1 利用管理器

28、定义和删除利用管理器定义和删除primary key 约束约束 2 利用管理器定义和删除利用管理器定义和删除unique 约束约束 3 利用利用sql语句创建及删除语句创建及删除primary key 约束和约束和unique 约束约束 网络数据库技术 第7章表数据操作 1)创建表的同时创建创建表的同时创建 语法格式语法格式: create table table_name (column_name datatype constraint constraint_name not null Primary key/unique clustered/nonclustered n) 网络数据库技术

29、第7章表数据操作 use xscj create table xs3 (xh char(6) not null constraint xh_pk primary key, xm char(8)not null,identtitycard char(20) constraint sh_uk unique, zhy char(10)null,xb char(2)not null,chsh smalldatetime not null, zxf tinyint null,bzh text null,rxrq datetime constraint datedflt default getdate()

30、 ) 网络数据库技术 第7章表数据操作 2) 通过修改表创建通过修改表创建primary key 约束和约束和unique 约束约束 alter table table_name add constraint constraint_name primary key/unique clustered/nonclustered (column,n) 网络数据库技术 第7章表数据操作 如如: Alter table xs add constraint xs_pk primary key clustered(xh) Go Alter table xs add constraint xs_uk uniq

31、ue nonclustered(shfzh) go 3)删除删除primary key 约束和约束和unique 约束约束 Alter table xs Drop constraint xs_pk，xs_uk go 网络数据库技术 第7章表数据操作 三、参照完整性的实现三、参照完整性的实现 对两个相关联的表进行数据插入和删除时对两个相关联的表进行数据插入和删除时,通过参照完整性保证它们之间通过参照完整性保证它们之间 数据的一致性数据的一致性 如果两个表之间定义了参照完整性如果两个表之间定义了参照完整性,则则: (1)从表不能引用不存在的键值从表不能引用不存在的键值 (2)如果主表中的键值更改了

32、如果主表中的键值更改了,那么整个数据库中那么整个数据库中,对从表中该键值的所有对从表中该键值的所有 引用要进行一致的更改引用要进行一致的更改. (3)如果主表中没有相关联的记录如果主表中没有相关联的记录,则不能将记录添加到从表则不能将记录添加到从表 (4)如果要删除某一记录如果要删除某一记录,应先删除从表中与该记录相匹配的记录应先删除从表中与该记录相匹配的记录. 利用利用 foreign key 定义从表的外码定义从表的外码, primary key 约束和约束和unique约束定义约束定义 主表中的主码或唯一码主表中的主码或唯一码(不允许为空不允许为空),可实现主表和从表之间的参照完整可实现

33、主表和从表之间的参照完整 性。性。 网络数据库技术 第7章表数据操作 定义表间参照关系定义表间参照关系:先定义主表的主码先定义主表的主码(或唯一码或唯一码), 再对从再对从 表定义外码约束表定义外码约束. 1 利用企业管理器定义表间的参照关系利用企业管理器定义表间的参照关系 2 利用企业管理上删除表间的参照关系利用企业管理上删除表间的参照关系 3 利用利用SQL命令定义表间的参照关系命令定义表间的参照关系 1）创建表的同时定义外码约束）创建表的同时定义外码约束 网络数据库技术 第7章表数据操作 create table table_name (column_name datatype fore

34、ign key references ref_table(ref_column) n) 网络数据库技术 第7章表数据操作 例例 在在xscj数据库中创建数据库中创建xs4表表,xs4.xh为主键为主键,然后定然后定 义从表义从表xs_kc1.xh为外码为外码 use xscj create table xs4 ( xh char(6) not null constraint xh_pk primary key, .) Go Create table xs_kc1 (xh char(6) not null foreign key references xs4(xh), ) Go 网络数据库技术

35、第7章表数据操作 例：例： use xscj alter table xs_kc1 add constraint kc_fk foreign key (kch) references kc(kch) go 网络数据库技术 第7章表数据操作 2) 通过修改表定义外码约束通过修改表定义外码约束 alter table table_name add constraint constraint_name foreign key (column,n) references ref_table(ref_column,n) 3)利用利用SQL命令删除表间的参照关系命令删除表间的参照关系 删除参照关系，实际是

36、删除从表的外码即可删除参照关系，实际是删除从表的外码即可 alter table table _name drop constraint constraint_name go 网络数据库技术 第7章表数据操作 7.4 默认值约束及默认值对象默认值约束及默认值对象 一个字段默认值的建立可通过如下两种方式实现：一个字段默认值的建立可通过如下两种方式实现： （1）在定义和修改表时，定义默认值约束）在定义和修改表时，定义默认值约束 （2）先定义默认对象，然后将该对象绑定到表的相应）先定义默认对象，然后将该对象绑定到表的相应 字段字段 一在表中定义及删除默认值约束一在表中定义及删除默认值约束 1.默认值

37、约束的定义默认值约束的定义 通过通过SQL命令的执行，在定义或修改表时，可定义一命令的执行，在定义或修改表时，可定义一 个字段的默认值约束。个字段的默认值约束。 网络数据库技术 第7章表数据操作 默认值约束定义的一般格式：默认值约束定义的一般格式： create table table_name (column name datatype not null | null default constraint_expression n) 网络数据库技术 第7章表数据操作 例：在定义表时定义一个字段的默认值例：在定义表时定义一个字段的默认值 Use xscj create table xs1 (x

38、h char(6) not null, xm char(8) not null, zhy char(10) null, xb bit not null, birthdate smalldatetime not null, zxf tinyint null， rxrq datetime default getdate() ) go 网络数据库技术 第7章表数据操作 例例2 下例的功能与上例相同，但在定义默认值的同时下例的功能与上例相同，但在定义默认值的同时 指定了约束名指定了约束名 use xscj create table xs1 (xh char(6) not null, xm char(8

39、) not null, zhy char(10) null, xb bit not null, birthdate smalldatetime not null, zxf tinyint null rxrq datetime CONSTRAINT datedflt default getdate() ) Go 网络数据库技术 第7章表数据操作 例例 3在修改表定义一个字段的默认值约束。在修改表定义一个字段的默认值约束。 use xscj alter table xs1 add adddate smalldatetime null constraint adddatedflt default getdate() with values 网络数据库技术 第7章表数据操作 With values 仅用在对表添加新字段的情况下。若使仅用在对表添加新字段的情况下。若使 用了用了with values ，则将为，则将为 表中各现有行添加的字段表中各现有行添加的字段 提供默认值：如果没有使用提供默认值：如果没有使用with values 那么每一