传智播客－Tree in SQL（译文）（3）
This has some predictable results that we can use for building queries. The root is always of the form (left = 1, right = 2 * (SELECT COUNT(*) from TreeTable)); leaf nodes always have (left + 1 = right); the BETWEEN predicate defines the subtrees; and so on. Here are some common queries that you can use to build others:
我们可以利用一些可预见的结果构建查询（表达式）。根部总是以这种形式构成（left = 1, right = 2 * (SELECT COUNT(*) from TreeTable)）；叶子节点则总是（left + 1 = right）；BETWEEN表达定义了子树；等等。这里有一些常用的查询，你可以用它们来组建更多其他（查询）：
1. Find an employee and all his/her supervisors, no matter how deep the tree.
查找一个雇员及他/她的所有上级，无论这颗树结构有多深。
SELECT P2.*
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = :myemployee;
2. Find the employee and all his/her subordinates. (This query has a nice symmetry with the first query.)
查找雇员及他/她所有下属。（这个查询与第一个查询形成良好的对称。）
SELECT P2.*
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.emp = :myemployee;
3. Add a GROUP BY and aggregate functions to these basic queries and you have hierarchical reports. For example, the total salaries that each employee controls:
给这些基本查询添加GROUP BY（关键字）和总计函数这样你就有分级记录了。例如，每个雇员能支配的薪酬总数：
SELECT P2.emp, SUM(S1.salary)
from Personnel AS P1, Personnel AS P2,
Salaries AS S1
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = S1.emp
GROUP BY P2.emp;
In the adjacency list method, this has to be done with a cursor.
在邻接列表模式里，必须依靠游标（cursor）才能这样做。
4. Find the level of each node, so you can print the tree as an indented listing.
查找每个节点的级别，这样你就能够以缩进列表形式打印这棵树了。
SELECT COUNT(P2.emp) AS indentation, P1.emp
from Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.lft;
5. The nested set mod
相关文档：
--第一步
--在master库中建立一个备份数据库的存储过程.
USE master
GO
CREATE PROC p
@db_name sysname, --数据库名
@bk_path NVARCHAR(1024) --备份文件的路径
A ......
--设置数据库输出，默认为关闭，每次新打开窗口都要重新设置
set serveroutput on
--调用 包 函数 参数
execute dbms_output.put_line('hello world');
--或者用call调用，相当于java中的调试程序打桩
call d ......
SQL :Structured Query Language结构化查询语言
1.Select [Predicate] *(filed) from table/view Where ... Group by ... Having... Order by ... With ...
Predicate：包括all/Distinct/Distinctrow/Top，限制查询结果；
As可以命名别名；
Where ... 指定某些条件，将所� ......
SQL注入攻击的危害性很大。在讲解其防止办法之前，数据库管理员有必要先了解一下其攻击的原理。这有利于管理员采取有针对性的防治措施。
一、 SQL注入攻击的简单示例。
statement := "SELECT * from Users WHERE Value= " + a_variable + "
上面这条语句是很� ......
SQL SERVER登录用户在服务器上的权限
一个登录用户到底在SQL SERVER实例上有什么样的权限，下面以SQL SERVER2005为例来细数一下。
一．
首先查看该登录用户属于哪个固定服务器角色。所有SQL SERVER的登录用户和角色都会在master.sys.server_principal视图上有一条记录。而记录登录用户属于什么服务器角色的视图是master. ......
