5

MySQL – Notes

1. Introduction

  • MySQL is an open-source relational database management system (RDBMS).

  • Used to store, manage, and retrieve data in databases.

  • Works with SQL (Structured Query Language) for database operations.

  • Developed by Oracle Corporation.

  • Popular for web applications, e.g., with PHP, Python, Java.

2. Features of MySQL

  1. Open-source & free

  2. High performance

  3. Cross-platform (Windows, Linux, Mac)

  4. Scalable & flexible

  5. Supports transactions

  6. Secure – Supports user privileges and passwords

  7. Supports multiple storage engines (InnoDB, MyISAM)

3. MySQL Databases

  • Database: Collection of related data organized in tables.

  • Table: Structure to store data in rows (records) and columns (fields).

Example Table: students

idnameagegrade
1Dinesh25A
2Ravi23B

4. MySQL Data Types

  1. Numeric Types: INT, FLOAT, DOUBLE, DECIMAL

  2. String Types: CHAR(n), VARCHAR(n), TEXT, BLOB

  3. Date & Time Types: DATE, DATETIME, TIMESTAMP, TIME, YEAR

  4. Boolean: BOOLEAN or TINYINT(1)

5. MySQL Basic Commands

  1. Create Database

 
CREATE DATABASE mydb;

  1. Use Database

 
USE mydb;

  1. Drop Database

 
DROP DATABASE mydb;

  1. Show Databases

 
SHOW DATABASES;

6. MySQL Table Commands

  1. Create Table

 
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, grade CHAR(1) );

  1. Drop Table

 
DROP TABLE students;

  1. Alter Table

 
ALTER TABLE students ADD COLUMN email VARCHAR(50); ALTER TABLE students DROP COLUMN email; ALTER TABLE students MODIFY COLUMN age INT(3);

  1. Show Tables

 
SHOW TABLES;

7. CRUD Operations

  1. Insert Data

 
INSERT INTO students (id,name,age,grade) VALUES (1,'Dinesh',25,'A');

  1. Select Data

 
SELECT * FROM students; SELECT name, age FROM students WHERE grade='A';

  1. Update Data

 
UPDATE students SET age=26 WHERE name='Dinesh';

  1. Delete Data

 
DELETE FROM students WHERE name='Ravi';

8. Filtering & Sorting

  • WHERE Clause

 
SELECT * FROM students WHERE age>20;

  • ORDER BY

 
SELECT * FROM students ORDER BY age DESC;

  • LIMIT

 
SELECT * FROM students LIMIT 5;

  • LIKE (Pattern Matching)

 
SELECT * FROM students WHERE name LIKE 'D%';

9. Aggregate Functions

FunctionDescription
COUNT()Count rows
SUM()Sum values
AVG()Average value
MIN()Minimum value
MAX()Maximum value

Example:

 
SELECT COUNT(*) FROM students; SELECT AVG(age) FROM students;

10. Grouping

  • GROUP BY – Group rows with same value

 
SELECT grade, COUNT(*) FROM students GROUP BY grade;

  • HAVING – Filter groups

 
SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*)>1;

11. Joins

  • Combine data from multiple tables

  1. INNER JOIN

 
SELECT students.name, marks.score FROM students INNER JOIN marks ON students.id = marks.student_id;

  1. LEFT JOIN

 
SELECT students.name, marks.score FROM students LEFT JOIN marks ON students.id = marks.student_id;

  1. RIGHT JOIN

 
SELECT students.name, marks.score FROM students RIGHT JOIN marks ON students.id = marks.student_id;

  1. FULL OUTER JOIN (not directly in MySQL, use UNION)

12. Indexes

  • Improve search speed on tables

 
CREATE INDEX idx_name ON students(name); DROP INDEX idx_name ON students;

13. Keys

  1. Primary Key – Unique identifier for each row

  2. Foreign Key – Links one table to another

 
CREATE TABLE marks ( id INT PRIMARY KEY, student_id INT, score INT, FOREIGN KEY (student_id) REFERENCES students(id) );

14. Views

  • Virtual table based on query

 
CREATE VIEW top_students AS SELECT name, grade FROM students WHERE grade='A'; SELECT * FROM top_students;

15. Transactions

  • Group of queries executed together

 
START TRANSACTION; UPDATE students SET age=26 WHERE id=1; UPDATE students SET grade='B' WHERE id=2; COMMIT; -- Save changes ROLLBACK; -- Undo changes

16. MySQL Functions

  1. String Functions: CONCAT(), UPPER(), LOWER(), SUBSTRING()

  2. Numeric Functions: ROUND(), CEIL(), FLOOR(), ABS()

  3. Date Functions: NOW(), CURDATE(), DATE_ADD(), DATEDIFF()

17. User & Privileges

  • Create user

 
CREATE USER 'dinesh'@'localhost' IDENTIFIED BY 'password';

  • Grant privileges

 
GRANT ALL PRIVILEGES ON mydb.* TO 'dinesh'@'localhost'; FLUSH PRIVILEGES;

  • Revoke privileges

 
REVOKE ALL PRIVILEGES ON mydb.* FROM 'dinesh'@'localhost';

18. Backup & Restore

  • Backup database

 
mysqldump -u root -p mydb > backup.sql

  • Restore database

 
mysql -u root -p mydb < backup.sql

19. Important Notes

  • SQL commands not case-sensitive (SELECT = select)

  • Use ; to terminate statements

  • Always use WHERE clause with UPDATE and DELETE to avoid affecting all rows