Found 4381 Articles for MySQL

How can we alter a MySQL stored function?

vanithasree
Updated on 22-Jun-2020 08:02:48

412 Views

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 { DEFINER | INVOKER } | COMMENT 'string'Here function_name is the name of the function which we want to alter.The ALTER FUNCTION statement can change the characteristics, defined above in the syntax, of a stored function. We can specify more than one change in an ALTER FUNCTION statement. But we ... Read More

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

radhakrishna
Updated on 22-Jun-2020 08:03:24

1K+ Views

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 counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // mysql> Select Factorial(5)// +--------------+ | Factorial(5) | +--------------+ |          120 | +--------------+ 1 row in set (0.27 sec) mysql> Select Factorial(6)// +--------------+ | Factorial(6) | +--------------+ |          720 | +--------------+ 1 row in set (0.00 sec)

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

mkotla
Updated on 13-Feb-2020 06:59:33

1K+ Views

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 function named ‘tbl_update’ which will update the values in a table named ‘student_marks’.mysql> Select * from student_marks// +---------+------+---------+---------+---------+ | Name    | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman   |   95 |      89 |      85 |      81 | ... Read More

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

Giri Raju
Updated on 13-Feb-2020 07:00:24

1K+ Views

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.ExampleIn the following example we are creating a stored function named ‘tbl_insert’ which will insert the values in a table named ‘student_marks’.mysql> Create Function tbl_insert(S_name Varchar(50), M1 INT, M2 INT, M3 INT, M4 INT)     -> RETURNS INT     -> DETERMINISTIC     -> BEGIN     -> INSERT INTO student_marks values(S_name, M1, M2, M3, M4);     ... Read More

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

Paul Richard
Updated on 22-Jun-2020 08:06:17

110 Views

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)

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

karthikeya Boyini
Updated on 22-Jun-2020 08:05:29

145 Views

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 row affected (0.04 sec) mysql> Insert into visitors Values(2, 'Raman', 1358659585); Query OK, 1 row affected (0.04 sec) mysql> Select userid, name, FROM_UNIXTIME(epoch) from visitors; +--------+--------+----------------------+ | userid | name   | FROM_UNIXTIME(epoch) | +--------+--------+----------------------+ |      1 | Gaurav | 2013-07-24 16:05:42  | |      2 | Raman  | 2013-07-24 16:16:25  | +--------+--------+----------------------+ 2 rows in set (0.00 sec)

What are MySQL group functions?

Alankritha Ammu
Updated on 22-Jun-2020 08:04:31

196 Views

Group functions are the function which applied to a group of rows 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 XOR.5COUNT()It returns a count of the number of rows returned.6COUNT(DISTINCT)It returns the count of a number of different values.7GROUP_CONCAT()It returns a concatenated string.8MAX()It returns the maximum value.9MIN()It returns the minimum value.10ST()It returns the population standard deviation.11STDDEV()It returns the population standard deviation.12STDDEV_POP()It returns the population standard deviation.13STDDEV_SAMP()It returns the sample ... Read More

How can group functions be used in ORDER BY clause?

Swarali Sree
Updated on 22-Jun-2020 07:56:21

167 Views

We can sort the result set groups by using group functions in the ORDER BY clause. By default, the sort order is ascending but we can reverse it by using DESC keyword.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ) ORDER BY Count(*) DESC; +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Prof        |      2009 |        2 | | Asst.Prof   |      2015 |        1 | | Asst.Prof   |      2016 |        1 | | Prof     ... Read More

How to use MySQL SOUNDEX() function with LIKE operator to retrieve the records from table?

Chandu yadav
Updated on 22-Jun-2020 07:55:56

327 Views

As we know that SOUNDEX() function is used to return the soundex, a phonetic algorithm for indexing names after English pronunciation of sound,  a string of a string. In the following example, we are taking the data from ‘student_info’ table and applying SOUNDEX() function with LIKE operator to retrieve a particular record from a table −mysql> Select * from Student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101 | YashPal  | Amritsar   | History    | | 105 | Gaurav   | Chandigarh | Literature | | 125 | Raman    | Shimla     ... Read More

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

Ramu Prasad
Updated on 13-Feb-2020 07:13:43

250 Views

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’ on this data, it will return NULL as result.mysql> Select * from Student_marks; +-------+------+---------+---------+---------+ | Name  | Math | English | Science | History | +-------+------+---------+---------+---------+ | Raman |   95 |      89 |      85 |      81 | | Rahul |   90 | ... Read More

Advertisements