Found 4219 Articles for MySQLi

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

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

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)

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

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

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.

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

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)

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

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