Found 4381 Articles for MySQL

How can the rows be sorted out in a meaningful way?

Abhinanda Shri
Updated on 22-Jun-2020 08:23:17

78 Views

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 More

How can we combine ROW selection with COLUMN selection in MySQL?

Nancy Den
Updated on 22-Jun-2020 08:27:42

191 Views

For combining ROW selection with COLUMN selection, we can use the ‘WHERE’ clause. For example, we have a table below −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)Now, the following query will show how we can combine ROW selection with COLUMN selection using WHERE clause.mysql> Select Name, RollNo, Grade from Student where Grade='M.Sc' or Grade='B.Tech'; +--------+--------+--------+ | Name   | RollNo ... Read More

What is MySQL LOAD DATA statement?

Ankitha Reddy
Updated on 22-Jun-2020 08:27:00

278 Views

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 More

How do we count the records from MySQL table where column holds duplicate/triplicates data?

Daniol Thomas
Updated on 13-Feb-2020 07:53:40

126 Views

Suppose we have the following table named stock_item in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ and for items ‘Shirts’, ‘Shoes’ and ‘Trousers’ triplicate value 29 is hold by column ‘quantity’ as shown in the table.mysql> Select * from stock_item; +------------+----------+ | item_name  |quantity  | +------------+----------+ | Calculator | 89       | | Notebooks  | 40       | | Pencil     | 40       | | Pens       | 32       | | ... Read More

How do we count the total duplicate records in a column of MySQL table?

Abhinaya
Updated on 13-Feb-2020 07:31:52

2K+ Views

Suppose we have the following table named stock_item in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ and for items ‘Shirts’, ‘Shoes’ and ‘Trousers’ triplicate value 29 is hold by column ‘quantity’ as shown in the table.mysql> Select * from stock_item; +------------+----------+ | item_name  |quantity  | +------------+----------+ | Calculator |       89 | | Notebooks  |       40 | | Pencil     |       40 | | Pens       |       32 | | ... Read More

What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?

Ayyan
Updated on 22-Jun-2020 08:11:54

446 Views

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 More

Create a MySQL stored procedure that counts the number of rows gets affected by MySQL query?

Krantik Chavan
Updated on 22-Jun-2020 08:13:41

1K+ Views

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)

What are MySQL subqueries and its general categories?

Paul Richard
Updated on 22-Jun-2020 08:01:50

297 Views

A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at runtime. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.A subquery can be used any place where an expression is allowed ... Read More

Create a MySQL stored procedure that generates five random numbers?

varun
Updated on 22-Jun-2020 08:14:24

670 Views

With the help of the following query we can create a stored procedure to generate five random numbers −mysql> DELIMITER // mysql> DROP PROCEDURE IF EXISTS RandomNumbers;     -> CREATE PROCEDURE RandomNumbers()     -> BEGIN     -> SET @i = 0;     -> REPEAT     -> SELECT RAND() AS 'Random Number';     -> SET @i = @i + 1;     -> UNTIL @i >=5 END REPEAT;     -> END     -> // Query OK, 0 rows affected (0.16 sec) Query OK, 0 rows affected (0.16 sec)Now, invoke the procedure to ... Read More

How can we delete a MySQL stored function from the database?

seetha
Updated on 22-Jun-2020 08:17:16

457 Views

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.

Advertisements