MySQL cheat sheet provides a single page that contains all of the most commonly used commands and statements. This one-page cheat sheet helps us to work with MySQL database more effectively and quickly.
MySQL is an open-source widely used RDBMS database that provides high performance and scalable web-based and embedded database applications for the customers. It has many useful commands and statements to work with web-based and embedded database applications. The command in MySQL is a powerful directive used to perform a specific task or service. They are very helpful for every developer to know and use these queries for interacting with the MySQL databases in real-time. MySQL commands are based on a structured query language (SQL) which can support various operating systems such as Linux, UNIX, macOS, and Windows.
We can see a list of all commands in MySQL using the below query:
mysql> HELP; Let us see the most commonly used commands and statements that we need to create and manage the database in MySQL.
MySQL command-line client commands
This command allows us to connect with MySQL Server with a username and passwords using below syntax.
mysql -u [username] -p; If you want to connect with a particular database, use this syntax:
mysql -u [username] -p [database]; If you want to set a new password, use this syntax:
mysqladmin -u root password your_password; We can use the “exit” command to quit the MySQL command-line client.
We can clear the console window in Linux using the below command:
mysql> system clear; It is to be noted that there is no command available for Windows to clear the console window of MySQL.
Working with Database
A database stores the organized collection of records that can be accessed and manage by the user very easily. It holds the data into tables, rows, columns, and indexes that help us to find the relevant information quickly. We can create a database using the below syntax. It also checks the database name, whether it already exists or not.
CREATE DATABASE IF NOT EXISTS db_name; If you want to change the current database with another database on which you are working, use the below syntax:
mysql> use db_name; We can delete a particular database along with its associated files permanently using the below syntax:
DROP DATABASE IF EXISTS db_name; To show all databases in the current server, use this syntax:
mysql> SHOW DATABASES; Working with tables
A table is a collection of related data stored in a row and column format within a database. We can create a new table using the below syntax. It also checks the table name, whether it already exists or not.
CREATE TABLE IF NOT EXISTS tab_name (
column_list (s)
); We can use the INSERT statement to add a record into the table, which is given below:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); We can delete a particular table along permanently using the below syntax:
DROP TABLE IF EXISTS tab_name; To show all tables in the current database, use this syntax:
mysql> SHOW TABLES; We can add a new column to the table or can drop any column, use the following statements:
-- Add a new column
ALTER TABLE tab_name ADD [COLUMN] colm_name;
-- Delete column
ALTER TABLE tab_name DROP [COLUMN] colm_name; We can add a primary key into the table, or can remove it from the table, use the following statements:
-- Add a primary key
ALTER TABLE tab_name ADD PRIMARY KEY (colm_names);
-- Delete primary key
ALTER TABLE tab_name DROP PRIMARY KEY; If you want to add an index with a specific name on a column, use this syntax:
ALTER TABLE tabl_name ADD INDEX [name] (column(s)); We can show the structure of a table using the below statement:
DESCRIBE tab_name; We can show the column information of a table using the below statement:
DESCRIBE tab_name colm_name; Working with Indexes
An index is a data structure that allows us to the faster retrieval of records on a database table. If we want to add an index in the table, we will use the statement as follows:
CREATE INDEX [index_name] ON [table_name] (column names); To drop an index, use this statement:
DROP INDEX index_name; Working with View
The View in MySQL is used to create a virtual table by joining one or more tables. We can create or replace a VIEW by using SELECT statements as follows:
CREATE [OR REPLACE] VIEW IF NOT EXISTS view_name AS SELECT column_list
FROM tables WHERE conditions; We can remove a view using the below statement:
DROP VIEW [IF EXISTS] view (s).; We can rename a view using the below statement:
RENAME TABLE view_name TO new_view_name; If you want to show all the views available in the database, use the below statement:
SHOW FULL TABLES [{FROM | IN } db_name] WHERE tab_type = 'VIEW'; Working with Stored Procedure
A stored procedure is a group of SQL statements with a specific name that accepts some inputs and performs a certain task. It saves time to write the same code again and again in a database. We can create a stored procedure in MySQL using the below statements:
CREATE PROCEDURE procedure_name[ (parameter_list) ]
BEGIN
Declaration_section
Executable_section
END;If you want to remove an existing stored procedure from the database, use the below statement:
DROP PROCEDURE [IF EXISTS] procedure_name; If you want to show all procedures available in the database, use the below statement:
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE condition]; Working with Triggers
A trigger is a procedural code in a database that automatically executed whenever certain events on a particular table or view in the database occur. We can create a new trigger using the below statements:
CREATE TRIGGER trigger_name
{ AFTER | BEFORE } {INSERT | UPDATE| DELETE }
ON tab_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END; If you want to remove an existing trigger, use the below statement:
DROP TRIGGER [IF EXISTS] trigger_name; If you want to show all triggers available in the database, use the below statement:
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE condition]; Working with Stored Functions
A stored function in MySQL is a set of SQL statements that perform some task/operation and return a single value. It is one of the types of stored programs in MySQL. When you will create a stored function, make sure that you have a CREATE ROUTINE database privilege. The syntax of creating a stored function in MySQL is given below:
DELIMITER $$
CREATE FUNCTION function_name(parameter(s))
RETURNS datatype
[NOT] {DETERMINISTIC, NO SQL, or READS SQL DATA}
BEGIN
-- SQL statements
END $$
DELIMITER ; If you want to remove an existing trigger, use the below statement:
DROP FUNCTION [IF EXISTS] fun_name; If you want to show all triggers available in the database, use the below statement:
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE condition];Popular queries based on tables
Here, we will see some of the essential queries executed on tables to find the relevant information.
If we want to show all the table data, use this syntax:
SELECT * FROM tab_name; If we want to show the data of some selected columns from a table, use this syntax:
SELECT colm1, colm2... FROM tab_name; We can get filtered result from the table using the below statement:
SELECT column_list(S) FROM tab_name WHERE condition; Sometimes our tables contain duplicates rows. But we want to show only the unique(distinct) rows, in that case, the below statements will be executed:
SELECT DISTINCT (column) FROM tab_name; Sometimes, the column name of a table is not appropriate for the output. In that case, we can use the below statement to change the column name in the result:
SELECT
Column(s) AS alias_name(s),
Expression AS alias.....
FROM tab_name; If we want to get the output from more than one table, we need to use the JOIN operation. The syntax for joining two tables is given below:
SELECT select_list
FROM table1
JOIN table2 ON condition; We can count the number of rows with this statement:
SELECT COUNT(*) FROM tab_name; We can sort the result set using the following statement:
SELECT column_list FROM tab_name
ORDER BY colm1 ASC [DESC], colm2 ASC [DESC]; If we want to group rows into the result set, use the below syntax:
SELECT column_list FROM tab_name
GROUP BY colm1, colm2 ...; If we want to update the rows of the table, use the below syntax:
UPDATE table_name
SET column1 = value1 ... WHERE condition;We can delete all records from the table using the below syntax:
DELETE FROM tab_name; Searching data from the table
We can search for data using the LIKE clause, as shown below:
SELECT column_list FROM tab_name
WHERE column LIKE '%pattern%'; We can also search for text using a regular expression with RLIKE operator, as shown below:
SELECT column_list FROM tab_name
WHERE column RLIKE 'regular_expression'; Control Option to the Administrator
Here, we will see the features that provide control options to the administrators and users on the database.
The grant statement enables system administrators to assign privileges and roles to the MySQL user accounts for accessing the database.
GRANT privilege_name(s)
ON object
TO user_account_name;The revoke statement enables system administrators to revoke privileges and roles to the user accounts on the database in the past.
REVOKE privilege_name(s)
ON object
FROM user_account_name;
Leave a Reply