18-3 使用 SQL 來檢視???

ڭ̦bW@`ФFpϥ ASP ŪƮwơAñNe{CASP PƮwqзǻyNO SQLAgѦػyAڭ̤~b ASP {XƮwi˵BsWBקBRʧ@C`N SQL i@BШΪkC

SQL OucƬd߻yv]Structured Query Language^²١AO IBM q 1970 ~NҵoiXӡAΩpƮw (Relational Databases) @ظƮwd߻yAQ SQL iHΨӶiUػPƮwBzAҦpG

ѩ SQL \wqAҥHwg@Ӭd߸ƮwзǻyCMUaƮwҴѪ SQL yb\W|tA򥻪\O@PC椸N ASP PƮwX̱`Ψ쪺SQLyk@ӾzAѩ SQL yk۷HAҥHŪ̥unHUykyFѤAYiWiJ ASP PƮwX@ɡC

Ʈw|Ӱ򥻬d߰ʧ@AY˵BsWBקBRAHUNv@гo|ذ򥻥\઺ SQL ykAHάdҡC

Yn˵ƮwơAϥΪ SQL DnOOuSELECTvA򥻻ykpUG

SELECT W1, W2, ... FROM ƪW1, ƪW2, ... [WHERE ] [ORDER BY W1, W2, ...] pUG SQL ]p򥻤WOҥ^媺۵MykA]bJWeCۧڭ̨ӬݬݴXӽdҡANiHA SQL C

Aڭ̥HƮw asr/example/database/basketball.mdb ҡAoӸƮw]tӸƪG

epUG
Ʈw "example/database/basketball.mdb"
ƪ "Player" e ƪ "Team" e
ID NickName Name TeamID Percentage
jean dӻ 38.25 
jones 49.77 
ben 50.26 
asser LfS 37.22 
window 36.67 
roger iP 25.88 
cosh \廨 70.98 
banny xP 88.97 
shyba 67.45 
10 batty zp 65.55 
11 joey \Ů 47.65 
12 roland dd 55.87 
13 sony L| 54.77 
14 beball μz 33.33 
15 gavins LF 55.65 
16 jojo 44.65 
17 jtchen  48.76 
18 Gao 67.88 
19 Wayne  65.87 
20 chingz  57.28 
ID Name WinNo
x_ 12 
s˶ 
10 
n붤 12 
xn 17 
 16 
 11 

HUOX˵O򥻽dҡAuoA@ƪC]Ū̥iHu SQL OAqqNqAAݬݤĶC^

  1. SQLOGSELECT * FROM Team
    NqGҦy
    Gu*vN Team ƪҦC
    dߵGG
    ID Name WinNo
    x_ 12 
    s˶ 
    10 
    n붤 12 
    xn 17 
     16 
     11 
  2. SQLOGSELECT TOP 3 * FROM Team
    NqGҦyơAueT
    GuTOP 3vNueTơC]iHϥΡuTOP 25 percentvANҦƪeʤGQC
    dߵGG
    ID Name WinNo
    x_ 12 
    s˶ 
    10 
  3. SQLOGSELECT Name, Percentage FROM Player WHERE NickName='gavins'
    NqG︹ gavins ymWΩRv
    dߵGG
    Name Percentage
    LF 55.65 
  4. SQLOGSELECT * FROM Team WHERE Name like 'x%'
    NqGWHuxv}Yy
    Gu%vNNתrC
    dߵGG
    ID Name WinNo
    x_ 12 
    10 
    xn 17 
  5. SQLOGSELECT Name, Percentage FROM Player WHERE Name like '__'
    NqGumBWrTӦrvymWΩRv
    Gu_vNN@rC
    dߵGG
    Name Percentage
    50.26 
    44.65 
     48.76 
     65.87 
  6. SQLOGSELECT Name, WinNo FROM Team WHERE WinNo>10
    NqGuӳƤj10vyW٤Ψӳ
    dߵGG
    Name WinNo
    x_ 12 
    n붤 12 
    xn 17 
     16 
     11 
  7. SQLOGSELECT Name, WinNo FROM Team WHERE WinNo>10 ORDER BY WinNo DESC
    NqGuӳƤj10vyW٤ΨӳơAîھڳӳƥѤjpƦC
    GY[J DESCAh|iѤpjƧǡC
    dߵGG
    Name WinNo
    xn 17 
     16 
    n붤 12 
    x_ 12 
     11 
  8. SQLOGSELECT TeamID, Name, Percentage FROM Player WHERE TeamID=5 ORDER BY Percentage DESC
    NqGuyNX5vyRvƦ]
    dߵGG
    TeamID Name Percentage
    67.45 
     57.28 
    LF 55.65 
    49.77 
    44.65 
    μz 33.33 
  9. SQLOGSELECT * FROM Player ORDER BY TeamID, Percentage DESC
    NqGC@yRvƦ]
    GCXG| TeamID ѤpjƧǡAA Percentage ѤjpƧǡC
    dߵGG
    ID NickName Name TeamID Percentage
    18 Gao 67.88 
    12 roland dd 55.87 
    13 sony L| 54.77 
    ben 50.26 
    jean dӻ 38.25 
    window 36.67 
    roger iP 25.88 
    17 jtchen  48.76 
    11 joey \Ů 47.65 
    asser LfS 37.22 
    10 batty zp 65.55 
    shyba 67.45 
    20 chingz  57.28 
    15 gavins LF 55.65 
    jones 49.77 
    16 jojo 44.65 
    14 beball μz 33.33 
    banny xP 88.97 
    cosh \廨 70.98 
    19 Wayne  65.87 
  10. SQLOGSELECT count(*) FROM Team WHERE WinNo>10
    NqGuӳƤj10vy`
    Gcount()Ʒ|pƵơAƮw|۰ʲͼȮɪW Expr1000C
    dߵGG
    Expr1000
  11. SQLOGSELECT max(Percentage) as ̰Rv FROM Player
    NqGҦy̰Rv
    Gmax(Percentage)Ʒ|pRv̤jȡCѩϥΤFuas ̰RvvAƮw|۰ʲͼȮɪW١ṵRvvC
    dߵGG
    ̰Rv
    88.97 
  12. SQLOGSELECT TOP 1 Name, Percentage FROM Player ORDER BY Percentage DESC
    NqG㦳̰Rvy
    dߵGG
    Name Percentage
    xP 88.97 
  13. SQLOGSELECT Name, Percentage FROM Player WHERE Percentage in (SELECT max(Percentage) FROM Player)
    NqG㦳̰Rvy
    G\Pe@ӽdҡAOΨ SQL OզXӹFP˪ĪGC
    dߵGG
    Name Percentage
    xP 88.97 
