Transaction Control Language (TCL) Commands in MySQL

TCL (Transaction Control Language) commands in MySQL are used to manage transactions,
ensuring data integrity and consistency.

TCL Commands in MySQL

Command Description

COMMIT Saves all changes made during the transaction.

ROLLBACK Undoes all changes made during the transaction.

SAVEPOINT Creates a checkpoint within a transaction.

RELEASE SAVEPOINT Deletes a specific savepoint.

SET AUTOCOMMIT Enables or disables automatic commits.

Example: Using COMMIT and ROLLBACK

-- Step 1: Disable AutoCommit

SET AUTOCOMMIT = 0;

-- Step 2: Start Transaction

START TRANSACTION;

-- Step 3: Insert a Record

INSERT INTO employees (name, salary) VALUES ('John Doe', 50000);

-- Step 4: Check Data Before Committing

SELECT * FROM employees;

-- Step 5: Commit the Transaction

COMMIT;
Now the changes are permanently saved.
Example: Using ROLLBACK:

START TRANSACTION;

UPDATE employees SET salary = salary + 5000 WHERE name = 'John Doe';

-- Oops! Let's revert the changes

ROLLBACK;

-- The salary remains unchanged

SELECT * FROM employees;

ROLLBACK reverts the changes before COMMIT.

Example: Using SAVEPOINT

START TRANSACTION;

UPDATE employees SET salary = salary + 1000 WHERE name = 'John Doe';

SAVEPOINT save1;

UPDATE employees SET salary = salary + 2000 WHERE name = 'John Doe';

ROLLBACK TO save1;

-- Now the second update is undone, but the first update remains.
SELECT * FROM employees;

SAVEPOINT allows partial rollback inside a transaction.

Example: Using RELEASE SAVEPOINT

START TRANSACTION;

UPDATE employees SET salary = salary + 3000 WHERE name = 'John Doe';

SAVEPOINT sp1;

UPDATE employees SET salary = salary + 5000 WHERE name = 'John Doe';

RELEASE SAVEPOINT sp1; -- Deletes the savepoint

ROLLBACK TO sp1; -- ERROR! (Savepoint does not exist)

Once a SAVEPOINT is released, you cannot rollback to it.

AUTO_INCREMENT

Syntax to Change AUTO_INCREMENT:

ALTER TABLE table_name AUTO_INCREMENT = new_value;

Check the Current AUTO_INCREMENT Value:

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'employees';
Reset AUTO_INCREMENT to 1
If you want to reset AUTO_INCREMENT to 1, you must first delete all records or truncate
the table:

TRUNCATE TABLE employees;

ALTER TABLE employees AUTO_INCREMENT = 1;

Add AUTO_INCREMENT to an Existing Column

If you already have a PRIMARY KEY column and want to enable AUTO_INCREMENT, use:

ALTER TABLE employees MODIFY id INT AUTO_INCREMENT;

This modifies the id column to be AUTO_INCREMENT.

Add AUTO_INCREMENT to a New Column:

If your table does not have a numeric primary key, follow these steps:

ALTER TABLE employees ADD id INT AUTO_INCREMENT PRIMARY KEY;

Using || Operator (Only with PIPES_AS_CONCAT Mode):

MySQL does not support || by default for string concatenation.

However, you can enable it using:

SET SESSION sql_mode = 'PIPES_AS_CONCAT';
Or
SET sql_mode = 'PIPES_AS_CONCAT';
 Example:
SELECT 'Hello' || ' ' || 'World' AS Greeting;

ENUM Data Type in MySQL

In MySQL, the ENUM data type is used to define a column with a fixed set of values. It is
more efficient than using a VARCHAR column when you have a limited number of possible
values.

Syntax for ENUM:

CREATE TABLE table_name (

column_name ENUM('value1', 'value2', 'value3', ..., 'valueN')
);
This restricts the column to accept only the specified values.
If an invalid value is inserted, MySQL inserts an empty string ('').

Example: Using ENUM in a Table:

CREATE TABLE employees (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
gender ENUM('Male', 'Female', 'Other')
);

Here, the gender column only allows 'Male', 'Female', or 'Other'.

Insert Valid Values

INSERT INTO employees (name, gender) VALUES ('Alice', 'Female');

INSERT INTO employees (name, gender) VALUES ('Bob', 'Male');

Insert Invalid Value (Fails):

INSERT INTO employees (name, gender) VALUES ('Charlie', 'Unknown'); -- ERROR!

Default Value in ENUM:

You can set a default value for an ENUM column:

CREATE TABLE employees (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
gender ENUM('Male', 'Female', 'Other') DEFAULT 'Other'
);

