数据库原理电子教案：第四章-SQL-Server表管理.doc

第4章 SQL Server表管理

课题： SQL Server表管理
目的要求：
掌握SQL Server2005数据库的数据类型
掌握SQL Server2005数据库表的SQL命令创建方法
掌握SQL Server2005数据库表的SQL命令管理方法
掌握SQL Server2005数据库表的基本组成
重点难点：
用SQL命令建立SQL Server2005表
课外作业布置：
复习本节课内容，完成本章习题

表与视图
复习提问
1.指出下面3种扩展名对应的文件类型：
.mdf .ndf .ldf
2. 建立数据库命令的命令是什么？
3.用SQL命令建立数据库时，说出表示以下短语的功能：
name=
filename=
size=
maxsize=
filegrowth=
log on
4.修改数据库worker，将数据库名称改为employee，写出对应的SQL命令。
5.删除数据库worker和视图vi，分别写出SQL命令。
6.在SQL Server2005中有个数据库Master，对应的文件是C盘上的Master.mdf和Master.ldf，指出此数据库的物理文件和逻辑文件。
4.1 SQL Server中的数据类型
1．整数型
可用于存储精确的整数，包括bigint（8B），int（4B），smallint（2B）和tinyint（1B） 4种类型。它们的区别在于存储的范围不同。 tinyint数据存储为单精度型、无符号整型、8b（1个字节）的数值形式，范围为0～255。
2．精确数值型
精确数值型数据由整数部分和小数部分构成。包括：
decimal：十进制型，如:decimal(10,5)
numeric：数值型，如：numeric(10)、 numeric(10，5)
这2种类型用法相同，长度有5，9，13等，且总位数不得小于小数位数。
3．近似数值型
近似数值型数据可以存储精度不是很高，但数据的取值范围却又非常大的数据。包括2种：
real：占4Byte，可用普通方法和科学计数法表示
float:占8Byte，可以写成float(n)的形式，n精度是1～15。可用普通方法和科学计数法表示，如12.3456e4
4．货币型
在SQL Server中用十进制数来表示货币值。分为2种：
money:长度为8Byte，如$326779.1234，精确到万分之一
smallmoney: 4Byte，如$23.333，4.51e8， $4.51e8
5．位（bit）型
在SQL Server中位型相当于逻辑型，只接收0和1，占用1个字节存储空间，表示是或不是。
6．字符型
字符型数据指由字母、数字和其他特殊符号（如标点、$，#，@）、汉字构成的字符串，设计表时应该考虑最大长度，最好留有余地，赋值时要用’ ’ 引起来，包括4种类型，其默认值都是50。
char:char(n)，1～8000，默认为1，不够时用空格补齐，超过时截掉后面的。
varchar: varchar(n),最多255，可变长，长度超过20时，不会用空格补齐，便于节省空间。
nchar: nchar(n)，n是1～4000，支持中英法等多国文字，不够时用空格补齐，超过时截掉后面的。
nvarchar: nvarchar(n)，n是1～4000，长度超过20时，不会用空格补齐，便于节省空间。
7．文本和图像型
当存储的字符数目大于8000时使用文本型，导入图形应用图像型。包括3种：
text:存储文本数据，长度为16 ，最大为231－1Byte。
ntext:采用unicode编码，长度为16 ，最大容量230－1Byte，可接受多种编码字符。
image:存储图片，长度为16，容量可到231－1Byte，但不可以直接输入。
8．日期时间型
在SQL Server中日期时间型的数据以字符串的形式表示，即要用单引号括起来，有两种类型。
datetime:日期和时间的结合体，长度为8。范围：1753.1.1 0时～9999.12.31 23:59:59。格式：MM/DD/YYYY hh:mm:ss AM/PM 日期分隔符可以是 /或 -，也可表示为 YYYY/MM/DD，时间分隔符是： ，中间用空格分开。可以只要日期，也可以只要时间，也可以组合使用，可只精确到月或分。
smalldatetime:长度为4，范围是：1900.1.1～2079.6.6。其它要求同datetime。
9．二进制类型
有2种类型，常用来存贮图形、声音等数据，不可以直接输入，长度默认为50，最大8000。
binary:格式是binary(n)，数据的存贮长度固定的，即n+4字节，若长度小于n，余下部分用0补齐。
Varbinary：格式是Varbinary(n)，长度可变，若长度小于n，余下部分不用0补齐。
10．特殊类型
Timestamp:时间戳，长度是8（与datetime相同），系统提供一个唯一值，反映数据修改的相对顺序，相当于一个单调上升的计数器，当所定义的列在更新或插入新行时，此列值自动更新并自动填写。如果列名为Timestamp，系统自动设置为Timestamp类型。
Uniqueidentifier:唯一标识型，长度为16，是根据网卡地址和CPU时钟产生的，通过函数newid()获得，全球各地机器产生的此值都不同，但用户可以修改。
Sql_variant:可变类型，长度不可以设置，能保存除文本、图形数据和timestamp外的其它任何数据。
User_datatype:用户自定义类型，根据数据库中“用户定义的数据类型”确定。
例：定义数据类型address，命令是：
exec sp_addtype address,’varchar(80)’,’not null’
删除自定义数据类型，命令是：
exec sp_droptype address
4.2 创 建 表
一、引例
【例4-1】用SQL语句完成创建student_Info表。
CREATE TABLE student_Info
( student_ID INT not null PRIMARY KEY,
student_Name CHAR(10) NULL,
student_Sex CHAR(2) NOT NULL,
born_Date DATETIME,
class_NO INT, tele_Number CHAR(10),
ru_Date DATETIME,
address VARCHAR(50),
comment VARCHAR(200) )
二、使用CREATE TABLE语句创建表
语法格式：
CREATE TABLE 表名
( 列名 类型(长度) ｛列约束条件｝
[,…n]
)
说明：
o [,…n]：表明可以重复前面的内容。在本语法中表明可以定义多个字段。
o 规定长度的数据类型不允许用户再定义长度。
列约束条件包括：
o NULL或NOT NULL 能否为空，默认为NULL
o PRIMARY KEY 主键
o UNIQUE 值是否唯一
o Foreign key 列名 references 表名(列名)
o Check (条件表达式)
o Default (常量表达式)
说明常量值只能是：常量、内部函数、null
三、建表实例1
要求：建立一个工人表，包括编号、姓名、性别、出生日期、入厂日期、职称、备注
Create table worker
(编号 int not null primary key,
姓名 char(8) not null,
性别 char(2) null,
出生日期 datetime null,
入厂日期 datetime not null ,
职称 varchar(20) ,
工资 money null default 2005,
备注 ntext null)
第8教案 实训4：用SQL命令创建表
实训4：用SQL命令创建表
（一）、实训目的
掌握利用查询分析器通过SQL命令建立数据库表的方法。
掌握用企业管理器查询数据库表的方法。
掌握通过SQL命令管理数据库表的方法。
（二）、实训内容
1、建表实例1（注意：默认值及主键定义方法）
要求：建立一个工人表，包括编号、姓名、性别、出生日期、入厂日期、职称、备注
Create table worker
(编号 int not null,
姓名 char(8) not null primary key(编号,姓名),
性别 char(2) null,
出生日期 datetime null,
入厂日期 datetime not null default (getdate()),
职称 varchar(20) null,
工资 money null,
备注 ntext null)

