SQLÓï¾ä»ã×Ü
SQL :Structured Query Language½á¹¹»¯²éѯÓïÑÔ
1.Select [Predicate] *(filed) from table/view Where ... Group by ... Having... Order by ... With ...
Predicate£º°üÀ¨all/Distinct/Distinctrow/Top£¬ÏÞÖƲéѯ½á¹û£»
As¿ÉÒÔÃüÃû±ðÃû£»
Where ... Ö¸¶¨Ä³Ð©Ìõ¼þ£¬½«ËùÓзûºÏÌõ¼þµÄ¼Ç¼¹ýÂ˳öÀ´£¬ÏÂÃæÊÇSQLÌṩµÄÔËËã·ûºÍ¹Ø¼ü×Ö¡£
ËãÊõÔËËã·û£º+¡¢-¡¢*¡¢/¡¢%
±È½ÏÔËËã·û:=¡¢<¡¢>¡¢>=¡¢<=¡¢<>
×Ö·û´®²Ù×÷±È½Ï·û£ºlike¡¢ not like
Âß¼²Ù×÷·û£ºand¡¢or¡¢not
ÖµµÄÓò£ºBetween¡¢not between
ÖµµÄÁÐ±í£ºin£¬not in
δ֪µÄÖµ£ºis null¡¢is not null
Group by ... ¶àºÍ¾ÛºÏº¯ÊýÒ»ÆðʹÓã¬sum¡¢avg¡¢count¡¢max¡¢min¡¢first¡¢last¡£
Having... ×é»ò¾ÛºÏº¯ÊýµÄÌõ¼þÅжÏ
2.¸ß¼¶²éѯ
UnionºÏ²¢¶à¸ö½á¹û¼¯¡£
Inner joinÄÚÁª½Ó²éѯ¡£
Outer joinÍâÁª½Ó²éѯ¡££¨Left/Right Outer join£©
Trasform½»²æ±í²éѯ¡£
Àý£ºTrasform sum£¨ÏúÁ¿£© as ÏúÁ¿ select ÓïÑÔÀàÐÍ from ͼÊéÏúÊÛ group by ÓïÑÔÀà±ð pivot ÏúÊÛʱ¼ä
Case¾²Ì¬½»²æ±í¡££¨Case ... When ... Then ..else null end£© as [...]
Óô洢¹ý³ÌʵÏÖ¶¯Ì¬½»²æ±í¡£
3.ÆäËû
¸ñʽ»¯º¯ÊýFormat£¨²ÎÊý£¬¸ñʽ£© ---²»Ö§³ÖSQL server
×Ö·û´®º¯Êý£ºMid¡¢Len
ÈÕÆÚº¯Êý£ºDateDiff
Ïà¹ØÎĵµ£º
תÔØ˵Ã÷£º¸øÕýÔÚ×öϵͳºÍÏë×öϵͳµÄÈË£¬SQL²©´ó¾«ÉÄãÃÇ»áÓõ½µÄ¡£——mAysWINd
Ò»¸öÌâÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµ ......
Ò»£¬
bcpÃüÁîÏê½â
bcpÃüÁîÊÇSQL ServerÌṩµÄÒ»¸ö¿ì½ÝµÄÊý¾Ýµ¼Èëµ¼³ö¹¤¾ß¡£Ê¹ÓÃËü²»ÐèÒªÆô¶¯ÈκÎͼÐιÜÀí¹¤¾ß¾ÍÄÜÒÔ¸ßЧµÄ·½Ê½µ¼Èëµ¼³öÊý¾Ý¡£bcpÊÇSQL ServerÖиºÔðµ¼Èëµ¼³öÊý¾ÝµÄÒ»¸öÃüÁîÐй¤¾ß£¬ËüÊÇ»ùÓÚDB-LibraryµÄ£¬²¢ÇÒÄÜÒÔ²¢Ðеķ½Ê½¸ßЧµØµ¼Èëµ¼³ö´óÅúÁ¿µÄÊý¾Ý¡£bcp¿ÉÒÔ½«Êý¾Ý¿âµÄ±í»òÊÓͼֱ½Óµ¼³ö ......
´Ó×òÌ쿪ʼ¾ÍÒ»Ö±ÔÚÍøÉϲéÕÒÊý¾Ý¿âΪSQL server 2000 µÄJSP¿ª·¢»·¾³µÄ´î½¨£¬µ½ÏÖÔÚΪֹÖÕÓÚÄܹ»Õý³£Áª½Ó²¢·ÃÎÊ¡£ÒÔÏÂÊǴµÄ²½Ö裺
1¡¢´ÓÍøÉÏÏÂÔØÒ»¸öSQL server 2000 ¸öÈË°æµÄ°²×°°ü£¬°´ÕÕÌáʾ²Ù×÷£¬µ±ÔÚÉí·ÖÑé֤ʱ£¬Ñ¡Ôñ»ìºÏģʽ£¬Óû§saµÄÃÜÂë¿ÉÒÔΪ¿Õ£¬Ò²¿ÉÒÔ²»Îª¿Õ¡£
2¡¢´ÓÍøÉÏÏÂÔØÒ»¸öSQ ......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄsql serverÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
¡¡¡¡Æäʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕ ......
ʵÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¡¡¡¡½¨Á¢Ò»¸ö Web Ó¦Ó㬷ÖÒ³ä¯ÀÀ¹¦Äܱز»¿ÉÉÙ¡£Õâ¸öÎÊÌâÊÇÊý¾Ý¿â´¦ÀíÖÐÊ®·Ö³£¼ûµÄÎÊÌâ¡£¾µäµÄÊý¾Ý·ÖÒ³·½·¨ÊÇ:ADO ¼Í¼¼¯·ÖÒ³·¨£¬Ò²¾ÍÊÇÀûÓÃADO×Ô´øµÄ·ÖÒ³¹¦ÄÜ£¨ÀûÓÃÓα꣩À´ÊµÏÖ·ÖÒ³¡£µ«ÕâÖÖ·ÖÒ³·½·¨½öÊÊÓÃÓÚ½ÏСÊý¾ÝÁ¿µÄÇéÐΣ¬ÒòΪÓα걾ÉíÓÐȱµã£ºÓαêÊÇ´æ·ÅÔÚÄÚ´æÖУ¬º ......