MySQL full Course

MySQL HOME

Welcome to the MySQL Tutorial! MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage data in a database.

MySQL Intro

MySQL is a powerful and widely used RDBMS, which stands for Relational Database Management System. It is commonly used in web applications to store data in a structured manner.

MySQL RDBMS

MySQL follows the principles of an RDBMS, which stores data in tables that are related to each other. These tables are connected using keys, and data can be manipulated using SQL queries.

Here's an example of creating a simple table in MySQL:


-- Example of creating a table in MySQL
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);
    

This table contains columns for customer information. The customer_id column is the primary key, and it auto-increments with each new record added to the table.

1. MySQL SQL

MySQL SQL is the language used to interact with MySQL databases. Below is an example of selecting data from a table:


-- Example of a MySQL SELECT query
SELECT * FROM customers;
    

2. MySQL SELECT

The SELECT statement is used to retrieve data from a database. Here's an example of using SELECT to retrieve specific columns:


-- Example of a MySQL SELECT query with specific columns
SELECT first_name, last_name FROM customers;
    

3. MySQL WHERE

The WHERE clause is used to filter records that meet a certain condition. Here's an example:


-- Example of a MySQL query with WHERE condition
SELECT * FROM customers WHERE age > 30;
    

4. MySQL AND, OR, NOT

AND, OR, and NOT are logical operators that combine multiple conditions. Here's an example:


-- Example of MySQL query with AND, OR, NOT operators
SELECT * FROM customers WHERE age > 30 AND city = 'New York';
    

5. MySQL ORDER BY

The ORDER BY clause is used to sort the results of a query. Here's an example:


-- Example of a MySQL query with ORDER BY
SELECT * FROM customers ORDER BY last_name DESC;
    

6. MySQL INSERT INTO

The INSERT INTO statement is used to add new records to a table. Here's an example:


-- Example of a MySQL INSERT INTO query
INSERT INTO customers (first_name, last_name, age, city)
VALUES ('John', 'Doe', 25, 'Los Angeles');
    

7. MySQL NULL Values

NULL values represent missing or undefined data. Here's an example of using NULL in a query:


-- Example of a MySQL query checking for NULL values
SELECT * FROM customers WHERE phone_number IS NULL;
    

8. MySQL UPDATE

The UPDATE statement is used to modify existing records in a table. Here's an example:


-- Example of a MySQL UPDATE query
UPDATE customers SET age = 26 WHERE first_name = 'John' AND last_name = 'Doe';
    

9. MySQL DELETE

The DELETE statement is used to remove records from a table. Here's an example:


-- Example of a MySQL DELETE query
DELETE FROM customers WHERE age < 20;
    

10. MySQL LIMIT

The LIMIT clause is used to restrict the number of rows returned in a query. Here's an example:


-- Example of a MySQL query with LIMIT
SELECT * FROM customers LIMIT 5;
    

11. MySQL MIN and MAX

The MIN and MAX functions are used to get the smallest and largest values in a column. Here's an example:


-- Example of using MIN and MAX functions
SELECT MIN(age) AS youngest, MAX(age) AS oldest FROM customers;
    

12. MySQL COUNT, AVG, SUM

COUNT, AVG, and SUM are aggregate functions used to calculate the total count, average, and sum of a column. Here's an example:


-- Example of using COUNT, AVG, and SUM functions
SELECT COUNT(*) AS total_customers, AVG(age) AS average_age, SUM(age) AS total_age FROM customers;
    

13. MySQL LIKE

The LIKE operator is used to search for a pattern in a column. Here's an example:


-- Example of using LIKE with wildcards
SELECT * FROM customers WHERE last_name LIKE 'S%';
    

MySQL Wildcards

In MySQL, wildcards are used with the LIKE operator to search for a specified pattern in a column.

Common wildcards used in SQL are:

Example:


SELECT * FROM customers
WHERE first_name LIKE 'J%';
    

MySQL IN

The IN operator is used to specify multiple possible values for a column in a WHERE clause.


SELECT * FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');
    

MySQL BETWEEN

The BETWEEN operator is used to filter the result set within a certain range.


SELECT * FROM customers
WHERE age BETWEEN 20 AND 30;
    

MySQL Aliases

MySQL aliases are used to give a table or a column a temporary name.


SELECT first_name AS "First Name", last_name AS "Last Name"
FROM customers;
    

MySQL Joins

MySQL JOIN operations allow you to retrieve data from two or more tables based on a related column.

MySQL INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.


SELECT orders.order_id, customers.first_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
    

MySQL LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). If no match, NULL values are returned for columns from the right table.


SELECT customers.first_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
    

MySQL RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). If no match, NULL values are returned for columns from the left table.


SELECT customers.first_name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
    

MySQL CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the tables.


SELECT customers.first_name, orders.order_id
FROM customers
CROSS JOIN orders;
    

MySQL Self Join

A SELF JOIN is a regular join but the table is joined with itself.


SELECT a.first_name AS "Employee", b.first_name AS "Manager"
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
    

MySQL UNION

The UNION operator is used to combine the results of two or more SELECT statements.


SELECT city FROM customers
UNION
SELECT city FROM suppliers;
    

MySQL GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows, like "total" or "average".


SELECT city, COUNT(*)
FROM customers
GROUP BY city;
    

MySQL HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.


SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) > 5;
    

MySQL EXISTS

