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.