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)
);