Coding Knowledge Center
MySQL
| Administration (export db) |
> mysqldump -u [user_name] -p[password] --database [database_name] >/tmp/databasename.sql
Export DB to a temporary file.
Specify the database administrator's user name, password, and the name of the database.
| Administration (import db) |
mysql> source /tmp/databasename.sql
OR
> mysql -u [user_name] -p[password] [database_name] < /tmp/databasename.sql
There are two ways to import a database.
One method is to login to the mysql server and import from there. The other method is from the command line.
| 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.
| 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.
| 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.
| 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
| SQL (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 SQL statements.