2、建表实例（注意：唯一性及约束规则的建立）
要求：建立一个工人信息表，包括编号、姓名、职务，要求主键为编号，姓名具有唯一性
Create table 职工信息表
(编号 char(10),
姓名 char(8),
职务 char(12),
Constraint pk_no primary key(编号),
Constraint pk_name unique(姓名)
)
说明：使用唯一性约束的字段可以为空，一个表中可定义多个唯一性字段。

3、建表实例（注意检查约束的建立）
要求：建立一个基本信息表，包括编号、姓名、性别、电话，要求性别只接受“F”和“M”，电话只能是8位数字
Create table 基本信息表
(编号 char(10),
姓名 char(8) check(len(姓名)<=8),
性别 char(2) default ‘M’,
Constraint chk_sex check(性别 in (‘F’,’M’)),
电话 int
Constraint chk_tel check(电话 like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)
)
说明：一个数据库中不允许出现同名的约束名。

4、建表实例（注意：外键约束的建立）
要求：有一个职工信息表(编号,姓名,性别,年龄)，其中主键是编号。现要建立一个工资表(编号、基本工资、奖金、扣款)，编号来自职工信息表中的编号。
Create table 工资表
(编号 char(10) not null primary key foreign key references 职工信息表(编号),
基本工资 float not null,
奖金 float null,
扣款 float null,
合计 float
)
说明：当前键与外键的类型要匹配，宽度相同，外键必须是另一张表的主键或唯一性约束字段。

