MySQL Full Course for free 🐬 (2023)

Introduction

This tutorial provides a comprehensive guide to MySQL, aimed at beginners looking to learn database management and SQL querying. It covers installation on various operating systems, fundamental concepts, and advanced functionalities, making it a valuable resource for anyone interested in mastering MySQL.

Step 1: Install MySQL

Installation on Windows

1. Download the MySQL installer from the official MySQL website.
2. Run the installer and choose the "Developer Default" setup type.
3. Follow the prompts to complete the installation.
4. Configure MySQL Server, setting the root password and selecting your preferred authentication method.

Installation on macOS

1. Download the MySQL DMG archive from the MySQL website.
2. Open the downloaded file and run the installer package.
3. Follow the installation instructions.
4. Start MySQL from System Preferences and set the root password.

Step 2: Understand Databases

• A database is a structured collection of data.
• Create a database using the command:

  CREATE DATABASE database_name;
  

• Show all databases with:

  SHOW DATABASES;
  

Step 3: Manage Tables

• Create a table using:

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );
  

• Example:

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100),
      email VARCHAR(100) NOT NULL
  );
  

Step 4: Insert Rows

• Insert data into a table:

  INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
  

Step 5: Select Data

• Retrieve data from a table:

  SELECT * FROM users;
  

Step 6: Update and Delete Data

• Update existing records:

  UPDATE users SET email = 'newemail@example.com' WHERE name = 'John Doe';
  

• Delete records:

  DELETE FROM users WHERE name = 'John Doe';
  

Step 7: Understand Transactions

• Use AUTOCOMMIT, COMMIT, and ROLLBACK to manage transactions:
  • Enable AUTOCOMMIT:

    SET AUTOCOMMIT = 1;
    

  • Commit a transaction:

    COMMIT;
    

  • Rollback a transaction:

    ROLLBACK;
    

Step 8: Use Functions

• Get current date and time:

  SELECT CURRENT_DATE(), CURRENT_TIME();
  

Step 9: Constraints

• Use constraints to enforce rules:
  • UNIQUE constraint:

    CREATE TABLE users (
        email VARCHAR(100) UNIQUE
    );
    

  • NOT NULL constraint ensures a column cannot have a NULL value.

Step 10: Keys and Indexes

• Primary keys uniquely identify records:

  CREATE TABLE users (
      id INT PRIMARY KEY
  );
  

• Use AUTO_INCREMENT for automatic ID generation.
• Create indexes to improve search performance:

  CREATE INDEX idx_email ON users(email);
  

Step 11: Joins

• Use joins to combine records from two or more tables:

  SELECT users.name, orders.amount
  FROM users
  JOIN orders ON users.id = orders.user_id;
  

Step 12: Advanced Queries

• Use GROUP BY to aggregate results:

  SELECT COUNT(*), country FROM users GROUP BY country;
  

• Use HAVING to filter grouped results:

  SELECT COUNT(*), country FROM users GROUP BY country HAVING COUNT(*) > 1;
  

Step 13: Stored Procedures and Triggers

• Create stored procedures for reusable SQL code:

  CREATE PROCEDURE procedure_name()
  BEGIN
      -- SQL statements
  END;
  

• Set triggers to perform actions before or after an event:

  CREATE TRIGGER trigger_name BEFORE INSERT ON users
  FOR EACH ROW
  BEGIN
      -- actions
  END;
  

Conclusion

This tutorial has covered the essentials of MySQL, from installation to advanced queries and database management techniques. Continue exploring additional features such as views and subqueries to deepen your understanding. Practice regularly to solidify your skills and consider working on practical projects to apply your knowledge in real-world scenarios.