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)