Mastering SQL: The Essential MySQL Cheat Sheet

List of content you will read in this article:

A relational database management system (RDBMS) will help IT teams and others to create, update, administer, and interact with a relational database. MySQL is one of the most popular and commonly used Relational Database Management Systems. MySQL databases are supported by SQL or Structured Query Language, which utilizes structured queries. Many small-scale and large-scale businesses rely on MySQL as it provides high-performance applications. Also, MySQL's commands and statements allow users to work with web-based and embedded database applications. It has many useful commands and powerful directives for working with applications that perform a specific task or service. MySQL commands and queries allow developers to utilize MySQL databases in real-time. These queries are supported on Windows, UNIX, macOS, and Linux systems.

MySQL's commands are a lot, and it isn't easy to memorize all of them. Many users look for a MySQL cheat sheet to work more effectively and quickly. The MySQL cheat sheet includes all the most popular and widely used syntaxes and commands.

Are you tempted to have a MySQL cheat sheet? A cheat sheet containing a concise summary of all popular commands is tempting. Continue reading this tutorial which contains the most commonly used commands and simple methods. This tutorial contains useful tips and tricks that will aid you in using and connecting MySQL Server instances.

MySQL Installation

You can install MySQL servers on different operating systems and platforms, such as Windows, OSX, Linux, etc.

If you're planning to install MySQL on Windows, follow these steps:

Download the MYSQL installer from Install MySQL Installer.
Execute the installer that you downloaded before.
Select the appropriate setup type. Developer Default is preferred.
Finally, complete the installation. (multiple MySQL products will be installed in this step.)

If you're going to install MySQL on Linux, follow these instructions:

Open the Terminal.
Run this command if you're using distros that are Debian based (apt): sudo apt install mysql-server
Run this command if you're using distros that use yum: sudo yum install mysql-shell
And if you're using distros that use dnf, run this command: sudo dnf install mysql-shell

MySQL cheat sheet for client Commands

MySQL will prompt for a password when you connect to the server using the MySQL command-line client. Client command lines are:

To allow users to connect to the MySQL CLI:

>MYSQL -U [USERNAME] -P;

To Exit the MySQL CLI:

>EXIT;

To clear the MySQL shell:

>SYSTEM CLEAR;

To create a new user:

>CREATE USER 'NEWUSER'@'LOCALHOST' IDENTIFIED BY 'NEW_PASSWORD'

To show users that have access to the MySQL Client:

>SELECT USER, HOST FROM MYSQL.USER;

To delete a user:

> DROP USER 'USERNAME'@'LOCALHOST';

To assign privileges to a user:

>GRANT ALL PRIVILEGES ON * . * TO 'USERNAME'@'LOCALHOST';

To show the privileges of a MySQL user:

> SHOW GRANTS FOR 'USERNAME'@'LOCALHOST';

To revoke all privileges of a MySQL user:

>REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'LOCALHOST';

To recreate the original database objects and table data from a backup set of SQL statements:

>MYSQLDUMP -U USERNAME -P DATABASENAME> DATABASENAME_BACKUP.SQL

MySQL cheat sheet for working with database

To create a new database with a specific name that doesn't exist on the database server:

CREATE DATABASE [IF NOT EXISTS] database_name;

To use or change a current database to another that you're working with:

USE database_name;

To drop a database forever and delete all files associated with this database:

DROP DATABASE [IF EXISTS] database_name;

Displaying all available databases in the current database server:

SHOW DATABASE;

MySQL cheat sheet for table commands

To show all tables in the current database:

>SHOW TABLES;

To create a new table within the current database:

>CREATE TABLE TABLENAME (

COLUMN1 DATATYPE,

COLUMN2 DATATYPE,

COLUMN3 DATATYPE,

....

CONSTRAINTS ....

);

To add a new column to a table:

>ALTER TABLE TABLENAME ADD COLUMNNAME DATATYPE;

To drop a column from a table:

>ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;

To alter an existing column in an existing table:

>ALTER TABLE TABLENAME

ALTER COLUMN COLUMNNAME DATATYPE;

To add or alter a primary key to an existing table:

>ALTER TABLE TABLENAME

ADD PRIMARY KEY (COLUMNNAME,...);

To drop an existing primary key in a table:

>ALTER TABLE TABLENAME

DROP PRIMARY KEY;

To create a foreign key on an existing table.

>ALTER TABLE TABLENAME1 ADD FOREIGN KEY (COLUMN1) REFERENCES TABLENAME2(COLUMN2);

To drop an existing foreign key in an existing table:

> ALTER TABLE TABLENAME DROP FOREIGN KEY FOREIGNKEY_NAME;

To change a table's name:

>RENAME TABLE OLD_TABLENAME TO NEW_TABLENAME;

To drop the entire table and its definition:

>DROP TABLE TABLE_NAME;

To remove all records in the MySQL table:

>TRUNCATE TABLE TABLENAME;

To display all the columns of a table:

>DESCRIBE TABLE_NAME;

To display all the values of a particular column:

>DESCRIBE TABLE_NAME COLUMN_NAME;

Working with Indexes

Adding an index to the table will allow faster retrieval of records. We can add an index to a table by using the following statements.

To create an index with a specific name on an existing table:

CREATE INDEX [index_name] ON [table_name] (column names);

To create a unique index:

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

To delete an existing index:

DROP INDEX index_name;

