The ON UPDATE CURRENT_TIMESTAMP defines that an update without an explicit timestamp would result in an update to the current timestamp value.You can remove ON UPDATE CURRENT_TIMESTAMP from a column using ALTER command.The syntax is as followsALTER TABLE yourTableName CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT NULL default CURRENT_TIMESTAMP;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table removeOnUpdateCurrentTimeStampDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP   ... Read More

To get the records of the last days from the current date, you need to use DATE_SUB(). We will also use NOW() to get the current date. The syntax for the same is as followsSELECT *FROM yourTableName WHERE yourDateTimeColumnName BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 2 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);Let us see an examplemysql> create table get2daysAgoDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(20), - > PostDateTime datetime - > ); Query OK, 0 rows affected (1.70 ... Read More

You need to use DISTINCT keyword to select distinct dates from datetime column in a table.For an example, let us create a tablemysql> create table PostMesssageDemo    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserMessage varchar(100),    - > UserPost datetime    - > ); Query OK, 0 rows affected (0.60 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', now()); Query OK, 1 row affected (0.17 sec) mysql> insert into PostMesssageDemo(UserMessage, UserPost) values('Software Developer', date_add(now(), interval 3 ... Read More

Here is the syntax of MySQL CASE OR conditionSELECT yourColumnName1, .....N ,    CASE WHEN yourColumnName2=0 or yourColumnName2IS NULL THEN 'yourMessage1' ELSE 'yourMessage2' END AS yourAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ReservationSystems    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > isSeatConfirmed tinyint    - > ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into ReservationSystems(Name, isSeatConfirmed) ... Read More

To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10.The syntax is as followsSELECT *FROM yourTableName ORDER BY yourIdColumnName LIMIT 10;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table Clients    - > (    - > Client_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > ClientName varchar(20)    - > ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using INSERT command.The query is as followsmysql> insert into Clients(ClientName) values('Larry'); Query OK, 1 row affected (0.09 ... Read More

To prevent negative numbers in MySQL, you need to use INT UNSIGNED. Let’s say you created a table with a column as int i.e. UserGameScores heremysql> create table preventNegativeNumberDemo    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),    - > UserGameScores int    - > ); Query OK, 0 rows affected (1.74 sec)Now if you need to prevent negative numbers in it, modify the same column with INT UNSIGNEDmysql> alter table preventNegativeNumberDemo modify column UserGameScores INT UNSIGNED NOT NULL; Query OK, 0 rows affected (3.32 sec) Records: 0 ... Read More

Let us first create a table wherein one of the columns is with datetime. The query to create a table is as followsmysql> create table Add6Hour - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ArrivalTime datetime - > ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into Add6Hour(ArrivalTime) values(now()); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement.The query is as followsmysql> select ... Read More

You need to use ORDER BY clause for this. Let us first create a table. The query to create a table is as followsmysql> create table OrderByDateThenTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ShippingDate date, - > ShippingTime time - > ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. Here, we have two similar dates, but different times i.e. 2018-01-24mysql> insert into OrderByDateThenTimeDemo(ShippingDate, ShippingTime) ... Read More

To get the record before the last one i.e. the second last record in MySQL, you need to use subquery.The syntax is as followsSELECT *FROM (SELECT *FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 2) anyAliasName ORDER BY yourIdColumnName LIMIT 1;Let us first create a table. The query to create a table is as followsmysql> create table lastRecordBeforeLastOne    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20) DEFAULT 'John',    - > Age int DEFAULT 18    - > ); Query OK, 0 rows affected (0.79 sec)Now you can insert some ... Read More

You can select most recent date out of a set of several possible timestamps with the help of ORDER BY clause.The syntax is as followsSELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourTimestampColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MostRecentDateDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(10),    - > ShippingDate timestamp    - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command. ... Read More