SimpleTuts.com
Login Register

Database Basics

Previous Next

1. What is a Database?

A database is a structured collection of data that can be easily accessed, managed, and updated. Databases are used to store and organize data in a way that allows for efficient retrieval and manipulation.

A Database Table

2. Key Concepts

  • Database Management System (DBMS): A software application that interacts with the database to manage data. Examples include MySQL, PostgreSQL, Oracle, and SQLite.
  • Database Schema: The structure of a database, including tables, columns, data types, and relationships between tables.
  • Table: A collection of related data entries organized in rows and columns. Each table represents a different type of entity (e.g., customers, orders).
  • Row (Record): A single entry in a table, representing a single instance of the entity (e.g., one customer's information).
  • Column (Field): A specific attribute of the entity, like name or email, that is stored for each record.
  • Primary Key: A unique identifier for each record in a table. It ensures that each record can be uniquely identified.
  • Foreign Key: A column or set of columns in one table that uniquely identifies a row in another table. It establishes a relationship between tables.
  • Query: A request for data from the database, typically written in SQL (Structured Query Language).
  • SQL: A standard language used to communicate with relational databases. It is used to perform tasks such as querying, updating, and managing data.

3. Basic SQL Operations

1. Creating a Database and Table

CREATE DATABASE my_database;

USE my_database;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

2. Inserting Data

INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com'),
       (2, 'Jane', 'Smith', 'jane.smith@example.com');

3. Querying Data

SELECT * FROM employees;

SELECT first_name, email FROM employees WHERE employee_id = 1;

4. Updating Data

UPDATE employees
SET email = 'john.newemail@example.com'
WHERE employee_id = 1;

5. Deleting Data

DELETE FROM employees
WHERE employee_id = 2;

6. Dropping a Table or Database

DROP TABLE employees;

DROP DATABASE my_database;
Previous Next