If no value is provided, it defaults to 'Other'.

Modify ENUM Column
Add a New Value
ALTER TABLE employees MODIFY gender ENUM('Male', 'Female', 'Other', 'Prefer Not to Say');
 Common Table Expressions (CTE) in MySQL
A CTE (Common Table Expression) in MySQL is a temporary result set that you can reference
within a SELECT, INSERT, UPDATE, or DELETE statement.

Basic Syntax of CTE:

WITH cte_name AS (
SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

The WITH clause defines the CTE.

🔹 The CTE (cte_name) acts like a temporary table.

Example: Using CTE in MySQL

🔹 Sample employees Table
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10,2)
);

INSERT INTO employees (name, department, salary) VALUES

('Alice', 'IT', 60000),
('Bob', 'HR', 55000),
('Charlie', 'IT', 70000),
('David', 'Finance', 80000),
('Eve', 'HR', 52000);
Using CTE to Select High Earners:

WITH HighEarners AS (
SELECT name, department, salary FROM employees WHERE salary > 60000
)
SELECT * FROM HighEarners;

Using CTE with Aggregation (COUNT, AVG, etc.)

WITH DeptSalary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM DeptSalary;

Recursive CTE in MySQL (For Hierarchical Data)

🔹 Use Case: Find an employee hierarchy in a company.
🔹 Sample employees Table with Manager IDs

CREATE TABLE employees (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
manager_id INT NULL
);
INSERT INTO employees (name, manager_id) VALUES
('Alice', NULL), -- CEO (no manager)
('Bob', 1), -- Bob reports to Alice
('Charlie', 1), -- Charlie reports to Alice
('David', 2), -- David reports to Bob
('Eve', 3); -- Eve reports to Charlie

Recursive CTE to Find Employee Hierarchy:

WITH RECURSIVE EmployeeHierarchy AS (

-- Base case (CEO)
SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive case (find employees under the previous level)
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = [Link]
)
SELECT * FROM EmployeeHierarchy;

CTE with INSERT, UPDATE, DELETE

🔹 Insert Data Using CTE
WITH HighEarners AS (
SELECT * FROM employees WHERE salary > 60000
)
INSERT INTO high_salary_employees (id, name, department, salary)
SELECT * FROM HighEarners;

Using CTE with UPDATE (via JOIN):

WITH HighEarners AS (
SELECT id, salary * 1.1 AS new_salary -- Increasing salary by 10%
FROM employees
WHERE salary > 60000
)
UPDATE employees e
JOIN HighEarners h ON [Link] = [Link]
SET [Link] = h.new_salary;

DELETE Using CTE in MySQL

In MySQL, CTEs do not support DELETE directly. However, you can use a CTE with a DELETE
query via JOIN.

Using CTE with DELETE (via JOIN):

WITH HighEarners AS (
SELECT id FROM employees WHERE salary > 60000
)
DELETE e FROM employees e
JOIN HighEarners h ON [Link] = [Link];

Temporary Tables in Mysql

A Temporary Table in MySQL is a special type of table that only exists during a session and is
deleted automatically when the session ends or the connection is closed.
Creating a Temporary Table
CREATE TEMPORARY TABLE temp_employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);

This table will only be available for the current session.

Inserting Data into Temporary Table;

INSERT INTO temp_employees (id, name, salary)

VALUES (1, 'John Doe', 50000), (2, 'Alice Smith', 60000);

Retrieving Data from Temporary Table;

SELECT * FROM temp_employees;

Dropping a Temporary Table Manually

DROP TEMPORARY TABLE temp_employees;

Creating a Temporary Table from an Existing Table:

CREATE TEMPORARY TABLE temp_high_salary AS

SELECT id, name, salary FROM employees WHERE salary > 60000;
 Users

How to Create a New User in MySQL:

In MySQL, you can create a new user using the CREATE USER command and then grant
privileges using the GRANT command.

Creating a New User

🔹 Syntax
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

 username → The name of the new user.

 host → The host from which the user can connect (localhost, % for any host, or a
specific IP).
 password → The password for the user.

[Link] a User for Localhost:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'John@1234';
This creates a user john who can log in only from localhost.

2: Create a User for Any Host:

CREATE USER 'john'@'%' IDENTIFIED BY 'John@1234';

This allows john to connect from any host (any IP or computer).

3. Create a User for a Specific IP Address

CREATE USER 'john'@'[Link]' IDENTIFIED BY 'John@1234';

This allows john to connect only from [Link].

Granting Privileges to the User

After creating a user, you need to assign permissions using GRANT.
🔹 Example 1: Grant All Privileges on a Database
GRANT ALL PRIVILEGES ON mydatabase.* TO 'john'@'localhost';

