Useful SQL Queries

Create USER in mysql:

mysql> CREATE USER 'exampledb'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.05 sec)

mysql> select User,Host from mysql.user;
+-----------+-----------+
| User | Host |
+-----------+-----------+
| exampledb | localhost |
| exampledb2 | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

Assigning Privileges:

mysql> GRANT CREATE,SELECT,INSERT,UPDATE,DELETE,DROP ON snappsapi.* TO 'exampledb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

To show the users in a MySQL database, first login as an administrative user, then run this query:

SELECT * from mysql.user;

Drop User in mysql:

mysql> drop user exampledb@localhost;
Query OK, 0 rows affected (0.00 sec)

To know Privileges in myslq

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uUSERNAME -pPASSWORD