SQLServer2005宝典学习笔记（数据操纵部分）

来源：互联网发布：bangumi 知乎编辑：程序博客网时间：2024/06/06 12:47

摘要：本文主要是我学习SQL2005宝典的笔记（只是第二部分），少许平时记录的知识点。主要内容：1、系统函数；2、几种联接；3、CTE公用表表达式；4、已有数据库结构的修改；5、数据库的备份与恢复；6、CSV文件加载数据和Excel复制数据；7、存储过程：加密与获得源码，存储过程返回数据；8、触发器的定义与应用；9、索引相关；10、 SQL通配符；11、SQL注入分析；12、其他：一些优化注意点，关键字Pivot ，between and 的注意点。

1、系统函数
每个数据库都可以查看系统函数的，只要有环境就可以，如下图是NorthWind数据库中查找系统函数的截图：

              这里只介绍一些常见的函数：
              1.0、Left和Right获得左边或右边的指定长度的字符串，语法：Left(string,count)；
              1.1、Rtrim和Ltrim去除字符串左边或右边的空格；
              1.2、Upper和Lower将指定的字符串转换成大写（或小写）；
              1.3、Len求字符串的长度；
              1.4、SubString，抽取指定位置开始的指定长度字符串，语法SubString(string,position,length)；
              1.5、CharIndex返回字符串中指定表达式的起始位置，例如：CharIndex ('a',id)表示id中’a’的位置；
              1.6、Replace(source,search,replace)在字符串中将指定字符串换成另外一种字符串；
              1.7、Cast/Convert类型转换，例如Cast (sales   AS   char(20)) ；Convert (char(20),   sales)  ；
              1.8、str(number,length,decimal)将数字转换成字符串；
              1.9、Stuff函数，格式化字符串，
                        例将123456789转换成123-45-6789；
                        Stuff(Stuff(‘123456789’,4,0,’-‘),7,0,’-‘)，内层在第四个位置加上‘-’；
        2、几种联接
              2.1、form a,b ….与a inner join b等价；
              2.2、Full join全外联接，查询所有数据，一般可用来查找所有问题数据；Cross join返回笛卡尔乘积记录集；
              2.3、Union联合默认删除重复的行，所以无需再加distinct画蛇添足；
              2.4、子查询类似于联接，使用联接从两个数据源取回数据后可以对其进行筛选和操纵。如果必须在联接前对数据进行操纵，应使用子查询；

        3、CTE公用表表达式，CTE(Common Table Expression)
                语法：With CTEName (parameters)
                            AS (Simple Subquery……)
                            Select ……From CTEName
                注：在性能上子查询和CTE几乎没有什么区别。
        4、已有数据库结构的修改
               4.1、修改数据库名（运用系统存储过程sp_renamedb）

Use Test0113;exec sp_renamedb 'Test0113', 'new_name'

4.2、删除数据库

USE MASTERGOIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test0113')DROP DATABASE [Test0113]

4.3、删除表

USE MASTERGOIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test0113')DROP DATABASE [Test0113]

4.4、修改表

4.41、增加一列：

ALTER TABLE tableName ADD columnName columnType NULLALTER TABLE tableName ADD columnName columnType NOT NULL DEFAULT colvalue

4.42、修改列的属性（列名除外）；

--（注意语法，有一次忘了COLUMN，怎么也没查到错误）ALTER TABLE tableName ALTER COLUMN columnName columnType NULL

4.43、修改列名，索引名；

EXEC sp_rename ‘tableName.columnName’,’newColumnName’,’COLUMN’

4.44、删除有数据的一个表的其中一列

ALTER TABLE tableName DROP COLUMN columnName

5、数据库的备份与恢复
5.1、备份：

backup DataBase Test2011 to Disk='C:\Test.bak'With name='Test2011Back'

5.2、恢复数据库：

Use master;Restore Database Test2011From Disk=’C:\Test2011Back.bak’

6、CSV文件加载数据和Excel复制数据

CREATE TABLE Test1220(TestID int primary key,TestContent char(10))BULK INSERT Test1220FROM 'C:\Test.csv'WITH(FirstRow=1,FieldTerminator=',',RowTerminator='\n')

              FirstRow表示从CSV的第几行开始读取，1表示从第一行开始读取，如果有标题则为2， RowTerminator表示分隔符的字符。
              通过Excel将数据复制到表中（注意类型要对应，非空的一定要有值，一些约束也需符合）
        7、存储过程：加密与获得源码，存储过程返回数据
               7.1、加密存储过程源码

Create Procedure procNameEncryptionAS……

              7.2、获得存储过程的源码
                        Sp_helptext procName
              7.3、存储过程返回值
                        7.31、方法一：

