Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 146 of 355
MySQL IF() to display custom YES or NO messages
Let us first create a table −mysql> create table DemoTable1850 ( OrderStatus varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1850 values('Yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1850 values('No'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1850 values('Yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1850 values('Yes'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1850; This will produce the ...
Read MoreMySQL query to replace null value with empty string in several columns while fetching data
For this, you can use IFNULL() or COALESCE(). Let us first create a table −mysql> create table DemoTable1849 ( ClientFirstName varchar(20), ClientLastName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1849 values('John', NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1849 values(NULL, 'Miller'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1849 values(NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1849 values('Chris', 'Brown'); Query OK, 1 row affected (0.00 sec)Display all ...
Read MoreAdd user defined value to a column in a MySQL query?
Let us first create a table −mysql> create table DemoTable1847 ( GameStatus ENUM('PENDING', 'COMPLETED', 'CANCELLED') ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1847 values('PENDING'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1847 values('COMPLETED'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1847 values('CANCELLED'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1847; This will produce the following output −+------------+ | GameStatus | +------------+ | PENDING ...
Read MoreSelecting records 15 days before today in MySQL?
For this, you can use the concept of INTERVAL and DATE_SUB(). Let us first create a table −mysql> create table DemoTable1845 ( ArrivalDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1845 values('2019-12-02'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1845 values('2019-11-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1845 values('2019-12-18'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1845 values('2019-12-25'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1845 values('2019-11-15'); Query ...
Read MoreCalculating percentage in a MySQL query and round off the result
For this, you can use CONCAT() and round(). Let us first create a table −mysql> create table DemoTable1844 ( Number int, TotalNumber int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1844 values(50, 500); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1844 values(80, 500); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1844 values(98, 500); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1844 values(45, 500); Query OK, 1 row affected (0.00 sec)Display all ...
Read MoreGet beginning and end date from a specific year in MySQL
For this, use MySQL YEAR() function. Let us first create a table −mysql> create table DemoTable1843 ( StartDate date, EndDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1843 values('2019-01-21', '2019-10-12'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1843 values('2018-10-12', '2018-12-31'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1843 values('2016-04-01', '2017-05-02'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1843;This will produce the ...
Read MoreMultiplying column with NULL row in MySQL?
To multiply with NULL row, you can use COALESCE(). Let us first create a table −mysql> create table DemoTable1842 ( NumberOfItems int, Amount int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1842 values(10, 40); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1842 values(20, 5); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1842 values(NULL, 10); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1842;This ...
Read MoreReturn records that do not have a value in a certain field with two SELECT statement in a single MySQL query
For this, you can use WHERE clause along with subquery. Let us first create a table −mysql> create table DemoTable1840 ( UserName varchar(20), UserType ENUM('GUEST', 'ADMIN') ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1840 values('Chris', 'Admin'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1840 values('David', 'Guest'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1840 values('Chris', 'Guest'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * ...
Read MoreWhat is the fastest way to insert a large number of rows into a MySQL table?
The syntax for the fastest way is given below. Here, we have used INSERT INTO just once and formed an optimized way −insert into yourTableName values(NULL, yourValue1', yourValue2), (NULL, yourValue1', yourValue2), ....N;Let us first create a table −mysql> create table DemoTable1839 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientAge int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1839 values(NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', ...
Read MoreSort search results based on substring position in MySQL
To sort search results based on substring position, use ORDER BY LOCATE(). Let us first create a table −mysql> create table DemoTable1838 ( Subject varchar(100) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1838 values('MongoDB MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1838 values('MySQL Java'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1838 values('JavaWithMySQL'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1838; This will ...
Read More