Database Articles - Page 388 of 671

You can use count(1). Let us first see the syntax −select count(1) from yourTableName;Let us first create a table −mysql> create table DemoTable    (    StudentName varchar(100)    ); Query OK, 0 rows affected (0.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName) values('John Smith'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(StudentName) values('Chris Brown'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName) values('David Miller'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(StudentName) values('Carol Taylor'); Query OK, 1 row affected (0.15 sec)Display all records from ... Read More

Use $max and $min operator along with aggregate framework to get the maximum and minimum value. Let us first create a collection with documents −> db.maxAndMinDemo.insertOne({"Value":98}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd698a357806ebf1256f129") } > db.maxAndMinDemo.insertOne({"Value":97}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd698af57806ebf1256f12a") } > db.maxAndMinDemo.insertOne({"Value":69}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd698b357806ebf1256f12b") } > db.maxAndMinDemo.insertOne({"Value":96}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd698b657806ebf1256f12c") } > db.maxAndMinDemo.insertOne({"Value":99}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd698b957806ebf1256f12d") }Following is the query to display all documents from a collection with the help of find() method ... Read More

For this, use the CHAR_LENGTH() function in MySQL. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Subject longtext    ); Query OK, 0 rows affected (1.17 sec)Now you can insert some records in the table using insert command −mysql> insert into DemoTable(Subject) values('MySQL, MongoDB'); Query OK,  1 row affected (0.20 sec) mysql> insert into DemoTable(Subject) values('MySQL, MongoDB'); Query OK,  1 row affected (0.17 sec) mysql> insert into DemoTable(Subject) values('MongoDB'); Query OK,  1 row affected (0.13 sec) mysql> insert into DemoTable(Subject) values('MySQL'); Query OK,  1 row affected (0.15 sec) Display all records from the table using select statement : mysql> select *from DemoTable;Output+----+---------------+ | Id | Subject | +----+---------------+ | 1 | MySQL, MongoDB | | 2 | MySQL, MongoDB | | ... Read More

We will create a stored procedure that does not change the variable value whenever the value is updated.Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value int    ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.13 sec) Display all records from the table using select statement : mysql> select *from DemoTable;Output+----+-------+ | Id | Value | +----+-------+ | 1 | 100 | +----+-------+ 1 row ... Read More

You can use $addToSet operator for this. Let us first create a collection with documents −> db.insertDataIntoArrayDemo.insertOne(    {       "UserDetails":[          {             "UserId" :"user121",             "userGroupMessage":[]          },          {             "UserId" :"user221",             "userGroupMessage":["Cool", "Good Morning"]          }       ]    } ); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd694e157806ebf1256f128") }Following is the query to display all documents from ... Read More

To perform SELECT with COUNT, use aggregate function COUNT(). Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(100),    Subject varchar(100)    ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Subject) values('John', 'MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name, Subject) values('John', 'Java'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name, Subject) values('Carol', 'MongoDB'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, Subject) values('Carol', ... Read More

To change data type from date to date/time, use alter command.alter table yourTableName change yourColumnName yourColumnName datetime;Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    LoginDate date    ); Query OK, 0 rows affected (1.26 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(LoginDate) values('2019-01-21'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable(LoginDate) values('2018-05-01'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(LoginDate) values('2017-12-31'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement ... Read More

To get MySQL LIKE statement, use the REGEX in MongoDB. Let us first create a collection with documents −> db.likeInMongoDBDemo.insertOne({"Name" : "Sam"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6922857806ebf1256f123") } > db.likeInMongoDBDemo.insertOne({"Name" : "John" }); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6923157806ebf1256f124") } > db.likeInMongoDBDemo.insertOne({"Name" : "Scott"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6924557806ebf1256f125") } > db.likeInMongoDBDemo.insertOne({"Name" : "Sean"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6924f57806ebf1256f126") } > db.likeInMongoDBDemo.insertOne({"Name" : "Samuel"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd6925857806ebf1256f127") }Following is the query to display all documents from a collection with ... Read More

To merge, use $setUnion operator. Let us first create a collection with documents −> db.mergeTwoArrayFieldDemo.insertOne({"NaturalNumbers":[1,2,3,8,10,20,30],"WholeNumbers":[0,1,2,63,78,20,45]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68e4057806ebf1256f11d") }Following is the query to display all documents from a collection with the help of find() method −> db.mergeTwoArrayFieldDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cd68e4057806ebf1256f11d"),    "NaturalNumbers" : [       1,       2,       3,       8,       10,       20,       30    ],    "WholeNumbers" : [       0,       1,       2,       63,       78,       20,       45    ] }Following is the query to merge two array field in MongoDB.> db.mergeTwoArrayFieldDemo.aggregate([    { "$project": {       "MergedArray": { "$setUnion": [ "$NaturalNumbers", "$WholeNumbers" ] }    }} ]);This will produce the following output −{ "_id" : ObjectId("5cd68e4057806ebf1256f11d"), "MergedArray" : [ 0, 1, 2, 3, 8, 10, 20, 30, 45, 63, 78 ] }

Let us first create a collection with documents −> db.findByObjectIdDemo.insertOne({"ClientName":"Larry", "ClientAge":23}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cd657806ebf1256f11a") } > db.findByObjectIdDemo.insertOne({"ClientName":"Chris", "ClientAge":26}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cdc57806ebf1256f11b") } > db.findByObjectIdDemo.insertOne({"ClientName":"David", "ClientAge":38, "isMarried":true}); {    "acknowledged" : true,    "insertedId" : ObjectId("5cd68cf657806ebf1256f11c") }Following is the query to display all documents from a collection with the help of find() method −> db.findByObjectIdDemo.find().pretty();This will produce the following output −{    "_id" : ObjectId("5cd68cd657806ebf1256f11a"),    "ClientName" : "Larry",    "ClientAge" : 23 } {    "_id" : ObjectId("5cd68cdc57806ebf1256f11b"),    "ClientName" : "Chris",    "ClientAge" : 26 } { ... Read More