| Introduction to Web Development |
|---|
| Basic PHP Syntax |
|---|
| PHP Basics |
|---|
| Variables and Data Types |
|---|
| Functions in PHP |
|---|
| User-defined Functions |
|---|
| Built-in Functions |
|---|
| Variable Scope |
|---|
| Include and Require |
|---|
| Working with Forms |
|---|
| Forms in HTML |
|---|
| Form Handling in PHP |
|---|
| Working with Files |
|---|
| File Handling |
|---|
| File System Functions |
|---|
| Working with Databases |
|---|
| Introduction to Databases |
|---|
| Advanced PHP Techniques |
|---|
| Object-Oriented Programming (OOP) |
|---|
| Error and Exception Handling |
|---|
1. Basic Concepts
- Database: A structured collection of data.
- Table: A collection of related data entries consisting of rows and columns.
- Row: A single, data item in a table (also known as a record).
- Column: A set of data values of a particular type, one for each row of the table.
2. SQL Commands
SQL commands are divided into several categories:
Data Definition Language (DDL)
DDL commands are used to define and manage database structures, such as tables and indexes.
CREATE: Create a new table or database
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
ALTER: Modify an existing database object, such as a table.
ALTER TABLE table_name
ADD column_name datatype;
DROP: Delete a table or database.
DROP TABLE table_name;
Data Manipulation Language (DML)
DML commands are used for managing data within schema objects.
SELECT: Retrieve data from one or more tables.
SELECT column1, column2
FROM table_name
WHERE condition;
INSERT: Add new records to a table.
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
UPDATE: Modify existing records.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE: Remove records from a table
DELETE FROM table_name
WHERE condition;
Data Control Language (DCL)
DCL commands are used to control access to data in the database.
GRANT: Give user's access privileges to the database
GRANT SELECT, INSERT ON table_name TO user_name;
REVOKE: Remove user's access privileges.
REVOKE SELECT, INSERT ON table_name FROM user_name;
Transaction Control Language (TCL)
TCL commands are used to manage transactions in the database.
COMMIT: Save the changes made in the current transaction
COMMIT;
ROLLBACK: Undo the changes made in the current transaction
ROLLBACK;
SAVEPOINT: Set a savepoint within a transaction.
SAVEPOINT savepoint_name;
3. SQL Clauses and Functions
SQL provides various clauses and functions to manipulate and retrieve data efficiently.
WHERE Clause
Used to filter records.
SELECT * FROM table_name
WHERE condition;
JOIN Clause
Used to combine records from two or more tables based on a related column.
SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
GROUP BY Clause
Used to group rows that have the same values in specified columns into summary rows.
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
HAVING Clause
Used to filter groups after grouping.
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > value;
ORDER BY Clause
Used to sort the result-set.
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;
Aggregate Functions
Functions that perform a calculation on a set of values and return a single value.
COUNT(): Returns the number of rows
SELECT COUNT(*) FROM table_name;
SUM(): Returns the sum of a numeric column
SELECT SUM(column_name) FROM table_name;
AVG(): Returns the average value
SELECT AVG(column_name) FROM table_name;
MAX(): Returns the maximum value.
SELECT MAX(column_name) FROM table_name;
MIN(): Returns the minimum value
SELECT MIN(column_name) FROM table_name;
4. Indexes
Indexes are used to retrieve data from the database more quickly.
CREATE INDEX index_name
ON table_name (column_name);
5. Views
A view is a virtual table based on the result set of an SQL statement.
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
6. Normalization
Normalization is a database design technique to reduce data redundancy and improve data integrity.
7. Advanced SQL
Subqueries: A query within another query
SELECT column1
FROM table_name
WHERE column2 = (SELECT column2 FROM table_name WHERE condition);
Stored Procedures: A set of SQL statements that can be executed as a program
CREATE PROCEDURE procedure_name AS
BEGIN
SQL statements;
END;
Triggers: SQL code that is automatically executed in response to certain events on a table.
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SQL statements;
END;
SQL is a powerful tool for database management and manipulation, and mastering it requires understanding these fundamental concepts and commands.
