第02章关系数据库系统2

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,北京信息科技大学,第,2,章,关系数据库基础,2.1,关系数据库系统概述,关系数据库的发展,SQL Server,简介,关系数据库标准语言,SQL,简介,SQL Server,的,Transact-SQL,介绍,关系数据库的三层模式结构,SQL Server,的数据库存储结构,建立数据库,SQL Server,的安全管理简介,SQL,标准中的架构,2,关系数据库的发展,CODASYL,于,1962,年发表的“信息代数”一文,E.F.Codd,从,1970,年起发表了一系列的论文,20,世纪,70,年代末的实验系统,System R,和,Ingres,从,20,世纪,8,0,年代逐步走向成熟,3,SQL Server,简介,20,世纪,80,年代：诞生。,1995,年：,SQL Server 6.0,1996,年：,SQL Server 6.5,1998,年：,SQL Server 7.0,2000,年：,SQL Server 2000,2005,年：,SQL Server 2005,2008,年：,SQL Server 2008,2012,年：,SQL Server 2012,4,DBMS,和解决方案,现在的,SQL Server,是关系数据库管理系统，但更是基于数据库的全面应用解决方案。,可以为数据库提供更安全可靠的存储功能,可以构建和管理用于业务的高可用和高性能的数据应用系统,提供了分布式数据库解决方案、商业智能和数据仓库解决方案、数据集成服务解决方案、以及超越关系数据库的应用解决方案（如,XML,数据管理、空间数据管理）等。,5,版本,有多个版本，可以适合不同规模和场合的应用,企业版,标准版,工作组版,Web,版,开发者版,学习版,移动版,6,关系数据库标准语言,SQL,简介,7,SQL,的历史（书,P34,）,SQL,的特点,SQL,是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动中的全部工作。,SQL,语言是一种高度非过程化的语言。,SQL,语言非常简洁。,SQL,语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。,SQL Server,的,Transact-SQL,介绍,SQL Server,使用的语言称作,Transact-SQL,，,简称,T-SQL,，它不仅包括,SQL,操作的内容，还有一般程序设计的能力,。,数据类型与变量说明,运算符与表达式,函数,全局变量,程序语句,8,SQL Server 2005/2008,支持的数据类型,9,分类,数据类型,备注,数,字,类型,整数,bigint, integer(,或,int), smallint, tinyint,字节长度分别为：,8,4,2,1,可与,IDENTITY,一起使用的唯一类型，,IDENTITY,通常用于自动生成唯一标识号,非整数,精确表示,decimal(p,s),或,numeric(p,s),P,是总的精度，,s,是小数位数,近似表示,float, real,货币类型,money, smallmoney,可以使用常用的货币符号，如￥,$,等。,字符类型,ASCII,char(n), varchar(n),varchar(max),需用单引号,Unicode,nchar(n), nvarchar(n), n,varchar(max),日期和时间类型,datetime, smalldatetime,需用单引号,二进制类型,binary, varbinary, varbinary(max),特殊类型,cursor, sql_variant,table, XML,1.,数据类型与变量说明,在,T-SQL,中用,DECLARE,语句说明内存,变量,，用,SELECT,语句或,SET,语句赋值。,变量说明的命令格式为：,DECLARE ,，, .,例,:,声明一个长度为,10,个字符的变量,id,declare id char 10,注意，变量名前必须有,前缀，以便与数据库字段名相区分。,10,2.,运算符与表达式,算术运算符,见书,P37,，表,2-2,位运算符,见书,P37,，表,2-3,比较运算符,见书,P38,，表,2-4,逻辑运算符,见书,P38,，表,2-5,字符串运算符,在,T-SQL,中可以用算术运算符的加号（,+,）做字符串的连接运算。如,abc+def,的结果为,abcdef,。,11,3.,函数,聚合函数,加密函数,游标函数,日期和时间函数,数学函数,元数据函数,行集函数,安全函数,字符串函数,类型转换函数,系统函数,系统统计函数,12,详细介绍请参阅,SQL Server,相关使用手册。,4.,全局变量,全局变量是,SQL Server,系统预定义的，用于返回有关配置设置和系统运行的信息。,全局变量以,做前缀。,例如,:SERVERNAME,和,FETCH_STATUS,13,局部变量？,5,程序语句,14,注释语句,/*,注释,*/ ,-,注释,赋值语句,给变量赋值,复合语句,可以定义一个语句块，可用于嵌套,条件语句,循环语句,执行过程的语句,主要用于执行存储过程,等待语句,挂起批处理，存储过程或事物的执行。,PRINT,语句,向客户输出信息。,RAISERROR,语句,根据出错类型，输出出错信息。,返回语句,无条件终止当前的查询等操作，返回调用程序,赋值语句,使用,SELECT,命令,语句格式一：,SELECT local_variable=expression ,local_variable=expression,语句格式二：,SELECT local_variable=expression ,local_variable=expression,FROM | ,使用,SET,命令的语句格式是：,SET local_variable=expression,15,直接对变量进行赋值,将查询结果赋值给变量,local_variable,是用,DECLARE,语句说明的局部变量,expression,是与变量数据类型相匹配的表达式。,变量的赋值,在,Transact-SQL,中不能像在一般的程序语言中一样使用,变量,=,变量值,来给变量赋值，必须使用,SELECT,或,SET,命令来设定变量的值，其语法如下：,SELECT ,局部变量表达式,SET ,局部变量表达式,例：给刚才定义的变量,id,并赋值,select id = 10010001,WAITFOR,语句,WAITFOR,语句挂起批处理、存储过程或事务的执行。,WAITFOR,语句通常用于事务控制，例如当已超过指定的时间间隔或到达一天中指定的时间再恢复程序的运行。,使程序延迟,10,秒执行：,WAITFOR DELAY 00:00:10,使程序等到,14,时,20,分再开始执行：,WAITFOR TIME 14:20,17,PRINT,语句,PRINT,语句用于向客户端输出或返回定义的信息。,PRINT,语句的基本格式如下：,PRINT msg_str | local_variable | string_expr,18,msg_str,是字符串常量,local_variable,可以是任何有效的字符数据类型变量（或者能够隐式转换为字符数据类型）,string_expr,是字符串表达式。,关系数据库的三层模式结构,关系数据库和,SQL,均支持三层模式结构。,基本表、视图、,SQL,存储,。,外部数据库、概念数据库、存储数据库,。,如何定义,基本表、视图、,SQL,存储,？,19,SQL Server,的数据库存储结构,每个,SQL Server,有,3,个基本系统数据库用于支持,SQL Server,的运行和管理，所有用户对象（包括基本表、视图、存储过程、触发器等）都建立在用户数据库中，在一个,SQL Server,上可以建立多个用户数据库。数据库的物理存储基本是由,SQL Server,自动管理的。,20,SQL Server,的数据库存储结构,21,master,数据库：,用于存储所有系统级信息，包括所有的其他数据库（如建立的用户数据库）的信息（包括数据库的设置、对应的操作系统文件名称和位置等）、所有数据库注册用户的信息以及系统配置设置等。,SQL Server,的数据库存储结构,22,tempdb,数据库,用于保存所有的临时表和临时存储过程，它还可以满足任何其它的临时存储要求。,tempdb,数据库是全局资源。,tempdb,数据库在,SQL Server,每次启动时都重新创建。,SQL Server,的数据库存储结构,23,model,数据库,model,数据库是一个模板数据库，当使用,CREATE DATABASE,命令建立新的数据库时，新数据库的第一部分总是通过复制,model,数据库中的内容创建，剩余部分由空页填充。,由于,SQL Server,每次启动时都要创建,tempdb,数据库，所以,model,数据库必须一直存在于,SQL Server,系统中。,建立用户数据库,用户数据存储在用户数据库中,建立用户数据库就是向操作系统申请存储空间专门用来存储用户数据库中的数据,用来存储数据库数据的操作系统文件可以分为,3,类：,主文件,次文件,事务日志文件,24,数据库存储文件的构成,Database,Data (file),.mdf or .ndf,Log (file),.ldf,存储数据库的启动信息和系统表，也可以用来存储用户数据。,每个数据库有且仅有一个主文件。,建议主文件使用,.mdf,扩展名。,主文件,次文件,事务日志文件,数据库存储文件的构成,Database,Data (file),.mdf or .ndf,Log (file),.ldf,保存所有主文件中容纳不下的数据。,使用多个独立磁盘驱动器上的次文件，还可以将一个数据库中的数据分布在多个物理磁盘上。,每个数据库有,0,或多个次文件。,建议次文件使用,.ndf,扩展名。,主文件,次文件,事务日志文件,数据库存储文件的构成,Database,Data (file),.mdf or .ndf,Log (file),.ldf,用来保存恢复数据库的日志信息。,每个数据库必须至少有一个事务日志文件（可以有多个）。,日志相当于数据库操作的“值班文件”，当数据库需要恢复时可以将数据库恢复到最新的、一致的状态。,建议日志文件使用,.ldf,扩展名。,主文件,次文件,事务日志文件,CREATE DATABASE,命令,建立数据库的命令是,CREATE DATABASE,。在,SQL Server,中建立数据库需要指定,数据库的名称,以及,有关存储文件的描述,（包括由,SQL Server,使用的逻辑名，建立在磁盘上的操作系统文件名（含路径），以及数据库规模等），另外还有文件组和日志等有关信息。常用命令格式如下：,CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,要建立的数据库的名称，数据库名称必须唯一，并且符合标识符的规则；,database_name,最多可以包含,128,个字符。,CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,指定用来存储数据库的操作系统文件（存储在磁盘上的数据文件），该关键字后跟以逗号分隔的,项列表。,CREATE DATABASE,命令,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,为数据库指定主文件。一个数据库只能有一个主文件，如果没有指定,PRIMARY,，那么,CREATE DATABASE,语句中列出的第一个文件将成为主文件。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,为定义的操作系统文件指定逻辑名称,logical_file_name,，该名称将由,SQL Server,管理和引用。,logical_file_name,在数据库中必须唯一，并且符合标识符的规则。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,指定要建立的操作系统文件名,os_file_name,，其中包含完整的路径名和文件名，并且不能指定压缩文件系统中的目录。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,指定所创建的操作系统文件的大小（,size,），,size,的单位可以是,KB,（千字节）、,MB,（兆字节）、,GB,（千兆字节）或,TB,（兆兆字节），默认是,MB,。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,指定定义的操作系统文件可以增长到的最大尺寸（,max_size,）。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,指定定义的操作系统文件的增长增量，该项设置的结果不能超过,MAXSIZE,设置。,用于定义对应的操作系统文件的属性。包括：,PRIMARY,(NAME =,logical_file_name,FILENAME = ,os_file_name, SIZE =,size, MAXSIZE = ,max_size,| UNLIMITED, FILEGROWTH =,growth_increment,),CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,用于定义用户文件组，,filegroup_name,是组名称，后续的,项列表给出该组的文件描述。利用文件组可以将指定的逻辑组件存储到指定的物理文件（在建立基本表的,CREATE TABLE,命令中有对文件组的引用）。（详见10.1）,CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON ,., ,CREATE DATABASE,命令,指定用来存储数据库日志的操作系统文件（日志文件），该关键字后跟以逗号分隔的,项列表。,如果没有指定,LOG ON,，将自动创建一个日志文件，该文件使用系统生成的名称，大小为数据库中所有数据文件大小总和的,25%,。,CREATE DATABASE,database_name,ON, ,.,FILEGROUP,filegroup_name, ,., ,LOG ON, ,., ,CREATE DATABASE,命令,建立数据库的例子,使用,CREATE DATABASE,命令在,SQL Server,中建立“,仓储订货,”数据库，向操作系统申请的数据主文件的初始大小为,10MB,，最大为,50MB,，增量为,5MB,；日志文件的初始大小为,5MB,，最大为,25MB,，增量为,5MB,。,CREATE DATABASE,仓储订货,ON,( NAME = order_dat,FILENAME = c:mssqldataorderdat.mdf,SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ),LOG ON,( NAME = order_log,FILENAME = d:mssqllogorderlog.ldf,SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ),注意：在建立数据库之前，为操作系统文件指定的目录路径必须存在。,SQL Server,的安全管理简介,用户分类,登录用户管理,数据库用户管理,权限管理,40,1.,用户分类,41,SQL Server,在安装时默认的系统管理员用户是,sa,，系统管理员负责整个系统的管理，他可以创建数据库、创建用户、指定数据库管理员（这里指某一个具体的数据库的管理者）等。,应用角度分类,2.,登录用户管理,SQL Server,又将用户分为,系统登录用户,和,数据库用户,。,一个用户必须首先能够登录到数据库管理系统，然后才能去使用具体的数据库。,系统登录用户,由系统管理员负责管理，,数据库用户,由数据库管理员负责管理。,42,系统角度分类,创建登录用户命令的基本格式,CREATE LOGIN login_name WITH PASSWORD=password ,43,login_name,指定新建立的登录用户名,WITH PASSWORD,用来为新建立的登录用户指定口令，这个口令通常是临时的，用户登录后可以自己更改口令。,例,2-2,：创建登录用户,orders_man,（初始口令是,123456,）。,CREATE LOGIN orders_man WITH PASSWORD=123456,44,3.,数据库用户管理,数据库用户的管理通常是由数据库管理员负责的，也可以由系统管理员代行数据库管理员的职责。,数据库用户管理包括,建立新的数据库用户,修改已有数据库用户,删除数据库用户。,45,建立新数据库用户的命令,CREATE USER,user_name,LOGIN,login_name, WITH DEFAULT_SCHEMA,=,schema_name,46,user_name,指定数据库用户名，如果没有指定,LOGIN,则默认自动映射到同名的登录用户名；如果希望数据库用户名和登录用户名不一致，可以使用,LOGIN login_name,指定登录用户名；通常情况下数据库用户名和登录用户名采用相同的名称。,WITH DEFAULT_SCHEMA,用来为新建立的数据库用户指定默认的模式。,例,2-3,：将登录用户,orders_man,指定为“仓储订货”数据库的用户。,需要切换到“仓储订货”数据库，然后执行如下命令：,CREATE USER orders_man,47,4.,权限管理,一般数据库用户初始时是没有任何权限的，为了对数据库进行操作和查询需要进一步获得授权。,48,SQL,标准中的架构,架构：是一个逻辑上的概念，是数据库中一组用户对象的逻辑集合。,通过架构（,schema,）管理对象。,49,图,2-3,某数据库的架构及对象列表,产品架构,订购架构,销售架构,定义架构的命令,CREATE SCHEMA schema_name AUTHORIZATION owner_name,架构通常由数据库管理员创建，使用短语,A,UTHORIZATION,可以指定架构的管理者（,如果不指定，,默认是创建者）。,50,例,2-4,：创建一个,warehouse,架构,CREATE SCHEMA warehouse,51,删除架构的命令,DROP SCHEMA schema_name,只有在架构为空、即架构中不包含任何对象时才可以删除架构。,52,默认架构,系统为用户预定义的默认架构是,dbo,，在数据库用户管理中可以为用户指定默认架构,。,53,使用架构,每一个数据库对象都在某个架构下，为此引用对象的基本格式是：,.,例如创建表的命令格式是（详见,5.1,）：,CREATE TABLE .(),例如查询的命令格式是（详见第,6,章）：,SELECT,FROM .,54,本章小结,关系数据库系统概述,关系数据库的发展,SQL Server,和,SQL,介绍,关系模型的相关概念和术语,关系模型的三个要素是：关系数据结构、关系操作集合和关系完整性约束,关系数据模型的完整性约束及其作用,关系代数,:,传统的集合运算和专门的关系运算,55,