Tutorialspoint

What are MySQL subqueries and its general categories?

I want to know about MySQL subqueries in detail along with the categories associated with them.
Paul Richard
Answered on 21st Feb, 2018

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 ... Read More

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

After running a MySQL query one or more rows would be affected by that. I want to create a stored procedure that will count the number of rows affected.
Krantik Chavan
Answered on 21st Feb, 2018

Following is an 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;     -> ... Read More

Create a MySQL stored procedure that generates five random numbers?

I want to create a stored procedure for generating fist five random numbers.
varun
Answered on 21st Feb, 2018

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     -> ... Read More

How can we use a MySQL stored function in a database query?

I have created a stored function and now want to use the same in a MySQL query, how it can be done?
Prabhas
Answered on 21st Feb, 2018

It can be understood with the help of following the example in which we have created a function ‘Profit’ to calculate the profit and apply that function on the data of table ‘item_list’ by using it in the database query.Examplemysql> CREATE FUNCTION profit(Cost DECIMAL (10, 2), Price DECIMAL(10, 2))   ... Read More

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

I have created a stored function and now I want to delete that particular stored function. Which statement do I need to use to delete or drop that function?
seetha
Answered on 21st Feb, 2018

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 ... Read More

How can we alter a MySQL stored function?

I have created a stored function but forgot to add comments in that. Now, with the help of which statement I can add comments in that or in other words for any other purpose how can I alter alr.....
vanithasree
Answered on 21st Feb, 2018

If we have ALTER ROUTINE privileges then we can alter MySQL stored function with the help of ALTER FUNCTION query. Its syntax is as follows:SyntaxALTER FUNCTION function_name [characteristic ...] characteristic:    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { ... Read More

How can I write a MySQL stored function that calculates the factorial of a given number?

I want to create a stored function to find the factorial of a number. The function should take the number as the parameter.
radhakrishna
Answered on 21st Feb, 2018

Following is the example of a stored function that can calculate the factorial of a given number:CREATE FUNCTION factorial (n DECIMAL(3, 0)) RETURNS DECIMAL(20, 0) DETERMINISTIC BEGIN DECLARE factorial DECIMAL(20, 0) DEFAULT 1; DECLARE counter DECIMAL(3, 0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET ... Read More

How to write a MySQL stored function that updates the values in a table?

Suppose I have a MySQL table having some basic details. I want to create a MySQL stored function that can update the values in that table. The values must be given while calling that function.
mkotla
Answered on 21st Feb, 2018

As we know that function is best used when we want to return a result. Hence, when we will create stored functions for manipulating tables like to Insert or Update values then it would be more or less like stored procedures. In the following example, we are creating a stored ... Read More

How to write a MySQL stored function that inserts values in a table?

Suppose I have a MySQL table having some basic details. I want to create a MySQL stored function that can store the values in that particular table. The values must be given while calling that .....
Giri Raju
Answered on 21st Feb, 2018

As we know that function is best used when we want to return a result. Hence, when we will create stored functions for manipulating tables like to Insert or Update values then it would be more or less like stored procedures. In the following example we are creating a stored ... Read More

What are MySQL group functions?

I want to know about the various group functions used in MySQL.
Alankritha Ammu
Answered on 21st Feb, 2018

Group functions are the function which applied to a group of row or in other words group functions operate on sets of values. The following table gives the Description of MySQL group functions:Sr.No.Name & Description1AVG()It returns the average value of the argument.2BIT_AND()It returns bitwise AND.3BIT_ORIt returns bitwise OR.4BIT_XOR()It returns bitwise ... Read More

How MySQL stored function evaluates if it got NULL value while using the dynamic values from a table?

I have created a stored function with the help of which I am using the data from MySQL table dynamically. But I also have NULL values in the table so how MySQL will evaluate those NULL values?
Ramu Prasad
Answered on 21st Feb, 2018

In such kind of cases when a stored function got NULL values then it will return NULL as the result. It can be understood from the example below in which we have a NULL value in the records of student ‘Mohit’. Now, when we will apply the stored function ‘avg_marks’ ... Read More

How can I convert the epoch stored in MySQL table into readable dates?

There are some epochs saved as values in columns of MySQL table. I want to convert it into readable values so that it can be understood by users. Which MySQL function would be useful for doing .....
karthikeya Boyini
Answered on 21st Feb, 2018

To illustrate it we are using the following example of a table named ‘vistors’ which have the epoch as follows:mysql> Create table visitors(userid int not null, name varchar(20), epoch int NOT NULL); Query OK, 0 rows affected (0.42 sec) mysql> Insert into visitors Values(1, 'Gaurav', 1358658942); Query OK, 1 ... Read More

How can I convert 1st January of the current year into epoch?

I want to convert a particular date into epoch. Which MySQL function would be useful for this?
Paul Richard
Answered on 21st Feb, 2018

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 ... Read More

How can we create a MySQL stored function that uses the dynamic data from a table?

I want to create a stored function with the help of which I can use the data from MySQL table dynamically.
Sravani S
Answered on 21st Feb, 2018

MySQL Stored functions can reference tables but they cannot make use of statements that return a result set. Hence we can say that there is no SELECT query that returns result set. But we can have SELECT INTO to get rid of that. For example, we are creating a function ... Read More

How can we see the source code of a particular MySQL stored function?

I have created some stored function and now I want to see their source code again. Which statement do I need to use?
Daniol Thomas
Answered on 21st Feb, 2018

With the help of SHOW CREATE FUNCTION statement, we can see the source code of a stored function. To make it understand we are using the stored function named Hello() in the query as follows:mysql> SHOW CREATE FUNCTION Hello\G *************************** 1. row ***************************            Function: ... Read More

Advertisements
Loading...
Unanswered Questions View All