    
        
Կ:
AAAA ֶID
select * from AAAA order by ID limit N,10

////
ѧSQLΪ֣кйSQL׼Ļ֪ʶһƪһЩݿ4ݲѯ͡⣬ǻWHEREӾ÷ͬʱṩ˸ѯľʾ

ƪǽһЩSQLӾвڻSELECTݲѯС


SELECTѡƽ
ǴһƪSELECT෱ĸѡЩѡݷصķʽЩѡӾ䡢ؼʺͺʽڡ

Ӿһ޸Ľ䡣Ӿ䲻ǱҪ䵫ݵݼʾWHEREӾӾ䡣

ؼʴݿڹܡЩؼʱǲѯġ硰INSERT INTO table_name (column1) VALUES (data1);еINTOVALUEˡǽ˽DISTINCTؼʣܴһЩǳõĿѡܡ

ܽһЩõӾ䡢ؼʺͺȻһÿһ־˵

ORDER BY C ָ򷵻ؽӾ
DISTINCT C ֻؽΨһеĹؼ 
COUNT -- ƥѯֵĺ 
AVG C úָеƽֵ
SUM Cúָеּ
MIN C úСķNULLֵ
MAX Cúеֵ
GROUP BY C л㼯ѯӾ
ORDER BYԲѯ
ORDER BYӾݿԲѯԼдӦóСֹˡORDER BYӾڲѯĽβ÷£

SELECT * FROM Contacts ORDER BY first_name;

κѡʹORDER BY Ӿ䷵ضн㻹Ӿ䣺
SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN a AND k ORDER BY last_name;

ԶԶ˳򰴴νͣԲѯие˳Ҫ
SELECT * FROM Contacts ORDER BY company, last_name, first_name;

ѯĬϰֻĸORDER BY ӾDESCؼʸĳɽСУߵnet_amountȣ򣩡лݶͬnet_amountֵôͬlast_nameֵĸȳֵȣΪlast_nameһлǰġ
SELECT * FROM Sales ORDER BY net_amount DESC, last_name, first_name;

ڰնԺ󣬴ݿ󽫰ݱڵĵһȻ˳򡣾ʵָб仯ˣӦбȽҪôӦȷضҪС

һֵעǣORDER BYӾ䣨ԼWHEREӾ䣩вһǷؽϵһֻ֡ҪõжݱڴȫЧ

SELECT company, first_name, net_amount FROM Sales ORDER BY start_date, last_name;



DISTINCTزظ

DISTINCTؼֻؽڲظС磬ʱҪҳSalesڵĹ˾ֲ뿴ÿĿDISTINCTӦÿһ˾һݣ

SELECT DISTINCT company FROM Sales;

ʹDISTINCTʱеСгڵԱĹ˾ÿһۼ¼ôʹ䡣ע⣬ܷͬһ˾Ŀȵȡ

SELECT DISTINCT company, last_name, first_name FROM Sales;

㻹ڶԽСΧͽʱSELECTʹDISTINCTΪȷʾݣݿȻ֤ʵǷƥУȻӦDISTINCTܡȫ϶ȷ֮󼴴ORDER BYӾ䡣ʾֻnet_amount100вűءDISTINCTĵ1ƥѯжƥУORDER BYõnet_amountͺĽ

SELECT DISTINCT company, last_name, first_name FROM Sales WHERE net_amount > 100 ORDER BY company, net_amount;


Ӧ߼
صһֵĺۼaggregate functionͨӦóоۼĽʱġֶʹõʵʺ磬ڷݿʱļֵʾ

$keyname = COUNT(*);
$resultkey = AVG(net_amount);

COUNT
COUNTеһһµĻʾܸݱڵSELECT COUNT(*) FROM Sales;

Ҳκνе

SELECT COUNT(*) FROM Sales WHERE net_amount > 100;

뿴ĳضжаǿֵ㲻ԸʹCOUNTע⣬ݿΪֶΪʱȱʡNULL򽫷رе⣬гڳһ»

SELECT COUNT(company) FROM Sales;

COUNTDISTINCTе
SELECT COUNT(DISTINCT company, last_name) FROM Sales;

COUNTͨڳȷFORѭѭ


AVG
AVGĳֶεƽֵб͡úеΪֶǷ͵ء0SELECT AVG(net_amount) FROM Sales;

ԽӾƸúӦ÷Χ

SELECT AVG(net_amount) FROM Sales WHERE company LIKE %ABCD Co%;

оۼһORDER BY佫ԡ

SUM

SUMĹʽAVGֻ࣬úؽֵֶĺ͡
SELECT SUM(net_amount) FROM Sales WHERE net_amount > 100;

AVGSUMMINMAXûָе¶᷵ش㲻ʹá*ͨ

