Introduction

• I don't care.
• MySQL is a relational database management system (RDBMS). It is an open-source, multi-user, multi-threaded database system that allows for storing and managing structured data in tables. It uses the Structured Query Language (SQL) to manage and manipulate data.

Key Features of MySQL:

• Open Source
• Cross-Platform
• Relational Database: MySQL is based on a relational database model, which stores data in tables (also known as relations).
• High Performance: It is optimized for speed and can handle a large amount of data efficiently.
• ACID Compliant: MySQL supports the ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring that database transactions are processed reliably.
  • Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all of the operations within a transaction are completed successfully, or none of them are applied. In other words, a transaction is atomic: it is "all or nothing."
  • Consistency ensures that a transaction takes the database from one valid state to another valid state. After a transaction, all data must be in a consistent state, adhering to all defined rules, constraints, and relationships.
  • Isolation ensures that transactions are executed in isolation from one another, even if they occur concurrently. Each transaction should be executed as if it is the only transaction being processed, preventing interference from other transactions.
  • Durability ensures that once a transaction is committed, it is permanent, even in the case of system failures like power outages or crashes. The changes made by the transaction are saved to disk and will survive any subsequent failures.
• Multi-User Access: MySQL allows multiple users to access the database simultaneously without affecting performance.

SQL Keywords

CREATE

1. CREATE DATABASE
   • The CREATE DATABASE command is used to create a new database. In Mongoose, you don't need to explicitly create a database; it is automatically created when you connect to the database.

// DB is created if it doesn't exist
mongoose.connect('mongodb://localhost/my_database');

CREATE DATABASE my_database;

1. USE DATABASE
   • The USE DB_NAME is used to select the database to use. In Mongoose, this is handled by the connection string.

mongoose.connect('mongodb://localhost/my_database');

USE my_database;

1. CREATE TABLE
   • The CREATE TABLE command is used to create a new table in the database. In Mongoose, this is similar to creating a new collection.

// DB is created if it doesn't exist
mongoose.connect('mongodb://localhost/my_database');

CREATE DATABASE my_database;

1. CREATE INDEX
   • The CREATE INDEX command is used to create an index on a table to improve query performance. In MongoDB, this is the same.

mongoose.connect('mongodb://localhost/my_database');

USE my_database;

DESCRIBE

• Used in SQL to view the structure of a table (its columns, data types, constraints, etc.). Mongoose Example: In MongoDB, there isn't a direct equivalent to DESCRIBE. However, you can inspect a schema programmatically.

mongoose.model('User', UserSchema);

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

INSERT

• The INSERT INTO command is used to insert new rows in a table. In mongoose you would insert a new document into a collection/(Model).

UserSchema.index({ email: 1 }); // Unnamed Index
UserSchema.index({ email: 1 }, { name: 'idx_email' }); // Named Index

-- Syntax: CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX idx_email ON Users (email); -- Named Index
CREATE INDEX ON Users (email); -- Unnamed Index

SELECT

• The SELECT statement in SQL is used to retrieve data from a database. In Mongoose, this is equivalent to using the .find() method to query a collection.

console.log(UserSchema.paths);
// Outputs details about the schema fields and types

DESCRIBE Users;

UPDATE

• The UPDATE statement is used to modify the existing records in a table. In mongoose you use find and update or .update()

// In mongoose its equivalent to .save() or .create();
const newUser = new User({ name: 'John Doe', email: 'john@example.com' });
newUser.save()

INSERT INTO Users (name, email)
VALUES ('John Doe', 'john@example.com');

DELETE

• The DELETE statement is used to delete existing records in a table. In mongoose we'd use deleteOne, deleteMany or find and delete.

const users = await User.find(); // Fetches all users
const { name, email } = await User.findById(1); // Fetches user with id = 1

SELECT * FROM Users; -- all users
SELECT name, email FROM Users WHERE id = 1; -- user of id 1

ALTER

• The ALTER TABLE statement in SQL is used to modify the structure of an existing table (add column, drop column and modify column). In Mongoose, the equivalent operation would be modifying the schema to include the new field and then handling updates to existing documents if necessary.

