Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
When MySQL SUM() function got a column, having no values, as an argument then it will return NULL, rather than 0, as output. The column can be of any data type. Following the example, using a table named ‘social’ having only one column named ‘id’ with no values, will illustrate itExamplemysql> Describe Social; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | Name | varchar(20) | YES | ... Read More
The intern() method of String class can be used to deal with the string duplication problems in Java. Using intern() we can save a lot of memory consumed by duplicate string instances. A string is duplicate if it contains the same content as another string but it can be occupied different memory locations.We know that JVM maintains a separate heap memory for string literals for the performance. Once we declare a string literal it will go to this pool and if another variable is assigned with the same literal value, it will be picked from the pool instead of creating a new ... Read More
When MySQL SUM() function got a column, having no values, an argument then it will return NULL, rather than 0, as output. But if we want to customize this output to show 0 as output then we can use MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To illustrate it, we are taking the example of ‘Tender’ table having the following data −mysql> Select * from tender; +----+---------------+--------------+ | Sr | CompanyName | Tender_value | +----+---------------+--------------+ | 1 | Abc Corp. ... Read More
MySQL SUM() function will return 0, rather than NULL, along with a warning on getting the character type column as its argument. Following example using data from table named ‘Social’ will illustrate it −Examplemysql> Select * from Social; +------+-------+ | Id | Name | +------+-------+ | 100 | Rahul | +------+-------+ 1 row in set (0.00 sec) mysql> Select SUM(Name) From Social; +-----------+ | SUM(Name) | +-----------+ | 0 | +-----------+ 1 row in set, 1 warning (0.00 sec)
Sometimes we need to export data into a CSV file whose name has a timestamp at which that file is created. It can be done with the help of MySQL prepared statement. To illustrate it we are using the following example −ExampleThe queries in the following example will export the data from table ‘student_info’ to the CSV file having a timestamp in its name.mysql> SET @time_stamp = DATE_FORMAT(NOW(), '_%Y_%m_%d_%H_%i_%s'); Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files'; Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files/'; Query OK, 0 rows affected ... Read More
The substring() method returns a String datatype which corresponds to the original String starting from the begin index until the end Index. If the end index is not specified, it is imperative that the endIndex is the String length. Since we are dealing with the String, the index starts at '0' position.Syntaxpublic String substring(int beginIndex) public String substring(int beginIndex, int endIndex)beginIndex: the starting index or position where we want to start to cut or substring our String.endIndex: the end index or position where we want to end our cutting or substring our String.This method returns a String datatype which corresponds to the ... Read More
For adding the column values we need to use UNION statement. It can be demonstrated with the help of the following example −ExampleIn this example data from student_info will be exporting to CSV file. The CSV file will have the first line as the name of the columns.mysql>(SELECT 'id', 'Name', 'Address', 'Subject')UNION(SELECT id, Name, Address, Subject From student_info INTO OUTFILE 'C:/mysql/bin/mysql-files/student_25.CSV' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r'); Query OK, 7 rows affected (0.04 sec)After executing the above query MySQL creates Student_25.CSV file which have the following values −id; "Name"; ... Read More
If we export the data from a table having NULL values then MySQL will store \N in CSV file for the record MySQL table having NULL values. It can be illustrated with the help of the following example −ExampleSuppose if we want to export the values of the table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | ... Read More
If we want to store any other value than \N in CSV file on exporting the data to CSV file from a table which contains NULL value(s) then we need to replace \N values with other value by using IFNULL statement. To illustrate it we are taking the following example −ExampleSuppose if we want to export the values of the table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | ... Read More
MySQL throws this error because of the two reasons, either no directory is specified under --secure--file--priv variable or we are giving the wrong path in our query while importing or exporting the data. To tackle this error we must have to check the value of –secure—file—priv variable by following query −mysql> Select @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---------------------------+ | C:\mysql\bin\mysql-files\ | +---------------------------+ 1 row in set (0.00 sec)We can see there is path under secure_file_priv variable and all the files would be created under this directory when we export the data.But, if the above command shows NULL as result then ... Read More