MySQL Cheat Sheet: Essential Commands & Syntax

Posted on Aug 6, 2024 in Computers

MySQL Cheat Sheet

MySQL Command-Line Client Commands

Connect to MySQL server using the mysql command-line client with a username and password (MySQL will prompt for a password):

mysql -u [username] -p

Connect to MySQL Server with a specified database using a username and password:

mysql -u [username] -p [database]

Exit mysql command-line client:

exit

Export data using mysqldump tool

mysqldump -u [username] -p [database] > data_backup.sql

To clear the MySQL screen console on Linux, use the following command:

mysql> system clear;

Currently, there is no command available on Windows for clearing the MySQL screen console window.

Working with Databases

Create a database with a specified name if it does not exist in the database server:

CREATE DATABASE [IF NOT EXISTS] database_name;

Use a database or change the current database to another database that you are working with:

USE database_name;

Drop a database with a specified name permanently. All physical files associated with the database will be deleted.

DROP DATABASE [IF EXISTS] database_name;

Show all available databases in the current MySQL database server:

SHOW DATABASES;

Working with Tables

Show all tables in the current database:

SHOW TABLES;

Create a new table:

CREATE TABLE [IF NOT EXISTS] table_name(
  column_list
);

Add a new column to a table:

ALTER TABLE table 
ADD [COLUMN] column_name;

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;

Add an index with a specific name to a table on a column:

ALTER TABLE table 
ADD INDEX [name](column, ...);

Add a primary key into a table:

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name,...);

Remove the primary key of a table:

ALTER TABLE table_name
DROP PRIMARY KEY;

Drop a table:

DROP TABLE [IF EXISTS] table_name;

Show the columns of a table:

DESCRIBE table_name;

Show the information of a column in a table:

DESCRIBE table_name column_name;

Working with Indexes

Creating an index with the specified name on a table:

CREATE INDEX index_name
ON table_name (column,...);

Drop an index:

DROP INDEX index_name;

Create a unique index:

CREATE UNIQUE INDEX index_name 
ON table_name (column,...);

Working with Views

Create a new view:

CREATE VIEW [IF NOT EXISTS] view_name 
AS 
  select_statement;

Create a new view using the WITH CHECK OPTION:

CREATE VIEW [IF NOT EXISTS] view_name 
AS 
select_statement
WITH CHECK OPTION;

Create or replace a view:

CREATE OR REPLACE view_name 
AS 
select_statement;

Drop a view:

DROP VIEW [IF EXISTS] view_name;

Drop multiple views:

DROP VIEW [IF EXISTS] view1, view2, ...;

Rename a view:

RENAME TABLE view_name
TO new_view_name;

Show views from a database:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';

Working with Triggers

Create a new trigger:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

Drop a trigger:

DROP TRIGGER [IF EXISTS] trigger_name;

Show triggers in a database:

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

Working with Stored Procedures

Create a stored procedure:

DELIMITER $$

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   body;
END $$

DELIMITER ;

Drop a stored procedure:

DROP PROCEDURE [IF EXISTS] procedure_name;

Show stored procedures:

SHOW PROCEDURE STATUS 
[LIKE 'pattern' | WHERE search_condition];

Working with Stored Functions

Create a new stored function:

DELIMITER $$
 
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$
 
DELIMITER ;

Drop a stored function:

DROP FUNCTION [IF EXISTS] function_name;

Show stored functions:

SHOW FUNCTION STATUS 
[LIKE 'pattern' | WHERE search_condition];

<br>

Modifying Data in Tables

Insert a new row into a table:

INSERT INTO table_name(column_list)
VALUES(value_list);

Insert multiple rows into a table:

INSERT INTO table_name(column_list)
VALUES(value_list1),
      (value_list2),
      (value_list3),
      ...;

Update all rows in a table:

UPDATE table_name
SET column1 = value1,
    ...;

Update data for a set of rows specified by a condition in the WHERE clause.

UPDATE table_name
SET column_1 = value_1,
    ...
WHERE condition

Update with join

UPDATE 
    table1, 
    table2
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition;

Delete all rows in a table

DELETE FROM table_name;

Delete rows specified by a condition:

DELETE FROM table_name
WHERE condition;

Delete with join

DELETE table1, table2
FROM table1
INNER JOIN table2
    ON table1.column1 = table2.column2
WHERE condition;

Searching

Search for data using the LIKE operator:

SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';

Text search using a regular expression with the RLIKE operator.

SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';

MySQL Cheat Sheet: Essential Commands & Syntax