Now, john can perform all actions on the mydatabase database.

Grant Limited Privileges:

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'john'@'localhost';

john can only read (SELECT), insert (INSERT), and update (UPDATE) data.

Grant Privileges on All Databases:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';

Apply Changes Using FLUSH PRIVILEGES

FLUSH PRIVILEGES;
Checking User Privileges
SHOW GRANTS FOR 'john'@'localhost';
This displays all permissions assigned to john.

Removing a User in MySQL:

DROP USER 'john'@'localhost';
FLUSH PRIVILEGES;

DCL
DCL (Data Control Language) commands in MySQL are used to control user access and
permissions in a database. The two primary DCL commands are:
 GRANT → Gives privileges to a user.
 REVOKE → Removes privileges from a user.

GRANT Command in MySQL

 The GRANT command is used to give specific privileges (permissions) to a user or

role in MySQL.

GRANT privileges ON database_name.table_name TO 'username'@'host' IDENTIFIED

BY 'password';

Privileges can be:

✔ ALL PRIVILEGES → Grants all permissions
✔ SELECT, INSERT, UPDATE, DELETE → Controls DML operations
✔ CREATE, DROP, ALTER → Controls schema changes
✔ GRANT OPTION → Allows user to grant privileges to others

Example 1: Creating a New User and Granting Privileges

CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

GRANT SELECT, INSERT ON company_db.* TO 'john'@'localhost';

Creates a user john with the password 'password123'

Grants SELECT and INSERT privileges on the company_db database
Example 2: Granting All Privileges:
GRANT ALL PRIVILEGES ON company_db.* TO 'john'@'localhost';

Now, john has full access to the company_db database.

Example 3: Granting Privileges to All Users:

GRANT SELECT ON *.* TO 'guest'@'%';

This allows all users (% means any host) to perform SELECT queries on all databases.

REVOKE Command in MySQL

The REVOKE command is used to remove privileges from a user.
🔹 Syntax
REVOKE privileges ON database_name.table_name FROM 'username'@'host';
Example 1: Removing Specific Privileges
REVOKE INSERT ON company_db.* FROM 'john'@'localhost';

Removing All Privileges:

REVOKE ALL PRIVILEGES ON company_db.* FROM 'john'@'localhost';

Checking User Privileges

🔹 To check a user’s privileges, use:
SHOW GRANTS FOR 'john'@'localhost';

Example Output:

GRANT SELECT, INSERT ON `company_db`.* TO 'john'@'localhost';

Removing a User in MySQL
🔹 To delete a user from MySQL

DROP USER 'john'@'localhost';

This removes the user john from MySQL.

FLUSH Privileges
1. After granting or revoking privileges, run:
FLUSH PRIVILEGES;
Temporary Table vs. CTE (Comparison Table)

Feature Temporary Table CTE (Common Table Expression)

Scope Exists for the session Exists only during query execution

Requires CREATE
Creation Uses WITH clause (No creation needed)
TEMPORARY TABLE

Exists only in memory during query

Storage Stored in memory/disk
execution

Available for multiple Available only for one query (unless used
Persistence
queries in a session multiple times in the same query)

Can INSERT, UPDATE, and

Modification Read-only (Cannot be modified)
DELETE

Indexes Can have indexes Cannot have indexes

Good for large data Optimized for single-query

Performance
processing transformations
 Indexes in MySQL
Indexes in MySQL improve query performance by allowing the database to find rows faster
without scanning the entire table.

Types of Indexes in MySQL

Index Type Description

Primary Index Automatically created on

PRIMARY KEY.

Unique Index Ensures unique values in a

column.

Composite Index on multiple columns.

Index

Full-Text Index Used for text searching.

Creating Indexes
Create an Index on a Single Column:

CREATE INDEX idx_emp_name ON employees(name);

This speeds up queries like:

SELECT * FROM employees WHERE name = 'Alice';

Create a Unique Index:

It prevents duplicate values in the column.

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Create a Composite Index (Multiple Columns):

CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

Optimizes queries like:
SELECT * FROM employees WHERE department_id = 2 AND salary > 50000;
Full-Text Index (For Searching Text Data)
CREATE FULLTEXT INDEX idx_fulltext_name ON employees(name);

Enables full-text search:

SELECT * FROM employees WHERE MATCH(name) AGAINST ('Alice');

Viewing Indexes:

SHOW INDEXES FROM employees;

Dropping Indexes:
DROP INDEX idx_emp_name ON employees;

How to Increase the Width of the MySQL Command Line Client Terminal(In Windows);

