Found 4219 Articles for MySQLi

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

Suppose if we have dumped multiple databases or all the databases and now want to restore it then we can do it with the following example −C:\mysql\bin>mysql -u root < tutorials_query1.sqlWith the help of above query, we are restoring the dumped multiple databases named ‘tutorials’ and ‘query1’, which are dumped in the file named ‘tutorials_query1.sql’. In this case, we do not need to write the name of the database.Similarly, with the help of the following query, we can restore all the databases dumped by mysqldump −C:\mysql\bin>mysql -u root < alldatabases.sqlWith the help of above query, we are restoring all the ... Read More

As all of these functions are used to return the position of a substring within a string but LOCATE() function is a bit different from POSITION() and INSTR() function. In both POSITION() AND INSTR() functions, we cannot manage the starting position of search with the help of argument as position argument in LOCATE() function. All of these functions are having a difference in syntax also.

Suppose if we have dumped the whole database and now want to restore it then we can do it with the following example −C:\mysql\bin>mysql -u root query < tutorials.sqlWith the help of above query, we are restoring the dumped database named ‘tutorials’, in the file tutorials.sql, into other database named ‘query’. It means that the tables of database ‘tutorials’ would be restored in a database named ‘query’.

By using mysql dump client program we can take the backup of all the databases into a file having the extension ‘.sql’. It can be understood with the help of the following example −ExampleIn this example, with the help of mysql dump client program, we are taking the backup all the databases in a file named ‘alldatabases.sql’. The following command will do this −C:\mysql\bin>mysqldump -u root --all-databases > alldatabases.sqlThe above command will create a file named alldatabases.sql which has the dump information of all the databases.

We need to pass the column name as the argument of OCTET_LENGTH() function to count the number of characters stored in a data column. It displays the number of characters when referenced in SELECT clause. It can also be used as comparison value to decide whether or not the row should returned by using it in WHERE clause. The contents of ‘Student’ table are used to demonstrate it −mysql> Select Name, OCTET_LENGTH(Name)As 'Str_Length' from Student; +---------+------------+ | Name    | Str_Length | +---------+------------+ | Gaurav  | 6          | | Aarav   | 5       ... Read More

MySQL ORD() function returns the code for the leftmost character if that character is a multi-byte i.e. sequence of one or more bytes, with the help of the following formula(1st bytecode) + (2nd bytecode * 256) + (3rd bytecode * 256^2)On the other hand, ASCII() function returns the ASCII value of the leftmost character of a given string.The difference between them lies on the point that whether the leftmost character is a multi-byte character or not. If it is not a multi-byte character then both ORD() and ASCII() functions return similar results. Following example will demonstrate it.mysql> Select ORD('Tutorialspoint'); +-----------------------+ ... Read More

By using mysql dump client program we can take the backup of a particular table from the databases into a file having the extension ‘.sql’. It can be understood with the help of the following example −ExampleIn this example, with the help of mysql dump client program, we are taking the backup of a table named ‘student_info’ from ‘query’ database in a file named ‘student_info.sql’. The following command will do this:C:\mysql\bin>mysqldump -u root query student_info > student_info.sqlThe above command will create a file named student_info.sql which has the dump information of a file named ‘student_info’ from database named ‘query’.Read More

Suppose if we want to restore a file that has been created by mysqldump then we can restore in an existing database or in a new database after creating it. Then with the help of SOURCE statement, we can restore it. We can illustrate it by an example:ExampleIn this example, we are restoring the table named student_info.sql which has been dumped. It was basically in the database name ‘query’. Now we will restore it into a database named ‘tutorials’.mysql> Use Tutorials; Database changed mysql> SOURCE student_info.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected ... Read More

We can check whether a string of specified pattern is not present within another string by using NOT LIKE operator along with wildcard characters.SyntaxNOT LIKE specific_patternSpecific_pattern is the pattern of string we do not want to find out within another string.ExampleSuppose we have a table named ‘student’ having names of the students and we want to get the details of all those students which are not having a pattern of string ‘av’ within their names. It can be done with the help of following MySQL query:mysql> Select * from Student WHERE name NOT LIKE '%av%'; +------+---------+---------+----------+--------------------+ | Id   ... Read More