MySQL Tutorial

MySQL is a popular open-source relational database management system (RDBMS). It allows you to store, organize, and retrieve data efficiently. MySQL is known for being reliable, easy to use, and fast. It's widely used in web development to manage data for applications.

MySQL Features

• Open Source: MySQL is free to use, and its source code is available for everyone.
• Cross-Platform: It runs on various platforms like Windows, Linux, and MacOS.
• High Performance: MySQL is known for its speed and reliability, especially with large datasets.
• Secure: MySQL provides strong data security features to protect your data.
• Scalability: You can scale MySQL from small applications to large systems handling millions of queries.

MySQL Versions

MySQL has gone through various versions, each bringing new features and improvements. The current stable version is MySQL 8.0, which includes enhancements like better JSON support, improved performance, and more security features.

# Example of Checking MySQL Version
mysql --version
# Output Example: mysql  Ver 8.0.23 for Win64 on x86_64 (MySQL Community Server)
    

MySQL Data Types

MySQL supports various data types to store different kinds of information. Here are some common data types:

• INT: For integers (whole numbers).
• VARCHAR: For variable-length strings, like names or titles.
• TEXT: For larger text data, like descriptions.
• DATE: For storing dates (YYYY-MM-DD).
• FLOAT: For numbers with decimal points.

# Example of Creating a Table with Different Data Types
CREATE TABLE Users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  bio TEXT,
  birth_date DATE,
  score FLOAT
);
    

MySQL Variables

Variables in MySQL are used to store temporary data. You can use variables to perform operations or hold values during your session.

# Example of Using Variables in MySQL
SET @userName = 'John Doe';
SELECT @userName;  # Output: John Doe
  

Install MySQL

Installing MySQL is straightforward. You can download it from the official MySQL website and follow the installation wizard for your operating system. Alternatively, you can use package managers like APT (for Linux) or Homebrew (for MacOS).

# Installation Example for Ubuntu
sudo apt update
sudo apt install mysql-server

# Installation Example for MacOS
brew install mysql
  

MySQL Connection

Connecting to MySQL from your application or directly from the command line requires a username, password, and the database server's address. Below is an example of connecting to a MySQL database using JavaScript with Node.js:

// Example of Connecting to MySQL using Node.js
const mysql = require('mysql');

// Create a connection object
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'yourUsername',
  password: 'yourPassword',
  database: 'yourDatabase'
});

// Connect to MySQL
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL!');
});

// Query example
connection.query('SELECT * FROM Users', (err, results) => {
  if (err) throw err;
  console.log(results);
});

connection.end();
  

User Management in MySQL

Managing users in MySQL involves creating new users, deleting users, viewing existing users, and changing passwords. Proper user management ensures that your database is secure and only accessible by authorized individuals.

MySQL Create User

To create a new user in MySQL, use the CREATE USER statement. You must specify a username and password. This is useful for granting specific permissions to different users.

# Syntax for Creating a New User
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# Example
CREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'SecurePass123';
  

In this example, a user named john_doe is created, and the user can only connect from the local machine (localhost) with the password SecurePass123.

MySQL Drop User

If you need to remove a user, you can use the DROP USER statement. Make sure that you only drop users who no longer need access to the database.

# Syntax for Dropping a User
DROP USER 'username'@'host';

# Example
DROP USER 'john_doe'@'localhost';
  

In this example, the user john_doe is deleted, which means they will no longer be able to access the database.

MySQL Show Users

To view a list of all users in MySQL, you can query the mysql.user table. This will show you the usernames and other details stored in the database.

# Command to Show All Users
SELECT user, host FROM mysql.user;

# Example Output
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | localhost |
| john_doe  | localhost |
| user1     | %         |
+-----------+-----------+
  

This example lists all users and the hosts from which they can connect. The % host means the user can connect from any IP address.

Change User Password

Changing a user's password is simple using the ALTER USER or SET PASSWORD statement. This is important for security if a user's password is compromised or needs to be updated.

# Method 1: Using ALTER USER
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

# Example
ALTER USER 'john_doe'@'localhost' IDENTIFIED BY 'NewSecurePass456';

# Method 2: Using SET PASSWORD (for older MySQL versions)
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

# Example
SET PASSWORD FOR 'john_doe'@'localhost' = PASSWORD('NewSecurePass456');
  

The example above changes the password for the user john_doe to NewSecurePass456. Always use strong passwords to keep your database secure.

MySQL Database Management

A database is a collection of organized data that can be easily accessed, managed, and updated. MySQL allows you to create and manage databases, making it a powerful tool for web development and data management.

MySQL Create Database

To create a new database in MySQL, use the CREATE DATABASE command. This is the first step before you can start adding tables and data.