SELECT * FROM Customers\G

How to Check Constraints for a Table:

Show create table tablename;

 Stored Procedures in MySQL
A Stored Procedure in MySQL is a set of SQL statements that are saved in the database and
can be executed as a single unit. It helps in reusability, reduces network traffic, and improves
performance.

DELIMITER $$

CREATE PROCEDURE SumTwoNumbers(

IN num1 INT,
IN num2 INT,
OUT result INT
)
BEGIN
SET result = num1 + num2;
END $$

DELIMITER ;
Calling:
CALL SumTwoNumbers(10, 20, @sum);
SELECT @sum AS TotalSum;
Creating a Simple Stored Procedure:

DELIMITER $$
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT * FROM Employees;
END $$

DELIMITER ;
Explanation
• DELIMITER $$ → Changes the delimiter to $$ to avoid conflicts with ; inside the
procedure.
• CREATE PROCEDURE GetAllEmployees() → Creates a stored procedure named
GetAllEmployees.
• BEGIN ... END → Defines the procedure's logic.
• SELECT * FROM Employees; → The query that gets executed when the procedure is
called.
• DELIMITER ; → Resets the delimiter back to ;.

Executing the Stored Procedure

CALL GetAllEmployees();
Stored Procedure with Input Parameters:

DELIMITER $$

CREATE PROCEDURE GetEmployeeByID(IN empID INT)

BEGIN
SELECT * FROM Employees WHERE EmployeeID = empID;
END $$

DELIMITER ;

CALL GetEmployeeByID(2);
Stored Procedure with Output Parameter:

DELIMITER $$

CREATE PROCEDURE GetTotalSalary(OUT totalSalary DECIMAL(10,2))

BEGIN
SELECT SUM(Salary) INTO totalSalary FROM Employees;
END $$

DELIMITER ;

Executing:
CALL GetTotalSalary(@total);
SELECT @total; -- Displays the total salary
Stored Procedure with Multiple Parameters

DELIMITER $$

CREATE PROCEDURE AddNewEmployee(IN empName VARCHAR(50), IN deptID INT, IN

empSalary DECIMAL(10,2), IN hireDate DATE)
BEGIN
INSERT INTO Employees (Name, DepartmentID, Salary, HireDate)
VALUES (empName, deptID, empSalary, hireDate);
END $$

DELIMITER ;

Executing:

CALL AddNewEmployee('David', 1, 60000, '2024-02-01');

Stored Procedure with IF-ELSE Condition:

DELIMITER $$

CREATE PROCEDURE CheckSalary(IN empID INT, OUT salaryStatus VARCHAR(20))

BEGIN
DECLARE empSalary DECIMAL(10,2);

SELECT Salary INTO empSalary FROM Employees WHERE EmployeeID = empID;

IF empSalary > 50000 THEN

SET salaryStatus = 'High Salary';
ELSE
SET salaryStatus = 'Low Salary';
END IF;
END $$

DELIMITER ;

Executing:
CALL CheckSalary(3, @status);
SELECT @status; -- Displays 'High Salary' or 'Low Salary'
Stored Procedure with a LOOP:
DELIMITER $$

CREATE PROCEDURE PrintNumbers(IN maxNum INT)

BEGIN
DECLARE num INT DEFAULT 1;

loop_label: LOOP
IF num > maxNum THEN
LEAVE loop_label;
END IF;

SELECT num;
SET num = num + 1;
END LOOP;
END $$
DELIMITER ;
Executing:
CALL PrintNumbers(5);

