SQL server 2005ѧϰ±Ê¼Ç(ÊÓͼ)
Ò»£®ÊÓͼ(VIEW)
¶¨Ò壺ÊÓͼÊÇÒ»ÕÅÐéÄâµÄ±í£¬ÆäÄÚÈÝÊÇ´ÓÒ»ÕÅ»ò¶àÕűíÖвéѯµÃÀ´µÄ½á¹û¼¯¡£
ÃèÊö£ºÊÓͼÊÇ´æ´¢ÔÚϵͳĿ¼ÖеÄÐÅÏ¢£¬Ëý²¢Ã»ÓÐÕæÕý´æ´¢Êý¾Ý£¬¶øÊÇÒÑ´æ±íµÄͶӰ¡£ÊÓͼµÄÊôÐÔÊǰ´ÕÕÒ»¸ö²éѯµÄ¹æ¶¨´ÓÒ»¸ö»òÕß¶à¸ö±íÖе¼³öµÄ£¬ÊÇÒÑ´æ±íµÄ¹ýÂËÆ÷¡£ËýÊÇÒ»ÕÅ“Ðé±í”£¬´ÓÒ»Õűí»òÕß¶àÕűíÖÐÅÉÉú³öÀ´µÄ£¬ÆäÀ´Ô´³ÆÎª“»ù±í”£¬Ò²¿ÉÒÔ˵ÊÓͼÊÇÒ»ÕÅ“ÅÉÉú±í”¡£
×÷ÓãºÓû§¸ü¾ß×Ô¼ºÐèÒª£¬½¨Á¢ÊÓͼ¡£ÕâÑù¾Í¹ýÂ˵ôÁËÄÇЩ²»³£ÓûòÕßÓû§²»ÐèÒªµÄÊý¾Ý£¬ÈÃÓû§µÄ²Ù×÷¸ü¼ÓѸËÙ¡£ÆäÓŵ㣺
1.½µµÍÊý¾Ý¿âµÄ¸´ÔÓÐÔ¡£
2.¼¯ÖÐÓû§Êý¾Ý¡£
3.¼òµ¥¹ÜÀí¡£
4.×éÖ¯Êý¾Ý×ªÒÆµ½ÆäËûÓ¦ÓóÌÐò¡£
5.Êý¾Ý±£ÃÜ¡£
¶þ£®ÊÓͼ½¨Á¢
CREATE VIEW <ÊÓͼÃû> [(<ÁÐÃû>[,<ÁÐÃû>]......)]
AS <×Ó²éѯ>
[WITH CHECK OPTION]
×Ó²éѯ¿ÉÒÔÊÇÈÎÒ⸴ÔÓµÄSELECTÓï¾ä£¬µ«ÊÇ×Ó²éѯÖв»ÄܰüÀ¨ORDER BYºÍDISTINCTÓï¾ä¡£
WITH CHECK OPTION ±íʾ¶ÔÊÓͼ½øÐÐUPTATA£¬INSERTºÍDELETE²Ù×÷ʱҪ±£Ö¤¸üС¢²åÈëºÍɾ³ýµÄÐÐÂú×ãÊÓͼ¶¨ÒåÖеÄΪ´ËÌõ¼þ(¼´×Ó²éѯÖеıí´ïʽ)¡£
Èý£®ÊÓͼ¹ÜÀí
1£®ÖØÃüÃûÊÓͼ
ʵÏÖ·½Ê½ÓÐÁ½ÖÖ£º
(1)ͨ¹ý sp_name
¸ñʽ£ºsp_name <ÔÊÓͼÃû>,<ÐÂÊÓͼÃû>
×¢Ò⣺´Ë²Ù×÷±ØÐëÔÚµ±Ç°Êý¾Ý¿âÖУ¬Ö»ÓÐÊý¾Ý¿âÓµÓÐÕߺ͹ÜÀíÕß²ÅÄܶÔÊÓͼ¸üÃû¡£
(2)ͨ¹ý¹ÜÀíÆ÷½øÐиüÃû¡£
2.ÊÓͼÐÞ¸Ä
¸ñʽ£º
ALTER VIEW <ÊÓͼÃû>
AS
SELECT ²éѯÓï¾ä
[WITH CHECK OPTION]
3.ɾ³ýÊÓͼ
¸ñʽ£º
DROP VIEW <ÊÓͼÃû>
×¢Ò⣺ÔÚɾ³ýÊÓͼºó£¬´ÓÕâ¸öÊÓͼÅÉÉú³öÀ´µÄÊÓͼ×Ô¶¯É¾³ý£¬µ«É¾³ýij¸ö±íºó£¬»ùÓÚÕâ¸ö±í´´½¨µÄÊÓͼ²»ÄÜ×Ô¶¯É¾³ý¡£
ËÄ£®ÊÓͼӦÓÃ
ÊÓͼӦÓõÄÔ¼Êø£¬ÏÂÁÐÇé¿öϲ»ÄܶÔÊÓͼ½øÐвÙ×÷£º
1.ÊÓͼÊôÐÔÀ´×Ô¾Û¼¯º¯Êý»òÕß°üº¬±í´ïʽ¡¢³£Á¿¡£
2.ÊÓÍ
Ïà¹ØÎĵµ£º
1£©
#pragma warning(disable:4146)
#import "C:\Program Files\Common Files\System\ADO\msado15.dll"
named_guids rename("EOF","adoEOF"), rename("BOF","adoBOF")
#pragma warning(default:4146)
using namespace ADODB;
2£©
#pragma warning(disable:4146) ......
SQL Server 2000ÖУ¬ÓÐÈý¸ö±È½ÏÀàËÆµÄ¹¦ÄÜ£ºËûÃÇ·Ö±ðÊÇ£ºSCOPE_IDENTITY¡¢IDENT_CURRENT ºÍ @@IDENTITY£¬ËüÃǶ¼·µ»Ø²åÈëµ½ IDENTITY ÁÐÖеÄÖµ¡£
IDENT_CURRENT ·µ»ØÎªÈκλỰºÍÈκÎ×÷ÓÃÓòÖеÄÌØ¶¨±í×îºóÉú³ÉµÄ±êʶֵ¡£IDENT_CURRENT ²»ÊÜ×÷ÓÃÓòºÍ»á»°µÄÏÞÖÆ£¬¶øÊÜÏÞÓÚÖ¸¶¨µÄ±í¡£IDENT_CURRENT ·µ»ØÎªÈκλỰºÍ×÷ÓÃÓòÖÐµÄ ......
Êýѧº¯Êý£º
1.¾ø¶ÔÖµ
S:SELECT abs(-1) value
O:SELECT abs(-1) value from dual
2.È¡Õû(´ó)
S:SELECT ceiling(-1.001) value
O:SELECT ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:SELECT floor(-1.001) value
O:SELECT floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:SELECT cast(-1.002 as int) value
O ......
×òÌìÎÒ˵£¬ÓÃ×éºÏË÷ÒýÓÅ»¯SQL£¬²¢²»ÊÇ×îÓŵģ¬ÕâÊÇÒòΪÔÚ8ÒڵıíÉÏÃæÓиöµÈ¼ÛµÄÎﻯÊÓͼ£¬Õâ¸öÎﻯÊÓͼ¿ÉÒÔ´úÌæÎÒÔÚ֮ǰÔÚ±íÉÏÃæ½¨Á¢µÄ×éºÏË÷Òý¡£
SQL> explain plan for SELECT distinct * from (select
2 (PROD_9005_GDF_WK_SS_FDIM.PROD_4_NAME),
3 PROD_9005_GDF_WK ......