You can use $or operator for this. Let us first create a collection with documents −> db.applyConditionDemo.insertOne({"StudentName":"Larry", "StudentAge":21, "StudentMarks":45}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80b78623186894665ae36") } > db.applyConditionDemo.insertOne({"StudentName":"Sam", "StudentAge":23, "StudentMarks":55}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80b87623186894665ae37") } > db.applyConditionDemo.insertOne({"StudentName":"David", "StudentAge":21, "StudentMarks":65}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80b95623186894665ae38") } > db.applyConditionDemo.insertOne({"StudentName":"Carol", "StudentAge":24, "StudentMarks":78}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80ba3623186894665ae39") } > db.applyConditionDemo.insertOne({"StudentName":"Chris", "StudentAge":21, "StudentMarks":88}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80bae623186894665ae3a") } > db.applyConditionDemo.insertOne({"StudentName":"Robert", "StudentMarks":98}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb80c3d623186894665ae3b") }Following is the ... Read More

You can use $pull operator for this. Let us first create a collection with documents −> db.removeNullDemo.insertOne( ... { ...    "_id" : 1, ...    "StudentDetails" : [ ...       { ...          "FirstName": "John", ...          "LastName":"Smith", ... ...       }, ...       { ...          "Age":21 ...       }, ...       null ... ...    ] ... } ... ); { "acknowledged" : true, "insertedId" : 1 } > db.removeNullDemo.insertOne( ... { ...    "_id" : 2, ... ... Read More

This SHOW DBS command won’t show the databases because you may have not created a document for a collection. If you will create document for a collection then the created database will be visible.Let us implement the above concept and create a database −> use web; switched to db webFollowing is the query to show all databases −> show dbs;This will produce the following output −admin 0.001GB config 0.000GB local 0.000GB my 0.001GB sample 0.001GB sampleDemo 0.000GB studentSearch 0.000GB test 0.010GB university 0.000GBAbove, the WEB database is not visible since we haven’t created a collection in the same database.In order ... Read More

Too return a specific field, use the find() method in MongoDB. Let us first create a collection with documents −> db.specificFieldDemo.insertOne({"FirstName":"John", "LastName":"Doe"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb8019a623186894665ae31") } > db.specificFieldDemo.insertOne({"FirstName":"John", "LastName":"Smith"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb801ab623186894665ae32") } > db.specificFieldDemo.insertOne({"FirstName":"David", "LastName":"Miller"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb801b3623186894665ae33") } > db.specificFieldDemo.insertOne({"FirstName":"Sam", "LastName":"Williams"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cb801bf623186894665ae34") }Following is the query to display all documents from the collection with the help of find() method −> db.specificFieldDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cb8019a623186894665ae31"),   ... Read More

To get distinct including whitespace, you can use below syntax −SELECT DISTINCT replace(yourColumnName, ' ', '') FROM yourTableName;Let us first create a table:mysql>create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(20) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql>insert into DemoTable(Name) values('John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values(' John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.09 sec) mysql>insert into DemoTable(Name) values('Sam'); Query OK, 1 row affected (0.15 sec) ... Read More

Use SUBSTRING() to get first N characters from a MySQL column. Let us first create a table −mysql>create table DemoTable (    Information text ); Query OK, 0 rows affected (2.63 sec)Insert records in the table using insert command −mysql>insert into DemoTable values('MySQL is a structured query language'); Query OK, 1 row affected (0.13 sec)Following is the query to display all records from the table using select statement −mysql>select *from DemoTable;This will produce the following output −+--------------------------------------+ | Information                         | +--------------------------------------+ | MySQL is a structured query ... Read More

You can use STR_TO_DATE() to convert US date format to MySQL format in INSERT. Let us first create a table −mysql>create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ShippingDatetime varchar(200) ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. Here, we are using INSERT to convert US date format −mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('01-31-2012 01:23', '%m-%d-%Y %H:%i')); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('12-01-2018 04:56', '%m-%d-%Y %H:%i')); Query OK, 1 row affected (0.19 sec) mysql>insert into DemoTable(ShippingDatetime) values(STR_TO_DATE('04-17-2019 10:10', '%m-%d-%Y %H:%i')); Query OK, 1 row ... Read More

To check if field exists and then to return the result set, you can use the below syntax −show columns from yourTableName where field='yourColumnName';Let us first create a table −mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserFirstName varchar(20),    UserLastName varchar(20),    UserAge int,    UserAddress varchar(200),    UserCountryName varchar(20) ); Query OK, 0 rows affected (0.67 sec)Here is the query to check if field exists and then return the result set −mysql> show columns from DemoTable where field='UserCountryName';This will produce the following output −+-----------------+-------------+------+-----+---------+-------+ | Field           | ... Read More

In order to know the last MySQL error, you can use SHOW command −SHOW ERRORS;Or you can use another syntax −SHOW WARNINGS;Here, we are creating a table that displays an error and then we will find out how to know the last MySQL error. Here, the error occurs because we have intentionally written incorrect create table statement −mysql> create table DemoTable(Id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table DemoTable(Id int)' at line 1Following is the query to ... Read More

To create database only once, use the below syntax.CREATE DATABASE IF NOT EXISTS yourDatabaseName;To create table only once, use the below syntax −CREATE TABLE IF NOT EXISTS yourTableName (    yourColumnName yourDatatype,    .    .    .    N );Let us implement both the above syntaxes to create database and table only once if does not already exist −mysql> CREATE DATABASE IF NOT EXISTS login; Query OK, 1 row affected (0.23 sec)Above query creates a database successfully.Following is the query to create a table −mysql> CREATE TABLE IF NOT EXISTS DemoTable (    Id int ); Query OK, 0 rows affected (0.56 sec)Above query creates a table successfully.