MySQL - Commonly used commands

Database Connection and Users

Connect to DB

mysql -h 'host_address' -u 'username' -p 'password';

Create users

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

P.S.
host - specify the user can log on which host, can be 'localhost' for local users.
        if want the user can log on from any remote host, can use wildcards '%'

Grant

GRANT privileges ON databasename.tablename TO 'username'@'host';

P.S.
privileges - could be SELECT, INSERT, UPDATE or ALL
databasename | tablename - could be '*.*' for all databases and tables

Set or Modify user password

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

or for currently login user:

SET PASSWORD = PASSWORD("newpassword");

example:

SET PASSWORD FOR 'lin'@'%' = PASSWORD("123456");

Revoke user privileges

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

to get detailed infomation, we can use:

SHOW GRANTS FOR 'username'@'host';

Delete users

DROP USER 'username'@'host';

Database CRUD statement

for DB

show databases;
create database 'dbname';
drop database 'dbname';
use 'dbname';

P.S.
DROP DATABASE - deletel all db & tables, and return the amount of files deleted (usually it's 3 times more than the amount of tables since there are '.MYD', '.MYI' and '.frm' files for each table)

for table

show tables; (should use 'use dbname' first)
desc 'tablename';
create table 'tablename';
drop table 'tablename';

P.S.
show tables like '%some_keywords_you_are_sure%';
show create table 'tablename';

Empty table

truncate table 'tablename';

Alter table name and column

alter column name

alter table 'tablename' change 'column_old' 'column_new';

alter table name

alter table 'tablename_old' rename 'tablename_new';

P.S.
alter table 'tablename' change 'field' 'field_type' [null or not]
alter table 'tablename' modify 'field' 'field_type' [null or not]

example:
alter table expert_info change birth varchar(20) null;

Alter table data

add new column

alter table 'tablename' add column 'column_name' type default value;

alter field name

alter table 'tablename' change 'column_name_src' 'column_name_dst' type 'default_value';

P.S.
alter table Board_Info change IsMobile IsTelphone int(3) unsigned default 1;

alter field default value

alter table 'tablename' alter 'column_name' set default 'value';

P.S.
alter table book alter flag set default 0;

alter field type

alter table 'tablename' change column 'column_name' 'type';

P.S.
alter table userinfo change column username varchar(20);

update field value

P.S.
update read_article set original_url_hash='71349' where id=269;

insert records

P.S.
insert into books_pub (name, address, city) values ('michael', '250 stress', 'nanjing');

Query

1. where
2. having
3. group by
4. order by
5. limit

query distinct records

SELECT DISTINCT 'fieldname' FROM 'tablename' WHERE 'query_statement';

query duplicate records

P.S.
SELECT name, age, count(*) FROM tb_stu WHERE age = '19' group by date;