Mysql cheatsheet

databases //show all databases Add column to table


ALTER table mytable ADD (columnname1 varchar(255) NOT NULL, columnname2 enum('Y','N') DEFAULT 'N' NOT NULL);

Create table with primary key


create table webuser_no_auto_approve(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, domain VARCHAR(255) NOT NULL) engine=innodb default charset=utf8 collate=utf8_unicode_ci;

check database size


SELECT  CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH РDATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM
INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%your_db%';

export database, or table


mysqldump -u user -h localhost -p databasename tablename > ~/database_or_table.sql

import database, or table



mysql -u user -h localhost -p databasename  < database.sql

create empty table structure from other database/table


create table mytable like database.othertable

insert data from different database/table //fields have to match


insert into newdb select * from otherdb.olddb

show character encoding for table


show create table whatever

create the user, and grant some priviledges on specific db. use '.' for all


grant select, insert update, delete on newdatabase to 'newuser' identified by 'pass';

Show last modification date


SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'somedb'
AND TABLE_NAME = 'sometable';

Duplicate a row


insert into new table select NULL, col1, col2 from newtable;

//null represents id pk. columns need to match To copy a column into the same table, first create the column to reflect the one you want to copy. Then


update table set newcolumn = oldcolumn

Rename a column


alter table resources change enddatenew enddate datetime;

Rename a table


alter table events rename to events_backup;

Fulltext search


alter table resources add FULLTEXT(description, short_description, title);

Insert into table only if it is not there yet


insert ignore into table(title) values("test");

update albums as a, artists as b SET a.rating=b.rating where a.artist_id=b.id;

Update a column of a table based on another one


insert ignore into table(title) values("test");

User Privileges

Check all users privileges


SELECT User from mysql.user;

Show privileges for user


SHOW GRANTS FOR 'user'@'localhost';

Grant privileges


GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON database.* TO username@localhost IDENTIFIED BY 'password';

Load CSV file


LOAD DATA LOCAL INFILE '/home/emile/Documents/bio.csv' INTO TABLE events FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (event_record_id, event_code, date, title, location, event_status, type);

Convert table to utf-8. Be careful


ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

June 16, 2011