SQL³£ÓÃÓï¾ä
1¡¢Óëgroup by´îÅäʹÓõĺ¯Êý
ÔÚÊý¾Ý¿âÖУ¬ÎÒÃÇ¿ÉÒÔʹÓÃGROUP BYº¯Êý°ÑÊý¾Ý×éºÏÔÚÒ»Æ𣬴Ӷø»ñµÃ×ܼÆÐÅÏ¢¡£¿ÉÒ԰Ѵ˹¦ÄÜ¿´³ÉÊÇÒ»ÖÖµ±Êý¾Ý´ÓÊý¾Ý¿âÖзµ»Øʱ°ÑÏàͬÀàÐ͵ÄÐÅÏ¢¼¯Öе½Ò»ÆðµÄÄÜÁ¦¡£ÏÂÃæ¸ø³öÁËÍêÕûÁÐ±í¡£
avg([distinct]column_name)
ÇóËùÓйÍԱнˮµÄƽ¾ùÖµ¡£
select AVG(emp_salary)
from employee;
ÇóÈ¡column_nameÖеÄËùÓÐÖµµÄƽ¾ùÖµ¡£Èç¹ûʹÓÃDISTINCTÑ¡ÏÔòֻʹÓò»Í¬µÄ·Ç¿ÕµÄÊýÖµ¡£
count([distinct]value)
ͳ¼Æ¹ÍÔ±µÄÊýÄ¿¡£
select COUNT(emp_name)
from employee;
ͳ¼ÆÑ¡ÔñÐеÄÊýÄ¿£¬²¢ºöÂÔVALUEÖеĿÕÖµ¡£Èç¹ûʹÓÃÁËDISTINCTÑ¡Ïî
Ôòֻͳ¼Æ²»Í¬µÄ·Ç¿ÕÊýÖµ¡£VALUE¿ÉÒÔÊÇÁÐÃû£¬Ò²¿ÉÒÔÊDZí´ïʽ¡£
max(value)
·µ»ØнˮµÄ×î´óÖµ
select MIN
emp_salary)
from employee;
´ÓÑ¡¶¨µÄVALUEÖÐÑ¡³öÊýÖµ/×Ö·ûµÄ×î´óÖµ£¬ºöÂÔ¿ÕÖµ¡£VALUEÒªÇóͬÉÏ¡£
min(value)
·µ»ØнˮµÄ×îСֵ
select MIN(emp_salsry)
from employee;
´ÓÑ¡¶¨µÄVALUEÖÐÑ¡³öÊýÖµ/×Ö·ûµÄ×îСֵ£¬ºöÂÔ¿ÕÖµ¡£VALUEÒªÇóͬÉÏ¡£
stddev(value)·µ»Ø¹ÍԱнˮµÄ±ê׼ƫ²î
select STDDEV(emp_salary)
from employee;
´ÓÑ¡ÔñµÄVALUEµÄ±ê×¼Öзµ»Ø±ê׼ƫ²î¡£
variance([distinct]value)
·µ»Ø¹ÍԱнˮµÄ·½²î
select VARIANCE(emp_salary)
from employee;
·µ»ØËùÑ¡ÐеÄËùÓÐÊýÖµµÄ·½²î£¬ºöÂÔVALUEµÄ¿ÕÖµ¡£DISTINCTºÍVALUEÒª
Çóͬǰ¡£
2¡¢Öµ×ª»»£¨case……when……then……else……end£©
select name as ÐÕÃû,case sex when boy then ÄÐ else Å® end as ÐÔ±ð from student;
½«²éѯ³öµÄÐÔ±ðboyת»»ÎªÄУ¬ÆäËüµÄת»»ÎªÅ®¡£
Ïà¹ØÎĵµ£º
1¡£Sysdate µ±Ç°ÈÕÆÚºÍʱ¼ä
SQL> Select sysdate from dual;
SYSDATE
----------
21-6ÔÂ -05
2¡£Last_day ±¾ÔÂ×îºóÒ»Ìì
SQL> Select last_day(sysdate) from dual;
LAST_DAY(S
----------
30-6ÔÂ -05
3¡£Add_months(d,n) µ±Ç°ÈÕÆÚdºóÍÆn¸öÔÂ
ÓÃÓÚ´ÓÒ»¸öÈÕÆÚÖµÔö¼Ó»ò¼õÉÙһЩÔ·Ý
date_value:=add_mont ......
×Ô¶¯ÌáʾµÄ¿ì½Ý¼ü£ºF6
¿ÉÒÔ¸ü¸Ä£ºTools->Preferences->User interface->Key Configuration ,ÔÚItemÁÐÀïÕÒµ½“Tools/Code Assistant”,ÔÚºóÃæ¶ÔÓ¦µÄ¿ì½Ý¸Ä³É×Ô¼ºÏ²»¶µÄ¡£
ĬÈÏÊDz»ÄÜ×Ô¶¯Ìá½»µÄ£¬°´ F10 ¼ü¿ÉÒÔÔÚ×ö¸ü¸ÄºóÌá½»¡£ ......
1¡¢SQL ServerÈÕÆÚÊý¾Ý¿âÄÚ²¿±íʾ¼°²éѯÊä³ö£º
SQL ServerÄÚ²¿¶ÔDateTimeÀàÐ͵ıíʾÊÇÓÃ8¸ö×Ö½ÚÀ´±íʾ£¬ÆäÖÐÇ°4¸ö×Ö½Ú±íʾµÄÊýֵΪ×Ô1900Äê1ÔÂ1ÈÕÁãʱÒÔÀ´µÄÌìÊý£¬ºóËĸö×Ö½ÚÊÇÌìÊýÖ®ÍâµÄÓжàÉÙ¸ö1/300Ã룬ÕâҲ˵Ã÷SQL ServerÄÚ²¿±íʾʱ¼äµÄ¾«¶ÈΪ1/300Ã룬Լ3.3ºÁÃë¡£
³£¹æµÄSQL²éѯ£¬Õý³£ÈÕÆÚÊä³ö¸ñʽΪ:yyyy-MM-dd hh: ......
selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
´´½¨Ò»¸ö¼òµ¥µÄ´¥·¢Æ÷
´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâµÄ´æ´¢¹ý³Ì£¬ÀàËÆÓÚʼþº¯Êý£¬SQL Server? ÔÊÐíΪ INSERT¡¢UPDATE¡¢DELETE ´´½¨´¥·¢Æ÷£¬¼´µ±ÔÚ±íÖвåÈë¡¢¸üС¢É¾³ý¼Ç¼ʱ£¬´¥·¢Ò»¸ö»òһϵÁÐ T-SQLÓï¾ä¡£
´¥·¢Æ÷¿ÉÒÔÔÚ²éѯ·ÖÎöÆ÷Àï´´½¨£¬Ò²¿ÉÒÔÔÚ±íÃûÉϵãÓÒ¼ü->“ËùÓÐÈÎÎñ”->“¹ÜÀí´¥·¢Æ÷”À´´´½¨£¬²» ......