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
SHOW USERS
USE my_database
SHOW TABLES
DESCRIBE my_table

Creating Tables:

CREATE TABLE table_name (
	column1 INT(32) NOT NULL AUTO_INCREMENT, 
	column2 VARCHAR(16),
 	column3 DATETIME,
	PRIMARY KEY(column1));

Creating an Innodb table with foreign key

CREATE TABLE table_name_2 (
	column1 INT(32) NOT NULL AUTO_INCREMENT, 
	column2 VARCHAR(16),
 	column3 DATETIME,
 	KEY `my_key_name` (column3),
	PRIMARY KEY(column1)
	CONSTRAINT `foreign_key_name` FOREIGN KEY (`column3`) 
		REFERENCES `my_table_name` (`column1`) 
		ON UPDATE CASCADE ON DELETE DO NOTHING
) TYPE=InnoDB;

Adding an index to a table:

ALTER TABLE my_table ADD INDEX(my_column);
CREATE INDEX my_index ON my_table (col1, col2, col3);

Querying:

SELECT * FROM my_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 use LEFT JOIN and RIGHT JOIN.

Inserting a Row:

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

Modifying Rows:

UPDATE my_table SET my_column = "value";

Modifying Columns:

ALTER TABLE my_table MODIFY my_column TYPE(SIZE) NOT NULL;

Renaming Columns:

ALTER TABLE my_table CHANGE column_old column_new TYPE(SIZE);

Inserting a new column:

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

Dropping a column:

ALTER TABLE my_table DROP COLUMN col;

Loading data from a tab delimited file:

LOAD DATA 
	LOCAL INFILE "infile.txt" 
	INTO TABLE my_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 my_table;

Executing a SQL file:

mysql -u user -p < file.sql

Database Dump:

mysqldump --opt -u username -p my_database > my_backup.sql
mysqldump --opt -u username -p --all-databases > my_backup.sql

Granting Privileges to Users:

GRANT ALL ON my_table TO some_user@host
GRANT ALL ON my_database.* TO some_user@host
GRANT SELECT, INSERT, UPDATE, DELETE ON my_table TO some_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 my_table FROM some_user@host
REVOKE ALL ON my_database.* FROM some_user@hosr
REVOKE SELECT, INSERT, UPDATE, DELETE ON my_table FROM some_user@host

Case Statement (simple):

select column1, column2,
	CASE column3
		WHEN 1 THEN "Low"
		WHEN 2 THEN "Medium"
		WHEN 3 THEN "High"
		ELSE "Unknown"
	END AS my_status
FROM my_table

Case Statement (multi-value):

select
	CASE
		WHEN col1 > 20 THEN "WARNING"
		WHEN col2 < 0 THEN "CAUTION"
		WHEN col3 = 'err' THEN "ERROR"
		ELSE "OK"
	END AS my_status
FROM my_table