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 Server™ ÔÊÐíΪ Insert¡¢Update¡¢Delete ´´½¨´¥·¢Æ÷£¬¼´µ±ÔÚ±íÖвåÈë¡¢¸üС¢É¾³ý¼Ç¼ʱ£¬´¥·¢Ò»¸ö»òһϵÁÐ T-SQLÓï¾ä¡£
´¥·¢Æ÷¿ÉÒÔÔÚ²éѯ·ÖÎöÆ÷Àï´´½¨£¬Ò²¿ÉÒÔÔÚ±íÃûÉϵãÓÒ¼ü->“ËùÓÐÈÎÎñ”->“¹ÜÀí´¥·¢Æ÷”À´´´½¨£¬²»¹ý¶¼ÊÇҪд T-SQL ......
¾µäSQLÓï¾ä
1¡¢ËµÃ÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb) (Access¿ÉÓÃ)
·¨Ò»£ºselect * into b from a where 1<>1
//a±ØÐëÊÇÒѾ´æÔÚµÄ±í£¬µ«ÊÇb¿ÉÒÔ²»´æÔÚ£¬µ±b²»´æÔÚʱ£¬ÏµÍ³»á×Ô¼º´´½¨±íb,¸Ã·½·¨Ö»»á¸´ÖƱíµÄ½á¹¹£¬¶ø²»»á¸´ÖƱíµÄÊý¾Ý
·¨¶þ£ºselect top 0 * int ......
תÔØ˵Ã÷£º¸øÕýÔÚ×öϵͳºÍÏë×öϵͳµÄÈË£¬SQL²©´ó¾«ÉÄãÃÇ»áÓõ½µÄ¡£——mAysWINd
Ò»¸öÌâÄ¿Éæ¼°µ½µÄ50¸öSqlÓï¾ä
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµ ......
windows ¼¯³ÉÑéÖ¤£º
<connectionStrings>
<add name="ConnectionStr" connectionString="Data Source=CAIPENG-PC;database=Test;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
»òÕß
<connectionStrings>
<add name=" ......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄsql serverÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
¡¡¡¡Æäʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕ ......