Navicat tips and tricks for developers working with MySQL.
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.
Navicat - Views
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`
Navicat - Queries
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`;
# 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)
Navicat - Functions
Working with Money
Suppose you're researching apartment rental data and you get a CSV file that contains $ and , 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 $ and , 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`( )
# 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`
# h. (optional)
GROUP BY `# of Bedrooms`;