Cheatography
https://cheatography.com
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 - 4294967295) |
LONGBLOB |
String (0 - 4294967295) |
TINYINT x |
Integer (-128 to 127) |
SMALLINT x |
Integer (-32768 to 32767) |
MEDIUMINT x |
Integer (-8388608 to 8388607) |
INT x |
Integer (-2147483648 to 2147483647) |
BIGINT x |
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 |
|
One of preset options |
|
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
)
); |
|
Created By
Metadata
Favourited By
and 36 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
Related Cheat Sheets
More Cheat Sheets by guslong