CREATE VIEW
Date: April 27th 2016
Last updated: April 27th 2016
A view is a virtual table. SQL collects the data when the view is called so it is always populated with the most current data.
useful resources
layout
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example
CREATE VIEW my_view AS
SELECT DISTINCT boardname
FROM board;
SELECT * from my_view;
/*
+---------------------+
| boardname |
+---------------------+
| |
| Baby Buggy |
| Bean Bag |
| Cherry Peppa |
... <- snipped ->
Example 2
CREATE VIEW my_boards AS
SELECT DISTINCT boardname, username
FROM surferprofile_board
JOIN auth_user
ON surferprofile_board.user_id = auth_user.id;
select * from my_boards;
/*
+---------------------+----------+
| boardname | username |
+---------------------+----------+
| Ducks Nuts | duck |
| Ducks Nuts (Thick) | duck |
| Fader | duck |
| Puzzle Piece | duck |
| Whiplash Taj Burrow | duck |
| Enough Said | duck |
... <- snipped ->
UPDATE view in example 2
CREATE OR REPLACE VIEW my_boards AS
SELECT DISTINCT boardname, boardmake
FROM surferprofile_board;
Query OK, 0 rows affected (0.01 sec)
/*
mysql> select * from my_boards;
+---------------------+---------------------------+
| boardname | boardmake |
+---------------------+---------------------------+
| | Hectic |
| Baby Buggy | Lost Surfboards |
| Bean Bag | Lost Surfboards |
| Cherry Peppa | Chilli Surfboards |
| Double Shot | DHD Surfboards |
| Double Shot (Thick) | DHD Surfboards |
... <- snipped ->
Create another view to use my_boards (Example 2)
CREATE VIEW board_manufacturers AS
SELECT DISTINCT boardmake
FROM my_boards;
select * from board_manufacturers;
/*
+---------------------------+
| boardmake |
+---------------------------+
| Hectic |
| Lost Surfboards |
| Chilli Surfboards |
| DHD Surfboards |
| Simon Anderson Surfboards |
| Rusty Surfboards |
| Superbrand Surfboards |
| MR Surfboards |
+---------------------------+
8 rows in set (0.00 sec)
DROP all VIEWs
DROP VIEW my_view;
Query OK, 0 rows affected (0.00 sec)
DROP VIEW my_boards;
Query OK, 0 rows affected (0.00 sec)
DROP VIEW board_manufacturers;
Query OK, 0 rows affected (0.00 sec)