MYSQL Cheatsheet a quick and easy way to memorize MYSQL code

These are the most commonly used MySQL commands and statements that will help you work with MySQL more effectively

Admildo Manuel
3 min readSep 5, 2020
The Homocoder lol

While I was coding a SaaS , I got myself thinking if there was a quick way to memorize Mysql commands just like I do with games like GT5 , well here is a snippet of what I found at Mysqltutirial.org a pretty cool website by the way.

MySQL command-line client Commands

Connect to database using a specified username and password:

mysql -u [username] -p [database];

Exit mysql command-line client:

exit;

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

mysql> system clear;

There is no command on Windows OS for clearing 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 DATABASE;

Working with tables

Show all tables in a current database.

SHOW TABLES;

Create a new table

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

Add a new column into a table:

ALTER TABLE table 
ADD [COLUMN] column_name;

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;

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

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

Add 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;

Querying data from tables

Query all data from a table:

SELECT * FROM table_name;

Query data from one or more column of a table:

SELECT 
column1, column2, ...
FROM
table_name;

Remove duplicate rows from the result of a query:

SELECT 
DISTINCT (column)
FROM
table_name;

Query data with a filter using a WHERE clause:

SELECT select_list
FROM table_name
WHERE condition;

Query data from multiple tables using inner join:

SELECT select_list
FROM table1
INNER JOIN table2 ON condition;

Query data from multiple tables using left join:

SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;

Query data from multiple tables using right join:

SELECT select_list 
FROM table1
RIGHT JOIN table2 ON condition;

Make a Cartesian product of rows:

SELECT select_list
FROM table1
CROSS JOIN table2;

Counting rows in a table.

SELECT COUNT(*)
FROM table_name;

Sorting a result set:

SELECT 
select_list
FROM
table_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];

Group rows using the GROUP BY clause.

SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;

Filter group using the HAVING clause:

SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;

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 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 RLIKE operator.

SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';

--

--

Admildo Manuel

I’m a nerd/christian with a strong drive for engineering,computer science , data science , math and entrepreneurship .