MYSQL
Learn SQL and relational database concepts using MySQL to
structure and manage tabular data.
Introduction to MySQL
MySQL is an open-source Relational Database Management System (RDBMS) based on Structured Query Language (SQL). It is used for storing and managing data using tables, rows, and columns.
You can install MySQL on Windows, Mac, or Linux using the official installer or a package manager. Common tools: XAMPP, WAMP, or MySQL Workbench.
Use the MySQL client to connect:
mysql -u root -p
Use the CREATE DATABASE command to create a new database.
CREATE DATABASE college;
Switch to a database using USE:
USE college;
Use CREATE TABLE to define table structure.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100)
);
Insert a record using INSERT INTO:
INSERT INTO students VALUES (1, 'Alice', 20, 'alice@example.com');
Retrieve records with SELECT:
SELECT * FROM students;
Filter results with the WHERE clause:
SELECT * FROM students WHERE age > 18;
Use UPDATE to modify data:
UPDATE students SET age = 21 WHERE id = 1;
MySQL Basics Continued
Use DELETE to remove data from a table.
DELETE FROM students WHERE id = 1;
Sort results in ascending or descending order using
ORDER BY.
SELECT * FROM students ORDER BY age ASC;
SELECT * FROM students ORDER BY name DESC;
Use LIMIT to restrict the number of rows returned.
SELECT * FROM students LIMIT 5;
Search for a pattern using LIKE.
SELECT * FROM students WHERE name LIKE 'A%';
SELECT * FROM students WHERE email LIKE '%@gmail.com';
Used to perform calculations on a set of values.
SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;
SELECT MAX(age), MIN(age) FROM students;
Group rows that have the same values into summary rows.
SELECT age, COUNT(*) FROM students GROUP BY age;
HAVING is used to filter the results of
GROUP BY.
SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;
Give temporary names to columns or tables using AS.
SELECT name AS 'Student Name', age AS 'Student Age' FROM students;
Return only distinct (different) values.
SELECT DISTINCT age FROM students;
Find values within a range.
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
MySQL Joins and Constraints
Returns records with matching values in both tables.
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.id;
Returns all records from the left table and matched records from the right.
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.id;
Returns all records from the right table and matched ones from the left.
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.id;
MySQL doesn’t support full outer join directly. Use
UNION of LEFT and RIGHT joins.
SELECT * FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
Uniquely identifies each record in a table.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50)
);
Links two tables by a common field.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
Ensures all values in a column are different.
CREATE TABLE users (
id INT,
email VARCHAR(100) UNIQUE
);
Ensures a column cannot have NULL values.
CREATE TABLE students (
id INT,
name VARCHAR(50) NOT NULL
);
Provides a default value for a column when none is specified.
CREATE TABLE products (
id INT,
status VARCHAR(10) DEFAULT 'active'
);
Ensures a column’s value satisfies a specific condition.
CREATE TABLE students (
id INT,
age INT CHECK (age >= 18)
);
MySQL Views, Indexes, and Transactions
A view is a virtual table based on a SQL query. It does not store data but fetches results dynamically.
CREATE VIEW student_names AS
SELECT name, age FROM students;
SELECT * FROM student_names;
Indexes improve the speed of data retrieval operations on a table.
CREATE INDEX idx_name ON students(name);
DROP INDEX idx_name ON students;
A stored procedure is a set of SQL statements saved to be reused.
DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
SELECT * FROM students;
END //
DELIMITER ;
CALL GetAllStudents();
Returns a value and can be used in SQL expressions.
DELIMITER //
CREATE FUNCTION Square(num INT) RETURNS INT
RETURN num * num; //
DELIMITER ;
SELECT Square(5);
A trigger is a function that is automatically invoked before or after a specific event (INSERT, UPDATE, DELETE).
CREATE TRIGGER before_insert_student
BEFORE INSERT ON students
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);
A transaction is a group of SQL statements that are executed together to ensure data integrity.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Used to undo transactions that are not yet committed.
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
ROLLBACK;
Used to permanently save a transaction in the database.
START TRANSACTION;
UPDATE students SET age = age + 1;
COMMIT;
Automatically generates a unique number when a new record is inserted.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
ENUM allows a field to be one of several predefined values. SET allows multiple values.
CREATE TABLE user_roles (
role ENUM('Admin', 'Editor', 'Viewer'),
access SET('Read', 'Write', 'Delete')
);
MySQL Advanced and Administrative Topics
Use the mysqldump command-line utility to back up your
database into a .sql file.
mysqldump -u root -p college > college_backup.sql
Use the mysql client to restore a backup file into an
existing database.
mysql -u root -p college < college_backup.sql
To create a new MySQL user:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Grant permissions to a user on a database or table.
GRANT ALL PRIVILEGES ON college.* TO 'newuser'@'localhost';
Remove specific privileges from a user.
REVOKE SELECT, INSERT ON college.* FROM 'newuser'@'localhost';
Export table data to a CSV or text file.
SELECT * FROM students
INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Import a CSV file into a table.
LOAD DATA INFILE '/tmp/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
CASE is used for conditional logic in SQL queries (like
IF...ELSE).
SELECT name,
CASE
WHEN age >= 18 THEN 'Adult'
ELSE 'Minor'
END AS status
FROM students;
Useful for querying, formatting, and manipulating dates.
SELECT CURDATE(), NOW(), YEAR(NOW()), MONTH(NOW());
SELECT DATEDIFF('2025-12-31', '2025-01-01');
MySQL’s metadata tables used to get database/table/column information.
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'college';
Reference Links for MySQL
Comprehensive and authoritative reference for all MySQL features.
Beginner-friendly guide with interactive examples.
https://www.w3schools.com/mysql/
Explanations, examples, and use-cases for developers and students.
https://www.geeksforgeeks.org/mysql-tutorial/
Step-by-step MySQL tutorials and exercises.
https://www.tutorialspoint.com/mysql/index.htm
Useful for trying SQL queries online and understanding function behavior.