MySQL cheat sheet provides you with one-page that contains the most commonly used MySQL commands and statements that help you work with MySQL more effectively. MySQL command-line client Commands Connect to MySQL server using mysql command-line client with a username and password (MySQL will prompt for a password): mysql -u username -p. SQL commands Cheat Sheet. MySQL prior to version 8.0 doesn't support the WITH clause. WITH previ ous Que ryA lias AS ( SEL ECT column1, COU NT( col umn 2) AS. (My)SQL Cheat Sheet Here are the most commonly used SQL commands and the most commonly used options for each. There are many more commands and options than listed here. In other words, the syntaxes as I have listed them are far from complete. See the links at the bottom for more complete syntaxes and more commands.

• Mysql Query Commands Cheat Sheet
• Cheat Sheet Synonyms
• Cheat Sheet Recipes
• Mysql Commands Cheat Sheet
• Mysql Command Line Cheat Sheet Pdf

The SQL cheat sheet provides you with the most commonly used SQL statements for your reference. You can download the SQL cheat sheet as follows:

Querying data from a table

Query data in columns c1, c2 from a table

Query all rows and columns from a table

Query data and filter rows with a condition

Query distinct rows from a table

Sort the result set in ascending or descending order

Skip offset of rows and return the next n rows

Group rows using an aggregate function

Filter groups using HAVING clause

Querying from multiple tables

Inner join t1 and t2

Left join t1 and t1

Right join t1 and t2

Perform full outer join

Produce a Cartesian product of rows in tables

Another way to perform cross join

Join t1 to itself using INNER JOIN clause

Using SQL Operators

Combine rows from two queries

Return the intersection of two queries

Subtract a result set from another result set

Query rows using pattern matching %, _

Query rows in a list

Query rows between two values

Check if values in a table is NULL or not

Managing tables

Create a new table with three columns

Delete the table from the database

Add a new column to the table

Drop column c from the table

Add a constraint

Drop a constraint

Rename a table from t1 to t2

Rename column c1 to c2

Remove all data in a table

Using SQL constraints

Set c1 and c2 as a primary key

Set c2 column as a foreign key

Make the values in c1 and c2 unique

Ensure c1 > 0 and values in c1 >= c2

Set values in c2 column not NULL

Modifying Data

Insert one row into a table

Insert multiple rows into a table

Insert rows from t2 into t1

Update new value in the column c1 for all rows

Update values in the column c1, c2 that match the condition

Delete all data in a table

Delete subset of rows in a table

Managing Views

Create a new view that consists of c1 and c2

Create a new view with check option

Create a recursive view

Create a temporary view

Delete a view

Managing indexes

Create an index on c1 and c2 of the t table

Create a unique index on c3, c4 of the t table

Drop an index

Managing triggers

Create or modify a trigger

WHEN

• BEFORE – invoke before the event occurs
• AFTER – invoke after the event occurs

EVENT

• INSERT – invoke for INSERT
• UPDATE – invoke for UPDATE
• DELETE – invoke for DELETE

TRIGGER_TYPE

• FOR EACH ROW
• FOR EACH STATEMENT

Delete a specific trigger

CLI

• Log in as root (password is prompted):

• Log in as root with password sekrit (REMARK: no space allowed between -p and the password):

• Log in as user on another host, and use database mydb (password is prompted):

• Setting the root password (after clean install):

Queries

First log in as root and use the mysql database: mysql -uroot -p mysql (password is prompted). Don’t forget that every query must be terminated with ;

In the overview below, CAPITALIZED words are part of the SQL syntax, lowercase words are names of tables, columns, etc.

Secure installation

Most HOWTOs suggest to run the mysql_secure_installation script after installing MariaDB/MySQL. Since that is an interactive script that constantly asks for user input, it is not suitable for automated setups. There are quick-and-dirty solutions by using either expect or a here document, but these feel kludgy to me.

What happens in the script is basically setting the database root password and removing a test database and users.

Setting the root password

The following snippet will set the database root password if it wasn’t set before, and will do nothing if it was. The command mysqladmin -u root status will succeed if the password was not set, and in that case the password will be set to the specified value.

This code snippet is idempotent, i.e. you can run it several times with the same end result and without it failing if a root password was set previously.

Removing test database and users

Mysql Query Commands Cheat Sheet

After setting the root password with the previous code, the following snippet will remove the test database and anonymous users. The database root user is set to only be allowed to log in from localhost.

Create a new database and user

Cheat Sheet Synonyms

Create a database and a user with all privileges for that database (warning: user/db are first removed if they exist):

Cheat Sheet Recipes

Backup/restore

Mysql Commands Cheat Sheet

Let’s say you have a Drupal site that you want to back up/restore. The database is drupal.

Mysql Command Line Cheat Sheet Pdf

• Backup: mysqldump -u root -p drupal > drupal_backup.sql
• Restore:
  • First, ensure that the drupal database exists (see above)
  • mysql -u root -p drupal < drupal_backup.sql