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
Articles on Trending Technologies
Technical articles with clear explanations and examples
What happens if I will prepare the statement with the same name without de-allocating the earlier one?
Actually, in MySQL, we can prepare a statement with the same name without de-allocating the earlier one because MySQL automatically drops the prepared statements when they are redefined or when we close the connection to the server. In other words, we can say that we can use the same name for prepared statements without de-allocating them explicitly. But, to free the memory on the server side we must have to de-allocate them. It can be done with the help of DEALLOCATE statement as follows −DEALLOCATE PREPARE statement;Here statement is the name of the prepared statements.DROP PREPARE statements is the synonym ...
Read MoreWhat kind of SQL statements can be used to prepare statements?
Actually, it is not possible to prepare all SQL statements because MySQL only allows the following kinds of SQL statements that can be prepared:SELECT statementsExamplemysql> PREPARE stmt FROM 'SELECT tender_value from Tender WHERE Companyname = ?'; Query OK, 0 rows affected (0.09 sec) Statement prepared mysql> SET @A = 'Singla Group.'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE stmt using @A; +--------------+ | tender_value | +--------------+ | 220.255997 | +--------------+ 1 row in set (0.07 sec) mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)INSERT, REPLACE, UPDATE and DELETEstatements that modify the ...
Read MoreHow Are MySQL INSTR() and LIKE operator similar?
We can use both INSTR() function and LIKE operator to search or match a particular pattern and they return same result. It can be demonstrated from the following example of ‘Student’ table.ExampleSuppose we want to search name, which contains ‘av’ in it, from ‘Student’ table. We can use INSTR() function as follows −mysql> Select Name from student where INSTR(name, 'av') > 0; +--------+ | Name | +--------+ | Gaurav | | Aarav | | Gaurav | +--------+ 3 rows in set (0.00 sec)Now, for the same kind of search we can use LIKE operator as follows −mysql> Select Name ...
Read MoreHow can I create a table and insert values in that table using prepare statements?
It can be understood with the help of following the example in which we have created the table named ‘Student’ by using prepared statement −mysql> PREPARE stmt3 FROM 'CREATE TABLE Student(Id INT, Name Varchar(20))'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE stmt3; Query OK, 0 rows affected (0.73 sec) mysql> DEALLOCATE PREPARE stmt3; Query OK, 0 rows affected (0.00 sec)Now, with the help of following queries using prepared statements, we can insert the valuesin table ‘Student’ −mysql> PREPARE stmt7 FROM 'INSERT INTO Student(Id, Name) values(?, ?)'; Query OK, 0 rows affected (0.00 sec) Statement ...
Read MoreHow to use REPLACE() function with column's data of MySQL table?
For using it with column’s data we need to provide column name as the argument of REPLACE() function. It can be demonstrated by using ‘Student’ table data as follows −Examplemysql> Select Id, Name, Subject, REPLACE(Subject, 's', ' Science') from Student WHERE Subject = 'Computers'; +------+--------+-----------+-----------------------------------+ | Id | Name | Subject | REPLACE(Subject, 's', ' Science') | +------+--------+-----------+-----------------------------------+ | 1 | Gaurav | Computers | Computer Science | | 20 | Gaurav | Computers | Computer Science | +------+--------+-----------+-----------------------------------+ 2 rows in set (0.00 sec)
Read MoreWhat MySQL would return if we refer a user variable which is not assigned any value explicitly?
In case, when we refer a user variable which is not assigned any value explicitly, MySQL would return NULL. In other words, its value would be NULL. Following example would illustrate it −mysql> Select @X, @Y, @Z, @S, @G; +------+-------+----------+------+------+ | @X | @Y | @Z | @S | @G | +------+-------+----------+------+------+ | Ram | Shyam | Students | 5000 | NULL | +------+-------+----------+------+------+ 1 row in set (0.00 sec)We can see from the above result set that @X, @Y, @Z and @S has been assigned values explicitly and they returned the values ...
Read MoreHow to capture multiple matches in the same line in Java regex
Exampleimport java.util.regex.*; class PatternMatcher { public static void main(String args[]) { int count = 0; // String to be scanned to find the pattern. String content = "aaa bb aaa"; String string = "aaa"; // Create a Pattern object Pattern p = Pattern.compile(string); // get a matcher object Matcher m = p.matcher(content); while(m.find()) { count++; System.out.println("Match no:"+count); ...
Read MoreWhat happens if I will assign a value to a MySQL user variable using a statement that returns multiple rows?
In case, if we will assign a value to a user variable using a statement that returns multiple rows then the value from the last row would be saved in that user variable because user variables can save the only single value. Following the example, in which we are using data from table ‘Tender’, will exhibit it −Examplemysql> select * from Tender; +----+---------------+--------------+ | Sr | CompanyName | Tender_value | +----+---------------+--------------+ | 1 | Abc Corp. | 250.369003 | | 2 | Khaitan Corp. | 265.588989 | | 3 | Singla group. | 220.255997 ...
Read MoreHow can we use SET statement to assign a SELECT result to a MySQL user variable?
For using the SET statement to assign a SELECT result to a user variable we need to write the SELECT statement as a subquery within parentheses. The condition is that the SELECT statement must have to return a single value. To make it understand we are using the data from ‘Tender’ table which is as follows −mysql> select * from Tender; +----+---------------+--------------+ | Sr | CompanyName | Tender_value | +----+---------------+--------------+ | 1 | Abc Corp. | 250.369003 | | 2 | Khaitan Corp. | 265.588989 | | 3 | Singla group. | 220.255997 | ...
Read MoreHow does MySQL QUOTE() function work with comparison values?
When QUOTE() function used with WHERE clause then the output depends upon the comparison values returned by WHERE clause. Following example will exhibit it −Examplemysql> Select Name, ID, QUOTE(Subject)AS Subject from Student WHERE Subject = 'History'; +-------+------+-----------+ | Name | ID | Subject | +-------+------+-----------+ | Aarav | 2 | 'History' | +-------+------+-----------+ 1 row in set (0.00 sec)
Read More