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 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 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.
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;
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;
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;
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';
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;
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');
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;
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';
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;
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;
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;
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;
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%';
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%';
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');
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 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 JOIN operations allow you to retrieve data from two or more tables based on a related column.
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;
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;
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;
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;
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;
The UNION operator is used to combine the results of two or more SELECT statements.
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
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;
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;
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);
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');
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;
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 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;
Comments in MySQL are used to provide explanations or notes within SQL code. There are two types of comments in MySQL:
-- or #/* comment */
-- 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;
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 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;
The CREATE DATABASE statement is used to create a new database in MySQL.
CREATE DATABASE new_database;
The DROP DATABASE statement is used to delete an existing database in MySQL.
DROP DATABASE old_database;
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)
);
The DROP TABLE statement is used to delete an existing table and its data in MySQL.
DROP TABLE users;
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;
Constraints in MySQL are used to define rules for the data in a table. Some common constraints include:
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)
);
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)
);
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)
);
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)
);
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)
);
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)
);
The CREATE INDEX statement is used to create an index on a table to improve query performance.
CREATE INDEX idx_name ON users (name);
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 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)
);
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 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 provides a wide variety of data types for storing different kinds of information. These data types can be broadly classified into the following categories:
INT, DECIMAL, FLOAT, DOUBLE, etc.VARCHAR, CHAR, TEXT, etc.DATE, DATETIME, TIME, etc.ENUM, SET, BLOB, etc.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 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:
CONCAT(), SUBSTRING(), LOWER(), UPPER(), etc.CURDATE(), NOW(), DATE_ADD(), DATEDIFF(), etc.ROUND(), FLOOR(), CEIL(), ABS(), etc.COUNT(), SUM(), AVG(), MAX(), MIN(), etc.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;