MySQL Command Line

MySQL Command Line is a command-line interface for interacting with a MySQL Server.

Instead of clicking graphical buttons, you type commands directly into the terminal.

mysql>

On Windows, you can open Command Prompt or PowerShell and then use the MySQL client if it is installed and available in the system PATH.

On Linux and macOS, MySQL commands can normally be executed from the Terminal.

The MySQL command-line client is commonly started using the mysql command.

mysql

If the client is correctly installed and configured, it can open the MySQL command-line environment.

You can specify the MySQL username while connecting.

mysql -u root

Here, -u specifies the username and root is the username.

The -p option tells the MySQL client to request a password.

mysql -u root -p

After running this command, MySQL will prompt you for the password.

After successful authentication, you may see the MySQL welcome message and the MySQL prompt.

mysql>

You can now execute SQL statements and MySQL commands.

The SELECT VERSION() statement can be used to display the MySQL server version.

SELECT VERSION();

Example output:

8.0.x

The SHOW DATABASES command displays databases available to the connected user.

SHOW DATABASES;

You can create a database from the command line using CREATE DATABASE.

CREATE DATABASE school;

This creates a database named school.

The USE command selects a database for subsequent operations.

USE school;

After selecting the database, you can create and work with its tables.

After selecting a database, create a table using CREATE TABLE.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

The SHOW TABLES command displays tables in the currently selected database.

SHOW TABLES;

The DESCRIBE command displays information about a table's columns.

DESCRIBE students;

You can also use:

DESC students;

Use the INSERT INTO statement to add records to a table.

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python');

The SELECT statement retrieves data from a table.

SELECT * FROM students;

The asterisk * means all columns.

You can select only the columns you need.

SELECT name, course
FROM students;

This returns only the name and course columns.

The WHERE clause filters records.

SELECT *
FROM students
WHERE course = 'Python';

Only students whose course is Python are returned.

The UPDATE statement changes existing records.

UPDATE students
SET course = 'Java'
WHERE id = 1;

The WHERE clause is important because it identifies the record to update.

The DELETE statement removes records from a table.

DELETE FROM students
WHERE id = 1;

Always use a suitable WHERE condition when you want to delete specific records.

In the MySQL command-line client, SQL statements are normally terminated with a semicolon.

SELECT * FROM students;

The semicolon tells the client that the statement is complete.

SQL statements can be written across multiple lines.

SELECT id, name, course
FROM students
WHERE course = 'Python';

MySQL waits for the terminating semicolon before executing the complete statement.

The MySQL command-line client displays prompts that indicate its current state.

mysql>

When entering a multi-line statement, the prompt can change to indicate that MySQL is waiting for more input.

If you start entering a statement but do not want to execute it, you can cancel the current input using:

\c

This clears the current statement and returns to the normal MySQL prompt.

The MySQL command-line client provides help commands.

help

You can also use:

help SELECT

Help can provide information about available MySQL commands and SQL statements.

You can leave the MySQL command-line client using commands such as:

exit

Another commonly used command is:

quit

SQL statements can also be stored in a file and executed through the MySQL client.

For example, a SQL file may contain:

CREATE DATABASE school;
USE school;
SHOW TABLES;

This is useful for scripts containing many SQL statements.

Common problems include:

• MySQL server is not running
• Incorrect username
• Incorrect password
• Incorrect hostname
• Incorrect port
• Database does not exist
• SQL syntax error
• Insufficient privileges

1. Open Terminal / Command Prompt
2. Start MySQL client
3. Connect to MySQL Server
4. Show databases
5. Select a database
6. Create tables
7. Insert data
8. Select data
9. Update or delete data
10. Exit MySQL

The following example demonstrates a basic MySQL command-line workflow:

mysql -u root -p

SHOW DATABASES;

CREATE DATABASE school;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python'),
(2, 'Amit', 'Java');

SELECT * FROM students;

This sequence connects to MySQL, creates a database, selects it, creates a table, inserts records, and displays the data.