The EXISTS operator is used to check if a subquery returns any results. It returns TRUE if the subquery returns one or more records.


SELECT customer_id, first_name
FROM customers
WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id);
    

MySQL ANY, ALL

The ANY and ALL operators are used with subqueries in SQL to compare a value to a set of results returned by the subquery.

ANY compares a value to any value in a list or subquery, and ALL compares a value to all values in a list or subquery.


SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');
    

MySQL INSERT SELECT

The INSERT SELECT statement is used to insert the result of a SELECT query into a table.


INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM old_table
WHERE condition;
    

MySQL CASE

The CASE statement goes through conditions and returns a value when the first condition is met. It is similar to an IF statement.


SELECT first_name, last_name,
       CASE
           WHEN age < 20 THEN 'Young'
           WHEN age BETWEEN 20 AND 30 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM customers;
    

MySQL Null Functions

MySQL provides functions to handle NULL values in the database. These functions include ISNULL(), IFNULL(), COALESCE(), and more.


SELECT IFNULL(phone, 'Not Available') AS phone_number
FROM customers;
    

MySQL Comments

Comments in MySQL are used to provide explanations or notes within SQL code. There are two types of comments in MySQL:


-- This is a single-line comment
SELECT * FROM customers;

# Another single-line comment
SELECT * FROM orders;

/*
This is a multi-line comment
spanning multiple lines
*/
SELECT * FROM products;
    

MySQL Operators

Operators in MySQL are used to perform operations on database fields and values. They are categorized as:

Example:


SELECT first_name, last_name
FROM customers
WHERE age >= 18 AND city = 'New York';
    

MySQL Database

MySQL databases store all the data in tables. You can create, manage, and delete databases as required.


-- Create a new database
CREATE DATABASE my_database;

-- Use the database
USE my_database;

-- Show existing databases
SHOW DATABASES;
    

MySQL Create Database

The CREATE DATABASE statement is used to create a new database in MySQL.


CREATE DATABASE new_database;
    

MySQL Drop Database

The DROP DATABASE statement is used to delete an existing database in MySQL.


DROP DATABASE old_database;
    

MySQL Create Table

The CREATE TABLE statement is used to create a new table in a MySQL database.


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

MySQL Drop Table

The DROP TABLE statement is used to delete an existing table and its data in MySQL.


DROP TABLE users;
    

MySQL Alter Table

The ALTER TABLE statement is used to modify an existing table, such as adding, deleting, or modifying columns.


-- Add a new column
ALTER TABLE users ADD COLUMN age INT;

-- Modify a column
ALTER TABLE users MODIFY COLUMN age TINYINT;

-- Drop a column
ALTER TABLE users DROP COLUMN age;
    

MySQL Constraints

Constraints in MySQL are used to define rules for the data in a table. Some common constraints include:

MySQL Not Null

The NOT NULL constraint ensures that a column cannot contain NULL values.


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

MySQL Unique

The UNIQUE constraint ensures that all values in a column are distinct.


CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE,
    PRIMARY KEY (id)
);
    

MySQL Primary Key

The PRIMARY KEY constraint uniquely identifies each record in a table. It ensures that no two rows can have the same primary key value.


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

MySQL Foreign Key

The FOREIGN KEY constraint is used to link two tables together, ensuring data integrity between them.


CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    user_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
    

MySQL Check

The CHECK constraint ensures that the value in a column satisfies a specific condition.


CREATE TABLE users (
    id INT AUTO_INCREMENT,
    age INT,
    PRIMARY KEY (id),
    CHECK (age >= 18)
);
    

MySQL Default

The DEFAULT constraint provides a default value for a column when no value is specified during record insertion.


CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    status VARCHAR(50) DEFAULT 'active',
    PRIMARY KEY (id)
);
    

MySQL Create Index

The CREATE INDEX statement is used to create an index on a table to improve query performance.


CREATE INDEX idx_name ON users (name);
    

MySQL Auto Increment

The AUTO_INCREMENT attribute is used to automatically generate unique values for a column, often used with the primary key.


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

MySQL Dates

MySQL provides various date and time types, including DATE, DATETIME, TIME, YEAR, and more for handling date-related operations.


CREATE TABLE events (
    id INT AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,
    PRIMARY KEY (id)
);
    

MySQL Views

A VIEW is a virtual table based on the result of a SELECT query. It can simplify complex queries by abstracting them.


CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE status = 'active';

-- To query the view
SELECT * FROM active_users;
    

MySQL References

MySQL uses references to define foreign key relationships between tables. This ensures that data integrity is maintained across tables. A foreign key is a column that links to the primary key of another table.


-- Creating a table with a foreign key reference
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    user_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
    

MySQL Data Types

MySQL provides a wide variety of data types for storing different kinds of information. These data types can be broadly classified into the following categories:

Here is an example of creating a table with different data types:


CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    dob DATE,
    registration_date DATETIME,
    status ENUM('active', 'inactive') DEFAULT 'active',
    PRIMARY KEY (id)
);
    

MySQL Functions

MySQL offers a wide range of built-in functions that you can use to manipulate data and perform calculations. These include string functions, date functions, numeric functions, and more.

Here are a few examples of commonly used MySQL functions:

Example of using a function in a query:


-- Concatenate first and last name
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Find the current date
SELECT CURDATE();

-- Find the total number of users
SELECT COUNT(*) FROM users;