Learn MySQL

This repository helps you learn MySQL, a popular open-source database management system. Master MySQL and build powerful applications.

Table of Contents

What is MySQL?

MySQL is an open-source RDBMS that uses SQL for data management. It’s popular for its speed, reliability, and ease of use in web applications.

Back to Top⤴️

Why Learn MySQL?

Learning MySQL is crucial for database management and web development. Key reasons include:

  1. Popularity: Widely used by developers and organizations.
  2. Open Source: Free to use and contribute to.
  3. Compatibility: Works with various OS and languages.
  4. Performance: Fast and reliable.
  5. Scalability: Handles large data and scales well.
  6. Community Support: Strong developer community.

Back to Top⤴️

How to Learn MySQL?

  1. Basics: Learn database concepts, SQL, and MySQL.
  2. Practice: Regularly write SQL queries.
  3. Advanced Topics: Study joins, subqueries, transactions, etc.
  4. Projects: Build real-world projects.
  5. Documentation: Read official MySQL docs.
  6. Communities: Join forums and groups.
  7. Courses: Take online courses and tutorials.
  8. Stay Updated: Follow latest trends and updates.

Back to Top⤴️

Resources to Learn MySQL

Here are some resources to help you learn MySQL:

  1. Official MySQL Documentation: The official MySQL documentation provides comprehensive information on MySQL features, functions, and best practices.

  2. MySQL Tutorial: The MySQL Tutorial on the MySQL website offers a step-by-step guide to learning MySQL from the basics to advanced topics.

  3. W3Schools MySQL Tutorial: The W3Schools MySQL Tutorial provides interactive examples and exercises to help you learn SQL and MySQL.

  4. MySQL Workbench: MySQL Workbench is a visual database design tool that allows you to create, manage, and visualize MySQL databases.

  5. Online Courses: Platforms like Coursera, Udemy, and Pluralsight offer online courses on MySQL for beginners and advanced users.

  6. Books: Books like “MySQL Cookbook” by Paul DuBois and “Learning MySQL” by Seyed M.M. (Saied) Tahaghoghi and Hugh E. Williams are excellent resources for learning MySQL.

  7. YouTube Tutorials: YouTube channels like Programming with Mosh, freeCodeCamp, and Codecademy offer video tutorials on MySQL for beginners.

  8. Practice Platforms: Websites like LeetCode, HackerRank, and SQLZoo provide practice problems and challenges to help you improve your SQL and MySQL skills.

By utilizing these resources and actively engaging with MySQL, you can become proficient in working with databases and building robust applications.

Back to Top⤴️

MySQL Cheat Sheet

Here’s a comprehensive MySQL cheat sheet covering common commands and syntax:

Basic Commands