Create Procedure Test(@para1 int ,@outPara2)--调用时Declare @returnValue int;Exec Test 1,@returnValue OutPut

                        7.32、方法二：return 关键字
              注意对于每个返回的记录集SQL Server在默认的情况下都发送一条消息，指出返回了或者影响了多少行记录，经过测试，最多甚至降低17%的性能，所以在有返回值的存储过程AS后面加上 Set NoCount ON
        8、触发器的定义与应用
              8.1、触发器的定义

Create Trigger trigName on tableNameAfter Upder               /*insert,delete均可*/AS ……

               8.2、根据触发器的知识解决以前一个问题：
                         一个用户注册了之后，注册日期一经确定就不可以再改变。
                         写了一个类似的例子：

Create table Test1220(TestID int Primary Key,TestContent Char(10) Default ‘Hello’)Create Trigger trig on Test1220After UpdateASIf Update(TestContent)RollBack

        9、索引相关
              9.1、创建主键为非聚焦索引
                       TestID int Primary Key NonClustcred
              9.2、创建聚焦索引
                        Create Clustered index IxOrderID On OrderDetail(OrderID)
              9.3、组合聚焦索引
                        Create Clustered index IxGuideName On Guide(LastName,FirstName);
               9.4、创建非聚焦索引
                         Create NonClustered index IndexName On TableName(columnName)
               9.5、创建唯一索引 Unique index
               9.6对于预期将在Where,Order By ,Group By子句中的每列基于它创建一个单列索引；索引的选择性，当数据重复密度高时不适合使用索引。
               9.7、索引优化
                        关于索引，通俗点的理解就是目录，查字典时如果没有目录就得将字典从前到后翻一遍查找所需的字。SQL查询数据库时也是一样，如果不用索引就得全表扫描。
                        运用索引优化过的分页语句：

--运用max和top实现查找第31-40的数据--66万条数据,瞬间秒杀(环境SQL2000，测试时精度不高)SELECT top 10* FROM Orders WHERE OrderID>(SELECT max(OrderID) FROM (SELECT top 20 OrderID FROM Orders ORDER BY OrderID ASC)a)ORDER BY OrderID ASC--相比之下，没有运用索引优化的方法：--运用top和not in实现--66万条数据耗时3秒(环境SQL2000，测试时精度不高，但性能还是和前者有明显的差距)SELECT top 10* FROM Orders WHERE OrderID not in(SELECT top 20 OrderID FROM Orders  ) ORDER BY OrderID ASC

          10、 SQL通配符
                (1)%多个字符，例如Like ‘m%’
               (2)_下划线,表示单个字符，例如Like ‘pingf_n’,注意有几个下划线就匹配几个字符
               (3)[]匹配指定范围中的字符，例如Like ’[a-g]’
                (4)[^]匹配不在指定范围内的字符，Like’[^m-t]’
                注意：如果要匹配以F%15开头的，有两种解决方案，其一用[]将通配符括起来，Like’F[%]15%’，这是SQL Server特有的，其二前面加上自定义转义字符，Like’F&%15%’ escape ‘&’,这是一般通用的。
        11、SQL注入分析
                 11.1、SQL注入分析
                         (1)恶意代码：123’;delete OrderDetail--
                         (2)使用 or 1=1 :例如123’ or 1=1
                         (3)绕开密码保护：
                               UserName： Jack’--注意左边是两个连接符，也就是SQL中注释符号
                               PassWord： Who Cases
                  11.2、防范注入攻击的一些方法
                         (1)采用存储过程
                         (2)检查参数是否包含语句结束符(例如分号，注释符等)
                         (3)避免动态SQL
                         (4)屏蔽出错信息。
        12、其他
               12.1、将数据列定义成not null，有利于简化查询，因为不需要检查值的null属性，有利于检索引擎做出判断；
               12.2、SQL优化小结；
                          (1)表的字段设置要恰当，尽可能减少占用空间；
                          (2)避免对大容量的数据段做无谓的检索；
                          (3)合理使用索引，提高查询效率；
               12.3、使用列序号来指定排序的列
                           Select ID,Name From Student Order By 1则按第1列ID排序。
               12.4、between and 的注意点
                           Between A and B 是闭区间[A,B]
                           注意： between ‘07/01/01’ and ‘08/31/01’ 表示从07/01/01 00:00:00.000到08/31/01 00:00:00.000，这就遗漏了8月31日0点之后的数据，另外还要注意毫秒的精确度。
                12.5关键字Pivot http://msdn.microsoft.com/zh-cn/library/ms177410.aspx （官方资料）。
        后记说明：写本文为了巩固所学知识，毕竟不是所有的知识都立即在工作中用到，我感到没用到的很容易忘，有些没有理解的没有写出，绝大多数内容都是源于SQLServer2005宝典的第二部分—使用Select操纵数据，如果冒犯了本书原作者或者翻译者的版权什么的请通知。Pivot部分资料点此下载（搜集的网上的Pivot资料，具体第一个作者是谁我也不知道，感觉找到的版本都一样，如果冒犯了原作者的版权什么的请通知）。