MYSQL AND SQL
Introduction to MySQL and SQL
What is SQL?
SQL (Structured Query Language) is a programming language used to manage and manipulate databases.
It allows users to create, read, update, and delete data in a relational database.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that uses SQL to store and
manage data efficiently. It is widely used for web applications and supports multiple users
simultaneously.
What Does "Relational" Mean?
A relational database is a database structured to recognize relations among stored data. It organizes data
into tables (rows and columns), where each table represents an entity, and relationships are established
using foreign keys.
Example:
• A Users table might be linked to an Orders table through a user_id column.
What is CRUD?
CRUD stands for Create, Read, Update, and Delete – the four basic operations that can be performed on
a database.
• Create (INSERT): Adds new records to a table.
• Read (SELECT): Retrieves data from a table.
• Update (UPDATE): Modifies existing records.
• Delete (DELETE): Removes records from a table.
Example CRUD operations in SQL:
-- Create a new record
INSERT INTO users (name, email) VALUES ('James Mwanza, 'james@example.com');
-- Read records
SELECT * FROM users;
-- Update a record
UPDATE users SET email = 'james.mwanza@example.com' WHERE name = 'James Mwanza;
-- Delete a record
DELETE FROM users WHERE name = 'James Mwanza;
Using MySQL with WAMP Server
WAMP (Windows, Apache, MySQL, PHP) is a software package that provides a local server
environment to run MySQL databases on Windows.
How to Use MySQL with WAMP Server
1. Download and Install WAMP Server from the official site.
2. Start WAMP Server and ensure the icon is green.
3. Access phpMyAdmin by opening a browser and going to http://localhost/phpmyadmin.
4. Create a New Database by clicking on "Databases" and entering a name.
5. Use SQL Queries to create tables and manipulate data.
Common SQL Commands
1. Data Definition Language (DDL) - Defines Database Structure
Command Description
CREATE DATABASE dbname; Creates a new database.
CREATE TABLE tablename (column1 datatype, column2
Creates a new table.
datatype);
Adds a new column to an existing
ALTER TABLE tablename ADD columnname datatype;
table.
DROP DATABASE dbname; Deletes an entire database.
DROP TABLE tablename; Deletes an entire table.
2. Data Manipulation Language (DML) - Handles Data Operations
Command Description
INSERT INTO tablename (column1, column2) VALUES
Adds new data to a table.
('value1', 'value2');
UPDATE tablename SET column1 = 'value' WHERE condition; Updates existing data.
Command Description
DELETE FROM tablename WHERE condition; Removes data from a table.
SELECT column1, column2 FROM tablename WHERE
Retrieves data from a table.
condition;
Returns the number of records in a
SELECT COUNT(*) FROM tablename;
table.
SELECT column1, COUNT(*) FROM tablename GROUP BY Groups data and returns aggregate
column1; counts.
SELECT column1 FROM tablename WHERE column2 LIKE Searches for a pattern within a
'%value%'; column.
SELECT column1 FROM tablename ORDER BY column2
Sorts data in descending order.
DESC;
Creating a Database and Table in MySQL
Step 1: Create a Database
CREATE DATABASE my_database;
USE my_database;
Step 2: Create a Table
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(255)
);
Step 3: Insert Data into Table
INSERT INTO users (name, email) VALUES ('John Phiri', 'john@icu.com');
Step 4: Retrieve Data from Table
SELECT * FROM users;
Creating and Displaying Data in a Website Using PHP and MySQL
Step 1: Create form.php - The HTML Form to Insert Data
<!DOCTYPE html>
<html>
<head>
<title>Insert User Data</title>
</head>
<body>
<h2>Enter User Details</h2>
<form method="POST" action="insert.php">
<label for="name">Name:</label>
<input type="text" name="name" required>
<br>
<label for="email">Email:</label>
<input type="email" name="email" required>
<br>
<button type="submit">Submit</button>
</form>
</body>
</html>
Step 2: Create insert.php to Handle Data Insertion
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$name = $conn->real_escape_string($_POST['name']);
$email = $conn->real_escape_string($_POST['email']);
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
echo "<script>alert('New record created successfully.');
window.location.href='display.php';</script>";
} else {
echo "Error: " . $conn->error;
}
}
$conn->close();
?>
Step 3: Create display.php to Show Data Dynamically
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM users ORDER BY id DESC";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Display Users</title>
</head>
<body>
<h2>Users List</h2>
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
<?php while ($row = $result->fetch_assoc()) { ?>
<tr>
<td><?php echo htmlspecialchars($row['id']); ?></td>
<td><?php echo htmlspecialchars($row['name']); ?></td>
<td><?php echo htmlspecialchars($row['email']); ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
<?php $conn->close(); ?>
