Create views in MySQL
Create views in MySQL
1. Plan the view structure by identifying which columns from which tables need to be included, and use the SELECT statement to make sure all necessary columns are included.
Plan out the columns that need to be included in the view and what tables they would be obtained from. Use a SELECT statement to make sure all columns are included, and add or remove any columns as necessary. For example: SELECT table1.column1, table1.column2, table2.column3, table2.column4 FROM table1, table2 WHERE table1.column1 = table2.column1 AND table1.column3 = table2.column5
2. Use the CREATE OR REPLACE VIEW statement to create, name, and update a view.
For example, where My_sample_view is the name of the view: CREATE OR REPLACE VIEW My_Sample_View AS SELECT table1.column1, table1.column2, table2.column3, table2.column4 FROM table1, table2 WHERE table1.column1 = table2.column1 AND table1.column3 = table2.column5;
3. Use the SELECT statement to execute the view and verify output and performance.
Use the following syntax to check the view’s output: SELECT * from My_Sample_View;
4. Use the CREATE INDEX statement to create indexes on columns that have a numeric or long string output to improve performance when executed.
Executing views should take no longer than 30 seconds. If they do, you may need to create indexes on columns that have numeric or long string outputs. For example: CREATE INDEX ind_column1 ON table1(column1)
5. Use the ALTER VIEW statement to modify or update the already created view without dropping it.
For instance, the following example shows how to execute the view while excluding table2.column4. ALTER VIEW My_Sample_View AS SELECT table1.column1, table1.column2, table2.column3 FROM table1, table2 WHERE table1.column1 = table2.column1 AND table1.column3 = table2.column5;