Here are examples for each of the MySQL commands:

  1. Connect to MySQL:

       mysql -u username -p
      
   mysql -u root -p

  2. Show Databases:

       SHOW DATABASES;

    Output:

       +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | mydatabase         |
   +--------------------+

  3. Use a Database:

       USE database_name;

   USE mydatabase;

  4. Show Tables:

       SHOW TABLES;

    Output:

       +------------------+
   | Tables_in_mydatabase |
   +------------------+
   | employees        |
   | departments      |
   +------------------+

  5. Show Table Structure:

       DESCRIBE table_name;

   DESCRIBE employees;

    Output:

       +------------+-------------+------+-----+---------+----------------+
   | Field      | Type        | Null | Key | Default | Extra          |
   +------------+-------------+------+-----+---------+----------------+
   | id         | int(11)     | NO   | PRI | NULL    | auto_increment |
   | first_name | varchar(50) | NO   |     | NULL    |                |
   | last_name  | varchar(50) | NO   |     | NULL    |                |
   | email      | varchar(100)| NO   | UNI | NULL    |                |
   +------------+-------------+------+-----+---------+----------------+

  6. Create Database:

       CREATE DATABASE database_name;
      
   CREATE DATABASE school;

  7. Create Table:

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

   CREATE TABLE students (
         id INT AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name VARCHAR(50),
         enrollment_date DATE
   );

  8. Insert Data:

       INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

   INSERT INTO students (first_name, last_name, enrollment_date)
   VALUES ('John', 'Doe', '2024-08-13');

  9. Select Data:

       SELECT column1, column2, ... FROM table_name WHERE condition;

   SELECT first_name, last_name FROM students WHERE enrollment_date = '2024-08-13';

    Output:

       +------------+-----------+
   | first_name | last_name |
   +------------+-----------+
   | John       | Doe       |
   +------------+-----------+

  10. Update Data:

      UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

  UPDATE students SET first_name = 'Jane' WHERE id = 1;

  11. Delete Data:

      DELETE FROM table_name WHERE condition;

  DELETE FROM students WHERE id = 1;

  12. Drop Table:

      DROP TABLE table_name;

  DROP TABLE students;

  13. Drop Database:

      DROP DATABASE database_name;
      
  DROP DATABASE school;

  14. Exit MySQL:

      EXIT;

  15. Show Users:

      SELECT user FROM mysql.user;

    Output:

      +------------------+
  | user             |
  +------------------+
  | root             |
  | mysql.infoschema |
  +------------------+

  16. Create User:

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

  CREATE USER 'student_user'@'localhost' IDENTIFIED BY 'password123';

  17. Grant Privileges:

      GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

  GRANT ALL PRIVILEGES ON school.* TO 'student_user'@'localhost';

  18. Flush Privileges:

      FLUSH PRIVILEGES;

  19. Backup Database:

      mysqldump -u username -p database_name > backup.sql

  mysqldump -u root -p school > school_backup.sql

  20. Restore Database:

      mysql -u username -p database_name < backup.sql

  mysql -u root -p school < school_backup.sql

  21. Show Grants:

      SHOW GRANTS FOR 'username'@'localhost';

  SHOW GRANTS FOR 'student_user'@'localhost';

    Output:

      +------------------------------------------------------------------------------------------------------------------+
  | Grants for student_user@localhost                                                                                |
  +------------------------------------------------------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON `school`.* TO `student_user`@`localhost`                                                 |
  +------------------------------------------------------------------------------------------------------------------+

  22. Revoke Privileges:

      REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

  REVOKE ALL PRIVILEGES ON school.* FROM 'student_user'@'localhost';

  23. Change Password:

      SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

  SET PASSWORD FOR 'student_user'@'localhost' = PASSWORD('newpassword123');

  24. Create Index:

      CREATE INDEX index_name ON table_name (column_name);

  CREATE INDEX idx_last_name ON students (last_name);

  25. Show Indexes:

      SHOW INDEX FROM table_name;

  SHOW INDEX FROM students;

    Output:

      +----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | students |          1 | idx_last_name  |            1 | last_name   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  +----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

  26. Explain Query:

      EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

  EXPLAIN SELECT first_name, last_name FROM students WHERE last_name = 'Doe';

    Output:

      +----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
  | id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
  +----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | students | NULL       | ref  | idx_last_name | idx_last_name| 153     | const |    1 |   100.00 | NULL  |
  +----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

  27. Show Processlist:

      SHOW PROCESSLIST;

    Output:

      +----+------+-----------+-----------+---------+------+-------+------------------+
  | Id | User | Host      | db        | Command | Time | State | Info             |
  +----+------+-----------+-----------+---------+------+-------+------------------+
  |  5 | root | localhost | mydatabase | Query   |    0 | init  | SHOW PROCESSLIST |
  +----+------+-----------+-----------+---------+------+-------+------------------+

  28. Kill Process:

      KILL process_id;

  KILL 5;

  29. Set Timezone:

      SET time_zone = timezone;

  SET time_zone = '+00:00';

  30. Show Variables:

      SHOW VARIABLES LIKE 'variable_name';

  SHOW VARIABLES LIKE 'time_zone';

    Output:

      +---------------+--------+
  | Variable_name | Value  |
  +---------------+--------+
  | time_zone     | +00:00 |
  +---------------+--------+

  31. Show Status:

      SHOW STATUS LIKE 'variable_name';

  SHOW STATUS LIKE 'Connections';

    Output:

      +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | Connections   | 12    |
  +---------------+-------+

Advanced Commands

