Essential Mysql Cheat Sheet
ADVERTISEMENT
1
2Essential MySQL Cheat Sheet
by
guslong
via
MySQL Data Types
Select queries
Creating and modifying (cont)
CHAR
String (0 - 255)
select all columns
return only 1 row matching query
VARCHAR
String (0 - 255)
SELECT * FROM tbl;
... LIMIT = 1
TINYTEXT
String (0 - 255)
select some columns
amend the values of a column
TEXT
String (0 - 65535)
SELECT col1, col2 FROM tbl;
UPDATE table SET column 1 ="v a l1 "
WHERE ...
BLOB
String (0 - 65535)
select only unique records
MEDIUMTEXT
String (0 - 16777215)
clear all the values, leaving the table structure
SELECT DISTINCT FROM tbl WHERE
MEDIUMBLOB
String (0 - 16777215)
condition;
TRUNCATE TABLE tbl;
LONGTEXT
String (0 - 429496 7 295)
column alias with AS
delete the table
LONGBLOB
String (0 - 429496 7 295)
SELECT col FROM tbl AS newname;
DROP TABLE tbl;
TINYINT x
Integer (-128 to 127)
order results
delete the database
SMALLINT x
Integer (-32768 to 32767)
SELECT * FROM tbl ORDER BY col [ASC |
DROP DATABASE db_name;
MEDIUMINT x
Integer (-8388608 to
DESC];
8388607)
Matching data
group results
INT x
Integer (-2147 4 83648 to
SELECT col1, SUM(col2) FROM tbl
matching data using LIKE
214748 3 647)
GROUP BY col1;
SELECT * FROM tbl1 WHERE col LIKE
BIGINT x
Integer (-
‘%value%’
9223 3 72 0 36 8 54 7 75808 to
Creating and modifying
922337 2 03 6 85 4 77 5 807)
matching data using REGEX
create a database
FLOAT
Decimal (precise to 23 digits)
SELECT * FROM tbl1 WHERE col RLIKE
CREATE DATABASE db_name;
‘regul a r_ e xp r es s ion’
DOUBLE
Decimal (24 to 53 digits)
select a database
DECIMAL
" D OU B LE " stored as string
Joins
USE db_name;
DATE
YYYY-MM-DD
INNER
returns only where match in both
DATETIME
YYYY-MM-DD HH:MM:SS
list the databases on the server
JOIN
tables
TIMESTAMP
YYYYMM D DH H MMSS
SHOW DATABASES;
OUTER
also returns non-ma t ching records
TIME
HH:MM:SS
show a table's fields
JOIN
from both tables
ENUM
One of preset options
DESCRIBE tbl;
LEFT
also returns non-ma t ching records
SET
Selection of preset options
JOIN
from left table
create a new table
RIGHT
also returns non-ma t ching records
Integers (marked x) that are " U NS I GN E D" have
CREATE TABLE tbl (field1, field2);
JOIN
in right table
the same range of values but start from 0 (i.e.,
insert data into a table
an UNSIGNED TINYINT can have any value
JOIN syntax:
INSERT INTO tbl VALUES ("va l 1", " v al 2 ");
from 0 to 255).
delete a row
SELECT * FROM tbl1 INNER JOIN tbl2 ON
tbl1.id = tbl2.id;
DELETE * FROM tbl WHERE condition;
add a column from a table
String functions mySQL
ALTER TABLE tbl ADD COLUMN col;
Compare strings
STRCMP ( "st r 1", " s tr 2 ")
remove a column from a table
Convert to lower case
LOWER( " s tr " )
ALTER TABLE tbl DROP COLUMN col;
Convert to upper
UPPER( " s tr " )
make a column a primary key
case
ALTER TABLE tbl ADD PRIMARY KEY
Left trim
LTRIM( " s tr " )
(col);
By guslong
Published 13th August, 2012.
Sponsored by
Last updated 29th June, 2014.
Learn to solve cryptic crosswords!
Page 1 of 2.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education