Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL Server 2005ÖеÄT SQLÔöÇ¿F

DDL´¥·¢Æ÷ DDL Triggers
SQL Server 2005¿ÉÒÔ¾ÍÕû¸ö·þÎñÆ÷»òÊý¾Ý¿âµÄij¸ö·¶Î§ÎªDDLʼþ¶¨Òå´¥·¢Æ÷¡£Ò²¿ÉÒÔΪµ¥¸öDDLÓï¾ä£¨ÀýÈ磺CREAT_TABLE¡¢DROP_TABLEµÈ£©»òÕßΪһ×éÓï¾ä£¨ÀýÈ磺ָ¶¨DDL_DATABASE_LEVEL_EVENTSÏëÒª´¥·¢Æ÷´¥·¢Êý¾Ý¿âËùÓÐDDLʼþ£©¶¨ÒåDDL´¥·¢Æ÷¡£
ÔÚDDL´¥·¢Æ÷ÄÚ²¿£¬¿ÉÒÔͨ¹ý·ÃÎÊeventdata()º¯Êý»ñµÃÓ뼤·¢¸Ã´¥·¢Æ÷µÄʼþÓйصÄÊý¾Ý¡£¸Ãeventdata()º¯Êý·µ»ØÓйØÊ¼þµÄxmlÊý¾Ý¡£
DDL´¥·¢Æ÷ÌØ±ðÓÐÓõķ½°¸°üÀ¨DDL¸ü¸ÄµÄÍêÕûÐÔ¼ì²é¡¢ÉóºË·½°¸ÒÔ¼°ÆäËû·½°¸¡£
´úÂëÑÝʾ£º
USE demo
GO
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR('ûÓÐɾ³ý±íµÄȨÏÞ.', 10, 1)
PRINT '³¢ÊÔÔÚÊý¾Ý¿â' + DB_NAME() + 'ÖÐɾ³ý±í.'
PRINT CONVERT (nvarchar (1000),EventData())
ROLLBACK
GO
-- ²âÊÔ
CREATE TABLE TestDROP(col1 INT)
GO
INSERT INTO TestDROP VALUES(1)
DROP TABLE testdrop
-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT '·¢ÉúDDL LOGIN.'
PRINT CONVERT (nvarchar (1000),EventData())
GO
-- ²âÊÔ
CREATE LOGIN login1 WITH PASSWORD = '123'
ALTER LOGIN login1 WITH PASSWORD = 'xyz'
DROP LOGIN login1
×ܽá
SQL Server 2005ÖеÄTransaction-SQLÔöÇ¿¹¦ÄÜÌá¸ßÁËÓû§ÔÚ±àд²éѯʱµÄ±í´ïÄÜÁ¦£¬Ê¹Óû§¿ÉÒÔ¸ÄÉÆ´úÂëµÄÐÔÄÜ£¬²¢ÇÒÀ©³äÁË´íÎó´¦ÀíÄÜÁ¦¡£SQL Server 2005 ÔÚTransaction-SQLÉÏËù×öµÄ¸Ä½ø·´Ó³ÁËÆä¸üºÃµØÂú×ãÁËANSI-99 SQL¹æ·¶µÄÒªÇóÒÔ¼°¿Í»§µÄÐèÇó¡£ÔÚTransaction-SQLºÍÍйܴúÂëÖ®¼äµÄÑ¡Ôñ¡£


Ïà¹ØÎĵµ£º

Oracle µÄÊý¾Ýµ¼Èëµ¼³ö¼° Sql Loader (sqlldr) µÄÓ÷¨

Ô­ÎÄ:http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html
ÔÚ Oracle Êý¾Ý¿âÖУ¬ÎÒÃÇͨ³£ÔÚ²»Í¬Êý¾Ý¿âµÄ±í¼ä¼Ç¼½øÐи´ÖÆ»òÇ¨ÒÆÊ±»áÓÃÒÔϼ¸ÖÖ·½·¨£º
1. A ±íµÄ¼Ç¼µ¼³öΪһÌõÌõ·ÖºÅ¸ô¿ªµÄ insert Óï¾ä£¬È»ºóÖ´ÐвåÈëµ½ B ±íÖÐ
2. ½¨Á¢Êý¾Ý¿â¼äµÄ dblink£¬È»ºóÓà create table B as select * from A ......

little notesÖ®SHARED_POOL SQL½âÎö

TOM´óÊåµÄµ÷ÓÅÊéÖÐ˵¹ýÒ»¾ä»°£¬¾ßÌåÓ¢ÎľÍÍüÁË£¬´ó¸ÅÒâ˼¾ÍÊÇ£ºÈç¹ûÓÐÈËÈÃÎÒд±¾ÔõÑùÈÃORACLEÐÔÄÜ×îÂýµÄÊéµÄ»°ÎһὫȡÏû°ó¶¨±äÁ¿(bind variable)×öΪÊéµÄµÚÒ»ÕºÍ×îºóÒ»ÕÂ(ËûµÄÒâ˼ÊÇËûºÜÓÐÓÄĬ~~!)£¬¿É¼û°ó¶¨±äÁ¿µÄÖØÒªÐÔ¡£
°ó¶¨±äÁ¿´ó¶àÓÃÔÚOLTP(online transaction process)ÖУ¬ÔÚOLAP(online analizy process)ÖоÍû± ......

Oracleά»¤³£ÓÃSQLÓï¾ä

1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
¡¡¡¡select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
¡¡¡¡from dba_tablespaces t, dba_data_files d
¡¡¡¡where t.tablespace_name = d.tablespace_name
¡¡¡¡group by t.tablespace_name;
¡¡¡¡
¡¡¡¡2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
¡¡¡¡select tablespace_ ......

̸SQL Server 2005ÖеÄT SQLÔöÇ¿E


ÐµĹØÏµÔËËã·û PIVOT/UNPIVOT/APPLY
1¡¢PIVOT
PIVOTÔËËã·û½«ÐÐÐýתΪÁУ¬²¢ÇÒ¿ÉÄÜͬʱִÐоۺϡ£Ê¹ÓÃPIVOTÔËËã·ûʱҪעÒâµÄÖØÒªÒ»µãÊÇ£¬ÐèҪΪËüÌṩһ¸ö²éѯ±í´ïʽ£¬±í´ïʽʹÓÃÊÓͼ¡¢ÅÉÉú±í»òÕßÊÇCTEÖ»·µ»ØËù¹Ø×¢µÄÁС£
2¡¢UNPIVOT
UNPIVOTÔËËã·ûÖ´ÐÐÓëPIVOTÔËËã·ûÏà·´µÄ²Ù×÷£»Ëû½«ÁÐÐýתΪÐÐÁË¡£
3¡¢APPLY ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