Today, with the expansion in data, every company needs a database for storing that data. One of the most commonly used and enterprise-adopted databases is MySQL , which was introduced as an open-source relational database management system in the market. This article details a comprehensive MySQL cheat sheet. The data is stored in the predefined tables in the database. You can perform operations on MySQL databases like adding, altering, modifying, deleting, and updating. To implement these actions, you need to run SQL queries that will communicate with the MySQL database.
MySQL Cheat Sheet
You can access the MySQL database using the command-line interface and run the commands for carrying out specific tasks. In this MySQL cheat sheet, we will be learning basic commands that will help you to work with the data stored within the MySQL database.
MySQL Command-line Client Commands
You can connect to the MySQL server with the help of the command-line client by providing the username and password.
mysql - [username] -p;
There are usually various databases present on a MySQL server, so you need to specify the name of the connecting database along with the username and password for accessing the database. You can use the following syntax to do so:
mysql - [username] -p [database];
If you want to exit the specified database, you can run the following command to do so. Exit; If you want to export the data, then you can use the mysqldump tool as shown below:
mysqldump -u [username] -p [database] >data_backup.sql;
If you want to clear the MySQL screen console window on the Linux system, then you can run the following command:
mysql> system clear;
If you are using Windows, then there is no specific command available for clearing the screen console window.
Working with Databases
1. For creating a database that does not already exist in the database server, you can use the following command:
CREATE DATABASE [IF NOT EXISTS] database_name;
2. If you are working within a database and want to change the database to another database, then you can use the following command:
USE database_name;
3. If you want to delete a specific database permanently along with all its associated files, then you can use the below-mentioned command to do so:
DROP DATABASE [IF EXISTS] database_name;
4. If you want to display the list of all the databases that are present within your MySQL server, then run the following command:
SHOW DATABASE;
Working with Tables
1. If you want to display all the tables that are present within a specific database, then you can run the following command to do so:
SHOW TABLES;
2. If you want to create a new table within a database, then use the below command for adding a table:
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
);
3. If you want to add a new column to the existing table, then you can use the following command to do so:
ALTER TABLE table
ADD [COLUMN] column_name;
4. If you want to drop a column of an existing table, then you can run the following command:
ALTER TABLE table1_name
DROP [COLUMN] column1_name;
5. If you want to create an index for an existing table on a specific column, then you can use the following syntax to do so:
ALTER TABLE table
ADD INDEX [name](column, ...);
6. If you want to add a primary key to a specific column of an existing table, then you can use the following command:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name,...);
7. For removing the primary key of an existing table, you can run the following command:
ALTER TABLE table_name
DROP PRIMARY KEY;
8. If you want to drop a table from the current database, then you can use the following command:
DROP TABLE [IF EXISTS] table_name;
9. To display all the columns of an existing table in a database, you can use the following command:
DESCRIBE table1_name;
10. In order to show the detailed information of a particular column within a table, then you can use the following command:
DESCRIBE table_name column_name;
Working with Indexes
Indexes are created to search for a specific object within a database, such as a table and a view. 1. If you want to create an index with a specific name for an existing table, then you can use the following command:
CREATE INDEX index_name
ON table_name (column,...);
2. If you want to drop an existing index, then you can run the following command:
DROP INDEX index1_name;
3. In order to create a unique index, you can use the below-mentioned command for achieving so:
CREATE UNIQUE INDEX index1_name
ON table1_name (column1,...);
Working with Views
1. For creating a view of existing tables, then you can use the following command:
CREATE VIEW [IF NOT EXISTS] view_name
AS
Select_statement;
2. If you want to create a new view along with the “WITH CHECK OPTION,” then you can use the following SQL syntax:
CREATE VIEW [IF NOT EXISTS] view1_name
AS _specify_select_statement
WITH CHECK OPTION;
3. For adding or replacing a specific view, then you can use the following command:
CREATE OR REPLACE view_name
AS
Select_statement;
4. For dropping an existing view then you can use the following command:
DROP VIEW [IF EXISTS] view_name;
5. For dropping multiple views, you can simply use the following command:
DROP VIEW [IF EXISTS] view1, view2, ...;
6. For renaming a view, use the following command:
RENAME TABLE view_name
TO new_view_name;
7. If you want to display all the views within the working database, then you can use the following command:
SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';
Working with Triggers
1. If you want to create a trigger in MySQL , then you can use the following command:
CREATE TRIGGER trigger1_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table1_name FOR EACH ROW
Trigger1_body;
2. For dropping a specific trigger, you can follow the below-mentioned command:
DROP TRIGGER [IF EXISTS] trigger_name;
3. If you want to display all the triggers present within a database, then you can use the following SQL syntax:
SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];
Working with Stored Procedures
1. If you want to create a stored procedure , then you need to follow the below-mentioned SQL syntax:
DELIMITER $$
CREATE PROCEDURE procedure1_name(parameter1_list)
BEGIN
create_body_code;
END $$
DELIMITER ;
2. For dropping a specific stored procedure, you need to follow the below-mentioned command:
DROP PROCEDURE [IF EXISTS] procedure_name;
3. In order to display all the stored procedures, you can use the following command:
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE search_condition];
Working with Stored Functions
1. If you want to create a new stored function, then you can run the following command:
DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
-- statements
END $$
DELIMITER ;
2. For dropping a specific stored function, then you use the following command:
DROP FUNCTION [IF EXISTS] function_name;
3. If you want to display the stored function, then you can use the following command:
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE search_condition];
Querying Data from Tables
1. If you want to query all the data from the table, then you can use the following command:
SELECT * FROM table_name;
2. For querying one or more data from the table then you can use the following command:
SELECT
column1, column2, ...
FROM
Table_name;
3. For removing the duplicate rows from a query’s result, you can use the following syntax:
SELECT
DISTINCT (column)
FROM
Table_name;
4. If you want to query the data using the where clause, then you can use the following command:
SELECT select1_list
FROM table1_name
WHERE condition;
5. For changing the output of the particular column name in the query result, use the below-mentioned SQL command:
SELECT
column1 AS alias1_name,
expression AS alias1,
...
FROM
Table1_name;
6. If you want to query the data from multiple tables and generate the result that will contain the common data from both the tables, then you can use the inner join as per the following syntax:
SELECT select_list
FROM table1
INNER JOIN table2 ON condition;
7. If you want to query the data from multiple tables and generate the result that will contain the common data along with all the data from the left table, then you can use the left join as per the below command:
SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;
8. If you want to query multiple tables to get the result that is common to both the tables along with all the data from the right table, then you can use the right join using the below command:
SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;
9. If you want to apply the cartesian product on the rows of the tables, then you can use the following command:
SELECT select_list
FROM table1
CROSS JOIN table2;
10. For counting the number of rows available in a table, you can use the following command:
SELECT COUNT(*)
FROM table1_name;
11. For sorting the result set, you can use the below-mentioned syntax:
SELECT
select1_list
FROM
table1_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];
12. For grouping the rows based on the mentioned columns list, you can use the below-mentioned syntax:
SELECT select1_list
FROM table1_name
GROUP BY column_1, column_2, ...;
13. If you want to filter the particular group depending on the given condition, then you can use the HAVING clause along with the GROUP BY clause as shown in the following syntax:
SELECT select1_list
FROM table1_name
GROUP BY column1
HAVING condition1;
Modifying Data in Tables
1. For inserting a specific row in the existing table, use the following command:
INSERT INTO table_name(column_list)
VALUES(value_list);
2. If you want to insert multiple rows to the existing table, then you can use the following command:
INSERT INTO table_name(column_list)
VALUES(value_list1),
(value_list2),
(value_list3),
...;
3. If you want to update all the rows within a table, then you can use the below syntax with the update command:
UPDATE table_name
SET column1 = value1,
...;
4. If you want to update data for some particular rows, then you can use the WHERE clause to do so:
UPDATE table_name
SET column_1 = value_1,
...
WHERE condition;
5. If you want to update the rows in the table, you can use the following command:
UPDATE
table1,
table2
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition1;
6. For deleting all the rows within a table, you can use the below-mentioned command:
DELETE FROM table1_name;
7. If you want to delete the specified rows using a condition, you can use the following command:
DELETE FROM table_name
WHERE condition;
8. If you want to delete the rows using the joining, then you can use the following command:
DELETE table1, table2
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
WHERE condition;
Searching
1. If you want to search for the data, then you can use the LIKE operator as shown in the below command:
SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';
2. If you want to search the text, then you can use the regular expression like RLIKE operator as shown below:
SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';
Conclusion
In this MySQL cheat sheet, you learned the basic and most commonly used MySQL queries that are used for performing frequent tasks. As per your creativity and skills, you can even create complex yet optimized queries to retrieve the data stored within the database. If you are to appear for an interview, then you can use this cheat sheet for brushing up your skills, as we have covered almost every important aspect of MySQL. So start learning and improve your skills as per the changing trends.
People are also reading:
Leave a Comment on this Post