MySQL – Creating Web Database and Working with Data
1. Introduction to MySQL
-
MySQL is a Relational Database Management System (RDBMS).
-
Uses SQL (Structured Query Language) to manage data.
-
Stores data in tables (rows + columns).
-
Widely used with PHP for web applications.
2. Using MySQL Monitor
2.1 Logging into MySQL
mysql -u root -p-
-u→ username -
-p→ password prompt
2.2 Creating Database
CREATE DATABASE college;2.3 Selecting Database
USE college;2.4 Creating Users
CREATE USER 'shivam'@'localhost' IDENTIFIED BY 'password123';2.5 Setting Privileges
GRANT ALL PRIVILEGES ON college.* TO 'shivam'@'localhost';
FLUSH PRIVILEGES;3. Column Data Types in MySQL
3.1 Numeric Types
-
INT → Integer
-
FLOAT / DOUBLE → Decimal numbers
3.2 String Types
-
VARCHAR(n) → Variable length string
-
CHAR(n) → Fixed length string
-
TEXT → Large text
3.3 Date & Time Types
-
DATE → YYYY-MM-DD
-
DATETIME → Date + Time
-
TIMESTAMP
3.4 Example Table
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
marks FLOAT
);4. Working with MySQL Database
4.1 Inserting Data
INSERT INTO students (name, age, marks)
VALUES ('Shivam', 21, 85);4.2 Retrieving Data
SELECT * FROM students;4.3 Retrieving Specific Columns
SELECT name, marks FROM students;4.4 Retrieving Data with Conditions
SELECT * FROM students
WHERE marks > 70;4.5 Retrieving Data in Order
SELECT * FROM students
ORDER BY marks DESC;4.6 Updating Records
UPDATE students
SET marks = 90
WHERE id = 1;4.7 Deleting Records
DELETE FROM students
WHERE id = 1;5. Working with Multiple Tables
5.1 Creating Another Table
CREATE TABLE courses (
course_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_name VARCHAR(50)
);5.2 Retrieving Data from Multiple Tables (JOIN)
SELECT students.name, courses.course_name
FROM students
JOIN courses
ON students.id = courses.student_id;6. Grouping and Aggregate Functions
6.1 Aggregate Functions
-
COUNT()
-
SUM()
-
AVG()
-
MAX()
-
MIN()
6.2 Example
SELECT AVG(marks) FROM students;6.3 GROUP BY
SELECT age, COUNT(*)
FROM students
GROUP BY age;7. Subqueries
7.1 Definition
- A query inside another query
7.2 Example
SELECT name FROM students
WHERE marks > (SELECT AVG(marks) FROM students);8. Dropping Tables and Databases
8.1 Drop Table
DROP TABLE students;8.2 Drop Database
DROP DATABASE college;9. Complete Working Program (PHP + MySQL)
Problem: Student Database CRUD
<?php
$conn = new mysqli("localhost", "root", "", "college");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert Data
$conn->query("INSERT INTO students (name, age, marks)
VALUES ('Shivam', 21, 85)");
// Retrieve Data
$result = $conn->query("SELECT * FROM students");
echo "<h3>Students:</h3>";
while ($row = $result->fetch_assoc()) {
echo $row['name'] . " - " . $row['marks'] . "<br>";
}
// Update Data
$conn->query("UPDATE students SET marks=95 WHERE name='Shivam'");
// Delete Data
$conn->query("DELETE FROM students WHERE name='Test'");
$conn->close();
?>10. Summary
10.1 Database Creation
-
CREATE DATABASE
-
CREATE USER
-
GRANT PRIVILEGES
10.2 Data Operations (CRUD)
-
INSERT → Add data
-
SELECT → Retrieve data
-
UPDATE → Modify data
-
DELETE → Remove data
10.3 Advanced Queries
-
JOIN → Multiple tables
-
GROUP BY → Group data
-
Aggregate functions → COUNT, AVG
-
Subqueries → Nested queries
10.4 Important Commands
-
DROP TABLE
-
DROP DATABASE