ͨ¹ý·ÖÎöSQLÓï¾äµÄÖ´Ðмƻ®ÓÅ»¯SQL(¶þ)
¶þ£ºÓÐЧµÄÓ¦ÓÃÉè¼Æ
ÎÒÃÇͨ³£½«×î³£ÓõÄÓ¦Ó÷ÖΪ2ÖÖÀàÐÍ£ºÁª»úÊÂÎñ´¦ÀíÀàÐÍ(OLTP)£¬¾ö²ßÖ§³Öϵͳ(DSS)¡£
Áª»úÊÂÎñ´¦Àí(OLTP)
¸ÃÀàÐ͵ÄÓ¦ÓÃÊǸßÍÌÍÂÁ¿£¬²åÈë¡¢¸üС¢É¾³ý²Ù×÷±È½Ï¶àµÄϵͳ£¬ÕâЩϵͳÒÔ²»¶ÏÔö³¤µÄ´óÈÝÁ¿Êý¾ÝΪÌØÕ÷£¬ËüÃÇÌṩ¸ø³É°ÙÓû§Í¬Ê±´æÈ¡£¬µäÐ͵ÄOLTPϵͳÊǶ©Æ±ÏµÍ³£¬ÒøÐеÄÒµÎñϵͳ£¬¶©µ¥ÏµÍ³¡£OTLPµÄÖ÷ҪĿ±êÊÇ¿ÉÓÃÐÔ¡¢Ëٶȡ¢²¢·¢ÐԺͿɻָ´ÐÔ¡£
µ±Éè¼ÆÕâÀàϵͳʱ£¬±ØÐëÈ·±£´óÁ¿µÄ²¢·¢Óû§²»ÄܸÉÈÅϵͳµÄÐÔÄÜ¡£»¹ÐèÒª±ÜÃâʹÓùýÁ¿µÄË÷ÒýÓëcluster ±í£¬ÒòΪÕâЩ½á¹¹»áʹ²åÈëºÍ¸üвÙ×÷±äÂý¡£
¾ö²ßÖ§³Ö(DSS)
¸ÃÀàÐ͵ÄÓ¦Óý«´óÁ¿ÐÅÏ¢½øÐÐÌáÈ¡Ðγɱ¨¸æ£¬ÐÖú¾ö²ßÕß×÷³öÕýÈ·µÄÅжϡ£µäÐ͵ÄÇé¿öÊÇ£º¾ö²ßÖ§³Öϵͳ½«OLTPÓ¦ÓÃÊÕ¼¯µÄ´óÁ¿Êý¾Ý½øÐвéѯ¡£µäÐ͵ÄÓ¦ÓÃΪ¿Í»§ÐÐΪ·ÖÎöϵͳ(³¬ÊУ¬±£ÏÕµÈ)¡£
¾ö²ßÖ§³ÖµÄ¹Ø¼üÄ¿±êÊÇËٶȡ¢¾«È·ÐԺͿÉÓÃÐÔ¡£
¸ÃÖÖÀàÐ͵ÄÉè¼ÆÍùÍùÓëOLTPÉè¼ÆµÄÀíÄî±³µÀ¶ø³Û£¬Ò»°ã½¨ÒéʹÓÃÊý¾ÝÈßÓà¡¢´óÁ¿Ë÷Òý¡¢cluster table¡¢²¢ÐвéѯµÈ¡£
½üÄêÀ´£¬¸ÃÀàÐ͵ÄÓ¦ÓÃÖð½¥ÓëOLAP¡¢Êý¾Ý²Ö¿â½ôÃܵÄÁªÏµÔÚÒ»Æð£¬ÐγɵÄÒ»¸öеÄÓ¦Ó÷½Ïò¡£
Èý£ºSQLÓï¾ä´¦ÀíµÄ¹ý³Ì
ÔÚµ÷Õû֮ǰÎÒÃÇÐèÒªÁ˽âһЩ±³¾°ÖªÊ¶£¬Ö»ÓÐÖªµÀÕâЩ±³¾°ÖªÊ¶£¬ÎÒÃDzÅÄܸüºÃµÄÈ¥µ÷ÕûsqlÓï¾ä¡£
±¾½Ú½éÉÜÁËSQLÓï¾ä´¦ÀíµÄ»ù±¾¹ý³Ì£¬Ö÷Òª°üÀ¨£º
²éѯÓï¾ä´¦Àí
DMLÓï¾ä´¦Àí(insert, update, delete)
DDL Óï¾ä´¦Àí(create .. , drop .. , alter .. , )
ÊÂÎñ¿ØÖÆ(commit, rollback)
SQL Óï¾äµÄÖ´Ðйý³Ì(SQL Statement Execution)
ÔÚijЩÇé¿öÏ£¬OracleÔËÐÐsqlµÄ¹ý³Ì¿ÉÄÜÓëÏÂÃæÁгöµÄ¸÷¸ö½×¶ÎµÄ˳ÐòÓÐËù²»Í¬¡£
ÈçDEFINE½×¶Î¿ÉÄÜÔÚFETCH½×¶Î֮ǰ£¬ÕâÖ÷ÒªÒÀÀµÄãÈçºÎÊéд´úÂë¡£
¶ÔÐí¶àoracleµÄ¹¤¾ßÀ´Ëµ£¬ÆäÖÐijЩ½×¶Î»á×Ô¶¯Ö´ÐС£¾ø´ó¶àÊýÓû§²»ÐèÒª¹ØÐĸ÷¸ö½×¶ÎµÄϸ½ÚÎÊÌ⣬Ȼ¶ø£¬ÖªµÀÖ´Ðеĸ÷¸ö½×¶Î»¹ÊÇÓбØÒªµÄ£¬Õâ»á°ïÖúÄãд³ö¸ü¸ßЧµÄSQLÓï¾äÀ´£¬¶øÇÒ»¹¿ÉÒÔÈÃÄã²Â²â³öÐÔÄܲîµÄSQLÓï¾äÖ÷ÒªÊÇÓÉÓÚÄÄÒ»¸ö½×¶ÎÔì³ÉµÄ£¬È»ºóÎÒÃÇÕë¶ÔÕâ¸ö¾ßÌåµÄ½×¶Î£¬ÕÒ³ö½â¾öµÄ°ì·¨¡£
DMLÓï¾äµÄ´¦Àí
±¾½Ú¸ø³öÒ»¸öÀý×ÓÀ´ËµÃ÷ÔÚDMLÓï¾ä´¦ÀíµÄ¸÷¸ö½×¶Îµ½µ×·¢ÉúÁËʲôÊÂÇé¡£
¼ÙÉèÄãʹÓÃPro*C³ÌÐòÀ´ÎªÖ¸¶¨²¿ÃŵÄËùÓÐÖ°Ô±Ôö¼Ó¹¤×Ê¡£³ÌÐòÒѾÁ¬µ½ÕýÈ·µÄÓû§£¬Äã¿ÉÒÔÔÚÄãµÄ³ÌÐòÖÐǶÈëÈçϵÄSQLÓï¾ä£º
EXEC SQL UPDATE employees
SET salary = 1.10 * salary
WHERE department_id = :var_department_id;
var_department_idÊdzÌÐò±äÁ¿£¬ÀïÃæ°üº¬²¿Ãźţ¬ÎÒÃÇÒªÐ޸ĸò¿ÃŵÄÖ°
Ïà¹ØÎĵµ£º
using System.Data.SqlClient;
using System.Data.OleDb;
private void tsmiImportTeacherInfo_Click(object sender, EventArgs e)
{
DataSet ds;
  ......
ʹÓà TRUNCATE TABLE ɾ³ýËùÓÐÐÐ
ÈôҪɾ³ý±íÖеÄËùÓÐÐУ¬Ôò TRUNCATE TABLE Óï¾äÊÇÒ»ÖÖ¿ìËÙ¡¢ÓÐЧµÄ·½·¨¡£TRUNCATE TABLE Óë²»º¬ WHERE ×Ó¾äµÄ DELETE Óï¾äÀàËÆ¡£µ«ÊÇ£¬TRUNCATE TABLE Ëٶȸü¿ì£¬²¢ÇÒʹÓøüÉÙµÄϵͳ×ÊÔ´ºÍÊÂÎñÈÕÖ¾×ÊÔ´¡£
Óë DELETE Óï¾äÏàͬ£¬Ê¹Óà TRUNCATE TABLE Çå¿ÕµÄ±íµÄ¶¨ÒåÓëÆäË÷ÒýºÍÆäËû¹ØÁª¶ÔÏóÒ ......
ϵͳҪÇó½øÐÐSQLÓÅ»¯£¬¶ÔЧÂʱȽϵ͵ÄSQL½øÐÐÓÅ»¯£¬Ê¹ÆäÔËÐÐЧÂʸü¸ß£¬ÆäÖÐÒªÇó¶ÔSQLÖеIJ¿·Öin/not inÐÞ¸ÄΪexists/not exists
Ð޸ķ½·¨ÈçÏ£º
inµÄSQLÓï¾ä
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
from tab_oa_pub WHERE is_check=1 and
category_id in (sel ......
×öÊý¾Ý¿â¿ª·¢»ò¹ÜÀíµÄÈ˾³£Òª´´½¨´óÁ¿µÄ²âÊÔÊý¾Ý£¬¶¯²»¶¯¾ÍÐèÒªÉÏÍòÌõ£¬Èç¹ûÒ»ÌõÒ»ÌõµÄ¼È룬ÄÇ»áÀË·Ñ´óÁ¿µÄʱ¼ä£¬±¾ÎĽéÉÜÁËOracleÖÐÈçºÎͨ¹ýÒ»ÌõSQL¿ìËÙÉú³É´óÁ¿µÄ²âÊÔÊý¾ÝµÄ·½·¨¡£
²úÉú²âÊÔÊý¾ÝµÄSQLÈçÏ£º
SQL> select rownum as id,
2 &nbs ......
1¡¢SELECT ²éѯÓï¾äºÍÌõ¼þÓï¾ä
SELECT ²éѯ×ֶΠfrom ±íÃû WHERE Ìõ¼þ
²éѯ×ֶΣº¿ÉÒÔʹÓÃͨÅä·û* ¡¢×Ö¶ÎÃû¡¢×ֶαðÃû
±íÃû£º Êý¾Ý¿â.±íÃû £¬±íÃû
³£ÓÃÌõ¼þ£º = µÈÓÚ ¡¢<>²»µÈÓÚ¡¢in °üº¬ ¡¢ not in ²»°üº¬¡¢ like Æ¥Åä
BETWEEN ÔÚ·¶Î§ ¡¢ not BETWEE ......