- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
![Ayyan](https://www.tutorialspoint.com/assets/profiles/13521/profile/60_79767-1512650417.jpg)
166 Views
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)
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
1K+ Views
If all the values in MySQL COALESCE() function are NULL then it returns NULL as the output. It means that this function does not find any non-NULL value in the list.Examplemysql> Select COALESCE(NULL, NULL, NULL, NULL); +----------------------------------+ | COALESCE(NULL, NULL, NULL, NULL) | +----------------------------------+ | NULL | +----------------------------------+ 1 row in set (0.00 sec)
![Jennifer Nicholas](https://www.tutorialspoint.com/assets/profiles/13569/profile/60_88876-1512715262.jpg)
93 Views
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 More
![Sai Subramanyam](https://www.tutorialspoint.com/assets/profiles/13512/profile/60_68191-1512710774.jpg)
56 Views
We can use MySQL absolute value i.e. ABS() function to select records from a table if the absolute value of the difference between two values is greater than a certain number. We are using the data from ‘Marks’ table to demonstrate it for example. Example mysql> Select * from studentmarks where abs(Hindi-English)>10; +--------+-------+---------+------+---------+-----------+ | Name | Hindi | English | Math | Physics | Chemistry | +--------+-------+---------+------+---------+-----------+ | Gaurav | 75 | 86 | 95 | 69 | 85 ... Read More
![Nikitha N](https://www.tutorialspoint.com/assets/profiles/13558/profile/60_76068-1512713229.jpg)
140 Views
While working with the database, we have a tendency to do plenty of changes like insert, update and delete data within the table which will cause the physical storage of the table fragment. As a result, the performance of database server is degraded.MySQL provides us with OPTIMIZE TABLE statement that allows you to optimize the table to avoid this defragmenting problem. Its syntax would be as follows −OPTIMIZE TABLE table_nameHere, table_name is the name of the table.ExampleWe are running this statement for the table Student_info as follows −mysql> Optimize table student_info\G *************************** 1. row *************************** Table: query.student_info Op: ... Read More
![Daniol Thomas](https://www.tutorialspoint.com/assets/profiles/13551/profile/60_130035-1512726078.jpg)
278 Views
MySQL query optimizer is an important element of the MySQL server that makes an best question execution set up for a query. For a particular query, the query optimizer uses the stored key distribution and other factors to decide the order in which tables should be joined when you performing the join, and which index should be used for a specific table.However, the key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete, or update. IIf the key distribution isn't correct, the question optimizer could pick a nasty ... Read More
![Rishi Raj](https://www.tutorialspoint.com/assets/profiles/13777/profile/60_73169-1512647303.jpg)
60 Views
As we know that an empty hexadecimal value is a zero-length binary string hence if 0 is added to it then the result would be 0. In other words, we can say that if we convert an empty hexadecimal value to a number then it produces 0. The following query will make it understand −mysql> SELECT X''+ 0; +--------+ | X''+ 0 | +--------+ | 0 | +--------+ 1 row in set (0.15 sec)
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
162 Views
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)
![Alankritha Ammu](https://www.tutorialspoint.com/assets/profiles/13523/profile/60_83090-1512649277.jpg)
95 Views
MySQL returns NULL if we provide any non-hexadecimal number as an argument to UNHEX() function. Following example will demonstrate it.Examplemysql> Select UNHEX('ANK96598'); +-------------------+ | UNHEX('ANK96598') | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec)As we know that the valid hexadecimal digits are between ‘0…9’, ‘A…F’ or ‘a…f’ hence the above query returns NULL.
![Akshaya Akki](https://www.tutorialspoint.com/assets/profiles/13526/profile/60_48621-1512703980.jpg)
1K+ Views
MySQL TRIM() function is used to remove all the suffixes or prefixes or both from the string. The working of TRIM() function can be understood with the help of its syntax −SyntaxTRIM([{BOTH | LEADING | TRAILING} [str_to_remove] FROM] string)Here, the argument BOTH means the prefixes from both left and right to be removed from the string.LEADING argument means that only leading prefixes to be removed.TRAILING argument means that only trailing prefixes to be removed.Str_to_remove is the argument which means the string we want to remove from the string.String argument means the string from which the prefixes have to be removed.Examplemysql> ... Read More