1.
Introduction to MySQL
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query
Language (SQL) to manage data. It's widely used for web applications and is a popular choice for
databases in various environments due to its reliability, performance, and ease of use.
2. MySQL Basics
Database: A collection of organized data stored electronically. In MySQL, databases contain
tables.
Table: A collection of related data organized in rows and columns.
Row: A single record in a table.
Column: A field in a table, which stores specific data types.
3. MySQL Data Types
MySQL supports various data types to store different kinds of data:
Numeric Types: INT, FLOAT, DOUBLE, DECIMAL
Date and Time Types: DATE, TIME, DATETIME, TIMESTAMP
String Types: CHAR, VARCHAR, TEXT, BLOB
Boolean Type: BOOLEAN (typically stored as TINYINT(1))
4. MySQL Functions
Functions in MySQL are predefined operations that perform calculations, manipulate data, or perform
operations on the data stored in tables. Functions are categorized into several types:
4.1. Aggregate Functions
These functions operate on a set of values and return a single value:
COUNT(): Returns the number of rows that match a specified condition.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MIN(): Returns the smallest value of the selected column.
MAX(): Returns the largest value of the selected column.
Example:
sql
Copy code
SELECT COUNT(*) FROM employees WHERE age > 30;
4.2. String Functions
These functions operate on string data types:
CONCAT(): Concatenates two or more strings.
SUBSTRING(): Extracts a substring from a string.
LENGTH(): Returns the length of a string.
UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
REPLACE(): Replaces occurrences of a substring within a string with another substring.
Example:
sql
Copy code
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
4.3. Date and Time Functions
These functions are used to manipulate date and time values:
NOW(): Returns the current date and time.
CURDATE(): Returns the current date.
CURTIME(): Returns the current time.
DATE(): Extracts the date part of a date or datetime expression.
TIME(): Extracts the time part of a date or datetime expression.
DATEDIFF(): Returns the difference in days between two dates.
Example:
sql
Copy code
SELECT NOW(); -- Returns current date and time
4.4. Numeric Functions
These functions perform operations on numeric data types:
ABS(): Returns the absolute value of a number.
ROUND(): Rounds a number to a specified number of decimal places.
CEIL(): Returns the smallest integer value greater than or equal to a number.
FLOOR(): Returns the largest integer value less than or equal to a number.
Example:
sql
Copy code
SELECT ROUND(salary, 2) FROM employees;
4.5. Control Flow Functions
These functions control the flow of queries based on conditions:
IF(): Returns a value if a condition is true, and another value if it’s false.
CASE: A more complex conditional statement that returns different values based on multiple
conditions.
COALESCE(): Returns the first non-null value in a list.
Example:
sql
Copy code
SELECT name, salary,
IF(salary > 50000, 'High', 'Low') AS salary_level
FROM employees;
5. SQL Queries in MySQL
5.1. Basic SELECT Statement
The SELECT statement is used to retrieve data from one or more tables.
sql
Copy code
SELECT column1, column2 FROM table_name;
5.2. WHERE Clause
The WHERE clause is used to filter records.
sql
Copy code
SELECT * FROM employees WHERE age > 30;
5.3. ORDER BY Clause
The ORDER BY clause is used to sort the result set by one or more columns.
sql
Copy code
SELECT * FROM employees ORDER BY age DESC;
5.4. JOINs
JOINs are used to combine rows from two or more tables based on a related column.
INNER JOIN: Returns records that have 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.
Example:
sql
Copy code
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;
5.5. GROUP BY Clause
The GROUP BY clause groups rows that have the same values in specified columns into summary rows.
sql
Copy code
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
5.6. HAVING Clause
The HAVING clause is used to filter groups based on conditions, often used with GROUP BY.
sql
Copy code
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
5.7. LIMIT Clause
The LIMIT clause is used to specify the maximum number of rows to return.
sql
Copy code
SELECT * FROM employees LIMIT 5;
6. Indexing in MySQL
Indexes are used to speed up the retrieval of data from a database table by reducing the amount of data
MySQL needs to examine.
Creating an Index:
sql
Copy code
CREATE INDEX idx_column ON table_name(column_name);
Composite Index: An index on multiple columns.
sql
Copy code
CREATE INDEX idx_columns ON table_name(column1, column2);
Primary Key: A unique index on the primary key column(s).
sql
Copy code
PRIMARY KEY (column_name)
7. Transactions in MySQL
Transactions allow you to execute a sequence of SQL statements as a single unit of work. MySQL
supports transactions with the InnoDB storage engine.
START TRANSACTION: Begins a new transaction.
COMMIT: Saves the changes made by the transaction.
ROLLBACK: Reverts the changes made by the transaction.
Example:
sql
Copy code
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
8. Stored Procedures and Functions
Stored procedures and functions are reusable SQL code blocks.
Stored Procedure: A procedure that performs a specific task.
sql
Copy code
CREATE PROCEDURE procedure_name()
BEGIN
-- SQL statements
END;
Stored Function: Similar to a procedure, but it returns a value.
sql
Copy code
CREATE FUNCTION function_name()
RETURNS datatype
BEGIN
-- SQL statements
RETURN value;
END;
9. Security Best Practices
Input Validation: Always validate and sanitize user inputs to prevent SQL injection.
Use Prepared Statements: Avoid dynamic SQL queries; use prepared statements instead.
Limit Privileges: Grant the minimum privileges necessary to database users.
Regular Backups: Regularly back up your databases to prevent data loss.
10. MySQL Performance Tuning
To improve MySQL performance, consider the following:
Query Optimization: Use EXPLAIN to analyze and optimize queries.
Indexing: Proper indexing can significantly improve query performance.
Caching: Use MySQL’s query cache to speed up query retrieval.
Normalization: Normalize your database to reduce redundancy and improve efficiency.
This guide provides a comprehensive overview of MySQL, including functions, query syntax, and best
practices. Each topic covered here is fundamental to working effectively with MySQL in various contexts,
from small-scale projects to large, complex databases.
