MySQL User and Table Administration Guide

Objective:  use the MySQL client (aka monitor) for interaction with the MySQL
database management system. (Note: use semicolon as a statement terminator.)
0. Login as the root user:
   type:  mysql -u root;

1. Set the root password:
   type: SET PASSWORD FOR root@localhost = PASSWORD('password');
    
2. List current accounts:   
   SELECT host,user FROM mysql.user [WHERE password = ''];   

3. Drop anonymous accounts and assign passwords:
   DROP USER ''[@localhost]; 
   SET PASSWORD FOR userName = PASSWORD('password');
   Note syntax for a user name: username[@hostname]
   
4. Create a database for development. Type: 
   CREATE DATABASE databaseName;

5. Set up user account(s) to create and manipulate tables in the database
   created. Type
     CREATE USER userName [IDENTIFIED BY 'password'];
       
     GRANT privilegeList on {*|DbName}.{*|table} TO USER[IDENTIFIED by password]
       [with grant option];   
   
   Privilege list for working with data: SELECT, INSERT, UPDATE, DELETE, EXECUTE
   " for modifying the database structure: CREATE, ALTER, DROP, INDEX, 
         CREATE VIEWs, CREATE ROUTINE, ALTER ROUTINE, TRIGGER, EVENT

6. View privileges:
   SHOW GRANTS [FOR user];
   
7. Revoke privileges:
   REVOKE {ALL | privilegeList} [, GRANT OPTION] ON [dbName.]table
     FROM user1[, user2]... 
   
6. Rename a user:
   RENAME USER username TO newUserName;

7. Drop a user:
   DROP USER username;  
   
8. Logout: EXIT;

9. Login (to test login for new user):
   mysql -u userName -p [databaseName] 

10. Issue commands to show and use database  
    SHOW DATABASES; 
    USE DATABASE databaseName):

11. Verify privileges by entering SQL commands to create a table, insert data, 
    select, update, delete data ... from the table.

12. Do table maintenance:
    SHOW TABLES;
    DROP TABLE tableName;
    ALTER TABLE tableName RENAME TO newTableName:

13. Retrieve the column or field properties for a table: 
    DESCRIBE tableName; or SHOW CREATE TABLE tableName;

14. Modify a table attribute:
    ALTER TABLE tableName  MODIFY columnName columnType):