数据库应用技术考试知识点

第二章系统数据类型介绍p24创建用户自定义数据类型：sp_addtype type_name , phystype(length)|(precision,scale),null | not null | identity查看，重命名，删除用户定义的数据类型：sp_help type_name查看用户自定义数据类型的特征；sp_rename type_name给用户自定义数据类型重新命名；sp_droptype type_name删除一个已经定义且未被使用的用户自定义数据类型。定义局部变量： DECLAER local_variable data_type n设定局部变量的值，必须使用SELECT命令或者SET命令。其语法形式为： SET local_variable = expression 或者 SELECT local_variable = expression ,.n 全局变量：全局变量分为两类，一是与SQL Server连接有关的全局变量，如rowcount表示受最近一个语句影响的行数；二是关于系统内部信息有关的全局变量，如version表示SQL Server的版本号。SQL Server运算符的优先级别数学函数：字符串函数:日期函数：转换函数：系统函数：聚合函数：批和脚本：批处理：包含一个或多个T-SQL语句的组，它将一次性地发送到SQL Server中执行，用GO来通知SQL Server一批T-SQL语句的结束。脚本就是一系列顺序提交的批。流程控制：各语句的作用：BEGINEND用来设定一程序块 IFELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。 CASE语句为多分支语句 WHILECONTINUEBREAK循环语句WAITFOR语句用来暂时停止程序执行 GOTO语句用来改变程序执行的流程 RETURN语句用于结束当前程序(所在的批、存储过程和触发器)的执行，返回到上一个调用它的程序或其它程序。 PRINT语句的作用是在屏幕上显示用户信息。 RAISERROR语句的作用是将错误信息显示在屏幕上，同时也可以记录在日志中。 各语句的形式：IFELSE语句的语法形式： IF Boolean_expression sql_statement | statement_block ELSEsql_statement | statement_block BEGINEND语句的语法形式：BEGIN sql_statement| statement_block ENDCase语句：见p37WHILECONTINUEBREAK语法形式： WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE WAITFOR语句的语法形式为：WAITFOR DELAY time | TIME time RETURN语句的语法形式为：RETURN integer_expression GOTO和RAISERROR见p39-40第三章数据库的逻辑结构 从逻辑角度，SQL Server 2005将数据库组织成为各种数据库对象，如数据表、视图、索引、数据类型、存储过程、触发器等。（各对象的定义见p48）。数据库的物理结构 从物理角度，SQL Server 2005数据库以多种操作系统文件形式存储在计算机硬盘上，一般一个数据库被组织成数据文件和日志文件两种类型的文件。 主要数据文件(.mdf)，该文件包含数据库的启动信息，并用于存储数据 次要数据文件(.ndf)，它含有不能置于主要数据文件中的数据，可以有多个，并分布在不同磁盘上。 日志文件(.ldf)，记录了用户对数据库的所有操作。 文件组文件组是数据库中数据文件的逻辑组合。每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。数据文件的组织方式：数据文件由若干个大小为64KB的区组成，每个区由8个8KB大小的连续空间组成，这些连续空间被成为数据页。在数据页上，数据行紧接着页首按顺序存放。页尾有一个行偏移表，表中页上的每一行都有一个条目，每个条目记录那一行的第一个字节与页首的距离。SQL Server 2005有两种区，统一区和混合区。统一区属于单个数据库对象所有，区所有的数据页只能由拥有该区的对象使用；混合区最多可以由8个逻辑对象来使用。事务日志：事务日志是数据库中已经发生的一连串修改和操作的记录。SQL Server 2005包含两种类型的数据库：系统数据库和用户数据库。系统数据库存储有关数据库系统的信息，例如master、model、msdb、tempdb。master数据库记录SQL Server 2005的所有系统级信息，包括登录帐户和系统配置设置。model数据库为用户创建新的数据库提供模板。 msdb数据库是代理服务使用的数据库，代理程序调度警报作业以及记录操作员的操作时使用。 tempdb数据库保存所有的临时表和临时存储过程。创建用户数据库：CREATE DATABASE database_nameON PRIMARY (NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMTED,FILEGROWTH=grow_increment),nLOG ON (NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMTED,FILEGROWTH=grow_increment),n参数说明：参数说明database_name要建立的数据库名称PRIMARY在主文件组中指定文件。ON指定存储数据库数据部分的磁盘文件(数据文件)。LOG ON指定建立数据库的日志文件。NAME指定数据或日志文件的文件名称FILENAME指定文件的操作系统文件名和路径。os_file_name中的路径必须指定为SQL Server所安装服务器上的某个文件夹。SIZE指定数据或日志文件的大小。用户可以以MB为单位指定大小，也可以使用默认单位MB来指定大小。如果没有为主文件提供 size，则数据库引擎将使用 model 数据库中的主文件的大小。如果指定了辅助数据文件或日志文件，但未指定该文件的 size ，则数据库引擎将以 1 MB 作为该文件的大小。 。 MAXSIZE指定文件能够增长到的最大长度。默认单位为MB，用户也可以以MB来指定该长度。如果没有指定长度的话，文件将一直增长直到磁盘满为止。要建立的数据库大小单位为MB。FILEGROWTH指定文件的增长增量。该参数设置不能超过MAXSIZE参数。指定值的默认单位为MB，用户也可以以KB为单位进行指定，此外还可以使用百分比(%)。如果该参数没有指定的话，默认值为10，最小值为64KB。修改数据库：ALTER DATABASE databasename ADD file,n TO filegroup filegroupname | ADD log file ,n | REMOVE file logical_file_name with delete | MODIFY file | MODIFY name=new_databasename | ADD filegroup filegroup_name | REMOVE filegroup filegroup_name | MODIFY filegroup filegroup_name filegroup_property|name=new_filegroup_name查看数据库状态：sys.databases: 数据库和文件目录视图，可以查看有关数据库的基本信息。Microsoft SQL Server 实例中的每个数据库都对应一行 sys.databases_files：可以查看有关数据库文件的信息。每个存储在数据库本身中的数据库文件在表中占用一行。sys.master_files:可以查看数据库文件的基本信息和状态信息。master 数据库中的每个文件对应一行。增加用户数据库容量：ALTER DATABASE database_nameMODIFY FILE(NAME=file_name,SIZE=newsize)缩减用户数据库容量：P65DBCCSHRINK DATABASE(database_name|database_id|0,target_percent)WITH NO_INFOMSGS数据库快照的作用：1、维护历史数据以生成报表。 2、将查询实施在数据库的快照上，可以释放主体数据库上的资源。 3、使用快照将数据库恢复到生成快照时的状态比从备份还原快得多。 创建快照数据库语法格式CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name, FILENAME = os_file_name ) ,.n AS SNAPSHOT OF source_database_name数据库更名与删除：p69更名：exec sp_renamedb old_name,new_name 删除：drop database database_name第四章数据表中行和列的作用：每行代表一唯一的记录，每列代表记录中的一个域。创建数据库：CREATE TABLE database_name. owner .| owner. table_name( | column_name AS computed_column_expression| := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE 修改表结构：ALTER TABLE table_name ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL| ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column_name ,.n 插入、更新和删除表数据:查看表结构:使用系统存储过程sp_help查看表结构EXECUTE sp_help table_name数据插入:INSERT命令完成数据插入。 INSERT INTO table_name(column1,column2)values(value1,value2)更新表中现存记录中的数据:UPDATE语句可以更新表中现存记录中的数据UPDATE table_nameSET column1=modified_value1,column2=modified_values,WHERE column1=value1,column2=value2删除数据：DELETE语句可以从表中删除一行或多行记录。DELETE FROM table_nameWHERE column1=value1,column2=value2数据完整性：存储在数据库中数据的一致性和正确性。数据完整性分为：实体完整性、参照完整性、域完整性和用户定义完整性四种。约束：CHECK约束 限制输入到一列或多列的值的范围 DEFAULT约束 如果没有为列指定数据，系统将默认值赋给列 PRIMARY KEY约束 如果某列或多列的值能惟一标识表中的每一行，这样的列称为表的主键，通过它可以强制表的实体完整性。FOREIGN KEY约束 外键(Foreign Key)是用于建立和加强两个表(主表与从表)的一列或多列数据之间的链接，当数据添加、修改或删除时，通过参照完整性保证它们之间数据的一致性。 定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束 UNIQUE约束确保表中的两个数据行在非主键列中没有相同的列值约束的创建与删除：CHECK约束的创建ALTER TABLE table_nameADD CONSTRAINT constraint_name CHECK (logical_expression)Check约束的删除：DROP CONSTRAINT constraint_name创建和删除DEFAULT约束ALTER TABLE table_nameADD CONSTRAINT constraint_name DEFAULT constraint_expressionDROP CONSTRAINT constraint_name 创建和删除PRIMARY KEY约束ALTER TABLE table_nameADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED constraint_expression DROP CONSTRAINT constraint_name创建和删除FOREIGN KEY约束ALTER TABLE table_nameADD CONSTRAINT constraint_name FOREIGN KEY REFERENCES referenced_table_name (ref_column)DROP CONSTRAINT FOREIGN KEY constraint_name创建和删除UNIQUE约束ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE constraint_expressionCLUSTERED|NONCLUSTEREDALTER TABLE table_nameDROP CONSTRAINT UNIQUE constraint_name创建默认值和将其绑定到表上的某列CREATE DEFAULT default_nameAS constraint_expressionEXEC sp_bindefault default_name table_name.column_name,|user_datetype第五章SELECT语句的形式：SELECT select_listINTO new_table_nameFROM table_listWHERE search_conditionsGROUP BY group_by_listHAVING search_conditionsORDER BY order_list ASC|DESCCOMPUTE row_aggregate(column_name)BY column_name简单查询：SELECT ALL | DISTINCT * | table_name | view_name .* | column_name AS column_titleFROM table_name | view_name各个子句的作用：FROM子句：指定SELECT语句查询的一个或多个表，最多可以指定16个表，每一个表名用逗号分隔。即从指定的数据表table_name1,table_name2,.的记录中，检索(SELECT)出指定的列column_name1, column_name2,.n形成结果集。不过，FROM后面的表名在两个或者两个以上时，SELECT列表中应该采用table_name.column_name形式限定列所属的表。使用SELECTINTO语句可以在查询数据的基础上创建新的数据表。通常，可使用这种方法来创建临时表，以便在随后的开发过程中使用。SELECT column_name1, column_name2,.nINTO new_table FROM table_nameWhere子句：从整个表中选出满足指定条件的内容，这就要用到WHERE子句。SELECT column_name1, column_name2,.nFROM table_nameWHERE search_conditionORDER BY column_name1ASC | DESC, column_name2ASC | DESC, .ORDER BY是一个可选的子句，如果有ORDER BY子句，将按照排序列名column_name1, column_name2, .进行排序，其结果表还要按选项的值升序(ASC)或降序(DESC)排列。缺省时为查询结果按升序排列。使用GROUP BY子句可以按一定的条件对查询到的结果进行分组，再对每一组数据计算统计信息。SELECT column_name1, column_name2 ,.nFROM table_name WHERE search_conditionGROUP BY group_by_expressionHAVING search_conditionGROUP BY将查询结果按 (group_by_expression)进行分组，该属性列相等的记录为一个组。通常，在每组中通过聚合函数来计算一个或者多个列。如果GROUP带有HAVING，则只有满足search_condition的组才能输出。Compute子句：SELECT column_name1, column_name2,.nFROM table_name WHERE search_conditionORDER BY column_nameASC | DESC, . COMPUTE row_aggregate(column_name), row_aggregate (column_name)BY column_name ,column_name其中，row_aggregate表示行聚合函数，如AVG()，COUNT()，MAX()，MIN()，SUM() 。COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE子句在结果集内对指定列进行分类汇总。可在同一查询内指定COMPUTE BY和COMPUTE。内连接：内连接是用比较运算符比较两个表中列值，将两个表中满足连接条件的行组合起来作为结果，它是最常见的表连接形式。内连接分为：等值连接，在SELECT列表中使用星号(*)的和在结果集中显示冗余列数据的连接。 不等值连接，在连接条件中使用除等于运算符以外的其它比较运算符(、=、=、!、)，来比较被连接列的列值。 自然连接，对结果集的冗余列数据进行限制的连接。在连接条件中使用等号(=)运算符比较被连接列的列值，但它使用选择列表指定查询结果聚合中所包括的列，并删除连接表中的重复列。P109第六章视图的概念 视图是基于某个查询结果的一个虚拟表，只是用来查看数据的窗口而已。 视图与真正的表很类似，也是由一组命名的列和数据行所组成，其内容由查询所定义。但是视图并不是以一组数据的形式存储在数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基表中。 当基表中的数据发生变化时，从视图中查询出来的数据也随之改变。创建视图：CREATE VIEW view_name (column_name ,.n)WITH ENCRYPTION ASSELECT_statement WITH CHECK OPTION通过视图查询数据p131第七章索引的概念：索引是一个表中所包含的值的列表，它说明了表中包含各个值的行所在的存储位置。索引中数据的存储：在没有建立索引的表内，使用堆的集合的方法组织数据页。在堆集中，数据行不按任何顺序进行存储，数据页序列也没有任何特殊顺序。因此扫描这些数据堆集花费的时间肯定较长。在建有索引的表内，数据行基于索引的键值按顺序存放，将改善系统查询数据的速度。索引的分类:按照索引存储方式的不同，可以将索引分为聚集索引和非聚集索引。在聚集索引(Clustered Index)中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序，因为行是经过排序的，所以每个表中只能有一个聚集索引。非聚集索引(Nonclustered Index)并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序，索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。按照索引取值方式可以将索引分为唯一索引和非唯一索引。唯一索引和非唯一索引既可以是聚集索引，也可以是非聚集索引。唯一索引是指索引值必须是唯一的，不允许数据表中具有两行相同的索引值。创建PRIMARY KEY或UNIQUE，默认建立一个唯一索引。索引视图：为视图创建独特的聚集索引，从而让访问此类视图的查询性能得以极大的改善。创建索引：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_nameON table | view (columnASC | DESC ,.n ) WITH PAD_INDEX ,FILLFACTOR = fillfactor ,IGNORE_DUP_KEY ,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDBON filegroup索引的查看：通过系统视图sys.indexs可查看数据库中的索引信息，通过sys.index_columns可查看索引列信息。P157索引的更名p157sp_rename objname= object_name, newname= new_name ,objtype=object_type删除索引：DROP INDEX table_name.index_name ,n第八章存储过程：一组完成特定功能的T-SQL语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。创建不带参数的存储过程CREATE PROCEDURE procedure_nameAS sql_statementsprocedure_name为所创建的存储过程的名字；sql_statements为在存储过程中需要执行的数据库操作。创建带参数的存储过程：CREATE PROCEDURE procedure_nameparameter data_type =DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTINAS sql_ statements使用 ALTER PROCEDURE命令ALTER PROCEDURE procedure_nameparameter data_type=DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION | RECOMPILE,ENCRYTIONAS Sql_statement,n触发器的概念：触发器也是一种存储过程，一种在基表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。创建触发器：CREATE TRIGGER trigge_nameON table | viewFOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)andor UPDATE(column_name) sql_statesments修改触发器：ALTER TRIGGER trigge_name ON table|viewFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)and|or UPDATE(column name)sql_statesments第九章事务的概念：事务是由一系列的数据查询操作或更新操作构成的。从用户的观点来看，根据业务规则，这些操作是一个整体，不能分割，即要么所有的操作都顺利完成，要么一个也不要做。绝不能只完成了部分操作，而还有一些操作没有完成。事务中任何一个语句执行时出错，系统都会返回到事务开始前的状态。事务的(原子性、一致性、隔离性、持久性)4个特性一般简称为事务的ACID特性事务的管理：使用BEGIN TRANSACTION建立事务 BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description 使用COMMIT TRANSACTION标识事务结束 COMMIT TRANSACTION transaction_name | tran_name_variable 使用COMMIT WORK语句标识事务结束 COMMIT WORK功能与 COMMIT TRANSACTION 相同，但 COMMIT TRANSACTION 接受用户定义的事务名称。使用ROLLBACK TRANSACTION回滚到事务的指定点 ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 使用ROLLBACK WORK回滚到事务的起点 ROLLBACK WORK 此语句的功能与 ROLLBACK TRANSACTION 相同，但 ROLLBACK TRANSACTION 接受用户定义的事务名称。 嵌套事务时，ROLLBACK WORK 始终回滚到最远的 BEGIN TRANSACTION 语句，并将 TRANCOUNT 系统函数减为 0。事务模式：显式事务由用户在其中定义事务的启动和结束隐式事务隐式事务是指在当前事务提交或回滚后，自动启动新事务自动事务模式在自动事务模式下，每个Transact_SQL语句在成功执行完成后，都被自动提交；如果遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。事务日志的内容各个事务的开始标记、结束标记、所有更新操作，每个记录的内容包含： 事务标识(标明是哪个事务) 操作的类型(插入、删除或修改) 操作对象(记录内部标识) 更新前数据的旧值(对插入操作而言，此项为空值) 更新后数据的新值(对删除操作而言，此项为空值)游标的概念：游标是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。某些业务规则要求对结果集逐行执行操作，而不是对整个结果集执行操作。游标正是这样一种基于逐行操作结果集的方法，它对SELECT语句的查询结果集中的记录行逐行处理，而不是整个结果集作同一处理，并基于游标的当前位置，更新或删除表或视图中的行。游标的使用：声明或创建游标打开游标推进游标指针从游标的结果集中提取数据逐行处理操作游标指针所指向的行数据关闭和释放游标声明游标DECLARE cursor_name CURSORFOR select_statementFOR READ ONLY | UPDATE OF column_name_list, 打开游标OPEN crusor_name读取游标中的数据FETCH NEXT|PRIOR|FIRST|LASTFROM cursor_name INTO fetch_target_list删除数据DELETEFROM table_name | view_nameWHERE CURRENT OF cursor_name更新数据UPDATE table_name | view_nameSET table_name. | view.column_name1=expression1 | NULL | (select_statement) ,column_name2 = expression2 | NULL | (select_statement)WHERE CURRENT OF cursor_name关闭游标CLOSE cursor_name释放游标DEALLOCATE CURSOR cursor_name补充两章备份：备份是指在某种介质上（如磁盘、磁带等）存储数据库（或一部分）的拷贝。还原：还原是指利用数据库的备份，将数据库返回到备份时的状态。数据库完整备份：数据库完整备份是指对数据库内的所有对象都进行备份数据库差异备份：数据库差异备份只备份自从上次数据库全部备份后（注意：不是上一次差异备份后）数据库变动的部分。事务日志备份：事务日志备份（Transaction log backup）只备份数据库的事务处理记录文件及文件组备份：文件及文件组备份是针对单一数据库文件或者是文件夹做备份和恢复，它的好处是便利和具有弹性，而且在恢复时可以仅仅针对受损的数据库文件做恢复。SQL Server 2005有三种还原模型：简单还原模型完整还原模型大容量日志记录还原用户使用SQL Server时，需要经过两个安全性阶段：身份验证和权限认证。SQL Server有两种验证模式，即Windows验证模式和混合验证模式。用户权限分为系统权限和对象权限。备份数据库T-SQL语句的语法形式BACKUP DATABASE database_name | database_name_varTO ,.nWITH DESCMPTION=text | text_varable, DIFFERBNTIAL, INIT | NOINIT, MEDIANAME=media_name | media_name_variable, NAME=backup_set_name | backup_set_name_var 使用T-SQL语句还原数据库RESTORE DATABASEdatabase_name | database_name_varFROM,nWITH FILE=file_numberMEDIANAME=media_name | med1a_name_variable, MOVE logical_file_name TO operating_system_file_name,n, NORECOVERY | RECOVERY | STANDBY=undo_file_name, REPLACE, RESTART 身份验证阶段：用户在SQL Server上获得对任何数据库的访问权限之前，必须登录到SQL Server上，并且被认为是合法的。权限认证阶段：检验用户是否有访问服务器上数据库的权限，为此需要授予每个数据库中映射到用户登录的帐户访问权限，权限认证可以控制用户在数据库中进行的操作。系统权限由数据库管理员授予其他用户，是指数据库用户能够对数据库系统进行某种特定的操作的权力，例如创建一个数据表CREATE TABLE；对象权限由创建基本表、视图等数据库对象的用户授予其他用户，是指数据库用户在指定的数据库对象上进行某种特定的操作的权力。例如查询SELECT、插入INSERT、修改UPDATE和删除DELETE等操作。