Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Database Articles
Page 167 of 546
How can we set up a MySQL User account by using SQL GRANT statement?
We can also add user account by using GRANT SQL command. It can be illustrated by using the following example −ExampleIn this example, we will add user Zara with password zara123 for a particular database,which is named as TUTORIALS.root@host# mysql -u root -p password; Enter password:******* mysql> use mysql; Database changed mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON TUTORIALS.* -> TO 'zara'@'localhost' -> IDENTIFIED BY 'zara123';The above statements will also create an entry in the MySQL database table called as a user.
Read MoreHow can we use MySQL POWER() function with the column’s data values?
If we want to use POWER() function with column’s data values then the first argument i.e. the base would be the name of the column and the second argument i.e. the exponent would be as specified by us. To understand it considers a table ‘Employee’ having the following records −mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | ...
Read MoreHow can I get the list of columns from a table in the database we are currently using?
It can be done with the SHOW COLUMNS statement. Its Syntax would be as follows −SyntaxSHOW COLUMNS FROM tab_nameHere tab_name is the name of the table from which we want to see the list of columns.ExampleIn the example we are getting the list of columns from a table named Student_info −mysql> SHOW COLUMNS FROM Student_info\G *************************** 1. row *************************** Field: studentid Type: int(11) Null: YES Key: Default: NULL Extra: *************************** 2. row *************************** Field: Name Type: varchar(40) Null: YES Key: Default: NULL Extra: *************************** 3. row *************************** ...
Read MoreHow Can MySQL operator precedence affect result set?
MySQL follows operator precedence and it has the following list of operators, having the same precedence which is on the same line −INTERVAL BINARY, COLLATE ! - (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + & | =, , >=, >,
Read MoreHow can we stuff a string with another one using MySQL functions?
MySQL have two functions namely LPAD() and RPAD() with the help of which we can stuff a string with another string.LPAD() function, as the name suggests, left stuff a string with another string. Following is the syntax for using it in MySQL −SyntaxLPAD(original_string, @length, pad_string)Here, original_string is the string in which we stuff another string.@length is the total length of string returned after stuffing.Pad_string is the string which is to be stuffed with original_string.Examplemysql> SELECT LPAD('tutorialspoint', 18, 'www.'); +----------------------------------+ | LPAD('tutorialspoint', 18, 'www.') | +----------------------------------+ | www.tutorialspoint | +----------------------------------+ 1 row in set ...
Read MoreHow can we check for NULL in a MySQL query?
With the help of IS NULL operator, we can check for NULL in a MySQL query. We cannot use = (comparison operator) because as we know that NULL is not a value. Following example using the data from ‘employee’ table will exhibit it −Examplemysql> Select * from Employee WHERE Salary IS NULL; +----+-------+--------+ | ID | Name | Salary | +----+-------+--------+ | 7 | Aryan | NULL | | 8 | Vinay | NULL | +----+-------+--------+ 2 rows in set (0.00 sec)The query above use IS NULL operator and produces the output where salary column is having NULL.mysql> ...
Read MoreHow can I use RAND() function in an ORDER BY clause to shuffle MySQL set of rows?
When we use MySQL ORDER BY clause with RAND() function then the result set would have the shuffled set of rows. In other words, the result set would be in a random order. To understand it considers a table ‘Employee’ having the following records −mysql> Select * from employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | | 6 | Mohan | ...
Read MoreHow can we generate the same sequence of random numbers in MySQL?
When invoked with an integer argument, RAND( ) uses that value to seed the random number generator. Each time you seed the generator with a given value, RAND( ) will produce the same sequence of random numbers. Following example will demonstrate it −Examplemysql> Select RAND(1), RAND(1), Rand(1); +---------------------+---------------------+---------------------+ | RAND(1) | RAND(1) | Rand(1) | +---------------------+---------------------+---------------------+ | 0.40540353712197724 | 0.40540353712197724 | 0.40540353712197724 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
Read MoreHow can I use IFNULL() function at the place of COALESCE() function in MySQL?
As we know that IFNULL() function will return the first argument if it is not NULL otherwise it returns the second argument. On the other hand, COALESCE() function will return first non-NULL argument. Actually, both IFNULL() and COALESCE() functions in MySQL works equivalently if the number of arguments is two only. The reason behind this is that IFNULL() function accepts only two arguments and in contrast, COALESCSE() function can accept any number of arguments.Suppose if we want to use IFNULL() function at the place of COALESCE() function then the number of arguments must be two. Following example will demonstrate it ...
Read MoreHow can I insert a value in a column at the place of NULL using MySQL COALESCE() function?
To understand it, we are using the data from the table ‘Employee’ having Salary=NULL for ID = 5 and 6, as follows −mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | NULL | | 6 | Mohan | NULL | +----+--------+--------+ 6 rows in set (0.00 sec)Now, the following queries will use COALESCE() function along with UPDATE and ...
Read More