MySQL Command Reference

This reference provides an overview of essential MySQL commands for database administration, table management, data manipulation, and querying. It serves as a quick guide for both beginners and experienced users working with MySQL.

Connecting to MySQL

Use the mysql client to connect to the MySQL server.

# Connect to MySQL with a username and prompt for a password
mysql -u username -p

# Connect to a specific database
mysql -u username -p database_name

# Connect to MySQL on a specific host and port
mysql -h hostname -P port_number -u username -p

Database Administration Commands

Create a Database

CREATE DATABASE database_name;

Drop a Database

DROP DATABASE database_name;

Show Databases

SHOW DATABASES;

Select a Database

USE database_name;

Table Management Commands

Create a Table

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
);

Show Tables

SHOW TABLES;

Describe a Table

DESCRIBE table_name;

# Or
EXPLAIN table_name;

# Or
SHOW COLUMNS FROM table_name;

Alter a Table

Add a column:

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints];

Modify a column:

ALTER TABLE table_name MODIFY COLUMN column_name new_datatype [new_constraints];

Rename a column:

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype [constraints];

Drop a column:

ALTER TABLE table_name DROP COLUMN column_name;

Rename a Table

RENAME TABLE old_table_name TO new_table_name;

Drop a Table

DROP TABLE table_name;

Data Manipulation Commands

Insert Data

Insert a single row:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Insert multiple rows:

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1a, value2a, ...),
    (value1b, value2b, ...),
    ...;

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Delete Data

DELETE FROM table_name WHERE condition;

Note: Omitting the WHERE clause will delete all rows in the table.

Truncate a Table

Remove all data from a table (cannot be rolled back):

TRUNCATE TABLE table_name;

Data Querying Commands

Select Data

Basic select:

SELECT column1, column2, ... FROM table_name;

Select all columns:

SELECT * FROM table_name;

Where Clause

SELECT * FROM table_name WHERE condition;

Order By Clause

SELECT * FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Group By Clause

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING condition;

Limit Clause

SELECT * FROM table_name LIMIT offset, count;

Join Operations

Inner Join:

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Left Join:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Right Join:

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Subqueries

SELECT *
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Transactions

Manage transactions to ensure data integrity.

Start a Transaction

START TRANSACTION;

# Or
BEGIN;

Commit a Transaction

COMMIT;

Rollback a Transaction

ROLLBACK;

User Management Commands

Create a User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Grant Privileges

GRANT privileges ON database.table TO 'username'@'host';

# Examples
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;
GRANT SELECT, INSERT ON database.* TO 'username'@'host';

Show Grants

SHOW GRANTS FOR 'username'@'host';

Revoke Privileges

REVOKE privileges ON database.table FROM 'username'@'host';

Drop a User

DROP USER 'username'@'host';

Import and Export Data

Export Database to SQL File

# From the command line
mysqldump -u username -p database_name > backup.sql

Import SQL File into Database

# From the command line
mysql -u username -p database_name < backup.sql

Export Table to CSV

SELECT column1, column2, ...
FROM table_name
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Import CSV into Table

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, ...);

Index Management

Create an Index

CREATE INDEX index_name ON table_name (column1, column2, ...);

Drop an Index

DROP INDEX index_name ON table_name;

Show Indexes

SHOW INDEX FROM table_name;

Stored Procedures and Functions

Create a Stored Procedure

DELIMITER //

CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2 datatype)
BEGIN
    -- Procedure body
END //

DELIMITER ;

Call a Stored Procedure

CALL procedure_name(param1_value, @param2_value);

# Retrieve OUT parameter
SELECT @param2_value;

Create a Function

DELIMITER //

CREATE FUNCTION function_name (param1 datatype, param2 datatype)
RETURNS return_datatype
DETERMINISTIC
BEGIN
    -- Function body
    RETURN some_value;
END //

DELIMITER ;

Use a Function

SELECT function_name(param1_value, param2_value);

Triggers

Create a Trigger

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
BEGIN
    -- Trigger body
END;

Drop a Trigger

DROP TRIGGER trigger_name;

Views

Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Drop a View

DROP VIEW view_name;

System Variables and Configuration

Show All Variables

SHOW VARIABLES;

Show Specific Variable

SHOW VARIABLES LIKE 'variable_name';

Set Session Variable

SET SESSION variable_name = value;

Set Global Variable

SET GLOBAL variable_name = value;

Information Schema Queries

List All Tables in a Database

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

List All Columns in a Table

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'database_name'
AND table_name = 'table_name';

Useful Commands

Show Current User

SELECT CURRENT_USER();

Show Current Database

SELECT DATABASE();

Show Server Version

SELECT VERSION();

Exit the MySQL Client

EXIT;

# Or
QUIT;

MySQL Client Tips

Commands end with a semicolon (;). Don't forget to include it.
Use \G instead of ; to display query results vertically.
Use SHOW WARNINGS; after a command to display any warnings.
Use arrow keys for command history and editing.
Use \c to cancel the current command input.
Use source /path/to/file.sql; to execute SQL commands from a file.

Common Data Types

Data Type	Description
`INT`	Integer numbers.
`DECIMAL(m,d)`	Fixed-point numbers with `m` digits and `d` decimals.
`VARCHAR(n)`	Variable-length character string up to `n` characters.
`CHAR(n)`	Fixed-length character string of `n` characters.
`TEXT`	Large text data.
`DATE`	Date value in `YYYY-MM-DD` format.
`DATETIME`	Date and time value in `YYYY-MM-DD HH:MM:SS` format.
`TIMESTAMP`	Timestamp value, typically used for tracking changes.
`BLOB`	Binary large objects, used to store binary data.
`ENUM`	Enumeration, a string object with a value chosen from a list of permitted values.

Operators and Functions

Comparison Operators

= Equal to
<> Not equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
BETWEEN ... AND ...
IN (value1, value2, ...)
LIKE 'pattern%'
IS NULL, IS NOT NULL

Logical Operators

AND
OR
NOT

Aggregate Functions

COUNT(column)
SUM(column)
AVG(column)
MAX(column)
MIN(column)

Date and Time Functions

NOW()
CURDATE()
CURTIME()
DATE_ADD(date, INTERVAL value unit)
DATEDIFF(date1, date2)
DATE_FORMAT(date, format)

String Functions

CONCAT(str1, str2, ...)
SUBSTRING(str, pos, len)
REPLACE(str, from_str, to_str)
UPPER(str)
LOWER(str)
TRIM([remstr FROM] str)

Performance Tips

Use indexes on columns that are frequently searched or joined.
Avoid using SELECT *; specify only the columns you need.
Use appropriate data types and keep them as small as possible.
Normalize your database to reduce redundancy.
Use EXPLAIN to analyze query performance.
Avoid subqueries when possible; use joins instead.

Return to Home