TCL Statements, and SELECT Queries with Conditional Operators in MySQL | Session #5

Описание: Certainly! Here's an in-depth description of each of the three key concepts: AUTO_INCREMENT, TCL Statements, and SELECT with Conditional Operators in MySQL, structured clearly under their respective headings.

1. AUTO_INCREMENT in MySQL

Definition:
AUTO_INCREMENT is a column attribute in MySQL used to automatically generate a unique number when a new row is inserted into a table. It is most commonly applied to primary key columns.

Key Points:

It is typically used with INT types (e.g., INT, BIGINT).

Only one AUTO_INCREMENT column is allowed per table.

It must be indexed (usually PRIMARY KEY or UNIQUE).

The value starts from 1 by default and increments by 1 for each new row, unless otherwise specified.

Syntax Example:

CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (id)
);


Inserting Data:

INSERT INTO users (name) VALUES ('Alice');
-- id is automatically assigned as 1


Resetting AUTO_INCREMENT:

ALTER TABLE users AUTO_INCREMENT = 100;
-- Next inserted id will be 100

2. TCL Statements in MySQL (Transaction Control Language)

Definition:
TCL (Transaction Control Language) statements are used to manage changes made by DML (Data Manipulation Language) statements. They allow you to commit, rollback, or save changes in a controlled way, especially useful for maintaining data integrity.

Common TCL Commands:

START TRANSACTION or BEGIN

COMMIT

ROLLBACK

SAVEPOINT

RELEASE SAVEPOINT

SET AUTOCOMMIT

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit the changes if all operations succeed
COMMIT;


Rollback Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Something went wrong...
ROLLBACK;
-- The update is undone


Use Case:
Ideal in banking systems or e-commerce where multiple interdependent updates need to either all succeed or all fail.

3. SELECT Statement with Conditional Operators in MySQL

Definition:
The SELECT statement is used to query data from a table. Conditional operators allow you to filter rows based on specified conditions.

Common Conditional Operators:

=, != (not equal)


BETWEEN ... AND ...

IN (...)

LIKE, NOT LIKE

IS NULL, IS NOT NULL

AND, OR, NOT

Example Queries:

Simple condition:

SELECT * FROM employees WHERE department = 'Sales';


Multiple conditions:

SELECT * FROM employees WHERE salary 50000 AND department = 'HR';


Using IN:

SELECT * FROM employees WHERE department IN ('Sales', 'HR', 'IT');


Using BETWEEN:

SELECT * FROM products WHERE price BETWEEN 100 AND 500;


Using LIKE:

SELECT * FROM customers WHERE name LIKE 'A%';
-- names starting with A

Summary
Concept Purpose Key Command(s)
AUTO_INCREMENT Automatically generates unique values for a column AUTO_INCREMENT
TCL Statements Manages transactions to ensure data integrity BEGIN, COMMIT, ROLLBACK
SELECT + Conditions Queries and filters data based on specified criteria SELECT ... WHERE ...