Online Classes available for different programming languages & for classes X , XII

Chapter 8 A Beginner’s Guide to MySQL

1. Introduction to SQL and the MySQL Environment

Structured Query Language (SQL) is the standard programming language designed to manage data within a Relational Database Management System (RDBMS). While traditional file systems require complex application programs for data access, RDBMS platforms like MySQL, Oracle, and SQL Server utilize SQL to define structures, manipulate records, and retrieve information with descriptive, English-like statements.

MySQL Quick-Start Rules

To interact effectively with the MySQL command-line environment, students must follow these foundational rules:

Case Insensitivity: SQL commands are case-insensitive (SELECT is the same as select). However, the source suggests it is a "good practice" to write database and table names in the same letter case used during their creation.
Statement Termination: The semicolon (;) is the required trigger for execution. MySQL will not process a command until it encounters this character.
Multiline Commands: If you press "Enter" without a semicolon, the system assumes the statement is continuing. The prompt will change from mysql> to ->. The system will continue to wait via this prompt until the terminating semicolon is provided.

2. The Building Blocks: Data Types and Constraints

Before constructing a database, you must define the type of data each attribute (column) will hold and the rules (constraints) that govern that data.

Commonly Used Data Types in MySQL

Data Type

Description

Key Details

CHAR(n)

Fixed-length character data.

Length n from 0 to 255. Reserved space is fixed; shorter strings are right-padded with spaces.

VARCHAR(n)

Variable-length character data.

Length n from 0 to 65535. Only occupies the bytes needed for the actual string entered.

INT

Standard integer value.

Occupies 4 bytes. Range: -2,147,483,648 to 2,147,483,647.

FLOAT

Floating-point numbers.

Occupies 4 bytes. Holds numbers with decimal points.

DATE

Calendar dates.

Format: 'YYYY-MM-DD'. Range: '1000-01-01' to '9999-12-31'.

Constraints

Constraints are restrictions used to ensure the accuracy and reliability of your data:

NOT NULL: Ensures that a column cannot have missing or unknown values.
UNIQUE: Ensures that all values in a specific column are distinct from one another.
DEFAULT: Provides a predefined value for a column if no value is provided during data entry.
PRIMARY KEY: An attribute that uniquely identifies each row or record in a table.
FOREIGN KEY: An attribute that refers to a primary key in another table to link the two relations together.

3. Data Definition Language (DDL): Constructing the Database

DDL is the subset of SQL used to define the database schema. The typical workflow involves establishing the database container, creating the initial tables, and then refining those structures as requirements evolve.

Database Operations

A database must be created and selected before any tables can be built.

Create a Database: CREATE DATABASE databasename;
Select a Database: USE databasename;
View Existing Tables: SHOW TABLES;

Table Creation and Inspection

The CREATE TABLE statement defines the columns, types, and constraints. Important: By default, attributes allow NULL values except for the primary key, which must always contain a value.

Syntax:
Inspection: To view the structure (schema) of a table, use DESCRIBE tablename; or the shorthand DESC tablename;.

Modifying Structures (ALTER TABLE)

As database requirements change, the ALTER TABLE statement allows you to modify the schema without deleting the table.

Adding a Primary Key: ALTER TABLE tablename ADD PRIMARY KEY (column_name);
Adding a Foreign Key: Use the REFERENCES clause to link to the parent table:
Adding a UNIQUE Constraint: ALTER TABLE tablename ADD UNIQUE (column_name);
Adding an Attribute: ALTER TABLE tablename ADD attribute_name DATATYPE;
Removing an Attribute: ALTER TABLE tablename DROP attribute_name;
Modifying Attributes: To change a data type or add NOT NULL/DEFAULT constraints: ALTER TABLE tablename MODIFY attribute DATATYPE [CONSTRAINT];

Deleting Structures (DROP Statement)

The DROP statement is used to permanently remove tables or databases.

Syntax: DROP TABLE tablename; or DROP DATABASE databasename;
Cautions: This operation cannot be undone. Dropping a database will automatically remove all tables and data contained within it.

4. Data Manipulation Language (DML): Managing Records

Once the structure is defined, DML is used to populate and manage the records.

Inserting Records

The INSERT INTO command adds new rows to a table.

Inserting into All Attributes: Values must match the table's column order exactly:
Inserting into Specific Columns: Specify names for the columns you wish to populate. Unspecified columns will be assigned NULL or their DEFAULT values:

Important Cautions

Foreign Key Order: When populating tables, you must ensure the referenced "parent" tables are populated before the "child" tables containing the foreign keys.
Advanced DML: The DML category also includes UPDATE (modification) and DELETE (removal) operations. The specific syntax for these commands will be covered in subsequent lessons.

5. Data Query Language (DQL): Retrieving Information

DQL is the most frequently used part of SQL, allowing users to retrieve specific data using the SELECT statement and the FROM clause.

Refining Output

Renaming Columns (Aliases): Use the AS keyword to create a temporary heading for the output.
- Rule for Aliases: If the aliased name contains a space, it must be enclosed in quotes (e.g., SELECT Ename AS 'Employee Name' FROM EMPLOYEE;).
Unique Records: Use the DISTINCT clause to suppress duplicate values in the result set.

Filtering Data (The WHERE Clause)

The WHERE clause limits results to rows that meet specific criteria.

Relational Operators: Use =, <, >, <=, >=, and != for basic comparisons.
Logical Operators: Combine conditions using AND, OR, or NOT.
Range Search: Use BETWEEN to find values within an inclusive range: WHERE Salary BETWEEN 20000 AND 50000;
Set Membership: Use IN to check for values matching any item in a list: WHERE DeptId IN ('D01', 'D02');
Null Handling: Because NULL represents an "unknown" value rather than a zero, it cannot be checked with the = operator. Use IS NULL or IS NOT NULL to identify missing data.

Pattern Matching (LIKE Operator)

The LIKE operator searches for string patterns using two wildcards:

% (Percent): Represents zero, one, or multiple characters.
_ (Underscore): Represents exactly one single character.

Sorting Results (ORDER BY)

The ORDER BY clause arranges the result set based on a specific column. By default, it sorts in ascending order. To reverse this, use the DESC keyword:

SELECT * FROM EMPLOYEE ORDER BY Salary DESC;

6. Practice Reference Table

Use this table as a quick guide to the primary SQL commands and their instructional categories.