Menu
MySQL

Introduction to SQL

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It allows users to perform various operations on data stored in a database, such as querying, updating, inserting, and deleting data. SQL is essential for interacting with relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, SQL Server, and others.

 

 

Key Characteristics of SQL

  • Declarative Language: SQL is a declarative language, meaning that users specify what they want to retrieve or modify, rather than how the operations should be performed. The database system figures out the best way to execute the query.
  • Standardized: SQL has been standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), ensuring compatibility across various database platforms (though implementations may vary slightly).
  • Set-Based Operations: SQL works with sets of data, allowing users to manipulate and retrieve entire sets of records at once rather than dealing with one row at a time.

 

 

Components of SQL

 

 

 

1. Data Definition Language (DDL):

DDL commands are used to define and manage the database structure. It includes commands to create, alter, and delete database objects such as tables, indexes, and schemas.

  • CREATE: Used to create a new database object (e.g., table, view).
  • ALTER: Modifies an existing database object.
  • DROP: Deletes an object from the database.
  • TRUNCATE: Removes all rows from a table but retains its structure.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   HireDate DATE
);
 

 

 

2. Data Manipulation Language (DML):

DML is used for data retrieval and manipulation within the database. It includes operations for selecting, inserting, updating, and deleting data.

  • SELECT: Retrieves data from one or more tables.
  • INSERT: Adds new data to a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes data from a table.

Example:

SELECT FirstName, LastName FROM Employees WHERE HireDate > '2020-01-01';
 

 

3. Data Control Language (DCL):

DCL deals with database security and access control. It allows administrators to grant or revoke user privileges.

  • GRANT: Gives users access to specific database operations.
  • REVOKE: Removes previously granted permissions.

Example:

GRANT SELECT, INSERT ON Employees TO 'user1';
 

 

 

4. Transaction Control Language (TCL):

TCL commands manage transactions in the database, allowing users to ensure data integrity by grouping operations together.

  • COMMIT: Saves all changes made in the current transaction to the database.
  • ROLLBACK: Undoes changes made in the current transaction.
  • SAVEPOINT: Sets a point within a transaction to which a rollback can be performed.

Example:

BEGIN TRANSACTION;
UPDATE Employees SET HireDate = '2021-01-01' WHERE EmployeeID = 1;
COMMIT;
 

 

 

 

 

MySQL