| MySQL | MongoDB |
|---|---|
 |
 |
SQL is a standardized programming language used to manage and manipulate relational databases. It consists of various sublanguages like DDL, DML, DCL, TCL, and DQL for defining structures, modifying data, controlling access, handling transactions, and querying data. SQL is the foundation of relational database management and is used in many database systems.
MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language. It is widely used for web applications, data storage, and management. MySQL is known for its speed, scalability, and reliability, supporting features like indexing, transactions, and user privileges.
- SQL is a language, while MySQL is a database management system that implements SQL.
- SQL is universal, whereas MySQL is one of many RDBMS that use SQL (others include PostgreSQL, Oracle, and Microsoft SQL Server).
- MySQL includes additional features like security controls, user management, and database storage mechanisms beyond SQL’s standard capabilities.
In summary, SQL is the language for managing databases, while MySQL is a specific database system that utilizes SQL to store, retrieve, and manage data efficiently.
SQL (Structured Query Language) is categorized into different sublanguages, each serving a specific role in database management. These sublanguages ensure efficient interaction with relational databases.
DDL consists of commands that define, alter, and remove database structures such as tables, indexes, and views. These commands impact the schema of the database and are auto-committed, meaning changes are permanent.
- CREATE is used to create new database objects, such as tables, indexes, or views.
- ALTER modifies the structure of existing database objects, such as adding or deleting columns.
- DROP permanently removes database objects along with their data.
- TRUNCATE deletes all records from a table but retains the table’s structure for future use.
DML is responsible for handling data stored in the database. Unlike DDL, these commands affect the records inside tables rather than the structure itself.
- SELECT retrieves data from one or more tables based on specific conditions.
- INSERT adds new records into a table.
- UPDATE modifies existing records in a table.
- DELETE removes specific records from a table without affecting its structure.
DCL is used to define access control and manage user permissions in a database system. These commands ensure security and regulate who can access or modify data.
- GRANT provides specific privileges to users, such as the ability to read, write, or modify data.
- REVOKE removes previously granted privileges, restricting user access.
TCL ensures the consistency and integrity of transactions within a database. These commands help manage multiple SQL operations as a single unit.
- COMMIT saves all changes made in a transaction permanently.
- ROLLBACK undoes changes made in the current transaction before they are committed.
- SAVEPOINT creates checkpoints within a transaction, allowing partial rollbacks without affecting the entire transaction.
DQL is primarily focused on retrieving data from a database. Although the SELECT statement is technically part of DML, it is often treated as a separate sublanguage due to its significance in querying data.
- SELECT is used to fetch data from tables based on specified conditions, filters, or aggregations.
Each SQL sublanguage serves a distinct function in database management. DDL modifies the database structure, DML manages the data, DCL controls access, TCL ensures transaction integrity, and DQL retrieves data efficiently. Understanding these sublanguages is essential for effective database administration and application development.