sqlÓÅ»¯34Ìõ
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´¡±í, ½»²æ±íÊÇÖ¸ÄǸö±»ÆäËû±íËùÒýÓõıí.
£¨2£© WHERE×Ó¾äÖеÄÁ¬½Ó˳Ðò£®£º
ORACLE²ÉÓÃ×Ô϶øÉϵÄ˳Ðò½âÎöWHERE×Ó¾ä,¸ù¾ÝÕâ¸öÔÀí,±íÖ®¼äµÄÁ¬½Ó±ØÐëдÔÚÆäËûWHEREÌõ¼þ֮ǰ, ÄÇЩ¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐëдÔÚWHERE×Ó¾äµÄĩβ.
£¨3£© SELECT×Ó¾äÖбÜÃâʹÓà ‘ * ‘£º
ORACLEÔÚ½âÎöµÄ¹ý³ÌÖÐ, »á½«'*' ÒÀ´Îת»»³ÉËùÓеÄÁÐÃû, Õâ¸ö¹¤×÷ÊÇͨ¹ý²éѯÊý¾Ý×ÖµäÍê³ÉµÄ, ÕâÒâζ׎«ºÄ·Ñ¸ü¶àµÄʱ¼ä
£¨4£© ¼õÉÙ·ÃÎÊÊý¾Ý¿âµÄ´ÎÊý£º
ORACLEÔÚÄÚ²¿Ö´ÐÐÁËÐí¶à¹¤×÷: ½âÎöSQLÓï¾ä, ¹ÀËãË÷ÒýµÄÀûÓÃÂÊ, °ó¶¨±äÁ¿ , ¶ÁÊý¾Ý¿éµÈ£»
£¨5£© ÔÚSQL*Plus , SQL*FormsºÍPro*CÖÐÖØÐÂÉèÖÃARRAYSIZE²ÎÊý, ¿ÉÒÔÔö¼Óÿ´ÎÊý¾Ý¿â·ÃÎʵļìË÷Êý¾ÝÁ¿ ,½¨ÒéֵΪ200
£¨6£© ʹÓÃDECODEº¯ÊýÀ´¼õÉÙ´¦Àíʱ¼ä£º
ʹÓÃDECODEº¯Êý¿ÉÒÔ±ÜÃâÖظ´É¨ÃèÏàͬ¼Ç¼»òÖظ´Á¬½ÓÏàͬµÄ±í.
£¨7£© ÕûºÏ¼òµ¥,ÎÞ¹ØÁªµÄÊý¾Ý¿â·ÃÎÊ£º
Èç¹ûÄãÓм¸¸ö¼òµ¥µÄÊý¾Ý¿â²éѯÓï¾ä,Äã¿ÉÒÔ°ÑËüÃÇÕûºÏµ½Ò»¸ö²éѯÖÐ(¼´Ê¹ËüÃÇÖ®¼äûÓйØϵ)
£¨8£© ɾ³ýÖظ´¼Ç¼£º
×î¸ßЧµÄɾ³ýÖظ´¼Ç¼·½·¨ ( ÒòΪʹÓÃÁËROWID)Àý×Ó£º
DELETE from EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
from EMP X WHERE X.EMP_NO = E.EMP_NO);
£¨9£© ÓÃTRUNCATEÌæ´úDELETE£º
µ±É¾³ý±íÖеļǼʱ,ÔÚͨ³£Çé¿öÏÂ, »Ø¹ö¶Î(rollback segments ) ÓÃÀ´´æ·Å¿ÉÒÔ±»»Ö¸´µÄÐÅÏ¢. Èç¹ûÄãûÓÐCOMMITÊÂÎñ,ORACLE»á½«Êý¾Ý»Ö¸´µ½É¾³ý֮ǰµÄ״̬(׼ȷµØ˵Êǻָ´µ½Ö´ÐÐɾ³ýÃüÁî֮ǰµÄ×´¿ö) ¶øµ±ÔËÓÃTRUNCATEʱ, »Ø¹ö¶Î²»ÔÙ´æ·ÅÈκοɱ»»Ö¸´µÄÐÅÏ¢.µ±ÃüÁîÔËÐкó,Êý¾Ý²»Äܱ»»Ö¸´.Òò´ËºÜÉÙµÄ×ÊÔ´±»µ÷ÓÃ,Ö´ÐÐʱ¼äÒ²»áºÜ¶Ì. (ÒëÕß°´: TRUNCATEÖ»Ô
Ïà¹ØÎĵµ£º
¡í1:È¡µÃµ±Ç°ÈÕÆÚÊDZ¾Ôµĵڼ¸ÖÜ
SQL> select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from
dual;
TO_CHAR(SYSDATE,'YY
-------------------
20030327 4 18:16:09
SQL> select to_char(sysdate,'W') from dual;
T
-
4 ......
SELECT tagid, tagname from uchome_mtag WHERE tagname IN (SELECT tagname from uchome_mtag GROUP BY tagname HAVING (COUNT(tagname) > 1)) ORDER BY tagname ......
Ò»¡¢SQL ServerÖÐÊý¾ÝÐеĴ洢·½Ê½
ÔÚSQL ServerÖдæ·ÅÊý¾ÝµÄÎļþ»áÒÔ8KBµÄ´óС·ÖÒ³¡£Ã¿Ò»Ò³¿ÉÒÔÊÇÊý¾Ý¡¢Ë÷ÒýÒÔ¼°ÆäËûSQL ServerÊý¾Ý¿âÐèҪΪÆäά»¤Êý¾ÝÎļþµÄÊý¾ÝÀàÐÍ¡£´ó¶àÊýÒ³ÊÇÊý¾ÝÒ³»òË÷ÒýÒ³¡£Ò³ÊÇSQL Server¶Á¡¢Ð´Êý¾ÝÎļþµÄµ¥Ôª¡£Ã¿Ò»Ò³Ö»°üÀ¨Ò»¸ö¶ÔÏóµÄÊý¾Ý»òË÷ÒýÐÅÏ¢£¬ËùÒÔÔÚÿһ¸öÊý¾Ý ......
ASP.NET Excelµ¼Èëµ½SQL ServerÊý¾Ý¿â
Ìṩ°ÑExcelÀïµÄÊý¾Ýµ¼Èëµ½SQL Server Êý¾Ý¿â£¬Ç°ÌáÊÇExcelÀïµÄ×Ö¶ÎÔÚSql Server±íÀﶼÓУ¬²»È»»á³öÏÖ´íÎó¡£×¢ÊͺÜÏêϸŶ£¡ÒªÒýÓõÄÃüÃû¿Õ¼äÊÇ£º
using System.Data.OleDb;
using System.Data.SqlClient;
//²Ù×÷Àà
public class ExcelToSQL
{
& ......