// update all user of name kb
const query = { name: "kb" };
User.update(query, { name: "thekbbohara" })

-- update all user of name kb
UPDATE Users
SET name = "thekbbohara", email = "thekbbohara@gmail.com"
WHERE name = "kb";

JOIN

• A JOIN clause is used to combine rows from two or more tables, based on a related column between them. In MongoDB, joins are not natively supported like in relational databases. Instead, you typically use aggregation pipelines like $lookup for similar functionality.

User.deleteOne({ _id: 1 })
// All users whose name is notKb will be deleted.
User.deleteMany({ name: "notKb" })

INNER JOIN

• The INNER JOIN keyword selects records that have matching values in both tables.

DELETE FROM Users WHERE id = 1;
DELETE FROM Users WHERE name = "notKb"
-- All users whose name is notKb will be deleted.

LEFT JOIN

• The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).

// Update the UserSchema to add the 'age' field
const UserSchema = new mongoose.Schema({
    name: String,
    email: String,
    age: Number, // New field
});

RIGHT JOIN

• The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

-- Adds an 'age' column to the Users table
ALTER TABLE Users ADD age INT;

-- Delete 'Email' column from Users table
ALTER TABLE Users DROP COLUMN email;

-- Makes 'id' column unsigned and auto-incrementing
ALTER TABLE Users MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT;

CROSS JOIN

• The CROSS JOIN keyword returns all records from both tables (table1 and table2).

// DB is created if it doesn't exist
mongoose.connect('mongodb://localhost/my_database');

DATATYPES

In MySQL there are three main data types: string, numeric, and date and time. But in MongoDB, there are a variety of data types, but they differ from those in MySQL. MongoDB uses BSON (Binary JSON) to store data, which supports a rich set of data types. Here's a comparison of common data types in MySQL and MongoDB:

String Data Types

Numeric Data Types

Date and Time Data Types

Boolean Data Types

Binary Data Types

JSON/Array Data Types

Other Data Types

PRIMARY KEY

• Ensures that each row in a table has a unique identifier.

// DB is created if it doesn't exist
mongoose.connect('mongodb://localhost/my_database');

CREATE DATABASE my_database;

FOREIGN KEY

• Ensures a column's values correspond to values in another table.

mongoose.connect('mongodb://localhost/my_database');

USE my_database;

Data Integrity and Constraints

1. NOT NULL: Ensures that a column cannot have NULL values.

mongoose.model('User', UserSchema);

CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

1. UNIQUE: Ensures that all values in a column are unique.

UserSchema.index({ email: 1 }); // Unnamed Index
UserSchema.index({ email: 1 }, { name: 'idx_email' }); // Named Index

-- Syntax: CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX idx_email ON Users (email); -- Named Index
CREATE INDEX ON Users (email); -- Unnamed Index

1. DEFAULT: Assigns a default value to a column if no value is provided.

console.log(UserSchema.paths);
// Outputs details about the schema fields and types

DESCRIBE Users;

1. CHECK (MySQL 8.0 ): Ensures that the values in a column satisfy a given condition.

// In mongoose its equivalent to .save() or .create();
const newUser = new User({ name: 'John Doe', email: 'john@example.com' });
newUser.save()

INSERT INTO Users (name, email)
VALUES ('John Doe', 'john@example.com');

1. AUTO_INCREMENT: Automatically generates a unique value for a column, often used for primary keys.

const users = await User.find(); // Fetches all users
const { name, email } = await User.findById(1); // Fetches user with id = 1

SELECT * FROM Users; -- all users
SELECT name, email FROM Users WHERE id = 1; -- user of id 1

That's all. You are good to go feel free to leave your feedback and you can get in touch with me here: thekbbohara

OH, by the way how do we setup Mysql.
I recommend using docker:

// update all user of name kb
const query = { name: "kb" };
User.update(query, { name: "thekbbohara" })

-- update all user of name kb
UPDATE Users
SET name = "thekbbohara", email = "thekbbohara@gmail.com"
WHERE name = "kb";

The above is the detailed content of Mysql for Mongoose developer.. For more information, please follow other related articles on the PHP Chinese website!