Getting started with MySQL database on Mac

Who is this for?

• You want to learn SQL using MySQL as a newbie.
• You want a solid MySQL database running on your laptop for data analysis.

Then keep reading.

1. Install MySQL on Mac

MySQL, the most popular open-source database, doesn’t come by default in macOS. You have to install it on your Mac first. There are many ways to do it and some common methods are listed below:

• Install the official package that includes MySQL Server and several command line utilities.
• Install via Docker
• Install via homebrew service
• Install via DBngin.

In this post, we are going to use DBngin as it’s the easiest way to setup and manage MySQL Server on Mac. No command lines required, no complex configuration, within a couple of clicks, you can install not only one MySQL Server but also many servers with various versions and ports, and get them run concurrently.

To install DBngin:

• Go to DBngin and download the installer.
• Install on Mac like any other Mac apps.
• Run DBngin and choose to create a new server.
• Choose MySQL, its version and create a server with a custom name.
• Start the server from the server controller.

It’s that simple, hit start when you need, and stop when you’re done.

2. Install TablePlus GUI

TablePlus provides a native GUI Client for MySQL that helps you design, develop, and manage your MySQL in the easiest way.

You can download TablePlus from here. It’s free to use with no time limitation.

3. Start working with MySQL

After you created and started the MySQL Server, click on the arrow button next to the start button to create a connection to the local MySQL database using TablePlus.

From there, you are free to work with MySQL. You can interact with the database using the GUI, or write and run the SQL queries with query editor.

To open SQL query editor, click on the editor button at the top of the left toggle, or use shortcut key Cmd + E.

Some common queries in MySQL to remember:

• CREATE queries:

Create new database:

CREATE DATABASE db_name;

Create a table:

CREATE TABLE customers (
    id int (10),
    name varchar(50),
    city varchar(50),
    PRIMARY KEY (id)
);  

• ALTER queries:

ALTER TABLE customers
    ADD age varchar(50);

• INSERT queries:

INSERT INTO customers
    VALUES (101, 'Joe', 'Singapore');

• UPDATE queries:

UPDATE
    customers
SET
    name = 'Bob',
    city = 'London'
WHERE
    id = 10;

• SELECT queries:

SELECT
    *
FROM
    customers;

• DELETE queries:

DELETE FROM customers
WHERE id = 101;

• DROP queries:

DROP TABLE customers;

If you don’t feel like writing queries and want to interact with the GUI only, you can follow this getting started guide for TablePlus on Mac.