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 175 of 355
MySQL query to update different fields based on a condition?
Let us first create a −mysql> create table DemoTable1436 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert −mysql> insert into DemoTable1436(Name) values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable1436(Name) values('Bob'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1436(Name) values('David'); Query OK, 1 row affected ...
Read MoreOrder dates in MySQL with the format "01 August 2019"?
To display dates like “01 August 2019”, use ORDER BY STR_TO_DATE(). Let us first create a −mysql> create table DemoTable1435 -> ( -> DueDate varchar(60) -> ); Query OK, 0 rows affected (1.08 sec)Insert some records in the table using insert −mysql> insert into DemoTable1435 values('01 August 2019'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable1435 values('01 Feb 2018'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1435 values('31 Jan 2017'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1435 values('01 March 2019'); Query OK, 1 row affected (0.11 sec)Display ...
Read MoreInsert current time minus 1 hour to already inserted date-time records in MYSQL
For subtracting dates, use MySQL DATE_SUB(). Let us first create a −mysql> create table DemoTable1434 -> ( -> ArrivalDatetime datetime -> ); Query OK, 0 rows affected (3.14 sec)Insert some records in the table using insert −mysql> insert into DemoTable1434 values('2019-09-30 21:10:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1434 values('2018-09-30 22:20:40'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable1434 values('2017-09-30 23:10:00'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select −mysql> select * from DemoTable1434;This will produce the following output −+---------------------+ | ArrivalDatetime ...
Read MoreFetch specific rows from a MySQL table with duplicate column values (names)?
Let us first create a −mysql> create table DemoTable1431 -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(20), -> EmployeeCountryName varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert −mysql> insert into DemoTable1431(EmployeeName, EmployeeCountryName) values('Adam Smith', 'AUS'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1431(EmployeeName, EmployeeCountryName) values('Chris Brown', 'US'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1431(EmployeeName, EmployeeCountryName) values('John Doe', 'UK'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1431(EmployeeName, EmployeeCountryName) values('Chris Brown', 'AUS'); Query ...
Read MoreAppend special characters to column values in MySQL
Let us first create a −mysql> create table DemoTable1626 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.37 sec)Insert some records in the table using insert −mysql> insert into DemoTable1626 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1626 values('Bob'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable1626 values('Robert'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select −mysql> select * from DemoTable1626;This will produce the following output −+--------+ | Name | +--------+ | Chris | | Bob | | ...
Read MoreSelect count of values (Yes, No) with same ids but different corresponding records in MySQL?
For this, you can use SUM() along with CASE statement. Let us first create a −mysql> create table DemoTable1430 -> ( -> EmployeeId int, -> isMarried ENUM('YES', 'NO') -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert −mysql> insert into DemoTable1430 values(1001, 'Yes'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1430 values(1001, 'No'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1430 values(1001, 'Yes'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1430 values(1001, 'Yes'); Query OK, 1 row affected (0.16 sec)Display ...
Read MoreFetch date records comparing with the current date's day and month in MySQL
For this, use MONTH() and DAY(). Let us first create a −mysql> create table DemoTable1429 -> ( -> AnniversaryDate date -> );Insert some records in the table using insert −mysql> insert into DemoTable1429 values('2019-09-29'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1429 values('2018-09-27'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1429 values('2016-09-28'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1429 values('2015-09-29'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select −mysql> select * from DemoTable1429;This will produce the following output −+-----------------+ | AnniversaryDate ...
Read MoreCount the number of comma's in every record from a comma-separated value column in MySQL
Let us first create a −mysql> create table DemoTable1510 -> ( -> Value varchar(50) -> ); Query OK, 0 rows affected (6.75 sec)Insert some records in the table using insert −mysql> insert into DemoTable1510 values('20, 35'); Query OK, 1 row affected (0.57 sec) mysql> insert into DemoTable1510 values('45, 67, 89'); Query OK, 1 row affected (0.99 sec) mysql> insert into DemoTable1510 values('90, 97, 101, 190'); Query OK, 1 row affected (1.15 sec)Display all records from the table using select −mysql> select * from DemoTable1510;This will produce the following output −+---------------+ | Value | ...
Read MoreFilter column value by the first character in MySQL
You can use LEFT() from MySQL. Let us first create a −mysql> create table DemoTable1428 -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(20) -> ); Query OK, 0 rows affected (1.05 sec)Insert some records in the table using insert −mysql> insert into DemoTable1428(EmployeeName) values('Chris Brown'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1428(EmployeeName) values('Bob Brown'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1428(EmployeeName) values('John Smith'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1428(EmployeeName) values('David Miller'); Query OK, 1 row affected (0.18 sec) ...
Read MoreExclude rows based on column value when another duplicate column value is found in MySQL?
For this, you can use subquery. Let us first create a −mysql> create table DemoTable1427 -> ( -> StudentId int, -> StudentMarks int -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert −mysql> insert into DemoTable1427 values(201, 89); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1427 values(201, 99); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1427 values(210, 98); Query OK, 1 row affected (0.16 sec)Display all records from the table using select −mysql> select * from DemoTable1427 ;This will produce the following output ...
Read More