Document for simple sql works

sql.md

SQL

MySQL

$ mysql -u root -p

Dump

Import

$ mysqldump -u root -p --routines [dbname] > [filename].sql;

$ mysqldump -u root -p --routines --all-databases > [filename].sql;

Export

$ mysql -u root -p [dbname] < [filename].sql;

$ mysql -u root -p < [filename].sql;

Database

Check

mysql> show databases;

Create

mysql> create database [dbname];

Delete

mysql> drop database [dbname];

User

Check

mysql> use mysql; mysql> select user, host from user;

Create

mysql> create user userid@localhost identified by '[password]';

mysql> create user '[userid]'@'%' identified by '[password]';

Delete

mysql> drop user [userid];

mysql> delete from user where user = '[userid]';

Privileges

Check

mysql> show grants for [userid]@[host];

Grant

mysql> grant all privileges on [dbname].[tablename] to [userid]@[host] identified by '[password]';

Revoke

mysql> revoke all on [dbname].[tablename] from [userid]@[host];

Flush

mysql> flush privileges;