DEV Community: Millie Molotov

MongoDB Command Shortcuts: The Ultimate Guide

Millie Molotov — Tue, 31 Dec 2024 16:06:07 +0000

This post is a comprehensive guide to the most important MongoDB commands and operations. It covers basic queries, CRUD operations, indexing, aggregation, and more advanced concepts.

1. MongoDB Basics

Connecting to MongoDB

mongo --host <hostname> --port <port> -u <username> -p <password> --authenticationDatabase <authDB>

Show Databases

show dbs;

Use a Database

use myDatabase;

Show Collections

show collections;

2. CRUD Operations

Create a Collection

db.createCollection("myCollection");

Insert Documents

db.myCollection.insertOne({ name: "John", age: 30 });
db.myCollection.insertMany([{ name: "Jane", age: 25 }, { name: "Doe", age: 22 }]);

Read Documents

db.myCollection.find();
db.myCollection.find({ name: "John" });

Update Documents

db.myCollection.updateOne({ name: "John" }, { $set: { age: 31 } });
db.myCollection.updateMany({ age: { $lt: 30 } }, { $set: { status: "young" } });

Delete Documents

db.myCollection.deleteOne({ name: "John" });
db.myCollection.deleteMany({ age: { $lt: 25 } });

3. Querying Documents

Basic Queries

db.myCollection.find({ name: "John" });
db.myCollection.find({ age: { $gt: 25 } });

Projection

db.myCollection.find({}, { name: 1, age: 1 });

Sorting

db.myCollection.find().sort({ age: -1 });

Limiting and Skipping

db.myCollection.find().limit(5);
db.myCollection.find().skip(5).limit(5);

4. Updating Documents

Update Operators

$set: Sets the value of a field

db.myCollection.updateOne({ name: "John" }, { $set: { age: 31 } });

$unset: Removes a field

db.myCollection.updateOne({ name: "John" }, { $unset: { age: "" } });

$inc: Increments the value of a field

db.myCollection.updateOne({ name: "John" }, { $inc: { age: 1 } });

$push: Adds an item to an array

db.myCollection.updateOne({ name: "John" }, { $push: { hobbies: "reading" } });

5. Deleting Documents

Delete Operators

deleteOne: Deletes a single document

db.myCollection.deleteOne({ name: "John" });

deleteMany: Deletes multiple documents

db.myCollection.deleteMany({ age: { $lt: 25 } });

6. Indexes

Creating Indexes

db.myCollection.createIndex({ name: 1 });

Viewing Indexes

db.myCollection.getIndexes();

Dropping Indexes

db.myCollection.dropIndex("name_1");

7. Aggregation

Basic Aggregation

db.myCollection.aggregate([
    { $match: { status: "active" } },
    { $group: { _id: "$age", total: { $sum: 1 } } }
]);

Common Aggregation Stages

$match: Filters documents

{ $match: { status: "active" } }

$group: Groups documents by a specified field

{ $group: { _id: "$age", total: { $sum: 1 } } }

$sort: Sorts documents

{ $sort: { total: -1 } }

$project: Reshapes documents

{ $project: { name: 1, age: 1 } }

8. Data Modeling

Embedding Documents

let post = {
    title: "Post Title",
    content: "Post Content",
    comments: [
        { user: "John", comment: "Great post!" },
        { user: "Jane", comment: "Thanks for sharing!" }
    ]
};

Referencing Documents

let user = { name: "John" };
let post = { title: "Post Title", content: "Post Content", userId: user._id };

9. Replication

Setting Up a Replica Set

rs.initiate();
rs.add("mongodb1.example.net:27017");
rs.add("mongodb2.example.net:27017");
rs.add("mongodb3.example.net:27017");

Checking Replica Set Status

rs.status();

10. Sharding

Enabling Sharding

sh.enableSharding("myDatabase");

Sharding a Collection

sh.shardCollection("myDatabase.myCollection", { shardKey: 1 });

11. Transactions

Starting a Transaction

