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()
Read More
▲