- 選用資料庫
USE practice
- 由表取得資料轉成視 (
VIEW)
CREATE VIEW qry AS SELECT ac.id, ac.email, ac.nick, dt.district, af.affect FROM ac_basic ac
LEFT JOIN district_tw dt ON dt.id=ac.district
LEFT JOIN status_affect af ON af.id=ac.affect
ORDER BY ac.id;
- 列出表、視
SHOW TABLES;
SHOW FULL TABLES;
- 顯示視的架構
SHOW CREATE TABLE qry;
SHOW COLUMNS FROM qry;
- 視的查詢
PREPARE stmt FROM 'SELECT id, email, nick, district, affect FROM qry WHERE district LIKE ? ORDER BY id';
SET @q='%北%';
EXECUTE stmt USING @q;
- 移除視
DROP VIEW IF EXISTS qry;