bWzdҤAڭ̤wgDpϥ count άO max ƨӶiέpAYnھȤPӶiέpANΨsիOuGROUP BYvAY٭nwANΨsձuHAVINGvA] SQL ҨϥΪ SELECT O榡N|ApUG SELECT W1, W2, ... FROM ƪW1, ƪW2, ... [WHERE ] [GROUP BY W1, W2, ...] [HAVING ] [ORDER BY W1, W2, ...] ڭ̰wsWԭzӻpUG HUOXӨϥΨ GROUP BY HAVING SQL dҡG
  1. SQLOGSELECT TeamID, count(*) as yH, avg(Percentage) as Rv FROM Player GROUP BY TeamID
    NqGCӲyyHƤΥRv
    Gavg(Percentage) iHpRvȡA SQL EXƦ Avg]ȡ^BCount]ơ^BMax]̤jȡ^BMin]̤pȡ^BStDev]s˥зǮt^BStDevp]sзǮt^BSum]`M^BVar]s˥ܲơ^BVarP]sܲơ^CѩoOCӲyέpƦrAҥHΨsիOuGROUP BYvC
    dߵGG
    TeamID yH Rv
    50.61666666666667 
    25.88 
    44.54333333333333 
    65.55 
    51.355 
    79.975 
    65.87 
  2. SQLOGSELECT TeamID, count(*) as yH FROM Player GROUP BY TeamID HAVING count(*)>2
    NqGCӲyyHơAuܲyHƤj 2 쪺
    Gavg(Percentage) iHpRvȡCѩoOCӲyέpƦrAҥHΨsիOuGROUP BYvAhϥΡuHAVINGvӫwC
    dߵGG
    TeamID yH

HWdҳOuw@ӸƪӶi˵dߡAUCdҫhOھڳoӸƪpʨӶi˵dߡC

  1. SQLOGSELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE ((Team.Name='x_') and (Player.TeamID=Team.ID))
    NqGx_y
    GѩӸƪ Name AҥHڭ̥ϥ Team.Name Player.Name ӰϤPƪCt~AoӸƪpʬO(Player.TeamID=Team.ID) ҫإߡAҥHb򪺽dҤAڭ̷|_ϥγoӬd߱C
    dߵGG
    Name Name Percentage
    x_ 50.26 
    x_ 67.88 
    x_ 36.67 
    x_ L| 54.77 
    x_ dd 55.87 
    x_ dӻ 38.25 
  2. SQLOGSELECT Team.Name, Player.Name, Percentage FROM Player, Team WHERE (Player.TeamID=Team.ID) and (Team.Name IN ('', 'x')) ORDER BY Team.Name, Percentage DESC
    NqGMxgƦ]
    dߵGG
    Name Name Percentage
     48.76 
    \Ů 47.65 
    LfS 37.22 
     xP 88.97 
     \廨 70.98 
  3. SQLOGSELECT Team.Name as yW, Team.WinNo as Ĺ, count(*) as yH, max(Percentage) as ̰Rv, min(Percentage) as ̧CRv, avg(Percentage) as Rv FROM Player, Team WHERE ((Player.TeamID=Team.ID)) GROUP BY Team.Name, Team.WinNo
    NqGCӲyέpƦr
    GѩoOCӲyέpƦrAҥHΨsիOuGROUP BYvCPɥѩQ줤ATeam.Name M Team.WinNo SΨEXơAҥHb GROUP BY ]n[WoC
    dߵGG
    yW Ĺ yH ̰Rv ̧CRv Rv
    10 48.76 37.22 44.54333333333333 
    x_ 12 67.88 36.67 50.61666666666667 
    xn 17 67.45 33.33 51.355 
    n붤 12 65.55 65.55 65.55 
     16 88.97 70.98 79.975 
    s˶ 25.88 25.88 25.88 
     11 65.87 65.87 65.87 

t~٦@Ǩϥ SQL ˵ƪdҡAi asp/example/database/selectQuery01.aspAŪ̦ۦլݬݡC

U@p`Npϥ SQL OӹƮwisWBקBRƵʧ@C


JScript {]pPΡGΩAݪ ASP