# Syntax for Creating a New Database
CREATE DATABASE database_name;

# Example
CREATE DATABASE my_new_database;
  

In this example, the database my_new_database is created. You can now start creating tables within this database.

MySQL Select Database

Once you have created a database, you need to select it before performing operations like creating tables or inserting data. Use the USE command to select a database.

# Syntax for Selecting a Database
USE database_name;

# Example
USE my_new_database;
  

The example selects the my_new_database for further operations. Any SQL commands you execute will now apply to this database.

MySQL Show Databases

To view all the databases that are currently available in your MySQL server, use the SHOW DATABASES command.

# Command to Show All Databases
SHOW DATABASES;

# Example Output
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_new_database    |
| test_db            |
+--------------------+
  

The example output lists all available databases, including the newly created my_new_database.

MySQL Drop Database

To delete a database, use the DROP DATABASE command. Be cautious when using this command as it permanently removes the database and all its contents.

# Syntax for Dropping a Database
DROP DATABASE database_name;

# Example
DROP DATABASE my_new_database;
  

The example deletes the my_new_database. Make sure you have a backup if the data is important.

MySQL Copy Database

To copy a database, there isn't a direct MySQL command, but you can use a combination of commands to achieve this. The following example uses the mysqldump tool for copying.

# Copy Database Using mysqldump (Command Line)
# Step 1: Export the database to a file
mysqldump -u username -p original_database > database_copy.sql

# Step 2: Create a new database
CREATE DATABASE new_database;

# Step 3: Import the SQL file into the new database
mysql -u username -p new_database < database_copy.sql;
  

In this example, the database original_database is exported to a file called database_copy.sql, and then imported into a new database named new_database.

MySQL Tables and Views

In MySQL, data is stored in tables, which are made up of rows and columns. A view is a virtual table that provides a way to look at data from one or more tables without changing the actual data.

MySQL CREATE Table

The CREATE TABLE statement is used to create a new table in the database. You define the table's structure by specifying columns, data types, and constraints.

# Syntax for Creating a New Table
CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  ...
);

# Example
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  age INT,
  grade VARCHAR(10)
);
  

In this example, a table named students is created with columns for id, name, age, and grade.

MySQL ALTER Table

The ALTER TABLE statement is used to modify an existing table. You can add, modify, or delete columns using this command.

# Syntax for Altering a Table
ALTER TABLE table_name
ADD column_name datatype;

# Example - Adding a New Column
ALTER TABLE students
ADD email VARCHAR(100);

# Example - Modifying an Existing Column
ALTER TABLE students
MODIFY COLUMN grade VARCHAR(5);

# Example - Dropping a Column
ALTER TABLE students
DROP COLUMN age;
  

In these examples, we add an email column, modify the grade column, and remove the age column from the students table.

MySQL Show Tables

To view a list of all tables in the current database, use the SHOW TABLES command.

# Command to Show All Tables
SHOW TABLES;

# Example Output
+-----------------+
| Tables_in_dbname |
+-----------------+
| students         |
| courses          |
+-----------------+
  

The example output lists all the tables in the selected database.

MySQL Rename Table

You can rename an existing table using the RENAME TABLE command. This changes the name of the table without affecting its structure or data.

# Syntax for Renaming a Table
RENAME TABLE old_table_name TO new_table_name;

# Example
RENAME TABLE students TO learners;
  

In this example, the table students is renamed to learners.

MySQL TRUNCATE Table

The TRUNCATE TABLE command removes all rows from a table, but keeps the table structure intact. This is faster than deleting rows one-by-one.

# Syntax for Truncating a Table
TRUNCATE TABLE table_name;

# Example
TRUNCATE TABLE learners;
  

In this example, all records in the learners table are deleted, but the table itself remains.

MySQL Describe Table

To see the structure of a table, use the DESCRIBE command. This shows information about each column, including the data type and constraints.

# Command to Describe a Table
DESCRIBE table_name;

# Example
DESCRIBE learners;

# Example Output
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)| NO   |     | NULL    |                |
| grade | varchar(5)  | YES  |     | NULL    |                |
| email | varchar(100)| YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
  

In this example, the DESCRIBE learners command provides detailed information about the columns in the table.

MySQL DROP Table

The DROP TABLE command is used to permanently delete a table and all of its data from the database. This action is irreversible, so use it with caution.

# Syntax for Dropping a Table
DROP TABLE table_name;

# Example
DROP TABLE students;
  

In this example, the students table is removed from the database, along with all the data it contains.

MySQL Temporary Table

A TEMPORARY table is a special type of table that exists only for the duration of a database session. It is automatically deleted when the session ends.

# Syntax for Creating a Temporary Table
CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
  ...
);

