SQL³£ÓÃÃüÁîʹÓ÷½·¨
(1) Êý¾Ý¼Ç¼ɸѡ£º
sql="select * from Êý¾Ý±í where ×Ö¶ÎÃû=×Ö¶ÎÖµ order by ×Ö¶ÎÃû [desc]"
sql="select * from Êý¾Ý±í where ×Ö¶ÎÃû like '%×Ö¶ÎÖµ%' order by ×Ö¶ÎÃû [desc]"
sql="select top 10 * from Êý¾Ý±í where ×Ö¶ÎÃû order by ×Ö¶ÎÃû [desc]"
sql="select * from Êý¾Ý±í where ×Ö¶ÎÃû in ('Öµ1','Öµ2','Öµ3')"
sql="select * from Êý¾Ý±í where ×Ö¶ÎÃû between Öµ1 and Öµ2"
(2) ¸üÐÂÊý¾Ý¼Ç¼£º
sql="update Êý¾Ý±í set ×Ö¶ÎÃû=×Ö¶ÎÖµ where Ìõ¼þ±í´ïʽ"
sql="update Êý¾Ý±í set ×Ö¶Î1=Öµ1,×Ö¶Î2=Öµ2 …… ×Ö¶În=Öµn where Ìõ¼þ±í´ïʽ"
(3) ɾ³ýÊý¾Ý¼Ç¼£º
sql="delete from Êý¾Ý±í where Ìõ¼þ±í´ïʽ"
sql="delete from Êý¾Ý±í" (½«Êý¾Ý±íËùÓмǼɾ³ý)
(4) Ìí¼ÓÊý¾Ý¼Ç¼£º
sql="insert into Êý¾Ý±í (×Ö¶Î1,×Ö¶Î2,×Ö¶Î3 …) valuess (Öµ1,Öµ2,Öµ3 …)"
sql="insert into Ä¿±êÊý¾Ý±í select * from Ô´Êý¾Ý±í" (°ÑÔ´Êý¾Ý±íµÄ¼Ç¼Ìí¼Óµ½Ä¿±êÊý¾Ý±í)
(5) Êý¾Ý¼Ç¼ͳ¼Æº¯Êý£º
AVG(×Ö¶ÎÃû) µÃ³öÒ»¸ö±í¸ñÀ¸Æ½¾ùÖµ
COUNT(*|×Ö¶ÎÃû) ¶ÔÊý¾ÝÐÐÊýµÄͳ¼Æ»ò¶ÔijһÀ¸ÓÐÖµµÄÊý¾ÝÐÐÊýͳ¼Æ
MAX(×Ö¶ÎÃû) È¡µÃÒ»¸ö±í¸ñÀ¸×î´óµÄÖµ
MIN(×Ö¶ÎÃû) È¡µÃÒ»¸ö±í¸ñÀ¸×îСµÄÖµ
SUM(×Ö¶ÎÃû) °ÑÊý¾ÝÀ¸µÄÖµÏà¼Ó
ÒýÓÃÒÔÉϺ¯ÊýµÄ·½·¨£º
sql="select sum(×Ö¶ÎÃû) as ±ðÃû from Êý¾Ý±í where Ìõ¼þ±í´ïʽ"
set rs=conn.excute(sql)
Óà rs("±ðÃû" »ñȡͳµÄ¼ÆÖµ£¬ÆäËüº¯ÊýÔËÓÃͬÉÏ¡£
(5) Êý¾Ý±íµÄ½¨Á¢ºÍɾ³ý£º
Create TABLE Êý¾Ý±íÃû³Æ(×Ö¶Î1 ÀàÐÍ1(³¤¶È),×Ö¶Î2 ÀàÐÍ2(³¤¶È) …… )
Àý£ºCreate TABLE tab01(name varchar(50),datetime default now())
Drop TABLE Êý¾Ý±íÃû³Æ (ÓÀ¾ÃÐÔɾ³ýÒ»¸öÊý¾Ý±í)
19. ¼Ç¼¼¯¶ÔÏóµÄ·½·¨£º
rs.movenext ½«¼Ç¼ָÕë´Óµ±Ç°µÄλÖÃÏòÏÂÒÆÒ»ÐÐ
rs.moveprevious ½«¼Ç¼ָÕë´Óµ±Ç°µÄλÖÃÏòÉÏÒÆÒ»ÐÐ
rs.movefirst ½«¼Ç¼ָÕëÒƵ½Êý¾Ý±íµÚÒ»ÐÐ
rs.movelast ½«¼Ç¼ָÕëÒƵ½Êý¾Ý±í×îºóÒ»ÐÐ
rs.absoluteposition=N ½«¼Ç¼ָÕëÒƵ½Êý¾Ý±íµÚNÐÐ
rs.absolutepage=N ½«¼Ç¼ָÕëÒƵ½µÚNÒ³µÄµÚÒ»ÐÐ
rs.pagesize=N ÉèÖÃÿҳΪNÌõ¼Ç¼
rs.pagecount ¸ù¾Ý pagesize µÄÉèÖ÷µ»Ø×ÜÒ³Êý
rs.recordcount ·µ»Ø¼Ç¼×ÜÊý
rs.bof ·µ»Ø¼Ç¼ָÕëÊÇ·ñ³¬³öÊý¾Ý±íÊ׶ˣ¬true±íʾÊÇ£¬falseΪ·ñ
rs.eof ·µ»Ø¼Ç¼ָÕëÊÇ·ñ³¬³öÊý¾Ý±íÄ©¶Ë£¬true±íʾÊÇ£
Ïà¹ØÎĵµ£º
ÎÒÃÇÔÚ±àдMISϵͳºÍWebÓ¦ÓóÌÐòµÈϵͳʱ£¬¶¼Éæ¼°µ½ÓëÊý¾Ý¿âµÄ½»»¥£¬Èç¹ûÊý¾Ý¿âÖÐÊý¾ÝÁ¿ºÜ´óµÄ»°£¬Ò»´Î¼ìË÷ËùÓеļǼ£¬»áÕ¼ÓÃϵͳºÜ´óµÄ×ÊÔ´£¬Òò´ËÎÒÃdz£³£²ÉÓã¬ÐèÒª¶àÉÙÊý¾Ý¾ÍÖ»´ÓÊý¾Ý¿âÖÐÈ¡¶àÉÙÌõ¼Ç¼£¬¼´²ÉÓ÷ÖÒ³Óï¾ä¡£¸ù¾Ý×Ô¼ºÊ¹ÓùýµÄÄÚÈÝ£¬°Ñ³£¼ûÊý¾Ý¿âSQL Server,OracleºÍMySQLµÄ·ÖÒ³Óï¾ä£¬´ÓÊý¾Ý¿â±íÖÐµÄµÚ ......
參¿¼: http://brightsky006.blog.163.com/blog/static/22583668200962195059485/
°ÑSQL 2005µÄ±¸·ÝÎļþµ¼Èëµ½SQL 2000
ѧϰÕä²Ø 2009-07-21 09:50 ÔĶÁ616 ÆÀÂÛ0
×ֺţº ´ó´ó ÖÐÖРСС
µçÄÔÉÏ°²×°ÁËSQL Server 2005£¬ÏÖÔÚÏë»»»ØSQL2000À´£¬ ......
ÕýÔÚ¼ÓÔØÊý¾Ý...
¡¡¡¡1.°´ÐÕÊϱʻÅÅÐò: select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
¡¡¡¡2.Êý¾Ý¿â¼ÓÃÜ: select encrypt(’ÔʼÃÜÂë’) select pwdencrypt(’ÔʼÃÜÂë’) select pwdcompare(’ÔʼÃÜÂë’,’¼ÓÃܺóÃÜÂë’) = 1--Ïàͬ£»·ñÔ ......
ʹÓÃscott/tigerÓû§ÏµÄemp±íºÍdept±íÍê³ÉÏÂÁÐÁ·Ï°£¬±íµÄ½á¹¹ËµÃ÷ÈçÏÂ
empÔ±¹¤±í(empnoÔ±¹¤ºÅ/enameÔ±¹¤ÐÕÃû/job¹¤×÷/mgrÉϼ¶±àºÅ/hiredateÊܹÍÈÕÆÚ/salн½ð/commÓ¶½ð/deptno²¿ÃűàºÅ)
dept²¿Ãűí(deptno²¿ÃűàºÅ/dname²¿ÃÅÃû³Æ/locµØµã)
¹¤×Ê £½ н½ð £« Ó¶½ð
1£®ÁгöÖÁÉÙÓÐÒ»¸öÔ±¹¤µÄËùÓв¿ÃÅ
2£®Áгöн½ð±È& ......
½ñÌìÔÚµçÄÔÉÏ°²×°ÁËVisual Studio 2005£¬ÀïÃæ×Ô´øÁËÒ»¸öSQL Server 2005 Express¡£°²×°Íê³ÉÖ®ºó½øÈëVS£¬Ð½¨Ò»¸öÊý¾Ý¿âÏîÄ¿£¬Ôړн¨Êý¾Ý¿âÒýÓÔһ²½Óöµ½ÁËÆæ¹ÖµÄÎÊÌâ¡£
ÔÚ·þÎñÆ÷ÃûµÄÏÂÀÁбíÀïÃ棬ͨ¹ýµã»÷“ˢД°´Å¥¿ÉÒÔÏÔʾµ±Ç°Í¨¹ýÍøÂç¿ÉÒÔÁ¬½Óµ½µÄÊý¾Ý¿âµÄÐÅÏ¢£¬ÆäÖÐÓÐÎÒµÄÖ÷»ú¡£È»¶ø£¬Ñ¡ÔñÁ ......