Overview of SQL Language Types
Structured Query Language (SQL) is a powerful tool used to interact with relational databases. It is divided into several language types, each serving a specific purpose:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
This section focuses on DDL and DML, the two most fundamental components of SQL.
Data Definition Language (DDL)
Data Definition Language (DDL) is used to define and manage the structure of a database. It includes commands that create, modify, and delete database objects such as tables, indexes, and schemas.
- Think of DDL as the blueprint of a building.
- It defines the structure and layout, but not the contents.
Key DDL Commands
- CREATE: Used to create database objects.
- CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerEmail VARCHAR(255) );
- ALTER: Modifies the structure of an existing object.
- ALTER TABLE Customer ADD COLUMN CustomerAddress VARCHAR(255);
- DROP: Deletes an object from the database.
- DROP TABLE Customer;
- TRUNCATE: Removes all records from a table but keeps the structure intact.
- TRUNCATE TABLE Customer;
- RENAME: Changes the name of a database object.
- RENAME TABLE Customer TO Client;
DDL commands are autocommitted, meaning changes are immediately saved and cannot be rolled back.
Data Manipulation Language (DML)
Data Manipulation Language (DML) is used to interact with the data stored in a database. It allows you to retrieve, insert, update, and delete data.
DML is like the daily operations inside a building, adding, moving, or removing furniture.
Key DML Commands
- SELECT: Retrieves data from the database.
- SELECT CustomerID, CustomerEmail FROM Customer;
- INSERT: Adds new data to a table.
- INSERT INTO Customer (CustomerID, CustomerEmail) VALUES (1, 'example@mail.com');
- UPDATE: Modifies existing data.
- UPDATE Customer SET CustomerEmail = 'newemail@mail.com' WHERE CustomerID = 1;
- DELETE: Removes data from a table.
- DELETE FROM Customer WHERE CustomerID = 1;
BEWARE: Update and Delete commands affect all rows unless constrained by a WHERE clause.
- UPDATE customers SET name = ‘Steven’;
- Everyone would be Steven
- UPDATE customers SET name = ‘Steven’ WHERE customerID = 1;
- Only the exact customerID would be updated
- Unlike DDL, DML commands are not autocommitted.
- They require explicit COMMIT or ROLLBACK actions to finalize or undo changes.
Comparing DDL and DML
| Aspect | DDL | DML |
|---|---|---|
| Purpose | Defines and manages database structure | Manipulates data within the database |
| Commands | CREATE, ALTER, DROP, TRUNCATE | SELECT, INSERT, UPDATE, DELETE |
| Transaction | Autocommitted | Requires explicit COMMIT or ROLLBACK |
| Impact | Affects the schema and structure | Affects the data within the structure |
| Use Cases | Creating tables, altering schemas | Querying data, updating records |
While DCL and TCL are essential for database management, they are beyond the scope of this section.
Practical Applications and Considerations
- Database Design: Use DDL to create a robust schema that supports your application's needs.
- Data Integrity: Use DML to ensure data is accurate and up-to-date.
- Performance: Be mindful of DML operations on large datasets, as they can impact performance.
