Let’s say you have a table with UserLoginTime column wherein we have stored some values for sample. This is the login time of users and we want to filter all these records on the basis of current day, month and year i.e. the current date. We will beLet us now create the table we discussed abovemysql> create table userLoginInformation    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),    - > UserLoginTime datetime    - > ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using ... Read More

To specify exact order with where id IN, you need to use find_in_set() function.The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1, yourValue2, yourValue3, ....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1, yourValue2, yourValue3, ....N’');Let us first create a tablemysql> create table FindInSetDemo    - > (    - > Id int,    - > Name varchar(20),    - > Age int    - > ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into FindInSetDemo values(10, 'John', 23); Query OK, 1 row affected (0.20 sec) mysql> insert ... Read More

To calculate the total time duration in MySQL, you need to use SEC_TO_TIME(). Let us see an example by creating a tablemysql> create table AddTotalTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > LoginTime time - > ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AddTotalTimeDemo(LoginTime) values('05:05:00'); Query OK, 1 row affected (0.10 sec) mysql> insert into AddTotalTimeDemo(LoginTime) values('07:20:00'); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

To exclude entries with “0”, you need to use NULLIF() with function AVG().The syntax is as followsSELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;Let us first create a tablemysql> create table AverageDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > StudentName varchar(20),    - > StudentMarks int    - > ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AverageDemo(StudentName, StudentMarks) values('Adam', NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into AverageDemo(StudentName, StudentMarks) values('Larry', 23); Query OK, ... Read More

To group by range in MySQL, let us first create a table. The query to create a table is as followsmysql> create table GroupByRangeDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > YourRangeValue int    - > ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into GroupByRangeDemo(YourRangeValue) values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(7); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(9); Query OK, 1 ... Read More

For this, let us first create a new table in MySQLmysql> create table useProcedure - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(20), - > LastName varchar(20) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into useProcedure(FirstName, LastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.27 sec)The following is your stored procedure to set two variables in a stored procedure with single select ... Read More

If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL. The syntax is as follows SELECT LAST_INSERT_ID(); To understand the above syntax, let us create a table. The query to create a table is as follows mysql> create table LastInsertedRow    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),      - > UserAge int    - > ); Query OK, 0 rows affected (0.56 sec) Insert some records in the table using insert command. The ... Read More

To change a MySQL column datatype from text to timestamp, you need to use ALTER command.The syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName TIMESTAMP;To understand the above syntax, let us create a table.The query to create a table is as followsmysql> create table textTotimestampdemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Source text    - > ); Query OK, 0 rows affected (0.44 sec)Here is the description of table using DESC command.The syntax is as followsDESC yourTableName;The query is as followsmysql> desc textTotimestampdemo;The following is the output+--------+---------+------+-----+---------+----------------+ | ... Read More

If you want the database location i.e. where it is created in MySQL, you can use system variable @@datadir.The syntax is as followsSELECT @@datadir;The following is the querymysql> select @@datadir;Here is the output. The above query returns the location+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the above directory in your system. The screenshot of the ... Read More

To increment all the rows of a particular ID column by 1, you need to use UPDATE command and update the table. The syntax of the query is as follows. We have also used ORDER BY hereUPDATE yourTableName SET yourIdColumnName=yourIdColumnName+1 ORDER BY yourIdColumnName DESC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table IdColumnadd1Demo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY - > ); Query OK, 0 rows affected (0.58 sec)Insert some records in the ... Read More