Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

By: FYIcenter.com

Part:   1  2  3  4  5  

(Continued from previous part...)

How To Create a New View?

You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:

mysql> CREATE TABLE comment (faqID INTEGER, 
   message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)

mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW faqComment AS SELECT f.id, f.title, 
   f.description, c.message FROM faq f, comment c 
   WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM faqComment;
+----+-------------+-------------------------+-----------+
| id | title       | description             | message   |
+----+-------------+-------------------------+-----------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | I like it |
+----+-------------+-------------------------+-----------+
1 row in set (0.07 sec)

How To Drop an Existing View?

If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:

mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM faqComment;
ERROR 1146 (42S02): Table 'fyi.faqcomment' doesn't exist

Part:   1  2  3  4  5