MySQL DML

1. to show current mysql version:

>show variables like '%version%';

 

2. create database or table with specified character set & collate:

>create database default character set utf8 collate utf8_general_ci;

>create table <table-name>(columns...)

  default character set utf8

  collate utf8_general_ci;

 

3. to check the system character set and collate:

>show character set like '%utf8%';

>show collate like '%utf8%';

 

4. startup / shutdown MySQL in command line:

>mysqld  --- startup

>mysqladmin -u root shutdown  -- shutdown

 

5. change user password:

>mysqladmin -u root password 123456   ---- first set

>mysqladmin -u root -p123456 password <new-password>  -- reset

 

6. execute sql file directly:

>mysql -uroot -p123456 < c:\abc.sql

 

7. create a user:

>grant all *.* to <user-name>@<host-name> identified by '<password>';