Grant all privileges on database in mysql

 Summary 

In the previous  article we have learned how to create a user in mysql and in this article we will learn how to grant all privileges on database in mysql.

Start to grant mysql privileges first we need to login our mysql server using the below command.

$mysql -u root -h localhost -p
Here -u switch indicates the user name and -h switch denotes the hostname and -p indicates the password. 

How to grant all  privileges on database 

We can restrict a user to operate  by grant all  privileges on database. Account privileges is very important task for a database admin to restrict a user to operate on database.Accidentally normal user can delete any records from the database. So a DBA should specify privileges to a normal user.

Case 1)  suppose we have a  normal database user techrideradmin and we want to give him  full privileges by which this user can do any operational tasks on all databases. For this we will use grant all privileges statement.operational task means he or she can do any DML,DDL operation like insert,update ,delete any records from any database. 
To  set privileges on database we need to execute the below command.

mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

In the above command first * denotes that  List of all databaes and the second * denotes list of all table name for all databases . we are using * that means this user has full privileges (can access all databases and tables).

Localhost identified by password means we create mysql user in local server and we set password using identified by statement.

After executing above command we need to execute flush privileges command.

mysql>FLUSH PRIVILEGES;

Case 2) suppose we have one database that is  techrideradmin and have one table on this database that is student . If we want to give permission for the particular database and table we need to execute the below command.

mysql>  grant all privileges on techrideradmin.student to 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.01 sec)

Here we have grant all privileges on database techrideradmin on student table for techrideradmin user.

Case 3) If we want to allow this user for the remote host we can use the below command. Suppose my server ip address 192.168.137.5 and my workstation ip address 192.168.137.1 . we want to  access the database using my workstation for do this we need to execute the below command.

mysql> grant all privileges on techrideradmin.student to 'techrideradmin'@'192.168.137.5' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

There are different type of privileges

1)ALL PRIVILEGES----Permit full permission to user.

mysql> grant all privileges on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant all privileges on database for all objects. 

2)CREATE--------------Give permission to create database and table.

mysql> grant create on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant create privileges to create database and table of the existing database.

3)SELECT--------------Give permission to view the details of the table data.

mysql> grant select  on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to create a readonly user in mysql and how to grant select privileges to view of all table data of any databases.

4)UPDATE--------------Give permission to update the data into the table

mysql> grant update on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant update privileges to update data into all tables of all databases.

5)INSERT---------------Give permission to insert data into table.

mysql> grant insert  on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant insert  privileges to insert data into all tables of all databases.

6)DELETE---------------Give permission to delete data from table.

mysql> grant delete on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant delete  privileges to delete data into all tables of all databases.

7)DROP-----------------Give permission to drop table and database.

mysql> grant drop on *.* to 'techrideradmin'@'localhost' identified by 'newpassword';

From the above query we have learned how to grant drop  privileges to drop any tables and databases.

Show grants for mysql user

Show grants command is used to find all provided grant information for a user.Here my username is techrideradmin and hostname is localhost so to view all grant info for that user using below command.

mysql> show grants for ‘techrideradmin’@’localhost’;

How to revoke privileges for MySQL user

If a user has leave from any organozation we need to revoke his or her privileges. To  revoke the privileges for the mysql user we need to execute the below command.

mysql> revoke all privileges on *.* from 'techrideradmin'@'localhost' identified by 'newpassword';

Query OK, 0 rows affected (0.00 sec)

After executing the above command all privileges has been revoked from this user.

Conclusion

In this article we have learned how to grant all privileges on database in mysql.

Read More»

How to use alter table in sql with query

 

SUMMARY

In this tutorial we will learn about alter table in sql. 

WHAT IS  ALTER TABLE

Generally alter table  is used to add column ,rename table ,drop column and modify data type of a column.Moreover any modification will be done after creating a table using alter table command.

PREREQUISITE 

First we need to  login our mysql server using the below command.

$mysql - u root -p

Now you need to create mysql database and table .Then check all database list and select our prefered database. To show database list we will use the below command.

mysql>show databases;

 you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select department table. Now fetch all records form the table.

HOW TO USE ALTER TABLE  WITH EXAMPLE

We have some queries about alter table in sql . These are given below.

1) Now we want to add city column into dept table ,we will  use alter table statement .

mysql>alter table dept add city varchar (30);

Here it was an example of alter table add column .you can add multiple columns using alter table statement.

2) Now we want to drop city column uisng alter table statement .

mysql>alter table dept drop column city;

Here it was an example of drop column using alter table statement. 

3) Now we want to rename dept table to department using alter table query.

mysql>alter table dept rename to department;

Using the above statement of alter table we can rename table name if it is required. 

4) Now we can change datatype of SAL column from int to varchar using alter table query.

mysql>alter table dept modify column SAL varchar (10);

From the above statement of alter table we can modify datatype of particular column.

CONCLUSION 

In this above article  we have learned about alter table in sql. 

Read More»

How to use in operator in sql with example

SUMMARY 

In this article we will learn about in operator and not in operator in sql. Generally to filter data from any table we use where clause along with condition.When we are using more than one value to filter data from the database table then in operator plays role to solve this issue and the query has become simple. 

