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
-
Economics & Finance
Database Articles
Page 278 of 547
How 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 | | ...
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 MoreHow can I check the list of MySQL tables, in the current database we are using, along with table type in the result set?
It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLESExampleIn the following example our current database is ‘query’ hence the statement below will show us the table list along with table type in the result set from this database −mysql> SHOW FULL TABLES; +-----------------------------+------------+ | Tables_in_query | Table_type | +-----------------------------+------------+ | accounts | BASE TABLE | | address | BASE TABLE | | cars ...
Read MoreWhich MySQL query can be used with the help of which we can see the list of MySQL databases?
With the help of following MySQL query, we can see the list of MySQL database −mysql> SELECT schema_name FROM information_schema.schemata; +--------------------+ | schema_name | +--------------------+ | information_schema | | gaurav | | mysql | | performance_schema | | query | | query1 | | sys | | tutorials | +--------------------+ 8 rows in set (0.00 sec)We can also use WHERE clause with this query as follows −mysql> SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%schema' OR schema_name LIKE '%s'; +--------------------+ | schema_name | +--------------------+ | information_schema | | performance_schema | | sys | | tutorials | +--------------------+ 4 rows in set (0.00 sec)
Read MoreWhat is the use of CHECK TABLE statement in maintaining the MySQL tables?
There may be something wrong which can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case, it can be crashed.With the help of CHECK TABLE statement MySQL allows us to check the integrity of database tables. Its syntax would be as follows −CHECK TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> CHECK table student_info\G *************************** 1. row *************************** Table: query.student_info ...
Read MoreWhich MySQL function is used to find first non-NULL value from a list of values?
We can use MySQL COALESCE() function to get the first non-NULL value as output from a list of values. In other words, this function will check all the values until non-null value found. It can take one or more than one argument. It is having the following syntax:COALESCE(value1, value2, …, valueN)ExampleFollowing is an example to demonstrate it −mysql> Select COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL); +--------------------------------------------------+ | COALESCE(NULL, NULL, NULL, 'Ram', 'Aarav', NULL) | +--------------------------------------------------+ | Ram | +--------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow MySQL evaluates an empty hexadecimal value?
Actually, MySQL evaluates an empty hexadecimal value to a zero-length binary string. It can be demonstrated as follows −mysql> Select CHARSET(X''); +--------------+ | CHARSET(X'') | +--------------+ | binary | +--------------+ 1 row in set (0.00 sec)The above result set shows that the empty hexadecimal value is a binary string. And the result set below shows that it is of length 0.mysql> Select LENGTH(X''); +-------------+ | LENGTH(X'') | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)
Read More