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
Articles on Trending Technologies
Technical articles with clear explanations and examples
Is it possible to make an insert or an update in the same MySQL query?
Yes, use ON DUPLICATE KEY UPDATE. Let us first create a table −mysql> create table DemoTable(Id int NOT NULL PRIMARY KEY, Number int); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 2 rows affected (0.14 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE ...
Read MoreSet the NULL values to 0 and display the entire column in a new column with MySQL SELECT
For this, use IFNULL(). Let us first create a table −mysql> create table DemoTable (Value int); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> ...
Read MoreSet special characters for password while creating a new MySQL user?
To set special characters for a password, use the following syntax −create user 'yourUserName'@'yourHostName' identified by 'yourSpecialCharacterPassword';Let us implement the above syntax in order to create a new user and set password with special characters −mysql> create user 'Mike'@'localhost' identified by 'Mike_123456'; Query OK, 0 rows affected (0.35 sec)Let us check the table where MySQL user and host is stored −mysql> select user, host from MySQL.user;This will produce the following output. The new user created successfully −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | ...
Read MoreExecute MySQL query from the terminal without printing results?
Let us first create a table −mysql> create table DemoTable709 (Amount int); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable709 values(100); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable709 values(560); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable709 values(7800); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable709 values(1020); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable709;This will produce the following output -+--------+ | Amount | +--------+ | 100 ...
Read MoreDisplay records after a particular date in MySQL
Let us first create a table −mysql> create table DemoTable708 ( CustomerName varchar(100), ShippingDate date ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable708 values('John', '2019-01-21'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable708 values('Chris', '2019-03-24'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable708 values('Robert', '2019-04-26'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable708 values('David', '2019-07-22'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable708;This will produce the ...
Read MoreMySQL query to order by NULL values
Let us first create a table −mysql> create table DemoTable707 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(100), StudentMarks int ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values('John', 45); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values(NULL, 65); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values('Chris', 78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values(NULL, 89); Query OK, 1 row affected (0.19 sec) mysql> insert into ...
Read MoreMySQL query to get the current date records wherein one of the columns displays current date
To achieve this, following is the syntax wherein we have used DATE(NOW()) −select *from yourTableName where DATE(yourColumnName)=DATE(NOW());Let us first create a table −mysql> create table DemoTable706 ( UserId varchar(100), UserName varchar(100), UserSignupDate datetime ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable706 values('John1@gmail.com', 'John', '2019-01-31 12:45:22'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable706 values('Chris123@gmail.com', 'Chris', '2019-07-22 10:05:02'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable706 values('12Robert@gmail.com', 'Robert', '2019-06-22 11:25:22'); Query OK, 1 row affected (0.22 sec) mysql> insert into ...
Read MoreMySQL query to retrieve current date from a list of dates
Let’s say the current date is −2019-07-22Let us first create a table −mysql> create table DemoTable705 (ShippingDate datetime); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable705 values('2019-01-21 23:59:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable705 values('2019-07-22 00:00:30'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable705 values('2019-07-21 12:01:30'); Query OK, 1 row affected (0.44 sec)Display all records from the table using select statement −mysql> select *from DemoTable705;This will produce the following output -+---------------------+ | ShippingDate | ...
Read MoreGet count of how many times a string appears in a MySQL column?
Let us first create a table −mysql> create table DemoTable704 (SubjectName text); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable704 values('Introduction to MySQL'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable704 values('Introduction to MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable704 values('Introduction to MySQL'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable704 values('Introduction to Java'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable704 values('Introduction to MongoDB'); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreReturn 0 in a new column when record is NULL in MySQL?
For this, you can use CASE statement. Let us first create a table −mysql> create table DemoTable703 (Price int); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable703 values(102); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable703 values(null); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable703 values(0); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable703 values(500); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable703 values(100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable703 values(null); Query ...
Read More