- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
517 Views
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
201 Views
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
424 Views
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
405 Views
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
377 Views
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
2K+ Views
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
389 Views
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
4K+ Views
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
47K+ Views
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
202 Views
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