If you work regularly with MySQL or MariaDB, then you will probably find Navicat Premium or Navicat for MySQL to be indispensable. In addition to MySQL and MariaDB, Navicat for MySQL also supports a number of cloud services, including Amazon RDS, Amazon Aurora, Oracle Cloud, Google Cloud, Microsoft Azure, Alibaba Cloud, Tencent Cloud and Huawei Cloud. Navicat Premium is a database development tool that allows you to simultaneously connect to MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL, and SQLite databases from a single application, and is also compatible with cloud databases. Both help you create views, queries and functions using an easy-to-use GUI interface. Moreover, you can save your work to the Cloud for reuse and collaborating with team members.
In today's blog, I'll be sharing a few tips and tricks for MySQL that you can apply using either Navicat for MySQL or Navicat Premium.
1: Retrieve Unique Values from a Single Column
Suppose you have a database filled with thousands of employee records and youd like to know how many unique employee last names there are within the thousands of rows. We can create a SELECT DISTINCT query that will do that:
SELECT DISTINCT
lastname
FROM
employees
ORDER BY lastname;
Rather than execute the above query every time that we wanted to see the distict employees we could create a view that we can execute queries against:
CREATE VIEW distinct_emp_names AS
SELECT DISTINCT
lastname
FROM
employees
ORDER BY lastname;
Here are the results:
2: Retrieve Unique Data from Multiple Columns
The DISTINCT clause also works with more than one column. In this case, MySQL relies on the combination of values in these columns to determine their uniqueness in the result set. For example, to get the unique combination of city and state from a table, you can create the following view:
CREATE VIEW distinct_cities_and_states AS
SELECT DISTINCT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY state, city;
Here are the results of the view:
3: Modify a Column Name
Suppose you just want to change the name of a column, you could run an ALTER TABLE statement to do so.
ALTER TABLE MyTable CHANGE COLUMN `Old Name` to `New Name`;
In Navicat, if you right-click a field in the Table Designer, you can choose to add, insert, delete and, of course, rename the field:
4: Split a Full Name into First and Last Names
There is often a need to split a column that contains a full name (i.e. full_name) into two columns, such as first_name and last_name. Here's how using the ALTER TABLE statement:
ALTER TABLE emails
ADD COLUMN `first_name` VARCHAR(30) AFTER `full_name`,
ADD COLUMN `last_name` VARCHAR(30) AFTER `first_name`;
UPDATE emails
SET
# Trim the white space
`full_name` = LTRIM(RTRIM(`full_name`)),
# Get the first name and copy it to a new column
`first_name` = SUBSTRING_INDEX(`full_name`, ' ', 1),
# Get the second name and copy it to a new column
`last_name` = SUBSTRING_INDEX(`full_name`, ' ', -1)
Here's is the above statement as is appears in the Navicat Query Editor:
Conclusion
In today's blog we learned a few tips and tricks for MySQL that we can apply using either Navicat Premium or Navicat for MySQL. Navicat database management tools make most DBA and development tasks a lot easier to carry out. Try them for yourself; both come with a 14 day free trial!
