数据库原理及应用课件：第5章 架构与基本表

1、数据库基础与实践技术（SQL Server 2008）第五章5.1 架构5.2 基本表5.3 分区表第5章 架构与基本表2/385.1.1 创建架构架构（schema，也称为模式）是数据库下的一个逻辑命名空间，是一个数据库对象的容器。架构对象的类型包括：基本表、视图、触发器等。一个数据库可包含一个或多个架构。在同一个数据库中，架构名必须唯一。3/38CREATE SCHEMA schema_name_clause .n := schema_name | schema_name AUTHORIZATION owner_name := table_definition | view_definit

2、ion | grant_statement revoke_statement | deny_statement 4/385.1.1 创建架构【例1】为用户“U1”定义一个架构，架构名为“Sales”。CREATE SCHEMA Sales AUTHORIZATION U15/385.1.1 创建架构【例2】创建由U1拥有、包含Test表的架构Common，同时授予U2对Test表有SELECT权，授予U3不能对Test表有删除权限。CREATE SCHEMA Common AUTHORIZATION U1 CREATE TABLE Test ( C1 int primary key, C2 c

3、har(4) ) GRANT SELECT TO U2 DENY DELETE TO U3;6/385.1.1 创建架构5.1.2 在架构间传输对象在架构之间传输对象就是更改对象所属的架构。ALTER SCHEMA schema_name TRANSFER securable_nameschema_name：架构对象将移入其中的架构名。securable_name：被移出架构的对象名。仅用于在同一数据库中对象的移动。7/38【例3】将Test表从Common架构传输到Special架构中。ALTER SCHEMA Special TRANSFER Common.Test8/385.1.2 在架

4、构间传输对象5.1.3 删除架构 DROP SCHEMA schema_name【说明】不能删除包含对象的架构。【例4】删除Special架构，假设该架构中包含Test表。先移除架构中的对象 ALTER schema common transfer Special.Test; 然后删除架构 DROP SCHEMA Special; 9/385.1 架构5.2 基本表5.3 分区表第5章 架构与基本表10/385.2 基本表（table）基本表是包含数据库中所有数据的数据库对象。是行的集合，或者说是元组的集合。设计数据库的关键任务就是设计数据库中所需要的表。包括以下主要特征：表要包含的列数每列中

5、数据的类型和长度哪些列是主键，哪些列是外键。11/385.2.1 SQL Server数据类型 SQL Server提供系统数据类型，定义了可与SQL Server一起使用的所有数据类型；用户还可以使用Transact-SQL或.NET框架定义自己的数据类型，它是系统提供的数据类型的别名。12/38系统数据类型13/381.数值类型 2.字符串类型3.日期时间类型4.货币类型1.数值类型14/38精确数值类型说明存储空间bigint存储从263 (9,223,372,036,854,775,808)到263-1 (9,223,372,036,854,775,807) 范围的整数8字节int存储

6、从231 (2,147,483,648 ) 到 231-1 ( 2,147,483,647 ) 范围的整数。4字节smallint存储从215 (32,768 ) 到 215-1 (32,767 ) 范围的整数2字节tinyint存储从 0255 之间的整数。1字节15/38bit存储1或0，相当于其它语言的逻辑数据类型。利用SSMS图形方式插入bit类型数据用“true”或“false”。SQL语句方式，上述值都可以用。1字节numeric(p,s)或decimal(p,s)定点精度和小数位数。使用最大精度时，有效值从 1038 +1 到 1038 -1。p为精度，指定小数点左边和右边可以存

7、储的十进制数字的最大个数。精度必须是从1到最大精度之间的值。最大精度为38。s为小数位数，指定小数点右边可以存储的十进制数字的最大个数。s的默认值为0。0 = s = p最多17字节1.数值类型1.数值类型【例5】decimal（10，5），表示共有10位数字，其中小数5位。 在上述定义下，表示56.342689时，实际存储值为？16/3817/38近似数值类型说明存储空间float(n)存储从-1.79E + 308至-2.23E 308、0以及2.23E308至1.79E + 308范围的浮点数。n有两个值，如果指定的n在124之间，则使用24，占用4字节空间，相当于real类型；如果指定

