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

SQLʹÓü¼ÇÉ

Ò»¡¢¼Ó¿ìsqlµÄÖ´ÐÐËÙ¶È
¡¡¡¡1.select Óï¾äÖÐʹÓÃsort,»òjoin
¡¡¡¡Èç¹ûÄãÓÐÅÅÐòºÍÁ¬½Ó²Ù×÷£¬Äã¿ÉÒÔÏÈselectÊý¾Ýµ½Ò»¸öÁÙʱ±íÖУ¬È»ºóÔÙ¶ÔÁÙʱ±í½øÐд¦Àí¡£ÒòΪÁÙʱ±íÊǽ¨Á¢ÔÚÄÚ´æÖУ¬ËùÒԱȽ¨Á¢ÔÚ´ÅÅÌÉϱí²Ù×÷Òª¿ìµÄ¶à¡£
¡¡¡¡È磺
SELECT time_records.*, case_name¡¡
from time_records, OUTER cases¡¡
WHERE time_records.client = "AA1000"¡¡
AND time_records.case_no = cases.case_no¡¡
ORDER BY time_records.case_no
¡¡¡¡Õâ¸öÓï¾ä·µ»Ø34¸ö¾­¹ýÅÅÐòµÄ¼Ç¼£¬»¨·ÑÁË5·ÖÖÓ42Ãë¡£¶ø£º
SELECT time_records.*, case_name¡¡
from time_records, OUTER cases¡¡
WHERE time_records.client = "AA1000"¡¡
AND time_records.case_no = cases.case_no¡¡
INTO temp foo;¡¡
SELECT * from foo ORDER BY case_no¡¡
·µ»Ø34Ìõ¼Ç¼£¬Ö»»¨·ÑÁË59Ãë¡£
¡¡¡¡2.ʹÓÃnot in »òÕßnot exists Óï¾ä
¡¡¡¡ÏÂÃæµÄÓï¾ä¿´ÉÏȥûÓÐÈκÎÎÊÌ⣬µ«ÊÇ¿ÉÄÜÖ´Ðеķdz£Âý£º
SELECT code from table1¡¡
WHERE code NOT IN ( SELECT code from table2
Èç¹ûʹÓÃÏÂÃæµÄ·½·¨£º
SELECT code, 0 flag¡¡
from table1¡¡
INTO TEMP tflag;¡¡
È»ºó£º
UPDATE tflag SET flag = 1
WHERE code IN ( SELECT code¡¡from table2¡¡
WHERE tflag.code = table2.code ;
È»ºó£º
SELECT * from¡¡
tflag¡¡
WHERE flag = 0;
¡¡¡¡¿´ÉÏÈ¥Ò²ÐíÒª»¨·Ñ¸ü³¤µÄʱ¼ä£¬µ«ÊÇÄã»á·¢ÏÖ²»ÊÇÕâÑù¡£
¡¡¡¡ÊÂʵÉÏÕâÖÖ·½Ê½Ð§Âʸü¿ì¡£ÓпÉÄܵÚÒ»ÖÖ·½·¨Ò²»áºÜ¿ì£¬ÄÇÊÇÔÚ¶ÔÏà¹ØµÄÿ¸ö×ֶζ¼½¨Á¢ÁËË÷ÒýµÄÇé¿öÏ£¬µ«ÊÇÄÇÏÔÈ»²»ÊÇÒ»¸öºÃµÄ×¢Òâ¡£
¡¡¡¡3.±ÜÃâʹÓùý¶àµÄ“or"
¡¡¡¡Èç¹ûÓпÉÄܵϰ£¬¾¡Á¿±ÜÃâ¹ý¶àµØÊ¹ÓÃor£º WHERE a = "B" OR a = "C"
¡¡¡¡Òª±È WHERE a IN ("B","C") Âý¡£ ÓÐʱÉõÖÁUNION»á±ÈORÒª¿ì¡£
¡¡¡¡4.ʹÓÃË÷Òý
¡¡¡¡ÔÚËùÓеÄjoinºÍorder by µÄ×Ö¶ÎÉϽ¨Á¢Ë÷Òý¡£ ÔÚwhereÖеĴó¶àÊý×ֶν¨Á¢Ë÷Òý¡£
WHERE datecol >= "this/date" AND datecol
<= "that/date"¡¡Òª±È¡¡WHERE datecol BETWEEN
"this/date" AND "that/date" Âý¡£¡¡¡¡
¡¡¡¡5.ÔÚ·¢Éú´íÎóµÄʱºòÖÕÖ¹sql½Å±¾µÄÖ´ÐÐ
¡¡¡¡Èç¹ûÄã´´½¨ÁËÒ»¸ösql½Å±¾£¬²¢ÇÒÔÚUNIXÃüÁîÐÐÖÐʹÓÃÒÔϵķ½Ê½À´Ö´ÐÐÕâ¸ö½Å±¾£º
¡¡¡¡$ dbaccess <½Å±¾ÎļþÃû>
¡¡¡¡Õâʱ£¬½Å±¾ÖеÄËùÓеÄsqlÓï¾ä¶¼»á±»Ö´ÐУ¬¼´Ê¹ÆäÖеÄÒ»¸ösqlÓï¾ä·¢ÉúÁË´íÎó¡£ÀýÈ磬Èç¹ûÄã½Å±¾ÖÐΪÈçϵÄÓï¾ä£º
BEGIN WORK;¡¡
INSERT INTO history¡¡
SELECT *¡¡


Ïà¹ØÎĵµ£º

SQL ServerÖеÄCTE

 1¡¢¹«Óñí±í´ïʽ (CTE) ¿ÉÒÔÈÏΪÊÇÔÚµ¥¸ö SELECT¡¢INSERT¡¢UPDATE¡¢DELETE »ò CREATE VIEW Óï¾äµÄÖ´Ðз¶Î§ÄÚ¶¨ÒåµÄÁÙʱ½á¹û¼¯¡£CTE ÓëÅÉÉú±íÀàËÆ£¬¾ßÌå±íÏÖÔÚ²»´æ´¢Îª¶ÔÏ󣬲¢ÇÒÖ»ÔÚ²éѯÆÚ¼äÓÐЧ¡£ÓëÅÉÉú±íµÄ²»Í¬Ö®´¦ÔÚÓÚ£¬CTE ¿É×ÔÒýÓ㬻¹¿ÉÔÚͬһ²éѯÖÐÒýÓöà´Î¡£
¡¡¡¡CTE ¿ÉÓÃÓÚ£º
¡¡¡¡´´½¨µÝ¹é²éѯ¡£ÓйØÏêϸР......

SQLÈÕÆÚºÍʱ¼äº¯Êý

 
--»ùÓÚʱ¼äSQLº¯Êý--
getdate() --·µ»Øµ±Ç°ÏµÍ³ÈÕÆÚºÍʱ¼ä¡£
DateAdd --ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ¡£
DATEADD ( datepart , number, date )
--Àý£ºÏòµ±ÌìµÄʱ¼äÔö¼Ó5Ìì
select dateadd(dd,5,getdate())
datediff --·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý¡£]
---ÀýÈç
 & ......

sql server ´æ´¢¹ý³Ì¼°´¥·¢Æ÷

Ò»Ö±¶¼ÏëÕÒ¸öʱ¼äÑо¿Ò»Ï£¬½ñÌ컨ÁËÒ»Ììʱ¼äȥʵ¼ùÁËһϣ¬·Ç³£ÓÐÓ᣿ÉÒÔ˵ÊÇÊý¾Ý¿âÀïÃæµÄ¾«»ª°É¡£»¹ºÃÎÒ½ñÌìȥŪÁ˼¸Ï£¬Ëä˵¶¼ÊÇЩ¼òµ¥µÄÓï¾ä£¬µ«ÊÇ×îÆðÂëÖªµÀÒÔºóÔÚÏîÄ¿¸ÃÔõôȥ×öÁË¡£ Ïл°ÉÙ˵¡£½øÈëÕýÌâ¡£
1.´´½¨SQL´æ´¢¹ý³Ì:
   CREATE PROCEDURE stu_proc1//Ö¸¶¨¹ý³ÌÃû
    @Sno varc ......

SQLν´Ê

1¡¢Î½´Ê ν´ÊÔÊÐíÄú¹¹ÔìÌõ¼þ£¬ÒÔ±ãÖ»´¦ÀíÂú×ãÕâЩÌõ¼þµÄÄÇЩÐС£
2¡¢Ê¹Óà IN ν´Ê
ʹÓà IN ν´Ê½«Ò»¸öÖµÓëÆäËû¼¸¸öÖµ½øÐбȽϡ£ÀýÈ磺
SELECT NAME from STAFF WHERE DEPT IN (20, 15)
´ËʾÀýÏ൱ÓÚ£º
SELECT NAME from STAFF WHERE DEPT = 20 OR DEPT = 15
µ±×Ó²éѯ·µ»ØÒ»×éֵʱ£¬¿ÉʹÓà IN ºÍ NOT IN ÔËËã·û¡£ÀýÈç£ ......

1.The SQL introuduction

Introducing Oracle Database 11g
List the features of Oracle Database 11g
Discuss the basic design, theoretical and physical aspects of a relational database
Categorize the different types of SQL statements
Describe the data set used by the course
Log onto the database using the SQL Develope ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