Mysql Cheatsheet

database
mysql
reference
The MySQL cheatsheet is a handy resource for quickly referencing various usage scenarios and essential tasks in database management, including creating tables, modifying table structure, deleting/retrieving data, working with constraints, and utilizing SQL functions.
Published

June 3, 2022

MySQL Reference Card

Version: 0.1

Author: ProgM4c

Attribute Types

Numbers

Name

Coded on

Name

Coded on

TINYINT

1 byte

FLOAT(W, D)

4 bytes

SMALLINT

2 bytes

DOUBLE(W, D)

8 bytes

MEDIUMINT

3 bytes

W: width(number of digits with the ‘.’)

D: number of decimals

INT

4 bytes

BIGINT

8 bytes

Parameters:

• UNSIGNED

• ZEROFILL

Coded on:

• SIGNED :

• UNSIGNED:

Strings (between ’ ’)

Name

Size

CHAR(M)

String with fixed size, 1 <= M <= 255

VARCHAR(M)

String with variable size, 1 <= M <= 255

TINYTEXT

Max length = 255

TEXT

Max length = 65535

MEDIUMTEXT

Max length = 16777215

LONGTEXT

Max length = 4294967295

DECIMAL(M, D)

Simulate a floating point number in a string format

Date and Time

Name

Format

DATE

AAAA-MM-JJ

DATETIME

AAAA-MM-JJ HH:MM:SS

TIMESTAMP

AAAAMMJJHHMMSS

TIMESTAMP(M)

First M characters of a TIMESTAMP

TIME

HH:MM:SS

YEAR

AAAA

ENUM: take one value in the defined list (can be NULL)

syntax:

attr_name ENUM(‘value1’, ‘value2’, …) {NULL | NOT NULL}

Database queries

create a database

CREATE DATABASE [IF NOT EXISTS] ;

delete a database

DROP DATABSE [IF EXISTS] ;

rename a database

ALTER DATABASE RENAME ;

list databases

SHOW DATABASES;

select a database

USE ;

Table queries

show a table

SHOW TABLES;

rename a table

ALTER TABLE RENAME ;

describe a table

DESCRIBE

;

delete a table

DROP TABLE ;

type of constraints

• NOT NULL

• UNIQUE

• PRIMARY KEY = NOT NULL + UNIQUE

• FOREIGN KEY

• CHECK

• DEFAULT

• AUTO_INCREMENT

create a table

CREATE TABLE (

(size) ,

(size) ,

…

PRIMARY KEY()

);

add / delete a constraints

ALTER TABLE ADD CONSTRAINT TYPEOFCONSTRAINT (, …)

ALTER TABLE DROP [CONSTRAINT | TYPEOFCONSTRAINT ];

Modify table structure

add / delete attribute

ALTER TABLE ADD [FIRST|AFTER ];

ALTER TABLE DROP ;

add / delete default value to an column

ALTER TABLE ALTER {SET DEFAULT |DROP DEFAULT};

change definition of an attribute without/with renaming it

ALTER TABLE MODIFY ;

ALTER TABLE CHANGE ;

Inserting data

INSERT INTO (, , …) VALUES (, , …);

Modifying data

UPDATE

SET = , = , …

WHERE ;

Deleting data

DELETE FROM WHERE ;

Retrieving data

Select statement

SELECT [ DISTINCT ] attributs

[ INTO OUTFILE fichier ]

[ FROM relation ]

[ WHERE condition ]

[ GROUP BY attributs [ ASC | DESC ] ]

[ HAVING condition ]

[ ORDER BY attributs ]

[ LIMIT [a,] b ]

operators in a where clause

=

Equal

<>

Not equal. Note: In some versions of SQL this operator may be written as !=

Greater than

<

Less than

=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

LIKE

Search for a pattern (‘%’ any sequence of characters ’_’ any character)

[NOT] IN

To specify multiple possible values for a column

IS [NOT] NULL

To check if the value of a column is NULL or not

AND OR NOT

Filter records based on more than once condition

Sub-requests

SELECT * FROM

WHERE prix > (SELECT MIN(prix) FROM tab2)

SELECT * FROM

WHERE nom NOT IN (SELECT nom FROM tab2)

SELECT * FROM

WHERE prix > ALL (SELECT prix FROM tab2) (sup. à ttes les valeurs)

SELECT * FROM

WHERE prix > ANY (SELECT prix FROM tab2) (sup. à au moins 1)

SQL aliases on column / table

SELECT AS FROM

(alias a result)

SELECT FROM

AS (alias a table name)

SQL functions

AVG() - (moyenne)

COUNT() - (nombre d’élément)

MAX() - (maximum)

MIN() - (minimum)

SUM() - (somme)

UCASE()

LCASE()

LEN()

NOW()

FORMAT()