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
Ïà¹ØÎĵµ£º
SELECT * from xcmis.temp_odr_prom@linkxceis where trim(ODR_NO) like 'CA10010082'
SELECT * from xcmis.temp_odr_prom@linkxceis where ODR_NO like 'CA10010082%'
SELECT * from xcmis.temp_odr_prom@linkxceis where ODR_NO = 'CA10010082'
OK
SELECT * from xcmis.temp_odr_prom@linkxceis where ODR_NO like 'C ......
Óû§È¨ÏÞ¹ÜÀí
Ò»¡¢·þÎñÆ÷µÇ¼ÕʺźÍÓû§ÕʺŹÜÀí
1.SQL Server·þÎñÆ÷µÇ¼¹ÜÀí
²»¹ÜʹÓÃÄÄÖÖÈÏ֤ģʽ£¬Óû§¶¼±ØÐëÏȾ߱¸ÓÐЧµÄÓû§µÇ¼Õʺš£SQL ServerÓÐÈý¸öĬÈϵÄÓû§µÇ¼Õʺţº¼´sa¡¢Builtin\administratorsºÍguest¡£saÊÇϵͳ¹ÜÀíÔ±(system administrator)µÄ¼ò³Æ£¬ÊÇÒ»¸öÌØÊâµÄÓû§£¬ÔÚSQL ServerϵͳºÍËùÓÐÊý¾Ý¿âÖÐÓ ......
SQL Server 2005ÆôÓÃsaÕ˺Å
ÆôÓÃsaÓû§ºÍÔ¶³ÌÁ¬½Ó
²Ëµ¥Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager
Ñ¡ÖÐSQL Server 2005 Network Configuration
ÔÚÓұߵÄTCP/IPÉϵãÓÒ¼ü£¬enabled
²Ëµ¥Start->Microsoft SQL Server 2005->SQL ......
¾³£»á¿´¼ûÔÚSQL³ÌÐòµÄ¿ªÍ·ÓÐÕâÑùÒ»¾ä»°
if OBJECT_ID('tb') is not null
drop table tb
º¯ÊýÓï·¨ÊÇÕâÑù£º
int OBJECT_ID('objectname');
×÷ÓÃÊÇ¿´¶ÔÏóobjectnameÊÇ·ñ´æÔÚ¡£
ÆäÖвÎÊýobjectname±íʾҪʹÓõĶÔÏó£¬ÊÇchar»òÕßncharÀàÐÍ¡£
·µ»ØÖµÀàÐÍΪint£¬Èç¹û¶ÔÏó´æÔÚ£¬Ôò·µ»Ø´Ë¶ÔÏóÔÚϵͳÖеı ......
--²âÊÔÊý¾Ý
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'ɽ¶«Ê¡'
UNION ALL SELECT '002','001','ÑĮ̀ÊÐ'
UNION ALL SELECT '004','002','ÕÐÔ¶ÊÐ'
UNION ALL SELECT '003','001','ÇൺÊÐ'
UNION ALL SELECT '00 ......