To encode base64, you can use two functionalities −TO_BASE64()FROM_BASE64()The syntax for base64 encode is as follows −SELECT TO_BASE64(anyValue) as AnyVariableName;The syntax for base64 decode is as follows −SELECT FROM_BASE64(encodeValue) as anyVariableNameTo understand the above concept, let us use the above syntax −Case 1 − EncodeTo encode the value, use the to_base64(). The query is as follows −mysql> select TO_BASE64('Password') as Base64EncodeValue;Output+-------------------+ | Base64EncodeValue | +-------------------+ | UGFzc3dvcmQ=      | +-------------------+ 1 row in set (0.00 sec)Case 2 − DecodeTo decode the value, use the from_base64(). The query is as follows −mysql> select FROM_BASE64('UGFzc3dvcmQ=') as Base64DecodeValue;Output+-------------------+ | Base64DecodeValue | +-------------------+ | ... Read More

You can use the in-built function from_unixtime() to convert INT to DATETIME. The syntax is as follows −SELECT FROM_UNIXTIME(yourColumnName, ’%Y-%m-%d') 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 IntToDateDemo -> ( -> Number int -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query to insert record is as follows −mysql> truncate table IntToDateDemo; Query OK, 0 rows affected (4.11 sec) mysql> insert ... 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

To get the original value, use the following syntax −Syntaxselect yourBitColumnName+0 from yourTableName;The above syntax cast the bit column to an integer. To understand the above concept, let us create a table and check how the returning value is blank. We will also see how to get the original value.The query to create a table.mysql> create table BitDemo -> ( -> hasValidId bit not null -> ); Query OK, 0 rows affected (1.21 sec)Insert some records in the table using insert command. The query to insert records is as follows −mysql> insert into BitDemo values(1); Query OK, 1 row affected ... Read More