Util MySQL commands

msql-basic-commands.sh

##################
## command line ##
##################

# run mysql;
mysql -h[host] -u[user] [database]

# dump database;
mysqldump -uroot [database] > database.sql

# restore database;
mysql -uroot [database] < database.sql

##################
## inside mysql ##
##################

# change between databases;
> use [database];

# show tables;
> show tables [like '[value]'];

# select all items from a table;
> select * from [table];

# select specific columns from a table;
> select [column, column ...] from [table];

# see columns and data type of a table;
> describe [table];

# insert data;
> insert into [table] ([column], [column] ...) values([value], [value] ...);

# update field;
update [table] set [field] = [value] where [condition];

# delete row;
> delete from [table] where [column]=[value];

# create new row;
> alter table [table] add column [column] [type];

# join common results from 2 tables;
> select [table.1column, table2.column ...] from [table1] inner join [table2] on table2.id = table1.id;

# change column name;
> alter table [table] change [old column] [new column] [type];

# create column;
> alter table [table] add [column] [type];
> alter table [table] add [column] [type] primary key;
> alter table [table] add [column] [type] auto_increment;

# remove column;
> alter table [table] drop [column];

# create new table;
> create table [table] ([column] [type], [column] [type] ...) [like [other table]];

# duplicate table;
> create table [table] select * from [old-table] [condition];

# rename table;
> rename table [old name] to [new name];

# delete table / database;
> drop [table | database];

# change stdout
> pager 'mate';

# send output as lines instead of columns;
> select * from [table] \G;

######################
## config at my.cnf ##
######################
~/my.cnf

# set UTF8 as default charset
[client] 
default-character-set=utf8 

[mysqld] 
character-set-server = utf8 
collation-server = utf8_general_ci 

[mysql] 
default-character-set=utf8 

The files are as .sh just to colorize the comments.