PREREQUISITE 

First we need to  login our mysql server using the below command.

$mysql - u root -p

Now you need to create mysql database and table .Then check all database list and select our prefered database. To show database list we will use the below command.

mysql>show databases;

you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select dept table. Now fetch all records form the table.

WHAT IS IN  OPERATOR 

In case of in operator we will get those rows which we have provided  within the in values along with where statement. 

EXAMPLE OF  IN OPERATOR 

we will  fetch those records which  department nos belong to 1,3 and 5 using in operator. 

mysql > select * from dept where deptno in (1,3,5);

WHAT IS NOT  IN  OPERATOR

 In case of not in operator we will get those rows which is not provided within the not in operator.

EXAMPLE OF  NOT IN OPERATOR 

We will also fetch the records which dept nos are not belong to 1,3 and 5 using not in operator. 

mysql > select * from dept where deptno not  in (1,3,5);

Note: you can find the above video tutorial for reference.

CONCLUSION 

we have learned about in operator in sql  and not in operator in sql .

Read More»

How to use Like operator in sql

SUMMARY

In this video tutorial we will learn how to use  Like operator in sql with wildcard character .

WHAT IS LIKE OPERATOR IN SQL

We use like operator for pattern search.There are two wildcard character " % " and " _ ".% is used to find from zero to any no of character._ is used to find for only one character. 

 First we need to login our mysql server . Then check all database list and select our preferred database.you select your database as per your requirement. In our case we select techrideradmin database . Then select the table list within the database. In our case we select department table. Now fetch all records form the table.

EXAMPLE OF LIKE OPERATOR IN SQL

We have solved some queries . You can see the video for reference.

1)Fetch the records whose first letter of ename is "A". For that purpose we use % wildcard .

mysql>Select * from department where ename like 'A%';

2)Fetch the records whose first two letters of ename is "AS".For that purpose we use % wildcard .

mysql>Select * from department where ename like 'AS%';

3)Fetch the records whose last letter of ename is "F".For that purpose we use % wildcard .

mysql>Select * from department where ename like '%F';

4)For any particular letter within the word we use _ character.

mysql > Select * from department where ename like 'AS_K';

Conclusion 

In this video tutorial we have learned  how to use  Like operator in sql with wildcard character .

Read More»

Display records if first character of name is same as last character in mysql

 Summary:

In this tutorial we will learn display records if first character of name is same as last character in mysql and also display records if first character of the first name is same as first character of the last name. To solve this we create one table in mysql and start our demonstration.

Prerequisite:    

Now we are going to create one table named staff using the below query.

mysql> create table staff ( deptno int ,firstname varchar(20),lastname varchar(20),sal int);

Query OK, 0 rows affected (0.07 sec)

Now describe the staff table which we have just created.

Now we are going to insert some records into the staff table. To do this we need to execute the below query.

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'ram','verma',2000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'kartik','sharma',5000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'kartik','khan',9000);

Query OK, 1 row affected (0.00 sec)

mysql> insert into staff (deptno,firstname,lastname,sal) values ( 1, 'aftab','ali',9000);

Query OK, 1 row affected (0.00 sec)

Display records if first character of name is same as last character in mysql

To display first any  number of records we will use  left() function and to display last any number of records we will use right () function.

Now we will fetch all records from our table using the below query.

mysql> select * from staff;

Now we want to  display records if first character of name is same as last character in mysql using the below query.

mysql> select * from staff where left(firstname,1)=right(firstname,1);

Display records if first character of the first name is same as first character of the last name.

Now we are going to display records if first character of the first name is same as first character of the last name. To do this we need to execute the below query.

mysql> select * from staff where left(firstname,1)=left(lastname,1);

Conclusion 

In this tutorial we have learneddisplay records if first character of name is same as last character in mysql and also display records if first character of the first name is same as first character of the last name.

Read More»

How to select random values from a table and store it in another table in mysql

Summary

In this tutorial we will learn how to select random values from a table and store it in another table in mysql.

To retrieve random values from a table we use rand() function in mysql. rand() function fetches random values from a table. Lets take an example.

How to select random values from a table

We have one table called department. Now we want to retrieve all records randomly we use rand() function.

 mysql> select * from department order by rand() ;

Here order by clause is used to sort the randomly generated data.

Now we can place the limit to fetch particular no of rows like the below query;

mysql> select * from department order by rand() limit 3;

Here we place limit 3 to fetch 3 rows instead of 8 rows.

select random values from a table and store it in another table in mysql

Now we are going to store our randomly generated records to another table. we have department table from which we have fetched our random records. Now we will create another table named backupdept and in this table we are going to the fetched data.

Now to create table in mysql we need to execute the below query.

mysql> create table backupdept ( deptno int ,deptname varchar(20),ename varchar(20),sal int);

Query OK, 0 rows affected (0.03 sec)

To insert data from one table to another table we will use insert into select statement as below.

mysql> insert into backupdept (deptno,deptname,ename,sal) select  * from department order by rand() ;

Conclusion

In this tutorial we have learned how to select random values from a table and store it in another table in mysql.

Read More»