Show Menu
Cheatography
  1. Home >
  2. Programming >

Essential MySQL Cheat Sheet by

Essential MySQL

MySQL Data Types

CHAR
String (0 - 255)
VARCHAR
String (0 - 255)
TINYTEXT
String (0 - 255)
TEXT
String (0 - 65535)
BLOB
String (0 - 65535)
MEDIUMTEXT
String (0 - 16777215)
MEDIUMBLOB
String (0 - 16777215)
LONGTEXT
String (0 - 429496­7295)
LONGBLOB
String (0 - 429496­7295)
TINYINT x
Integer (-128 to 127)
SMALLINT x
Integer (-32768 to 32767)
MEDIUMINT x
Integer (-8388608 to 8388607)
INT x
Integer (-2147­483648 to 214748­3647)
BIGINT x
Integer (-9223­372­036­854­775808 to 922337­203­685­477­5807)
FLOAT
Decimal (precise to 23 digits)
DOUBLE
Decimal (24 to 53 digits)
DECIMAL
"­DOU­BLE­" stored as string
DATE
YYYY-MM-DD
DATETIME
YYYY-MM-DD HH:MM:SS
TIMESTAMP
YYYYMM­DDH­HMMSS
TIME
HH:MM:SS
One of preset options
Selection 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 JOIN
returns only where match in both tables
OUTER JOIN
also returns non-ma­tching records from both tables
LEFT JOIN
also returns non-ma­tching records from left table
RIGHT JOIN
also 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 strings
STRCMP­("st­r1",­"­str­2")
Convert to lower case
LOWER(­"­str­")
Convert to upper case
UPPER(­"­str­")
Left trim
LTRIM(­"­str­")
Substring of a string
SUBSTR­ING­("st­r","i­nx1­"­,"in­x2")
Concat­enate
CONCAT­("st­r1",­"­str­2")

MySQL calcul­ation functions

Count rows
COUNT(col)
Average
AVG(col)
Minimum value
MIN(col)
Maximum value
MAX(col)
Sum of values
SUM(col)

Create table with auto-i­ncr­eme­nting primary key

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

Metadata

  • Languages:
  • Published: 13th August, 2012
  • Last Updated: 12th May, 2016
  • Rated: 5 stars based on 46 ratings

Favourited By

rce343 lek jeaninem71 jackynkc dusanch dgbirm spegusess TechsmithTN eGoddess technix64 saito200
and 35 more ...

Comments

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

Reviewed a few and liked the layout of this one. seemed to be geared to the data that i need to remind myself as a new user.

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          PHP Syntax for beginners Cheat Sheet
          sqlite3 Cheat Sheet

          More Cheat Sheets by guslong

          VBA for Excel Cheat Sheet
          PHP Syntax for beginners Cheat Sheet