Databases and SQL Overview

Databases and DBMS

• A database is a collection of interrelated data.
• Database Management System (DBMS) is software to create, manage, and organize databases.
• Relational Database Management System (RDBMS) is a type of DBMS that organizes data into tables (relations), consisting of rows (records) and columns (attributes).
• Examples of RDBMS include MySQL, PostgreSQL, and Oracle.

Structured Query Language (SQL)

• SQL, or Structured Query Language, is used to store, manipulate, and retrieve data from RDBMS.
• SQL is essential for performing CRUD operations:
  • CREATE: Creating databases, tables, and inserting data.
  • READ: Retrieving data from the database.
  • UPDATE: Modifying existing data.
  • DELETE: Removing data or whole tables from the database.
• SQL keywords are not case sensitive (e.g., SELECT and select are equivalent).

SQL vs MySQL

• SQL is the language for interacting with relational databases, while MySQL is an RDBMS that implements SQL.

SQL Data Types

• Data types define what kind of data can be stored in a column or variable.
• Commonly used data types in MySQL include:
  • CHAR: Fixed-length string (0-255).
  • VARCHAR: Variable-length string (0-255).
  • BLOB: Binary large object storage (0-65535).
  • INT: Integer (-2,147,483,648 to 2,147,483,647).
  • FLOAT: Decimal number with up to 23 digits of precision.
  • BOOLEAN: Represents true or false values (0 or 1).
  • DATE: Date format YYYY-MM-DD.
• Consider using UNSIGNED with integer types for non-negative values.

Types of SQL Commands

• DQL (Data Query Language): Retrieves data from databases (e.g., SELECT).
• DDL (Data Definition Language): Manages database structure (e.g., CREATE, DROP, ALTER).
• DML (Data Manipulation Language): Modifies data (e.g., INSERT, UPDATE, DELETE).
• DCL (Data Control Language): Grants or revokes permissions (e.g., GRANT, REVOKE).
• TCL (Transaction Control Language): Manages database transactions (e.g., COMMIT, ROLLBACK).

Data Definition Language (DDL)

• DDL commands manage the structure of databases and their objects.
• Key DDL Commands:
  • CREATE TABLE: Creates a new table specifying name, columns, data types, and constraints (e.g., CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary DECIMAL(10, 2));).
  • ALTER TABLE: Modifies an existing table's structure (e.g., ALTER TABLE employees ADD COLUMN email VARCHAR(100);).
  • DROP TABLE: Deletes an existing table along with its data (e.g., DROP TABLE employees;).
  • CREATE INDEX: Enhances query performance by creating an index (e.g., CREATE INDEX idx_employee_name ON employees (name);).