8、的n在2553之间，则使用53，占用8字节空间。若省略(n)，则默认为53。4字节或8字节real存储从3.40E + 38到3.40E + 38范围的浮点型数4字节1.数值类型2.字符串类型18/38普通编码字符串类型统一字符编码字符串类型在使用字符数据类型时，需要在其前后加上英文单引号或者双引号。普通编码字符串类型普通编码字符串类型说明存储空间char(n)固定长度的普通编码字符串类型，n表示字符串的最大长度，取值范围为18000 n个字节。当实际字符串所需空间小于n时，系统自动在后边补空格varchar(n)可变长度的字符串类型，n表示字符串的最大长度，取值范围为18000字符数2字节额

9、外开销text最多可存储231-1 (2,147,483,647) 个字符每个字符1个字节varchar(max)最多可存储231-1个字符字符数2字节额外开销19/3420/38【说明】如果在使用char(n)或varchar(n)类型时未指定n，则默认长度为1。如果在使用CAST和CONVERT函数时未指定n，则默认长度为30。普通编码字符串类型统一字符编码字符串类型统一编码字符串类型说明存储空间nchar(n)固定长度的统一编码字符串类型，n表示字符串的最大长度，取值范围为140002n字节。当实际字符串所需空间小于2n时，系统自动在后边补空格nvarchar(n)可变长度的统一编码字符

10、串类型，n表示字符串的最大长度，取值范围为140002*字符数2字节额外开销ntext最多可存储230-1 (1,073,741,823) 个统一字符编码的字符每个字符2个字节nvarchar(max)最多可存储230-1个统一字符编码的字符2*字符数2字节额外开销21/343.日期时间类型日期时间类型说明存储空间date定义一个日期，范围为 到 。字符长度10位，默认格式为：YYYY-MM-DD。YYYY表示4位年份数字，范围从0001到9999；MM表示2位月份数字，范围从01到12；DD表示2位日的数字，范围从 01 到 31（最大值取决于具体月份）3字节time(n)定义一天中的某个时

11、间，该时间基于24小时制。默认格式为：hh:mm:ss.nnnnnnn，范围为00:00:00.0000000到23:59:59.9999999。精确到100纳秒。n为秒的小数位数，取值范围是0到7的整数。默认秒的小数位数是7(100ns)35字节datetime定义一个采用24小时制并带有秒的小数部分的日期和时间，范围为到-，时间范围是00:00:00到23:59:59.997。默认格式为：YYYY-MM-DD hh:mm:ss.nnn，n为数字，表示秒的小数部分（精确到0.00333 秒）8字节22/34日期时间类型说明存储空间smalldatetime定义一个采用24小时制并且秒始终为零

12、(:00)的日期和时间，范围为到。默认格式为：YYYY-MM-DD hh:mm:00。精确到1分钟4字节datetime2定义一个结合了24小时制时间的日期。可将该类型看成是datetime类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。默认格式是：YYYY-MM-DD hh:mm:ss.nnnnnnn，n为数字，表示秒的小数位数（最多精确到100 纳秒），默认精度是7位小数。该类型的字符串长度最少19 位（YYYY-MM-DD hh:mm:ss），最多27位(YYYY-MM-DD hh:mm:ss.0000000)68字节23/343.日期时间类型当插入数据或者在

13、其他地方使用Datetime类型时，需要用单引号把它括起来。在字符串中，以斜线(/)、连字符(-)或句号(.)作为分隔符来指定年月日。年可用2位或4位表示，月和日可用1位或2位表示。24/383.日期时间类型25/38可以使用如下命令改变日期的顺序： Set dateformat format参数可以是mdy,ymd,dmy,ydm,myd,dym。【例6】Set dateformat dym 那么，输入07/10/09是什么日期？3.日期时间类型4.货币类型货币类型说明存储空间money存储922,337,203,685,477.5808到922,337,203,685,477.5807范围

