MySQLi Articles - Page 257 of 341

The now() function returns the constant time that exhibits the time at which any statement began to execute. The sysdate() function returns the exact same datetime at which it executed the statement from MySQL 5.0.13.Suppose if you are updating datetime with now() in triggers or stored procedure, the now() method returns the time at which time the triggering and stored procedure begin to execute.Here is the demo of update with now(). Let us first create a table. The query to create a table is as follows −mysql> create table NowDemo -> ( -> DueDateTime datetime -> ); Query OK, 0 ... Read More

The syntax for mass update with CASE WHEN/ THEN/ ELSE is as follows −UPDATE yourTableName set yourColumnName=case when yourColumnName=Value1 then anyUpdatedValue1 when yourColumnName=Value2 then anyUpdatedValue2 when yourColumnName=Value3 then anyUpdatedValue3 when yourColumnName=Value4 then anyUpdatedValue4 else yourColumnName end;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table CaseUpdateDemo -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CaseUpdateDemo values(1, 'John'); Query OK, 1 row affected ... Read More

Whenever your column has an auto incremented primary key then there is an advantage that you do not need to give value for that column in the INSERT command. This means MySQL will give the value for that column.To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table AutoIncrementedPrimary    -> (    -> Id int auto_increment,    -> Name varchar(100),    -> Age int,    -> Primary key(Id)    -> ); Query OK, 0 rows affected (0.56 sec)Now insert records for the column Name and Age ... Read More

In BIGINT(8), the number 8 represents how the data will be displayed. It does not affect the storage. The number is used to display width.BIGINT takes 8 bytes i.e. 64 bits. The signed range is -9223372036854775808 to 9223372036854775807 and unsigned range takes positive value. The range of unsigned is 0 to 18446744073709551615.To understand bigint(8), let us create a table with BIGINT(8) and zerofill column −mysql> create table BigIntDemo8 -> ( -> Number1 BIGINT(8) not null, -> Number2 BIGINT(8) unsigned zerofill not null -> ); Query OK, 0 rows ... Read More

To get the index of last substring in a given string, use the char_length() function. First, we need to calculate string length and subtract the last sub string length from the entire length. The difference in length is index of substring.SyntaxThe syntax is as follows −select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ', REVERSE(yourColumnName))+1 as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SubStringIndexDemo    -> (    -> Words varchar(200)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table ... Read More

You can achieve with the help of timestampdiff() method from MySQL. The syntax is as follows −SyntaxSELECT ABS(TIMESTAMPDIFF(HOUR, yourColumnName1, yourColumnName2)) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DifferenceInHours    -> (    -> StartDateTime datetime,    -> EndDateTime datetime    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInHours values('2018-12-20 10:00:00', '2018-12-19 12:00:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DifferenceInHours ... Read More

To order by relevance, use the case statement. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table OrderByRelevance    -> (    -> UserId int,    -> UserName varchar(200)    -> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByRelevance values(101, 'Carol Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into OrderByRelevance values(102, 'Carol Adams'); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More

To query for string fields with a specific length, use the char_length() or length() from MySQL.SyntaxThe syntax is as follows −Case 1 − Use of char_length()This can be used when we are taking length in a number of characters.The syntax −select *from yourTableName where char_length(yourColumnName)=anySpecificLengthValue;Case 2 − Use of length()This can be used when we are taking the length in bytes.The syntax −select *from yourTableName where length(yourColumnName)=anySpecificLengthValue;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table StringWithSpecificLength -> ( -> Id int, -> Name varchar(100), -> FavouriteLanguage ... Read More

Let us learn some points about TINYINT type in MySQL −The TINYINT type takes 1 byte i.e. 8 bits.The TINYINT(N), where N indicates the display width you want.For example, TINYINT(1) can be used to display width which is 1.Let us learn about the minimum and maximum values −The maximum value for tinyint is= (2(8-1)-1) = 127 The minimum value for tinyint is = -(2(8-1)) = -128.The value will be between -128 to 127. This means TINYINT (1) does not affect the maximum and minimum value of tinyint.Let us check it −Firstly, create a table with a column set as TINYINT ... Read More

You can use date-sub() and now() function from MySQL to fetch the rows added in last hour.SyntaxThe syntax is as follows −select *from yourTableName where yourDateTimeColumnName create table LastHourRecords -> ( -> Id int, -> Name varchar(100), -> Login datetime -> ); Query OK, 0 rows affected (0.67 sec)Insert records in the form of datetime using insert command. The query to insert record is as follows −mysql> insert into LastHourRecords values(1, 'John', ' 2018-12-19 10:00:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into LastHourRecords values(2, 'Carol', '2018-12-19 10:10:00'); Query OK, 1 row affected (0.15 sec) ... Read More