We use SQL or Structured Question Language to speak with a relational database and create SQL views.
What are SQL Views? They’re digital tables that mix and retailer knowledge from precise database tables that you simply create. In flip, you get safe and simplified entry to your dataset whereas hiding the underlying desk buildings and implementations.
How does this work? You already know that SQL queries are used to create SQL views. When the SQL view is created, the underlying SQL question is saved. While you use an SQL question to search for knowledge from a number of tables, the question runs and helps you retrieve knowledge from corresponding database tables. Equally, once you question a created SQL view, the saved question is first retrieved after which executed.
How is creating SQL views helpful?
As a result of SQL views are supported by saved SQL queries, they assist safe your database. Amongst different issues, in addition they simplify advanced searches and enhance the efficiency of your searches. Let’s examine how creating SQL views can profit your database and your purposes:
#1. Supplies database safety
SQL views act as digital tables, hiding the buildings of the particular database tables. So simply by a view it isn’t potential to know what number of tables there are and what the totally different columns are within the desk. As well as, you may add entry management restrictions to your database so {that a} person can solely question the view and never the precise tables.
#2. Simplifies advanced searches
Usually your SQL queries span a number of tables with advanced be part of situations. When you use such advanced queries often, you may faucet into the facility of SQL view creation. It can save you your advanced question in your SQL view. This lets you merely question the view, as an alternative of getting to run the entire advanced question.
#3. Reduces schedule adjustments
In case your underlying desk buildings are modified, similar to including or eradicating columns, your views stay unchanged. When you depend on your SQL views for all of your queries, you do not have to fret in regards to the schema change. Since they’re digital tables, your created SQL view will proceed to work seamlessly.
#4. Improves question efficiency
While you create SQL views, the database engine optimizes the underlying question earlier than saving it. A saved question runs quicker than working the identical question instantly. So, utilizing SQL views to question your knowledge will provide you with higher efficiency and quicker outcomes.
Find out how to create SQL views?
To create SQL views, you need to use the CREATE VIEW command. A view accommodates a SELECT assertion. This offers the question that can be saved within the view. The syntax is as follows:
CREATE VIEW view_name AS
SELECT
column_1,
column_2,
column_3...
FROM
table_name_1, table_name_2
WHERE
condition_clauseLet’s perceive this by way of an instance. Bear in mind that you’ve got created two tables division And worker. The division accommodates the identify of the division and its ID. Accordingly, the worker desk accommodates the identify and ID of the worker, together with the ID of the division to which he belongs. You utilize these two tables to create your SQL view.
Create your database tables
| department_id | Division identify |
|---|---|
| 1 | Finance |
| 2 | Expertise |
| 3 | Firm |
If you’re utilizing MySQL, you may create this desk utilizing the next question:
CREATE TABLE `division` (
`department_id` int,
`department_name` varchar(255),
PRIMARY KEY (`department_id`)
);Insert knowledge into the desk you created utilizing the SQL beneath:
INSERT INTO division VALUES (1, 'Finance');
INSERT INTO division VALUES (2, 'Expertise');
INSERT INTO division VALUES (3, 'Enterprise');| employee_id | Title worker | department_id |
|---|---|---|
| 100 | John | 3 |
| 101 | Mary | 1 |
| 102 | Natalya | 3 |
| 103 | Bruce | 2 |
With the division desk prepared and full of knowledge, create the worker desk utilizing the MySQL question beneath:
CREATE TABLE `worker` (
`employee_id` INT,
`employee_name` VARCHAR(255),
`department_id` INT,
FOREIGN KEY (`department_id`) REFERENCES division(department_id)
);Then enter the worker information in your desk. Please word that for the reason that department_id column has a overseas key reference with the division desk, you can’t insert a department_id which isn’t current within the different desk.
INSERT INTO worker VALUES (100, 'John', 3);
INSERT INTO worker VALUES (101, 'Mary', 1);
INSERT INTO worker VALUES (102, 'Natalya', 3);
INSERT INTO worker VALUES (103, 'Bruce', 1);Question your database tables
Let’s use the tables in a database question. Take the case the place you should question for the employee_id, Title workerAnd Division identify for all workers. On this case, you should use each tables and concatenate them utilizing the frequent column, viz department_id. So your query turns into:
SELECT
employee_id,
employee_name,
department_name
FROM
worker,
division
WHERE
worker.department_id = division.department_id;
Create your SQL view
You could usually search for or confer with this data. As well as, your search time would improve over time as extra information exist in your tables. In such a state of affairs, you may create the SQL view that corresponds to this question.
Use the next question to create a view named employee_info:
CREATE VIEW employee_info AS
SELECT
employee_id,
employee_name,
department_name
FROM
worker,
division
WHERE
worker.department_id = division.department_id;
With this view, you may instantly question the identical. Your query is due to this fact simplified to:
SELECT
*
FROM
employee_info;When you run a question in SQL view, you get the identical output as once you ran your unique question. Nevertheless, your search is now straightforward to keep up. The show hides the complexity of your question with none compromise on end result or efficiency.
Find out how to exchange SQL views?
If in case you have a view with a specific identify and also you need to change or exchange it, use the CREATE OR REPLACE VIEW command. Means that you can change the SELECT assertion used to create the view. Due to this fact, your view output is changed whereas conserving the view identify intact. As well as, create a brand new SQL view if it would not exist already.
You’ll be able to exchange SQL views utilizing the next syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT
new_column_1,
new_column_2,
new_column_3 ...
FROM
new_table_name_1,
new_table_name_2 ...
WHERE
new_condition_clauseYou’ll be able to perceive this higher with an instance. Remember the division and the worker tables. A employee_info From this, an SQL view is created, which accommodates the columns employee_id, employee_name, and division identify.
For safety causes, chances are you’ll need to obtain the employee_id faraway from this view. As well as, you additionally need to change the column names to cover the precise database columns. You may make these adjustments to the present view utilizing the next SQL question:
CREATE OR REPLACE VIEW employee_info AS
SELECT
employee_name as identify,
department_name as division
FROM
worker,
division
WHERE
worker.department_id = division.department_id;
As soon as the view has been changed, you’re going to get totally different outcomes when you use the identical search you used earlier than. For instance, the question end result exhibits that one of many columns has been eliminated. As well as, the column names have been modified from Title worker And Division identify Disagreeable identify And division, respectively.
Harness the facility of SQL view creation
By creating SQL views, you not solely get simplified queries and quicker knowledge searches, but additionally safety and safety towards schema adjustments. Creating SQL views is sort of straightforward. You’ll be able to flip any current question you have got right into a SQL view.
Use the step-by-step information above to create SQL views and benefit from them. With SQL views, you may make your database purposes easy and safe whereas enhancing their efficiency.
If you wish to handle your individual SQL server, take a look at SQL Server Administration Studio (SSMS).