Tutorial by Examples

Privileges The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have ...

A view is most useful when it can be used to pull in data from more than one table. CREATE VIEW myview AS SELECT a.*, b.extra_data FROM main_table a LEFT OUTER JOIN other_table b ON a.id = b.id In mysql views are not materialized. If you now perform the simple query SELECT * FROM myview, my...

A VIEW acts very much like a table. Although you can UPDATE a table, you may or may not be able to update a view into that table. In general, if the SELECT in the view is complex enough to require a temp table, then UPDATE is not allowed. Things like GROUP BY, UNION, HAVING, DISTINCT, and some su...

-- Create and drop a view in the current database. CREATE VIEW few_rows_from_t1 AS SELECT * FROM t1 LIMIT 10; DROP VIEW few_rows_from_t1; -- Create and drop a view referencing a table in a different database. CREATE VIEW table_from_other_db AS SELECT x FROM db1.foo WHERE x IS NOT NULL; DROP V...