Querying data from tables

To query all data from a specific table:

SELECT * FROM table_name;

To query data from one column or more columns of a table:

SELECT

column1, column2, ...

FROM

table_name;

To Remove duplicate rows from the result of a query:

SELECT

DISTINCT (column)

FROM

table_name;

To add filters for querying data:

SELECT select_list

FROM table_name

WHERE condition;

To change the column name's output using the column alias:

SELECT

column1 AS alias_name,

expression AS alias,

...

FROM

table_name;

To query data from multiple tables with the inner join command:

SELECT select_list

FROM table1

INNER JOIN table2 ON condition;

To query data from multiple tables with the left join command:

SELECT select_list

FROM table1

LEFT JOIN table2 ON condition;

To query data from multiple tables with the right join command:

SELECT select_list

FROM table1

RIGHT JOIN table2 ON condition;

To construct a cartesian product of rows:

SELECT select_list

FROM table1

CROSS JOIN table2;

To count rows of a table:

SELECT COUNT(*)

FROM table_name;

To sort the results of a set or list:

SELECT

select_list

FROM

table_name

ORDER BY

column1 ASC [DESC],

column2 ASC [DESC];

To group rows:

SELECT select_list

FROM table_name

GROUP BY column_1, column_2, ...;

Code language: SQL (Structured Query Language) (sql)

To filter existing groups:

SELECT select_list

FROM table_name

GROUP BY column1

HAVING condition;

Modifying data in tables

To insert a new row to an existing table:

INSERT INTO table_name(column_list)

VALUES(value_list);

To insert multiple rows to an existing table:

INSERT INTO table_name(column_list)

VALUES(value_list1),

(value_list2),

(value_list3),

...;

To update all rows in a table:

UPDATE table_name

SET column1 = value1,

...;

To update data for some rows by specified condition using WHERE clause:

UPDATE table_name

SET column_1 = value_1,

...

WHERE condition

To update data for some rows by specified condition using JOIN clause:

UPDATE

table1,

table2

INNER JOIN table1 ON table1.column1 = table2.column2

SET column1 = value1,

WHERE condition;

To drop all rows of an existing table:

DELETE FROM table_name;

To delete rows by specified condition:

DELETE FROM table_name

WHERE condition;

To delete a table with the JOIN command:

DELETE table1, table2

FROM table1

INNER JOIN table2

ON table1.column1 = table2.column2

WHERE condition;

Searching data from the table

To search data from a table:

SELECT column_list FROM tab_name

WHERE column LIKE '%pattern%';

To search for text with a regular expression:

SELECT column_list FROM tab_name

WHERE column RLIKE 'regular_expression';

MySQL cheat sheet for working with views

To create a view:

CREATE VIEW [IF NOT EXISTS] view_name

AS

select_statement;

To create a view using the “WITH CHECK OPTION” command:

CREATE VIEW [IF NOT EXISTS] view_name

AS select_statement

WITH CHECK OPTION;

To create or replace a view:

CREATE OR REPLACE view_name

AS

select_statement;

To delete a view:

DROP VIEW [IF EXISTS] view_name;

To delete multiple views:

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

To rename a specific view:

RENAME TABLE view_name

TO new_view_name;

To show views of a database:

SHOW FULL TABLES

[{FROM | IN } database_name]

WHERE table_type = 'VIEW';

MySQL cheat sheet for working with Triggers

When certain events take place on a table or view in a database, triggers are automatically executed. They are procedural codes in a database.

To create a new trigger:

CREATE TRIGGER trigger_name

{ AFTER | BEFORE } {INSERT | UPDATE| DELETE }

ON tab_name FOR EACH ROW

BEGIN

--variable declarations

--trigger code

END;

To remove an existing trigger:

DROP TRIGGER [IF EXISTS] trigger_name;

To show all available triggers in the database:

SHOW TRIGGERS

[{FROM | IN} db_name]

[LIKE 'pattern' | WHERE condition];

MySQL cheat sheet for working with stored procedures

Writing procedures that perform a similar task is time-saving. Standard SQL statements are grouped into stored procedures with a specific name. These are used to perform some action within a database. It's easy to create stored procedures in MySQL to save time. All you need is the following command.

To create a stored procedure:

CREATE PROCEDURE procedure_name[ (parameter_list) ]

BEGIN

Declaration_section

Executable_section

END;

To drop an existing stored procedure from the database:

DROP PROCEDURE [IF EXISTS] procedure_name;

To show all available procedures in the database:

SHOW PROCEDURE STATUS

[LIKE 'pattern' | WHERE condition]; Modifying data in tables

Working with stored functions

To create a new stored function:

DELIMITER $$

CREATE FUNCTION function_name(parameter_list)

RETURNS datatype

[NOT] DETERMINISTIC

BEGIN

-- statements

END $$

DELIMITER ;

To delete an existing function:

DROP FUNCTION [IF EXISTS] function_name;

To show all stored functions:

SHOW FUNCTION STATUS

[LIKE 'pattern' | WHERE search_condition];

Conclusion

This tutorial covered the most widely used and popular commands to provide an enjoyable, easy, effective, and time-saving experience working with MySQL. Our brief discussion covers the most commonly used MySQL command line client commands and commands that work with databases, tables, indexes, views, triggers, procedures, functions, etc.

Please save it to use when you essentially need it!

People also read:

Category: Tutorials