Mysql Query Cheat Sheet

admin



Cheat sheet MySQL is easy to use and its syntax is easier to remember and the queries cane written easily. It can be used with any web technology to store the data. It is secure and faster to perform. Its structure is easy to work on and understand. Our SQL Cheat Sheet enables you to revise almost the entire set of SQL Commands quickly. It is highly recommended to go through this cheat sheet if you are: Preparing for your SQL exam Going for an interview. This MariaDB and MySQL cheat sheet covers useful commands for connecting to servers, analyzing data, and other key activities. The commands on this cheat sheet are valid for the interactive prompt and SQL scripts, but much can be extrapolated for use with programming libraries as well. MySQL cheatsheet The SQL cheat sheet provides you with the most commonly used SQL statements for your reference.

These are my most often used MySQL commands and queries.

Jump down to: Databases | MySQL Users | Tables | MySQL Events | Misc

Connecting to MySQL

Connect to MySQL on the command line (replace USERNAME with your own):

(You will then be prompted to enter your MySQL password.)

MySQL Databases #

List all databases on the command line:

Create a database on the command line:

Replace database_name, above, with your name of choice.

Mysql Query Cheat Sheet Examples

Delete a database:

Replace database_name, above, with the actual name of your database.

Use (or select) a database:

Check which database is currently selected:

Backing up a database on the command line with mysqldump:

Cheat

(Use ‘mysqldump --opt --all-databases > all_backup.sql‘ to backup everything.)

Import a .sql database file into MySQL on the command line:

(Replace “root” with your MySQL user name, if needed.)

MySQL Users #

List all MySQL users in a database. This shows a table of each MySQL user with the host, username, and password:

Delete a MySQL user:

Create a MySQL user and set the user’s password:

Grant database access to a MySQL user:

MySQL Tables #

List all tables of a MySQL database on the command line:

Describing the format of a table:

Replace table_name, above, with the actual name of your table.

Create a table on the command line:

Example:

Delete a table (aka Drop a table):

Replace table_name, above, with the actual name of the table you want to remove.

Delete all rows from a table, while leaving the table in tact:

Show all indexes (and keys) of a table:

Add a PRIMARY KEY to a table, assigning an existing column as the PRIMARY KEY. The bigint(20) NOT NULL part will vary according the attributes of your column:

Delete a PRIMARY KEY from a table. This is done in a different way than deleting a regular index.

Delete an index or key from a table:

Create an index on a table:

Create an index, sorted in descending order. The DESC order only works in MySQL version 8+.

Create an index on multiple columns:

Sky island?. Remove a row based on the value of a field (column) in a table:

Replace table_name, above, with the actual name of your table. Replace field_name, above, with the actual name of your field. Replace ‘whatever’ with the value you’re searching for.

Selecting from tables

To run a SELECT query from the command line, type:

Retrieving information from a table (general):

Retrieve all rows from a MySQL table:

Retrieve some rows, those with a field that has a specified value:

Retrieve table rows based on multiple critera:

Update a value for a row that has another value set to a specified value:

To UPDATE a certain field value (dependent on the current/old value) in a MySQL database from the command line, type:

Mysql query cheat sheet

To UPDATE a certain field value, regardless of the current value, just use:

Replace a string in a MySQL table. The WHERE clause is not necessary, but it can speed up the query in a very large table:

Load tab-delimited data into a table:

(Use n for NULL)

Inserting one row at a time:

(Use NULL for NULL)

Reloading a new data set into existing table:

Selecting specific columns:

Retrieving unique output records:

Sheet

Sorting:

Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

Mysql Commands Cheat Sheet Pdf

(% is wildcard – arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like “_____”;
(_ is any single character)

Extended Regular Expression Matching:

(. for char, […] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use “REGEXP BINARY”

Count all rows in a table:

Mysql Query Cheat Sheet Download

Selecting from multiple tables:

(Example)

(You can join a table to itself to compare by using ‘AS’)

Mysql Query Cheat Sheet

Maximum value:

Auto-incrementing rows:

Adding a column to an already-created table:

Removing a column:

MySQL Events #

List all MySQL events including the LAST_EXECUTED date:

Sql Query Cheat Sheet Pdf

Check if event scheduler is on or off:

Turn event scheduler on:
To turn the event schedule on, edit the my.cnf file, usually at /etc/my.cnf.
Add this line: event_scheduler=on

Then, you’ll still have to do this when connected to MySQL:

Restart the MySQL server.

Delete an event named “event_name”:

Misc #

Batch mode (feeding in a script):

Mysql Query Tutorial

(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: