You can use SELECT IF statement with OR. To understand select with OR, let us create a table. The query to create a table is as follows −mysql> create table EmployeeInformation    -> (    -> EmployeeId int,    -> EmployeeName varchar(100),    -> EmployeeStatus varchar(100)    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into EmployeeInformation values(1, 'Sam', 'FullTime'); Query OK, 1 row affected (0.23 sec) mysql> insert into EmployeeInformation values(2, 'Mike', 'PartTime'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

In MySQL, there is no way to truncate with condition. You cannot use truncate statement with where clause.If you want the condition, use delete command −DELETE FROM yourTableName WHERE youCondition;The above syntax is fine but if you want a faster solution, then DELETE is not good in comparison to Truncate. The advantage with truncate is that it does not write to the logs.Let us create a table. The query to create a table is as follows −mysql> create table DeleteDemo -> ( -> Id int, -> Name varchar(100) ... Read More

Whenever you retrieve datetime from a table, the datetime gives ‘YYYY-MM-DD’ format. If you want to change the output, then you need to use in-built date_format() from MySQL.The syntax is as follows −SELECT DATE_FORMAT(yourDatetimeColumnName, yourFormat) 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 UserDateFormat -> ( -> ProductId int, -> ProductDeliverDate datetime -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The ... Read More

SQLSQL is a type of language that can be used to utilize your database. It is a base language for databases like MySQL, SQL Server, Oracle etc. SQL stands for Structure Query Language and it can be used to utilize the relational database management system.This can also be used for accessing, manipulating and updating data in the database using some commands. The SQL commands are as follows −SELECTUPDATE, etc.SQL can also be used in the creation of schema as well as controlling the data access.MySQLMySQL is a relational database management system that utilizes the SQL command. MySQL provides the tools ... Read More

The number 2 and 1 in TINYINT(2) vs TINYINT(1) indicate the display width. There is no difference between tinyint(1) and tinyint(2) except the width.If you use tinyint(2) or even tinyint(1), the difference is the same. You can understand the above concept using zerofill option.tinyint(1) zerofilltinyint(2) zerofillLet us create a table. The query to create a table is as follows −mysql> create table tinyIntDemo    -> (    -> Number1 tinyint(1) zerofill,    -> Number2 tinyint(2) zerofill    -> ); Query OK, 0 rows affected (0.62 sec)Insert record in the table using insert command. The query is as follows −mysql> insert ... Read More

You can use in-built function date_add() from MySQL. The syntax is as follows −UPDATE yourTableName SET yourDateColumnName=DATE_ADD(yourDateColumnName, interval 1 year);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UpdateDate -> ( -> Id int, -> DueDate datetime -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query to insert record is as follows −mysql> insert into UpdateDate values(1001, '2012-5-21'); Query OK, 1 row affected (0.17 ... Read More

To set user variable from result of query in MySQL, you need to move that variable into the assignment.To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table UserVariable -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserVariable values(101, 'John'); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

You can use alter command. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumNam enum(yourOldValue1, yourOldValue2, ....N, yourNewValue1, yourNewValue2, ....N);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> AllColors enum('RED', 'GREEN') -> ); Query OK, 0 rows affected (0.66 sec)Now you have two members in AllColors of enum type. If you want to add more members to your enum type, use the following query −mysql> alter table EnumDemo -> modify column ... Read More

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