Tuesday, 27 August 2013

MySQL Query plus Grant Privileges

MySQL Query Tutorial is about basic queries which require in day to day work. This tutorial also show you how to create user, Grant Privileges and Show Privileges of specific user. it also show hot to connect it from PHP Page on same Network. Before reading this Tutorial you must have installed XAMPP, Apache and MySQL must be Running.

  1. Open your browser and go to the link PHP Myadmin
  2. Now Type database Name in Field "Create new database" and Press Create. (For Example Database Name is Search)
  3. Now Choose Database search from Drop Down Menu and click on SQL Tab on right Side.
  4. Write Below query in SQL Box and Press GO for creating your first MySQL Table.
    • CREATE TABLE IF NOT EXISTS `country` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) )
  5. Its time to add some values in country Table. clcik on SQL Tab and write following INSERT Query to add some countries.
    • INSERT INTO `country` (`name`) VALUES ( 'Afghanistan'), ( 'Africa'), ( 'Albania'), ( 'Algeria'), ( 'Andorra'), ( 'Canada');
  6. For View click on SQL Tab and Write Below Query.
    • SELECT * FROM `country`
  7. For Update click SQL Tab and write below Query.
    • UPDATE `country` SET `name` = 'Australia' WHERE `name` = 'Afghanistan';
  8. Now if you want to grant this database access to someone else you have to write below SQL Query in SQL Box.
    • CREATE USER 'username'@'%' IDENTIFIED BY 'passwordforuser'
  9. To Grant SELECT option to specific user write below code
    • GRANT SELECT ON search.country TO 'username'@'%'
  10. To Grant UPDATE option to specific user write below code
    • GRANT UPDATE ON search.country TO 'username'@'%'
  11. To Grant INSERT option to specific user write below code
    • GRANT INSERT ON search.country TO 'username'@'%'
  12. You can check Grant option for any user by writing below code.
    • SHOW GRANTS FOR 'username'@'%';
Above tutorial Reference is from MYSQL Reference Manual.
Website Link for this tutorial is Crewow website