💻 Lesson 3: MySQL CLI & phpMyAdmin

Two Tools, One Database

There are many ways to interact with a MySQL server, but the two most common for learners and developers are:

Both tools send the exact same SQL to the exact same MySQL server — they're just different interfaces. Think of it like driving a car with a manual transmission vs. an automatic: the engine underneath is identical, but the controls feel different.

The MySQL Command-Line Client

Logging In

Open your terminal (Ubuntu/WSL, macOS Terminal, or Linux terminal) and connect to MySQL:

mysql -u root -p
                

Let's break down those flags:

After entering your password, you'll see the MySQL prompt:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.xx-0ubuntu0.24.04.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
                    

That mysql> prompt means you're inside the MySQL client. Everything you type here is sent to the MySQL server.

Logging Out

Any of these commands will disconnect you and return to your regular terminal:

EXIT;
QUIT;
\q
                

mysql -u root -p my_database
                    

Navigating with CLI Commands

MySQL has two kinds of commands you'll type at the prompt:

1. SQL statements — standard SQL that works on any database (SELECT, INSERT, CREATE, etc.)
2. MySQL-specific commands — shortcuts that only work in the MySQL CLI (SHOW, DESCRIBE, USE, etc.)

Let's walk through the essential navigation commands. Try each one as you read!

SHOW DATABASES — List All Databases

SHOW DATABASES;
                

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

These are MySQL's built-in system databases. You'll add your own databases starting in Lesson 4.

USE — Select a Database

Before you can look at tables, you need to tell MySQL which database to work with:

USE mysql;
                

Database changed
                    

Now all commands will operate on the mysql system database (until you USE a different one).

SHOW TABLES — List Tables in the Current Database

SHOW TABLES;
                

+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| ...                                                  |
| user                                                 |
+------------------------------------------------------+
                    

The mysql system database contains tables that store user accounts, permissions, and server settings. You'll rarely interact with these directly, but it's good to know they exist.

DESCRIBE — Show a Table's Structure

DESCRIBE (or its shorthand DESC) shows the columns, data types, and constraints for a table:

DESCRIBE user;
                

+-----------+-----------------------------------+------+-----+---------+-------+
| Field     | Type                              | Null | Key | Default | Extra |
+-----------+-----------------------------------+------+-----+---------+-------+
| Host      | char(255)                         | NO   | PRI |         |       |
| User      | char(32)                          | NO   | PRI |         |       |
| ...       | ...                               | ...  | ... | ...     | ...   |
+-----------+-----------------------------------+------+-----+---------+-------+
                    

This is incredibly useful — you'll use DESCRIBE constantly to remind yourself what columns a table has.

SELECT — Query Data

And of course, you can run any SQL query. Here's a quick peek at the MySQL user accounts:

SELECT User, Host FROM user;
                

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| phpmyadmin       | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
                    

Navigation Command Reference

CLI Tips and Tricks

Every Statement Ends with a Semicolon

SQL statements in the CLI must end with a semicolon (;). If you press Enter without one, MySQL thinks you're still typing and shows a continuation prompt:

mysql> SELECT
    -> VERSION()
    -> ;
                

The -> prompt means "I'm waiting for more input." Just type the semicolon and press Enter to execute.

Multi-Line Statements

You can (and should) write longer SQL across multiple lines for readability. MySQL doesn't execute until it sees the semicolon:

SELECT
    User,
    Host
FROM
    user
WHERE
    Host = 'localhost';
                

This is much easier to read than cramming everything on one line.

Command History

Press the Up Arrow key to cycle through previous commands — just like in your regular terminal. This saves a lot of retyping.

Clearing the Screen

\! clear
                

Or on Windows/WSL, you can use SYSTEM clear — the \! prefix runs a shell command from inside MySQL.

Getting Help

-- General help
HELP;

-- Help on a specific topic
HELP SELECT;
HELP CREATE TABLE;
                

Useful Shorthand Commands

Vertical Output with \G

When a table has many columns, the default horizontal output wraps and becomes hard to read. Use \G instead of ; to display each row vertically:

SELECT * FROM user WHERE User = 'root'\G
                

*************************** 1. row ***************************
                 Host: localhost
                 User: root
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          ...
1 row in set (0.00 sec)
                    

Each column is listed on its own line with its value — much easier to read for wide tables.

phpMyAdmin: A Guided Tour

Open your browser and go to http://localhost/phpmyadmin. Log in with your root credentials. Here's what you'll see:

The Interface Layout

• Left Sidebar — shows all your databases as an expandable tree. Click a database name to select it, then expand it to see its tables.
• Tab Bar — appears when you've selected a database or table. Key tabs:
  • Structure — view and modify columns, indexes, and keys
  • SQL — type and execute raw SQL queries
  • Search — build queries visually with dropdowns and filters
  • Insert — add new rows via a form
  • Export / Import — dump or load data (CSV, SQL, etc.)
• Main Panel — displays results, forms, table data, and structure depending on which tab you're on.

Browsing a Database

1. In the left sidebar, click on mysql (the system database)
2. The main panel shows a list of all tables in that database
3. Click on the user table
4. You'll see the table's data displayed in a grid — this is the same data you'd see with SELECT * FROM user; in the CLI

Viewing Table Structure

1. With the user table selected, click the Structure tab
2. You'll see every column listed with its type, null setting, key status, default value, and extras
3. This is the visual equivalent of DESCRIBE user;

Running a Query

1. Click on mysql in the sidebar (to select the database)
2. Click the SQL tab
3. Type a query in the text box:

   
   SELECT User, Host FROM user;
                           

4. Click Go (or press Ctrl+Enter)
5. The results appear below in a formatted table

Running Queries in Both Tools

Let's run the same commands side by side to reinforce that both tools do the same thing.

Example 1: Check the MySQL Version

Both return the same version number.

Example 2: List Databases

Example 3: Select a Database and View Tables

Example 4: Describe a Table

CLI vs. phpMyAdmin: When to Use Which

Saving and Running SQL Files

As your queries get longer, typing them into the CLI every time becomes tedious. A better approach is to save your SQL in .sql files and run them from the command line.

Creating a SQL File

Open your text editor (VS Code recommended) and create a file called test.sql:

-- test.sql
-- A simple test script

SELECT VERSION();
SELECT NOW();
SHOW DATABASES;
                

Save it somewhere accessible (e.g., your home directory or a project folder).

Running a SQL File from the CLI

There are two ways:

Method 1: SOURCE command (from inside MySQL)

-- Log in first, then:
SOURCE /path/to/test.sql;
                

Method 2: Redirect input (from the terminal, before logging in)

mysql -u root -p < /path/to/test.sql
                

Method 2 is especially useful for automation — you can run SQL scripts from bash scripts, cron jobs, or deployment pipelines.

Running a SQL File in phpMyAdmin

1. Click the Import tab
2. Click Choose File and select your .sql file
3. Click Go

Exercises

Summary

🚀 What's Next?

You're comfortable navigating MySQL with both tools. In the next lesson, you'll start building your own databases and tables — creating the structures that will hold your data. This is where it gets really fun!