MySQL Query Cheatsheet

Related Posts

• Basic MySQL Administration for all your administration needs, such as setting up users and taking backups.

Table of Contents

1. Create Table Examples
2. Selections
3. Foreign Keys
4. Triggers
5. Table Size Analytics
6. Inserting / Importing Data
7. Updating Data
8. Uniqueness Queries
9. Table Management
10. Working With Timestamps
11. Working With Dates
12. Misc

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;

Clone A Table Structure

If you have a table that you already like the structure of, its easy to create another the same (however this will not copy the data).

CREATE TABLE `my_table_clone` LIKE `table_to_copy_from`;

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;

Selections  

Select all columns

SELECT * FROM `table1`;

Select All Columns From One Table in a Join.

SELECT `table1`.* FROM `table1`
JOIN `table2` ON `table1`.`someTable1Column` = `table2`.`someTable2Column`
order by table2.someColumn

The order by part is just there in the example to demonstrate a use case as to why would even want to bother doing a join if only selecting data from one table.

Select All Unique IDs across Multiple Tables

Let's imagine you have multiple tables of data containing identifiers and you need to know all your identifiers across all of the tables. You may wish to do the following:

SELECT DISTINCT (`id`) FROM (
    SELECT `id` FROM `table1` 
    UNION
    SELECT `id` FROM  `table2`
) AS inputTable
ORDER BY `id` ASC;

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)
...

E.g.

SELECT * FROM table LIMIT 10
# do some processing
SELECT * FROM table LIMIT 10 OFFSET 10
# do some more processing
SELECT * FROM table LIMIT 10 OFFSET 20
...

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

Foreign Keys  

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;

Remove Foreign Key In Existing Table

ALTER TABLE `childTable` 
DROP FOREIGN KEY `f_key_column1`;

Remove / Drop Constraint

There is no DROP Constraint in MySQL. Instead, one has to utilize DROP FOREIGN KEY OR DROP INDEX as outlined above.

Triggers  

Show Table Triggers

SHOW TRIGGERS where `Table` = 'my_table_name'\G

Triggers tend to be very long, so we ended with \G to return the result in vertical format, which makes the output much clearer to read.

Create a Trigger

DELIMITER $$

CREATE TRIGGER unique_terminated_employee BEFORE INSERT ON terminated_employee
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM active_employee WHERE employee_id = NEW.employee_id;
  IF (c > 0) THEN
    SET NEW.employee_id = NULL;
  END IF;

END$$

DELIMITER ;

However, if you are trying to create this trigger through PHP MySQLi, then you would not be messing around with setting the delimiter, and you would do something like so:

$query = <<<EOT
CREATE TRIGGER unique_terminated_employee BEFORE INSERT ON terminated_employee
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM active_employee WHERE employee_id = NEW.employee_id;
  IF (c > 0) THEN
    SET NEW.employee_id = NULL;
  END IF;
END;
EOT;

$createTriggerResult = $mysqliConn->query($query);

Delete A Trigger

DROP TRIGGER [trigger name]

Table Size Analytics  

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.

If you want a quick way to find the tables with the most rows, you could run:

SELECT table_rows "Rows Count"
FROM information_schema.tables
WHERE table_schema="$DB_NAME" 
ORDER BY table_rows;

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`

If you want that in a much more easily readable form (when dealing with large tables), then use the FORMAT function to put commas in there:

SELECT FORMAT(count(*),0) FROM `table1`;

E.g.

+--------------------+
| FORMAT(count(*),0) |
+--------------------+
| 1,660,000          |
+--------------------+

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.

Inserting / Importing 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.

Import SQL File

mysql -u $USERNAME -p $DB_NAME < $FILENAME.sql

Cloning Data

The query below demonstrates cloning a set of rows in a table for another set that has a different column_c value (in this case 4697). This is useful if column_c is some sort of grouping identifier. The table doesn't have to be the same, you could tweak the query to copy data into a history table.

INSERT INTO `my_table` 
(
    `column_a`,
    `column_b`,
    `column_c` 
)
SELECT 
    `column_a`,
    `column_b`,
    4697 
FROM `my_table` WHERE `column_c` = 1405;

Updating Data  

Update Column To Be Sum Of Others In Same Row

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

Uniqueness Queries  

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`;

Remove Primary Key

This will remove the primary key constraint, but leave the column and its data intact.

ALTER TABLE `table_name` DROP PRIMARY KEY;

Change Column Definition

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

ALTER TABLE `table_name` 
MODIFY `column1_name` INT UNSIGNED NOT NULL,
MODIFY `column2_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

The following worked in MySQL 5.6:

ALTER TABLE `my_table_name` ALTER `my_column_name` DROP DEFAULT"

However, I also have a previous note:

To remove a default value, simply use the modify column command shown above in "change column definition", without having specified anything for DEFAULT.

... which may also be applicable.

Rename Table

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

Change Character Set

ALTER TABLE `my_table` CONVERT TO CHARACTER SET utf8;

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'), 1,0)) = 0;

Find Tables Without Primary Or Unique 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  

Convert to Unix Timestamp With UNIX_TIMESTAMP

Sometimes it's easier to have MySQL convert the timestamp field to a unix timestamp so that you can work with the integer values. E.g.

SELECT * FROM `users` WHERE UNIX_TIMESTAMP(`created_at`) >=  1618415226"

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';

Delete Items Older Than MAX_AGE

DELETE FROM `my_table` 
WHERE `timestamp_field` < (NOW() - INTERVAL $MAX_AGE SECOND);

SECOND can be replace with MINUTE, HOUR, etc

Working With Dates  

The example below selects from my_table where the my_date_field is any date in January 2017.

SELECT * FROM my_table WHERE YEAR(my_date_field) = 2017 AND MONTH(my_date_field) = 1

Working With JSON

If you want to "replace" (insert if not exist, change if does) data within a json field, then you can do so with JSON_MERGE_PATCH like this:

UPDATE `myTable` 
SET `my_json_field` = JSON_MERGE_PATCH(`my_json_field`, '{"prop1" : "value1", "prop2" : "value2" }') 
WHERE `x` = 'y' 

Misc  

Show Whitespace

select quote(`columnName`) from `tableName`;

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

• MySQL - Check Uniqueness of Columns Values
• Stack Overflow - MySQL Select Multiple VALUES
• The Data Charmer - Finding tables without primary keys
• Stack Overflow - MySQL: FULL OUTER JOIN - How do I merge one column?
• Stack Overflow - Select mySQL based only on month and year
• Stack Overflow - How to change the default charset of a MySQL table?
• Stack Overflow - How to get the sizes of the tables of a mysql database?
• Stack Overflow - How to update JSON data type column in MySQL 5.7.10?
• Stack Overflow - Create mysql trigger via PHP?