Here are examples for each of the advanced MySQL commands:

  1. Joins:

       SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

   SELECT employees.first_name, employees.last_name, departments.department_name
   FROM employees
   JOIN departments ON employees.department_id = departments.id;

    Example Explanation: This query retrieves the first and last names of employees along with their department names by joining the employees table with the departments table on the department_id column.

  2. Group By:

       SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

   SELECT department_id, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department_id;

    Example Explanation: This query counts the number of employees in each department, grouping the results by department_id.

  3. Order By:

       SELECT * FROM table_name ORDER BY column_name;

   SELECT * FROM employees ORDER BY last_name ASC;

    Example Explanation: This query retrieves all records from the employees table and orders them alphabetically by last_name in ascending order.

  4. Aliases:

       SELECT column_name AS alias_name FROM table_name;

   SELECT first_name AS fname, last_name AS lname FROM employees;

    Example Explanation: This query selects the first_name and last_name columns from the employees table, renaming them to fname and lname respectively in the result set.

  5. Aggregate Functions:

       SELECT COUNT(column_name) FROM table_name;

   SELECT AVG(salary) AS average_salary FROM employees;

    Example Explanation: This query calculates the average salary of all employees using the AVG function.

  6. Subqueries:

       SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);

   SELECT first_name, last_name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE department_name = 'Sales');

    Example Explanation: This query retrieves the names of employees who work in the ‘Sales’ department by using a subquery to find the corresponding department_id.

  7. Views:

       CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

   CREATE VIEW employee_sales AS
   SELECT first_name, last_name, department_name
   FROM employees
   JOIN departments ON employees.department_id = departments.id
   WHERE department_name = 'Sales';

    Example Explanation: This command creates a view called employee_sales that shows the names of employees who work in the ‘Sales’ department.

  8. Transactions:

       START TRANSACTION;
   INSERT INTO table_name VALUES (value1, value2, ...);
   COMMIT;

   START TRANSACTION;
   INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
   INSERT INTO accounts (account_id, balance) VALUES (2, 2000);
   COMMIT;

    Example Explanation: This set of commands starts a transaction, inserts two records into the accounts table, and then commits the transaction to make the changes permanent.

  9. Stored Procedures:

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

   DELIMITER //
   CREATE PROCEDURE AddEmployee(IN first_name VARCHAR(50), IN last_name VARCHAR(50), IN dept_id INT)
   BEGIN
         INSERT INTO employees (first_name, last_name, department_id) VALUES (first_name, last_name, dept_id);
   END //
   DELIMITER ;

    Example Explanation: This command creates a stored procedure named AddEmployee that inserts a new employee into the employees table. The procedure takes three input parameters: first_name, last_name, and dept_id.

  10. Indexes:

      CREATE INDEX index_name ON table_name (column_name);

  CREATE INDEX idx_department_id ON employees (department_id);

    Example Explanation: This command creates an index named idx_department_id on the department_id column of the employees table to speed up queries involving that column.

  11. Foreign Keys:

      ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(column_name);

  ALTER TABLE orders
  ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

    Example Explanation: This command adds a foreign key constraint to the orders table, linking the customer_id column in orders to the id column in the customers table, ensuring referential integrity.

  12. Triggers:

      CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN
        -- Trigger logic here
  END;

  CREATE TRIGGER update_salary BEFORE UPDATE ON employees
  FOR EACH ROW
  BEGIN
        IF NEW.salary > OLD.salary THEN
        SET NEW.salary = OLD.salary;
        END IF;
  END;

Example Explanation: This command creates a trigger named update_salary that is executed before an update on the employees table. The trigger prevents the salary from being increased by resetting the new value to the old value if an attempt is made to increase it.

This cheat sheet provides a comprehensive overview of MySQL commands, syntax, and additional advanced topics like foreign keys and triggers.

Note

Remember to replace placeholders like username, database_name, table_name, etc., with your actual values. This cheat sheet should help you get started with basic MySQL commands and operations.

Back to Top⤴️

MySQL Data Types

Numeric Types

String Types

Date/Time Types

Numeric Functions