5、利用企业管理器对所建进行查看、参数修改、删除操作。

6、利用查询分析器对对所建进行查看、参数修改、删除操作。
第9教案 表记录管理

课题：表记录管理
目的要求：
掌握SQL Server2005表的管理方法
掌握SQL Server2005记录的输入方法
掌握SQL Server2005记录的管理方法
掌握SQL Server2005视图管理方法
重点难点：
用SQL命令进行记录的处理
课外作业布置：
复习本节课内容，完成本章习题

4.3 修改表的结构
复习提问
1、如何将表建立在数据库Northwind中，说出在查询分析器中的操作过程。
2、建表时，要设置性别只能为“男”或“女”，如何设置限制条件？
3、建表时有一个字段参加工作日期，将其默认值设置系统日期，如何设置限制条件？
4、建表时有2个字段学号和课程号，设置这2个字段都设置为主键，如何设置限制条件？
一、使用企业管理器修改表结构
（1）打开“企业管理器”并展开服务器，继续展 开“数据库”，并展开要修改表的数据库，本例 是“Student”。在“表”项上单击鼠标，右边会出 现表对象，右击要修改的表名，并选择 “设计 表”命令。
（2）这时会出现创建表结构时的窗口，然后 对表结构做修改就可以了。最后保存退出。
二、使用ALTER TABLE语句修改表结构
1．语法格式
ALTER TABLE table_name
{
ALTER COLUMN 列名 类型 [NULL|NOT NULL]
ADD 列名 类型 [NULL|NOT NULL]
DROP COLUMN 列名
[,…n]
}
其它参数与建表相同。如果表中已有记录，修改表结构后可能会导致数据的值发生改变，以适应新结构的要求。
2．应用举例
在数据库factory中建立一个工人信息表employee，然后在表中增加一个salary字段，删除表中的age字段，修改cq字段的数据类型。
Use factory
Create table employee
(id char(8) primary key,
name char(20) not null,
department char(20) null,
age int,
cq int,
)
alter table employee
Add salary float,
drop column age,
Alter column cq decimal(4,1)
应当这样修改：
alter table employee
Add salary float
alter table employee
drop column age
alter table employee
Alter column cq decimal(4,1)
说明：修改表结构时，每次只能修改一个字段，不能同时修改多个字段。如果要修改多个字段，则需要多次使用alter table 命令。
4.4 添加表中的记录
一、使用企业管理器添加记录
使用企业管理器添加记录的步骤如下。
（1）右击要添加记录的表名，并选择打开表命令，进而选择“返回所有行”。
（2）如果是第一次执行该命令，出现的窗口的二维表中是没有任何内容的。
（3）在标有“*”的一行输入相应数据就可以了。
二、使用INSERT语句添加记录
1．语法
INSERT [INTO] 表名 [（列名1[,列名2…]）]
VALUES (值1 [, 值2…])
2．参数说明
INSERT语句每次只能插入一条记录，如果要插入N条记录，则要使用N次INSERT语句。
如果包括全部列，则列名列表可以省略，但值要与表中列的顺序一一对应，类型一致，个数相等，不可省略任何一个。
插入记录后，系统自动按照主键值排列记录的顺序。
Student库的student_info表结构为：
（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)
例1：insert student_info
Values(‘20080105’,’张小强’,’男’,’1990-12-20’,’200801’,’5578678’,’2008-09-12’,’长沙市香樟路22号’,’中共预备党员’,’888888’,’Football’)
也可以这样写：
例2：insert student_info
（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)
Values(20080105,’张小强’,’男’,’1990-12-20’,200801,’5578678’,’ 2008-09-12’, ’长沙市香樟路22号’,’中共预备党员’)
Student库的student_info表结构为：
（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)
例3：insert student_info
（student_ID,student_name,student_sex,born_date,class_NO)
Values(‘20080105’,’张小强’,’男’,’1990-12-20’,’2008010’)
说明：
其它没有赋值的各列，系统自动设为null。
日期型字段中：可以省略日，但年和月不可省略，如’1990-12-20’,系统也接受’1990-12‘这种日期，而在企业管理器中输入1990-12时，系统自动修改为1990-12-1。
4.5 修改表中的记录
一、使用企业管理器修改记录
通过企业管理器修改记录和添加记录的操作非常类似 ，操作起来比较简单，具体可参考实际操作。
二、使用update语句修改记录
语法格式：
UPDATE 表名
SET 列名=值[,…n]
[WHERE 条件表达式]
参数说明：若没有指定条件，则不会筛选记录，即所有记录的这一列的值全部改为同一个值。
【例4-7】将陈白露的tele_Number改为“2625786”。
UPDATE student_Info
SET tele_Number='2625786'
WHERE student_Name='陈白露'
说明：
如果省略了WHERE子句，那么表明不需要筛选记录，即所有的记录的tele_Number字段的值都改为“2092786”。
与使用企业管理器修改表中记录比较，使用语句可以对满足某条件的记录进行大批量的修改，快捷很多。
思考：将表result_info表中的所有result字段值都增加2，该如何设置？
4.6 删除表中的记录
一、使用企业管理器删除记录
方法1：删除单条记录
右击记录，执行“删除”。
方法2：删除连续多条记录：
按shift键配合或拖动鼠标，选择多条记录，右击，执行“删除”。
二、使用DELETE语句删除记录
1．语法
DELETE [FROM] 表名
[WHERE 条件]
2．参数说明：若省略了WHERE子句的话表明是要删除表中所有的记录，这时候就成了空表（结构还存在）。
【例4-8】将1980年出生的学生删除。
DELETE FROM student_Info
WHERE YEAR(born_Date)=1980

