MySQL Getting Started

In this lesson you install MySQL, connect to it and create your first database.

Install MySQL

System	Easiest way
Windows	Download the MySQL Installer from the official MySQL website and choose "Server" and "Workbench". XAMPP also includes MySQL (as MariaDB).
macOS	Download the DMG installer, or run `brew install mysql` with Homebrew
Ubuntu Linux	`sudo apt install mysql-server`

During setup you choose a password for the root user, the main admin account. Write it down somewhere safe.

If you cannot install anything, many free online SQL playgrounds let you pick MySQL as the engine. They are fine for practising the queries in this tutorial.

Connect with the Command Line

Open a terminal (or Command Prompt on Windows) and type:

Example

bash

mysql -u root -p

-u root means "log in as the user root"
-p means "ask me for the password"

After you type the password, the prompt changes to mysql>. Now you can type SQL. Check which version you have:

Example

sql

SELECT VERSION();

Output

+-----------+
| VERSION() |
+-----------+
| 8.4.6     |
+-----------+
1 row in set (0.00 sec)

Your number may be different. Type exit to leave the client.

See the Databases

Example

sql

SHOW DATABASES;

Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

These four are MySQL's own system databases. Do not change or delete them.

Create a Database

Example

sql

CREATE DATABASE shop;

Output

Query OK, 1 row affected (0.01 sec)

To avoid an error when the database already exists, add IF NOT EXISTS:

Example

sql

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

utf8mb4 lets the database store any character: English, Urdu, Arabic, even emoji. It is the default in MySQL 8 and the right choice for new projects.

Select a Database

Before you create tables, tell MySQL which database to use:

Example

sql

USE shop;

Output

Database changed

You can check the current database at any time with SELECT DATABASE();.

Delete a Database

Example

sql

DROP DATABASE IF EXISTS test_db;

This removes the database and every table inside it, with no undo. Be very careful with it on a real server.

Useful Client Commands

Command	What it does
`SHOW DATABASES;`	Lists all databases
`USE name;`	Switches to a database
`SHOW TABLES;`	Lists the tables in the current database
`DESCRIBE table;`	Shows the columns of a table
`SOURCE file.sql;`	Runs SQL statements saved in a file
`exit`	Leaves the client

Graphical Tools

If you prefer clicking to typing, use:

MySQL Workbench: the official free desktop tool
phpMyAdmin: a web tool included with XAMPP and most hosting control panels
DBeaver: a free tool that works with many databases

All of them send the same SQL to the server.

Exercise

Which statement makes shop the current database, so new tables are created inside it?

OPEN shop;SELECT shop;USE shop;CREATE DATABASE shop;