Install MySQL & Run Queries

You can run every example in this course in the browser, but sooner or later you will want a real MySQL server on your own computer. That is what you will use when you build a website, connect from PHP or Python, or practise for a job interview.

In this lesson you will install MySQL, meet the two main tools for talking to it, and load the practice database that the rest of the course uses.

Choose how to install

There are three common ways to get a database server on your laptop. Pick one.

If you are not sure, choose MySQL Community Server 8.4 LTS or 9.7 LTS. LTS means long-term support: the version gets security fixes for years, so tutorials and companies stay on it.

Install MySQL Community Server

The official downloads are at dev.mysql.com/downloads. On Windows, the steps look like this:

1. Open the MySQL Community Server download page and pick the LTS version you want.
2. Download the Windows MSI installer. You do not need an Oracle account; look for the "No thanks, just start my download" link.
3. Run the installer and accept the typical setup.
4. A configuration wizard opens. Keep the default port 3306, which is the standard MySQL port.
5. Choose a strong password for the root account. Root is the all-powerful administrator account, so write the password somewhere safe.
6. Leave "run MySQL as a Windows service" switched on, so the server starts with your computer.

On macOS, download the DMG package from the same page and follow the same idea: install, set a root password, start the server. On Linux, add the official MySQL APT or Yum repository from dev.mysql.com, because the MySQL package that comes with your distribution may be an older version.

If you use Docker, one command starts a MySQL 8.4 server:

docker run --name shop-mysql -e MYSQL_ROOT_PASSWORD=Choose-A-Strong-One -p 3306:3306 -d mysql:8.4

MySQL Workbench

MySQL Workbench is Oracle's free desktop program for working with MySQL. It gives you a window with a query editor, a list of databases on the left and a results grid at the bottom. Download it from the Workbench page on dev.mysql.com.

When you open Workbench, it usually shows a "Local instance" connection on the home screen. Click it, type your root password, and the SQL editor opens. Type a query, then click the lightning-bolt button to run it. Pressing Ctrl+Enter runs only the statement your cursor is in, which is handy when the editor holds many queries.

Workbench is the friendliest way to start, because you can see tables and results in a grid. You will still want to know the command-line client, because servers in the cloud usually have no graphical screen.

The mysql command-line client

Every MySQL installation includes mysql, a program you run in a terminal (Command Prompt, PowerShell or a macOS/Linux terminal). It connects to the server, sends your SQL, and prints the results as a text table.

mysql -u root -p

The -u root part gives the user name, and -p tells the client to ask for the password. Type it (nothing appears on screen while you type) and press Enter. You now see the mysql> prompt, where you can type SQL. Type exit to leave.

On Windows the program lives in a folder such as C:\Program Files\MySQL\MySQL Server 8.4\bin. If the terminal says mysql is not recognised, add that folder to your PATH, or type the full path to mysql.exe.

A quick test is to ask the server for its version:

SELECT VERSION();

You will see a number such as 8.4.x or 9.7.x. The client always waits for a semicolon before it runs anything. If you press Enter and only see ->, the client thinks your statement is not finished: type ; and press Enter again.

XAMPP ships MariaDB

Many students in India already have XAMPP installed for PHP. Its control panel has a "MySQL" button, but the server it starts is actually MariaDB. MariaDB began as a copy of MySQL, so the basics are the same: the same mysql client commands, the same port 3306, and the same SELECT, JOIN and GROUP BY. Almost every query in the first half of this course runs unchanged on it.

The two have drifted apart in newer features, default settings and some functions, so a few later lessons (such as JSON in MySQL and What's New in MySQL 8.4 & 9.7 LTS) will not match MariaDB exactly. If you plan to work with MySQL professionally, install the real MySQL as well.

With XAMPP on Windows, start MySQL from the control panel and connect like this:

C:\xampp\mysql\bin\mysql.exe -u root

There is no -p, because XAMPP's root account has no password by default. That is acceptable on your own laptop, but never on a server that other people can reach. XAMPP also includes phpMyAdmin at http://localhost/phpmyadmin, a web page where you can run SQL in a box.

Create the shop database and load the practice data

A MySQL server can hold many databases, one per project. Create one called shop and make it the current database:

CREATE DATABASE shop;
USE shop;

Query OK, 1 row affected (0.01 sec)

Database changed

USE shop; tells the client which database your following statements apply to. Next, download the practice file shop.sql. It creates five tables and fills them with sample rows. Load it with the SOURCE command inside the client (use forward slashes in the path, even on Windows):

SOURCE C:/Users/Priya/Downloads/shop.sql;

Or load it straight from the terminal, in the folder where you saved the file, naming the database after the password option:

mysql -u root -p shop < shop.sql

In Workbench, double-click shop in the schema list so it becomes the default database, then choose File, Open SQL Script, pick shop.sql and click the lightning bolt. Finally, check that the tables arrived:

SHOW TABLES;

+----------------+
| Tables_in_shop |
+----------------+
| customers      |
| employees      |
| order_items    |
| orders         |
| products       |
+----------------+
5 rows in set (0.00 sec)

The time in brackets will be slightly different on your computer. That is normal.

Common mistakes

• ERROR 1045 (28000): Access denied for user 'root'@'localhost'. The password is wrong. Check Caps Lock and try again. On XAMPP, leave out -p.
• ERROR 1046 (3D000): No database selected. You loaded shop.sql or ran a query before USE shop;. The practice file does not choose a database for you.
• ERROR 1050 (42S01): Table 'customers' already exists. You loaded the file twice. Drop and recreate the database as shown in the tip above.
• Working as root forever. Root is fine for learning, but real applications should use a separate user with limited rights. You will learn this in Users, Privileges & Backups.

Key points

• MySQL Community Server 8.4 LTS or 9.7 LTS is the best choice for this course.
• MySQL Workbench is a graphical tool; mysql is the command-line client that comes with every install.
• The client runs a statement only when it sees a semicolon.
• XAMPP's "MySQL" is really MariaDB: very close, but not identical in newer features.
• Run CREATE DATABASE shop; and USE shop;, then load shop.sql to get the practice tables.

USE shop;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM employees;