4.7 删 除 表
一、使用企业管理器删除表
右击表，选择“删除”。
二、使用DROP TABLE语句删除表
格式：DROP TABLE table_name
例：drop table 职工信息表
说明：表删除后，结构和记录都会被删除。

第10教案 实训5：记录与视图操作
实训5：记录与视图操作
（一）、实训目的
掌握利用查询分析器向表添加记录的方法。
掌握用企业管理器和查询分析器管理表记录的方法。
掌握通过SQL命令建立视图和管理视图的方法。
（二）、实训内容
1、用企业管理器输入记录
2、用查询分析器输入记录
insert student_info
（student_ID,student_name,student_sex,born_date,class_NO,tele_Number,ru_date,address,comment)
Values(20080105,’张小强’,’男’,’1990-12-20’,200801,’5578678’,’ 2008-09-12’, ’长沙市香樟路22号’,’中共预备党员’)
3、用企业管理器修改记录
4、用查询分析器修改记录
将“张小强”的备注由’中共预备党员’改为’中共正式党员’。
4.8 视图的概念
视图（view）是虚拟的表，它的数据来自于一个或多个表甚至是视图。使用视图有下列好处：
限制用户检索的数据。视图相当于提供了一个特定的“窗口”，用户所看到的数据只跟用户的需求有关系。
方便数据的操作。视图可以将几个表的数据集中到一起，对该视图的操作也就是操作了几个表，简化了操作界面。
一、使用企业管理器创建视图
在Student数据库中，以创建“班号为200501的学生情况”视图为例，说明在企业管理器中创建视图的过程。
（1）打开“企业管理器”并展开服务器，继续展开“数据库”，并展开要建立视图的数据库，本例是“Student”。在“视图”项上右击鼠标，执行“新建视图”命令，如图所示。
（2）接着弹出“创建视图”的窗口，在这个窗口中从上往下有4个窗格。
（3）在第一个窗格中单击鼠标右键，从弹出的菜单中选择“添加表”命令。
（4）接着出现“添加表”对话框，在“添加表”对话框中选择要加入的表、视图或函数。本例中需用到“student_Info”这张表，所以选中这张表后单击“添加”按钮就完成了添加表的工作。如果要加入多张表，借助Ctrl和Shift键可以进行多选。
（5）在第二个窗格中选择创建视图所需的字段，可以指定列的别名、排序方式和规则等。本例中需要先选择所有列，选择所有列可以直接在“*”前的小方框打上勾号，也可以将所有列前面的小方框打上勾号，再设定“class_No”字段的准则为“＝200501”。
（6）单击“保存”按钮，在出现的对话框中输入视图名。本例输入“班号为200501的学生情况”即可。
4.9.2 使用CREATE VIEW创建视图
1．语法
CREATE VIEW 视图名 [列名 [,…n]]
[WITH ENCRYPTION]
AS
SELECT 语句
[WITH CHECK OPTION]
说明：
为了保密可以使用WITH ENCYPTION对存放的CREATE VIEW文本加密。但是该选项使用后，创建者也是没有办法再看到的。所以创建者应在其他地方做个备份。
WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时，要保证更新、插入或删除的记录满足视图中SELECT语句的条件表达式。
SELECT语句的完整格式：select … from… where…
列名表示字段的别名，即字段在视图中的输出名。
【例4-9】创建选修了课程号为1的学生情况视图。
CREATE VIEW 选修了课程号1的学生
AS
SELECT student_Name,class_No,result
FROM student_Info,result_Info,course_info
WHERE student_Info.student_Id=result_Info.student_Id and result_Info.course_No='1'
说明： CREATE VIEW 必须是第一条语句才能执行
【例4-10】创建班号为“200501”的学生情况视图。
CREATE VIEW 班号为200501的学生情况
AS
SELECT student_Name,class_No,Tel_number
FROM student_Info
WHERE class_NO='200501'
4.10 管 理 视 图
一、查看视图
通过系统存储过程sp_helptext查看生成视图的代码。例如：
Use Student
Exec sp_helptext '班号为200501的学生情况'
说明：
如果系统存储过程不是批处理语句的第一句，那么需要用Execute命令，可以简写为Exec。如果是第一句，可以省略Exec，直接写存贮过程名。如：sp_helptext '班号为200501的学生情况'
单引号可以省略。
二、删除视图
删除视图有两种方法。一是通过企业管理器，其操作方法与删除表的方法一样。另外一种方法是通过SQL语句。
语法格式
DROP VIEW 视图名 [,…n]
参数说明
DROP VIEW一次可以删除多个视图。
举例：删除视图“班号为200501的学生情况”。
drop view 班号为200501的学生情况
三、通过视图修改基本表中的数据
1、修改视图
格式：
alter view 视图名[(列名1,列名2,…)]
As
select 语句
例：修改视图
alter view 班号为200501的学生情况(student_id,student_name,Tele_number,Student_sex)
As
select student_id,student_name,Tele_number,Student_sex
From Student_info
说明：select后面的列名列表必须要和视图列表中的字段名相同
2、向视图中插入记录
格式：
insert into 视图名
values(值列表）
例：向视图“班号为200501的学生情况”插入一条记录
insert into 班号为200501的学生情况
values(‘20011123’,‘李成龙’,7788778,‘男’)
说明：
此命令在向视图插入记录的同时也向视图对应的表（称为基表）student_info插入了一条记录。
当视图所依赖的表有2个以上时，则不允许向视图插入记录，因为会影响到多个基表。
3、修改视图中记录的内容
格式：
update 视图名
set 列名＝值
例：在视图“班号为200501的学生情况”中，将”李成龙”的名字改为“魏成龙”。
Update 班号为200501的学生情况
Set student_name=‘魏成龙’
Where student_name=‘李成龙’
说明：
此命令在向视图修改记录的同时也向视图对应的表（简称基表）student_info也做了修改。
当视图所依赖的表有2个以上时，则不允许修改视图的记录，因为会影响到多个基表。
4、删除视图中记录的内容
格式：
delete
from 视图名
where 条件
例：在视图“班号为200501的学生情况”中，将 “魏成龙”的记录删除。
delete
from 班号为200501的学生情况
where student_name=‘魏成龙’
说明：
此命令在向视图修改记录的同时删除了基表student_info的相应记录。
当视图所依赖的表有2个以上时，一般不允许删除视图的记录，因为会影响到多个基表。