MySQL

[top] Datatypes 

INT            -2147483648 to 2137483647
TINYINT        -128 to 127
SMALLINT       -32768 to 32767
MEDIUMINT      0 to 16777215
BIGINT         -9223372036854775808 to 9223372036854775807
FLOAT(M,D)     M = display length including decimals (defaults to 10)
               D = decimal places (defaults to 2, max 24)
DOUBLE(M,D)    M = display length including decimals (defaults to 16)
               D = decimal places (defaults to 4, max 53)
DECIMAL(M,D)   M = display length including decimals (required)
               D = decimal places (required)
DATE           1000-01-01 to 9999-12-31 (YYYY-MM-DD)
DATETIME       1000-01-01 00:00:00 to 9999-12-31 23:59:59 (YYY-MM-DD HH:MM:SS)
TIMESTAMP      January 1, 1970 to 2037 (YYYYMMDDHHMMSS)
TIME           HH:MM:SS
YEAR(M)        M = 2 for 2 digit year, 4 for 4 digit year (08 or 2008)

CHAR(M)        M = 1 to 255 (Fixed length)
VARCHAR(M)     M = 1 to 25 (Variable length)
BLOB           max length 65535 characters
TINYBLOB       max length 255 characters
MEDIUMBLOB     max length 16777215 characters
LONGBLOB       max length 4294967295 characters
               case sensitive sorts and comparisions (not so with TEXT type)
TEXT           max length 65535 characters
TINYTEXT       max length 255 characters
MEDIUMTEXT     max length 16777215 characters
LONGTEXT       max length 4294967295 characters
ENUM           max list items 65535
Various datatypes.


[top] Functions 

length(string)
SELECT length("Hello world!"); => 14

concat (column1, column2)
SELECT concat(firstname, lastname) FROM names;

concat_ws('seperator', column1, column2);
SELECT concat_ws(' ', firstname, lastname) AS fullname FROM names;

rtrim(string)
SELECT rtrim('mystring   ');

ltrim(string)
SELECT ltrim('   mystring');

SELECT trim(leading 'X' from 'XXXhelloXXX'); => helloXXX
SELECT trim(trailing 'X' from 'XXXhelloXXX'); => XXXhello
SELECT trim('X' from 'XXXhelloXXX'); => hello

rpad(string, length, 'pad char')
SELECT rpad('mystring', 10, 'X'); => mystringXX

lpad(string, length, 'pad char')
SELECT lpad('mystring', 10, 'X'); => XXmystring

locate(string_to_find, string_to_search, postion_to_start)
SELECT locate('You are the sunshine of my life.', 'sunshine'); => 13 (not zero based)
SELECT locate('You are the sunshine of my life.', 'sunshine', 17); => 0 

SELECT substring("You are the sunshine of my life.', 5, 7); => "are the" (not zero based)
SELECT left("You are the sunshine of my life.', 3); => "You" (not zero based)
SELECT right("You are the sunshine of my life.', 5); => "life." (not zero based)

SELECT lcase("MYSQL"); => mysql
SELECT ucase("mysql"); => MYSQL
SELECT repeat("yo", 5); => yoyoyoyoyo
Various functions.


[top] mysql client 

MYSQL_PS1='mysql [\u@\d]>\_' => mysql [user@(database)]> 

shell> mysql -u [user] -p [password] [database]
mysql> connect [database];
mysql> desc [table];
MySQL client commands.


[top] mysqlshow 

shell> mysqlshow -u [user] -p [password] 			(show database names)
shell> mysqlshow -u [user] -p [password] [database] 		(show database table names)
shell> mysqlshow -u [user] -p [password] [database] [table] 	(show database table structure)
Display various database structures


[top] statements 

auto_increment id field
INSERT INTO table VALUES ('NULL', "item name', "item desc", "item price");
INSERT INTO table (item_name, item_desc, item_price) VALUES ("item name', "item desc", "item price");

limit
SELECT * FROM grocery_inventory LIMIT 0, 3;
- will only display the first three records
- LIMIT (starting point (zero based), number of records);

replace
replace into grocery_inventory VALUES (1, 'Granny Smith Apples', 'Sweet!', '0.50', 1000);
- will replace the values of an existing primary key
Various mysql statements.