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
Ïà¹ØÎĵµ£º
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 ......
ÔÚÕâÀï,ÎÒËùÖ¸µÄ°æ±¾ÊÇÖ¸µÄ2000,2005,2008ÕâÑùµÄ°æ±¾Çø±ð,¶ø²»ÊÇÆóÒµ°æ,¿ª·¢°æÕâÑùµÄ°æ±¾Çø±ð.´Ó¹Ù·½µÄ˵·¨À´¿´,SQL ServerÊÇÔÊÐíÊý¾Ý¿â´ÓµÍ°æ±¾Ïò¸ß°æ±¾»Ö¸´,µ«²»Ö§³Ö¸ß°æ±¾ÏòµÍ°æ±¾µÄ»Ö¸´,Èç¹ûÎÒÃÇÓø߰汾µÄÊý¾Ý¿âÔڵͰ汾µÄÊý¾Ý¿âÒýÇæÏ»ָ´,»á³öÏÖʲô´íÎóÃèÊöÄØ?
......
Rebuild indexes online with SQL Server 2005
http://blogs.techrepublic.com.com/datacenter/?p=249
Online index rebuild
SQL Server 2005 introduces the ability to rebuild your indexes in an
online fashion so that other processes are able to access the table
while the rebuild is occurring. Because y ......
SQL Server ¾Ñé £¨×ªÔØ£©
http://www.cnblogs.com/treeyh/archive/2007/08/06/844763.html
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Äã¶¼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâÆªÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉ ......
[Sql]EXCEPT ºÍ INTERSECT¹Ø¼ü×Ö
http://www.cnblogs.com/treeyh/archive/2008/07/01/1232845.html
EXCEPT
´Ó EXCEPT ²Ù×÷Êý×ó±ßµÄ²éѯÖзµ»ØÓұߵIJéѯδ·µ»ØµÄËùÓзÇÖØ¸´Öµ¡£
INTERSECT
·µ»Ø INTERSECT ²Ù×÷Êý×óÓÒÁ½±ßµÄÁ½¸ö²éѯ¾ù·µ»ØµÄËùÓзÇÖØ¸´Öµ¡£
A. ʹÓà EXCEPT
ÔÚʾÀýÖÐʹÓà TableA ºÍ TableB ÖеÄÊý¾Ý¡£
......