If I have a column that needs to be cleaned or tested in some way. For example:
Select mycolumn, if(mycolumn is null, TRUE, FALSE) as mycolumnisnull from mytableThis example is not that amazing, but it lets you place conditions for the data. Then instead of re-writing these conditions in your application layer, you can re-use the View from the database.
Select email, regex ['some amazing email verification/cleaning regex line'] as cleaned_emails from emailsHere again, we have some condition that placed in the database. We can now call the emails table and tell it to give us only the cleaned emails according to the defined conditions.
You can place some business logic in your Views. I wrote about it in my blog before.
This time I will just use an example:
Select business_loan, if (business_loan > 30000, 'Allow Discount', 'No Discount') as discount from loansSo here, you have an embedded rule that says if the loan is over 30,000, then give a discount. Otherwise, no discount.
There are advantages and disadvantages to using business rules in your database. One advantage is the visual way of verifying that your code is working. You can see next to your data if your code works or if you need to make changes.
Displaying parts of many-column tables
Very simply, if you know you only need a few columns from a table with many columns, you can just specify it in a View. It just keeps things tidier.
Create View contact_detailsI have used this (a lot) for finding tables to extract data from when I did some ETL-data warehouse work. This is an example:
Select col11 as firstname, col52 as lastname, col37 phonenumber from 100columntable
Create View src_thetableiwaslookingforThis helped me a lot in the past and also helped when I worked with other developers on the same project. We just all used the same interfaces (Views).
Select * from database1.tablethatiwant
Fashioning your data
You can use some basic text manipulation on your data to help you display it better.
Select concat(title,'. ',firstname, ' ', lastname) as fullname from contact_details.Here, we just added the title, first name and last name into one string (example, Mr. John Smith).
If you really want to go crazy, you can do this
Create View HTML_export
Select concat('[tr] [td] ',firstname,'[/td] [td] ',lastname,'[/td] [/tr] ') as list from contact_details
So, if in the future you want to get a quick list of all the contact names in HTML format, you can just use the View.
Views are very useful :)
Especially if you dislike long-winded languages when you can do it in 1 line of SQL.
I plan on writing a more detailed article soon about this topic