MySQL Cheat Sheet

gistfile1

MySQL Cheat Sheet


Indexes:
- UNIQUE
- INDEX
- PRIMARY KEY
- FULLTEXT		(MyISAM engine only)

SHOW ENGINES					-> shows available storage engines and default storage engine
SHOW CHARACTER SET				-> shows available charachter sets
SHOW COLLATION LIKE 'charset%'	-> shows all collations starting with 'charset'
CONVERT()						-> converts text from one charset to another

CREATE DATABASE forum CHARACTER SET utf8 COLLATE utf8_general_ci; USE forum;

CHARSET utf8; -> changes to utf8 charset

CREATE TABLE users (
user_id MEDIUMINT UNISGNED NOT NULL,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(40) NOT NULL,
pass CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
INDEX reg_date,
PRIMARY KEY (user_id)
) ENGINE INNODB;


UTC_"funcnames"				-> various functions for working with UTC date and times
UTC_DATE()					-> returns current UTC date
UTC_TIME()					-> returns current UTC time
UTC_TIMESTAMP()				-> returns current date and time in UTC


- CONVERT_TZ(dt, from, to)	-> converts a date and time from one time zone to another
e.g.: CONVERT_TZ(date_utc, 'UTC', 'America/New York')


ALTER TABLE tablename CLAUSE:
- ALTER TABLE table ADD COLUMN col TYPE (BEFORE/AFTER)
- ALTER TABLE table CHANGE COLUMN col newcolname TYPE
- ALTER TABLE table DROP COLUMN col
- ALTER TABLE table ADD INDEX indexname colname
- ALTER TABLE table DROP INDEX indexname
- ALTER TABLE table RENAME TO newtable
- ALTER TABLE table CONVERT TO CHARACTER SET x COLLATE York


SHOW TABLE STATUS LIKE 'messages'(\G/;)


GRANT statement:
First: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT DELETE, INSERT, SELECT, UPDATE ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

SHOW GRANTS statement:
SHOW GRANTS FOR 'jeffrey'@'localhost';

REVOKE statement:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

Remember FLUSH PRIVILEGES after using GRANT or REVOKE


mysqldump for backup:
mysqldump -u root -pPassword --opt db table > table.sql
Restore:
mysql -u root -p < table.sql