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 |
RSS Feed
@
+