接續 113 — More Complex Joins
輸入：SELECT * from songs WHERE songs.title LIKE "%doctor%";
輸出：
94|11|Doctor Brown|324
890|11|Just What The Doctor Ordered (Live At Hammersmith Odeon London 1977)|40
1042|5|Doctor Wu|389
1329|16|Doctor Wu|299
1574|11|You Shouldn’t Call The Doctor (If You Can’t Afford The Bills)|427
1619|6|Rock ’N’ Roll Doctor|407
1629|5|Flying Doctor|372
2041|13|Witchdoctor Woman|59
2764|5|Doctor|414
3357|1|Down At The Doctors|260
3451|4|Just What The Doctor Ordered|40
3484|8|Octave Doctors|219
4395|3|Doctor Doctor|203
4982|2|Call the Doctor|122
我想找一首歌，但歌名只記得一部分
這時候就可以這樣用 LIKE “%記得的部份%” 來查詢，而且不用在意大小寫
輸入： SELECT * FROM songs WHERE songs.title LIKE "for%";
輸出：
235|3|For Whom the Bell Tolls|56
281|1|For Those About To Rock (We Salute You)|90
1033|10|For Miss Caulker|73
1048|11|For a Brother|356
1282|1|For Your Love|328
1430|3|Ford O’ Kabul River|430
1545|8|Forty-Five Hundred Times|318
1778|7|For Crying Out Loud|290
2855|9|Forbidden|252
3045|2|For Your Life|409
3119|11|Forget Her|3
3865|7|FOR LADIES ONLY|393
4378|2|For Yasgur’s Farm|198
4398|2|Forty Summers|194
4752|5|Forever Angel|175
注意這次的 ％ 只加在後面，所以查到的都是 for 開頭
輸入：.schema
輸出：
CREATE TABLE songs (_id INTEGER PRIMARY KEY, track INTEGER, title TEXT NOT NULL, album INTEGER);
CREATE TABLE albums (_id INTEGER PRIMARY KEY, name TEXT NOT NULL, artist INTEGER);
CREATE TABLE artists (_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
輸入：
CREATE VIEW artist_list AS
SELECT artists.name, albums.name, songs.track, songs.title FROM songs
INNER JOIN albums ON songs.album= albums._id
INNER JOIN artists ON albums.artist = artists._id
ORDER BY artists.name, albums.name, songs.track;輸出：無
輸入：.schema
輸出：
CREATE TABLE songs (_id INTEGER PRIMARY KEY, track INTEGER, title TEXT NOT NULL, album INTEGER);
CREATE TABLE albums (_id INTEGER PRIMARY KEY, name TEXT NOT NULL, artist INTEGER);
CREATE TABLE artists (_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE VIEW artist_list AS
SELECT artists.name, albums.name, songs.track, songs.title FROM songs
INNER JOIN albums ON songs.ablum = albums._id
INNER JOIN artists ON albums.artist = artists._id
ORDER BY artists.name, albums.name, songs.track;
輸入：SELECT * FROM artist_list;
輸出：
（略刪）
ZZ Top|Tres Hombres|11|Waitin’ For The Bus (Live) (Bonus)
ZZ Top|Tres Hombres|12|Jesus Just Left Chicago (Live) (Bonus)
ZZ Top|Tres Hombres|13|La Grange (Live) (Bonus)
簡單的說，這邊創了一個 VIEW，這東西就是把我們之前查過的東西，存到裡面去
以後要用的時候就直接叫這個 VIEW，不用每次都打一長串指令或找文件上的指令複製過來
輸入：SELECT * FROM artist_list WHERE name LIKE "jefferson%";
輸出：
（略刪）
Jefferson Starship|Dragon Fly|6|Come To Life
Jefferson Starship|Dragon Fly|7|All Fly Away
Jefferson Starship|Dragon Fly|8|Hyperdrive
使用 view 的時候 table 的指令一樣能用
輸入：
CREATE VIEW album_list AS
SELECT name FROM albums
ORDER BY name輸出：無
輸入：SELECT * FROM album_list;
輸出：
（略刪）
XS All Areas
You Know Who You Are
Zappa In New York
Zooma
heavens to betsy
whip Jamboree
Tim 老師補充說，有些資料給外人用時，對方不需要看到全部
這時候就可以用上 VIEW
原本的 albums 有三個 col，一般人可能只會用到名子而已
輸入：.headers on
輸出：無
輸入：SELECT * FROM artist_list WHERE name LIKE "jefferson%";
輸出：
name|name:1|track|title
Jefferson Airplane|Surrealistic Pillow|1|She Has Funny Cars
Jefferson Airplane|Surrealistic Pillow|2|Somebody To Love
當初未指定 col 的名稱，由於有兩個都叫 name，系統就自動更蓋第二個名稱
但換用其他資料系統時未必會自動更名，也可能會直接崩潰
輸入：drop view artist_list;
輸出：無（把 view 給刪了，重新創一個）
刪除 view 指令 drop view view_name
刪除 table 指令 drop table table_name
Tim 老師有提醒，刪除 view 不會影響到資料庫
但是刪除 table 資料就不見了，要特別注意
輸入：
CREATE VIEW artist_list AS
SELECT artists.name AS artist, albums.name AS album, songs.track, songs.title FROM songs
INNER JOIN albums ON songs.album= albums._id
INNER JOIN artists ON albums.artist = artists._id
ORDER BY artists.name, albums.name, songs.track;輸出：無
輸入： SELECT * FROM artist_list WHERE artist LIKE "jefferson%";
輸出：
artist|album|track|title
Jefferson Airplane|Surrealistic Pillow|1|She Has Funny Cars
Jefferson Airplane|Surrealistic Pillow|2|Somebody To Love
Jefferson Airplane|Surrealistic Pillow|3|My Best Friend
只要當初創的時候指定名子即可