资源简介

(共68张PPT)
项目3 数据表的创建与管理
任务1 数据表的概念与数据类型
子任务1 表的概念
(1)系统表：存储的是系统信息，就像操作系统的注册表一样，不能随意更改。
(2)用户自定义表：存储的是用户的信息，就像操作系统的用户文件一样，用户可随意进行更改。
(3)已分区表：是数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中。在维护整个集合完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或者索引更容易管理。如果表非常大或者可能变得非常大，可以选择使用已分区表。
(4)临时表：SQL Server 2005中除了系统表和用户自定义表外，还有一类临时表。临时表与永久表相似，但临时表存储在tempdb数据库中，当会话断掉之后会自动删除。临时表是在SQL Server 2005启动以后，用户在操作数据库的时候创建生成的，在断开与SQL Server 2005服务器的连接或者关闭服务以后，临时表将自动删除。
子任务2 表的数据类型
4.1 SQL Server表及数据类型
在SQL Server 2012中同时还提供了用户自定义数据类型的功能，方便用户管理相同类别的数据，但它并不是真正的数据类型。
1）使用系统存储过程sp_addtype建立自定义数据类型
基本语法：
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
2）使用系统存储过程sp_droptype删除已经建立的自定义数据类型
基本语法：
sp_droptype [ @typename = ] 'type'
3）使用企业管理器建立和删除用户自定义数据类型
任务2 创建数据表
创建主
键约束
创建唯
一性约束
创建外
键约束
创建检
查约束
保存表
的定义
创建表
在【对象资源管理器】窗口中：
展开“数据库”下的EDUC节点，右键“表”节点，选择“新建表”命令，进入【表设计器】
表的设计主要包括以下内容：
表所包含的数据类型。
表的各列及每一列的数据类型（如果必要，还应注意列宽）。
哪些列允许空值。
是否要使用以及何时使用约束、默认设置或规则。
所需索引的类型，哪里需要索引，哪些列是主键，哪些列是外键。
1）NULL
2）主键
3）默认值
4）标识列
5）全局惟一标识列
子任务1 使用表设计器创建表
（1）打开企业管理器。在“企业管理器”窗口中展开服务器组，然后展开“数据库”文件夹，单击选定要存储表的数据库，右击，从弹出的快捷菜单中执行“新建”→“表”命令，弹出如图4-3所示的窗口。
（2）表设计窗口的上方有一个表格，在这个表格中可以输入字段的属性，每一行对应一个字段。
（3）在该窗口的表格区，可以设计该表。
（4）设计好表后，单击工具栏上的“保存”按钮，弹出“选择名称”对话框，在该对话框中的“输入表名”文本框中输入“student”，单击“确定”按钮即可。
子任务2 使用T-SQL语句创建表
基本语法：
CREATE TABLE
[ database_name. table_name
( column_name data_type | [ IDENTITY ( seed , increment ) ] | [ NULL | NOT NULL ]
| [CONSTRAINT PRIMARY KEY | CONSTRAINT ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
用T-SQL语句创建一个较复杂的表的语句如下：
CREATE TABLE test
( id int IDENTITY ( 1, 2) NOT NULL，
cno int PRIMARY KEY CLUSTERED，
name Varchar(5)
)
子任务3 使用已有表创建新表
1. 使用T-SQL语句创建表之间的关系
下面以创建Selectcourse表为例进行介绍:
在“课程管理系统”数据库中创建一个Selectcourse表，它有三个字段，Id字段，数据类型为int，此字段自动编号，标识种子为1，标识增量为2，且它为主键，cNo字段和stId字段，分别是course 和student表的主键，则使用T-SQL语句创建Selectcourse表与course 和student表之间关系的T-SQL语句如下：
CREATE TABLE Selectcourse
(
Id int identity(1,2) NOT NULL PRIMARY KEY,
cNo int CONSTRAINT FK_1 FOREIGN KEY REFERENCES course(CNo),
stId int CONSTRAINT FK_2 FOREIGN KEY REFERENCES student(stId)
)
2. 使用表设计器创建表之间的关系
３. 使用数据库关系图创建表之间的关系
子任务3 使用已有表创建新表
【例4.1】 省略CREATE DATABASE命令中的各选项创建exampledb1数据库。
（1）新建查询。在SSMS窗口中单击工具栏中的【新建查询】按钮，新建一个查询窗口。
（2）在查询窗口中输入Transact-SQL语句
（3）执行查询。单击工具栏中的【√】按钮可以检查Transact-SQL语句，单击【执行】按钮可以执行指定的Transact-SQL语句。
执行结果如图4.6所示。
任务3 管理数据表
子任务1 修改数据表结构
子任务1 修改数据表结构
【任务分析】
设计人员在完成表的创建后，难免要修改其结构，包括修改表名、字段的数据类型和字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎和删除表的完整性约束条件等。
【课堂任务】
掌握修改表结构的两种操作方法。
使用SSMS修改表结构。
使用ALTER TABLE语句修改表结构。
（一）使用SSMS修改表结构
1．修改表名
选中要修改的表，再单击一次，表名即处于编辑状态，直接输入新的表名。或用鼠标右键单击要修改的表，在弹出的快捷菜单中选择【重命名】命令，再输入新表名。
2．修改字段名和字段数据类型、增加和删除字段、修改字段的排列位置
用鼠标右键单击要修改的表，在弹出的快捷菜单中选择【设计】命令，打开【表设计器】窗口，同新表一样，可以向表中加入列、从表中删除列或修改列的属性，修改完毕单击【保存】按钮即可。
3．更改表的存储引擎、删除表的完整性约束条件
用鼠标右键单击要修改的表，在弹出的快捷菜单中选择【设计】命令，打开【表设计器】窗口，单击【表设计器】选项卡中的命令或快捷菜单中的命令可以修改主键、关系、索引和CHECK约束等。
（二）使用ALTER TABLE语句修改表结构
1．ALTER TABLE语句的语法格式
ALTER TABLE <表名>
{
[ALTER COLUMN <字段名><新数据类型> [[NULL | NOT NULL ]]
| [ADD<新字段名><数据类型> [<表级完整性约束条件>][,…n]]
|[DROP CONSTRAINT <约束名>[,…n] |COLUMN <字段名>[,…n]]
}
2．参数的功能说明
（1）ALTER COLUMN <字段名><新数据类型> [NULL | NOT NULL ]修改指定表中字段的数据类型或完整性约束条件。
（2）ADD<新字段名><数据类型> [<表级完整性约束条件>][,…]为指定的表增加一个新字段，并指定新字段的数据类型和完整性约束。
（3）DROPCONSTRAINT <约束名>[,…n] |COLUMN <字段名>[,…n]删除指定表中不需要的约束或字段。
提示 ① 添加列时，不需要带关键字COLUMN；在删除列时，在列名前要带上关键字COLUMN，因为在默认情况下，认为是删除约束。
② 添加列时，需要带数据类型和长度；在删除列时，不需要带数据类型和长度，只需指定列名。
③ 如果在该列定义了约束，则在修改时会限制，如果确实要修改该列，则先必须删除该列上的约束，然后再修改。
使用T-SQL语句修改表
1. 修改表的结构
ALTER TABLE table
{
[ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ] }
| ADD
{ [ ] }| [ ...n ]
| DROP COLUMN{drop_column_name} [ ...n ]
| ADD
{ [ CONSTRAINT ]add_ constraint_name } [ ,...n ]
| DROP
{ [ CONSTRAINT ]drop_ constraint_name
} [ ,...n ]
}
子任务2 数据表更名
在MS SQL中，如果你想修改某一个数据库的名称，你可以通过下面几种方法实现。
方法一：使用SP_RENAMEDB系统存储过程实现。
语法： sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'
例子： 我需要将数据库Test_1的名称修改为Test
use master
go
exec sp_renamedb @dbname='Test_1', @newname='Test';
go
2. 重命名表
Sp_rename [ @objname = ] ‘object_name’ , [ @newname=]‘new_name’
有时候执行该脚本，会报如下错误：
消息 5030，级别 16，状态 2，第 1 行
无法用排他锁锁定该数据库，以执行该操作。
出现这种情况，主要是还有其它程序或用户连接数据库，你必须确保没有任何用户正在使用数据库. 可以通过将数据库设置为单用户模式。在数据库属性——》“连接”选择里面找到限制访问“选项，选择SIGLE_USER选项。然后执行上面语句，即可修改数据库名称。
方法二：使用Microsoft SQL Server Managment Studio管理工具，选中要修改名称的数据Test1，单击右键，选择”重命名”；注意如果有其他用户会话连接该数据库则会报错，必须先杀掉那些会话进程或使数据库处于单用户模式下，再执行上面操作，否则会报错。
子任务3 查看信息
MsSqld把很多系统信息都存在了master系统表中比如用户名，数据库名等一些系统信息，可以通过存储过程，函数，或者直接查询的方式来获取这些信息。master数据库中sysusers表中存放的是系统所有用户名。这里包括系统账户和用户账户两种都在这里存放。其中name字段就是用户名，需要注意的是下面两个字段：islogin=“1”表示本条是账户，islogin=“0”表示本条是角色，status=“2”表示用户帐户，status=“0”表示系统帐户。其他字段无太多使用意义。
使用企业管理器查看
在企业管理器中展开相应的数据库节点，找到需要查看属性的表，右击该表名称，在弹出的快捷菜单中选择“属性”选项，弹出“表属性”对话框，如图4-12所示。
在这个对话框中可以查看表中列的相关信息，如ID、名称、数据类型，大小的等信息。但是用户不能对其进行修改，用户可以单击“权限”按钮，来查看和修改表的权限，关于权限的管理会在后续章节中详细介绍。
使用T-SQL语句查看
1. 使用存储过程sp_help显示有关数据库对象、用户定义数据类型或SQL Server所提供的数据类型的信息。
基本语法：
sp_help [ [ @objname = ] name ]
2. 使用存储过程sp_spaceused显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间的信息。
基本语法：
sp_spaceused [[@objname =] 'objname']
子任务4 修改数据表
网站更换空间的时候，经常会因为MSSQL数据库名不同，导致从旧空间备份的数据库在新空间还原后无法正常使用。这时候，我们可以通过将数据库表的所有者修改为"dbo"来解决这个问题，具体的方法如下：
打开"Microsoft SQL Server"的“企业管理器”，点开要修改的数据库，选择工具栏上的“工具”－“SQL 查询分析器”，输入以下代码：
1、批量修改MSSQL表的所有者
exec sp_MSforeachtable 'exec sp_changeobjectowner " ","dbo"'
2、单个修改MSSQL表的所有者：
exec sp_changeobjectowner '要改的表名','dbo'
然后按"F5"执行命令，即可修改成功，刷新数据库就可以看到结果了。
子任务5 删除数据表
使用企业管理器删除表
在企业管理器中展开相应的数据库节点，找到需要删除的表，右击该表名称，在弹出的快捷菜单中选择“删除”选项，弹出“除去对象”对话框，在该对话框中列出可供删除的表（在本例中只有一个），选中后，单击“全部除去”按钮，实现表的删除。
使用T-SQL语句删除表
基本语法：
DROP TABLE table_name
任务4 数据的完整性
子任务1 数据完整性概念
关系模型的完整性包括两方面的含义：数据的正确性和相容性，用于保证数据在语义上的合理性和有效性。
数据完整性可以分为4种类型。
实体完整性
参照完整性
域完整性
用户自定义完整性
案例4-6 设置完整性
【案例描述】
在SSMS中，使用Transact-SQL语句为数据表设置约束，满足关系模型的实体完整性。
设置完整性的操作步骤如下:
(1)选择【开始】→【所有程序】→Microsoft SQL Server 2005→SQL Server Management Studio命令，打开【连接到服务器】对话框；单击【连接】按钮连接数据库，进入Microsoft SQL Server Management Studio主界面。
(2)单击工具栏中的【新建查询】按钮，如果服务器连接处于断开状态，则会弹出【连接到服务器】对话框。这时需要设置身份验证方式。单击【连接】按钮，打开查询编辑器，在【新建查询】编辑器中输入学生信息表的具体代码。
(3)单击工具栏中的【执行】按钮，即完成学生信息表student的建立。
(4)采用同样的方法单击【新建查询】按钮，新建一个查询编辑器，创建一个学生成绩表SC。
(5)单击工具栏中的【保存】按钮，即完成了数据表student和SC的创建和数据的实体完整性约束。
子任务2 约束
约束(CONSTRAINT)是数据库服务器强制用户必须遵从的业务逻辑，它定义了列允许的取值，限制用户输入指定列的值，从而强制引用完整性。
约束是强制完整性的标准机制。SQL Server 2005中的约束机制包括以下5种。
非空(NOT NULL)约束
检查(CHECK)约束
唯一(UNIQUE)约束
主键(PRIMARY KEY)约束
外键(FOREIGN KEY)约束
(1)选择【开始】→【所有程序】→Microsoft SQL Server 2005→SQL Server Management Studio命令，打开【连接到服务器】对话框；单击【连接】按钮连接数据库，进入Microsoft SQL Server Management Studio主界面。
(2)展开对象资源管理器，查找并定位到要修改的数据表student。
(3)右击要修改的数据表，在弹出的快捷菜单中选择【修改】命令。
(4)进入表设计器，单击要创建CHECK约束的字段。
(5)进入添加CHECK约束界面，将添加的约束添加至【选定的CHECK约束】列表框。
(6)单击【常规】选项表达式后的【浏览】按钮，进入【CHECK约束表达式】对话框，在文本框中输入表达式。
(7)单击【确定】按钮即可完成检查约束的设置。
【案例描述】
在SSMS中，利用视图为数据表创建检查约束。
在SSMS中，利用视图为数据表创建CHECK约束的操作步骤如下:
子任务3 查看、删除约束
在删除表，有外键约束和引用时，会提示不可删除，这时需要我们确认被哪些表引用，以及被存储过程引用的表，在表删除后，也经相应的删除相关过程。
1 被我引用的对象
select * from sys.dm_sql_referenced_entities('tablename','object');
--查看依赖项
2 引用我的对象
select * from sys.dm_sql_referencing_entities('tablename','object');
3 查询外键约束
--exec sp_helpconstraint 'tablename';
会列出相关表，及主键，可以删除对应的约束后，再删除表
4 删除约束及表、过程、视图脚本
--删除外键约束 alter table tablename drop constraint 主/外键id;
alter table tc_test drop constraint pk_tc_onstage_tc_test;
--删除过程
drop procedure pr_tc_tc_test;
--删除表
drop table tc_test;
--删除视图
drop view v_tc_tc_test;
子任务4 规则
1.规则的概念：
规则（Rule） 就是数据库中对存储在表的列或用户自定义数据类型中的值的规定和限制。规则是单独存储的独立的数据库对象。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响。规则和约束可以同时使用，表的列可以有一个规则及多个CHECK 约束。规则与CHECK 约束很相似，都可以执行一些与检查约束相同的功能。但检查约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个检查约束，相比之下，使用在ALTERTABLE 或CREATE TABLE 命令中的CHECK 约束是更标准的限制列值的方法，但CHECK 约束不能直接作用于用户自定义数据类型。规则也是维护数据库中数据完整性的一种手段，使用它可以避免表中出现不符合逻辑的数据，例如工资小于0、性别出现非男非女等。
2. 规则的创建语法
使用CREATE RULE语句可以创建规则，其语法结构如下：
CREATERULE <架构名>.<规则名>
AS<规则表达式> //规则表达式中可以包含算术运算符、关系运算符和谓词（例如IN、LIKE、BETWEEN等）。
2.1 例子：
例1：
创建一个规则SexRule，指定变量@sex的取值只能为'男'或'女'，代码如下：
CREATERULE SexRule
AS@sex IN ('男', '女')
例2：
创建一个规则WageRule，指定变量@wage的取值范围为0~50000，代码如下：
CREATERULE WageRule
AS@wage BETWEEN 0 AND 50000
3.创建了规则之后，还需要绑定到指定的数据列
绑定规则是指将已经存在的规则应用到列或用户自定义的数据类型中。使用存储过程sp_bindrule可以将规则绑定到列或用户自定义的数据类型，语法如下：
sp_bindrule [ @rulename = ]规则名,
[ @objname = ]对象名
3.1例子：
例：
规则SexRule绑定到表Employees的列Sex上的语句如下：
USE HrSystem
GO
EXEC ***sp_bindrule*** 'SexRule', 'Employees.Sex'
GO
测试规则SexRule：
例：（必须先绑定）
下面通过一个INSERT语句验证规则的应用效果。执行下面的INSERT语句，向表Employees中插入一条记录。
USE HrSystem
GO
INSERTINTO Employees (Emp_name, Sex, Title, Wage,IdCard,Dep_id)
VALUES('小李', '无', '职员', 10000, '110123xxxx', 1)
GO
**结果：**INSERT语句设置列Sex的值为“无”。因为列Sex绑定到规则SexRule，而在规则SexRule中规定列值只能是“男”或“女”。因此，执行INSERT语句的结果如下：
消息513，级别16，状态0，第2行
列的插入或更新与先前的CREATE RULE语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库'HrSystem'，表'dbo.Employees'，列'Sex'。
语句已终止。
返回结果中提示INSERT语句中指定的列Sex的指定值（'无'）不满足之前绑定的规则。
4 .因为规则是独立的数据库对象，即使删除掉了数据表也不会影响到规则，所以需要取消指定列的规则，还得自己解除绑定（注意仅仅是取消绑定关系）
4.1解除绑定规则语法：
解除规则绑定：
使用存储过程sp_unbindrule可以解除规则的绑定，它的基本语法如下：
sp_unbindrule [ @objname = ]对象名
对象名可以是表名和列名，也可以是自定义的数据类型。
4.2例子：
例：
使用存储过程***sp_unbindrule***取消表Employees的列Sex上绑定的规则，具体语句如下：
USE HrSystem
GO
EXEC ***sp_unbindrule*** 'Employees.Sex'
GO
执行的结果如下：
（所影响的行数为1 行）
已从表的列上解除了规则的绑定。
子任务5 默认值
案例：对课程表“Student”，为专业列“Specialty”设置默认值“计算机信息管理”，即当未对列“Specialty”输入数据时，该列的值自动被赋予“计算机信息管理”。可实现数据的域完整性控制。
在【表设计器】中
单击【表设计器】上方的列“Specialty”
在【列属性】选项卡中
在“默认值或绑定”文本框中输入‘计算机信息管理’字符串
子任务6 identity列
特点
列的数据类型为不带小数的数值类型。
在插入数据行操作时，该列的值由系统按标识增量的设置自动生成，不允许空值。
列值不重复，具有唯一标识表中一行的作用，从而可以实现表的实体完整性。
定义
指定数据类型为整数类型并且不允许为空。
设置（是标识）为“是”。
设置标识种子，指表中第一行的值，默认值为1。
设置标识增量，表示相邻两个标识值之间的增量，默认值为1。
案例：对课程表“Course”，将其序号列“No”定义为标识列，使其按照自然数从1开始逐步增加1的规律自动编号。
打开【表设计器】
在【列属性】选项卡中
数据类型为int
标识规范为“是”
标识种子为1
标识增量为1
任务5 表数据的操作
子任务1 插入记录
虽然 SELECT 语句的完整语法较复杂，但是其主要的子句可归纳如下：
SELECT [ ALL | DISTINCT ][ TOP n [ PERCENT ] [ WITH TIES ] ]select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
子任务1 插入记录
【任务分析】
设计人员在完成表的创建后，只是建立了表结构，还应该向表中添加数据，并进行查看、修改或删除数据记录。
【课堂任务】
掌握在SSMS中添加数据、查看数据、修改数据、删除数据等操作。
向表添加数据。
快速查看、修改和删除数据记录。
1．向表添加数据
向表添加数据时，不同数据类型的数据格式不同，因此应严格遵守它们各自的要求。添加的数据按输入顺序保存，数据记录的条数不限，只受数据库存储空间限制。
打开SSMS窗口，连接到本地的数据库引擎，在【对象资源管理器】窗格中展开【服务器】→【数据库】节点，单击前面的【+】按钮，展开该数据库，单击【表】节点前面的【+】按钮，显示该数据库下的所有表，用鼠标右键单击要操作的表，选择快捷菜单中的【编辑前1000行】命令，打开该表的数据编辑窗口，最后一条记录下面有一条所有字段都为NULL的记录，在此处添加新记录，记录添加后数据将自动保存在数据表中。
子任务2 查看记录
1. 查询表中所有字段
在SELECT语句中使用通配符“*”就可以返回FROM 子句内所有表和视图内的所有列，服务器会按照用户创建表或视图时候声明的列顺序来显示所有的列。
2. 查询指定的字段
基本语法：
SELECT select_list
FROM table_source
3. 设置列的别名
常用的设置字段别名的方法三种：
使用 ANSI的标准方式。
使用AS关键字。
使用SQL Server 2000支持的“=”。
子任务2 查看记录
select * from [Table] where ID in (select top (【N】+1) ID from [Table] where id <=【ID】 order by id desc
union
select top 【N】 ID from [Table] where id>【ID】 order by id )
order by ID
例如：有数据表 A
id name datet
1 123123 2015-08-04 11:19:32.770
3 qwerqwer 2015-08-04 11:19:34.710
4 qwerqfsd 2015-08-04 11:19:35.507
7 jhndf 2015-08-04 11:19:38.507
8 sdfsdfc 2015-08-04 11:19:39.720
1. 基于比较的查询
比较运算符的结果布尔数据类型，它有3种值：TRUE、FALSE及UNKNOWN。
2. 基于范围的查询
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
3.基于集合的查询
test_expression [ NOT ] IN
( expression [ ,...n ] )
4.基于字符匹配的查询
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
5.基于空值的查询
expression IS [ NOT ] NULL
6.基于多重条件的查询
在WHERE子句中，可以使用逻辑运算符AND和OR来连接多个条件，构成一个更加复杂的条件来查询数据。
子任务3 修改记录
快速查看、修改和删除数据记录
（1）修改数据记录。修改某字段的值，只需单击该字段，然后输入新的值即可。
（2）删除数据记录。删除某条数据记录，可选中该记录单击鼠标右键，选择快捷菜单中的【删除】命令，出现图5.4所示的提示框，如果确认删除，则单击【是】按钮，注意此处的删除是永久删除，将无法撤销所做的更改。
子任务4 删除记录
删除数据记录。删除某条数据记录，可选中该记录单击鼠标右键，选择快捷菜单中的【删除】命令，出现图5.4所示的提示框，如果确认删除，则单击【是】按钮，注意此处的删除是永久删除，将无法撤销所做的更改。
本章结束

展开更多......

收起↑