Integer (-9223372036854775808 to 9223372036854775807)
FLOAT
Decimal (precise to 23 digits)
DOUBLE
Decimal (24 to 53 digits)
DECIMAL
"DOUBLE" stored as string
DATE
YYYY-MM-DD
DATETIME
YYYY-MM-DD HH:MM:SS
TIMESTAMP
YYYYMMDDHHMMSS
TIME
HH:MM:SS
ENUM
One of preset options
SET
Selection of preset options
Integers (marked x) that are "UNSIGNED" 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 ("val1", "val2");
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 column1="val1" 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 ‘regular_expression’
Joins
INNER JOIN
returns only where match in both tables
OUTER JOIN
also returns non-matching records from both tables
LEFT JOIN
also returns non-matching records from left table
RIGHT JOIN
also returns non-matching records in right table
JOIN syntax:
SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id;
String functions mySQL
Compare strings
STRCMP("str1","str2")
Convert to lower case
LOWER("str")
Convert to upper case
UPPER("str")
Left trim
LTRIM("str")
Substring of a string
SUBSTRING("str","inx1","inx2")
Concatenate
CONCAT("str1","str2")
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_INCREMENT,
column VARCHAR(2),
column VARCHAR(32),
PRIMARY KEY (`id`)
);
Comments
| 
Add a Comment
| 
PDF Download
| 
Find:
Favourited by 11 Members:
Comments
No comments yet. Add yours below!
Add a Comment
You are posting a reply. Cancel Reply.
Contents
Cheatographer
More by guslong
Cheat Sheet Stats
Tags
Related (shares tags with):
Thumbnail