
Photo by Arnaud Papa on Unsplash
Do you find yourself repeatedly typing the same query? Want to save it for reuse? A VIEW
to the rescue it is! In this blog post we will look at a scenario and a VIEW
that fits this very need.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and MySQL 5.7.21 for these exercises.
Tables and Data
Suppose we have these two tables in a relationship with a PRIMARY KEY
and a FOREIGN KEY
.
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| pipe_id | smallint(6) | NO | PRI | NULL | auto_increment |
| pipe_name | varchar(25) | NO | UNI | NULL | |
| joint_num | varchar(25) | NO | | NULL | |
| heat | varchar(25) | NO | | NULL | |
| pipe_length | decimal(4,2) | YES | | NULL | |
| has_degree | tinyint(1) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| pipe_id | smallint(6) | YES | MUL | NULL | |
| degree_amount | decimal(4,2) unsigned | YES | | NULL | |
+---------------+-----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
And let’s see what data is present in them both:
+---------+-------------+-----------+-------+-------------+------------+
| pipe_id | pipe_name | joint_num | heat | pipe_length | has_degree |
+---------+-------------+-----------+-------+-------------+------------+
| 215 | Joint-28 | 39393A | 9111 | 17.40 | 1 |
| 216 | Joint-88 | 19393Y | 9011 | 16.60 | 0 |
| 217 | Joint-98 | 39393V | 8171 | 10.40 | 0 |
| 218 | Joint-2228 | 34493U | 9100 | 11.80 | 1 |
| 219 | Joint-8819 | 18393z | 9159 | 13.00 | 0 |
| 220 | Joint-9844 | 19293Q | 8174 | 9.13 | 0 |
| 221 | Joint-7834 | 17QTT | 179 | 44.40 | 1 |
| 222 | Joint-171 | 34493U | 17122 | 9.45 | 1 |
| 223 | Joint-6844 | 17297Q | 6114 | 11.34 | 0 |
| 224 | Joint-4841 | 19395Q | 5144 | 7.43 | 0 |
| 225 | Joint-1228A | 34493U | 8575B | 15.22 | 1 |
| 226 | Joint-2128 | 34493C | 91 | 13.55 | 1 |
| 227 | Joint-222B | 34493U | 9100B | 21.63 | 1 |
+---------+-------------+-----------+-------+-------------+------------+
13 rows in set (0.01 sec)
+---------+---------------+
| pipe_id | degree_amount |
+---------+---------------+
| 215 | 12.50 |
| 218 | 22.50 |
| 221 | 10.00 |
| 222 | 4.00 |
| 225 | 10.25 |
| 226 | 5.75 |
| 227 | 11.75 |
+---------+---------------+
7 rows in set (0.00 sec)
I need to briefly dive into the has_degree
column.
Known as a boolean value (or MySQL’s version of one), this column is used to test for true or false.
If the value in this column is a 0 (zero) then it is considered false. Whereas anything else would be deemed true.
I chose a boolean data type for this column specifically since it is partially used. Every pipe does not have a degree value, hence not a bend.
To query and find the degree_amount
, in addition to, all the columns from the pipe
table, we can use an INNER JOIN
-> FROM pipe AS p
-> INNER JOIN degree_value AS h
-> ON
-> p.pipe_id = h.pipe_id
-> WHERE p.has_degree <> 0;
+-------------+-----------+-------+-------------+---------------+
| pipe_name | joint_num | heat | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28 | 39393A | 9111 | 17.40 | 12.50 |
| Joint-2228 | 34493U | 9100 | 11.80 | 22.50 |
| Joint-7834 | 17QTT | 179 | 44.40 | 10.00 |
| Joint-171 | 34493U | 17122 | 9.45 | 4.00 |
| Joint-1228A | 34493U | 8575B | 15.22 | 10.25 |
| Joint-2128 | 34493C | 91 | 13.55 | 5.75 |
| Joint-222B | 34493U | 9100B | 21.63 | 11.75 |
+-------------+-----------+-------+-------------+---------------+
7 rows in set (0.05 sec)
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
The key here is the WHERE
clause filter. Columns with a has_degree
value, not equal to zero (basically any value other than zero) are considered true according to the boolean condition. Meaning that row needs to be joined on the matching row from the degree_value
table. Therefore, retrieving the degree_amount
.
That’s great and wonderful but, retyping this query over and over, perhaps multiple times per day is not productive.
And unnecessary.
This presents a prime use case for a VIEW
.
This is more like it:
-> SELECT p.pipe_name, p.joint_num, p.heat, p.pipe_length, h.degree_amount
-> FROM pipe AS p
-> INNER JOIN degree_value AS h
-> ON
-> p.pipe_id = h.pipe_id
-> WHERE p.has_degree <> 0);
Query OK, 0 rows affected (0.05 sec)
Now we can SELECT
from the find_degree
VIEW
, same as a table.
See for yourself.
+-------------+-----------+-------+-------------+---------------+
| pipe_name | joint_num | heat | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28 | 39393A | 9111 | 17.40 | 12.50 |
| Joint-2228 | 34493U | 9100 | 11.80 | 22.50 |
| Joint-7834 | 17QTT | 179 | 44.40 | 10.00 |
| Joint-171 | 34493U | 17122 | 9.45 | 4.00 |
| Joint-1228A | 34493U | 8575B | 15.22 | 10.25 |
| Joint-2128 | 34493C | 91 | 13.55 | 5.75 |
| Joint-222B | 34493U | 9100B | 21.63 | 11.75 |
+-------------+-----------+-------+-------------+---------------+
7 rows in set (0.03 sec)
Not only can you SELECT
all columns, you can choose any number of them you need.
-> FROM find_degree;
+-------------+-------------+---------------+
| pipe_name | pipe_length | degree_amount |
+-------------+-------------+---------------+
| Joint-28 | 17.40 | 12.50 |
| Joint-2228 | 11.80 | 22.50 |
| Joint-7834 | 44.40 | 10.00 |
| Joint-171 | 9.45 | 4.00 |
| Joint-1228A | 15.22 | 10.25 |
| Joint-2128 | 13.55 | 5.75 |
| Joint-222B | 21.63 | 11.75 |
+-------------+-------------+---------------+
7 rows in set (0.00 sec)
As a matter of fact, If you find that result set useful, Views can be created from other Views.
Let’s do that now.
-> SELECT pipe_name, pipe_length, degree_amount
-> FROM find_degree);
Query OK, 0 rows affected (0.05 sec)
Selecting from the closer_look
VIEW
:
+-------------+-------------+---------------+
| pipe_name | pipe_length | degree_amount |
+-------------+-------------+---------------+
| Joint-28 | 17.40 | 12.50 |
| Joint-2228 | 11.80 | 22.50 |
| Joint-7834 | 44.40 | 10.00 |
| Joint-171 | 9.45 | 4.00 |
| Joint-1228A | 15.22 | 10.25 |
| Joint-2128 | 13.55 | 5.75 |
| Joint-222B | 21.63 | 11.75 |
+-------------+-------------+---------------+
7 rows in set (0.00 sec)
Will A View Have Current Data?
What if you INSERT
data into the tables that comprise the VIEW
‘s definition?
Will that data be available whenever selecting against the VIEW
?
Let’s find out with a quick example.
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO degree_value(pipe_id, degree_amount)
VALUES(LAST_INSERT_ID(),6.25);
Query OK, 1 row affected (0.03 sec)
Let’s query the find_degree
VIEW
and determine if it was updated with the previously inserted record:
+-------------+-----------+-------+-------------+---------------+
| pipe_name | joint_num | heat | pipe_length | degree_amount |
+-------------+-----------+-------+-------------+---------------+
| Joint-28 | 39393A | 9111 | 17.40 | 12.50 |
| Joint-2228 | 34493U | 9100 | -11.50 | 22.50 |
| Joint-7834 | 17QTT | 179 | 44.40 | 10.00 |
| Joint-171 | 34493U | 17122 | 9.45 | 4.00 |
| Joint-1228A | 34493U | 8575B | 15.22 | 10.25 |
| Joint-2128 | 34493C | 91 | 13.55 | 5.75 |
| Joint-222B | 34493U | 9100B | 0.00 | 11.75 |
| Joint-1619 | 93935 | T494 | 9.55 | 6.25 |
+-------------+-----------+-------+-------------+---------------+
8 rows in set (0.01 sec)
The new record with pipe_name
‘Joint-1619’ is present, proving Views display current data.
Benefits Of A View
These are a few benefits of a VIEW
…
- In a sense, views provide a named, reusable query.
- Views can abstract away sensitive data from users with limited credentials and minimal privileges.
- Views can provide an interface to end users without exposing any details of the back-end.
Limitations And Drawbacks
While studying Views, I found the below conclusions from the official documentation relevant and want to share them with you.
- A
VIEW
, and I quote the MySQL View documentation, is what is referred to as “frozen” at the time of creation. Signifying, any changes to the source tableSELECT
statement that provides the definition, will not affect theVIEW
. For example, if a column is dropped, selecting that column from theVIEW
would result in an error. Likewise, if a column is added to the source table, it would not be available in theVIEW
. - A Views definition has the following restrictions in regards to the
SELECT
statement:- Cannot refer to system or user-defined variables.
- Cannot refer to prepared statement parameters
- Cannot refer to parameters or local variables within a stored program.
- Cannot refer to a
TEMPORARY
table. - Views cannot be associated with a
TRIGGER
.
How’s Your View?
I hope through the examples provided in this blog post, you can implement your own
VIEW
when you need it. If you find yourself typing the same query over and over again, consider encapsulating it in aVIEW
.
Be sure to explore the official MySQL 5.7 Online Manual for an in-depth overview of all topics covered in this post.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Have I mentioned how much I love a cup of coffee?!?!To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
- Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.