MYSQL
Introduction to MySQL
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS).
It uses Structured Query Language (SQL) to store, manage, and retrieve data.
Developed by MySQL AB, later acquired by Oracle Corporation.
MySQL is widely used in web applications, business systems, and modern software solutions.
2. Why Use MySQL?
Free and open source for most uses.
Fast, reliable, and scalable for small to very large datasets.
Supports multi-user access, meaning many users can work on the same data at the same time.
Works on various platforms: Windows, Linux, macOS.
3. Key Features
Relational Database: Data is organized in tables (rows and columns).
High Performance: Optimized for read and write operations.
Security: Provides user authentication, privileges, and data protection.
Portability: Databases can be moved across operating systems.
Connectivity: Works with many programming languages like PHP, Java, Python.
Client-Server Architecture: MySQL server stores data; clients send queries.
4. MySQL Architecture Overview
MySQL Server: The central program that handles all database operations.
Clients: Tools or applications that send SQL queries.
Storage Engine: Determines how data is stored. Popular engines:
InnoDB (default): supports transactions & foreign keys.
MyISAM: fast, mainly used for read-heavy operations.
5. Common Uses of MySQL
Website databases (e-commerce, CMS like WordPress).
Online transaction processing (banking, booking systems).
Data warehousing.
Enterprise applications.
6. Basic MySQL Terminology
Database: A collection of related data.
Table: Structure containing rows and columns.
Row (Record): A single entry of data.
Column (Field): A specific attribute of the data.
Primary Key: Uniquely identifies each row.
Foreign Key: Establishes relationship between tables.
Query: A request sent to the database.
7. Advantages of MySQL
Easy to learn and implement.
Excellent community support and documentation.
Supports large databases (up to terabytes).
Cost-effective for small and large organizations.
8. Limitations
Does not support full SQL standards in all areas.
Complex enterprise-level features require advanced configurations.
MySQL Theory Notes: CREATE DATABASE, INSERT, SELECT, UPDATE, DELETE
1. CREATE DATABASE
The CREATE DATABASE statement is used to create a new database in MySQL.
Before storing tables and data, a database must be created. Each database works as a container that holds multiple tables, views, and other objects.
This command is typically executed once during the setup of a project or application.
Syntax
Example
2. INSERT (Insert Value in a Table)
The INSERT statement is used to add new records into a table.
It is one of the most frequently used SQL commands because data entry is essential for building any database-driven application.
MySQL allows inserting data either by specifying column names or by inserting into all columns following the table’s column order.
Syntax (Inserting into selected columns)
Syntax (Inserting into all columns)
Example
3. SELECT
The SELECT statement is used to retrieve data from a table.
It is the most important command in SQL because it allows us to read and display stored data.
SELECT can retrieve specific columns or all columns. It can also be combined with conditions, sorting, grouping, or functions.
Syntax (Select specific columns)
Syntax (Select all columns)
Example
Example
4. UPDATE
The UPDATE statement is used to change or modify existing data in a table.
It allows updating one or multiple columns at the same time.
To avoid accidental data changes, the WHERE clause is usually applied; otherwise, every row in the table will be updated.
Syntax
Example
5. DELETE
The DELETE statement is used to remove records from a table.
It removes only the data, not the table structure.
Just like UPDATE, DELETE should always be used with a WHERE clause unless you want to remove all rows from the table.
Syntax
Example
Functions in SQL.
Functions in SQL – Theory Notes
SQL functions are built-in operations that perform calculations, transform data, or return specific values based on input.
They help simplify queries, reduce manual processing, and make data manipulation easier and more efficient.
SQL functions generally do not modify the database; instead, they operate on data and return a result.
SQL functions are broadly classified into:
Single-Row (Scalar) Functions
Aggregate (Group) Functions
Both types are essential in data retrieval and analysis.
1. Single-Row (Scalar) Functions
These functions operate on one row at a time and return one result per row.
They are often used inside SELECT, WHERE, and ORDER BY clauses.
Scalar functions are further divided into categories:
a. String Functions
b. Numeric Functions
c. Date and Time Functions
a. Numeric Function .
1. POWER() Function
Definition
POWER() is a numeric function used to return the value of a number raised to a specified power.
It performs exponentiation.
Syntax
Example
Example
2. ROUND() Function
Definition
ROUND() is used to round a numeric value to a specified number of decimal places.
If the decimal part is 5 or more, it rounds upward; otherwise, downward.
Syntax
number: the value to round
decimal_places: how many digits to keep after the decimal
If omitted, it rounds to the nearest whole number
Example
Example
3. MOD() Function
Definition
MOD() returns the remainder after dividing one number by another.
It is useful for finding even/odd numbers or cyclic patterns.
Syntax
dividend: the number to divide
divisor: the number by which dividend is divided
Example
Example
String Functions in SQL
(UCASE, LCASE, MID, LENGTH, LEFT, RIGHT, INSTR, LTRIM, RTRIM, TRIM)
String functions in SQL are used to process and manipulate textual data stored in database fields. These functions help format text, extract substrings, search within strings, and clean unwanted spaces. They are essential in reporting, data cleaning, user interface formatting, and preparing data for comparisons or conditions.
1. UCASE()
Purpose (Theory):
UCASE() converts every alphabetic character in a string to uppercase. It is mainly used when standardizing text for display or comparison. Since SQL comparisons can be case-sensitive depending on collation, converting to uppercase helps maintain uniformity in search queries or reports.
Syntax
dividend: the number to divide
divisor: the number by which dividend is divided
Example
Example
3. MOD() Function
Definition
MOD() returns the remainder after dividing one number by another.
It is useful for finding even/odd numbers or cyclic patterns.
Syntax
dividend: the number to divide
divisor: the number by which dividend is divided
Example
Example
3. MOD() Function
Definition
MOD() returns the remainder after dividing one number by another.
It is useful for finding even/odd numbers or cyclic patterns.
Syntax
dividend: the number to divide
divisor: the number by which dividend is divided
Example
Example
3. MOD() Function
Definition
MOD() returns the remainder after dividing one number by another.
It is useful for finding even/odd numbers or cyclic patterns.
Syntax
dividend: the number to divide
divisor: the number by which dividend is divided
Example
Example