Structured Query Language, commonly known as SQL, stands as the cornerstone of relational database management systems (RDBMS) worldwide. Whether you're an aspiring data analyst, a seasoned database administrator, or a software developer, understanding SQL is essential for manipulating, managing, and querying data effectively. In this comprehensive guide, we will delve into the fundamental concepts of SQL, explore its syntax and capabilities, and provide practical examples to solidify your understanding.
What is SQL?
SQL, originally developed in the 1970s, is a standardized programming language designed for managing and manipulating data in relational database systems. Its primary functions include querying data, defining and modifying database schemas, and performing administrative tasks such as granting permissions and creating backups. SQL is not limited to any specific database system; rather, it is a universal language used across various platforms, including MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.
Basic SQL Syntax
SQL commands are categorized into several types:
Data Definition Language (DDL): Used to define the database structure.
CREATE TABLE: Creates a new table.
ALTER TABLE: Modifies an existing table structure.
DROP TABLE: Deletes a table and its data.
Data Manipulation Language (DML): Used to manipulate data within tables.
SELECT: Retrieves data from a database.
INSERT INTO: Inserts new records into a table.
UPDATE: Modifies existing records in a table.
DELETE FROM: Deletes records from a table.
Data Control Language (DCL): Used to manage permissions and access rights.
GRANT: Grants specific privileges to database users.
REVOKE: Revokes previously granted privileges.
Transaction Control Commands: Used to manage transactions within a database.
COMMIT: Saves changes made during the current transaction.
ROLLBACK: Reverts changes made during the current transaction.
SAVEPOINT: Sets a point within the transaction to which you can later roll back.
Creating and Manipulating Tables
Let's delve into practical examples to illustrate SQL's capabilities:
-- Creating a new table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
-- Inserting data into the table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'IT', 60000.00),
(2, 'Jane', 'Smith', 'HR', 55000.00),
(3, 'Michael', 'Johnson', 'Finance', 70000.00);
-- Updating records
UPDATE Employees
SET Salary = 72000.00
WHERE EmployeeID = 3;
-- Deleting records
DELETE FROM Employees
WHERE EmployeeID = 2;
Comentários