SQL ѧϰ±Ê¼ÇÖ®SelectÍêÕûÓï·¨¼°Ö´Ðйý³ÌÏê½â
SELECTÓï¾äµÄÍêÕûÓ﷨Ϊ£º
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
from tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
×¢Òâ Where£¬GroupBy£¬Having£¬OrderBy ˳Ðò¡£
Ö´Ðв½Öè
1£©ÏÈ´Ófrom×Ö¾äÒ»¸ö±í»ò¶à¸ö±í´´½¨¹¤×÷±í
2£©½«whereÌõ¼þÓ¦ÓÃÓÚ1£©µÄ¹¤×÷±í£¬±£ÁôÂú×ãÌõ¼þµÄÐÐ
3£©GroupBy ½«2£©µÄ½á¹û·Ö³É¶à¸ö×é
4£©Having ½«Ìõ¼þÓ¦ÓÃÓÚ3£©×éºÏµÄÌõ¼þ¹ýÂË£¬Ö»±£Áô·ûºÏÒªÇóµÄ×é¡£
5£©Order By¶Ô½á¹û½øÐÐÅÅÐò¡£
ʹÓÃDISTINCTÈ¥³ýÖØ¸´µÄÐÅÏ¢
Ö´ÐУº
select * from student
·µ»Ø½á¹û
-stuName -stuno
Tom Sawyer 1
Andy Ke 2
Angelia Jolie 3
Celine Dion 4
alex 5
alex 6
Ö´ÐУº
select distinct stu_name from student
½á¹û£º
alex
Andy Ke
Angelia Jolie
Celine Dion
Tom Sawyer
¼´È¥µôÁËÖØ¸´Öµ£¬ÓÖ¶Ô²éѯ½á¹û½øÐÐÁËÅÅÐò¡£Êµ¼ÊÉÏDBMSµÄ²Ù×÷¹ý³ÌÊÇÏȶԲéѯ½á¹ûÅÅÐòÔÚ´Ó½á¹ûÖÐÈ¥³ýÖØ¸´µÄ
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
Àý×Ó£ºÕÒ³öÿ¸ö¸èÊÖÏúÁ¿×î¸ßµÄ3Ê׸è
create table singer_info
(
id int primary key identity(1,1),
name varchar(20),
song varchar(20),
amount int
)
insert into singer_info values('jay','aaa',1000)
insert into singer_info values('jay','bbb',2000)
insert into singer_info values('jay','cc ......
TRUNCATE TABLE ÔÚ¹¦ÄÜÉÏÓë²»´ø Where ×Ó¾äµÄ Delete
Óï¾äÏàͬ£º¶þÕß¾ùɾ³ý±íÖеÄÈ«²¿ÐС£µ« TRUNCATE TABLE ±È Delete Ëٶȿ죬ÇÒʹÓõÄϵͳºÍÊÂÎñÈÕÖ¾×ÊÔ´ÉÙ¡£
Delete
Óï¾äÿ´Îɾ³ýÒ»ÐУ¬²¢ÔÚÊÂÎñÈÕÖ¾ÖÐΪËùɾ³ýµÄÿÐмǼһÏî¡£TRUNCATE TABLE ͨ¹ýÊÍ·Å´æ´¢±íÊý¾ÝËùÓõÄÊý¾ÝÒ³À´É¾³ýÊý¾Ý£¬²¢ÇÒÖ»ÔÚÊÂÎñÈÕÖ¾ÖмÇÂ¼Ò ......
1¡¢INSTR4(string1,string2[,a][,b]) ·µ»Østring1Öаüº¬string2µÄλÖÃaºÍbÊÇÒÔUCS4´úÂëµãΪµ¥Î»¡£
ÒÔÉϺ¯Êý·µ»Østring1Öаüº¬string2µÄλÖᣴÓ×ó±ß¿ªÊ¼É¨Ãèstring1,ÆðʼλÖÃÊÇA¡£Èç¹ûAΪ¸ºÊýÄÇô´ÓÓұ߿ªÊ¼É¨Ãè¡£µÚB´Î³öÏÖµÄλÖý«±»·µ»Ø¡£AºÍBȱʡ¶¼Îª1£¬¼´·µ»ØÔÚ
string1ÖеÚÒ»´Î³öÏÖstring2µÄλÖá£Èç¹ûstring2ÔÚAº ......
----use pubs-sales
-----´´½¨Ë÷Òý
create index index_name
on authors(au_lname)
--´´½¨Ë÷Òýºó±íÖÐËùÓеÄÊý¾Ý¸ú֮ǰûÓÐÇø±ð
select * from authors
--µ¥¶À²éË÷ÒýÁÐ
select au_lname from authors
--Ç¿ÖÆÊ¹Ó÷Ǵؼ¯Ë÷Òý
select * from authors with(index(index_name))
--¶à×ֶηǴؼ¯Ë÷Òý
create inde ......