To display current configuration variables, you can use show command. The syntax is as follows −show variables;You can further rewrite the above syntax with LIKE operator. The syntax is as follows −show variables like ‘%anyStringValue%’;The query is as follows displaying an example to fetch some of the configuration variables −mysql> show variables like '%max%';Output+------------------------------------------------------+----------------------+ | Variable_name                                        | Value                | +------------------------------------------------------+----------------------+ | binlog_max_flush_queue_time                         ... Read More

The MySQL BOOLEAN and BOOL both are equivalent to TINYINT(1). Whenever you create a column using BOOLEAN and BOOL data type, MySQL implicitly convert the BOOLEAN and BOOL to TINYINT(1). The BOOLEAN and BOOL are equivalents of TINYINT(1), since they are synonyms.Create a table using BOOLEAN data type. The query to create a table.mysql> create table BooleanDemo    -> (    -> IsOn BOOLEAN    -> ); Query OK, 0 rows affected (0.58 sec)Now check internal structure of the above table. The query is as follows −mysql> show create table BooleanDemo;Output+-------------+----------------------------------------------------------------------------------------------------------------------------------+ | Table       | Create Table   ... Read More

For this, you can insert using MySQL dual table. Let us create a table to understand the concept conditional insert. The query to create a table is as follows −mysql> create table ConditionalInsertDemo    -> (    -> UserId int,    -> TotalUser int,    -> NumberOfItems int    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ConditionalInsertDemo values(101, 560, 780); Query OK, 1 row affected (0.19 sec) mysql> insert into ConditionalInsertDemo values(102, 660, 890); Query OK, 1 row affected (0.20 sec) ... Read More

BOOL and BOOLEAN both acts like TINYINT(1). You can say that both are synonyms for TINYINT(1).BOOLEANHere is an example of BOOLEAN. The query to create a table with column boolean type.mysql> create table Demo -> ( -> isVaidUser boolean -> ); Query OK, 0 rows affected (1.08 sec)The query to insert records in the table using insert command is as follows −mysql> insert into Demo values(true); Query OK, 1 row affected (0.19 sec) mysql> insert into Demo values(0); Query OK, 1 row affected (0.17 sec)Display all values from the table ... Read More

You can use TRUNCATE() function from MySQL to format number to 2 decimal places. The syntax is as follows −SELECT TRUNCATE(yourColumnName, 2) 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 FormatNumberTwoDecimalPlace    -> (    -> Number float    -> ); 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 FormatNumberTwoDecimalPlace values(123.456); Query OK, 1 row affected (0.13 sec) mysql> insert into FormatNumberTwoDecimalPlace values(1.6789); Query OK, 1 ... Read More

You can increment a database using update command. The syntax is as follows −UPDATE yourTableName set yourColumnName=yourColumnName+1 where condition;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table IncrementBy1    -> (    -> Id int,    -> Name varchar(100),    -> CounterLogin int    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records using insert command. The query to insert records in the table is as follows −mysql> insert into IncrementBy1 values(100, 'John', 30); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

You can use phpMyAdmin, since it is one of the best free tools. This can be used for every system with PHP and MySQL. It is a free and open source administration tool for MySQL and MariaDB. PHPMYADMINHere is the URL to download −https://www.phpmyadmin.net/downloads/The following are the features of phpMyAdmin −Open source toolMySQL and MariaDB database management.One of the most popular MySQL administration toolsEasily Import data from CSV and SQLExport data to various formats. These include : CSV, SQL, XML, PDF, Word, Excel, LaTeX, etc.Administering multiple serversYou can also use EMMA. This is also a light-weight application.Read More

You can create JSON format using group_concat() function from MySQL. The syntax is as follows −SELECT yourColumnName1, GROUP_CONCAT(CONCAT('{anytName:"', yourColumnName, '", anyName:"', yourColunName, '"}')) anyVariableName from yourTableName group by yourColumnName1;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table JsonFormatDemo -> ( -> UserId int, -> UserName varchar(100), -> UserEmail varchar(100) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert ... Read More

To select the last 6 months records from news table, use the date_sub() function from MySQL since news records are arranged according to date.The syntax is as follows −select *from yourTableName where yourDateTimeColumnName >= date_sub(now(), interval 6 month);To understand the above concept, let us first create a NEWS table with only NEWS ID and the date on which it published −mysql> create table Newstable -> ( -> NewsId int, -> NewsDatetime datetime -> ); Query OK, 0 rows affected (0.66 sec)Insert records in the table using insert command. ... Read More

You can use prepare statement to Reset MySQL AutoIncrement using a MAX value from another table.The following is the syntax −set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1); SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2 AUTO_INCREMENT=', @anyVariableName1); PREPARE yourStatementName FROM @anyVariableName2; execute yourStatementName;The above syntax will reset MySQL auto_increment using a maximum value from another table. To understand the above syntax, let us create two tables. The first table will contain the records and the second table will use the maximum value from the first table and use for an auto_increment property.The query to create a table is as follows −mysql> create table FirstTableMaxValue ... Read More