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 110 of 341
How can we import data from a text file having names of the columns in first row?
Sometimes, the input text file has the names of the columns in the first row and to import data from such kind of text file to MySQL table we need to use ‘IGNORE ROWS’ option. To illustrate it we are using the following example −ExampleFollowings are the comma separated values in A.txt file −Id, Name, Country, Salary 100, ”Ram”, ”INDIA”, 25000 101, ”Mohan”, ”INDIA”, 28000We want to import this data into the following file named employee3_tbl −mysql> Create table employee3_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 ...
Read MoreHow LOCATE() function can be used with MySQL WHERE clause?
When we use LOCATE() function with MySQL WHERE clause, we need to provide the substring as first argument and column name of the table as the second argument along with a comparison operator. Following is an example using ‘Student’ table to demonstrate it −ExampleSuppose we have the following values in ‘Student’ table −mysql> Select * from Student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 15 | Harshit ...
Read MoreWhat is the use of MySQL BINARY keyword while performing string comparison?
When MySQL performs string comparison then it is not case-sensitive but with the help of BINARY keyword, MySQL can perform case-sensitive string comparison. It is because BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters. It can be illustrated with the following example from table ‘Student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | ...
Read MoreHow can we use the MySQL reserved words as an identifier?
We must have to use quotes with reserved words to use them as an identifier. The quotes can be single or double depends upon ANSI_QUOTES SQL mode.If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −mysql> create table `select`(id int); Query OK, 0 rows affected (0.19 sec)If this mode is enabled then we can use backtick (“`”) and double quotes (“”) both as identifier quote character. Consider the following example in which we created a table named ‘trigger’ −mysql> Create table "trigger" (id ...
Read MoreWhat happens if the substring is there for more than one time in the string given as the arguments of LOCATE() function?
In case if the substring is there for more than one time in the string then MySQL LOCATE() function will return the position of the first occurrence of the substring.Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?')As Result; +--------+ | Result | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)As we can see that the substring ‘good’ is in the string for two times. The first occurrence is at position 10 and another occurrence is at position 29. MySQL returns the position of the first occurrence.
Read MoreHow can I manage the start position of searching in MySQL LOCATE() function?
As we know that by default searching in LOCATE() function starts from beginning. We can manage the start position by giving an argument to specify the position from which we want to start the search in string. Following example will demonstrate it −Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?',11)As Result; +--------+ | Result | +--------+ | 29 | +--------+ 1 row in set (0.00 sec)In the above example, we have given the value 11 as the argument for position. It means that MySQL will start searching from 11th position.
Read MoreWhat happens if I will use integer values as arguments of MySQL LOCATE() function?
MySQL allows us to use integer values as the arguments of the LOCATE() function. We do not need to use quotes. It can be demonstrated with the help of the following example −Examplemysql> Select LOCATE(5,1698235); +-------------------+ | LOCATE(5,1698235) | +-------------------+ | 7 | +-------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(56,1698235); +--------------------+ | LOCATE(56,1698235) | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(23,1698235); +--------------------+ | LOCATE(23,1698235) | +--------------------+ | 5 | +--------------------+ 1 row in set (0.00 sec)
Read MoreHow an enumeration value in MySQL can be used in an expression?
As we know that enumeration values are associated with index values hence if we will use enumeration values in an expression then all the calculations would be done on index numbers. The following example will clarify it −mysql> Select * from Result; +-----+--------+-------+ | Id | Name | Grade | +-----+--------+-------+ | 100 | Gaurav | GOOD | | 101 | Rahul | POOR | | 102 | Rahul | NULL | | 103 | Mohan | | +-----+--------+-------+ 4 rows in set (0.00 sec) mysql> Select SUM(Grade) from result; +------------+ | SUM(Grade) | +------------+ ...
Read MoreHow can CONCAT_WS() function be used with MySQL WHERE clause?
When we use CONCAT_WS() function with WHERE clause then the output would be based upon the condition provided in WHERE clause. It can be understood from the example of ‘Student’ table as followsExamplemysql> Select CONCAT_WS(' ',Name, Last_name, 'Resident of', Address, 'is studying', Subject)AS 'Student Detail' from student WHERE id = 20; +----------------------------------------------------------------+ | Student Detail | +----------------------------------------------------------------+ | Gaurav Rathore Resident of Jaipur is studying Computers | +----------------------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow can CONCAT() function be applied on columns of MySQL table?
We can use CONCAT() function to combine the values of two or more columns. In this case, the arguments of the CONCAT() functions would be the name of the columns. For example, suppose we have a table named ‘Student’ and we want the name and address of the student collectively in one column then the following query can be written −mysql> Select Id, Name, Address, CONCAT(ID, ', ', Name, ', ', Address)AS 'ID, Name, Address' from Student; +------+---------+---------+--------------------+ | Id | Name | Address | ID, Name, Address | +------+---------+---------+--------------------+ | 1 | Gaurav | Delhi ...
Read More