const session = db.getMongo().startSession();
session.startTransaction();

Committing a Transaction

session.commitTransaction();
session.endSession();

Aborting a Transaction

session.abortTransaction();
session.endSession();

12. Security

Creating a User

db.createUser({
    user: "myUser",
    pwd: "myPassword",
    roles: [{ role: "readWrite", db: "myDatabase" }]
});

Authenticating a User

db.auth("myUser", "myPassword");

13. Best Practices

Use Indexes: Ensure that your queries are efficient by using indexes.
Data Modeling: Choose the right data model (embedding vs. referencing) based on your use case.
Backup Regularly: Regularly backup your data to prevent data loss.
Monitor Performance: Use monitoring tools to keep an eye on the performance of your MongoDB instance.
Secure Your Database: Implement proper authentication and authorization mechanisms.

Conclusion

This post summarizes key concepts, commands, and operations for working with MongoDB. Keep this as a quick reference while you're working with MongoDB!

How to retrieve values from all types of HTML Inputs in JavaScript

Millie Molotov — Sun, 29 Dec 2024 16:08:29 +0000

This blog explains how to use JavaScript to retrieve values from different input types in an HTML form by their id.

Input Types and Retrieval Methods

1. Text Input

HTML Code:

  <input type="text" id="textInput">

JavaScript Code:

  const textValue = document.getElementById('textInput').value;

Explanation: Retrieves the value entered in the text input field.

2. Email Input

HTML Code:

  <input type="email" id="emailInput">

JavaScript Code:

  const emailValue = document.getElementById('emailInput').value;

Explanation: Retrieves the email entered by the user. This field expects a valid email format.

3. Password Input

HTML Code:

  <input type="password" id="passwordInput">

JavaScript Code:

  const passwordValue = document.getElementById('passwordInput').value;

Explanation: Retrieves the text entered in the password input field.

4. Number Input

HTML Code:

  <input type="number" id="numberInput">

JavaScript Code:

  const numberValue = document.getElementById('numberInput').value;

Explanation: Retrieves the number entered in the input field.

5. Date Input

HTML Code:

  <input type="date" id="dateInput">

JavaScript Code:

  const dateValue = document.getElementById('dateInput').value;

Explanation: Retrieves the selected date in YYYY-MM-DD format.

6. Radio Button

HTML Code:

  <input type="radio" id="radio1" name="radioInput" value="Option 1">
  <input type="radio" id="radio2" name="radioInput" value="Option 2">

JavaScript Code:

  const radioValue = document.querySelector('input[name="radioInput"]:checked')?.value || null;

Explanation: Retrieves the value of the selected radio button. If none is selected, it returns null.

7. Checkbox

HTML Code:

  <input type="checkbox" id="checkbox1" value="Option A">
  <input type="checkbox" id="checkbox2" value="Option B">

JavaScript Code:

  const checkboxValues = Array.from(document.querySelectorAll('input[type="checkbox"]:checked')).map(cb => cb.value);

Explanation: Retrieves all selected checkbox values as an array. If no checkboxes are selected, it returns an empty array.

8. Dropdown (Select)

HTML Code:

  <select id="dropdownInput">
    <option value="Option 1">Option 1</option>
    <option value="Option 2">Option 2</option>
  </select>

JavaScript Code:

  const dropdownValue = document.getElementById('dropdownInput').value;

Explanation: Retrieves the selected value from the dropdown menu.

9. Textarea

HTML Code:

  <textarea id="textareaInput"></textarea>

JavaScript Code:

  const textareaValue = document.getElementById('textareaInput').value;

Explanation: Retrieves the text entered in the textarea field.

Example: Complete Script

Here is an example that retrieves values from all the input types in a form:

