Navicat MySQL: Tips and Tricks
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.