SQLServer2000.doc

第 9 页 共 9 页SQL Server 2000第一章:SQL Server概述系统表数据库功能sysloginsmaster记录每一个能够登录到SQL Server上的登录账户sysmessagesmaster记录SQL Server能够返回的每一个系统错误或警告sysdatabasesmaster记录SQL Server上的每一个数据库sysusers所有数据库记录数据库中每一个Windows2000用户、windows2000 工作组、SQL Server用户或SQL Server角色sysobjects所有数据库记录数据库中的每一个对象(表)1、 调度执行：Watford语句允许开发者定义一个时间、或一个时间的间隔waitfor delay time | waifor time time 有例子下例在晚上 10:20 执行存储过程 update_all_stats。BEGIN WAITFOR TIME 22:20 EXECUTE update_all_statsEND第二章:创建和管理数据库1 、创建数据库：l 主数据文件：.mdf次数据文件：.ndf日志文件：.ldf2、更改数据库：在前面都加上alter database dbname更改哪一个数据库更改数据库文件：modify file 添加数据库文件：Add file 删除数据库文件：remove file在哪种文件上（主文件、日志文件）。3、收缩整个数据库：可以使用企业管理器或dbcc shrinkdatabase语句:dbcc shrinkdatabase(dbname,target_percent|notruncate|truncateonly)l target_percent是数据库收缩后的数据库文件中所要的剩余可用空间百分比。l NOTRUNCATE导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。 l TRUNCATEONLY导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用 TRUNCATEONLY 时，忽略 target_percentis。4、分离和附加数据库：l 附加数据库：EXEC sp_attach_db dbname = dbname, filename1=c:Program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf,当前主文件 filename2=c:Program FilesMicrosoft SQL ServerMSSQLDatapubs_log.ldf当前日志文件 多个文件相对filenamel 分离数据库:exec sp_detach_db dbname5、删除数据库: drop database dbname6、排序规则：collate Chinese_PRC_Stroke_BIN 简体中文(中华人民共和国)排序规则 l 首先通过sql server联机丛书中索引出windows 排序规则 找到相应的国家，对照下面的查出该国家的排序规则SELECT*FROM :fn_helpcollations()l 排序规则可以为数据库、表中的列、投影表达式（不太清楚）。第三章 创建数据类型和表1、current_timestamp当前的时间2、convert和cast是转换成另一种数据类型l convert(dbType,data,dataStyle)l case(data as dbType)它没有dataStyle注：convert和cast之间的最大区别是有没有dataStyle(数据的局线性)； dataStyle通过SQL Server联机丛书中索引中输入convert找到3、substring提取字符串4、用户定义的数据类型：确保数据类型的一致性。创建数据类型：sp_addtype typeName,varchar(10),null用户界面来定义Exec sp_addtype typeName,varchar,null 存储过程来定义删除数据类型：sp_droptype typeNameexec sp_droptype typeName5、查看当前数据库中所有数据类型：select * from systypes第四章 实现数据完整性1、check()必须加入（）2、primary key 唯一的。不许为空。Primary key 与foreign key reference l primary key 可以加desc（降序）| asc（升序）。在此之前必须加入clustered（聚合化数据）如：primary key clustered (au_id desc)l 想让外键跟着主键的变化而变化。在外键后加 on delete cascade | on update cascadeCascade子表跟着父表的变化而变化No action将会产生错误。并返回到父表中on delete no action | on update no actionl 组合主键:在primary key 加上 clusteredprimary key clustered (use_id,book_id)3、 nique唯一的。可以是空的4、 default默认值5、 查看表中所有的存储过程：exec sp_helpconstraint assembleKEY查看当前数据库中的存储过程：select * from sysconstraints第五章 Transact-SQL 介绍1、Transact-SQL语句的种类：l 数据定义语言（DDL，Data Definition Language）语句，它允许在数据库中创建对象。包括（create object_name|alter object_name|drop object_name）l 数据控制语言（DCL, Data Control Language）语句，它允许确定谁可以查看和修改数据。 用来改变与数据库用户或角色有关联的权限。（grant|deny|revoke）grant select on products to public | deny insert on products to public | update on | deletegrant同意 deny 不同意 revoke删除当前数据中所有的权限l 数据操作语言（DML, Data Manipulation Language）语句，它允许你查看和修改数据。包括（select insert update delete）2、标准标识符它们包括字母、符号（_、或#）和数字。标准标识符中不允许嵌入空格。使用标识符的规则包括：l 第一个字符必须是字母az或AZl 第一个字符后，标识符可以包括字母、数字或符号（、$、#或_）l 以符号开始的标识符名称具有特殊用途 以符号开始的标识符表示局部变量或参数 以数字标识#开始的标识符表示临时表或过程 以双数字标识#开始的标识符表示全局临时对象3、变量：l 是全局变量提供关于SQL Server的当前状态信息。而不能由用户创建。l 在下面我只介绍系统变量：变量值identity插入到数据库中的标识列的最后一个值rowcount受最后一条语句影响的行的数目4、函数：l 下面我只介绍：聚合函数聚合函数描述Avg(列)平均值Count(列)这一列的总行数Count(*) 总行数Max(列)最大值Min(列)最小值Sun(列)总和l 还有日期函数、数学函数等在微软SQL Server 2000P1345、流控制的语言：流控制工具包括：条件逻辑（IFELSE 和平共处CASE）、循环（只有WHILE,但是它经常与continue、break连用）、无条件转移（GOTO）和返回（return）。下面对这些流控制结构的总结:结构说明Beginend定义一个语句块Goto label在指定标签语句处继续进行处理Ifelse判断条件语句Waitfor为语句执行设置时间。最长为（24小时）While循环语句break退出while内层循环continue重新开始while循环 着重讲解case: 首先把case作为一个无标题的列 case有两种格式：如下： case columnName when 1 then awhen 5 then belse 其它end case when columnName=1 then awhen columnName=5 then belse 其它end 在不同的环境下选择不同的格式（方法） case只是一种显示效果 null 与 is nullu 两个表达式如果相等，返回null，否则返回其中一个表达式语法：case when columnName1=columnName2 then null else columnName1u 在该列中有空的值。在显示时不让它显示为空值。用最大值或最小值代替什么的此时the 后面加（），在括号里可以加上select语句来显示最大值或最小值第六章 使用Transact-SQL查询工具l 批处理即执行多条sql语句。第一条语句后加gol osql在dos下执行 ：osql/?查看所有内容第七章 检索数据使用select 语句检索数据。1、distinct：消除重复行 group by：分组 order by：排序(asc升)、(desc降)在默认情况下为升序2、过滤数据：3、通配符的类型：通配符说明%包含其中任意字符串_任意单个字符 指定范围或集合内的任何单个字符不在指定的范围或集合内的任意单个字符过滤的类型搜索条件比较运算符=、=、=、字符串比较Likenot Like逻辑运算符And(与)、or(或)、not(非)值的范围 Between、not between值的列表In、not in未知值Is null、is not null4、在Like搜索条件中使用通配符的示例表达式返回值Like br%每个以字母br开关的名称Like %een每个以字母een结尾的名称Like %en%每个包含字母en的名称Like _en每个以字母en结尾、包含3个字母的名称LikeCK每个以字母C或K形状的名称Like S-Ving每个以ing结尾并且以S到V中的任意字形状的四个字符名称LikeMC%每个以字母M开关、第二个字母不是字母C的名称第八章数据分组与汇总1、group by 分组（是对显示的所有行，而不是列）2、 having l 对列或表达式使用having子句为结果集中的组设置条件。l 相当于select 中的where语句 。它只能和group by 一起使用 3、 with rollup和with cubel 作为汇总（先分组再汇总）。l rollup与cube虽然都是汇总，再在显示上也有所区别。（这一点我也搞不懂！）。它只能和group by一起使用 注：先分组再汇总4、 compute 和compute by l 每一组一个汇总。 Compute by 它只能和order by 一起使用；compute group by | order byl compute和compute by 本质上的区别: compute只是单纯的做一个汇总。在最后开辟一行显示结果compute sum()|avg()后面接聚合函数 compute 聚合函数by 列此时是按列进行分组。只对每一组进行汇总，对于总体不进行汇总compute sum()|avg()后面接聚合函数 by 列按哪列进行分组 注：先分组|排序，再汇总第九章多表联接1、join on有五种联接方式 inner join on内联（默认） left join on左联 right join on右联 full join on完整外部联接 cross join 交叉联：条件 必须确保这两个表的行数是一样的 是1对多的关联：如下所示：Au_id123Bu_name大连青海上海Au_id123Bu_name大连青海上海Au_id123Bu_name大连青海上海2、合并多个结果集：union第十章 子查询1、select 语句中嵌套select语句第十一章 修改数据1、事务是SQL Server中最小的单元。一个工作单元要称为事务，它必须满足被称为ACID测试的四个标准（原子性、一致性、隔离性、持久性）。l 原子性：所有的数据的修改必须成功的完成，或者都不被永久的写入数据库。l 一致性：在每一个事务执行后，数据库必须保持一致的状态。在执行过程中，所有应用的规则都必须要保证数据的一致性。所有的约束条件都必须满足。所有的内部数据结构必须处于可接受的状态。l 隔离性：在事务完成之前，它对数据库的修改对其他事务必定不可见。在事务提交之前，其他事务应该只能看到在事务之前的状态下的数据。l 持久性：一旦一个事务完成，即使在系统失败的情况下，其对数据的修改也必定不会返回2、事务一些概念：l 事务有ACID四个特性 。l 事务的原子性、一致性、独立性及持久性 。l 事务的原子性是指一个事务要么全部执行,要么不执行 。l 事务的一致性是指事务的运行并不改变数据库中数据的一致性. 。l 事务的独立性是指两个以上的事务不会出现交错执行的状态。事务的持久性是指事务运行成功以后,就系统的更新是永久的.不会无缘无故的回滚。3、通常使用begin transaction启动事务。4、commit或rollback结束事务commit tran提交事务rollback取消事务（回滚）5、commit语句：该死语句确保事务内的所有修改都永久地成为数据库地一部分。它也将释放事务所使用的资源，例如锁。rollback语句：它可以取消对事务所做的全部修改。还可以释放事务所占用的资源。如果事务中出现了严重错误（例如死锁），SQL Server在处理过程中会自动执行事务的rollback语句。对于非严重错误（例如违反update语句中的check约束），则不会引起自动地引起自动地回滚。这是因为开发人员必须在每个语句后检查error，再发出一个显示的rollback语句,并且转到事务的最后。error：检查SQL是否有误6、可以嵌套事务：begin tran 1begin tran 2commit 2commit 17、插入的几种类型：可以是一个临时表#tab5改为#tab5 临时表 把已有表中的数据插入到另一个表中insert tabName1现有的表select * from tableName2已有数据的表 把查询结果放到一个新表中select columnName|* into tab5 from tab1 tab5是新建的表；tab1是已有的表 8、删除数据：delete table_Name|view_Name where search_conditions9、更新数据：update table_Name|view_Name set column_Name=expression|default|null where search_conditions第十二章全文索引和第十四章创建和维护索引一、全文索引：1、索引的优点l 索引用来快速地寻找那些具有特定值的记录l 如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录2、全文索引的注意事项：l SQL Server2000只有企业版的才有全文索引在安装时选择自定义安装（把全文索引选上）l SQL Server2005自带全文索引3、创建全文索引：l 首先为当前数据库启用全文检索：sp_fulltext_database enable|disablel 在表中上创建全文索引：sp_fulltext_table tableName, active, 全文目录名称, 单键列详细请参阅SQL Server联机丛书索引中输入sp_fulltext_table二、索引1、fillfactor是填充因子：l fillfactor用来优化包含聚集索引和非聚集索引的表中的insert和update语句的性能。l 在索引页已填满时，SQL Server就必须花时间拆分该索引页，以便为新行腾出空间。为此fillfactor值可以在叶级索引页上分配出一定百分比的可用空间，以减少页拆分。2、组合索引：多个列作为键值。l 在创建组合索引时，需要考虑以下事项和原则： 最多可以有16个列组合到一个组合索引中。总长度不能超过900字节 在组合索引中所有的列都必须来自同一个表，除非该索引在视图上创建 为了让查询优化使用组合索引，必须在查询的where子句中引用组合索引的第一列 在（column1,column2）上的索引与在（column2,column1）上的索引不同。3、创建索引：l 唯一索引：在创建唯一索引时，表中的数据不可以有重复的。Create unique index index_Name on table_Name(column_Name)当已存在这个索引名字时，可以在后面加上 with drop_existing(删除存在的索引)。l 聚集化索引：可以对表中的数据进行排序Create clustered index index_Name on table_Name(colum_Name asc|desc) 在它俩之间必须加空格l 非聚集化索引：在默认情况下是非聚集化索引Create unique index index_Name on table_Name(column_Name)Create index index_Name on table_Name(column_Name)可以有重复的l 如果加unique 但此列又有重复的：这时必须创建组合索引Create unique index index_Name on table_Name(column1,column2)在select 时where后只需一个条件就可以。(这两个其中一个)l Fillfactor：语法：Create index index_Name on table_Name(colunm) with fillfactor=2020个是开辟的页数当已存在这个索引名字时，可以在后面加上drop_existing(删除存在的索引)。4、删除索引：drop index table_Name.index_Name5、 查询当前数据库中所有的索引：select * from sysindexes查询当前表中的索引：sp_helpindex tableName6、 维护索引：l dbcc showcontig显示指定表的数据和索引的碎片的信息还有一些第十五章视图1、 创建视图：create view view_Name as select语句2、 修改视图：alter view view_Name as select语句3、 删除视图：drop view view_Name4、 给视图受予权限：grant select on products to public | deny insert on products to public | update on | delete根表受予权限是一样的5、查看数据库中所有的表（包括视图）：select * from sysobjects where name=table_Name|view_Name sp_helptext view_tab1-所定义的视图。当这个视图加密时。是查看不出他所定义的视图sp_depends tab1-|view_tab1-查看表或视图的相关信息6、加密：在创建+ with encryption对视图进行加密7、可以通过视图对表进行操作（添加、修改、删除）第16章 实现存储过程1、创建存储过程： create proc proc_Nameas SQL语句可以加入参数：2、存储过程可以进行嵌套（一个存储过程调用另一个存储过程）3、修改存储过程：alter proc proc_Name as SQL语句4、查看存储过程：sp_helptext pro_Name第17章用户定义函数1、 标量函数：就是在beginend块中使用系统自带的函数。返加一个值2、 多语句表值函数：返回一个表或视图。（对其进行insert 、update、delete）的一些操作3、 使用内嵌表值函数：返回一个表(有点像视图的作用)4、 删除函数：drop function function_name5、 修改:alter function function_name(参数) as 6、 创建函数：l 创建标题函数：create function function_name (参数)returns Nchar(20) asbeginend使用：select dbo.fn_date(getdate(),年,月,日)l 创建多语句表值函数：create function function_name (参数) returns 变量名 table(列名) asbeginend内嵌对表的操作语句l 创建内嵌表值函数：像视图一样create function fuction_name (参数)returns table asreturn( select customerid,companyname from customers where region=region)第十八章解发器1、 解发器是一种特殊的存储过程，它在遇到试图更改其所保护的表中的数据时自动执行。解发器与指定的表相关联。2、 有两种触发器：INSTEAD Of和after (for)instead of 用于视图而after没有限制第二十二章 管理事务与锁1、 事务：能确保把对多个数据修改作为一个单元来处理，即元子操作2、锁：锁防止更新数据时产生冲突，用户不能读取或修改其他用户正在更改的数据。重点 bcp语法：在do