Today we will learn about the MySQL cheat sheet. Get quick access to essential MySQL commands and syntax with our comprehensive cheat sheets. Streamline your MySQL workflow today, from basic queries to advanced database management. A relational database management system (RDBMS) helps IT teams and others create, update, administer, and interact with a relational database. MySQL is one of the most popular and widely used relational database management systems. MySQL database is supported by SQL or Structured Query Language, which uses structured queries.
Many small and large businesses rely on MySQL because it provides high-performance applications. In addition, MySQL’s commands and statements allow all users to work with web-based and embedded database applications. It contains many useful commands and powerful instructions for working with applications that perform a specific task or service. MySQL commands and queries allow developers to use MySQL databases in real time. These questions are supported on Windows, UNIX, macOS, and Linux systems.
MySQL has many commands, and memorizing them all is not easy. Many users search for a MySQL cheat sheet to work more efficiently and quickly. MySQL cheat sheet includes all popular and widely used syntax and commands. A cheat sheet containing a brief summary of all the popular commands is tempting. Continue reading this tutorial which contains the most used commands and simple methods. This tutorial contains useful tips and tricks to help you use and connect to a MySQL Server instance.
MySQL Installation
You can install MySQL Server on various operating systems and platforms like Windows, OSX, Linux, etc. If you are planning to install MySQL on Windows, follow these steps:
- Download MYSQL Installer from Install MySQL Installer.
- Run the installer you downloaded earlier.
- Select the appropriate setup type. Developer defaults are preferred.
- Finally, complete the installation. (This step will install multiple MySQL products.)
If you are going to install MySQL on Linux, follow these instructions:
- Open Terminal.
- If you are using Debian based (apt): sudo apt install mysql-server then run this command
- If you use distros that use yum, run this command: sudo yum install mysql-shell
- And if you use distros that use dnf, run this command: sudo dnf install mysql-shell
MySQL cheat sheet for client commands
When you connect to the server using the MySQL command-line client, MySQL will prompt for a password. The client command line is:
To allow users to connect to MySQL CLI:
>MYSQL -U [USERNAME] -P;
To exit the MySQL CLI:
>EXIT;
To clear the MySQL shell:
>SYSTEM CLEAR;
Create a new user:
>CREATE USER 'NEWUSER'@'LOCALHOST' IDENTIFIED BY 'NEW_PASSWORD'
To show users who have access to the MySQL client:
>SELECT USER, HOST FROM MYSQL.USER;
Delete a user:
> DROP USER 'USERNAME'@'LOCALHOST';
Assign privileges to a user:
>GRANT ALL PRIVILEGES ON * . * TO 'USERNAME'@'LOCALHOST';
Show the privileges of a MySQL user:
> SHOW GRANTS FOR 'USERNAME'@'LOCALHOST';
Revoke all privileges of a MySQL user:
>REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'LOCALHOST';|
To recreate the original database object and table data from a backup set of SQL statements:
>MYSQLDUMP -U USERNAME -P DATABASENAME> DATABASENAME_BACKUP.SQL
MySQL cheat sheet for working with databases
To create a new database with a specific name that does not exist on the database server:
CREATE DATABASE [IF NOT EXISTS] database_name;
To use an existing database or change another one you’re working with:
USE database_name;
To drop a database permanently and delete all files associated with this database:
DROP DATABASE [IF EXISTS] database_name;
Displaying all available databases on 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 into an existing database:
>CREATE TABLE TABLENAME ( COLUMN1 DATATYPE, COLUMN2 DATATYPE, COLUMN3 DATATYPE, .... CONSTRAINTS .... );
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 amend an existing column in an existing table:
>ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME DATATYPE;
To add or change a primary key to an existing table:
>ALTER TABLE TABLENAME ADD PRIMARY KEY (COLUMNNAME,...);
To drop an existing primary key into a table:
>ALTER TABLE TABLENAME DROP PRIMARY KEY;
To create a foreign key to an existing table.
>ALTER TABLE TABLENAME1 ADD FOREIGN KEY (COLUMN1) REFERENCES TABLENAME2(COLUMN2);
To drop an existing foreign key into an existing table:
> ALTER TABLE TABLENAME DROP FOREIGN KEY FOREIGNKEY_NAME;
To rename a table:
>RENAME TABLE OLD_TABLENAME TO NEW_TABLENAME;
To drop the entire table and its definitions:
>DROP TABLE TABLE_NAME;
To remove all records in a MySQL table:
>TRUNCATE TABLE TABLENAME;
To display all columns of a table:
>DESCRIBE TABLE_NAME;
To display all values in 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 using the following statement.
To create an index on an existing table with a specific name:
CREATE INDEX [index_name] ON [table_name] (column names);
Create a unique index:
CREATE UNIQUE INDEX index_name ON table_name (column,...);
To delete an existing index:
DROP INDEX index_name;
Searching data from the table
To query all data from a particular table:
SELECT * FROM table_name;
To search for data from one column or multiple columns in a table:
SELECT
column1, column2, ...
FROM
table_name;
To remove duplicate rows from the results of a query:
SELECT DISTINCT (column) FROM table_name;
To add filters for searching data:
SELECT select_list FROM table_name WHERE condition;
To change the output of column names using column aliases:
SELECT column1 AS alias_name, expression AS alias, ... FROM table_name;
To search for data from multiple tables with the inner join command:
SELECT select_list FROM table1 INNER JOIN table2 ON condition;
To search data from multiple tables with the LEFT JOIN command:
SELECT select_list FROM table1 LEFT JOIN table2 ON condition;
Search data from multiple tables with the right join command:
SELECT select_list FROM table1 RIGHT JOIN table2 ON condition;
To create a Cartesian product of rows:
SELECT select_list FROM table1 CROSS JOIN table2;
To count the rows of a table:
SELECT COUNT(*) FROM table_name;
Sort the results of a 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
Insert a new row into a new table:
INSERT INTO table_name(column_list) VALUES(value_list);
Insert multiple rows into 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 some rows of data based on specified conditions using the WHERE clause:
UPDATE table_name
SET column_1 = value_1,
...
WHERE condition
To update some rows of data by specified condition using JOIN clause:
UPDATE table1, table2 INNER JOIN table1 ON table1.column1 = table2.column2 SET column1 = value1, WHERE condition;
Drop all rows of a new table:
DELETE FROM table_name;
To delete rows by specified conditions:
DELETE FROM table_name WHERE condition;
Delete a table with 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 text with a regular expression:
SELECT column_list FROM tab_name WHERE column RLIKE 'regular_expression';
MySQL cheat sheet for working with views
Create a view:
CREATE VIEW [IF NOT EXISTS] view_name AS select_statement;
Create a view using the “with check option” check below command:
CREATE VIEW [IF NOT EXISTS] view_name AS select_statement WITH CHECK OPTION;
Create or replace a view:
CREATE OR REPLACE view_name AS select_statement;
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 occur in tables or are observed in the database, triggers are executed automatically. They are the procedural code of a database.
To remove an existing trigger:
DROP TRIGGER [IF EXISTS] trigger_name;
Show all available triggers on the database:
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE condition];
MySQL cheat sheet working for stored procedures
Writing methods that perform a similar task saves time. They are used to act on some operations within a database. It is simple to create stored procedures in MySQL to save time. For your needs, all 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;
Show all available methods in the database:
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE condition]; Modifying data in tables
Working with stored functions
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 covers the most used and popular commands to provide an enjoyable, easy, efficient, 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, methods, functions, etc.
Save it for use when you absolutely need it!
