To get the number of columns, use the aggregate function count(*) with information_schema table from MySQL. The syntax is as follows to find the number of columns −SELECT COUNT(*) as anyVariableName from INFORMATION_SCHEMA.COLUMNS where table_schema = ’yourDatabaseName’ and table_name = ’yourTableName’;To understand the above syntax, let us create a table with some columns. The following is the query to create a table −mysql> create table CountColumns −> (    −> Bookid int,    −> BookName varchar(200),    −> BookAuthorName varchar(200),    −> BookPublishedDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Now, we have total 4 columns in my ... 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

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

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

You can typecast NULL to 0 with the help of IFNULL() function. The syntax is as follows −select ifnull(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us first create a table −mysql> create table TypecastDemo    −> (       −> AccountNumber int    −> ); Query OK, 0 rows affected (0.84 sec)Let us insert some records with NULL value. The query to insert records is as follows −mysql> insert into TypecastDemo values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into TypecastDemo values(1234); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

The NOW() function gives current datetime as a timestamp while CURDATE() gives only current date, not time.Now let us work on both the functions with the help of select statement. The query is as follows −The following is a demo of NOW() function −mysql> select NOW();The following is the output −+---------------------+ |             now() | +---------------------+ | 2018-11-27 15:17:01 | +---------------------+ 1 row in set (0.00 sec)A demo of CURDATE().mysql> select CURDATE();The following is the output that displays only date, not time −+------------+ | curdate() | +------------+ | 2018-11-27 | +------------+ 1 row in set ... Read More

To get the random integer range, use the rand() function. The query to create a table −mysql> create table RandomIntegerDemo −> (    −> Number int −> ); Query OK, 0 rows affected (0.61 sec)Inserting records into table. The query is as follows −mysql> insert into RandomIntegerDemo values(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14); Query OK, 14 rows affected (0.14 sec) Records: 14 Duplicates: 0 Warnings: 0Now you can display all records with the help of select statement. The query is as follows −mysql> select *from RandomIntegerDemo;The following is the output displaying integers ... Read More

To add leading zero to some value, use the LPAD() function of MySQL. The syntax is as follows −select lpad(yourColumnName, lengthofColumnValue+1, 0) from yourTableName;Here is an example of LPAD().mysql> select lpad('98765432', 9, 0);The following is the output −+----------------------+ | lpad('98765432', 9, 0) | +----------------------+ | 098765432            | +----------------------+ 1 row in set (0.00 sec)To check it in a live example, let us first create a table −mysql> create table leadingZeroDemo −> (    −> Id varchar(200) −> ); Query OK, 0 rows affected (0.63 sec)Now insert some records into the table with the help of ... Read More

You can get the MySQL table columns data type with the help of "information_schema.columns".The syntax is as follows −SELECT DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'To understand the above syntax, let us first create a table −mysql> create table DataTypeDemo −> (    −> Id int,    −> Address varchar(200),    −> Money decimal(10, 4) −> ); Query OK, 0 rows affected (0.60 sec)Apply the above syntax to get the MySQL columns data type. The query is as follows −mysql> select data_type from information_schema.columns where table_schema = 'business' and able_name = 'DataTypeDemo';The following is the output ... Read More

To add a day to datetime field, use the DATE_ADD() function. The syntax is as follows −SELECT DATE_ADD(yourColumnName, interval yourIntegerValue day) as anyVariableName from yourTableName;Let us first create a table −mysql> create table AddOneDayDemo −> (    −> YourDay datetime −> ); Query OK, 0 rows affected (1.37 sec)Insert current date with the help of curdate() and after that use date_add() function to add a day.To insert a day into the table, the following is the query −mysql> insert into AddOneDayDemo values(curdate()); Query OK, 1 row affected (0.17 sec)Display records with the help of select statement. The query is as ... Read More