SQLÓïÑÔ»ù´¡ Êý¾Ý²éѯ
ÔÚSQLÓïÑÔÖУ¬Ö»ÌṩÁËÒ»¸ö¶¯´ÊSELECTÓÃÀ´½øÐÐÊý¾Ý²éѯ²Ù×÷£¬µ«Õâ¸ö¶¯´ÊµÄ²ÎÊýÊ®·Ö¸´ÔÓ£¬ÇÒÄÜǶÌ×ʹÓã¬ÆäͨÓøñʽÈçÏ£º
SELECT [All|Distinct]<Ä¿±êÁбí´ïʽ>[£¬<Ä¿±êÁбí´ïʽ>]...
from <±íÃû»òÊÓͼÃû>[£¬<±íÃû»òÊÓͼÃû>]...
[WHERE<Ìõ¼þ±í´ïʽ>]
[GROUP BY<ÁÐÃû1>[HAVING<Ìõ¼þ±í´ïʽ>]]
[ORDER BY<ÁÐÃû2>[ASC|DESC]];
1¡¢µ¥±í²éѯ
Êý¾Ý²éѯÖ÷Ҫͨ¹ýһЩÀý×ÓÀ´ËµÃ÷SELECTÓï¾äµÄʹÓ᣼ÙÉèÓÐѧÉú±íStudent£¨Sno£¬Sname£¬Ssex£¬Sage£¬Sdept£©£¬»¹Óпγ̱íCourse£¨Cno£¬Cname£¬Credit£¬Cpno£©ºÍÑ¡ÐÞ±íSC£¨Sno£¬Cno£¬Grade£©¡£ÆäÖÐCnoΪ¿Î³Ì±í£¬CnameΪ¿Î³ÌÃû³Æ£¬CpnoΪÏÈÐ޿γ̺ţ¬CreditΪѧ·Ö£¬GradeΪ³É¼¨¡£
²éѯȫÌåѧÉúµÄѧºÅÓëÐÕÃûµÄÃüÁî¸ñʽΪ£º
SELECT Sno,Sname
from Student;
²éѯȫÌåÄÐͬѧµÄÏêϸ¼Ç¼µÄÃüÁî¸ñʽΪ£º
SELECT *
from Student
WHERE Ssex="ÄÐ"£»
²éѯËùÓÐÄêÁä´óÓÚ21ËêµÄѧÉúµÄÐÕÃû¡¢³öÉúÄê·ÝºÍËùÔÚϵ£¬ÒªÇóÓÃСд×Öĸ±íʾËùÔÚϵÃû¡£
SELECT Sname£¬'Year of birth',2004-Sage,lower(Sdept)
from Student
WHERE Sage>21;
²éѯISϵ¡¢MAϵºÍCSϵѧÉúµÄÐÕÃûºÍÐÔ±ðµÄÃüÁî¸ñʽΪ£º
SELECT Sname£¬Ssex
from Student
WHERE Sdept In('IS','MA','CS');
²éѯÃû×ÖÖеڶþ¸ö×ÖΪ'Ñô'µÄѧÉúµÄÐÕÃû¡¢Ñ§ºÅµÄÃüÁî¸ñʽΪ£º
SELECT Sname,Sno
from Student
WHERE Sname LIKE'_ _Ñô%'£» //ÆäÖеē_”´ú±íÒ»¸ö×Ö·û£¬¶ø“%”´ú±í0µ½Èô¸É¸ö×Ö·û¡£
²éѯDB_Design¿Î³ÌµÄ¿Î³ÌºÅºÍѧ·ÖµÄÃüÁî¸ñʽΪ£º
SELECT Cno,Credit
from Course
WHERE Cname LIKE 'DB\_Design'Escape'\';
²éѯѡÐÞÁË3ºÅ¿Î³ÌµÄѧÉúµÄѧºÅ¼°³É¼¨£¬²éѯ½á¹û°´·ÖÊýµÄ½µÐòÅÅÁÐËùÓÐÓгɼ¨µÄѧÉúѧºÅºÍ¿Î³ÌºÅ¡£
SELECT Sno£¬Credit
from SC
WHERE Cno='3'
ORDER BY Grade DESC£»
ÔÚSQLÓïÑÔÖУ¬Ò²¿ÉÒÔʹÓü¯º¯Êý£º
Count([Distinct|All]*):ͳ¼ÆÔª×é¸öÊý£»
Count([Distinct|All]<ÁÐÃû>):ͳ¼ÆÒ»ÁÐÖÐÖµµÄ¸öÊý£»
Sum([Distinct|All]<ÁÐÃû>):¼ÆËãÒ»ÁÐÖµµÄ×ܺͣ»
Avg([Distinct|All]<ÁÐÃû>):¼ÆËãÒ»ÁÐÖµµÄƽ¾ùÖµ£»
Max([Distinct|All]<ÁÐÃû>):ÇóÒ»ÁÐÖµÖеÄ×î´óÖµ£»
Min([Distinct|All]<ÁÐÃû>):ÇóÒ»ÁÐÖµÖеÄ×îСֵ£»
Çó¸÷¸ö¿Î³ÌºÅ¼°ÏàÓ¦µÄÑ¡¿ÎÈËÊý¡£
SELECT Cno£¬Count(Sno)
from SC
GROUP BY Cn
Ïà¹ØÎĵµ£º
delete from
uservalid
where(lastlogin<'2007-1-1
0:0:00')
Çå³ýÔÚuservalid±íÖÐ×îºóµÇ½ʱ¼äÔÚ2007Äê1ÔÂ1ÈÕÁãʱÁã·Ö֮ǰµÄÊý¾Ý
²éѯijһ·¶Î§ÄÚµÄÊý¾ÝÔòselete * from uservalid
where(wealth in(500,1000))
ÔÚ²éѯ·ÖÎöÆ÷ÀïÃæselete from where
inÕâЩ¹Ø¼ü×Ö¶¼×Ô¶¯´óдÁË
......
/*
±êÌ⣺һ¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSQLÓï¾ä(ÕûÀí°æ)
×÷Õߣº°®Ð¾õÂÞ.ع»ª
ʱ¼ä£º2010-05-10
˵Ã÷£ºÒÔÏÂÎåÊ®¸öÓï¾ä¶¼°´ÕÕ²âÊÔÊý¾Ý½øÐйý²âÊÔ£¬×îºÃÿ´ÎÖ»µ¥¶ÀÔËÐÐÒ»¸öÓï¾ä¡£
ÎÊÌâ¼°ÃèÊö£º
--1.ѧÉú±í
Student(S#,Sname,Sage,Ssex) ......
¾³£»á¿´¼ûÔÚSQL³ÌÐòµÄ¿ªÍ·ÓÐÕâÑùÒ»¾ä»°
if OBJECT_ID('tb') is not null
drop table tb
º¯ÊýÓï·¨ÊÇÕâÑù£º
int OBJECT_ID('objectname');
×÷ÓÃÊÇ¿´¶ÔÏóobjectnameÊÇ·ñ´æÔÚ¡£
ÆäÖвÎÊýobjectname±íʾҪʹÓõĶÔÏó£¬ÊÇchar»òÕßncharÀàÐÍ¡£
·µ»ØÖµÀàÐÍΪint£¬Èç¹û¶ÔÏó´æÔÚ£¬Ôò·µ»Ø´Ë¶ÔÏóÔÚϵͳÖеı ......
ÔÚsqlÓï¾äÖÐÌí¼Ó±äÁ¿¡£
declare @local_variable data_type
ÉùÃ÷ʱÐèÒªÖ¸¶¨±äÁ¿µÄÀàÐÍ£¬
¿ÉÒÔʹÓÃsetºÍselect¶Ô±äÁ¿½øÐи³Öµ£¬
ÔÚsqlÓï¾äÖоͿÉÒÔʹÓÃ@local_variableÀ´µ÷ÓñäÁ¿
ÉùÃ÷ÖпÉÒÔÌṩֵ£¬·ñÔòÉùÃ÷Ö®ºóËùÓбäÁ¿½«³õʼ»¯ÎªNULL¡£
ÀýÈ磺declare @id int
&nb ......
1¡¢¶¨Òå»ù±¾±í
SQLÓïÑÔʹÓö¯´ÊCREATE¶¨Òå»ù±¾±í£¬Æä¾ßÌåÓï·¨¸ñʽÈçÏ£º
CREATE TABLE <±íÃû>
(<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]...[£¬<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]][,<±í¼¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ>])£»
ÀýÈ磺½¨Á¢Ò»¸öѧÉú±íStudent£¬ËüÓÉѧºÅSno£¬ÐÕÃûSname£¬ÐÔ±ðSsex£¬Äê ......