- 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 4219 Articles for MySQLi
65 Views
As we know that with the help of SHOW DATABASES statement we can see the list of MySQL databases. Similarly, we can use SHOW SCHEMAS as the synonym of SHOW DATABASES to get the list of databases.Examplemysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | gaurav | | mysql | | performance_schema | | query | | query1 | | sys | | tutorials | +--------------------+ 8 rows in set (0.07 sec) mysql> SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | gaurav | | mysql | | performance_schema | | query | | query1 | | sys | | tutorials | +--------------------+ 8 rows in set (0.00 sec)
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)
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)
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
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
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
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
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)
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)
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.