ROUND(num, decimals)     -- Round number
CEILING(num)             -- Round up
FLOOR(num)               -- Round down
ABS(num)                 -- Absolute value
RAND()                   -- Random number

Date Functions

```sqlNOW() – Current date and time CURDATE() – Current date YEAR(date) – Extract year MONTH(date) – Extract month DATEDIFF(date1, date2) – Difference between dates


[Back to Top⤴️](#table-of-contents)

## Common MySQL Functions

### String Functions

```sql
CONCAT(str1, str2)        -- Concatenate strings
SUBSTRING(str, pos, len)  -- Extract substring
UPPER(str)               -- Convert to uppercase
LOWER(str)               -- Convert to lowercase
TRIM(str)                -- Remove leading/trailing spaces

Back to Top⤴️

Common MySQL Errors and Solutions

Here are some common MySQL errors and their solutions:

  1. Error: Access denied for user 'username'@'localhost'

    Solution:

    • Ensure that the username and password are correct.
    • Check if the user has the necessary privileges.

    • Use the following command to grant privileges:

      GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

  2. Error: Can't connect to MySQL server on 'localhost'

    Solution:

    • Ensure that the MySQL server is running.
    • Check the MySQL server configuration file (my.cnf or my.ini) for the correct port and bind address.

    • Use the following command to start the MySQL server:

      sudo service mysql start

  3. Error: Unknown database 'database_name'

    Solution:

    • Verify that the database name is correct.

    • Use the following command to create the database if it does not exist:

      CREATE DATABASE database_name;

  4. Error: Table 'table_name' doesn't exist

    Solution:

    • Verify that the table name is correct.
    • Check if the table exists in the specified database.

    • Use the following command to create the table if it does not exist:

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

  5. Error: Duplicate entry 'value' for key 'PRIMARY'

    Solution:

    • Ensure that the value for the primary key is unique.

    • Use the following command to check for existing values:

      SELECT * FROM table_name WHERE primary_key_column = 'value';

  6. Error: Syntax error in SQL statement

    Solution:

    • Verify the SQL syntax.
    • Check for missing or extra commas, parentheses, or quotes.

    • Use the following command to help identify the error:

      EXPLAIN SELECT * FROM table_name WHERE condition;

Back to Top⤴️

