Database Fundamentals
Database Fundamentals is the basic knowledge of how data is stored, organized, and managed in a computer system. A database helps applications store information so it can be easily accessed, updated, and managed. One common database system used in web development is MySQL.
1. Database
A database is a collection of organized data stored in a computer system so that it can be easily accessed and managed.
Example
A school database may store:
Student names
Student ID numbers
Courses
Results
2. Table
A table is where data is stored inside a database. It organizes data into rows and columns.
Example Table (Students)
| ID | Name | Course |
|---|---|---|
| 1 | John | Computer Science |
| 2 | Mary | Mathematics |
3. Row (Record)
A row represents a single record in a table. Each row contains information about one item.
Example
In a student table, one row represents one student.
4. Column (Field)
A column represents a category of data in a table.
Example
In a student table, columns may include:
ID
Name
Course
5. Primary Key
A primary key is a unique identifier used to identify each record in a table. No two rows can have the same primary key.
Example
Student ID can be used as a primary key.
6. Relationship
A relationship connects tables in a database so they can share information.
Example
A Students table
A Courses table
These tables can be connected so each student is linked to a course.
7. Database Management System (DBMS)
A DBMS is software used to create, manage, and control databases.
Examples
MySQL
Oracle Database
Microsoft SQL Server
✅ Simple Summary
| Term | Meaning |
|---|---|
| Database | Collection of organized data |
| Table | Structure where data is stored |
| Row | One record of data |
| Column | Category of data |
| Primary Key | Unique identifier |
| Relationship | Connection between tables |
| DBMS | Software used to manage databases |
MySQL Workbench** to set up a database for Django:
SQL with MySQL
SQL (Structured Query Language) is the language used to communicate with databases like MySQL. It lets you store, retrieve, update, and delete data in a database. Learning SQL is essential before working with Django, because Django uses SQL behind the scenes to interact with the database.
1. Creating a Database
You can create a database to store your tables.
CREATE DATABASE school;
2. Creating Tables
Tables store data in rows and columns.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
course VARCHAR(50)
);
3. Inserting Data
Add records to your table using INSERT.
INSERT INTO students (id, name, course)
VALUES (1, 'John', 'Computer Science');
4. Reading Data (SELECT)
Retrieve data from the table using SELECT.
SELECT * FROM students;
SELECT name, course FROM students;
5. Updating Data
Change existing data using UPDATE.
UPDATE students
SET course = 'Mathematics'
WHERE id = 1;
6. Deleting Data
Remove records using DELETE.
DELETE FROM students
WHERE id = 1;
7. Filtering Data
Use WHERE to filter records based on conditions.
SELECT * FROM students
WHERE course = 'Computer Science';
8. Sorting Data
Use ORDER BY to sort results.
SELECT * FROM students
ORDER BY name ASC;
9. Joining Tables
Combine data from multiple tables using JOIN.
SELECT students.name, courses.course_name
FROM students
JOIN courses ON students.id = courses.student_id;
10. Summary of Key SQL Commands
| Command | Purpose |
|---|---|
| CREATE DATABASE | Make a new database |
| CREATE TABLE | Make a new table |
| INSERT INTO | Add data to a table |
| SELECT | Read data |
| UPDATE | Change data |
| DELETE | Remove data |
| WHERE | Filter records |
| ORDER BY | Sort results |
| JOIN | Combine multiple tables |
0 comments:
Post a Comment