MySQL Query Cheatsheet

Create Table Examples

CREATE TABLE `broker` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `redirect_url` text NOT NULL,
    `secret` varchar(30) NOT NULL,
    `modified_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Foreign Keys

Create Table With A Foreign Key

CREATE TABLE `rivers` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `country_id` TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Table With A Foreign Key 2

CREATE TABLE `rivers` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `country_id` TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add Foreign Key To Existing Table

ALTER TABLE `childTable` 
ADD CONSTRAINT `f_key_column1`
FOREIGN KEY(`column1`) REFERENCES `parentTable` (`id`)
ON DELETE RESTRICT ON UPDATE CASCADE;

Find Size (Row Count) of A Table

To grab a quick "estimate" value, you can run:

# get the db to update internal stats
ANALYZE TABLE table_name

# select the row count from the internal stats
SELECT table_rows "Rows Count"
FROM information_schema.tables
WHERE table_name="Table_Name"
AND table_schema="Database_Name";

Please note that this will not be an exact answer unlike counting the rows, but should be a close estimate.

Count Rows In A Table

This will get you the exact number of rows that a table has, but can take quite some time on larger tables.

SELECT COUNT(*) FROM `table1`

There must not be a space between COUNT and (*)

Using count(id) instead of count(*) won't be much faster. Some people have actually reported it being slower. This is a test I did on a table with 26 columns.

Selection Queries

Select All Data From A Table

SELECT * FROM `table1`

Select Only Certain Columns From A Table

SELECT `column1`, `column2`, `column3` FROM `table1`

Select Rows That Have Certain Values

SELECT * FROM `table` WHERE `column1`='value1', `column2`='value2'

Select Rows That Have Column Value In Range

SELECT * FROM `table` WHERE `column1` IN ('value1', 'value2')

Select Rows That Have Either Column1 in range or Column2 in Range

SELECT * FROM `table` WHERE `column1` IN ('value1', 'value2') OR WHERE `column2` IN ('value1', 'value2')

Select Chunks of Results

If you are dealing with large datasets, then sometimes it is best to just grab and process a chunk of results at a time.

SELECT * FROM table LIMIT $CHUNK_SIZE
# do some processing
SELECT * FROM table LIMIT $CHUNK_SIZE OFFSET $CHUNK_SIZE
# do some more processing
SELECT * FROM table LIMIT $CHUNK_SIZE OFFSET ($CHUNK_SIZE * 2)
...

CHUNK_SIZE is just the number of rows you want to handle at a time

Inserting Data

Import CSV File

LOAD DATA INFILE '[full path to csv]' 
INTO TABLE `[table name]` 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The command above assumes the first line is the name of the fields and not data. If this is not the casse then remove the last part of the query.

Updating Data

Update Column To Be Sum Of Others In Same Row

UPDATE `table1` 
SET sum_column = (column1 + column2 + column3)
WHERE `id`='123';

Uniqueness

Make A Column Unique

Refer to "Add Index" under table management.

Find Unique Values In A Table Column

SELECT DISTINCT(`column1`) FROM `table1`

Count Unique Column Values

SELECT COUNT(DISTINCT(`column1`)) as `count` FROM `table1`

The result will show up as the value of column "count"

Select Unique Sets

The following query will only select rows where the set formed by column1 and column2 are unique.

SELECT * FROM table1 GROUP BY column1, column2 HAVING count(*) = 1

Find If Column Set Is Unique

If the query below returns any rows, then column set is NOT unique, otherwise it is.

SELECT * FROM table1 GROUP BY column1, column2 HAVING count(*) > 1

Select Duplicate Sets

This is the exact same query as before, just given another name for fast lookup.

SELECT * FROM table1 GROUP BY column1, column2 HAVING count(*) > 1

Table Management

Add Column

Use the ADD command with ALTER TABLE to add a column. Everything after INT UNSIGNED in the example below is optional.

ALTER TABLE `table_name` 
ADD `column_name` 
INT UNSIGNED 
NOT NULL 
DEFAULT 1 
AFTER `column_1`

Add Column At Start of Table

To add a column to the start of a table, you need to use the FIRST keyword like so:

ALTER TABLE table1 ADD 
`column1` INT UNSIGNED 
NOT NULL FIRST

Add Column As Primary Key

If your table doesn't have a primary key and you want to add one, I suggest using the following:

ALTER TABLE table1 ADD 
`id` INT UNSIGNED 
NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

Add Index

ALTER TABLE `table_name` 
ADD INDEX `index_name` (`column1`, `column2`);

If you want to add a unique index:

ALTER TABLE `table_name` 
ADD UNIQUE INDEX `index_name` (`column1`, `column2`);

Remove Index

ALTER TABLE `table_name` DROP INDEX `index_name`;

Change Column Definition

To change a column, simply use the MODIFY command with ALTER TABLE.

ALTER TABLE `table_name` 
MODIFY `column_name` INT UNSIGNED NOT NULL

Rename Column

To rename a column, use the CHANGE command rather than MODIFY.

ALTER TABLE `my_table` \
CHANGE `original_name` `new_name` varchar(30) NOT NULL

Remove Column

Use the DROP COLUMN command to remove columns. You can drop any number of columns in a single query as shown below:

ALTER TABLE `table_name`
DROP COLUMN `column1`,
DROP COLUMN `column2`;

Remove Default Value

To remove a default value, simply use the modify column command shown above without having specified anything for DEFAULT. Things like DROP DEFAULT do not work in MySQL.

Rename Table

RENAME TABLE `[table name]` to `[new table name]`

Find Tables Without Primary Key

select table_schema,table_name 
from information_schema.columns  
group by table_schema,table_name   
having sum(if(column_key in ('PRI','UNI'), 1,0)) = 0;

List MyISAM Tables

Sometimes it is useful to find out which of your tables are using MyISAM for various reasons (not ACID compliant etc).

SELECT `table_name` FROM INFORMATION_SCHEMA.TABLES 
WHERE engine = 'MyISAM';

You could replace MyISAM with any engine to list tables of some other engine you want to check for.

Alternatively, to only list those tables in a specific database, use the following:

SHOW TABLE STATUS FROM `YOUR_DB_NAME_HERE` WHERE `Engine`!='InnoDB';

List Empty Tables

SELECT `table_name` 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_ROWS = '0' 
AND TABLE_SCHEMA = '[database name]';

Working With Timestamps

Using DATEDIFF

Select items from the table that are less than 1 day old.

SELECT * FROM `my_table` 
WHERE DATEDIFF(NOW(), `timestamp_field`) < 1;

Using Exact Date

Delete items that have a timestamp before the 1st of January 2017.

DELETE FROM `my_table` 
WHERE `timestamp_field` < '2017-01-01 00:00:00';

Misc

Find Size of Table in Memory

SELECT data_length+index_length table_size
FROM information_schema.tables
WHERE table_schema='[database name]'
AND table_name='[table name]'
AND engine IS NOT NULL;

List Column Names in Alphabetical Order

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    table_name = '[table name]'
    AND table_schema = '[database name]'
ORDER BY column_name;

Output is in bytes.

For a full breakdown of the different parts, you can execute:

SHOW TABLE STATUS LIKE '[table name]'\G

References

Author

Programster

Stuart is a software developer with a passion for Linux and open source projects.

comments powered by Disqus