Java 學習記錄112 — SQL Order by and Joins

張小雄
2 min readJan 28, 2022

--

接續 111 — Querying Data With SQL

輸入:SELECT * from artists;

輸出:

_id|name

1|Mahogany Rush

2|Elf

3|Mehitabel

4|Big Brother & The Holding Company

5|Roy Harper

6|Pat Benatar

不管輸入幾次順序都是這樣,這是因為用 PRIMARY KEY 的關係

輸入: SELECT * FROM artists ORDER BY name;

輸出:

_id|name

11|1000 Maniacs

86|10cc

66|AC DC

152|Aerosmith

178|Alice Cooper

104|Allan Holdsworth

按照姓名來排列

輸入: SELECT * FROM albums ORDER BY name;

輸出:

_id|name

11|1000 Maniacs

86|10cc

66|AC DC

(略刪)

398|Zappa In New York|140

364|Zooma|187

325|heavens to betsy|72

430|whip Jamboree|72

可以看到最後兩行不是 Z 開頭,那是因為是小寫

輸入:SELECT * FROM albums ORDER BY name COLLATE NOCASE;

輸出:

_id|name|artist

54|18 Singles|193

281|1984|84

372|25 Years On|28

(略刪)

347|XS All Areas|29

173|You Know Who You Are|3

398|Zappa In New York|140

364|Zooma|187

這樣可以忽略大小寫來排,最後兩行現在是 Z 開頭了

輸入:SELECT * FROM albums ORDER BY name COLLATE NOCASE DESC;

輸出:

_id|name|artist

364|Zooma|187

398|Zappa In New York|140

173|You Know Who You Are|3

(略刪)

372|25 Years On|28

281|1984|84

54|18 Singles|193

更改排列順序,變成降序了

輸入:SELECT * FROM albums ORDER BY artist, name COLLATE NOCASE;

輸出:

_id|name|artist

210|Mahogany Rush IV|1

140|Mahogany Rush Live|1

227|Carolina County Ball|2

176|Trying To Burn The Sun|2

(略刪)

421|Burn|196

332|Come Taste The Band|196

10|Concerto For Group and Orchestra|196

161|Deep Purple|196

130|Deep Purple In Rock|196

71|Fireball 25th Anniversary Edition|196

75|Machine Head (1997 Remixes)|196

150|Made In Europe|196

92|Made In Japan|196

88|Shades Of Deep Purple|196

162|Stormbringer|196

368|The Book Of Taliesyn|196

76|Who Do We Think We Are Remastered Edition|196

看到結尾 196 那組,前面姓名也有按照順序

所以排順序的欄位可以超過一個

輸入:SELECT * FROM songs ORDER BY album,track;

輸出:

(略刪)

2125|10|Lost For Words|438

3439|11|High Hopes|438

4375|1|All You Ever Wanted|439

4057|2|I Got Mine|439

1121|3|Strange Times|439

2784|4|Psychotic Girl|439

3851|5|Lies|439

2198|6|Remember When (Side A)|439

2350|7|Remember When (Side B)|439

3491|8|Same Old Thing|439

1654|9|So He Won’t Break|439

4784|10|Oceans And Streams|439

5021|11|Things Ain’t Like They Used To Be|439

同一張專輯的歌,按照專輯裡的順序排列

輸入:SELECT * FROM albums WHERE _id=439;

輸出:

_id|name|artist

439|Attack & Release|133

輸入:SELECT * FROM artists WHERE _id=133;

輸出:

_id|name

133|Black Keys

查編號349的專輯名稱跟其歌手是哪位

但這樣查也太不方便

輸入:SELECT songs.track, songs.title, albums.name FROM songs JOIN albums ON songs.album = albums._id;

輸出:

track|title|name

2|I Can’t Quit You Baby|BBC Sessions

1|Taking the Easy Way Out Again|Rhinos Winos and Lunatics

6|Let’s Have A Party|Private Practice

7|Flaming Telepaths|Champions Of Rock

11|Yearnin’|The Big Come Up

1|Bat Out Of Hell|Bat Out Of Hell

(略刪)

把兩張表合併起來了

之前 songs 裡面的資料是長這樣

5350|4|Just Walk In My Shoes (2004 Digital Remaster)|118

現在 songs表 最後面的 album,直接變成 albums表 裡面的 name 了

Tim 老師補充:前面選擇欄位時可以把 songs.track 的 songs 給省略,但以後用 Java 操作的時候可能會忘記到底用了哪個表的欄位

所以加上是哪張表會是一個好習慣

輸入:SELECT songs.track, songs.title, albums.name FROM songs JOIN albums ON songs.album = _id;

輸出:

Error: in prepare, ambiguous column name: _id (1)

有點像這種情況,最後面原來是 albums._id 現在省略成 _id,現在系統不知道你說的是哪張表的 id

輸入: SELECT songs.track, songs.title, albums.name FROM songs INNER JOIN albums ON songs.album = albums._id;

輸出:(略刪)

結果跟上面一樣,但上面是省略 INNER,老師說其他資料庫可能不行省略,所以還是保持好習慣加上比較好

--

--