It can be understood with the help of following the example in which we have updated the table named ‘Student’, having the following data, by using prepared statement −mysql> Select * from Student; +------+-------+ | Id   | Name  | +------+-------+ | 1    | Ram   | | 2    | Shyam | | 3    | Mohan | +------+-------+ 3 rows in set (0.00 sec) mysql> PREPARE stmt11 FROM 'UPDATE Student SET Name = ? WHERE Id = ?'; Query OK, 0 rows affected (0.03 sec) Statement prepared mysql> SET @A = 'Sohan', @B = 3; ... Read More

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 More

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 More

In this case, MySQL will not pad anything and truncate the characters from the original string up to the value of length provided as the argument in LPAD() or RPAD() functions.Examplemysql> Select LPAD('ABCD',3,'*'); +--------------------+ | LPAD('ABCD',3,'*') | +--------------------+ | ABC                | +--------------------+ 1 row in set (0.00 sec) mysql> Select RPAD('ABCD',3,'*'); +--------------------+ | RPAD('ABCD',3,'*') | +--------------------+ | ABC                | +--------------------+ 1 row in set (0.00 sec)We can observe from the above example that both the functions do not pad ‘*’ and truncate the original string up to the length specified i.e. 3 as the argument.

Suppose if we want to make the output of REPEAT() function more readable then we can use another function/s with it. For example, if we want to add space or some other character between the repeated values then we can use CONCAT() function.Examplemysql> Select REPEAT(CONCAT(' *', Subject, '* '), 3)AS Subject_repetition from student; +-----------------------------------------+ | Subject_repetition                      | +-----------------------------------------+ | *Computers* *Computers* *Computers*     | | *History* *History* *History*           | | *Commerce* *Commerce* *Commerce*        | | *Computers* *Computers* *Computers*     | ... Read More

For repeating the values stored in a data column of MySQL table, the name of the column must be passed as the first argument of REPEAT() function. The data from ‘Student’ table is used to demonstrate it:Examplemysql> Select REPEAT(Name,3)AS Name from student; +-----------------------+ | Name                  | +-----------------------+ | GauravGauravGaurav    | | AaravAaravAarav       | | HarshitHarshitHarshit | | GauravGauravGaurav    | | YashrajYashrajYashraj | +-----------------------+ 5 rows in set (0.00 sec)

MySQL server supports prepared statements, which are useful when we want to run many queries that differ only in very small details. We can prepare a statement and then execute it multiple times and each time with different data values. Basically, prepared statements in MySQL take advantage of client/server binary protocol. Prepared statements provide enhanced performance because the complete statement is parsed only one by the server.Followings are the steps for using prepared statements in MySQL −Prepare the statement It is the first step in which we will prepare a statement by using PREPARE statement. For example, following is a statement ... Read More

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 More

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 More

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 More