Show Menu

Essential MySQL Cheat Sheet by guslong

Essential MySQL

MySQL Data Types

CHARString (0 - 255)
VARCHARString (0 - 255)
TINYTEXTString (0 - 255)
TEXTString (0 - 65535)
BLOBString (0 - 65535)
MEDIUMTEXTString (0 - 16777215)
MEDIUMBLOBString (0 - 16777215)
LONGTEXTString (0 - 429496­7295)
LONGBLOBString (0 - 429496­7295)
TINYINT xInteger (-128 to 127)
SMALLINT xInteger (-32768 to 32767)
MEDIUMINT xInteger (-8388608 to 8388607)
INT xInteger (-2147­483648 to 214748­3647)
BIGINT xInteger (-9223­372­036­854­775808 to 922337­203­685­477­5807)
FLOATDecimal (precise to 23 digits)
DOUBLEDecimal (24 to 53 digits)
DECIMAL"­DOU­BLE­" stored as string
DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MM:SS
TIMESTAMPYYYYMM­DDH­HMMSS
TIMEHH:MM:SS
ENUMOne of preset options
SETSelection of preset options

Integers (marked x) that are "­UNS­IGN­ED" have the same range of values but start from 0 (i.e., an UNSIGNED TINYINT can have any value from 0 to 255).

Select queries

select all columns
SELECT * FROM tbl;
select some columns
SELECT col1, col2 FROM tbl;
select only unique records
SELECT DISTINCT FROM tbl WHERE condition;
column alias with AS
SELECT col FROM tbl AS newname;
order results
SELECT * FROM tbl ORDER BY col [ASC | DESC];
group results
SELECT col1, SUM(col2) FROM tbl GROUP BY col1;
 

Creating and modifying

create a database
CREATE DATABASE db_name;
select a database
USE db_name;
list the databases on the server
SHOW DATABASES;
show a table's fields
DESCRIBE tbl;
create a new table
CREATE TABLE tbl (field1, field2);
insert data into a table
INSERT INTO tbl VALUES ("va­l1", "­val­2");
delete a row
DELETE * FROM tbl WHERE condition;
add a column from a table
ALTER TABLE tbl ADD COLUMN col;
remove a column from a table
ALTER TABLE tbl DROP COLUMN col;
make a column a primary key
ALTER TABLE tbl ADD PRIMARY KEY (col);
return only 1 row matching query
... LIMIT = 1
amend the values of a column
UPDATE table SET column­1="v­al1­" WHERE ...
clear all the values, leaving the table structure
TRUNCATE TABLE tbl;
delete the table
DROP TABLE tbl;
delete the database
DROP DATABASE db_name;

Matching data

matching data using LIKE
SELECT * FROM tbl1 WHERE col LIKE ‘%value%’
matching data using REGEX
SELECT * FROM tbl1 WHERE col RLIKE ‘regul­ar_­exp­res­sion’
 

Joins

INNER JOINreturns only where match in both tables
OUTER JOINalso returns non-ma­tching records from both tables
LEFT JOINalso returns non-ma­tching records from left table
RIGHT JOINalso returns non-ma­tching records in right table

JOIN syntax:

SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;

String functions mySQL

Compare stringsSTRCMP­("st­r1",­"­str­2")
Convert to lower caseLOWER(­"­str­")
Convert to upper caseUPPER(­"­str­")
Left trimLTRIM(­"­str­")
Substring of a stringSUBSTR­ING­("st­r","i­nx1­"­,"in­x2")
Concat­enateCONCAT­("st­r1",­"­str­2")

MySQL calculation functions

Count rowsCOUNT(col)
AverageAVG(col)
Minimum valueMIN(col)
Maximum valueMAX(col)
Sum of valuesSUM(col)

Create table with auto-incrementing primary key

CREATE TABLE table_name (
id INT AUTO_I­NCR­EMENT,
column VARCHA­R(2),
column VARCHA­R(32),
PRIMARY KEY (id)
);
 

Share This Cheat Sheet!

Favourited by 15 Members:

Qai88 sgmanivannan DeeJRoth Dexter caroline.artz Theosis alexikia Dranzz akipta wdfelippe webdevo and 5 more ...

Comments

PastExpiryDotCom PastExpiryDotCom, 16:35 12 Jul 13

Just starting to learn SQL on my own. This sheet will be invaluable!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.