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
MySQL Articles
Page 77 of 355
What is MySQL LOAD DATA statement?
LOAD DATAThis 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, a path is the address of the file.file_name is the name of the .txt filetable_name is the table where the data will be loaded.To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −100 John USA 10000 101 Paul UK 12000 102 Henry NZ 11000 103 Rick ...
Read MoreWhat kind of information is displayed by MySQL DESCRIBE statement?
The DESCRIBE statement gives information about MySQL table’s structure.ExampleConsider the constructing of the following table name ‘Employee’ with Create Table statement as follows −mysql> Create table Employee(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(20)); Query OK, 0 rows affected (0.20 sec)Now with the help of ‘DESCRIBE Employee‘ statement, we can get the information about the employee table.mysql> Describe Employee; +-------+-------------+------+-----+---------+------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+------------------+ | ID | int(11) | NO | PRI | NULL | auto_increment ...
Read MoreWhat is the use of comparison operators with MySQL subquery?
The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. MySQL then compares the value that results from the subquery with the value on the other side of the comparison operator. MySQL subquery can be used before or after any of the comparison operators like =, >, >=,
Read MoreHow can we use a subquery that contains a reference to a table that also appears in the outer query?
A subquery that contains a reference to a table that also appears in the outer query is called a correlated subquery. In this case, MySQL evaluates from inner query to the outer query. To understand it we are having the following data from table ‘cars’ −mysql> Select * from Cars; +------+--------------+---------+ | ID | Name | Price | +------+--------------+---------+ | 1 | Nexa | 750000 | | 2 | Maruti Swift | 450000 | | 3 | BMW | 4450000 | | 4 ...
Read MoreHow can the rows be sorted out in a meaningful way?
For sorting the rows in a meaningful way we can use ORDER BY clause. Suppose we want to sort the rows of the following table −mysql> Select * from Student; +--------+--------+--------+ | Name | RollNo | Grade | +--------+--------+--------+ | Gaurav | 100 | B.tech | | Aarav | 150 | M.SC | | Aryan | 165 | M.tech | +--------+--------+--------+ 3 rows in set (0.00 sec)The query below sorted the table by ‘Name’.mysql> Select * from student order by name; +--------+--------+--------+ | Name | RollNo | Grade | +--------+--------+--------+ | Aarav | ...
Read MoreHow can we delete a MySQL stored function from the database?
If we have ALTER ROUTINE privileges then with the help of DROP FUNCTION statement, we can delete a MySQL stored function. Its syntax can be as follows −SyntaxDROP FUNCTION [IF EXISTS] function_nameHere function_name is the name of the function which we want to delete from our database.Examplemysql> DROP FUNCTION if exists Hello1; Query OK, 0 rows affected (0.70 sec)Now after deleting the function, check for the CREATE FUNCTION statement and we will get the error as follows −mysql> SHOW CREATE FUNCTION Hello1; ERROR 1305 (42000): Function Hello1 does not exist.
Read MoreCreate a MySQL stored procedure that counts the number of rows gets affected by MySQL query?
Following is a procedure that counts the number of rows get affected by MySQL query −mysql> Delimiter // mysql> CREATE PROCEDURE `query`.`row_cnt` (IN command VarChar(60000)) -> BEGIN -> SET @query = command; -> PREPARE stmt FROM @query; -> EXECUTE stmt; -> SELECT ROW_COUNT() AS 'Affected rows'; -> END // Query OK, 0 rows affected (0.00 sec) mysql> Delimiter ; mysql> Create table Testing123(First Varchar(20), Second Varchar(20)); Query OK, 0 rows affected (0.48 sec) mysql> CALL row_cnt("INSERT INTO testing123(First,Second) Values('Testing First','Testing Second');"); +---------------+ | Affected rows | +---------------+ | 1 | +---------------+ 1 row in set (0.10 sec) Query OK, 0 rows affected (0.11 sec)
Read MoreWhat kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
MySQL scalar subquery returns exactly one column value from one row and we can use it where a single column is permissible. Followings are the cases when scalar subqueries return value other than one row −Case1 − When it returns 0 rowsIn case if the subquery returns 0 rows then the value of scalar subquery expression would be NULL.Case2 − When it returns more than one rowsIn case if the subquery returns more than one row then, due to the property of scalar subquery, MySQL returns an error.It can be understood with the help of an example which uses the ...
Read MoreHow can we use a MySQL subquery with INSERT statement?
It can be understood with the help of an example in which we would copy the values of a table into other table. We are using the data from table ‘cars’ and copy its data to table ‘copy_cars’ −mysql> CREATE TABLE copy_cars LIKE cars; Query OK, 0 rows affected (0.86 sec) mysql> SELECT * from copy_cars; Empty set (0.08 sec)The following query using the subquery will insert the values same as ‘cars’ to table ‘copy_cars’ −mysql> INSERT INTO Copy_cars Select * from Cars; Query OK, 8 rows affected (0.07 sec) mysql> SELECT * from copy_cars; +------+--------------+---------+ | ID ...
Read MoreHow can I convert 1st January of the current year into epoch?
It can be done by using UNIX_TIMESTAMP() function as follows −mysql> Select UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01')); +--------------------------------------------------+ | UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01')) | +--------------------------------------------------+ | 1483209000 | +--------------------------------------------------+ 1 row in set (0.03 sec)It can be verified by the following query −mysql> Select FROM_UNIXTIME(1483209000); +---------------------------+ | FROM_UNIXTIME(1483209000) | +---------------------------+ | 2017-01-01 00:00:00 | +---------------------------+ 1 row in set (0.02 sec)
Read More