Views, Text & DateTime Functions, and Joining Tables in MySQL | Session #7
In MySQL, working with data efficiently requires knowledge of Views, Text & DateTime functions, and Table Joins. These features help in data management, reporting, and complex queries.
1. Views in MySQL
A View is a virtual table based on the result of a SELECT query. It does not store data physically but can simplify complex queries and improve readability.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary 50000;
👉 You can now query the view:
SELECT * FROM EmployeeView;
2. Text Functions in MySQL
Text functions allow manipulation of string data. Commonly used functions:
CONCAT() → Combine strings
LEFT() / RIGHT() → Extract part of a string
LENGTH() → Get string length
UPPER() / LOWER() → Change case
SUBSTRING() → Extract substring
Example:
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
SELECT UPPER(FirstName) FROM Employees;
3. DateTime Functions in MySQL
DateTime functions handle date and time operations:
NOW() → Current date and time
CURDATE() → Current date
DATE_ADD() / DATE_SUB() → Add or subtract days
DATEDIFF() → Difference between two dates
YEAR(), MONTH(), DAY() → Extract parts of a date
Example:
SELECT CURDATE() AS Today;
SELECT DATEDIFF('2025-12-31', CURDATE()) AS DaysLeft;
4. Joining Tables in MySQL
Joins combine data from multiple tables based on related columns:
INNER JOIN → Returns matching rows from both tables
LEFT JOIN → Returns all rows from left table and matching from right
RIGHT JOIN → Returns all rows from right table and matching from left
FULL OUTER JOIN → Returns all rows when there is a match (MySQL supports via UNION)
Example:
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
✅ Benefits:
Views simplify query handling and reporting.
Text & DateTime functions allow precise data manipulation.
Joins enable combining data from multiple tables for comprehensive analysis.
Together, these features make MySQL a powerful tool for database management and reporting.