If you work regularly with MySQL, Mongo DB, AWS RDS, or DynamoDB, then you might find Navicat Premium useful. Navicat Premium helps you create views, queries and functions using an easy-to-use UIX. Even better, you can save your work into the cloud for reuse.
Here's a snapshot of the Navicat UIX. If you look to the left, you'll notice that there are views, queries, and functions.
Below are a few of my favorite examples of views, queries and functions.
Get Unique Data from a Single Column
Suppose you have a database filled with thousands of rows and one of the columns is titled Cities. You're interested in knowing how many unique cities are within the thousands of observations.
# Create a view that will only show unique values from within a single column. SELECT DISTINCT `column name` FROM `table name`
Modify a Column Name
Suppose you just want to change the name of a column, here's how to run that function.
ALTER TABLE MyTable CHANGE COLUMN `Full Name` to `Name`;
Split Full Name into Fname and Lname
Suppose you have a column titled Full Name and you want to split the information into two columns Fname and Lname. Here's how
ALTER TABLE emails # Drop the columns DROP COLUMN IF EXISTS `fname`, DROP COLUMN IF EXISTS `lname`, # Create the new columns ADD COLUMN `fname` VARCHAR(30) AFTER `Full Name`, ADD COLUMN `lname` VARCHAR(30) AFTER `fname`; UPDATE emails SET # Trim the white space `Full Name` = LTRIM(RTRIM(`Full Name`)), # Get the first name and copy it to a new column `fname` = SUBSTRING_INDEX(`Full Name`, ' ', 1), # Get the second name and copy it to a new column `lname` = SUBSTRING_INDEX(`Full Name`, ' ', -1)
Working with Money
Suppose you're researching apartment rental data and you get a CSV file that contains
, symbols inside the column with numerical data.
Before you can run any calculations, you'll first need to do a few things such as:
1. Remove the
, from the Rent column.
2. Convert the Rent into an Integer.
3. Assign the newly created Integers into a new column called Money.
4. Run a function (such as
AVG() to calculate an average.
5. Assign the results from
AVG() to a new column called Avg Rent.
CREATE DEFINER=`root`@`localhost` PROCEDURE `get high and low rent`( ) BEGIN # a. Use the two columns from the database SELECT `Rent`, `# of Bedrooms`, # b. Strip '$' and ',' # c. Convert `Rent` amount into an Integer # d. Assign the number to a variable named @money # e. Cast the variable as a column titled `Money` @money := CONVERT( REPLACE( SUBSTRING_INDEX(`Rent`,'$', -1), ',', ''), UNSIGNED INTEGER ) AS Money, # f. Now that @money is an Integer, calculate an average of all rows AVG(@money) AS `Avg Rent` # g. Run all of these operations on the database titled `MyAptData` FROM `MyAptData` # h. (optional) GROUP BY `# of Bedrooms`; END
Here's what it will look like in Navicat.