# Example
CREATE TEMPORARY TABLE temp_students (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);
  

In this example, a temporary table called temp_students is created. It will disappear after the session is closed.

MySQL Copy Table

You can copy an existing table's structure and/or data using the CREATE TABLE ... SELECT command or LIKE statement. This is useful for creating backups or similar tables.

# Syntax to Copy Table Structure and Data
CREATE TABLE new_table AS SELECT * FROM old_table;

# Example
CREATE TABLE students_backup AS SELECT * FROM students;

# Copy Only Structure (Without Data)
CREATE TABLE empty_copy LIKE students;
  

In these examples, students_backup copies all the data and structure of the students table, while empty_copy only duplicates the table structure.

MySQL Repair Table

If a table becomes corrupted, you can use the REPAIR TABLE command to attempt to fix it. This is helpful when tables have issues due to crashes or data corruption.

# Syntax to Repair a Table
REPAIR TABLE table_name;

# Example
REPAIR TABLE students;
  

In this example, the REPAIR TABLE students command tries to fix any corruption in the students table.

MySQL Add/Delete Column

You can modify a table's structure by adding or deleting columns using the ALTER TABLE command.

# Add a New Column
ALTER TABLE table_name ADD column_name datatype;

# Example - Adding a Column
ALTER TABLE students ADD email VARCHAR(100);

# Delete a Column
ALTER TABLE table_name DROP COLUMN column_name;

# Example - Deleting a Column
ALTER TABLE students DROP COLUMN age;
  

In these examples, an email column is added to the students table, and the age column is removed.

MySQL Show Columns

To display information about the columns in a table, use the SHOW COLUMNS command. This command provides details like the column name, type, and constraints.

# Command to Show Columns in a Table
SHOW COLUMNS FROM table_name;

# Example
SHOW COLUMNS FROM students;

# Example Output
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)| NO   |     | NULL    |                |
| email | varchar(100)| YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
  

In this example, the SHOW COLUMNS FROM students command lists details about each column in the students table.

MySQL Rename Column

To change the name of an existing column in a table, use the ALTER TABLE ... CHANGE or ALTER TABLE ... RENAME COLUMN command.

# Syntax to Rename a Column
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

# Example
ALTER TABLE students CHANGE name full_name VARCHAR(100);

# Or using RENAME COLUMN
ALTER TABLE students RENAME COLUMN name TO full_name;
  

In this example, the column name in the students table is renamed to full_name.

MySQL Views

A view is a virtual table based on the result-set of an SQL query. It doesn't store data itself but provides a way to look at data from one or more tables.

# Syntax to Create a View
CREATE VIEW view_name AS SELECT columns FROM table_name WHERE conditions;

# Example
CREATE VIEW student_names AS SELECT full_name, age FROM students WHERE age >= 18;

# Accessing the View
SELECT * FROM student_names;

# Dropping a View
DROP VIEW view_name;

# Example
DROP VIEW student_names;
  

This example creates a view called student_names to display students aged 18 or older. The view is later removed using the DROP VIEW command.

MySQL Table Locking

Table locking is used to prevent conflicts between multiple operations on the same table. Locks can be read locks (shared) or write locks (exclusive).

# Syntax to Lock a Table
LOCK TABLES table_name READ;   # For Read Lock
LOCK TABLES table_name WRITE;  # For Write Lock

# Example - Write Lock
LOCK TABLES students WRITE;

# Perform your operations here...

# Unlock Tables
UNLOCK TABLES;
  

In this example, a write lock is applied to the students table, preventing other users from modifying the table until the lock is released with UNLOCK TABLES.

MySQL Account Lock

You can lock a MySQL user account to prevent it from accessing the database. This is useful for security purposes, such as temporary account suspension.

# Syntax to Lock a User Account
ALTER USER 'username'@'host' ACCOUNT LOCK;

# Example
ALTER USER 'john'@'localhost' ACCOUNT LOCK;
  

In this example, the MySQL user 'john'@'localhost' is locked, meaning they cannot log in until the account is unlocked.

MySQL Account Unlock

To restore access for a locked user account, use the ALTER USER ... ACCOUNT UNLOCK command.

# Syntax to Unlock a User Account
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

# Example
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;
  

In this example, the previously locked MySQL user 'john'@'localhost' is unlocked, allowing them to access the database again.

MySQL Queries Overview

MySQL queries are instructions to interact with the database. Common queries include creating, reading, updating, and deleting records.

MySQL Constraints

Constraints are rules applied to table columns to enforce data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

# Example of Adding Constraints
CREATE TABLE students (
  id INT PRIMARY KEY,           # PRIMARY KEY Constraint
  email VARCHAR(100) UNIQUE,    # UNIQUE Constraint
  age INT NOT NULL,             # NOT NULL Constraint
  grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))  # CHECK Constraint
);
  

