接續 114 — Wildcards in Queries and Views
輸入：.backup music-backup2
輸出：無（備份數據庫）
輸入： DELETE FROM songs WHERE track<50;
輸出：無（刪除 songs table 裡面 track 小於 50 的 song）
輸入： SELECT * FROM songs;
輸出：
_id|track|title|album
198|60|Turkeys|177
912|53|My Brother Makes The Noises For The Talkies|177
1116|59|The Strain|177
（略刪）
4991|56|Button Up Your Overcoat|177
5256|50|Closer To You|254
5312|71|Re-Cycled Vinyl Blues — Neil Innes|177
只剩下幾首歌了
輸入： SELECT * FROM artist_list;
輸出：
artist|album|track|title
Bonzo Dog Band|Cornology|50|I Want To Be With You
Bonzo Dog Band|Cornology|51|Noises For The Leg
Bonzo Dog Band|Cornology|52|Busted
（略刪）
Bonzo Dog Band|Cornology|70|Labio-Dental Fricative — Vivian Stanshall Sean Head Showband
Bonzo Dog Band|Cornology|71|Re-Cycled Vinyl Blues — Neil Innes
Bonzo Dog Band|Cornology|72|Trouser Freak — Roger Ruskin Spear & His Giant Orchestral Wardrobe
J.J. Cale|Anyway The Wind Blows — The Anthology|50|Closer To You
輸入：SELECT * FROM artist_list WHERE track <>70;
輸出：
（略刪）
Bonzo Dog Band|Cornology|68|Bad Blood
Bonzo Dog Band|Cornology|69|Slush
Bonzo Dog Band|Cornology|71|Re-Cycled Vinyl Blues — Neil Innes
Bonzo Dog Band|Cornology|72|Trouser Freak — Roger Ruskin Spear & His Giant Orchestral Wardrobe
找出 track 不等於 70
輸入：SELECT count(*) FROM artist_list;
輸出：
count(*)
24
計算數量
輸入： .restore music-backup2
輸出：無（還原數據庫）
輸入： SELECT count(*) FROM artist_list;
輸出：
count(*)
5350
原本有這麼多
以下都是小挑戰
Tim老師用投影片出的題目沒有文檔
我就沒有貼上來了
輸入：
SELECT songs.title, albums.name FROM songs
INNER JOIN albums ON songs.album = albums._id
WHERE albums.name LIKE "%Forbidden%";輸出：
The Illusion of Power|Forbidden
Sick and Tired|Forbidden
Can’t Get Close Enough|Forbidden
Forbidden|Forbidden
Shaking Off the Chains|Forbidden
Get a Grip|Forbidden
Kiss of Death|Forbidden
Guilty as Hell|Forbidden
Rusty Angels|Forbidden
I Won’t Cry for You|Forbidden
輸入：
SELECT songs.title, albums.name, songs.track FROM songs
INNER JOIN albums ON songs.album = albums._id
WHERE albums.name LIKE "%Forbidden%"
ORDER BY songs.track;輸出：
The Illusion of Power|Forbidden|1
Get a Grip|Forbidden|2
Can’t Get Close Enough|Forbidden|3
Shaking Off the Chains|Forbidden|4
I Won’t Cry for You|Forbidden|5
Guilty as Hell|Forbidden|6
Sick and Tired|Forbidden|7
Rusty Angels|Forbidden|8
Forbidden|Forbidden|9
Kiss of Death|Forbidden|10
輸入：
SELECT songs.title, artists.name FROM songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name "Deep Purple";輸出：
Love Help Me (2000 Digital Remaster)|Deep Purple
Might Just Take Your Life|Deep Purple
And The Address (2000 Digital Remaster)|Deep Purple
（略刪）
The Mule|Deep Purple
Lucille|Deep Purple
Space Truckin’|Deep Purple
輸入：UPDATE artists SET name="One Kitten" WHERE name = "Mehitabel";
輸出：
3|One Kitten
輸入：
SELECT songs.title from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith"
ORDER BY songs.title;輸出：
(Dulcimer Stomp) The Other Side
(Going Down) Love In An Elevator
(Hoodoo) Voodoo Medicine Man
(Water Song) Janie’s Got A Gun
Adam’s Apple
Adam’s Apple
（略刪）
Write Me
You See Me Crying
Young Lust
輸入：
SELECT count(songs.title) from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith";輸出：
151
輸入：
SELECT DISTINCT songs.title from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith"
ORDER BY songs.title;輸出：
（略刪）
Walkin’ The Dog
What It Takes
Woman Of The World
Write Me
You See Me Crying
Young Lust
新功能 去掉重複
輸入：
SELECT count(DISTINCT songs.title) from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith";輸出：
128
計算去掉重複後的數量
原本我以為 DISTINCT 擺在 COUNT 前面
輸入：
SELECT count(DISTINCT artists.name) from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith";輸出：
1
輸入：
SELECT count(DISTINCT albums.name) from songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE artists.name = "Aerosmith";輸出：
13