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
Database Articles
Page 468 of 546
Get the second last row of a table in MySQL?
You need to use ORDER BY clause to get the second last row of a table in MySQL.The syntax is as follows.select *from yourTableName order by yourColumnName DESC LIMIT 1, 1;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table secondLastDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(10) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command.The query is as follows.mysql> insert into secondLastDemo(StudentName) values('Larry'); Query OK, 1 row affected (0.15 ...
Read MoreHandling optional/empty data in MongoDB?
In order to handle empty data, you can use $ne operator. Let us create a collection with documents. Following is the query>db.handlingAndEmptyDataDemo.insertOne({"StudentName":"John", "StudentCountryName":""}); { "acknowledged" : true, "insertedId" : ObjectId("5c9cbd5ca629b87623db1b12") } >db.handlingAndEmptyDataDemo.insertOne({"StudentName":"John", "StudentCountryName":null}); { "acknowledged" : true, "insertedId" : ObjectId("5c9cbd6ba629b87623db1b13") } > db.handlingAndEmptyDataDemo.insertOne({"StudentName":"John"}); { "acknowledged" : true, "insertedId" : ObjectId("5c9cbd71a629b87623db1b14") }Following is the query to display all documents from a collection with the help of find() method> db.handlingAndEmptyDataDemo.find().pretty();This will produce the following output{ "_id" : ObjectId("5c9cbd5ca629b87623db1b12"), "StudentName" : "John", "StudentCountryName" : "" } { "_id" : ObjectId("5c9cbd6ba629b87623db1b13"), "StudentName" : ...
Read MoreUpdating a MySQL column that contains dot (.) in its name?
If the MySQL column contains dot (.) in its name, then you need to use backticks around the column name. To understand the above concept, let us create a table. The query to create a table is as followsmysql> create table UpdateDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `User.FirstName.LastName` varchar(60) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into UpdateDemo(`User.FirstName.LastName`) values('John Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Adam Smith'); Query OK, ...
Read MoreHow to connect to my MongoDB table by command line?
In order to connect to my table by command line, you need to use db commanddb.yourCollectionName.find();Let’s say we have a database “sample” with some collections. First check the current database> use sample; switched to db sample > db; Sample Now we have reached the database sample. The database “sample” is having the following collections: > show collections;This will produce the following outputarraySizeErrorDemo basicInformationDemo copyThisCollectionToSampleDatabaseDemo deleteAllRecordsDemo deleteDocuments deleteDocumentsDemo deleteSomeInformation documentWithAParticularFieldValueDemo employee findListOfIdsDemo findSubstring getAllRecordsFromSourceCollectionDemo getElementWithMaxIdDemo internalArraySizeDemo largestDocumentDemo makingStudentInformationClone oppositeAddToSetDemo prettyDemo returnOnlyUniqueValuesDemo selectWhereInDemo sourceCollection studentInformation sumOfValueDemo sumTwoFieldsDemo truncateDemo updateInformation userInformationHere is the correct way of connecting to a table i.e. collection. You ...
Read MoreMongoDB Query to select records having a given key?
To select records having a given key, you can use $exists operator. The syntax is as follows −db.yourCollectionName.find( { yourFieldName: { $exists : true } } ).pretty();To understand the above syntax, let us create a collection with the document. The query to create a collection with a document is as follows −> db.selectRecordsHavingKeyDemo.insertOne({"StudentName":"John", "StudentAge":21, "StudentMathMarks":78}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7be780f10143d8431e0f") } > db.selectRecordsHavingKeyDemo.insertOne({"StudentName":"Carol", "StudentMathMarks":89}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7bfc80f10143d8431e10") } > db.selectRecordsHavingKeyDemo.insertOne({"StudentName":"Sam", "StudentAge":26, "StudentMathMarks":89}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7c1280f10143d8431e11") } > db.selectRecordsHavingKeyDemo.insertOne({"StudentName":"Sam", "StudentMathMarks":98}); { "acknowledged" : true, ...
Read MoreMongoDB Query for boolean field as “not true”
You can use $ne(not equal) operator for this. The syntax is as follows −db.yourCollectionName.find({yourFieldName: {$ne: true}}).pretty();To understand the above syntax, let us create a collection with the document. The query to create a collection with a document is as follows −> db.queryForBooleanFieldsDemo.insertOne({"EmployeeName":"Larry", "EmployeeAge":24, "isOldEmployee":true}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7f7680f10143d8431e13") } > db.queryForBooleanFieldsDemo.insertOne({"EmployeeName":"Mike", "EmployeeAge":20, "isOldEmployee":false}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7f8680f10143d8431e14") } > db.queryForBooleanFieldsDemo.insertOne({"EmployeeName":"Sam", "EmployeeAge":23, "isOldEmployee":false}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7f9380f10143d8431e15") } > db.queryForBooleanFieldsDemo.insertOne({"EmployeeName":"David", "EmployeeAge":25, "isOldEmployee":true}); { "acknowledged" : true, "insertedId" : ObjectId("5c8b7fa280f10143d8431e16") } > db.queryForBooleanFieldsDemo.insertOne({"EmployeeName":"Carol", "EmployeeAge":27, "isOldEmployee":true}); ...
Read MoreHow do I delete blank rows in MySQL?
Use the delete command to delete blank rows in MySQL.The syntax is as followsdelete from yourTableName where yourColumnName=' ' OR yourColumnName IS NULL;The above syntax will delete blank rows as well as NULL row.To understand the concept, let us create a table.The query to create a table is as followsmysql> create table deleteRowDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into deleteRowDemo(StudentName) values('John'); Query OK, 1 row affected ...
Read MoreRemoving all collections whose name matches a string in MongoDB
To remove all collections whose name matches a string, you can follow some steps. Use for loop to iterate over all collections and find that particular collection name with certain string. After that, use the drop method to remove all collections.Let’s say we are using the database “sample”. The collections are as follows in sample database> show collections;This will produce the following outputarraySizeErrorDemo basicInformationDemo copyThisCollectionToSampleDatabaseDemo deleteAllRecordsDemo deleteDocuments deleteDocumentsDemo deleteMultipleIdsDemo deleteSomeInformation documentWithAParticularFieldValueDemo employee findListOfIdsDemo findMimimumElementInArrayDemo findSubstring getAllRecordsFromSourceCollectionDemo getElementWithMaxIdDemo insertDocumentWithDateDemo internalArraySizeDemo largestDocumentDemo makingStudentInformationClone oppositeAddToSetDemo prettyDemo returnOnlyUniqueValuesDemo selectWhereInDemo sourceCollection studentInformation sumOfValueDemo sumTwoFieldsDemo truncateDemo updateInformation userInformationNow remove all the collection names that match a ...
Read MoreHow to get element with max id in MongoDB?
To get the element with a max id, you can use the find() method. To understand the above concept, let us create a collection with the document. The query is as follows −> db.getElementWithMaxIdDemo.insertOne({"Name":"John", "Age":21}); { "acknowledged" : true, "insertedId" : ObjectId("5c8bbce480f10143d8431e1c") } > db.getElementWithMaxIdDemo.insertOne({"Name":"Larry", "Age":24}); { "acknowledged" : true, "insertedId" : ObjectId("5c8bbcec80f10143d8431e1d") } > db.getElementWithMaxIdDemo.insertOne({"Name":"David", "Age":23}); { "acknowledged" : true, "insertedId" : ObjectId("5c8bbcf580f10143d8431e1e") } > db.getElementWithMaxIdDemo.insertOne({"Name":"Chris", "Age":20}); { "acknowledged" : true, "insertedId" : ObjectId("5c8bbcfe80f10143d8431e1f") } > db.getElementWithMaxIdDemo.insertOne({"Name":"Robert", "Age":25}); { "acknowledged" : true, "insertedId" : ObjectId("5c8bbd0880f10143d8431e20") }Display all documents from ...
Read MoreCheck if MongoDB database exists?
There are two possibilities to check if MongoDB database exists.Case 1: The first possibility is that the MongoDB database exists i.e. it returns particular index.Case 2: The second possibility is that the MongoDB database does not exist i.e. it returns index -1.NOTE: An index starts from 0 and ends with (N-1) like an array.The syntax is as follows to check if MongoDB database exists.db.getMongo().getDBNames().indexOf("yourDatabaseName");Case 1: Let us implement the above syntax to check if MongoDB database exists. Following is the querydb.getMongo().getDBNames().indexOf("test");This will produce the following output6Look at the above sample output, we are getting 6 that means the database “test” ...
Read More