Here is a complete set of MySQL notes, including explanations, queries, and examples.
MySQL Notes
Introduction
MySQL is a popular relational database management system (RDBMS) that uses Structured Query
Language (SQL) to interact with databases.
Key Concepts:
1. Database: Collection of data organized in tables.
2. Table: Contains rows and columns where data is stored.
3. Query: Instructions given to the database to retrieve or manipulate data.
Basic SQL Commands
1. Database Operations
Create a Database
CREATE DATABASE my_database;
Use a Database
USE my_database;
Show All Databases
SHOW DATABASES;
Delete a Database
DROP DATABASE my_database;
2. Table Operations
Create a Table
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
grade CHAR(2)
);
Show All Tables
SHOW TABLES;
View Table Structure
DESCRIBE students;
Modify a Table
Add a Column:
ALTER TABLE students ADD email VARCHAR(100);
Delete a Column:
ALTER TABLE students DROP COLUMN email;
Delete a Table
DROP TABLE students;
3. Insert, Update, Delete Data
Insert Data
INSERT INTO students (name, age, grade) VALUES ('Alice', 22, 'A');
Insert Multiple Records
INSERT INTO students (name, age, grade) VALUES
('Bob', 21, 'B'),
('Charlie', 23, 'A');
Update Data
UPDATE students SET grade = 'A+' WHERE name = 'Bob';
Delete Data
DELETE FROM students WHERE name = 'Charlie';
4. Select Queries
Select All Records
SELECT * FROM students;
Select Specific Columns
SELECT name, age FROM students;
Filter Records (WHERE)
SELECT * FROM students WHERE age > 21;
Order Records
SELECT * FROM students ORDER BY age DESC;
Limit Records
SELECT * FROM students LIMIT 2;
5. Aggregate Functions
COUNT
SELECT COUNT(*) FROM students;
SUM
SELECT SUM(age) FROM students;
AVG
SELECT AVG(age) FROM students;
MAX
SELECT MAX(age) FROM students;
MIN
SELECT MIN(age) FROM students;
6. Joins
INNER JOIN
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;
LEFT JOIN
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;
RIGHT JOIN
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;
FULL OUTER JOIN
SELECT students.name, courses.course_name
FROM students
FULL OUTER JOIN courses ON students.id = courses.student_id;
7. Constraints
Primary Key: Uniquely identifies a row.
Foreign Key: Links two tables.
Unique: Ensures all values in a column are unique.
Not Null: Ensures a column cannot have a null value.
Default: Provides a default value for a column.
Example:
CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
student_id INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
8. Advanced Queries
Group By
SELECT grade, COUNT(*) FROM students GROUP BY grade;
Having Clause
SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 1;
Subquery
SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);
9. Indexes
Create Index
CREATE INDEX idx_name ON students(name);
Drop Index
DROP INDEX idx_name ON students;
10. Views
Create View
CREATE VIEW student_view AS
SELECT name, grade FROM students WHERE grade = 'A';
Use View
SELECT * FROM student_view;
Drop View
DROP VIEW student_view;
11. Stored Procedures
Create Procedure
DELIMITER //
CREATE PROCEDURE GetStudents()
BEGIN
SELECT * FROM students;
END //
DELIMITER ;
Call Procedure
CALL GetStudents();
12. Triggers
Create Trigger
DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
SET NEW.name = UPPER(NEW.name);
END //
DELIMITER ;
This is a comprehensive summary to get started with MySQL. If you need more examples or specific
queries, let me know!
