To view stored procedure/function definition in MySQL, you can use show command. The syntax is as follows −SHOW CREATE PROCEDURE yourProcedureName;To understand the above syntax, you can create a procedure and check that definition. Let us create a stored procedure −mysql> delimiter // mysql> create procedure AllRecords()    -> begin    -> select *from student;    -> end // Query OK, 0 rows affected (0.24 sec)You can call the stored procedure with the help of call command. The query is as follows −mysql> delimiter ; mysql> call AllRecords();The following is the output −+------+-------+ | id   | Name  | +------+-------+ ... Read More

Calculate Age based on date of birth with the help of DATE_FORMAT() method in MySQL. Firstly, get the current date time with the help of now() method and you can place your date of birth in DATE_FORMAT().The syntax is as follows −SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(), 'yourDateofbirth')), '%Y')+0 AS anyVariableName;Apply the above syntax to calculate age from yourDateofbirth. In the above syntax, replace yourDateofbirth with your date of birth. The query is as follows −SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(), '2010-11-25')), '%Y')+0 AS Age;The following is the output −+------+ | Age | +------+ | 8 | +------+ 1 row in set (0.00 sec)Let ... Read More

A ternary conditional operator looks like ?: in programming language like C, C++, Java etc. The syntax is as follows −(yourCondition) ? statement1:statement2;In the above syntax, if yourCondition becomes true then statement1 will evaluate and if yourCondition becomes false then statement2 will evaluate.But the above syntax does not work in MySQL. We can use IF() function from MySQL for the same purpose.Let us see an example −Case 1mysql> select if(3 > 5, 'Condition is true', 'Condition is not true') as ConditionalResult;The following is the output in which second statement evaluates since is 3 isn’t more than 5 −+-----------------------+ | ConditionalResult ... Read More

Yes, you can use the method CURRENT_USER() to know the current username in MySQL.The above method returns the username that can be used to authenticate the client connection.The query is as follows −mysql> select CURRENT_USER();The following is the output −+----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec)Or you can use USER() method from MySQL. The query is as follows −mysql> select user();Here is the output −+----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)

To store money amounts in MySQL, the best choice is to use DECIMAL data type or NUMERIC type. Float data type is not a good choice for money amounts. It gives some rounding errors. Therefore, avoid float for money amounts.Let us first create a table with data type DECIMAL. The following is the query to create a table −mysql> create table MoneyStorageDemo -> ( -> Amount DECIMAL(4, 2) -> ); Query OK, 0 rows affected (0.44 sec)Inserting some values into the table with the help of insert command. The query is ... Read More

You cannot do select IN range. For the same result, use BETWEEN. Let us see an example −IN(start, end): It means that the intermediate value between start and end won’t get displayed. For the above logic, you can use BETWEEN.BETWEEN clause is inclusive, for example, suppose there are 1, 2, 3, 4, 5, 6 numbers. If you want to display numbers from 2 to 6 inclusively, then using BETWEEN the numbers 2 and 6 will also get displayed.Let us create a table −mysql> create table SelectInWithBetweenDemo -> ( -> PortalId int ... Read More

To add AUTOINCREMENT in MySQL, you can use the ALTER command.ALTER TABLE yourTableName change yourColumName yourColumnName dataType AUTO_INCREMENT PRIMARY KEY;To understand the above concept, create a table with a column. Let us create a table −mysql> create table AlterTableToAddAutoIncrement -> ( -> StudentId int -> ); Query OK, 0 rows affected (0.57 sec)Implement the above syntax to change “StudentId” with AUTOINCREMENT. The query is as follows −mysql> alter table AlterTableToAddAutoIncrement change StudentId StudentId int AUTO_INCREMENT Primary key; Query OK, 0 rows affected (1.93 sec) Records: 0 Duplicates: 0 Warnings: 0We ... Read More

To know the biggest value from two or more fields, use the function GREATEST() from MySQL.The syntax is as follows −SELECT GREATEST(MAX(yourColumnName1), MAX(yourColumnName2), ...............MAX(yourColumnName2) ) from yourTableName;Let us understand the above concept by creating a table with more than two columns −mysql> create table GreatestOfTwoOrMore -> ( -> Marks1 int, -> Marks2 int, -> Marks3 int -> ); Query OK, 0 rows affected (0.57 sec)Here is the query to insert records in a table −mysql> insert into GreatestOfTwoOrMore values(23, 78, 89); Query OK, 1 row ... Read More

You can strip time component from datetime with the help of DATE() function. The syntax is as follows −SELECT DATE(yourColumnName) from yourTableName;To understand the above concept, let us first create a table −mysql> create table StripComponentDatetimeDemo -> ( -> YourDateTime datetime -> ); Query OK, 0 rows affected (0.60 sec)Let us now insert some records in the table. The query is as follows −mysql> insert into StripComponentDatetimeDemo values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.13 sec)Display records with the help of select statement. The query is as follows displaying ... Read More

To round down to nearest integer, use FLOOR() function from MySQL. The syntax is as follows −SELECT FLOOR(yourColumnName) from yourTableName;Let us first create a table −mysql> create table FloorDemo -> ( -> Price float -> ); Query OK, 0 rows affected (0.57 sec)Insert records to column Price. The query to insert records is as follows −mysql> insert into FloorDemo values(5.75); Query OK, 1 row affected (0.21 sec) mysql> insert into FloorDemo values(5.23); Query OK, 1 row affected (0.31 sec) mysql> insert into FloorDemo values(5.50); Query OK, 1 row affected (0.12 sec)Display ... Read More