SQL 日常开发笔记(持续更新)-SQL Server 查询处理中的各个阶...-SQL Server 数据库备份

当前位置: 数据库>sqlserver

本页文章导读:

▪SQL 日常开发笔记(持续更新) SQL CHARINDEXSQL 在2008的更新中，我最喜欢的是MERGE，2012中就是OFFSET了。SQL 2012新的分页特性OFFSET： orde by aa offset 10 and 20SQL .........

▪SQL Server 查询处理中的各个阶段(SQL执行顺序) 转　　SQL Server 查询处理中的各个阶段(SQL执行顺序) 　　SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中，代码按编码顺序被处理，但是在SQL语言中，第一个被.........

▪SQL Server 数据库备份　　备份数据库是指对数据库或事务日志进行复制，当系统、磁盘或数据库文件损坏时，可以使用备份文件进行恢复，防止数据丢失。　　SQL Server数据库备份支持4种类型，分别应用于不同的.........

[1]SQL 日常开发笔记(持续更新)

来源: 发布时间: 2013-10-25

SQL CHARINDEX

SQL 在2008的更新中，我最喜欢的是MERGE，2012中就是OFFSET了。

SQL 2012新的分页特性OFFSET： orde by aa offset 10 and 20

SQL 模糊查询匹配后面有数字的

select * from users where username like 'zhangqian[0-9]'

SQL 函数不单单可以缓存写好的SQL语句，貌似还缓存读取出的数据（SheetHaveRece）

SQL 中参数为output时，会自动先返回标识指给主键，在执行添加

SQL 函数ISNUMERIC(exps),判断是否为数字，是 =1 否=0

SQL datediff(day,@StartTime,@EndTime),

SQL DateName(YEAR,StartDate) 获取日期的年

SQL datepart(DAYOFYEAR,getdate()) 获取日期的某一部分当前是获取今年的第几天

SQL Group By 第一排序字段,第二排序字段

SQL SubString (exp,start,end)字符串截取

SQL select right(字段，长度)从右侧开始获取N位

SQL isnumeric(字段) = 1 是否数字判断

SQL inner join [只取相同的数据]

left join [左表为主]

right join [右表为主]

SQL 查询的数据到参数中，用 is not null 来判断 IF(@PasswordWithIn IS NOT NULL)

SQL : 索引的建立-->在 "表" 下找到"索引"分支右键，新建添加聚集索引或非聚集索引

SQL 列数据合并：

select CT.*,STUFF((select ',' + f.Name from F_Product f where f.ID=CT.TmpId for xml path('')),1,1,'') AS Name from CT

SQL case when 的用法：

update OrderPay set NoPayMoney=(case when (@PayMoney>=AllMoney) then 0 else (@PayMoney-AllMoney) end) where OrderId=@OrderId

SQL SQL 2012的OFFSET分页方式

SQL count(*)可以忽略Null来进行统计

SQL 日期转换的神奇用法

print Convert(VARCHAR(10), GETDATE(), 2 )==13.03.12 --简写日期

print Convert(VARCHAR(10), GETDATE(),110)==03-12-2013 --获取的日期

print Convert(VARCHAR(10), GETDATE(),111)==2013-03-12

print Convert(VARCHAR(10), GETDATE(),112)==20130312

print Convert(VARCHAR(10), GETDATE(),114)==15:26:54:4 --获取的时分秒

SQL 一条查询语句进行不同条件统计：用sql按性别统计男生、女生人数

select sum(case 性别 when 1 then 1 else 0 end) 男生人数，sum(case 性别 when 0 then 1 else 0 end) 女生人数 from table

select count(1),sum(case sex when 1 then 1 else 0 end) from users

SQL 强制删除数据库订阅：

sp_removedbreplication 'SERVICE_Test'

SQL 随机挑选10挑数据：

MySQL:order by rand() limit 10

ACCESS : order by rnd(id) --id为数据库的自动编号字段

SQL Server : order by newid()

[2]SQL Server 查询处理中的各个阶段(SQL执行顺序) 转

来源: 发布时间: 2013-10-25

SQL Server 查询处理中的各个阶段(SQL执行顺序)

　　SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中，代码按编码顺序被处理，但是在SQL语言中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但是几乎总是最后被处理。

每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。下面是对应用于SQL server 2000和SQL Server 2005的各个逻辑步骤的简单描述。

(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2) ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

逻辑查询处理阶段简介

FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1

ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。

OUTER(JOIN)：如果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.

CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.

HAVING：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.

SELECT：处理SELECT列表，处理各种聚积函数，并产生VT8.

