Scribd
Upload
17 views

Mastering MySQL Notes

Uploaded by

Sahithya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
17 views

Mastering MySQL Notes

Uploaded by

Sahithya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Mastering MySQL - Comprehensive Notes

1. Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) that uses Structured

Query Language (SQL).

Key features include scalability, multi-user access, and support for complex queries.

It is widely used in web applications and enterprise systems.

2. Basic SQL Commands

**DDL (Data Definition Language)**:

- CREATE: Create databases or tables.

- ALTER: Modify table structure.

- DROP: Delete a table or database.

**DML (Data Manipulation Language)**:

- INSERT: Add data to a table.

- UPDATE: Modify data.

- DELETE: Remove data.

**DQL (Data Query Language)**:

- SELECT: Retrieve data from tables.

**TCL (Transaction Control Language)**:

- COMMIT: Save changes.

- ROLLBACK: Undo changes.


**DCL (Data Control Language)**:

- GRANT: Assign privileges.

- REVOKE: Remove privileges.

3. Database Design

**Schema Design**:

A schema is the logical structure of a database.

**Normalization**:

Process of organizing data to reduce redundancy:

- 1NF: Eliminate duplicate columns.

- 2NF: Ensure no partial dependencies.

- 3NF: Remove transitive dependencies.

**Relationships**:

- One-to-One

- One-to-Many

- Many-to-Many

4. Advanced Querying

**Joins**:

- INNER JOIN: Returns matching rows from both tables.

- LEFT JOIN: All rows from the left table, matching rows from the right.

- RIGHT JOIN: All rows from the right table, matching rows from the left.

- FULL OUTER JOIN: All rows when there is a match in either table.
**Subqueries**:

Nested queries to filter data.

**Window Functions**:

Enable calculations across a set of rows.

5. Indexes and Optimization

**Indexes**:

Indexes improve query performance.

- Syntax: CREATE INDEX index_name ON table_name(column_name);

**Optimization Techniques**:

- Use EXPLAIN to analyze query performance.

- Avoid SELECT *; instead, specify columns.

6. Stored Procedures and Functions

**Stored Procedures**:

Reusable blocks of SQL code.

Example:

CREATE PROCEDURE GetUser(IN userId INT) BEGIN SELECT * FROM users WHERE id =

userId; END;

**Functions**:

Return a single value.

Example:
CREATE FUNCTION GetTotalSalary() RETURNS DECIMAL BEGIN RETURN (SELECT

SUM(salary) FROM employees); END;

7. Transactions and Locking

**ACID Properties**:

- Atomicity: All operations succeed or none.

- Consistency: Database remains valid.

- Isolation: Concurrent transactions do not interfere.

- Durability: Changes persist after commit.

**Commands**:

- START TRANSACTION

- COMMIT

- ROLLBACK

8. Security and User Management

**Creating Users**:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

**Granting Privileges**:

GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';

**Revoking Privileges**:

REVOKE INSERT ON database_name.* FROM 'username'@'host';

9. Backup and Recovery


**Backup**:

- Using mysqldump: mysqldump -u username -p database_name > backup.sql

**Restore**:

- mysql -u username -p database_name < backup.sql

10. Replication and Clustering

**Replication**:

- Master-Slave: Changes in the master are replicated to slaves.

- Multi-Master: Updates are possible from multiple servers.

**Clustering**:

High availability solution where data is distributed across nodes.

11. Practical Projects

**Examples**:

- Library Management System

- E-commerce Database

- Real-Time Analytics Dashboard

You might also like