MySQL Reference

This is a short list of MySQL statements that may or may not be useful to you. I’m putting these things here so I do not have to google for them every time I forget the exact syntax.

MySQL Meta Commands (self descriptive I think):

SHOW DATABASES
USE DATABASE
SHOW TABLES
DESCRIBE TABLE

Creating Tables:

CREATE TABLE table_name (
	column1 TYPE(SIZE) NOT NULL AUTO_INCREMENT, 
	column2 TYPE(SIZE), 
	PRIMARY KEY(column1));

Creating an Innodb table with foreign key

CREATE TABLE table_name_2 (
	column1 TYPE(SIZE) NOT NULL AUTO_INCREMENT, 
	column2 TYPE(SIZE),
 	column3 TYPE(SIZE),
 	KEY `col3` (column3),
	PRIMARY KEY(column1)
	CONSTRAINT `fk_col3` FOREIGN KEY (`column3`) REFERENCES `table_name_1` (`column1`) ON UPDATE CASCADE ON DELETE DO NOTHING
) TYPE=InnoDB;

Adding an index to a table:

ALTER TABLE TABLE ADD INDEX(COLUMN);
CREATE INDEX INDEX ON TABLE (col1, col2, col3);

Querying:

SELECT * FROM TABLE WHERE col1='something' GROUP BY col2 ORDER BY col3

Joins:

SELECT * FROM tab1 JOIN tab2 ON tab1.col1=tab2.col2 WHERE [condition];

you can also have LEFT JOIN and RIGHT JOIN.

Inserting a Row:

INSERT INTO TABLE (col1, col2, col3) VALUES ('aaa, 'bbb', NULL);

Modifying Rows:

UPDATE TABLE SET COLUMN = "value";

Modifying Columns:

ALTER TABLE TABLE MODIFY COLUMN TYPE(SIZE) NOT NULL;

Renaming Columns:

ALTER TABLE TABLE CHANGE column_old column_new TYPE(SIZE);

Inserting a new column:

ALTER TABLE TABLE ADD COLUMN col2 TYPE(SIZE) AFTER col1;

Dropping a column:

ALTER TABLE TABLE DROP COLUMN col;

Loading data from a tab delimited file:

LOAD DATA 
	LOCAL INFILE "infile.txt" 
	INTO TABLE TABLE 
	FIELDS TERMINATED BY ',' 
	LINES TERMINATED BY '\n' 
	(col1, col2, col3);

Note that under windows line terminator is ‘\r\t’.

Dump a table into a csv file:

SELECT * INTO OUTFILE 'result.csv' 
	FIELDS TERMINATED BY ',' 
	OPTIONALLY ENCLOSED BY '"' 
	LINES TERMINATED BY '\n' 
	FROM mytable;

Executing a SQL file:

mysql -u user -p < file.sql

Database Dump:

mysqldump --opt -u username -p database > backup.sql
mysqldump --opt -u username -p --all-databases > backup.sql

Granting Privileges to Users:

GRANT ALL ON TABLE TO user@host
GRANT ALL ON DATABASE.* TO user@host
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE TO user@host

Most common privileges: SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, FILE (controls the select into outfile and load data infile), INDEX and GRANT OPTION

Revoking Privileges:

REVOKE ALL ON TABLE FROM user@host
REVOKE ALL ON DATABASE.* FROM user@hosr
REVOKE SELECT, INSERT, UPDATE, SELETE ON TABLE FROM user@host