To get the users logged in today, use the below syntax. Here, we are expecting that your datetime field is a string type −select yourColumnName1, yourColumnName2, yourColumnName3, ...N from youTableName WHERE STR_TO_DATE(yourColumnName1, ‘format’') =CURDATE();Let’s say we have the following “DateEqualToday “ table that stores users first and last name with the login date −+------+------------+-----------+------------+ | Id   | First_Name | Last_Name | LoginDate  | +------+------------+-----------+------------+ |    1 | James      | Smith     | 20-12-2018 | |    2 | Carol      | Taylor    | 21-12-2017 | |    3 | John       ... Read More

Update only one cell’s data with the help of UPDATE command. The syntax is as follows −UPDATE yourTableName yourColumnName=yourNewValue where yourColumnName=yourOldValue;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table changeCellsData    -> (    -> Id int,    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into changeCellsData values(101, 'Mike', 23); Query OK, 1 row affected (0.12 sec) mysql> insert into ... Read More

To loop through all rows of a table, use stored procedure in MySQL. The syntax is as follows −delimiter // CREATE PROCEDURE yourProcedureName() BEGIN DECLARE anyVariableName1 INT DEFAULT 0; DECLARE anyVariableName2 INT DEFAULT 0; SELECT COUNT(*) FROM yourTableName1 INTO anyVariableName1; SET anyVariableName2 =0; WHILE anyVariableName2 < anyVariableName1 DO    INSERT INTO yourTableName2(yourColumnName, ...N) SELECT (yourColumnName1, ...N) FROM yourTableName1 LIMIT anyVariableName2, 1;    SET anyVariableName2 = anyVariableName2+1; END WHILE; End; //To understand the above syntax, let us create two tables i.e. one has records and the second table will have records from the loop using stored procedures.The following is the query ... Read More

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

The MySQL CASE works like a switch statement. The syntax of CASE is as follows −Case 1 − Compare StatementCase when anyCompareStatement then value1 when anyCompareStatement then value2 . . N else anyValue end as anyVariableName;Case 2 − ConditionsThe second syntax can be used when you are selecting only one column. The syntax is as follows −case yourColumnName when condition1 then result1 when condition1 then result2 . . N else anyValue end;To understand the above concept, let us use select statement.Case 1The query is as follows −mysql> select    -> case when 45 < 55 then '55 is greater than ... Read More

You can use date-format() function from MySQL to get the first day of every corresponding month. The syntax is as follows −select DATE_FORMAT(yourDatetimeColumnName ,'%Y-%m-01') 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 getFirstDayOfMonth -> ( -> DueDatetime datetime -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into getFirstDayOfMonth values(date_add(now(), interval 3 month)); Query OK, 1 row affected ... Read More

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