SQL views are virtual tables that allow you to access data from one or more tables in a database as if they were a single table. Views provide a way to simplify complex data structures and improve the readability and security of your queries.
Creating a View
A view is created using the CREATE VIEW
statement. The syntax for creating a view is as follows:
CREATE VIEW view_name AS SELECT column_name1, column_name2, ... FROM table_name WHERE condition;
For example, let’s consider a table named employees
with the following data:
id | name | department | salary |
---|---|---|---|
1 | John Doe | Sales | 50000 |
2 | Jane Doe | Marketing | 60000 |
3 | Jim Smith | IT | 65000 |
We can create a view that shows only the names and salaries of employees in the IT department:
CREATE VIEW it_employees AS SELECT name, salary FROM employees WHERE department = 'IT';
Now, we can query the it_employees
view just like a regular table:
SELECT * FROM it_employees; /* Output: name salary ---------- ---------- Jim Smith 65000 */
Updating a View
Views are typically read-only, meaning that you can only query data from them and not modify the data. However, some databases allow you to update data through a view if certain conditions are met, such as having a single table underlying the view and not having any aggregate functions in the SELECT statement.
The syntax for updating a view is as follows:
UPDATE view_name SET column_name1 = value1, column_name2 = value2, ... WHERE condition;
For example, let’s say we want to give a 10% raise to the IT employee with the name “Jim Smith”. We can update the view as follows:
UPDATE it_employees SET salary = salary * 1.10 WHERE name = 'Jim Smith';
This will update the underlying employees
table as well:
SELECT * FROM employees WHERE department = 'IT'; /* Output: id name department salary -- ---- ---------- ------ 3 Jim Smith IT 71500 */
Deleting a View
To delete a view, you use the DROP VIEW
statement. The syntax is as follows:
DROP VIEW view_name;
For example, if we no longer need the it_employees
view, we can delete it as follows:
DROP VIEW it_employees;
Conclusion
SQL views are a useful tool for organizing and simplifying data structures in your database. They provide a way to encapsulate complex data structures and improve the readability and security of your queries. Just remember that views are typically read-only and that updating a view may not be possible in all databases.