MySQL INSERT Record

Use the INSERT INTO statement to add a new record to a table.

# Syntax to Insert a Record
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

# Example
INSERT INTO students (id, name, age) VALUES (1, 'John Doe', 20);
  

MySQL UPDATE Record

Use the UPDATE statement to modify existing records in a table.

# Syntax to Update a Record
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

# Example
UPDATE students SET age = 21 WHERE id = 1;
  

MySQL DELETE Record

Use the DELETE FROM statement to remove records from a table.

# Syntax to Delete a Record
DELETE FROM table_name WHERE condition;

# Example
DELETE FROM students WHERE id = 1;
  

MySQL SELECT Record

The SELECT statement is used to retrieve data from the database.

# Syntax to Select Records
SELECT column1, column2 FROM table_name WHERE condition;

# Example
SELECT name, age FROM students WHERE age > 18;
  

MySQL Replace

The REPLACE statement works like INSERT, but replaces the record if it already exists.

# Syntax to Replace a Record
REPLACE INTO table_name (column1, column2) VALUES (value1, value2);

# Example
REPLACE INTO students (id, name, age) VALUES (1, 'Jane Doe', 22);
  

Insert On Duplicate Key Update

Use INSERT ... ON DUPLICATE KEY UPDATE to update a record if the insert fails due to a unique constraint.