DISTINCT：将重复的行从VT8中移除，产生VT9.

ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).

TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

注：步骤10，按ORDER BY子句中的列列表排序上步返回的行，返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的是，它不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。

因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。例如，下面的派生表查询无效，并产生一个错误：

select *
from(select orderid,customerid from orders order by orderid)
as d

下面的视图也会产生错误

create view my_view
as
select *
from orders
order by orderid

错误信息: Msg 1033, Level 15, State 1, Procedure my_viewasselect, Line 2
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,and common table expressions, unless TOP or FOR XML is also specified.

在SQL中，表表达式中不允许使用带有ORDER BY子句的查询，而在T—SQL中却有一个例外（应用TOP选项）。

所以要记住，不要为表中的行假设任何特定的顺序。换句话说，除非你确定要有序行，否则不要指定ORDER BY 子句。排序是需要成本的，SQL Server需要执行有序索引扫描或使用排序运行符。
推荐一段SQL代码：行列转置

/*问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94

想变成(得到如下结果)：
姓名语文数学物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/

create table tb(姓名 varchar(10),课程 varchar(10),分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '

[3]SQL Server 数据库备份

来源: 发布时间: 2013-10-25

　　备份数据库是指对数据库或事务日志进行复制，当系统、磁盘或数据库文件损坏时，可以使用备份文件进行恢复，防止数据丢失。

　　SQL Server数据库备份支持4种类型，分别应用于不同的场合，下面简要介绍。

　　（1）完全备份

　　完全备份，即完整数据库备份，可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。这是大多数人常用的方式，但需要花费更多的时间和空间，所以一般推荐一周做一次完全备份。　

　　（2）事务日志备份

　　事务日志备份时一个单独的文件，记录数据库的改变，备份时只需要复制上次备份以来对数据库所做的改变，可支持从数据库、差异或文件备份中快速恢复，时间少，速度快，推荐每小时甚至更频繁地备份事务日志。

　　（3）差异备份

　　在完整数据库备份之间执行差异数据备份，比完全备份小，因为只包含自完全备份以来所改变的数据库，优点是存储和恢复速度快。推荐每天做一次差异备份。

　　（4）文件和文件组备份

　　数据库一般由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能备份完，那么可以使用文件和文件组备份，每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以此种备份并不常用。

　　本实例运用SQLDMO.backup对象完成整个系统数据库的备份。这使得在系统或数据库发生故障（如硬盘发生故障）时可以重建系统。

　　备份整个数据库的语法如下：

BACKUP DATABASE {database_name|@database_name_var}
TO<backup_device>[,...n]
[WITH
[BLOCKSIZE={blocksize|@blocksize_variable}]
[[,]DESCRIPTION={'text'|@text_variable}]
[[,]DIFFERENTIAL]
[[,]EXPIREDATE={date|@date_var}
|RETAINDAYS={days|@days_var}]
[[,]PASSWORD={password|@password_variable}]
[[,]FORMAT|NOFORMAT]
[[,]{INIT|NOINIT}]
[[,]MEDIADESCRIPTION={'text'|@text_variable}]
[[,]MEDIANAME={media_name|@media_name_variable}]
[[,]MEDIAPASSWORD={mediapassword|@mediapassword_variable}]
[[,]NAME={backup_set_name|@backup_set_name_var}]
[[,]{NOSKIP|SKIP}]
[[,]{NOREWIND|REWIND}]
[[,]{NOUNLOAD|UNLOAD}]
[[,]RESTART]
[[,]STATS[=percentage]]
]

　　备份数据库参数及说明如下：

备份数据库参数及说明参　　数　　说　　明DATABASE

指定一个完整的数据库备份。加入指定了一个文件盒文件组的列表，那么仅有这些被指定的文件和文件组被备份

{ database_name | @database_name_var }

指定了一个数据库，从该数据库中对事物日志、部分数据库或完整的数据库进行备份。如果作为变了量(database_name_var)提供，则可将该名称指定为字符串常量(@database_name_var=database name)或字符串

数据类型（ntext或text数据类型除外）的变量

<backup_device>

指定备份操作时要使用的逻辑或物理设备。可以是下列一种或多种形式：

{logical_backup_device_name}|{@logical_backup_device_name_var}：是由 sp_addupmdevice创建的备份设备的逻辑名称，数据库将备份到该设备中，其名称必须遵守标识符规则。如果将其作为变量(@logical_backup_device_name_var)提供，

则可将该备份设备名称指定为字符串常量(@logical_backup_device_name_var)提供，则可将该备份设备名称指定为字符串常量(@logical_backup_device_name_var=logical backup device name)或字符串数据类型（