Interview Questions

  1. What is MySQL?
    • MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating databases.
  2. What are the different data types in MySQL?
    • Numeric Types: INT, BIGINT, DECIMAL, FLOAT
    • String Types: VARCHAR, CHAR, TEXT, ENUM
    • Date/Time Types: DATE, TIME, DATETIME, TIMESTAMP
  3. How do you create a database in MySQL?

    • Use the CREATE DATABASE statement:

      CREATE DATABASE database_name;
  4. What is the difference between CHAR and VARCHAR?
    • CHAR is a fixed-length string, while VARCHAR is a variable-length string.
  5. How do you retrieve data from a MySQL table? (Using SELECT statement)

    • Use the SELECT statement:

      SELECT column1, column2 FROM table_name WHERE condition;
  6. What is the purpose of the PRIMARY KEY?
    • The primary key uniquely identifies each record in a table and ensures that no duplicate values exist.
  7. What are NULL values in MySQL?
    • NULL values represent missing or unknown data in a table.
  8. How do you insert data into a MySQL table?

    • Use the INSERT INTO statement:

      INSERT INTO table_name (column1, column2) VALUES (value1, value2);
  9. What is the difference between WHERE and HAVING clauses?
    • WHERE filters rows before grouping, while HAVING filters groups after grouping.
  10. How do you update existing records in MySQL?

    • Use the UPDATE statement:

         UPDATE table_name SET column1 = value1 WHERE condition;
  11. Explain the different types of JOINs in MySQL.
    • INNER JOIN: Returns records with matching values in both tables.
    • LEFT JOIN: Returns all records from the left table and matched records from the right table.
    • RIGHT JOIN: Returns all records from the right table and matched records from the left table.
    • FULL JOIN: Returns all records when there is a match in either left or right table.
  12. What is an INDEX in MySQL? Why is it important?
    • An index is a data structure that improves the speed of data retrieval operations on a database table.
  13. What are AUTO_INCREMENT fields in MySQL?
    • AUTO_INCREMENT fields automatically generate a unique number for each new record.
  14. How do you perform pattern matching in MySQL? (Using LIKE and wildcards)

    • Use the LIKE operator with wildcards (% and _):

         SELECT * FROM table_name WHERE column_name LIKE 'pattern%';
  15. What is a UNION and how is it different from JOIN?
    • UNION combines the results of two or more SELECT statements, while JOIN combines rows from two or more tables based on a related column.
  16. How do you prevent SQL injection in MySQL queries?
    • Use prepared statements and parameterized queries to prevent SQL injection.
  17. What is the difference between INNER JOIN and LEFT JOIN?
    • INNER JOIN returns only matching records from both tables, while LEFT JOIN returns all records from the left table and matched records from the right table.
  18. Explain the ACID properties in the context of MySQL transactions.
    • Atomicity: Ensures all operations within a transaction are completed successfully.
    • Consistency: Ensures the database remains in a consistent state before and after the transaction.
    • Isolation: Ensures transactions are isolated from each other.
    • Durability: Ensures that once a transaction is committed, it remains permanent.
  19. How do you create and manage stored procedures in MySQL?

    • Use the CREATE PROCEDURE statement:

         DELIMITER //
   CREATE PROCEDURE procedure_name()
   BEGIN
          -- SQL statements
   END //
   DELIMITER ;
  20. What are subqueries, and when would you use them?
    • Subqueries are nested queries used to perform operations that depend on the results of another query.
  21. What is normalization? Explain different normal forms.
    • Normalization is the process of organizing data to reduce redundancy. Normal forms include:
      • 1NF: Eliminate duplicate columns.
      • 2NF: Remove subsets of data that apply to multiple rows.
      • 3NF: Remove columns not dependent on the primary key.
  22. How does MySQL handle transactions? What are COMMIT and ROLLBACK?
    • Transactions are handled using START TRANSACTION, COMMIT, and ROLLBACK statements. COMMIT saves changes, while ROLLBACK undoes changes.
  23. What are triggers in MySQL? How do you create them?

    • Triggers are automatic actions executed in response to certain events on a table. Use the CREATE TRIGGER statement:

         CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN
          -- Trigger logic
   END;
  24. Explain the difference between InnoDB and MyISAM storage engines.
    • InnoDB supports transactions, foreign keys, and row-level locking. MyISAM is faster for read-heavy operations but does not support transactions or foreign keys.
  25. How does MySQL optimize queries? What is EXPLAIN used for?
    • MySQL optimizes queries using indexes and query execution plans. EXPLAIN is used to analyze and understand the execution plan of a query.
  26. What is a view in MySQL? How do you create and manage views?

    • A view is a virtual table based on the result of a SELECT query. Use the CREATE VIEW statement:

         CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
  27. What are foreign key constraints, and how do they maintain data integrity?
    • Foreign key constraints ensure that the value in one table matches a value in another table, maintaining referential integrity.
  28. How does replication work in MySQL? What are the types of replication?
    • Replication involves copying data from one MySQL server to another. Types include master-slave and master-master replication.
  29. What is partitioning in MySQL? What are its advantages?
    • Partitioning divides a table into smaller, more manageable pieces. Advantages include improved performance and easier maintenance.
  30. How do you handle concurrency issues in MySQL?
    • Use locking mechanisms like row-level locking and transactions to handle concurrency issues and ensure data consistency.

Back to Top⤴️

Conclusion

MySQL is a powerful and versatile database management system that is essential for anyone working with databases or developing web applications. By learning MySQL, you can build robust applications, manage data efficiently, and enhance your programming skills. Use the resources and cheat sheet provided in this repository to start your journey to mastering MySQL. Happy learning!

Back to Top⤴️

References

Back to Top⤴️

License

This repository is licensed under the MIT License.

Back to Top⤴️

Connect with me

Back to Top⤴️

Support

If you like this learning repository and find it useful, consider buying me a coffee or sponsoring me through the GitHub Sponsor. Your support will help me to continue and bring more exciting projects. Thank you!

Buy Me A Coffee

Sponsor Me

Show your support by 🌟 the repository.