# Syntax for Insert On Duplicate Key Update
INSERT INTO table_name (column1, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column2 = new_value;

# Example
INSERT INTO students (id, name) VALUES (1, 'John Smith')
ON DUPLICATE KEY UPDATE name = 'John Smith';
  

MySQL INSERT IGNORE

Use INSERT IGNORE to avoid errors and skip records that violate constraints.

# Syntax for Insert Ignore
INSERT IGNORE INTO table_name (column1, column2) VALUES (value1, value2);

# Example
INSERT IGNORE INTO students (id, email) VALUES (1, 'duplicate@example.com');
  

Insert Into Select

Use INSERT INTO ... SELECT to copy data from one table to another.

# Syntax for Insert Into Select
INSERT INTO target_table (column1, column2)
SELECT column1, column2 FROM source_table WHERE condition;

# Example
INSERT INTO alumni (name, graduation_year)
SELECT name, graduation_year FROM students WHERE graduation_year < 2020;
  

MySQL Indexes Overview

Indexes are used in MySQL to speed up data retrieval operations. An index creates a data structure that improves the efficiency of SELECT queries, but can slow down INSERT, UPDATE, and DELETE operations.

MySQL Create Index

Use the CREATE INDEX statement to add an index to a table column. Indexes can be applied to one or more columns to make search queries faster.

# Syntax to Create an Index
CREATE INDEX index_name ON table_name (column_name);

# Example
CREATE INDEX idx_lastname ON employees (last_name);
  

MySQL Drop Index

Use the DROP INDEX statement to remove an index from a table.

# Syntax to Drop an Index
DROP INDEX index_name ON table_name;

# Example
DROP INDEX idx_lastname ON employees;
  

MySQL Show Indexes

Use the SHOW INDEX statement to view all indexes in a specific table. This command provides details like the index name, columns used, and type of index.

# Syntax to Show Indexes
SHOW INDEX FROM table_name;

# Example
SHOW INDEX FROM employees;
  

MySQL Unique Index

A UNIQUE INDEX ensures that all values in a column are distinct. It prevents duplicate entries in the indexed column.

# Syntax to Create a Unique Index
CREATE UNIQUE INDEX index_name ON table_name (column_name);

# Example
CREATE UNIQUE INDEX idx_email ON users (email);
  

MySQL Clustered Index

A Clustered Index determines the physical order of data in a table. In MySQL, the Primary Key is a clustered index by default. There can only be one clustered index per table since it defines the table’s storage order.

# Creating a Clustered Index (Implicit with Primary Key)
CREATE TABLE products (
  product_id INT PRIMARY KEY,  # Primary Key is a Clustered Index
  product_name VARCHAR(100),
  price DECIMAL(10, 2)
);
  

MySQL Clustered vs Non-Clustered Index

A Clustered Index dictates the physical storage order of data, while a Non-Clustered Index creates a separate object in the table that points back to the original rows.

# Example of Non-Clustered Index
CREATE TABLE orders (
  order_id INT PRIMARY KEY,  # Clustered Index (by default)
  customer_id INT,
  order_date DATE
);

# Non-Clustered Index on order_date
CREATE INDEX idx_order_date ON orders (order_date);
  

In the example, order_id is the clustered index as it determines the physical order. idx_order_date is a non-clustered index used to speed up searches on the order_date column.

MySQL Clauses Overview

MySQL clauses are used to specify conditions and sorting when querying the database. Clauses can filter, organize, or limit results in SQL statements.

MySQL WHERE Clause

The WHERE clause is used to filter records that meet specific conditions.

# Syntax for WHERE Clause
SELECT column_name FROM table_name WHERE condition;

# Example
SELECT * FROM employees WHERE department = 'Sales';
  

In the example, only employees from the "Sales" department are selected.

MySQL DISTINCT Clause

The DISTINCT clause is used to return only unique (distinct) values, removing duplicates from the result set.

# Syntax for DISTINCT Clause
SELECT DISTINCT column_name FROM table_name;

# Example
SELECT DISTINCT department FROM employees;
  

The example selects a list of unique departments from the "employees" table.

MySQL FROM Clause

The FROM clause specifies the table to query data from. It is a mandatory part of a SELECT statement.

# Syntax for FROM Clause
SELECT column_name FROM table_name;

# Example
SELECT first_name, last_name FROM employees;
  

This example retrieves the first and last names of employees from the "employees" table.

MySQL ORDER BY Clause

The ORDER BY clause sorts the result set in ascending or descending order.

# Syntax for ORDER BY Clause
SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;

# Example
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
  

In the example, employees' names are sorted alphabetically by last name in ascending order.

MySQL GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

# Syntax for GROUP BY Clause
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

# Example
SELECT department, COUNT(*) FROM employees GROUP BY department;
  

This example counts the number of employees in each department.

MySQL HAVING Clause

The HAVING clause is used to filter groups after applying the GROUP BY clause. It works like a WHERE clause, but for grouped data.

# Syntax for HAVING Clause
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition;

# Example
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
  

In the example, it selects departments with more than 5 employees.

MySQL Privileges

MySQL provides a set of privileges that control what actions a user can perform on the database. These privileges can be granted or revoked.

MySQL Grant Privilege

The GRANT statement gives users permissions to perform certain tasks on the database.

# Syntax to Grant Privileges
GRANT privilege_type ON database_name.table_name TO 'username'@'host';

# Example
GRANT SELECT, INSERT ON employees.* TO 'john'@'localhost';
  

This example grants the user "john" the ability to SELECT and INSERT data in the "employees" database.

MySQL Revoke Privilege

The REVOKE statement removes previously granted privileges from a user.

# Syntax to Revoke Privileges
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

# Example
REVOKE INSERT ON employees.* FROM 'john'@'localhost';
  

In the example, the INSERT privilege is revoked from user "john" on the "employees" database.

MySQL Control Flow Functions Overview

MySQL provides several control flow functions that help in conditional logic. These functions are used to perform decision-making operations directly within SQL queries.

MySQL IF() Function

The IF() function in MySQL is used to perform a simple conditional test. It returns one value if the condition is true and another if it is false.

# Syntax for IF() Function
IF(condition, value_if_true, value_if_false);

# Example
SELECT IF(salary > 50000, 'High', 'Low') AS Salary_Category FROM employees;
  

In this example, if an employee's salary is greater than 50,000, it returns 'High', otherwise 'Low'.

MySQL IFNULL() Function

The IFNULL() function returns a specified value if the expression is NULL. Otherwise, it returns the expression itself.

# Syntax for IFNULL() Function
IFNULL(expression, value_if_null);

# Example
SELECT IFNULL(phone_number, 'Not Provided') AS Contact FROM customers;
  

In this example, if the "phone_number" is NULL, it displays "Not Provided".

MySQL NULLIF() Function

The NULLIF() function compares two expressions. If they are equal, it returns NULL; otherwise, it returns the first expression.

# Syntax for NULLIF() Function
NULLIF(expression1, expression2);

# Example
SELECT NULLIF(total_sales, 0) AS Sales_Value FROM sales;
  

If "total_sales" is 0, the result will be NULL. Otherwise, it will return the actual "total_sales" value.

MySQL CASE Statement

The CASE statement goes through conditions and returns a value when the first condition is met. If no conditions are true, it returns the value in the ELSE clause.

# Syntax for CASE Statement
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END;

# Example
SELECT 
  name, 
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 65 THEN 'Adult'
    ELSE 'Senior'
  END AS Age_Category 
FROM users;
  

In this example, the "users" table is queried to categorize each user as 'Minor', 'Adult', or 'Senior' based on their age.

MySQL IF Statement

The IF statement is used in stored procedures to perform conditional logic, similar to how it's used in other programming languages.

# Syntax for IF Statement in MySQL (Used in Stored Procedures)
IF condition THEN
  statements;
ELSE
  alternative_statements;
END IF;

# Example (Stored Procedure)
DELIMITER //
CREATE PROCEDURE CheckSalary (IN emp_id INT)
BEGIN
  DECLARE salary_check VARCHAR(20);

  IF (SELECT salary FROM employees WHERE id = emp_id) > 50000 THEN
    SET salary_check = 'High Salary';
  ELSE
    SET salary_check = 'Low Salary';
  END IF;

  SELECT salary_check;
END //
DELIMITER ;
  

In this example, a stored procedure checks if an employee's salary is above 50,000 and categorizes it as "High Salary" or "Low Salary".

MySQL Conditions Overview

MySQL provides several operators to filter and control query results based on specific conditions. These conditions help you to narrow down data according to your requirements.

MySQL AND Condition

The AND operator in MySQL is used to filter records based on more than one condition. All conditions connected by AND must be true.

# Example of MySQL AND Condition
SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;
  

This query retrieves employees who are in the 'Sales' department and have a salary greater than 50,000.

MySQL OR Condition

The OR operator in MySQL is used to filter records if any of the conditions is true.

# Example of MySQL OR Condition
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
  

This query retrieves employees who are in either the 'Sales' or 'Marketing' department.

MySQL AND OR Combination

You can combine AND and OR conditions to create complex queries.

# Example of MySQL AND OR Combination
SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000;
  

This query retrieves employees who are in 'Sales' or 'Marketing' departments and have a salary greater than 50,000.

MySQL LIKE Condition

The LIKE operator is used to search for a specified pattern in a column.

# Example of MySQL LIKE Condition
SELECT * FROM customers
WHERE name LIKE 'J%';
  

This query retrieves customers whose names start with 'J'.

MySQL IN Condition

The IN operator allows you to specify multiple values in a WHERE clause.

# Example of MySQL IN Condition
SELECT * FROM products
WHERE category IN ('Electronics', 'Clothing', 'Books');
  

This query retrieves products that belong to the categories 'Electronics', 'Clothing', or 'Books'.

MySQL NOT Condition

The NOT operator negates a condition, retrieving rows where the condition is false.

# Example of MySQL NOT Condition
SELECT * FROM employees
WHERE NOT department = 'Sales';
  

This query retrieves employees who are not in the 'Sales' department.

MySQL BETWEEN Condition

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

# Example of MySQL BETWEEN Condition
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
  

This query retrieves orders made between January 1, 2023, and December 31, 2023.

MySQL IS NULL and IS NOT NULL

The IS NULL operator is used to check for NULL values, while IS NOT NULL checks for non-NULL values.

# Example of IS NULL
SELECT * FROM employees
WHERE manager_id IS NULL;

# Example of IS NOT NULL
SELECT * FROM employees
WHERE email IS NOT NULL;
  

These queries check for employees without a manager and employees who have an email address, respectively.

MySQL Boolean

MySQL evaluates conditions as boolean values, where 0 is false and any non-zero value is true.

# Example of MySQL Boolean Condition
SELECT * FROM products
WHERE available = TRUE;
  

This query retrieves products that are marked as available (true).

MySQL Not Equal (!= or <>) Condition

The != or <> operators are used to check if two values are not equal.

# Example of Not Equal Condition
SELECT * FROM employees
WHERE department != 'Sales';
  

This query retrieves employees who are not in the 'Sales' department.

MySQL Joins Overview

In MySQL, joins are used to combine rows from two or more tables based on related columns. There are several types of joins, each serving a specific purpose when retrieving data from multiple tables.

MySQL JOIN

A general JOIN in MySQL combines rows from two or more tables. By default, MySQL uses an INNER JOIN if you don't specify the type.

# Basic MySQL JOIN Example
SELECT employees.name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.id;
  

This query retrieves the names of employees along with their department names.

MySQL INNER JOIN

An INNER JOIN returns records that have matching values in both tables.

# MySQL INNER JOIN Example
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;
  

This query retrieves only the employees that have a department assigned.

MySQL LEFT JOIN

A LEFT JOIN returns all records from the left table, and the matched records from the right table. If there's no match, the result is NULL from the right side.

# MySQL LEFT JOIN Example
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
  

This query retrieves all employees, including those who do not belong to any department.

MySQL RIGHT JOIN

A RIGHT JOIN returns all records from the right table, and the matched records from the left table. If there's no match, the result is NULL from the left side.

# MySQL RIGHT JOIN Example
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;
  

This query retrieves all departments, including those that don't have any employees assigned.

MySQL CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, which means all possible combinations of rows.

# MySQL CROSS JOIN Example
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
  

This query retrieves a combination of all employees with each department.

MySQL SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. It's useful for hierarchical data like organizational structures.

# MySQL SELF JOIN Example
SELECT A.name AS Employee, B.name AS Manager
FROM employees A
JOIN employees B
ON A.manager_id = B.id;
  

This query retrieves employees along with their managers.

MySQL DELETE JOIN

The DELETE JOIN statement allows deleting records from a table using a join condition.

# MySQL DELETE JOIN Example
DELETE employees
FROM employees
JOIN departments
ON employees.department_id = departments.id
WHERE departments.department_name = 'Closed';
  

This query deletes employees that belong to departments marked as 'Closed'.

MySQL Update Join

The UPDATE JOIN statement allows updating records in a table using a join condition.

# MySQL Update Join Example
UPDATE employees
JOIN departments
ON employees.department_id = departments.id
SET employees.salary = employees.salary * 1.10
WHERE departments.department_name = 'Sales';
  

This query increases the salary by 10% for employees in the 'Sales' department.

MySQL EquiJoin

An EquiJoin is a type of join that uses equality conditions. It is similar to an INNER JOIN where only matching records are returned.

# MySQL EquiJoin Example
SELECT employees.name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.id;
  

This query retrieves employee names and their department names using an equality condition.

MySQL Natural Join

A NATURAL JOIN is a type of join that automatically matches columns with the same name in both tables.

# MySQL Natural Join Example
SELECT employees.name, departments.department_name
FROM employees
NATURAL JOIN departments;
  

This query retrieves data where column names match between the two tables.

MySQL Keys Overview

In MySQL, keys are essential for creating relationships between tables and enforcing data integrity. They help in organizing and managing data effectively. Below are different types of keys used in MySQL.

MySQL Unique Key

A Unique Key in MySQL ensures that all values in a column are unique. It allows NULL values (but only one NULL per column).

# MySQL Unique Key Example
CREATE TABLE users (
  id INT AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE,
  username VARCHAR(50),
  PRIMARY KEY (id)
);
  

This example ensures that the email column has unique values for each user.

MySQL Primary Key

A Primary Key is a unique identifier for each record in a table. It must contain unique values and cannot contain NULL.

# MySQL Primary Key Example
CREATE TABLE employees (
  employee_id INT AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  PRIMARY KEY (employee_id)
);
  

In this example, employee_id is the primary key, uniquely identifying each employee.

MySQL Foreign Key

A Foreign Key is a field (or collection of fields) in one table, that refers to the Primary Key in another table. It creates a link between the two tables.

# MySQL Foreign Key Example
CREATE TABLE departments (
  department_id INT AUTO_INCREMENT,
  department_name VARCHAR(100),
  PRIMARY KEY (department_id)
);

CREATE TABLE employees (
  employee_id INT AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT,
  PRIMARY KEY (employee_id),
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
  

Here, department_id in the employees table is a foreign key that references the department_id in the departments table, establishing a relationship between employees and their departments.

MySQL Composite Key

A Composite Key is a combination of two or more columns in a table that together uniquely identify a row. It is often used when a single column is not sufficient to uniquely identify records.

# MySQL Composite Key Example
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);
  

In this example, the combination of order_id and product_id forms the composite key, ensuring that each product in an order is unique.

MySQL Triggers Overview

A trigger in MySQL is a set of SQL statements that automatically execute when a specified event occurs on a table. It is commonly used to automate processes, enforce constraints, or perform complex data validation.

MySQL Create Trigger

To create a trigger in MySQL, you use the CREATE TRIGGER statement. A trigger can be set to execute before or after an `INSERT`, `UPDATE`, or `DELETE` operation on a table.

# MySQL Create Trigger Example
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();
  

This example creates a trigger named before_employee_insert that sets the created_at field to the current timestamp whenever a new employee is inserted into the `employees` table.

MySQL Show Trigger

To display the list of triggers in your database, you can use the SHOW TRIGGERS command.

# MySQL Show Triggers
SHOW TRIGGERS FROM your_database_name;
  

Replace your_database_name with the name of your database to see all triggers associated with it.

MySQL DROP Trigger

To delete a trigger, use the DROP TRIGGER statement followed by the trigger name.

# MySQL DROP Trigger Example
DROP TRIGGER IF EXISTS before_employee_insert;
  

This example removes the trigger before_employee_insert if it exists.

MySQL BEFORE INSERT Trigger

A BEFORE INSERT trigger executes before a new row is inserted into the table. It is useful for validating or modifying data before it is added.

# MySQL BEFORE INSERT Trigger Example
CREATE TRIGGER before_insert_check_age
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.age < 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be at least 18.';
  END IF;
END;
  

This example creates a trigger that prevents users younger than 18 from being added to the `users` table.

MySQL AFTER INSERT Trigger

An AFTER INSERT trigger runs after a new row is inserted into the table. It is often used for logging or maintaining related data.

# MySQL AFTER INSERT Trigger Example
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_log (order_id, status, created_at)
  VALUES (NEW.order_id, 'Order Created', NOW());
END;
  

In this example, after a new order is inserted into the `orders` table, an entry is automatically added to the `order_log` table.

MySQL BEFORE UPDATE Trigger

A BEFORE UPDATE trigger executes before an existing row is updated. It is often used for validation or audit purposes.

# MySQL BEFORE UPDATE Trigger Example
CREATE TRIGGER before_update_check_quantity
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.stock_quantity < 0 THEN
    SET NEW.stock_quantity = 0;
  END IF;
END;
  

This trigger ensures that the `stock_quantity` does not go below zero in the `products` table.

MySQL AFTER UPDATE Trigger

An AFTER UPDATE trigger executes after an existing row is updated. It's useful for logging changes or maintaining history.

# MySQL AFTER UPDATE Trigger Example
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_log (employee_id, action, updated_at)
  VALUES (OLD.employee_id, 'Updated', NOW());
END;
  

In this example, after an employee record is updated, a log entry is created in the `employee_log` table.

MySQL BEFORE DELETE Trigger

A BEFORE DELETE trigger is executed before a row is deleted. It can be used to check or prepare data before deletion.

# MySQL BEFORE DELETE Trigger Example
CREATE TRIGGER before_delete_protect_admin
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  IF OLD.role = 'admin' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Admin users cannot be deleted.';
  END IF;
END;
  

This example prevents deletion of users with the role `admin`.

MySQL AFTER DELETE Trigger

An AFTER DELETE trigger executes after a row has been deleted. It can be used for cleanup or maintaining history.

# MySQL AFTER DELETE Trigger Example
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO deleted_users (user_id, deleted_at)
  VALUES (OLD.user_id, NOW());
END;
  

Here, when a user is deleted from the `users` table, the user's ID and deletion time are recorded in the `deleted_users` table.

MySQL Aggregate Functions Overview

Aggregate functions in MySQL are used to perform calculations on multiple rows of a table's column and return a single value. They are often used with the `GROUP BY` clause to aggregate data based on specific conditions.

MySQL COUNT()

The COUNT() function returns the number of rows that match a specified condition. It is useful for counting entries in a table.

# MySQL COUNT() Example
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
  

This query counts the number of orders with the status of `shipped`.

MySQL SUM()

The SUM() function calculates the total sum of a numeric column. It is commonly used to find the total amount of sales, costs, or other numerical data.

# MySQL SUM() Example
SELECT SUM(total_amount) FROM sales WHERE year = 2023;
  

This example returns the total amount of sales for the year 2023.

MySQL AVG()

The AVG() function returns the average value of a numeric column. It is useful for calculating average sales, ratings, or any other numeric averages.

# MySQL AVG() Example
SELECT AVG(rating) FROM product_reviews WHERE product_id = 101;
  

This query calculates the average rating for a product with an ID of 101.

MySQL MIN()

The MIN() function returns the smallest value in a set. It is often used to find the minimum price, age, or other minimum values in a column.

# MySQL MIN() Example
SELECT MIN(price) FROM products WHERE category = 'Electronics';
  

This query finds the minimum price of products in the 'Electronics' category.

MySQL MAX()

The MAX() function returns the largest value in a set. It is typically used to find the highest price, age, or other maximum values in a column.

# MySQL MAX() Example
SELECT MAX(salary) FROM employees WHERE department = 'IT';
  

This example retrieves the highest salary in the 'IT' department.

MySQL GROUP_CONCAT()

The GROUP_CONCAT() function concatenates values from multiple rows into a single string. It is useful for combining names, IDs, or any text fields from multiple rows into a single output.

# MySQL GROUP_CONCAT() Example
SELECT GROUP_CONCAT(name) FROM students WHERE class = '10th Grade';
  

This query concatenates the names of all students in the '10th Grade' into a single string.

MySQL FIRST()

MySQL does not have a direct FIRST() function, but you can simulate it using `ORDER BY` and `LIMIT`. It is often used to get the first entry in an ordered list.

# MySQL FIRST() Example (Simulated)
SELECT * FROM employees ORDER BY hire_date ASC LIMIT 1;
  

This example retrieves the first employee who was hired based on the earliest `hire_date`.

MySQL LAST()

Similar to FIRST(), MySQL does not have a built-in `LAST()` function, but you can use `ORDER BY` with `LIMIT` to achieve the same result. It retrieves the last entry in an ordered list.

# MySQL LAST() Example (Simulated)
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;
  

This query retrieves the last employee who was hired based on the most recent `hire_date`.