MySQLi Articles - Page 384 of 422

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

With the help of MySQL REPEAT() function, we can replicate a string for a specified number of times.SyntaxREPEAT(Str, No.)Here Str is the string which is to be replicated for a specified number of times.No. is the numerical value which indicates that how many times the string would be repeated.Examplemysql> Select REPEAT('#*',5); +----------------+ | REPEAT('#*',5) | +----------------+ | #*#*#*#*#*     | +----------------+ 1 row in set (0.00 sec)

MySQL returns NULL if the argument of QUOTE() function is NULL.Examplemysql> Select QUOTE(NULL); +-------------+ | QUOTE(NULL) | +-------------+ | NULL         +-------------+ 1 row in set (0.00 sec) mysql> Select Name, QUOTE(NULL) from student where id = 1; +--------+-------------+ | Name   | QUOTE(NULL) | +--------+-------------+ | Gaurav | NULL        | +--------+-------------+ 1 row in set (0.08 sec)

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)

MySQL QUOTE() function can be used to append values of a column with single quotes. For this, we must have to pass column name as the argument of QUOTE() function. Data from ‘Student’ table is used to demonstrate it as follows Example mysql> Select Name, ID, QUOTE(Subject)AS Subject from Student; +---------+------+-------------+ | Name | ID | Subject | +---------+------+-------------+ | Gaurav | 1 | 'Computers' | | Aarav | 2 | 'History' | | Harshit | 15 ... Read More

If we want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name.Run the following command at the source host. This will dump the complete database into dump.txt file.$ mysqldump -u root -p database_name table_name > dump.txt password *****We can copy complete database without using a particular table name as explained above.Now, ftp dump.txt file on another host and use the following command. Before running this command, make sure we have created database_name on the destination server.$ mysql -u root -p database_name < dump.txt password *****Another way to accomplish this without ... Read More