MIN
MINָСķǿֵָС֡һַذĸ˳ֵĵ1ֵSELECT MIN(net_amount) FROM Sales WHERE last_name = Smith;
SELECT MIN(last_name) FROM Sales;


MAX

MAXĹʽMINһֻúķǿֵúҲַ
SELECT MAX(net_amount) FROM Sales;
SELECT MAX(company) FROM Sales WHERE net_amount > 100;

MAXʱڰԶֶεȷһĿļIDһǹݿ⣬ʹһϢһĿʱؽԷûִݲ

GROUP BY 

ȻᵽЩṩ൱õϢǣGROUP BYӾæĻеֶӼӦЩҪSalesÿһҹ˾һһεִMAXѯȫԴGROUP BYӾͬĽ

SELECT company, MAX(net_amount) FROM Sales GROUP BY company;

Իÿҹ˾net_amountĵֵѡʱҲԲø䣬㻹öԺ顣

ʾϸַʽȣGROUP BYӾָҪʾСȻ֪ӽĵ1last_nameֵSum( net_amount )ʾȫ˾ĽƥϵСΪֻʹCompanyֶǵ顣

SELECT company, last_name, SUM(net_amount) FROM Sales GROUP BY company;

Уlast_nameʵϲûṩʲôõϢΪһҪõĹ׼Դж顣ͿڽвضеĺָGROUP BYģ

SELECT company, AVG(net_amount), last_name FROM Sales GROUP BY company, last_name;


Ӹÿҹ˾ÿһϸƽnet_amountгGROUP BYе˳Ž򣬵ʵʵĺֵһġ


ӱ֯ʾСЩǺõģ磬Ҫʾ˵ȴʾˣ

SELECT company, COUNT(sale_id) FROM Sales GROUP BY company, last_name;


ʹGROUP BYĲѯ
ʾԽWHERE־ϵĸƲѯķΧWHEREӾȱ㣬Ȼִкʹʱġ

SELECT company, AVG(net_amount), FROM Sales WHERE net_amount > 100 GROUP BY company;

ֻЩWHEREAVGע⣬WHEREӾGROUP BYӾ֮ǰ㻹HAVINGԷ֮ƷصĽϡ

SELECT company, AVG(net_amount), FROM Sales WHERE last_name BETWEEN a AND m GROUP BY company HAVING AVG(net_amount) > 500;


ÿҹ˾net_amountƽֵֻЩԱͬʱֻʾ500Ľ

///////////
ҪSQLһع
Щ»ﵽĿģ

SQLݲѯ 漰ݿֻѯ͡
SQLSELECTѡ 漰SELECTϸںͶԺϼƺĲ 
--------------------------------------------------------------------------------

һ
ѡܼ򵥣һѡѯһѯڲһڵһвõԴѡѯĺϲȽϵ䵽ݿжӦС

ʹܵһ;ǶеĿɱרеֵжλ磬ҵһݿAlbumLyricҿԺ׵ͨӲѯҵÿһMetallicaĸаjusticeĸ

SELECT song_name FROM Album
WHERE band_name = Metallica
AND song_name IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE %justice%);

Ǻܼ򵥵ģҴAlbumѡMetallicaĸţlyricѡаjusticeĸʹINؼLyricʾAlbumзظơ

ʹLyricAlbumеĽ޶WHEREӾеѡ񲿷ȫ԰ģҲҪʹAlbum.song_nameLyric.song_nameרơûдսLyricзκֵҪLyricһʹһJOIN

ʹNOT INų
ʹNOT INؼȷزһеĽ磬ҪͨĴMetallicaڡAnd Justice for Allרвʡjusticeĸ

SELECT song_name FROM Album
WHERE album_name = And Justice for All
AND band_name = Metallica
AND song_name NOT IN
(SELECT song_name FROM Lyric
WHERE song_lyric LIKE %justice%);

ǰSQLУѡMetallicaġAnd Justice for All,רеиǴидСjusticeиLyricûгֵAlbum鷵и֮ڷѯʹôȽ飬ͨһͿԵõȷеĽ

ʹEXISTSؽ
ʱͨ;ͬݣҪĽƥ䣨أõֵĽ磬ҿͨAlbumõMetallicaĸбǣҲԴҵCoverеõDamage, IncݵMetallicaĸбҿֱӱȽϲѯֵء

SELECT Album.song_name FROM Album
WHERE Album.band_name = Metallica
AND EXISTS
(SELECT Cover.song_name FROM Cover
WHERE Cover.band_name = Damage, Inc.
AND Cover.song_name = Album.song_name);

SQLУʹרƣΪֱӶȽϣǽΪһԴʹáҲCoverзؽһЩݿ֧NOT EXISTSؼȷ㲢ûƥ䡣