function submitForm() {
  const data = {
    textInput: document.getElementById('textInput').value,
    emailInput: document.getElementById('emailInput').value,
    passwordInput: document.getElementById('passwordInput').value,
    numberInput: document.getElementById('numberInput').value,
    dateInput: document.getElementById('dateInput').value,
    radioInput: document.querySelector('input[name="radioInput"]:checked')?.value || null,
    checkboxInput: Array.from(document.querySelectorAll('input[type="checkbox"]:checked')).map(cb => cb.value),
    dropdownInput: document.getElementById('dropdownInput').value,
    textareaInput: document.getElementById('textareaInput').value
  };

  console.log(data);
  alert('Form data submitted! Check the console for details.');
}

How to Use

Add the HTML form elements to your webpage with the specified id attributes.
Use the corresponding JavaScript snippet to retrieve values from the inputs.
The collected data can be logged or sent to a server for further processing.

This documentation provides a clear reference for working with various input types in HTML and collecting their values using JavaScript.

SQL Filtering and Sorting with Real-life Examples

Millie Molotov — Mon, 23 Dec 2024 16:10:22 +0000

This blog explains the SQL clauses like WHERE, HAVING, ORDER BY, GROUP BY, and other related clauses using real-life examples with the employees and departments tables.

Tables Structure
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
LIMIT Clause
DISTINCT Clause
AND, OR, NOT Operators

Tables Structure

`employees` Table

emp_id	name	age	department_id	hire_date	salary
1	John Smith	35	101	2020-01-01	5000
2	Jane Doe	28	102	2019-03-15	6000
3	Alice Johnson	40	103	2018-06-20	7000
4	Bob Brown	55	NULL	2015-11-10	8000
5	Charlie Black	30	102	2021-02-01	5500

`departments` Table

dept_id	dept_name
101	HR
102	IT
103	Finance
104	Marketing

WHERE Clause

The WHERE clause is used to filter records based on specified conditions.

SQL Query

SELECT name, age, salary
FROM employees
WHERE age > 30;

Result

name	age	salary
John Smith	35	5000
Alice Johnson	40	7000
Bob Brown	55	8000

Explanation: The WHERE clause filters the rows to include only employees who are older than 30 years.

Example with `AND` Operator

SELECT name, age, salary
FROM employees
WHERE age > 30 AND salary > 5000;

Result

name	age	salary
Alice Johnson	40	7000
Bob Brown	55	8000

Explanation: The WHERE clause filters employees who are older than 30 and have a salary greater than 5000.

GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values into summary rows, like finding the number of employees in each department.

SQL Query

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Result

department_id	employee_count
101	1
102	2
103	1

Explanation: The GROUP BY clause groups employees by department_id and counts the number of employees in each department.

HAVING Clause

The HAVING clause is used to filter groups created by the GROUP BY clause. It works like the WHERE clause but is used after aggregation.

SQL Query

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5500;

Result

department_id	avg_salary
102	5750
103	7000

Explanation: The HAVING clause filters the groups based on the average salary of employees in each department. Only departments with an average salary greater than 5500 are included.

ORDER BY Clause

The ORDER BY clause is used to sort the result set by one or more columns. By default, it sorts in ascending order; to sort in descending order, use DESC.

SQL Query (Ascending Order)

SELECT name, salary
FROM employees
ORDER BY salary;

Result

name	salary
John Smith	5000
Charlie Black	5500
Jane Doe	6000
Alice Johnson	7000
Bob Brown	8000

Explanation: The result is sorted by salary in ascending order.

SQL Query (Descending Order)

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Result

name	salary
Bob Brown	8000
Alice Johnson	7000
Jane Doe	6000
Charlie Black	5500
John Smith	5000

Explanation: The result is sorted by salary in descending order.

LIMIT Clause

The LIMIT clause is used to specify the number of records to return from the result set. This is particularly useful for paging or limiting large result sets.

SQL Query

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Result

name	salary
Bob Brown	8000
Alice Johnson	7000
Jane Doe	6000

Explanation: The LIMIT clause restricts the output to only the top 3 highest-paid employees.

DISTINCT Clause

The DISTINCT clause is used to return only distinct (different) values in a result set, removing duplicates.

SQL Query

