Instead of simulating an array variable, use temporary table in MySQL. The syntax is as follows −create temporary table if not exists yourTemporaryTableName select yourColumnName1, yourColumnName2, ......N from yourTableName where conditionTo understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SimulateArrayDemo -> ( -> Id int, -> FirstName varchar(100), -> LastName varchar(100 -> ) -> ); Query OK, 0 rows affected (1.25 sec)Insert some records in the table using ... Read More

Whenever you want all the values like not null for a column then use count(*). This is faster than using count() method.The syntax to use count(*) is as follows −select count(*) as anyVariableName from yourTableName;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table CountingDemo    -> (    -> BookId int    -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CountingDemo values(100); Query OK, 1 row affected (0.13 sec) ... Read More

To simulate a print statement in MySQL, you can use select statement. The syntax is as follows −SELECT ‘anyStringValue’ as ’ ‘;You can check the above syntax at the MySQL command line client.Case 1To print a string.mysql> select 'HELLO MYSQL' as ' ';Output+-------------+ |             | +-------------+ | HELLO MYSQL | +-------------+ 1 row in set (0.00 sec)Case 2a) To print integer, use the following query −mysql> select 23 as ' ';Output+----+ |    | +----+ | 23 | +----+ 1 row in set (0.00 sec)b) To print float or double type, use the following ... Read More

In order to get the date from the timestamp, you can use DATE() function from MySQL.The syntax is as follows −SyntaxSELECT DATE(yourTimestampColumnName) 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 DateFromTimestamp -> ( -> ShippingDateTime timestamp -> ); Query OK, 0 rows affected (0.60 sec)Insert date and time for the column ShippingDateTime we created above.The query to insert record is as follows −mysql> insert into DateFromTimestamp values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFromTimestamp values('2013-11-26 14:36:40'); ... Read More

To make a primary key start from 1000, you need to alter your table and set to auto_increment with value 1000. The syntax is as follows −alter table yourTableName auto_increment=1000;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table PrimaryKey1000Demo    -> (    -> ProductId int auto_increment,    -> PRIMARY KEY(ProductId)    -> ); Query OK, 0 rows affected (0.56 sec)Now here is the query that will update the primary key to start from 1000 −mysql> alter table PrimaryKey1000Demo auto_increment=1000; Query OK, 0 rows affected (0.20 ... Read More

You need to store the date as the complete date time rather than storing only month and year. If you declare as a datetime then you can extract the month and year using MONTH() and YEAR() function from MySQL.The syntax is as follows −select MONTH(yourDateTimeColumnName) as anyVariableName1, YEAR(yourDateTimeColumnName) as anyVariableName2 from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OnlyMonthandYear    -> (       -> DueDateTime datetime    -> ); Query OK, 0 rows affected (0.56 sec)Insert date in the table using insert command. ... Read More

Let us create a table to sort date and time in ascending order. The query to create a table is as follows −mysql> create table SortByDateAndTime    -> (    -> UserId int,    -> UserName varchar(100),    -> IssueDate date,    -> IssueTime time    -> ); Query OK, 0 rows affected (0.60 sec)Insert the records in the table using insert command. The query is as follows −mysql> insert into SortByDateAndTime values(1, 'John', '2018-12-16', '10:30'); Query OK, 1 row affected (0.14 sec) mysql> insert into SortByDateAndTime values(2, 'Bob', '2018-12-16', '10:10'); Query OK, 1 row affected (0.14 sec) ... Read More

Calculate the average of numbers in a column with the help of MySQL aggregate function AVG().The syntax is as follows −select avg(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table AverageCalculateDemo    −> (       −> SubjectMarks int    −> ); Query OK, 0 rows affected (0.67 sec)The following is the query to insert some records into the table −mysql> insert into AverageCalculateDemo values(70); Query OK, 1 row affected (0.14 sec) mysql> insert into AverageCalculateDemo values(80); Query OK, 1 row affected ... Read More

The reset the primary key of a table means to reset the auto_increment property to 1. The syntax is as follows to reset the primary key of a table.alter table yourTableName auto_increment = 1;To understand, let us create a table −mysql> create table ResetPrimaryKey −> (    −> Id int auto_increment,    −> PRIMARY KEY(Id) −> ); Query OK, 0 rows affected (0.59 sec)Insert some records into the table. The query to insert records is as follows −mysql> insert into ResetPrimaryKey values(); Query OK, 1 row affected (0.18 sec) mysql> insert into ResetPrimaryKey values(); Query OK, 1 row affected ... Read More

You can maintain the order in MySQL IN query with the help of field command. The syntax is as follows −select *from yourTableName anyVariableName where anyVariableName.yourColumnName in(value1, value2, ......N) order by field(anyVariableName.yourColumnName, value1, value2, ......N);To implement the above syntax let us create a table −mysql> create table OrderInDemo    −> (       −> Id int,       −> Name varchar(100),       −> Age int    −> ); Query OK, 0 rows affected (1.24 sec)Now let us insert some records in the table. The query to insert records are as follows −mysql> insert into OrderInDemo values(90, ... Read More