MySQL / MariaDB / Cheatsheet, referencing the most common SQL commands and statements

Insight Jun 07, 2020
MySQL / MariaDB / Cheatsheet, referencing the most common SQL commands and statements

A SQL cheat sheet referencing the most commonly used SQL commands and statements.

List of common MySQL / MariaDB Commands

-- List of all MySQL / MariaDB commands:
-- Note that all text commands must be first on line and end with ';'
-- ?         (\?) Synonym for `help'.
-- clear     (\c) Clear the current input statement.
-- connect   (\r) Reconnect to the server. Optional arguments are db and host.
-- delimiter (\d) Set statement delimiter.
-- edit      (\e) Edit command with $EDITOR.
-- ego       (\G) Send command to mysql server, display result vertically.
-- exit      (\q) Exit mysql. Same as quit.
-- go        (\g) Send command to mysql server.
-- help      (\h) Display this help.
-- nopager   (\n) Disable pager, print to stdout.
-- notee     (\t) Don't write into outfile.
-- pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
-- print     (\p) Print current command.
-- prompt    (\R) Change your mysql prompt.
-- quit      (\q) Quit mysql.
-- rehash    (\#) Rebuild completion hash.
-- source    (\.) Execute an SQL script file. Takes a file name as an argument.
-- status    (\s) Get status information from the server.
-- system    (\!) Execute a system shell command.
-- tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
-- use       (\u) Use another database. Takes database name as argument.
-- charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
-- warnings  (\W) Show warnings after every statement.
-- nowarning (\w) Don't show warnings after every statement.

Browse database

-- Browse as mysql user
SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table_name;
SHOW CREATE TABLE table_name;
SHOW PROCESSLIST;
KILL process_number;
# Browse directly from terminal
mysqlshow my_db_name table_name;

Confirm current database user

USE my_db_name;
SELECT USER(); 

Create / Delete / Use database

-- Create database OR check if already exists and then create
-- [IF NOT EXISTS] is used to prevent an error from occurring if the database does exist.

CREATE DATABASE my_db_name;
CREATE DATABASE IF NOT EXISTS my_db_name;

-- Create database with character set and collation
CREATE DATABASE my_db_name CHARACTER SET UTF8mb4;
CREATE DATABASE my_db_name CHARACTER SET UTF8mb4 collate utf8mb4_general_ci;

-- Drop database OR only drop database if exists
-- [IF EXISTS] is used to prevent an error from occurring if the database does not exist.
DROP DATABASE my_db_name;
DROP DATABASE IF EXISTS my_db_name;

-- Use database
USE my_db_name;

Users and Privileges

-- Grant select privileges
GRANT ALL PRIVILEGES ON my_db_name.* TO 'my_user_name'@'localhost' IDENTIFIED BY 'my_password';

-- Grant selective privileges
GRANT SELECT, INSERT, DELETE ON my_db_name.* TO 'my_user_name'@'localhost' IDENTIFIED BY 'my_password';

-- Revoke one privileges only
REVOKE ALL PRIVILEGES ON my_db_name.* FROM 'my_user_name'@'host';

-- Revoke all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'my_user_name'@'host';
-- Passwords
SET PASSWORD = PASSWORD('my_new_password')
SET PASSWORD FOR 'user'@'host' = PASSWORD('my_new_password')
SET PASSWORD = OLD_PASSWORD('my_new_password')
-- Drop database user
DROP USER 'user_name'@'localhost'

Show database character set and collation

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

Alter database character set and collation

-- Set database charset and collation
ALTER DATABASE my_db_name CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER DATABASE my_db_name CHARACTER SET UTF8mb4 collate utf8mb4_bin;
ALTER DATABASE my_db_name CHARACTER SET UTF8mb4 collate utf8mb4_unicode_ci;

Field Data Types

MySQL/MariaDB data types can be categorised as numeric, date and time, and string values.

Numeric types

  • TINYINT − This data type represents small integers falling within the signed range of -128 to 127, and the unsigned range of 0 to 255.
  • BOOLEAN − This data type associates a value 0 with “false,” and a value 1 with “true.”
  • SMALLINT − This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.
  • MEDIUMINT − This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.
  • INT(also INTEGER) − This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.
  • BIGINT − This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.
  • DECIMAL( also DEC, NUMERIC, FIXED) − This data type represents precise fixed-point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.
  • FLOAT − This data type represents a small, floating-point number of the value 0 or a number within the following ranges −
    -3.402823466E+38 to -1.175494351E-38
    1.175494351E-38 to 3.402823466E+38
  • DOUBLE (also REAL and DOUBLE PRECISION) − This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges −
    -1.7976931348623157E+308 to -2.2250738585072014E-308
    2.2250738585072014E-308 to 1.7976931348623157E+308
  • BIT − This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be applied with “ b’[value]’” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

  • DATE − This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.
  • TIME − This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”
  • DATETIME − This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.
  • TIMESTAMP − This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detailing the time of database modifications, e.g., insertion or update.
  • YEAR − This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String Data Types

The string type values supported by MySQL/MariaDB are as follows:

  • String literals − This data type represents character sequences enclosed by quotes.
  • CHAR − This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.
  • VARCHAR − This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.
  • BINARY − This data type represents binary byte strings, with M as the column length in bytes.
  • VARBINARY − This data type represents binary byte strings of variable length, with M as column length.
  • TINYBLOB − This data type represents a blob column with a maximum length of 255 (28 - 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.
  • BLOB − This data type represents a blob column with a maximum length of 65,535 (216 - 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.
  • MEDIUMBLOB − This data type represents a blob column with a maximum length of 16,777,215 (224 - 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.
  • LONGBLOB − This data type represents a blob column with a maximum length of 4,294,967,295(232 - 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.
  • TINYTEXT − This data type represents a text column with a maximum length of 255 (28 - 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.
    TEXT − This data type represents a text column with a maximum length of 65,535 (216 - 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.
  • MEDIUMTEXT − This data type represents a text column with a maximum length of 16,777,215 (224 - 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.
  • LONGTEXT − This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (232 - 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.
  • ENUM − This data type represents a string object having only a single value from a list.
  • SET − This data type represents a string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.

Field Options

Unsigned

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

Zerofull

When you select a column with type ZEROFILL it pads the displayed value of the field with zeros up to the display width specified in the column definition. Values longer than the display width are not truncated. Note that usage of ZEROFILL also implies UNSIGNED.

Binary

The BINARY type is similar to the CHAR type, but stores binary byte strings rather than non-binary character strings. M represents the column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.

Allow Null

Allows you to specify whether your fields are allowed to be NULL . If you specify "Allow Null", then it will be possible to create records with NULL values in those fields.

Tables

Create / Delete / Modify tables

CREATE TABLE `table_name` (`table_name_id` int(11) NOT NULL, `field1` varchar(255) NOT NULL, `field2` varchar(30)  NULL);
-- Example 1
CREATE TABLE `about_me` (
  `about_me_id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phone_number` char(11) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` char(1) NOT NULL   
) ;
-- Example 2 setting primary key and default charset with table desc
CREATE TABLE `table_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `phone_number` char(11) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` char(1) NOT NULL, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='I am describing this table';

Select

SELECT * FROM table_name;
SELECT * FROM table1, table2, ...;
SELECT field1, field2, ... FROM table1, table2, ...;
SELECT ... FROM ... WHERE condition;
SELECT ... FROM ... WHERE condition GROUPBY field;
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...;
SELECT DISTINCT field1, field2 FROM ...;

Insert

INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...)

Database Management

Repair tables after unclean shutdown

mysqlcheck --all-databases
mysqlcheck --all-databases --fast

Backup and restore

# Backup
mysqldump -u MyUsername -p my_db_name > my_db_name_backup.sql
# Restore from terminal
mysql - u Username -p my_new_db_name < my_db_name_backup.sql

Glen Smale

Glen Smale is a FullStack Software Engineer & CTO @StaffCluster with 15yrs tech industry experience. He is a freelance technology Journalist and writer @Bundupress.

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.