Database languages are specialized languages used to create, manipulate, and query databases. They provide the means to define database schemas, insert, update, delete, and retrieve data from databases.
Types of Database Languages:
1.) Data Definition Language (DDL):
Purpose: Used to define the database structure or schema.
Functions:
- CREATE: Used to create databases, tables, indexes, and other database objects.
- ALTER: Used to modify existing database structures, such as adding or dropping columns in a table.
- DROP: Used to delete databases, tables, and other objects.
- TRUNCATE: Used to remove all records from a table, but not the table itself.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT
);
ALTER TABLE Employees ADD Email VARCHAR(100);
DROP TABLE Employees;
2.) Data Manipulation Language (DML):
Purpose: Used for managing data within the schema objects.
Functions:
- SELECT: Retrieves data from the database.
- INSERT: Adds new data into the database.
- UPDATE: Modifies existing data within the database.
- DELETE: Removes data from the database.
Example:
SELECT * FROM Employees WHERE DepartmentID = 1;
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (1, 'John', 'Doe', 1);
UPDATE Employees SET Email = 'john.doe@example.com' WHERE EmployeeID = 1;
DELETE FROM Employees WHERE EmployeeID = 1;
3.) Data Control Language (DCL):
Purpose: Used to control access to data in the database.
Functions:
- GRANT: Gives user’s access privileges to the database.
- REVOKE: Removes access privileges from the database.
Example:
GRANT SELECT, INSERT ON Employees TO user1;
REVOKE INSERT ON Employees FROM user1;
4.) Transaction Control Language (TCL):
Purpose: Used to manage transactions within the database.
Functions:
- COMMIT: Saves all changes made in the current transaction.
- ROLLBACK: Reverts all changes made in the current transaction.
- SAVEPOINT: Sets a savepoint within a transaction.
- SET TRANSACTION: Sets the characteristics of the current transaction.
Example:
BEGIN TRANSACTION;
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (2, 'Jane', 'Smith', 2);
SAVEPOINT Savepoint1;
UPDATE Employees SET DepartmentID = 3 WHERE EmployeeID = 2;
ROLLBACK TO Savepoint1;
COMMIT;
Database Interfaces
Database interfaces provide the means through which users interact with a database. These interfaces range from command-line interfaces to graphical user interfaces and APIs.
Types of Database Interfaces:
Command-Line Interface (CLI):
Description: Users interact with the database by typing commands directly into a command-line interface. It is often used by database administrators and developers.
Example: MySQL Command Line, PostgreSQL psql.
Graphical User Interface (GUI):
Description: Provides a graphical interface for users to interact with the database, making it easier for non-technical users to manage and query data.
Example: phpMyAdmin for MySQL, pgAdmin for PostgreSQL, Microsoft SQL Server Management Studio.
Web-Based Interface:
Description: Allows users to interact with the database through a web browser. These interfaces are typically easier to access remotely.
Example: Adminer, DBeaver, Oracle APEX.
Application Programming Interface (API):
Description: Allows developers to interact with the database programmatically through code. APIs can be used in various programming languages to perform database operations.
Example: JDBC for Java, ODBC for various languages, ADO.NET for .NET languages.
Embedded SQL:
Description: Embeds SQL statements directly within the code of a host programming language.
Example: SQL statements within C/C++ code, PL/SQL for Oracle databases.
Database Connectivity Drivers:
Description: Provide the necessary connectivity between an application and the database.
Example: JDBC (Java Database Connectivity), ODBC (Open Database Connectivity), and database-specific drivers like MySQL Connector/J.
Summary
Database Languages:
- DDL: Defines database structure (CREATE, ALTER, DROP).
- DML: Manages data within the structure (SELECT, INSERT, UPDATE, DELETE).
- DCL: Controls access to data (GRANT, REVOKE).
- TCL: Manages transactions (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION).
Database Interfaces:
- CLI: Command-line tools for direct interaction.
- GUI: Graphical interfaces for easier management.
- Web-Based Interface: Browser-based management tools.
- API: Programmatic interaction with databases.
- Embedded SQL: SQL embedded within programming languages.
- Database Connectivity Drivers: Middleware for connecting applications to databases.