MySQL Cheatsheet

ALTER TABLE

ALTER TABLE table_name CHANGE column_name new_column_name int(11) NOT NULL DEFAUL 0;
ALTER TABLE table_name MODIFY column_name column_type;
ALTER TABLE table_name ADD column_name varchar(16) NOT NULL AFTER column_name2;
ALTER TABLE table_name ADD column_name varchar(32) AFTER column_name2
ALTER TABLE table_name ADD column_name varchar(32) FIRST
ALTER TABLE table_name DROP column_name;

Drop primary key:

ALTER TABLE table_name DROP PRIMARY KEY;

Create new primary key:

ALTER TABLE table_name ADD PRIMARY KEY(field_name);
if you want it to be auto_increment (you can’t make a non-primary-key field auto_increment):

ALTER TABLE table_name CHANGE field_name field_name mediumint unsigned not null auto_increment

Create new primary key made up by two fields:

ALTER TABLE table_name ADD PRIMARY KEY(field_name1, field_name2);

CREATE TABLE

CREATE TABLE student2 SELECT * FROM student WHERE class=’Four’
CREATE TABLE table_name1 LIKE database_name2.table_name2;

CREATE TABLE example_default_now (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, /* id related to the 'author' table */
data VARCHAR(100),
created TIMESTAMP DEFAULT NOW()
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT

INSERT INTO Table1 (column_1, column_2) SELECT column1, column2 FROM Table2

INSERT INTO table1 VALUES(1, “250,000″, “250000″);

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE

UPDATE table_name SET name=”ciao” WHERE id=1;

DELETE

DELETE table1.*

FROM table1

LEFT JOIN table2 ON table1.field1 = table2.field2

WHERE table2.field3 IS NULL

[like the corresponding SELECT query but you must specify the table from which you want to delete]

CREATING DUMP

mysqldump -uroot -p database_name > dump.sql
mysqldump -uroot -p database_name table_name > dump.sql
mysqldump -uroot -p –all-databases > dump.sql

mysqldump -d -uroot -p database_name > dump.sql oppure
mysqldump –no-data -uroot -p database_name > dump.sql
it outputs just the CREATE TABLE statements, very useful for diff

SELECT * INTO OUTFILE ‘file_name’ FROM table_name

DUPLICATE A ROW

INSERT INTO table1 (field1, field2, field3) SELECT field1, field2, field3 FROM table1 WHERE id=12;
INSERT A DUMP

use database_name [if the dump is about just a table]
source a.sql

Dropping (deleting) all the tables from a database

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

UTILITIES

DESC table_name;

show CREATE TABLE table_name; [more details than the previous one]
SHOW db_name [to check the tables]
select database() [to check the working database]

rehash [to let the consolle to autocomplete a brand new name of a table or a field]

SHOW processlist [show the list of all the processes running on the server]

GRANT

This way I create the new user and I give them the grant:

GRANT ALL ON database_name.* TO ‘db_user’@'db_host’ IDENTIFIED BY ‘user_password’; [mind the apostrophes]

CHEATS

ORDER BY rand() [random order]

INDEXES
CREATE INDEX index_name ON table_name(column_name)
DROP INDEX index_name ON table_name;

FULLTEXT INDEXES

ALTER TABLE table_name ADD FULLTEXT INDEX(column_name);
ALTER TABLE tableName DROP index `index_name_that_is_usually_the_column_name` ;

TROUBLESHOOTING / DEBUG
SHOW PROCESSLIST;
KILL

;

less “slow query log” (see my.cnf)