SQL学习日记-依然是基础
1 sql.server2005零基础学：
（1） 基础知识
a、数据与信息：数据（Data）是描述事物的符号记录，它具有多种表现形式，可以是文字、图形、图像、声音、语言等。数据是经过组织的比特的集合，而信息是具有特定释义和意义的数据。
b、数据库管理系统的基本功能：定义数据、处理数据、数据库运行管理、数据组织、存储和管理、数据库的建立和维护。
c、数据库的类型：层次型数据库、网络型数据库、关系型数据库。
d、数据定义语言（DDL）：SQL Server 2005数据库提供数据定义语言（Data Definition Language DDL），用户可以使用DDL对数据库的结构进行描述，DDL还包括安全保密定义，如口令、级别、存取权限等。这些定义存储在数据字典中，是数据库管理系统运行的基本依据。数据定义语言涉及的T-SQL语言中的CREATE TABLE、CREATE VIEW、CREATE INDEX等语句。
e、数据操纵语言（DML）：SQL Server 2005数据库提供数据操纵语言（Data Manipulation Language DML），实现对数据库中数据的基本操作，如插入、修改和删除。数据操纵语言涉及的T-SQL语言中的INSERT、UPDATE、DELETE等语句。
f、数据控制语言（DCL）：数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句。SQL Server 2005数据库中，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。在SQL Server 2005中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。
g、数据查询语言（DQL）：数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块，即“SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>”的格式，SQL Server 2005数据库对标准的数据查询语言进行了功能上的扩展。
（2） 语句示例
a、GROUP BY：将查询的结果做统计。
工作：使用WHERE子句，可以设定不想要做统计的资料，而使用HAVING子句可以筛选已经统计之后的字段。
其他说明：使用WHERE子句，可以设定不想要做统计的资料
使用HAVING子句可以筛选已经统计之后的字段。
b、Select：以下只给出使用示例
工作：使用WHERE子句，可以设定不想要做统计的资料，而使用HAVING子句可以筛选已经统计之后的字段。
其他说明：复制表结构：select * into b from a where 1<>1。
c、exec：执行一个存储过程，或者执行一个动态批次。批次是一个内容为SQL语句的字符串。
工作：exec('select '+@sqlstr+' from 表名')。
其他说明：在exec的括号里，只允许是字符串变量、字符串常量存在。不允许在这里调用函数或者使用case表达式，基本的方法是将语句保存到一个变量里。EXEC也没有输出参数。缺省情况下，exec将返回该次查询的输出给调用者。如果希望将该结果保存到变量里，那就必须使用Insert EXEC语法，并从表里从新读取该数据，然后储存到目标变量里。
d、视图：视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
创建：CREATE VIEW [vSKL] AS
SELECT ID,Name FROM CMS_KeywordInfo_KeyWordManager。
更新：UPDATE [vSKL] set Sequence=ID where id=26
撤销：Drop VIEW [vSKL]
e、存储过程：已预编译为一个可执行过程的一个或多个 SQL 语句。其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。
创建：CREATE PROCEDURE [dbo].[SP_GetTaxisList]
@strOrgCodes AS varchar(max)
AS…
修改：ALTER PROCEDURE [dbo].[SP_GetTaxisList]
@strOrgCodes AS varchar(max)
AS…
调用：EXEC dbo.[Sales by Year] @ strOrgCodes =’stw
其他说明：优点：提高性能。降低网络开销。提供更好的一致性。提高准确性。增加功能。
f、函数：用户定义的函数 (UDF) 是准备好的代码片段，它可以接受参数，处理逻辑，然后返回某些数据。有两种主要的 UDF 类型：返回标量值的 UDF 和返回表值的 UDF。
创建：CREATE FUNCTION [dbo].[GetDeptPath]
(@tableId int)
RETURNS TABLE
AS…
修改：ALTER FUNCTION [dbo].[GetDeptPath]
(@tableId int)
RETURNS TABLE
AS…
调用：select * from GetDeptPath(14)
g、触发器：触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行，如在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。
创建：CREATE TRIGGER trgAddKeyWords
ON dbo.CMS_KeywordInfo_KeyWordManager
FOR INSERT--、UPDATE 或 DELETE
AS
select * from dbo.GetDeptPath(14)
取代：CREATE TRIGGER trgAddKeyWords
ON dbo.CMS_KeywordInfo_KeyWordManager
instead of INSERT --、UPDATE 或 DELETE
AS
select * from dbo.GetDeptPath(14)
调用：insert into CMS_KeywordInfo_KeyWordManager
select Name, DeptID, Type, DeptName, IsEnable, CreateTime, ClickCount, Sequence, SiteCode, OLDID from CMS_KeywordInfo_KeyWordManager where id=26。
删除：drop TRIGGER trgAddKeyWords
其他说明：重命名触发器：exec sp_rename 原名称, 新名称。
查看数据库中所有的触发器：select * from sysobjects where xtype='TR'
查看触发器内容：exec sp_helptext trgAddKeyWords
禁用：alter table CMS_KeywordInfo_KeyWordManager disable trigger trgAddKeyWords
启用：alter table CMS_KeywordInfo_KeyWordManager enable trigger trgAddKeyWords
取代：实际上并没有执行插入操作，是用触发器中的sql取代了对原表的操作。
h、事务和锁：事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除，在存储过程或函数相应位置加入以下标识句柄即可。
开始：begin transaction。
回滚：rollback transaction
提交：commit transaction
其他说明：事务的特性（1）原子性：事务必须是完全成功或完全失败；（2）一致性：事务必须强制招待所有数据一致性规则。（3）隔离性：所有修改数据的事务都必须彼此隔离开来。（4）永久性：事务所做的更改是永久而稳固的。
事务的隔离级别（1）提交读：是SQLServer的默认模式。如果事务已提交，则该级别允许读取数据。（2）未提交读：是最低的限制的隔离级别。这事务结束前可更改数据以及删除和添加行。（3）可重复读：进行锁定，这样将只能添加行而不能更新数据。（4）可串行读：整个数据集将被锁定， 其他用户将不能添加或更新。
设置事务的隔离级别： Set Transction Isolation Level {Read Committed|Read Uncommitted|Repeatable Read|Serializable}。
死锁概念：在多用户环境下，当不同用户分别锁定一个资源之后，双方都等待对方释放它所锁定的资源时，就产生一个锁定请求环，从而出现死锁现象。事务会对访问或更改的数据加锁。可以通过强制任务释放锁：Set deadlock_priority{low|normal}
f、索引和查询优化：分为聚集索引和非聚集索引。将表中的数据有序地组织起来的索引称为聚集索引（通常为常用分类如PID），一个表只有一个聚集索引，表上其他的索引都是非聚集索引（可以为ID），聚集索引将表内的数据进行有序的组织，并不是指磁盘上数据页内数据的物理顺序，也不是指数据页在磁盘上的物理顺序，而是数据页间逻辑上以树型结构链接起来。
（1）非聚集索引是对聚集索引的索引；
（2）索引组织表和堆表包含的数据行是一样的，只是组织形式不同而已；
（3）非聚集索引的叶子节点行存储的是索引列和书签。
（4）聚集索引的叶子节点行就是表中的数据行；
（5）无论是聚集索引还是非聚集索引的叶子节点上都有一个指向上下页的指针。
（6）不要在一个表上建立太多的索引，索引的维护会影响DML的效率。
索引的使用：
（1）WHERE条件中不该将索引列放在表达式中、不能用Like的头匹配'%aa'、不用<>、not in/exists/like、is null、is not null
（2）构造索引查找，避免索引扫描
（3）尽量使用等值运算符 c1>=2 优于 c1>1
（4）表达式的顺序：将过滤性最强的表达式放在靠近WHERE的位置，因为在一个WHERE子句中只有一个表达式是可以使用索引的，其他的表达式都是基于索引查找结果集的过滤，所以应尽量保证有效索引的使用。
（5）删除SOL默认聚集索引：通常新建数据表时会将主键设置为聚集索引，在小数量级时影响不大，如果达到百万以上时就需要重新设置聚集索引，可以在SQL Management中直接删除现有聚集索引。
g、全文搜索：每个表只允许创建一个全文索引；按装full text search全文索引服务并启动；只有有唯一索引栏的表才能建立全文索引，并且全文索引建立好之后就不能改变表了，如要改变表就必须得取出索引，然后再装入。。
测试查询
SELECT * FROM userTB
--WHERE CONTAINS(address,'changpin')
WHERE CONTAINS(address,'"aaa*"')
--WHERE CONTAINS(address,'昌平')
创建索引
CREATE UNIQUE INDEX ix_usertb_id ON usertb(id)。
对表设置全文索引应用如下步骤：
1.选择要建立全文索引的表，然后选择： Full-Text Index Table ->Define Full-Text Indexing on a table
2.系统会启动Sql Server Full-Text Index 向导。
3.选择一个唯一索引。
4.选择一个要建立索引的字段。
5.选择catalog.
6.选择更新索引计划（由于全文索引和普通索引表不同，不能自动更新，所以得加一个
7.Finish
8.选择在数据库Full-text catalogs中里的新建立的catalog，然后运行Start Population,Full Population 就可以了。
h、游标：是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。Transact-SQL 游标主要用于存储过程、触发器和 Transact-SQL 脚本中，它们使结果集的内容可用于其他 Transact-SQL 语句。
定义游标并填充：declare cursor1 cursor for select * from table1。
打开游标：open cursor1
游标下移取值：fetch next from cursor1 into @id,@name
是否成功获取：while @@fetch_status=0
游标继续下移：fetch next from cursor1 into @id,@name
关闭游标：close cursor1
撤销游标：deallocate cursor1
在存储过程或触发器中使用 Transact-SQL 游标的典型过程为：
1.声明 Transact-SQL 变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。
2.使用 DECLARE CURSOR 语句将 Transact-SQL 游标与 SELECT 语句相关联。另外，DECLARE CURSOR 语句还定义游标的特性，例如游标名称以及游标是只读还是只进。
3.使用 OPEN 语句执行 SELECT 语句并填充游标。
4.使用 FETCH INTO 语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 Transact-SQL 语句可以引用那些变量来访问提取的数据值。Transact-SQL 游标不支持提取行块。
5.使用 CLOSE 语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个 OPEN 语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE 语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用 DECLARE 语句来重新生成游标。
（3） 重难点分析
a、分页：见文档。
（4） 涉及到的知识点
a、 PERCENT关键字：设定前面几个百分比的记录，SELECT TOP 1 PERCENT ID,Title FROM CMS_InfoPublish_Content ORDER BY ID。
b、 日期格式：前后加#号，譬如WHERE 日期 = #
c、 通配符：
%：表示一个或多个字符。
[x-y]：指定字符范围。
[^x-y]：指定排除的字符范围。
_：一个字符的通配符。
d、 使用除法：要小心，当除号两边的数都是整数时，两个数相除的结果以整数显示，结果会将相除的小数部分去掉，保留下整数部分，例如：SELECT 1/4 AS NUM 结果是0而不是0.25，通常的处理方法为让某个整数乘以一个1.0，系统运算的时候会自动的将运算中的所有数据自动转换为实数进行运算，最终结果也以实数显示。例如：select 1.0*1/4 as num返回的结果就是0.25。
