-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';

--  How to create a user on localhost.
create user [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];
create user ramaswamy@localhost identified by "mypassword";

-- How to create a user for an IP address other than localhost.
create user [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];
create user ramaswamy@192.168.0.105 identified by "mypassword";

-- How to grant permission to a user to select only from localhost.
grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];
grant select on posts.* to ramaswamy@localhost identified by 'mypassword';
flush privileges;

-- How to grant a user permission to create, insert, update, delete and create temporary tables from localhost.
grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];
grant select, create, insert, update, delete, create temporary tables 
on posts.* to amit@localhost identified by 'mypassword';
flush privileges;

-- How to grant a user permission to create, insert, update, delete and create temporary tables from any host.
grant [<permission_1,permission_2,...permission_n>|<all>] 
on [database].[<table_name>|<all_tables(*)>] 
to [user name]@[<localhost>|<IP address>|<any host('%')>] 
identified by ["password"];
grant select, create, insert, update, delete, create temporary tables on posts.* 
to amit@'%' identified by 'mypassword';
flush privileges;
-- How to grant a user permission to select only from any host but to a specific table of a database.
grant select on posts.url_master to jhon@'%' identified by 'mypassword';
flush privileges;

-- How to grant all privileges to a user from all machines.
grant all on posts.* to joy@'%' identified by 'mypassword';
flush privileges;

-- How to revoke all privileges from a user.
revoke all privileges on posts.* from joy@'%';
flush privileges;

-- 10. How to check permissions granted to a specific user.
show grants for amit;

-- How to check the list of system privileges that the MySQL server supports.
show privileges;

-- How to Grant permission to a user so that (s)he can execute not more than a specific number of queries in an hour.
Create user steffi@localhost identified by 'mypassword';
grant SELECT on posts.* to steffi@localhost identified by'my password
with MAX_QUERIES_PER HOUR 50; flush privileges;

-- 
set password for steffi@localhost = password('mypassword123');

-- How to delete user.
drop user steffi@'localhost';

-- How to rename a user.
rename user amit@local host to sumit@localhost;

-- How to create a user and granting no privileges
grant usage on posts.* to boris@localhost identified by 'mypassword';