Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÓïÑÔ»ù´¡

SQL:Structured Query Language,1974ÄêBoyceºÍChamberlinÌá³ö.
Êý¾Ý¶¨Òå :CREATE,DROP
Êý¾Ý¿â²éѯ:SELECT
Êý¾Ý²Ù×Ý :INSERT,UPDATE,DELETE
Êý¾Ý¿ØÖÆ :GRANT,REVOKE
Ò».Êý¾ÝµÄ¶¨ÒåºÍÐÞ¸Ä:
1.¶¨Òå»ù±í
CREATE TABLE ±íÃû (<ÁÐÃû1 ÀàÐÍ[NOT NULL][,ÁÐÃû2 ÀàÐÍ[NOT NULL]>…[ÆäËû²ÎÊý]);
¹ØÓÚÀàÐÍ:
INTEGER È«×Ö³¤µÄ¶þ½øÖÆÕûÊý.
SMALLINT °ë×Ö³¤µÄ¶þ½øÖÆÕûÊý.
DECIMAL(p[,q])ѹËõÊ®½øÖÆÊý,¹²pλСÊýµãºóÓÐqλ,15>=p>=q>=0,q=0ʱ¿ÉÊ¡ÂÔ.
FLOAT Ë«×Ö³¤µÄ¸¡µãÊý.
CHAR(n) ³¤¶ÈΪnµÄ¶¨³¤×Ö·û´®.
VARCHAR(n)±ä³¤×Ö·û´®,×î´ó³¤Îªn.
2.Ð޸Ļù±í
ALTER TABLE ±íÃû ADD ÁÐÃû ÀàÐÍ; //Ôö¼ÓÐÂÁÐ.
3.ɾ³ý»ù±í
DROP TABLE ±íÃû; //ɾ³ý»ù±íÁ¬Í¬±íÖмǼ.
¶þ.ÊÓͼµÄ¶¨Òå
1.¶¨ÒåÊÓͼ
CREATE VIEW ÊÓͼÃû[ÁÐÃû[,ÁÐÃû]…]AS ×Ó²éѯ[WITH CHECK OPTION];
2.ÊÓͼɾ³ý
DROP VIEW ÊÓͼÃû;
Èý.Êý¾Ý²Ù×Ý
SELECT [DISTINCT] Ñ¡ÔñÊä³öÊôÐÔ±í
from Òª¼ìË÷µÄ±íÃû
[WHERE ¼ìË÷Ó¦Âú×ãµÄÌõ¼þ]
[GROUP BY ·Ö×éÊôÐÔ±í(HAVING ¼ìË÷Ìõ¼þ)]
[ORDER BY ÅÅÐòÊôÐÔ±í];
1. ¼òµ¥²éѯ
(1).²»Ïû³ýÖظ´Ôª×éµÄ¼ìË÷
SELECT TITLE from BOOKS
(2).Ïû³ýÖظ´Ôª×éµÄ¼ìË÷
SELECT DISTINCT TITLE from BOOKS
(3).ʹÓÔ*”È«²¿¼ìË÷
SELECT * from BOOKS
(4).´ø±í´ïʽµÄ¼ìË÷
SELECT TITLE , PRICE*3 from BOOKS WHERE TITLE=’²Ù×÷ϵͳ’
(5).ÒªÇóÅÅÐòµÄ¼ìË÷
SELECT CARD-NO,TITLE,DATA from BOOKS
WHERE DATA<1998.7.1 ORDER BY CARD-NO ASC|DESC //Éý»ò½µ
2. º¬Óи÷ÖÖν´ÊµÄ¼ìË÷
COMPARISON, IN, LIKE, NULL, QUANTIFIED, EXISTS, BETWEEN
(1).BETWEEN
SELECT TITLE,SORT-NO,LO-NO from BOOKS WHERE LO-NO
BETWEEN 4 AND 5µÈ¼ÛÓÚ
SELECT TITLE,SORT-NO,LO-NO from BOOKS WHERE LO-NO
>=4 AND LO-NO<=5
(2).IN ͬÉÏÀý:
SELECT TITLE,SORT-NO,LO-NO from BOOKS WHERE LO-NO
IN(4,5) ´ËÀý»¹¿Éд³É:
SELECT TITLE,SORT-NO,LO-NO from BOOKS WHERE LO-NO
=4 OR LO-NO=5
(3).LIKE:ÕÒ³öÐÕÕŵÄÇé¿ö
SELECT NAME,CARD-NO,DEPT from BOOKS WHERE NAME
LIKE’ÕÅ%’ //ÊôÐÔÃûÀàÐÍÒ»¶¨ÎªCHAR;¶ÌÏß“-”±íʾ´ËλΪһ¸ö×Ö·û;
“%”±íʾÈÎÒâ×Ö·û´®(º¬¿Õ´®);ÆäËü×Ö·û´ú±í×ÔÉí.
3. Áª½Ó²éѯ
(1).µÈÖµÁª½Ó
SELECT BORROWERS.*,LOANS.CARD-NO,LOANS.TITLE
from BORROWERS


Ïà¹ØÎĵµ£º

Ò»¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSQLÓï¾ä

 /* 
 ±êÌ⣺һ¸öÏîÄ¿Éæ¼°µ½µÄ50¸öSQLÓï¾ä(ÕûÀí°æ) 
 ×÷Õߣº°®Ð¾õÂÞ.ع»ª
 Ê±¼ä£º2010-05-10 
 ËµÃ÷£ºÒÔÏÂÎåÊ®¸öÓï¾ä¶¼°´ÕÕ²âÊÔÊý¾Ý½øÐйý²âÊÔ£¬×îºÃÿ´ÎÖ»µ¥¶ÀÔËÐÐÒ»¸öÓï¾ä¡£ 
 ÎÊÌâ¼°ÃèÊö£º 
 --1.ѧÉú±í 
 Student(S#,Sname,Sage,Ssex) ......

50¸ö³£ÓõÄSQLÓï¾ä

Student(S#,Sname,Sage,Ssex) ѧÉú±í 
Course(C#,Cname,T#) ¿Î³Ì±í 
SC(S#,C#,score) ³É¼¨±í 
Teacher(T#,Tname) ½Ìʦ±í 
ÎÊÌ⣺ 
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£» 
select a.S# from (select s#,score from SC where C#='001') a,(sele ......

SQL ³£ÓÃÓï¾äÒÔ¼°º¯Êý

SQL ³£ÓÃÓï¾äÒÔ¼°º¯ÊýÖ®Ò» 
SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
¡¡¡¡¡¡¡¡¡¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
¡¡¡¡¡¡¡¡¡¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
¡¡¡¡¡¡¡¡¡¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
¡¡¡¡--Êý¾Ý¶¨Òå
¡¡¡¡ CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
¡¡¡¡¡¡¡¡¡¡¡¡DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í
......

SQLÖÐdeclareÉêÃ÷±äÁ¿

ÔÚsqlÓï¾äÖÐÌí¼Ó±äÁ¿¡£
declare @local_variable data_type
ÉùÃ÷ʱÐèÒªÖ¸¶¨±äÁ¿µÄÀàÐÍ£¬
¿ÉÒÔʹÓÃsetºÍselect¶Ô±äÁ¿½øÐи³Öµ£¬
ÔÚsqlÓï¾äÖоͿÉÒÔʹÓÃ@local_variableÀ´µ÷ÓñäÁ¿
ÉùÃ÷ÖпÉÒÔÌṩֵ£¬·ñÔòÉùÃ÷Ö®ºóËùÓбäÁ¿½«³õʼ»¯ÎªNULL¡£
ÀýÈ磺declare @id int
       &nb ......

SQLÓïÑÔ»ù´¡ Êý¾Ý²éѯ

ÔÚSQLÓïÑÔÖУ¬Ö»ÌṩÁËÒ»¸ö¶¯´ÊSELECTÓÃÀ´½øÐÐÊý¾Ý²éѯ²Ù×÷£¬µ«Õâ¸ö¶¯´ÊµÄ²ÎÊýÊ®·Ö¸´ÔÓ£¬ÇÒÄÜǶÌ×ʹÓã¬ÆäͨÓøñʽÈçÏ£º
SELECT [All|Distinct]<Ä¿±êÁбí´ïʽ>[£¬<Ä¿±êÁбí´ïʽ>]...
from <±íÃû»òÊÓͼÃû>[£¬<±íÃû»òÊÓͼÃû>]...
[WHERE<Ìõ¼þ±í´ïʽ>]
[GROUP BY<ÁÐÃû1>[HAVING<Ì ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