SELECT DISTINCT department_id
FROM employees;

Result

department_id
101
102
103

Explanation: The DISTINCT clause returns unique department_id values, eliminating duplicates.

AND, OR, NOT Operators

The AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause.

AND Operator

The AND operator is used to combine two or more conditions. The result will include only rows where all conditions are true.

SELECT name, age, salary
FROM employees
WHERE age > 30 AND salary > 5500;

Result

name	age	salary
Alice Johnson	40	7000
Bob Brown	55	8000

Explanation: The WHERE clause filters rows where both conditions (age > 30 and salary > 5500) are true.

OR Operator

The OR operator is used when only one of the conditions must be true.

SELECT name, age, salary
FROM employees
WHERE age < 30 OR salary > 7000;

Result

name	age	salary
Jane Doe	28	6000
Alice Johnson	40	7000
Bob Brown	55	8000

Explanation: The WHERE clause filters rows where either age < 30 or salary > 7000 is true.

NOT Operator

The NOT operator is used to exclude rows where a condition is true.

SELECT name, age, salary
FROM employees
WHERE NOT salary > 6000;

Result

name	age	salary
John Smith	35	5000
Charlie Black	30	5500
Jane Doe	28	6000

Explanation: The WHERE clause filters rows where salary > 6000 is false, meaning it returns employees earning 6000 or less.

Conclusion

This blog explains how to filter, group and sort data using SQL’s WHERE, HAVING, ORDER BY, GROUP BY, and other clauses with real-life examples from the employees and departments tables. Understanding these clauses is fundamental for writing efficient SQL queries, analyzing data, and managing databases effectively.

SQL Quick Reference: Simplifying Database Management

Millie Molotov — Sat, 21 Dec 2024 13:32:35 +0000

This blog comprehensively guides the most important SQL commands and operations. It covers basic queries, joins, subqueries, indexes, and more advanced concepts.

SQL Basics
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Joins
Subqueries
Indexes
Aggregation Functions
Grouping and Sorting
Transactions
Advanced SQL
Best Practices

SQL Basics

Structure of a SQL Query

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Commenting in SQL

Single-line comment: -- This is a comment
Multi-line comment:

  /* This is a 
     multi-line comment */

Data Definition Language (DDL)

Creating a Table

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    hire_date DATE
);

Altering a Table

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Dropping a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Modifying a Column

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Renaming a Table

ALTER TABLE old_table_name
RENAME TO new_table_name;

Dropping a Table

DROP TABLE table_name;

Creating an Index

CREATE INDEX index_name
ON table_name (column_name);

Dropping an Index

DROP INDEX index_name;

Data Manipulation Language (DML)

Inserting Data into a Table

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO employees (id, name, age, hire_date)
VALUES (1, 'John Doe', 30, '2022-01-01');

Updating Data in a Table

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET age = 31
WHERE id = 1;

Deleting Data from a Table

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

Data Query Language (DQL)

Selecting Data from a Table

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

Example:

SELECT * FROM employees;
SELECT name, age FROM employees WHERE age > 30;

Wildcards

*: Select all columns
%: Wildcard for zero or more characters (in LIKE clause)
_: Wildcard for exactly one character (in LIKE clause)

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

Data Control Language (DCL)

Granting Permissions

GRANT permission ON object TO user;

Example:

GRANT SELECT, INSERT ON employees TO 'user1';

Revoking Permissions

REVOKE permission ON object FROM user;

Example:

REVOKE SELECT ON employees FROM 'user1';

Joins

INNER JOIN

Returns rows when there is a match in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table. If no match, NULL values will appear for columns from the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left table. If no match, NULL values will appear for columns from the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL OUTER JOIN

Returns rows when there is a match in one of the tables.

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Subqueries

Subquery in SELECT

SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS alias
FROM table1;

Subquery in WHERE

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

Subquery in FROM

SELECT alias.column1
FROM (SELECT column1 FROM table2 WHERE condition) AS alias;