ʹúϼƺȽ
ʹѡصıмݣ㻹һWHEREѡʹúϼƺȷ顣磬ҪʵÿһMetallicaAlbumеĿңһ뷵ȱٸרơܷأAlbumInfoһר(album_tracks)Ӧж׸Ϣ

SELECT AlbumInfo.album_name FROM AlbumInfo
WHERE AlbumInfo.band_name = Metallica
AND album_tracks <>
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name);

ѾɹطMetallicaרУӦеĿAlbumʵʵĸĿרơ

ѡ
һǹÿһרĿҪõһȽϱô죿ԽһѡĽΪսһءܾϼƺʹáͨأķʿΪĲѯһ֡һӽÿһMetallicaרӦðĿAlbumаĿ

SELECT AlbumInfo.album_name, album_tracks,
(SELECT COUNT(*) FROM Album
WHERE Album.album_name = AlbumInfo.album_name)
FROM  AlbumInfo
WHERE AlbumInfo.band_name = Metallica;

һǿ漰AlbumInfoнalbum_tracksֵıΪAlbumʵʵĿ

UPDATE AlbumInfo SET album_tracks =
SELECT COUNT(*) FROM Album
WHERE AlbumInfo.album_name = Album.album_name)
WHERE AlbumInfo.band_name = Metallica;

еѡһ԰λִС

ѡȽϹؼ(ALL, SOME, ANY)
ʹñ׼ѯܣؼֿʹ㽫һʽֵһѡȽϣЩؼַTRUEFALSEBooleanֵALLؼҪѡֵرȽSOMEANYؼҪһԡALLؼֵһʵ

SELECT * FROM AlbumSales
WHERE album_gross >
ALL (SELECT album_costs FROM AlbumProduction);

ӽAlbumSales񷵻AlbumProduction渶ܶڳɱרм¼ANYALLиܶרɱר¼= ANYINؼͬġ<> ALLNOT INؼǶԵȵġؼANYSOMEҲǵͬġݿжЩؼֵ֧ǲͬģڳʱҪŲ̷ϡ

˭Ա׼ݽṹʣ
ѡѯ䷨Ǽ׶ģúʱʹȴһѵ㡣ڱ׼ݽṹ⣬ѡø˽⡣

///////////////////////////////
SELECT

һ򵥵SELECTǲѯķʽFROMӾеöԶĽһιʵ

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

ʵУʹõ(table1.column1)ָרһ漰רֻһοıг֣ͲҪƣǼƻԿɶ𵽰

FROMӾб֮ɶָԼı񣬾һЩݿһʽJOIN֮ǰǿЧشⷽƣ̸

䷨һ򵥵INNER JOINһЩݿ⽫һⲿJOINǵͬġWHEREӾ֪ݿһҪؽʱгıڸϳһıһֵעǣıȽҪΪ鷵صרͬУtable1.column1table2.column1ϱ񣬵Ƿصȴtable2.column2

WHEREӾʹANDؼչı㻹ʹıĽʵʵشÿ񷵻רУtable3table1ƥ䣬ûдtable3κζʾֻȷtable1йרtable3֮Сעtable3ҪFROMӾбá

SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1;

ȻҪעǣѯķʽһָJOINݿܶвͬĴȡʹõŻ档ңԶWHEREӾԵĶ彫ԸĽµĲѯзÿһܵĽصרrogue򣬾CROSS JOIN֮һ

ϰݿ⴦͵ķʽֻϣһ򵥵SELECTͿԴﵽĿġ

JOIN
JOINĹʽSELECTͬģӲͬıзһרĽ顣ڰJOIN֮ʹⲿJOINǶĽĸõĿƣһ漰ܱܶ֡

JOINм֣LEFTRIGHTFULL OUTERINNERCROSSʹõҪĽġ磬ʹLEFT OUTER JOINгĵһзйصУͬʱûϢһصĻǱڵشгĵڶмС

INNER JOINͰJOINǲͬģINNER JOINֻЩжݵС

ԵһSELECTѯʹJOIN

SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1;



Ӳѯ
ӲѯѡһѯнΪԴʹõһ;Խƻ壬жѯӦ֮еݡӲѯԲοݵںһЩ£һררһѡĽ

ʹһаҪصݣһһȽϵȷʲôȷʵȤġ

SELECT column1 FROM table1 WHERE EXISTS ( SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 );

ӲѯҪһܱ֡д۵ģȡʹõıĴС͸ԣܻӦÿһѯڱѯΪԴʹ֮ǰصܵĻԵʹJOINԽٵͺʱṩͬϢ

JOINӲѯ 
JOINSĸϸĲйص ӵһЩӲѯĸݣʹSQLѡϲѯ
///////////////////////////////