Show Menu

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
ENUM One of preset options
SET 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 calculation 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-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 13 Members:

rd5coding pneumatix dttk andshecodes2 webdevo wdfelippe akipta chi_zach Dranzz alexikia Theosis caroline.artz Dexter

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.