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
MySQLi Articles
Page 85 of 341
What kind of settings can we do to a text file by query while exporting the values from MySQL table into a text file?
While exporting the data from MySQL table to a text file we can use ‘FIELDS TERMINATED BY’, ‘ENCLOSED BY’, ‘LINES TERMINATED BY’ and other options too to put the values of fields in different settings of the text file. It can be illustrated with the help of the following example −ExampleSuppose we are having following data from table ‘Student_info’ −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | ...
Read MoreHow can we import the text file, having some line prefixes, into MySQL table?
Suppose if we have a line prefix in the text file then with the help of using ‘LINES STARTING BY’ option we can ignore that prefix and import correct data into MySQL table. It can be understood with the help of the following example −ExampleSuppose we are using ‘VALUE’ as the ‘LINE PREFIX’ in the text file as follows −id, Name, Country, Salary VALUE:105, Chum*, Marsh, USA, 11000 106, Danny*, Harrison, AUS, 12000Now while importing this text file into MySQL table then we ...
Read MoreHow can we MySQL LOAD DATA INFILE statement with 'ENCLOSED BY' option to import data from text file into MySQL table?
Sometimes the input text files have the text fields enclosed by double quotes and to import data from such kind of files we need to use the ‘ENCLOSED BY’ option with LOAD DATA INFILE statement. We are considering the following example to make it understand −ExampleFollowings are the comma-separated values in A.txt file −100, ”Ram”, ”INDIA”, 25000 101, ”Mohan”, ”INDIA”, 28000We want to import this data into the following file named employee2_tbl −mysql> Create table employee2_tbl(Id Int, Name Varchar(20), Country Varchar(20), Salary Int); Query OK, 0 rows affected (0.1 sec)Now, the transfer of data from a file to a ...
Read MoreHow can we transfer information between MySQL and data files?
Transferring the information between MySQL and data files mean importing data from data files into our database or exporting data from our database into files. MySQL is having two statements that can be used to import or export data between MySQL and data files −LOAD DATA INFILEThis statement is used for importing the data from data files into our database. It reads data records directly from a file and inserts them into a table. Its syntax would be as follows −SyntaxLOAD DATA LOCAL INFILE '[path/][file_name]' INTO TABLE [table_name ];Here, the path is the address of the file.file_name is the name ...
Read MoreHow can we store a value in user-defined variable?
We can store a value in a user-defined variable in a statement and then refer to it afterward in other statements. Followings are the ways to store a value in user-defined variable −With SET statementwe can store a user-defined variable by issuing a SET statement as follows −SyntaxSET @var_name = expr[, @var_name = expr]…In this @var_name is the variable name which consists of alphanumeric characters from current character set. We can use either = or := assignment operator with SET statement.For example following queries can store the user variables with SET statement −mysql> SET @value = 500; Query OK, 0 ...
Read MoreWhat happens with the output of MySQL EXPORT_SET() function if I will skip the value of the fifth argument i.e. a number of bits?
Actually, the default value of the fifth argument i.e. number of bits is 64, hence if we will not specify any value on fifth argument them MySQL will check the bits up to 64 bits and produce the result. It can be understood from the following example −Examplemysql> SELECT EXPORT_SET(5, 'Y', 'N', ' ')\G *************************** 1. row *************************** EXPORT_SET(5, 'Y', 'N', ' '): Y N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N ...
Read MoreHow MySQL FIELD() and ELT() functions are complements of each other?
On the basis of the working of both the functions, we can say that both are the complement of each other. Actually, as we know that FIELD() function, on providing a string as an argument, returns the index number of the string from string list and ELT() function, on providing index number as an argument, returns the string from string list. In the following example, we have applied both the functions on the same string, it would demonstrate the concept −Examplemysql> SELECT ELT(4, 'Ram', 'is', 'good', 'boy')As Result; +--------+ | Result | +--------+ | boy | +--------+ ...
Read MoreWhat would be the output of MySQL ELT() function if the index number, provided as an argument, is not an integer?
As we know the 1st argument of ELT() function must be an integer value but when we provide index number which is not an integer the MySQL ELT() function returns NULL with a warning.Examplemysql> select ELT('one','Ram,is,good,boy')As Result; +--------+ | Result | +--------+ | NULL | +--------+ 1 row in set, 1 warning (0.00 sec) mysql> Show Warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: 'one' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec)
Read MoreIn MySQL, how FIELD() function is different from FIND_IN_SET() function?
As we know, both the functions are used to search a string from the arguments provided in them but there are some significant differences between them as follows −FIND_IN_SET() − function uses the string list that is itself a string containing the substring separated by commas. Whereas, FIELD() function contains list of different strings among which it will find the index number of the string, if present, which is to be searched.FIND_IN_SET() − function returns NULL if any of the argument i.e. either search string or string list is NULL. In contrast, FIELD() function do not returns NULL but returns ...
Read MoreWhat MySQL ELT() function returns if the index number, provided as an argument, is higher than the number of strings?
MySQL ELT() function returns NULL as output if the index number provided as argument is higher than the number of strings. Following is an example to make it clearer −Examplemysql> Select ELT(6,'Ram','is','a','good','boy')As Result; +--------+ | Result | +--------+ | NULL | +--------+ 1 row in set (0.00 sec)As we can see that index number is 6 and the list of strings is having only 5 strings. Hence MySQL returns NULL.
Read More