Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
To toggle a value of an int field, you can use update command with if(). The syntax is as follows −update yourTableName set yourColumnName = IF(yourColumnName = 0, 1, 0);To understand the above toggle syntax, create a table with some int value. The query to create a table is as follows −mysql> create table ToggleDemo −> ( −> IsOnOrOff int −> ); Query OK, 0 rows affected (0.53 sec)Let us insert int values in the table with the help of insert command. The query is as follows −mysql> insert into ToggleDemo values(1); Query OK, 1 ... Read More
You can find the highest number in a column with the help of aggregate function MAX. The syntax is as follows −select max(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table with an int column. The following is the query to create a table.mysql> create table HighestNumberDemo −> ( −> BigNumber int −> ); Query OK, 0 rows affected (0.87 sec)Now insert some values in the table. The query to insert records are as follows −mysql> insert into HighestNumberDemo values(1234); Query OK, 1 row affected (0.43 sec) mysql> insert ... 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
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 change table engine with the help of alter command. The syntax is as follows −alter table yourTableName ENGINE = yourEngineName;To understand the above syntax let us create a table with engine MyISAM. Later you can change any other engine. The following is the query to create a table.mysql> create table ChangeEngineTableDemo −> ( −> MovieId int, −> MovieName varchar(100), −> IsPopular bool −> )ENGINE = 'MyISAM'; Query OK, 0 rows affected (0.37 sec)Look at the above query, the table engine is MyISAM, now you can change it to any other engine. Here, we will change ... Read More
You can get timestamp with the help of current_timestamp, now() and current_timestamp().Case 1 − Using current_timestamp()The query is as follows −mysql> SELECT CURRENT_TIMESTAMP();The following is the output displaying timestamp −+---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2018-11-29 16:09:31 | +---------------------+ 1 row in set (0.00 sec)Case 2 − Using now()The query is as follows −mysql> select now();The following is the output −+---------------------+ | now() | +---------------------+ | 2018-11-29 16:09:38 | +---------------------+ 1 row in set (0.00 sec)You can get integer UNIX timestamp with the help of below query −mysql> select unix_timestamp();The following is the ... Read More
Use the STR_TO_DATE() function from MySQL to set a date format for displaying DD/MM/YYYY date. The syntax is as follows −SELECT STR_TO_DATE(yourColumnName, ’%d/%m/%Y) as anyVariableName from yourTableName.To understand the above syntax, let us create a table −mysql> create table DateFormatDemo −> ( −> IssueDate varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)Inserting some string dates into the table. The query to insert date is as follows −mysql> insert into DateFormatDemo values('26/11/2018'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFormatDemo values('27/11/2018'); Query OK, 1 row affected (0.18 sec) mysql> ... Read More
To list all triggers in a MySQL database, you can use the SHOW command. The query is as follows −mysql> show triggers;The following is the output −+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode ... Read More
To cast DATETIME as a DATE in MySQL, use the CAST() function. The syntax is as follows −select cast(yourColumnName as Date) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table −mysql> create table ConvertDatetimeToDate −> ( −> YourDatetime datetime −> ); Query OK, 0 rows affected (0.95 sec)Inserting datetime into the table with the help of insert command. The query is as follows −mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.17 sec) mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval -1 day)); Query OK, 1 row affected (0.15 sec) ... Read More
You can use SHOW VARIABLES command to display current configuration variables. The syntax is as follows −SHOW VARIABLES;If you want any specific information, then implement the LIKE operator. The syntax is as follows −SHOW VARIABLES LIKE ‘%AnySpecificInformation%’;Now we will implement the above syntax −mysql> show variables like '%variable%';The following is the output −+--------------------------------+------------------------------------------------------------------------------------------+ | Variable_name | Value ... Read More