Indexes

Creating an Index

CREATE INDEX index_name
ON table_name (column1, column2);

Dropping an Index

DROP INDEX index_name;

Unique Index

Ensures that all values in a column (or group of columns) are unique.

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Aggregation Functions

COUNT

Counts the number of rows that match a specific condition.

SELECT COUNT(*) FROM table_name WHERE condition;

SUM

Returns the sum of values in a column.

SELECT SUM(column_name) FROM table_name;

AVG

Returns the average of values in a column.

SELECT AVG(column_name) FROM table_name;

MIN and MAX

Returns the minimum and maximum values in a column.

SELECT MIN(column_name), MAX(column_name) FROM table_name;

Grouping and Sorting

GROUP BY

Groups rows that have the same values into summary rows.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

HAVING

Filters groups after applying GROUP BY.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;

ORDER BY

Sorts the result set in ascending or descending order.

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;

Transactions

Starting a Transaction

BEGIN TRANSACTION;

Committing a Transaction

COMMIT;

Rolling Back a Transaction

ROLLBACK;

Advanced SQL

CASE WHEN

Conditional logic inside a query.

SELECT column1,
       CASE
           WHEN condition THEN 'Result 1'
           WHEN condition THEN 'Result 2'
           ELSE 'Default'
       END AS alias
FROM table_name;

UNION and UNION ALL

UNION: Combines the result sets of two or more queries (removes duplicates).
UNION ALL: Combines result sets (keeps duplicates).

SELECT column FROM table1
UNION
SELECT column FROM table2;

SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

Best Practices

Use JOIN instead of subqueries when possible for better performance.
Index frequently searched columns to speed up queries.
Avoid SELECT * and specify only the columns you need.
Use LIMIT for large result sets to restrict the number of rows returned.
Normalize your data to avoid redundancy and improve consistency.
Use WHERE clauses instead of HAVING to filter data before aggregation.
Test queries for performance, especially for large datasets.
Use transactions to ensure data consistency, especially for operations that involve multiple DML statements.

Conclusion

This SQL cheatsheet covers all the essential SQL commands and techniques you’ll need for working with relational databases. Whether you are querying, inserting, updating, or joining data, this guide will help you work more effectively with SQL.

DEV Community: Millie Molotov

MongoDB Command Shortcuts: The Ultimate Guide

1. MongoDB Basics

Connecting to MongoDB

Show Databases

Use a Database

Show Collections

2. CRUD Operations

Create a Collection

Insert Documents

Read Documents

Update Documents

Delete Documents

3. Querying Documents

Basic Queries

Projection

Sorting

Limiting and Skipping

4. Updating Documents

Update Operators

5. Deleting Documents

Delete Operators

6. Indexes

Creating Indexes

Viewing Indexes

Dropping Indexes

7. Aggregation

Basic Aggregation

Common Aggregation Stages

8. Data Modeling

Embedding Documents

Referencing Documents

9. Replication

Setting Up a Replica Set

Checking Replica Set Status

10. Sharding

Enabling Sharding

Sharding a Collection

11. Transactions

Starting a Transaction

Committing a Transaction

Aborting a Transaction

12. Security

Creating a User

Authenticating a User

13. Best Practices

Conclusion

How to retrieve values from all types of HTML Inputs in JavaScript

Input Types and Retrieval Methods

1. Text Input

2. Email Input

3. Password Input

4. Number Input

5. Date Input

6. Radio Button

7. Checkbox

8. Dropdown (Select)

9. Textarea

Example: Complete Script

How to Use

SQL Filtering and Sorting with Real-life Examples

Table of Contents

Tables Structure

employees Table

departments Table

WHERE Clause

SQL Query

Result

Example with AND Operator

Result

GROUP BY Clause

SQL Query

Result

HAVING Clause

SQL Query

Result

ORDER BY Clause

SQL Query (Ascending Order)

Result

SQL Query (Descending Order)

`employees` Table

`departments` Table

Example with `AND` Operator