How to create a MySQL viewSoon Sam SantosBlockedUnblockFollowFollowingJun 23Photo by Markus Spiske on UnsplashIn this tutorial I am going to teach you how to create a MySQL view so you can enhance your project and reduce the amount of code.
We will follow all the steps from creating a table, inserting values, facing the problem and then finally creating the view.
If you already know the first steps you can skip to the end of this article.
1.
Create the tablesWe will use two tables, one to store authors data and another one for the articles data.
CREATE TABLE authors ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(255), country VARCHAR(255));CREATE TABLE articles ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content VARCHAR(1000), topic VARCHAR(100), id_author INT NOT NULL FOREIGN KEY (id_author) REFERENCES authors(id));As you can see, authors will only have name and country.
While articles will have title, content, topic and the foreign key which stores the article’s author id.
Insert valuesI will insert one value to make it clear to understand.
INSERT INTO authors (author_name, country)VALUES ('Soon Sam', 'Brazil')INSERT INTO articles (title, content, topic, id_author)VALUES ('How to create a MySQL view using MySQL-Front', 'This will teach how to create a view', 'MySQL', 1)Generating meaningful dataTime to action, you’ll probably need to display this data combined to get a meaningful result.
Suppose we want to display all articles in a list, which will show only the article title, topic, author’s name and country.
For that we will need to filter the data and use JOIN .
SELECT title, topic, author_name, countryFROM articlesJOIN authors ON authors.
id = articles.
id_authorAnd we will have this beautiful output:Output from the SELECT aboveThe problem and the solution: viewImagine you have to show this data in multiple parts of your app and you have to write it all the time.
This SELECT is actually not so big, only 8 lines, but working in a real app you may face some 50+ lines SELECT statements and you’ll definitely not want to write it all the time.
This is the purpose of the view , you can create a view that once you call, it will display the code you wrote on it.
Let’s make it work so you’ll understand it better.
To create a view you just need to type CREATE VIEW `view_name` AS .
and paste your code in front of that.
CREATE VIEW view_article ASSELECT title, topic, author_name, countryFROM articlesJOIN authors ON authors.
id = articles.
id_author;You do it once and you will see a new TABLE in your database.
This is the view_article .
Call it as a normal SELECT statement.
SELECT * FROM view_article;Same output than beforeAnd you will have the same output with less code than before.
You can still make your WHERE, ORDERY BY and other conditions working with the view .
SELECT * FROM view_articleWHERE title LIKE '%How%';This is still going to work.
.. More details