MySQL Command and Syntax Cheat Sheet: From Basic to Advanced Level Guide
MySQL is a powerful Relational Database Management System that is completely open-source. It utilizes Structured Query Language (SQL) to efficiently manage databases. With just a handful of SQL statements, users can interact with MySQL using a simple programming language. Additionally, MySQL is incredibly versatile and can run on multiple platforms, including Windows, Linux, and Mac. Its flexibility makes it an ideal choice for businesses of all sizes, from small-scale startups to large corporations.
This tutorial will give you a helpful MySQL cheat sheet that covers both basic and advanced commands. It will provide you with all the necessary syntaxes to interact effectively with MySQL RDBMS.
SELECT Syntax
SELECT * FROM table_name1;
SELECT * FROM table_name1, table_name2;
SELECT field1, field2 FROM table_name1, table_name2;
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...
TRUNCATE table
In MySQL, the TRUNCATE TABLE statement is used to delete all rows from a table, while still retaining the table structure.
Note: Once you execute this statement, all the rows in my_table will be deleted, and any auto-increment values for the table will be reset to their starting value (usually 1). and the data can not be rolled back.
TRUNCATE TABLE table_name;
INSERT Record
In MySQL, the INSERT INTO statement is used to add new rows to a table. Here's the basic syntax
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value, ...);
- table_name: The name of the table that you want to insert data into.
- column1, column2, column3, etc.: The names of the columns that you want to insert data into.
- value1, value2, value3, etc.: The values that you want to insert into the corresponding columns.
/*Insert a single record*/
INSERT INTO my_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3');
/*Insert multiple records in a single query*/
INSERT INTO my_table (column1, column2, column3) VALUES
('value1', 'value2', 'value3'),
('value4', 'value5', 'value6'),
('value7', 'value8', 'value9');
SELECT JOIN
There are four types of joins in MySQL:
1. INNER JOIN:
Returns only the matching rows from both tables. It's the most common type of join used in SQL. The basic syntax is:
/*INNER JOIN on two tables table1 and table2*/
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
2. LEFT JOIN:
Returns all the rows from the left table and the matching rows from the right table. If there are no matching rows from the right table, the result will contain NULL values. The basic syntax is:
/*LEFT JOIN on two tables table1 and table2*/
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
3. RIGHT JOIN:
Returns all the rows from the right table and the matching rows from the left table. If there are no matching rows from the left table, the result will contain NULL values. The basic syntax is:
/*RIGHT JOIN on two tables table1 and table2*/
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
4. FULL OUTER JOIN (or FULL JOIN):
FULL OUTER JOIN, also known as FULL JOIN, is a type of join that returns all rows from both tables, including the non-matching rows. However, MySQL doesn't support FULL OUTER JOIN directly. You can use a combination of LEFT JOIN and RIGHT JOIN to simulate a FULL OUTER JOIN. Here's an example:
Suppose we have two tables: "orders" and "customers". The "orders" table has a "customer_id" column that refers to the "id" column in the "customers" table.
/*FULL OUTER JOIN orders table and customers table*/
SELECT *
FROM orders
LEFT JOIN customers
ON orders.cust_id = customers.id
UNION
SELECT *
FROM orders
RIGHT JOIN customers
ON orders.cust_id = customers.id
WHERE orders.cust_id IS NULL;
MySQL comments
There are two types of comments in MySQL:
- Single-line comments: You can use the "--" symbol to add a single-line comment to your query. Everything after the "--" symbol will be ignored by the server. For example:
- Multi-line comments: You can use the "/* /" symbols to add a multi-line comment to your query. Everything between the "/" and "*/" symbols will be ignored by the server. For example:
SELECT *
FROM orders
WHERE amount > 1000 -- This is a single-line comment
/* This is a multi-line comment
that spans multiple lines */
SELECT *
FROM orders
WHERE amount > 1000
Display Table Schema (all column names) for a table
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';
Display all Table Schema (all column names) for a database
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
Display All Database name
SHOW DATABASES;
Create Database
To create a database in MySQL, you can use the following SQL command:
Simply replace the database_name with the database name that you want to create and run it
/*Basic Syntax*/
CREATE DATABASE database_name;
/*Create a database with CHARACTER SET COLLATE and ENGINE property */
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=InnoDB;
Check all tables in a database that do not have the AUTO_INCREMENT attribute on their primary key column
Here's an example SQL query that you can execute to retrieve the table names that do not have an AUTO_INCREMENT attribute:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
AND auto_increment IS NULL;
DROP DATABASE
- IF EXISTS (optional): This keyword is used to avoid errors if the specified database does not exist. If this keyword is used, and the specified database does not exist, no error will be returned.
- database_name: This is the name of the database that you want to delete.
DROP DATABASE mydatabase;
/* or use the bellow command it will check if the database exist*/
DROP DATABASE IF EXISTS database_name;
ALTER DATABASE
ALTER DATABASE database_name [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name
The ALTER DATABASE statement can be used to change the character set and collation of a database. Here's a brief description of each clause:
- database_name: The name of the database to be modified.
- DEFAULT CHARACTER SET [=] charset_name: Specifies the default character set for the database. The = sign is optional.
- DEFAULT COLLATE [=] collation_name: Specifies the default collation for the database. The = sign is optional.
To modify a database's character set and collation, you can use the following syntax:
ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Create Table
/*Syntax to create table*/
CREATE TABLE table (field1 type1, field2 type2);
CREATE TABLE table (field1 type1, field2 type2, INDEX (field));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1,field2));
CREATE TABLE table1 (fk_field1 type1, field2 type2, ..., FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)) [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ..., FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
/*Example code for creating table name my_table */
CREATE TABLE my_table (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100)
);
CREATE TEMPORARY table
To create a temporary table in MySQL, you can use the CREATE TEMPORARY TABLE statement followed by the name of the table you want to create and a set of parentheses that define the columns of the table. Here's an example:
CREATE TEMPORARY TABLE my_temp_table (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100)
);
ALTER table
To alter an existing table in MySQL, you can use the ALTER TABLE statement followed by the name of the table you want to modify, and then specify the modification you want to make using one of several possible clauses
Adding a new column:
ALTER TABLE my_table ADD COLUMN new_column VARCHAR(50);
Modifying an existing column:
ALTER TABLE my_table MODIFY COLUMN existing_column VARCHAR(100);
Dropping a column:
ALTER TABLE my_table DROP COLUMN column_to_drop;
Basic Syntaxes are the following:
ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
DROP table
The full syntax for dropping a table:
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ... [RESTRICT | CASCADE]
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
DROP TABLE table_name1, table_name2, ...
Change Columns Order in a Table
Basic Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name column_definition [AFTER|BEFORE another_column_name];
- table_name: The name of the table that contains the column you want to modify.
- column_name: The name of the column you want to modify.
- column_definition: The new definition of the column, including any changes to its data type or other attributes.
- AFTER or BEFORE: Optional keywords that specify the position of the modified column relative to another column. You can use AFTER to place the column after another column, or BEFORE to place it before another column. If you omit these keywords, the column will be added to the end of the table.
Some Examples are Below:
ALTER TABLE my_table MODIFY COLUMN column1 varchar(50) AFTER column2;
ALTER TABLE my_table MODIFY field1 type1 FIRST
ALTER TABLE my_table MODIFY field1 type1 AFTER another_field
ALTER TABLE my_table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE my_table CHANGE old_name_field1 new_name_field1 type1 AFTER
another_field