14、的数值，精确到小数点后4位8字节smallmoney存储-214,748.3648 到214,748.3647范围的数值，精确到小数点后4位4字节26/34【说明】当为money或smallmoney的类型输入数据时，必须在有效位置前面加一个货币单位符号$，且如果货币值为负数，需要在$后面加上负号。 56.342689$15000.321.23E-32014-09-28true，false优 良 89March 1 98SQL Server2008系统数据类型用户定义数据类型28/38CREATE TYPE schema_name. type_name FROM base_type ( pre

15、cision , scale ) NULL | NOT NULL Exec sp_addtype typename, basetype,nulltype【例7】创建一个名为telephone的数据类型，其相应的系统数据类型为：char(8)，不允许空 CREATE TYPE telephone FROM CHAR(8) NOT NULL【例8】exec sp_addtype zipcode, char(9),null29/38用户定义数据类型30/38SQL Server中，每个数据库最多可以包含20亿个表。每个表可以定义至多1024个属性，每行最多包含8060个字节。表中的行数及总大小仅受可

16、用存储空间的限制。同一个表中不允许有相同的属性名。5.2.2 基本表管理1.创建基本表2.修改基本表3.删除基本表可以使用T-SQL语句实现，也可以在SSMS中用图形化方法实现。31/385.2.2 基本表管理5.2.2 基本表管理CREATE TABLE ( ， ， )；1. 创建基本表1.创建基本表常用完整性约束1.主码约束：PRIMARY KEY2.参照完整性约束:Foreign KeyReferences3.唯一性约束：UNIQUE4.非空值约束：NOT NULL5.缺省值约束：DEFAULT 6.检查约束：Check5.2.2 基本表管理CREATE TABLE Student (S

17、no CHAR(20) PRIMARY KEY ， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage TINYINT， Sdept CHAR(16)； 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。5.2.2 基本表管理建立一个“课程”表Course，它由课程号Cno、课程名字Cname、先行课号Cpno和学分Ccredit组成。其中Cno是主码，Cpno参照Cno取值。CREATE TABLE Course( Cno CHAR(1

18、4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, Foreign Key Cpno References Course (Cno); 5.2.2 基本表管理CREATE TABLE SC( Sno CHAR(20) , Cno CHAR(14) , Grade INT DEFAULT 0, Constraint SCPK Primary key (Sno, Cno),Foreign Key(Sno) Refrences Student(Sno),Foreign Key(Cno) References Course(C

19、no),Check(Grade=0 AND Grade=100)建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成(0到100分取值)，其中(Sno, Cno)为主码。5.2.2 基本表管理约束名37/38定义计算属性：column_name as computed_column_expressionpersisted计算属性是由同一表中的其它属性通过表达式计算得到的。表达式可以是非计算属性的属性名、常量、函数、变量，也可以是一个或多个运算符连接的上述元素的任意组合。表达式不能是子查询，也不能包含用户定义数据类型。1)创建有计算列的表示例：创建有计算列的表【例9】

20、创建一个带计算列的表，表中包含课程的课程号，该课程的总成绩和学习该课程的人数，以及平均成绩。Create table sum_avg(课程号 char(3), 总成绩 real not null, 人数 int not null, 平均成绩 as 总成绩/人数)38/3839/38一个表只能有一个使用 IDENTITY 属性定义的列，且必须通过使用 decimal、int、numeric、smallint、bigint 或 tinyint 数据类型来定义该列。必须同时指定种子和增量值。两者都未指定时，二者的默认值均为1。标识符列不能允许为空值，也不能包含 DEFAULT 定义或对象。标识列的值

21、是系统自动生成的。2)创建包含标识列的表如果确实要为标识列提供值，则必须将表的IDENTITY_INSERT属性设置为ON（默认时该属性的值为OFF）。SET IDENTITY_INSERT database_name. schema_name . table ON | OFF 40/382)创建包含标识列的表示例：创建包含标识列的表CREATE TABLE IDTable ( SID INT IDENTITY(100,1) NOT NULL, Name VARCHAR(20)41/38练习42/38class_info表结构如下，请创建该表。属性名数据类型长度是否为空默认值说明班级编号int

22、not主码，标识列，从100开始，增量为1班级名称nchar8not无入学年份smalldatetimeyes无所属系别nchar12yes无专业编号intyes无 外码，参照专业表专业号取值人数smallintno500人数502. 修改表结构使用ALTER TABLE语句实现。ALTER TABLE语句可以对已定义的表进行添加/删除列；修改列定义；添加/删除约束。43/3844/382. 修改表结构ALTER TABLE with nocheck ADD |完整性约束 DROP |column ALTER column ；45/38【说明】alter table 语句中，一次只能包含一条a

23、lter column，add，drop子句中的一条。alter column子句一次只能修改一个列的属性，add和drop子句则可以操作多个。add子句增加的约束只能是表级约束。删除属性列时，必须先删除该列上的约束条件或索引。with nocheck 表示不检查当前数据是否违反修改后的类型或约束。2. 修改表结构【例10】为SC表添加“修课类别”列，此列的定义为：Type NCHAR(1)，允许空。ALTER TABLE SC ADD Type NCHAR(1) NULL【例11】将新添加的Type列的数据类型改为NCHAR(2)。ALTER TABLE SC ALTER COLUMN Ty

24、pe NCHAR(2)46/382. 修改表结构47/38【例12】向课程表添加“任课教师”和“是否必修”两列，要求类型分别为字符类型和布尔类型，其中是否必修初值为T(必修）。 Alter table course add Cteacher varchar(16), Bixiu bit default True2. 修改表结构【例13】删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname)；2.修改表结构【例14】ALTER TABLE tb2 WITH NOCHECKADD CONSTRAINT tb2_checkCHECK(tb1_te

25、l LIKE 13,5,8,90-90-90-90-90-90-90-90-90-9) 3. 删除表DROP TABLE , 【注意】：如果被删除的表中有其他表对它的外键引用约束，则必须先删除外键所在的表，然后再删除被引用的表。49/385.1 架构5.2 基本表5.3 分区表第5章 架构与基本表50/385.3 分区表分区表是按照某种标准将表中的数据按水平方式划分成不同的子集，存储在一个或多个文件组中。合理的使用分区会在很大程度上提高数据库的性能。51/38比较适于进行分区的情况表中数据量大；该表包含（或将包含）以多种不同方式使用的大量数据。数据是分段的，比如数据以年为分隔。对表的常规维护操

26、作只针对表的一个数据子集。52/38分区表特点分区表是从物理上将一个大表分成几个小表，但从逻辑上来看，还是一个大表。对用户而言，所面对的依然是一个大表，用户只要将记录插入到大表中就可以了。53/38创建分区表步骤1创建分区函数。告诉SQL Server以什么方式对表进行分区。2创建分区方案。告诉SQL Server将已分区的数据放在哪个文件组中。3使用分区方案创建分区表。【说明】在创建分区表之前，最好先创建数据库文件组。54/381. 创建分区函数CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

27、AS RANGE LEFT | RIGHT FOR VALUES ( boundary_value , n )55/38示例【例15】在 int 列上创建左侧分区函数。CREATE PARTITION FUNCTION myRangePF1(int)AS RANGE LEFT FOR VALUES (1,100,1000);上述分区函数将表分为四个分区：56/38示例【例16】在int列上创建右侧分区函数。CREATE PARTITION FUNCTION myRangePF2(int)AS RANGE RIGHT FOR VALUES (1, 100, 1000)57/38示例【例17】在d

28、ate列上创建右侧分区函数。将表分成12个分区，每个分区对应一个月的值CREATE PARTITION FUNCTION myDateRangePF1(date)AS RANGE RIGHT FOR VALUES ( 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201);58/382. 创建分区方案CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name ALL TO ( file_group_name | PRIMARY ,.n ) ; 59/38