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 106 of 341
How can I use MySQL OCTET_LENGTH() function to count the number of characters stored in a data column?
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 MoreHow can we take a backup of all the databases by using mysqldump client program?
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.
Read MoreHow can I restore a database dumped by mysqldump?
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’.
Read MoreWhat MySQL TRIM() function returns if 1st argument(i.e. BOTH, LEADING, TRAILING) is not specified?
By default MySQL will assume the argument BOTH if the 1st argument is not specified in TRIM() function. Following example will demonstrate it.Examplemysql> SELECT TRIM('A' FROM 'ABCDAEFGAA'); +-----------------------------+ | TRIM('A' FROM 'ABCDAEFGAA') | +-----------------------------+ | BCDAEFG | +-----------------------------+ 1 row in set (0.00 sec)The above result set shows that when we did not specify 1st argument then MySQL returns the output by assuming BOTH as the 1st argument of TRIM() function.
Read MoreWhat would be the output of MySQL SUM() function if a column having no values has been passed as its argument?
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 MoreHow can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?
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 MoreHow MySQL SUM() function evaluates if it got the column, having character data type, as its argument?
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)
Read MoreHow can we export data to a CSV file whose filename name contains timestamp at which the file is created?
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 MoreHow can we export data to a CSV file along with columns heading as its first line?
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 MoreHow MySQL evaluates if we export the data to CSV file from a table which contains a NULL value(s)?
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