DELIMITER in Mysql
The DELIMITER command in MySQL is used to change the default statement delimiter (;) to
something else. This is useful when writing stored procedures, triggers, functions, and
events, where multiple SQL statements are used within a block.
Why Use DELIMITER?
By default, MySQL uses ; as a statement delimiter. However, when defining a stored
procedure, we use BEGIN ... END with multiple SQL statements. If we use ; inside the
procedure, MySQL might treat it as the end of the procedure, leading to errors.
To prevent this, we change the delimiter to something unique (e.g., $$ or //) using the
DELIMITER command.
Basic Syntax
DELIMITER new_delimiter

new_delimiter → Any symbol(s) that do not conflict with SQL syntax, commonly $$ or //.

PREPARE Statement in MySQL

The PREPARE statement in MySQL is used to create dynamic SQL queries that can be
executed multiple times with different values. This is useful for parameterized queries,
avoiding SQL injection, and improving performance.

Syntax of PREPARE Statement

PREPARE stmt_name FROM 'SQL_QUERY';

EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;

1. stmt_name → Name of the prepared statement.

2. SQL_QUERY → The SQL command to execute.
3. EXECUTE → Runs the prepared statement.
4. DEALLOCATE PREPARE → Frees memory after execution.

Example 1: Simple PREPARE Statement

PREPARE my_query FROM 'SELECT NOW() AS Current_Time';
EXECUTE my_query;
DEALLOCATE PREPARE my_query;

Example 2: Using PREPARE with a Variable

We can use session variables (@var) inside a PREPARE statement.
SET @table_name = 'employees';
SET @sql_query = CONCAT('SELECT * FROM ', @table_name);

PREPARE my_query FROM @sql_query;

EXECUTE my_query;
DEALLOCATE PREPARE my_query;

This dynamically selects data from the employees table.

Example 3: Using PREPARE with WHERE Clause:

SET @emp_id = 2;
SET @sql = 'SELECT * FROM employees WHERE id = ?';

PREPARE my_query FROM @sql;

EXECUTE my_query USING @emp_id;
DEALLOCATE PREPARE my_query;

Example 4: Using PREPARE to Insert Data

SET @emp_name = 'John Doe';

SET @emp_age = 30;

SET @sql = 'INSERT INTO employees (name, age) VALUES (?, ?)';

PREPARE insert_query FROM @sql;

EXECUTE insert_query USING @emp_name, @emp_age;
DEALLOCATE PREPARE insert_query;
Using PREPARE to Update Data:

SET @new_age = 35;

SET @emp_id = 2;

SET @sql = 'UPDATE employees SET age = ? WHERE id = ?';

PREPARE update_query FROM @sql;

EXECUTE update_query USING @new_age, @emp_id;
DEALLOCATE PREPARE update_query;

Using PREPARE for a Dynamic Table Creation

SET @table_name = 'new_table';
SET @sql = CONCAT('CREATE TABLE ', @table_name, ' (id INT PRIMARY KEY, name
VARCHAR(50))');

PREPARE create_table FROM @sql;

EXECUTE create_table;
DEALLOCATE PREPARE create_table;

Triggers

Triggers are special stored programs that automatically execute in response to specific
events on a table, such as INSERT, UPDATE, or DELETE operations. They are useful for tasks
like enforcing business rules, maintaining audit trails, and ensuring data integrity.
Types of Triggers:
1. BEFORE Triggers: Execute before the triggering event.
o BEFORE INSERT
o BEFORE UPDATE
o BEFORE DELETE
 2. AFTER Triggers: Execute after the triggering event.
o AFTER INSERT
o AFTER UPDATE
o AFTER DELETE

Creating Triggers:
To create a trigger in MySQL, use the CREATE TRIGGER statement with the following syntax:

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
-- Trigger logic here
END;

Examples:
1. BEFORE INSERT Trigger:
Suppose we have a Products table, and we want to ensure that the Price of a product is
always positive before inserting a new record.
DELIMITER $$
CREATE TRIGGER before_product_insert
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
IF [Link] <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be positive';
END IF;
END $$

DELIMITER ;
Explanation:
 Trigger Timing: BEFORE INSERT specifies that the trigger activates before an insert
operation on the Products table.
 Trigger Body: The trigger checks if the Price of the new row ([Link]) is less than
or equal to zero. If so, it raises an error using the SIGNAL statement to prevent the
insertion.

AFTER UPDATE Trigger:

Assume we have an Orders table and an OrderHistory table. We want to log any updates
made to the Orders table into the OrderHistory table.

DELIMITER $$

CREATE TRIGGER after_order_update

AFTER UPDATE ON Orders
FOR EACH ROW
BEGIN
INSERT INTO OrderHistory (OrderID, OldStatus, NewStatus, ChangeDate)
VALUES ([Link], [Link], [Link], NOW());
END $$

DELIMITER ;
Important Considerations:
 DELIMITER Command: In MySQL, the DELIMITER command is used to change the
statement delimiter temporarily, allowing the use of semicolons within the trigger
body without ending the CREATE TRIGGER statement prematurely.
 OLD and NEW Keywords: Within trigger bodies, the OLD keyword refers to the
existing values before the triggering event (available in UPDATE and DELETE triggers),
and the NEW keyword refers to the new values after the triggering event (available in
INSERT and UPDATE triggers).
 Error Handling: In the BEFORE INSERT trigger example, the SIGNAL statement is used
to raise an error and prevent the insertion if the specified condition is met.
 MySQL Trigger Example
Let us start creating a trigger in MySQL that makes modifications in the employee table.
First, we will create a new table named employee by executing the below statement:
CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10)
);

mysql> DELIMITER //
mysql> Create Trigger before_insert_empworkinghours
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;
END IF;
END //