• MySQL Cheat Sheet Edit Cheat Sheet. Below you find a unordered list of solutions by tasks useful for a MySQL DBA: Live Monitoring of MySQL. There are two useful tools: mytop; innotop. With “mytop” being an own Debian package, while “innotop” is included in the “mysql-client” package. From both innotop has the more advanced.
• SQL commands Cheat Sheet. HAVING Limit the results of a query based on an aggregate property. MySQL prior to version 8.0 doesn't support the WITH clause.
• MySQL Cheat sheet These are basic MySQL queries that you can use to extract data for a MySQL database. Query terms that are italicized represent the names of fields or tables in your database.
• MySQL queries cheat sheet. Connect to host 123.45.6.789 with username ken and enter password when prompted. Mysql -h 123.45.6.789 -u ken -p Backup and restore mysql database.

These are my most often used MySQL commands and queries.

MySQL SQL Injection Practical Cheat Sheet There are lot of excellent SQL injection cheat sheets out there; however, I found the majority provide only the components of a SQL injection rather an entire, working string. As a result, successfully putting a valid query together can take some trial and error and waste precious time.

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.

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:

(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:

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:

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:

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:

(% 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:

Selecting from multiple tables:

(Example)

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

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: Drivers dvb-tv sound cards & media devices.

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.

Download dragonchip driver. Delete an event named “event_name”:

Misc #

Batch mode (feeding in a script):

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

Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk.

The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. Download datamax driver. You can download this cheat sheet as follows:

You may also read the contents here:

SQL Basics Cheat Sheet

SQL

SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.

SAMPLE DATA

QUERYING SINGLE TABLE

Fetch all columns from the country table:

Fetch id and name columns from the city table:

Fetch city names sorted by the rating column in the default ASCending order:

Fetch city names sorted by the rating column in the DESCending order:

Aliases

Columns

Tables

FILTERING THE OUTPUT

COMPARISON OPERATORS

Fetch names of cities that have a rating above 3:Fetch names of cities that are neither Berlin nor Madrid:

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':Fetch names of cities that start with any letter followed by'ublin' (like Dublin in Ireland or Lublin in Poland):

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:Fetch names of cities that don't miss a rating value:Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

QUERYING MULTIPLE TABLES

INNER JOIN

JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there's no matching row, NULLs are returned as values from the second table.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

FULL JOIN

FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned.

CROSS JOIN

CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

NATURAL JOIN used these columns to match rows:
city.id, city.name, country.id, country.name.
NATURAL JOIN is very rarely used in practice.

AGGREGATION AND GROUPING

GROUP BYgroups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values.

AGGREGATE FUNCTIONS

• avg(expr) − average value for rows within the group
• count(expr) − count of values for rows within the group
• max(expr) − maximum value within the group
• min(expr) − minimum value within the group
• sum(expr) − sum of values within the group

EXAMPLE QUERIES

Find out the number of cities:

Find out the number of cities with non-null ratings:

Find out the number of distinctive country values:

Find out the smallest and the greatest country populations:

Find out the total population of cities in respective countries:

Find out the average rating for cities in respective countries if the average is above 3.0:

SUBQUERIES

A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries.

SINGLE VALUE

The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.

This query finds cities with the same rating as Paris:

MULTIPLE VALUES

A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.

This query finds cities in countries that have a population above 20M:

CORRELATED

A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

This query finds cities with a population greater than the average population in the country:

This query finds countries that have at least one city:

SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different

UNION

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows.

This query displays German cyclists together with German skaters:

INTERSECT

INTERSECT returns only rows that appear in both result sets.

This query displays German cyclists who are also German skaters at the same time:

EXCEPT

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.

This query displays German cyclists unless they are also German skaters at the same time:

Mysql Query Cheat Sheet