Found 4219 Articles for MySQLi

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)

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)

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

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

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

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

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 ‘Avg_marks’ that uses the dynamic data from table named ‘Student_marks’, having following records, to calculate the average of marks.mysql> Select * from Student_marks; +-------+------+---------+---------+---------+ | Name  | Math | English | Science | History | +-------+------+---------+---------+---------+ | Raman |   95 |      89 |      85 | ... Read More

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: Hello            sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `Hello`(S Varchar(20)) RETURNS varchar(20) CHARSET latin1 DETERMINISTIC RETURN CONCAT('Hello, ', S, '!') character_set_client: cp850 collation_connection: cp850_general_ci   Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)Read More

We can mysql.proc to see the list, along with complete information, of stored functions in a particular MySQL database by the following query −mysql> Select * from mysql.proc where db = 'query' AND type = 'FUNCTION' \G *************************** 1. row ***************************                   db: query                 name: factorial                 type: FUNCTION        specific_name: factorial             language: SQL      sql_data_access: CONTAINS_SQL     is_deterministic: YES        security_type: DEFINER     ... Read More

We can see the list, along with other information, of stored functions in a particular MySQL database by the following query −mysql> SHOW FUNCTION STATUS WHERE db = 'query'\G *************************** 1. row ***************************                   Db: query                 Name: factorial                 Type: FUNCTION              Definer: root@localhost             Modified: 2021-11-16 14:04:48              Created: 2021-11-16 14:04:48        Security_type: DEFINER           ... Read More