MySQL Views: How to Create View from Tables
้ฉฌๅบๆฏยท่พไผฆ
โก ๆบ่ฝๆ่ฆ
MySQL Views are virtual tables that store no data of their own and instead present the result of a stored SELECT query. This resource explains how to create, query, join, and drop views, with runnable examples from the myflixdb database.
ไปไนๆฏ่งๅพ MySQL?
ๆฏ่ง ๆฏ่ๆ่กจ๏ผไธๅญๅจไปปไฝ่ชๅทฑ็ๆฐๆฎ๏ผไฝๆพ็คบๅญๅจๅจๅ ถไป่กจไธญ็ๆฐๆฎใๆขๅฅ่ฏ่ฏด๏ผ่งๅพๅชไธ่ฟๆฏ SQL ๆฅ่ฏขใ่งๅพๅฏไปฅๅ ๅซ่กจไธญ็ๆๆ่กๆๅ ่กใ MySQL ่งๅพๅฏไปฅๆพ็คบไธไธชๆๅคไธช่กจ็ๆฐๆฎใ
MySQL ่งๅพ่ฏญๆณ
็ฐๅจ่ฎฉๆไปฌ็ไธไธๅจ MySQL.
CREATE VIEW `view_name` AS SELECT statement;
NeoCity
- โๅๅปบ่งๅพ`view_name`โ ๅ่ฏ MySQL ๆๅกๅจๅจๆฐๆฎๅบไธญๅๅปบไธไธชๅไธบโview_nameโ็่งๅพๅฏน่ฑก
- โAS SELECT ่ฏญๅฅโ ๆฏ่ฆๆๅ ็ SQL ่ฏญๅฅ MySQL ่งๅพใๅฎๅฏไปฅๆฏไธๆก SELECT ่ฏญๅฅ๏ผๅฏไปฅๅ ๅซๆฅ่ชไธไธชๆๅคไธช่กจ็ๆฐๆฎใ
ไธบไปไน่ฆไฝฟ็จ่งๅพ๏ผ
Before building one, it helps to know what a view actually buys you.
ๆจๅฏ่ฝๅธๆไฝฟ็จ่งๅพไธป่ฆๅบไบไปฅไธ 3 ไธชๅๅ
- Ultimately, you will use your SQL knowledge, to create applications, which will use a database for data requirements. Itโs recommended that you use VIEWS of the original table structure in your application instead of using the tables themselves. This ensures that when you refactor your DB, your legacy code will see the original schema via the view without breaking the application.
- VIEWS ๆ้ซไบๅฏ้็จๆงใๆจไธๅฟ ้ๅคๅๅปบๆถๅ่ฟๆฅ็ๅคๆๆฅ่ฏขใๆๆๅคๆๆง้ฝ่ฝฌๆขไธบไฝฟ็จ VIEWS ็ไธ่กๆฅ่ฏขใ่ฟ็ง็ฒพ็ฎ็ไปฃ็ ๅฐๆดๅฎนๆ้ๆๅฐๆจ็ๅบ็จ็จๅบไธญใ่ฟๅฐๆถ้คๆผๅ้่ฏฏ็ๆบไผ๏ผๅนถไธๆจ็ไปฃ็ ๅฐๆดๅ ทๅฏ่ฏปๆงใ
- ่งๅพๆๅฉไบๆฐๆฎๅฎๅ จใๆจๅฏไปฅไฝฟ็จ่งๅพไป ๅ็จๆทๆพ็คบๆๆไฟกๆฏๅนถ้่ไฟก็จๅกๅท็ญๆๆๆฐๆฎใ
ๅฆไฝๅจ MySQL
With the syntax and the benefits established, the steps below build a working view end to end.
ไปฅไธๆฏๅๅปบ่งๅพ็ๅๆญฅ่ฟ็จ MySQL:
ๆญฅ้ชค1๏ผ ไฝฟ็จโmyflixdbโๅๅปบๆไปฌ็็ฌฌไธไธช่งๅพ
็ฐๅจ่ฎฉๆไปฌไฝฟ็จโmyflixdbโๅๅปบๆไปฌ็็ฌฌไธไธช่งๅพ๏ผๆไปฌๅฐๅๅปบไธไธช็ฎๅ็่งๅพๆฅ้ๅถๆๅ่กจไธญ็ๅฐ็ๅใ
Suppose authorization requirements state that the accounts department can only see memberโs number, name and gender from the memberโs table. To achieve this you can create a VIEW โ
CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;
ๆญฅ้ชค2๏ผ ๅฑๅผ่งๅพ่็น
ๅจไปฅไธไฝ็ฝฎๆง่กไธ่ฟฐ่ๆฌ MySQL ้ๅฏน myflixdb ็ๅทฅไฝๅฐๅนถๅฑๅผๆฐๆฎๅบ่ตๆบ็ฎก็ๅจไธญ็่งๅพ่็น๏ผๆไปฌๅพๅฐไปฅไธ็ปๆใ
่ฏทๆณจๆ๏ผaccounts_v_members ๅฏน่ฑก็ฐๅจๅจๆฐๆฎๅบ่งๅพๅฏน่ฑกไธญๅฏ่งใ
ๆญฅ้ชค3๏ผ ๆง่ก SELECT ่ฏญๅฅ
็ฐๅจ่ฎฉๆไปฌๆง่กไธไธช ้ๆฉ่ฏญๅฅ ไป่งๅพไธญ้ๆฉๆๆๅญๆฎต๏ผๅฆไธๆ็คบ MySQL ๅๅปบ่งๅพ็คบไพใ
SELECT * FROM `accounts_v_members`;
ๆญฅ้ชค4๏ผ ๆง่ก่ๆฌ
ๅจไปฅไธไฝ็ฝฎๆง่กไธ่ฟฐ่ๆฌ MySQL ้ๅฏน myflixdb ็ๅทฅไฝๅฐไธบๆไปฌๆไพไบไปฅไธๆพ็คบ็็ปๆใ
| membership_number | full_names | gender |
|---|---|---|
| 1 | Janet Jones | Female |
| 2 | Janet Smith Jones | Female |
| 3 | Robert Phil | Male |
| 4 | Gloria Williams | Female |
| 5 | Leonard Hofstadter | Male |
| 6 | Sheldon Cooper | Male |
| 7 | Rajesh Koothrappali | Male |
| 8 | Leslie Winkle | Male |
| 9 | Howard Wolowitz | Male |
Only the authorized columns for accounts department have been returned. Other details found in the members table have been hidden.
ๅฆๆๆไปฌๆณๆฅ็็ปๆ็นๅฎ่งๅพ็ SQL ่ฏญๅฅ๏ผๆไปฌๅฏไปฅไฝฟ็จไธ้ขๆพ็คบ็่ๆฌๆฅๆง่กๆญคๆไฝใ
SHOW CREATE VIEW `accounts_v_members`;
ๆง่กไธ่ฟฐ่ๆฌๅฐไธบๆจๆไพ่งๅพๅ็งฐๅ็จไบๅๅปบ่งๅพ็ SQL SELECT ่ฏญๅฅใ
่ฟๆฅๅ่งๅพ MySQL
A single-table view is the simplest case. Views become far more valuable when they wrap a multi-table query.
็ฐๅจ่ฎฉๆไปฌ็ไธไธช็ธๅฝๅคๆ็ไพๅญ๏ผๅฎๆถๅๅคไธช่กจๅ็จ้ ๅ ๅ ฅ.
ๆไปฌๅฐๆๅ ๅๅปบ็ JOIN๏ผ่ฏฅ JOIN ไปไธ (3) ไธช่กจ๏ผๅณไผๅใ็ตๅฝฑๅ็ตๅฝฑ็ง่ต๏ผไธญ่ทๅไฟกๆฏใไปฅไธๆฏๅธฎๅฉๆไปฌๅฎ็ฐๆญค็ฎ็็่ๆฌใ
CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;
ๆง่กไธ่ฟฐ่ๆฌไผๅจๆไปฌ็ myflixdb ไธญๅๅปบๅไธบ general_v_movie_rentals ็่งๅพ
็ฐๅจ่ฎฉๆไปฌไปๅไธบ general_v_movie_rentals ็่กจไธญ้ๆฉๆๆๅญๆฎตใ
SELECT * FROM `general_v_movie_rentals`;
ๅจไปฅไธไฝ็ฝฎๆง่กไธ่ฟฐ่ๆฌ MySQL ๅทฅไฝๅฐ ้ๅฏน myflixdb ๆไปฌๅพๅฐไบไปฅไธๆพ็คบ็็ปๆใ
| membership_number | full_names | title | transaction_date | return_date |
|---|---|---|---|---|
| 1 | Janet Jones | Pirates of the Caribean 4 | 20-06-2012 | 28-06-2012 |
| 1 | Janet Jones | Forgetting Sarah Marshal | 22-06-2012 | 25-06-2012 |
| 3 | Robert Phil | Forgetting Sarah Marshal | 22-06-2012 | 25-06-2012 |
| 2 | Janet Smith Jones | Forgetting Sarah Marshal | 21-06-2012 | 24-06-2012 |
| 3 | Robert Phil | X-Men | 23-06-2012 | 28-06-2012 |
่ฏทๆณจๆ๏ผๆไปฌไธๅฟ ็ผๅๅคๆ็ JOIN ๆฅ่ฏขๆฅ่ทๅๆๅ ณไผๅใ็ตๅฝฑๅ็ตๅฝฑ็ง่ต่ฏฆ็ปไฟกๆฏ็ไฟกๆฏใๆไปฌๅช้ๅจๅธธ่ง SELECT ่ฏญๅฅไธญไฝฟ็จ่ฏฅ่งๅพ๏ผๅฐฑๅไปปไฝๅ ถไปๆฎ้่กจไธๆ ทใๅฏไปฅไปๅจ myflixdb ไธ่ฟ่ก็ๅบ็จ็จๅบ็ณป็ปไธญ็ไปปไฝไฝ็ฝฎ่ฐ็จ่ฏฅ่งๅพใ
Dropping ๆต่งๆฌกๆฐ MySQL
DROP ๅฝไปคๅฏ็จไบไปไธญๅ ้ค่งๅพ ๆฐๆฎๅบ ไธๅ้่ฆใๅ ้ค่งๅพ็ๅบๆฌ่ฏญๆณๅฆไธใ
DROP VIEW `general_v_movie_rentals`;
Dropping a view removes only the stored query; the underlying tables and their data are untouched.
