sqlÖ®×óÁ¬½ÓÓÒÁ¬½Ó
內Á¬½Ó½öÑ¡³öÁ½ÕűíÖл¥ÏàÆ¥ÅäµÄ¼Ç¼£®Òò´Ë£¬Õâ»áµ¼ÖÂÓÐʱÎÒÃÇÐèÒªµÄ¼Ç¼ûÓаüº¬½øÀ´¡£
Ϊ¸üºÃµÄÀí½âÕâ¸ö¸ÅÄÎÒÃǽéÉÜÁ½¸ö±í×÷ÑÝʾ¡£ËÕ¸ñÀ¼Òé»áÖеÄÕþµ³±í(party)ºÍÒéÔ±±í(msp)¡£¸´ÖÆÄÚÈݵ½¼ôÌù°å´úÂë:
party(Code,Name,Leader)
Code: Õþµ³´úÂë
Name: Õþµ³Ãû³Æ
Leader: Õþµ³ÁìÐä
msp(Name,Party,Constituency)
Name: ÒéÔ±Ãû
Party: ÒéÔ±ËùÔÚÕþµ³´úÂë
Constituency: Ñ¡Çø¡¡¡¡
Ò»¡¢ÔÚ½éÉÜ×óÁ¬½Ó¡¢ÓÒÁ¬½ÓºÍÈ«Á¬½Óǰ£¬ÓÐÒ»¸öÊý¾Ý¿âÖÐÖØÒªµÄ¸ÅÄîÒª½éÉÜһϣ¬¼´¿ÕÖµ(NULL)¡£ÓÐʱ±íÖУ¬¸üÈ·ÇеÄ˵ÊÇijЩ×Ö¶ÎÖµ£¬¿ÉÄÜ»á³öÏÖ¿ÕÖµ, ÕâÊÇÒòΪÕâ¸öÊý¾Ý²»ÖªµÀÊÇʲôֵ»ò¸ù±¾¾Í²»´æÔÚ¡£¿ÕÖµ²»µÈͬÓÚ×Ö·û´®ÖеĿոñ£¬Ò²²»ÊÇÊý×ÖÀàÐ͵Ä0¡£Òò´Ë£¬ÅжÏij¸ö×Ö¶ÎÖµÊÇ·ñΪ¿Õֵʱ²»ÄÜʹÓÃ=,ÕâЩÅжϷû¡£±ØÐèÓÐרÓõĶÌÓIS NULL À´Ñ¡³öÓпÕÖµ×ֶεļǼ£¬Í¬Àí£¬¿ÉÓà IS NOT NULL Ñ¡³ö²»°üº¬¿ÕÖµµÄ¼Ç¼¡£
¡¡¡¡ÀýÈ磺ÏÂÃæµÄÓï¾äÑ¡³öÁËûÓÐÁìµ¼ÕßµÄÕþµ³¡££¨²»ÒªÆæ¹Ö£¬ËÕ¸ñÀ¼Òé»áÖÐȷʵ´æÔÚÕâÑùµÄÕþµ³£©¸´ÖÆÄÚÈݵ½¼ôÌù°å´úÂë:
SELECT code, name from party
WHERE leader IS NULL¡¡¡¡
ÓÖÈ磺һ¸öÒéÔ±±»¿ª³ý³öµ³£¬¿´¿´ËûÊÇË¡£(¼´¸ÃÒéÔ±µÄÕþµ³Îª¿ÕÖµ)¸´ÖÆÄÚÈݵ½¼ôÌù°å´úÂë:
SELECT name from msp
WHERE party IS NULL¡¡¡¡
¶þ¡¢ÈÃÎÒÃÇÑÔ¹éÕý´«£¬¿´¿´Ê²Ã´½Ð×óÁ¬½Ó¡¢ÓÒÁ¬½ÓºÍÈ«Á¬½Ó¡£
¡¡¡¡A left join£¨×óÁ¬½Ó£©°üº¬ËùÓеÄ×ó±ß±íÖеļǼÉõÖÁÊÇÓұ߱íÖÐûÓкÍËüÆ¥ÅäµÄ¼Ç¼¡£
¡¡¡¡Í¬Àí£¬Ò²´æÔÚ×ÅÏàͬµÀÀíµÄ right join£¨ÓÒÁ¬½Ó£©£¬¼´°üº¬ËùÓеÄÓұ߱íÖеļǼÉõÖÁÊÇ×ó±ß±íÖÐûÓкÍËüÆ¥ÅäµÄ¼Ç¼¡£ ¶øfull join(È«Á¬½Ó)¹ËÃû˼Ò壬×óÓÒ±íÖÐËùÓмǼ¶¼»áÑ¡³öÀ´¡£
¡¡¡¡½²µ½ÕâÀÓÐÈË¿ÉÄÜÒªÎÊ£¬µ½µ×ʲô½Ð£º°üº¬ËùÓеÄ×ó±ß±íÖеļǼÉõÖÁÊÇÓұ߱íÖÐûÓкÍËüÆ¥ÅäµÄ¼Ç¼¡£
¡¡¡¡ÎÒÃÇÀ´¿´Ò»¸öʵÀý£º
SELECT msp.name, party.name
from msp JOIN party ON party=code¡¡¡¡
Õâ¸öÊÇÎÒÃÇÉÏÒ»½ÚËùѧµÄJoin(×¢Ò⣺Ҳ½Ðinner join)£¬Õâ¸öÓï¾äµÄ±¾ÒâÊÇÁгöËùÓÐÒéÔ±µÄÃû×ÖºÍËûËùÊôÕþµ³¡£
¡¡¡¡ºÜÒź¶£¬ÎÒÃÇ·¢ÏָòéѯµÄ½á¹ûÉÙÁËÁ½¸öÒéÔ±£ºCanavan MSP, Dennis¡£ÎªÊ²Ã´£¬ÒòΪÕâÁ½¸öÒéÔ±²»ÊôÓÚÈκÎÕþµ³£¬¼´ËûÃǵÄÕþµ³×Ö¶Î(Party)Ϊ¿ÕÖµ¡£ÄÇôΪʲô²»ÊôÓÚÈκÎÕþµ³¾Í²é²»³öÀ´ÁË£¿ÕâÊÇÒòΪ¿ÕÖµÔÚ×÷¹Ö¡£ÒòΪÒéÔ±±íÖÐÕþµ³×Ö¶Î(Party)µÄ¿ÕÖµÔÚÕþµ³±íÖÐÕÒ²»µ½¶ÔÓ¦µÄ¼Ç¼×÷Æ¥Å䣬¼´from msp JOIN party
Ïà¹ØÎĵµ£º
1.ÏÈÆôÓà xp_cmdshell À©Õ¹´æ´¢¹ý³Ì£º
Use Master
GO
Exec sp_configure 'show advanced options', 1
GO
Reconfigure;
GO
sp_configure 'xp_cmdshell', 1
GO
Reconfigure;
GO
(×¢£ºÒòΪxp_cmdshellÊǸ߼¶Ñ¡ÏËùÒÔÕâÀïÆô¶¯xp_cmdshell£¬ÐèÒªÏȽ« show advanced ......
SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))
SELECT STR(@testFloat, 38, 2)
´ÓExcelÖе¼Èëµ½sql2000£¬ÓÐÒ»ÁГÁªÏµ·½Ê½”±ä³ÉÁËfloatÀàÐÍ£¬ÎÒÏëת»»³ÉnvarcharÀàÐÍ£¬ÓÃÏÂÃæµÄÓï¾ä
select convert(nvarchar(30),convert(int,ÁªÏµ·½Ê½)) from employee
go
//Êý¾ÝÒç³ö£¬²»ÐУ¡
select ......
Private Sub insert1_click()
Dim iCount As Integer
Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=8233;Initial Catalog=hskmis;Data Source=127.0.0.1"
cn.Open
cn.Execute ("delete temp9")
'¼ÆËã¸Ã±íÓжàÉÙÐ ......
Ò».Êý¾Ý¿ØÖÆÓï¾ä (DML) ²¿·Ö
1.Insert (ÍùÊý¾Ý±íÀï²åÈë¼Ç¼µÄÓï¾ä)
Insert INTO ±íÃû(×Ö¶ÎÃû1, ×Ö¶ÎÃû2, ……) VALUES ( Öµ1, Öµ2, ……);
&nb ......
×î½ü×öÁ˼¸¸öССͳ¼ÆµÄ±¨±í½çÃæ£¬ÓÉÓÚ.net²»´øgroup by µÄ¹¦ÄÜ£¬Í³¼ÆÆðÀ´ÓÐʱºòÏ൱²»±ã£¬±ã³Ã×Å˯×ŵÄʱºòдÁËÒ»¸öÀàËÆµÄ·½·¨¡£
Óв»×ãÖ®´¦»òÊÇÓиüºÃµÄ·½·¨»¹Íû´ó¼ÒÖ¸Õý¡£
ÖÁÓÚЧÂÊÈçºÎ£¿Î´Öª£¬ÒòΪ±¾È˵IJâÊÔÊý¾Ý¾ÍÊDZȽÏÉÙ¡£
/// <summary>
/// SQL Group by
/// </summary>
/// &l ......