sqlÊý¾Ý¿âµÄ¹Ø¼ü×Ö¼°²éѯ¼°º¯Êý
±¾ÖܺÍÉÏÖܾÀí¸øÎÒÃÇ×öÁËÁ½´Î¹ØÓÚsqlµÄÅàѵ£¬¸Ð¾õºÜÓÐÓÃËùÒÔ×ܽáһϣ¡
Union£ºÖ»ÓÐÁ½Õűí½á¹¹ÏàͬµÄ½á¹û¼¯²ÅÄÜʹÓÃunion£¬½«ËùÓеıíÊý¾Ý·Åµ½Ò»¸ö½á¹û¼¯ÖС£
Count£º¼ÆËã²ÎÊýÁбíÖеÄÊý×ÖÏîµÄ¸öÊý¡£À¨ºÅÀï±ß¿ÉÒÔÊÇÁÐÃû£¬Ò²¿ÉÒÔÊDzÎÊýÖµ¡£
Case£ºcaseÖ»ÄܸúÔÚselectºó±ß£¬²»ÄÜÓÃÔÚwhereºó±ß¡£fromºó±ßÈç¹ûÊÇ×Ó²éѯµÄ»°±ØÐëÌí¼Ó×Ó²éѯ½á¹û¼¯Ãû³Æ¡£
Join£ºÁ¬½ÓºÅ£¬·ÖΪinner join£¨ÄÚÁ¬½Ó£©ºÍouter join£¨ÍâÁ¬½Ó£©£¬ÆäÖÐouter joinÓÖ·ÖΪleft join£¨×óÁ¬½Ó£©ºÍright join£¨ÓÒÁ¬½Ó£©¡£inner join£ºÖ»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐУ»left join£º·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼£»right join£º·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼¡£
Exists£º¼ìÑé²éѯÊÇ·ñ·µ»ØÊýÖµ£¬ºó±ß¸ú×Ó²éѯ¡£
Isnull/Is not null£ºÅжÏÊÇ·ñΪ¿Õ¡£
Cube£º»ã×ÜÊý¾Ý£¬Éú³É¶àάÊý¾Ý¼¯¡£cubeÖ»ÓÐÔÚgroup by·Ö×éÁ½¸öÒÔÉϲÅÆð×÷Óã¬ÊǶÔgroup byºóµÄ×ֶηÖ×éºóÔÚ½øÐе¥¶À·Ö×é¡£
Over£º¿ÉÒÔÓë¾ÛºÏº¯ÊýÒ»ÆðʹÓÃÀ´µÃ³öµþ¼Ó½á¹û¼¯¡£over£¨order by¡£¡£¡££©ºó¸úÅÅÃû¿ª´°º¯Êý£¬over£¨partition by¡£¡£¡££©ºó¸ú¾ÛºÏ¿ª´°º¯Êý¡£Ê¹ÓÃover×Ó¾ä±È×Ó²éѯЧÂʸߡ£
Partition by£º¶Ô½á¹û¼¯½øÐзÖ×éʱ»áÓÐÖظ´Êý¾Ý£¬group by²»»á³öÏÖÖظ´Êý¾Ý¡£
Row_number()£ºÎª²éѯ³öÀ´µÄÿһÐмǼÉú³ÉÒ»¸öÐòºÅ£¬Ê¹Ó÷½·¨Îª£ºRow_number() over£¨order by ¡£¡£¡££©¡£
exec ´æ´¢¹ý³Ì=±äÁ¿ µÈͬÓÚreturn ±äÁ¿¡£
select ÈÎÒâÖµÏ൱ÓÚ¸³Öµ£¬¼´²éѯÈÎÒâÖµ£¬ÀýÈ磺select 1£¬Ö´ÐкóÏÔʾ½á¹ûΪ1¡£
Ïà¹ØÎĵµ£º
ÓÃsql*plus»òµÚÈý·½¿ÉÒÔÔËÐÐsqlÓï¾äµÄ³ÌÐòµÇ¼Êý¾Ý¿â£º
Ôö¼ÓÒ»¸öÁУº
ALTER TABLE ±íÃû ADD(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp ADD(weight NUMBER(38,0));
ÐÞ¸ÄÒ»¸öÁеÄÊý¾ÝÀàÐÍ(Ò»°ãÏÞÓÚÐ޸ij¤¶È£¬ÐÞ¸ÄΪһ¸ö²»Í¬ÀàÐÍʱÓÐÖî¶àÏÞÖÆ):
ALTER TABLE ±íÃû MODIFY(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp MODIFY(wei ......
COUNT(*)ÓëCOUNT(COL)
ÍøÉÏËÑË÷ÁËÏ£¬·¢ÏÖ¸÷ÖÖ˵·¨¶¼ÓУº
±ÈÈçÈÏΪCOUNT(COL)±ÈCOUNT(*)¿ìµÄ£»
ÈÏΪCOUNT(*)±ÈCOUNT(COL)¿ìµÄ£»
»¹ÓÐÅóÓѺܸãЦµÄ˵µ½Õâ¸öÆäʵÊÇ¿´ÈËÆ·µÄ¡£
ÔÚ²»¼ÓWHEREÏÞÖÆÌõ¼þµÄÇé¿öÏ£¬COUNT(*)ÓëCOUNT(COL)»ù±¾¿ÉÒÔÈÏΪÊǵȼ۵ģ»
µ«ÊÇÔÚÓÐWHEREÏÞÖÆÌõ¼þµÄÇé¿öÏ£¬COUNT(*)»á±ÈCOUNT(COL)¿ì·Ç³£¶à ......
select s.* from t_info t,t_info_class_relationship s where t.id=s.info_id and t.status is not null and s.class_id in(
select a.id
from T_INFO_CLASS a
start with a.parent_id=39902
connect by prior
a.id =parent_id u ......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(
SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£ ......