[Sql Server2008]Ê÷½á¹¹µÄµÝ¹éËã·¨
±¾ÎÄÖ÷Òª½²ÊöÈý¸öÄÚÈÝ£º
1.ÈçºÎ´´½¨hierarychyidµÄ±í£¬²åÈëÊý¾Ý¼°»ù±¾µÝ¹é²éѯ¡£
2.½éÉÜhierarchyidµÄ10ÖÖרÓк¯Êý¡£
3.½éÉÜhierarchyidÌØÓеÄÉî¶ÈÓÅÏÈË÷Òý(Depth-First Indexing)ºÍ¹ã¶ÈÓÅÏÈË÷Òý(Breadth-First Indexing)
ÔÚÉÏÒ»½ÚÖÐ
http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
ÎÒÃÇÒѾÑÝʾÁËÈçºÎÔÚSQL ServerÖÐͨ¹ýÖ÷¼üºÍÍâ¼üÀ´´æ´¢ÈçÏÂͼËùʾµÄÊ÷ÐͽṹÊý¾Ý
ËäȻͨ¹ýÖ÷¼üºÍÍâ¼üµÄÏ໥´îÅä¿ÉÒÔÂú×ãÎÒÃǵIJéѯ¡¢´æ´¢ÐèÇ󣬵«ÊÇÕâÖÖ·½Ê½²¢²»Ò×ÓÚ¹ÜÀíºÍά»¤£¬ÐÒÔ˵ÄÊÇ£¬ÔÚSQL Server 2008ÖÐÌṩÁËÒ»ÖÖеÄÊý¾ÝÀàÐÍhierarchyidºÍÏà¹ØµÄ²Ù×÷·½·¨À´´æ´¢ºÍ²éѯÕâÖÖÊ÷ÐͲã´Î¹ØϵÊý¾Ý¡£
Ê×ÏÈ´´½¨Êý¾Ý±í:
create database TestDb
go
use TestDb
go
Create table EmployeeTreeTable
(
NodeId hierarchyid PRIMARY KEY,
NodeLevel AS NodeId.GetLevel(),
EmployeeId int UNIQUE NOT NULL,
EmployeeName nvarchar(32) NOT NULL,
)
NodeIdÊǼǼÊ÷ÐͲã´ÎµÄId£¬ÊÇhierarchyidÀàÐÍ¡£NodeLevelÊǸö¼ÆËãÁУ¬ÓÃÓÚ´æ´¢µ±Ç°Ê÷ÊÇÉî¶ÈÖµ£¬¸ù½ÚµãΪ0¡£¹ØÓÚNodeId.GetLevel()·½·¨½«ÔÚÏÂÃæÕ½ÚÖÐÏêϸ½éÉÜ¡£
°´ÕÕÉÏͼËùʾµÄ²ã´Î¹ØϵΪ±í²åÈëÊý¾Ý£º
--²åÈëÊý¾Ý
declare @DepthNode hierarchyid;--Éî¶ÈId
declare @BreadthNode hierarchyid;--¹ã¶ÈId
--²åÈë¸ù½Úµã
insert into EmployeeTreeTable values(hierarchyid::GetRoot(),1,'ÏîÄ¿¾Àí')
--¼ÆËãÉî¶È²¢²åÈë×Ó½Úµã2
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 1;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),2,'¼¼Êõ¾Àí');
--¼ÆËã½Úµã2¹ã¶È£¬ÔÚ½Úµã2Óұ߲åÈë½Úµã3
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),3,'²úÆ·¾Àí');
--¼ÆËã½Úµã3¹ã¶È£¬ÔÚ½Úµã3Óұ߲åÈë½Úµã4
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 3;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),4,'²âÊÔ¾Àí');
--¼ÆËã½Úµã2Éî¶È²¢²åÈë×Ó½Úµã5
select @DepthNode = NodeId from EmployeeTreeTa
Ïà¹ØÎĵµ£º
×î½üÕýÔÚѧϰÓÃSQL×öһЩ¶«Î÷£¬ÔÚÒ»¸öÌû×ÓÉÏÃæ¿´µ½Õâƪ¶ÔºÜÓаïÖú£¬¾ÍתÔص½ÁË×Ô¼ºµÄBlogÀϣÍû¶Ô´ó¼ÒÒ²ÓаïÖú¡£
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù× ......
·½·¨Ò»
µÚÒ»²½£ºµÇÈësql/plus Ö´ÐÐÃüÁÎÞÏȺó˳Ðò£©
set time on; (˵Ã÷£º´ò¿ªÊ±¼äÏÔʾ£©
set autotrace on; (˵Ã÷£º´ò¿ª×Ô¶¯·ÖÎöͳ¼Æ£¬²¢ÏÔʾSQLÓï¾äµÄÔËÐнá¹û£©
set autotrace traceonly; (˵à ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE data ......
use myoa
select * from
delete from department where departmentid=1
insert Department(DepartmentId,DepartmentName) values(1,'¼¼Êõ²¿')
update Department set departmentname='ÐÅÏ¢¼¼Êõ²¿' where departmentid=1
--ɾ³ý±í
drop table department
--ɾ³ýÊý¾Ý¿â
drop database bai
--ͳ¼ÆÊý¾Ý¿â±íÖмǼ
se ......
µÚÒ»²½:ÏÂÔØ°²×°Oracle 10g Release 2 ¿Í»§¶ËÈí¼þ,ÏÂÔØÁ¬½ÓÊÇ:
http://download.oracle.com/otn/nt/oracle10g/10201/10201_database_win32.zip
ÏÂÔغó°²×°
µÚ¶þ²½:ÏÂÔØ°²×°Oracle ODAC,ÏÂÔØÁ¬½ÓÊÇ:
http://download.oracle.com/otn/other/ole-oo4o/ODAC1020221.exe
µÚÈý²½: ÖØÆôMS SQ ......