MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Transactions
The MySQL Transactions
The MySQL transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.
Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction. This will ensure no data losses or failed executions of SQL queries.
Properties of Transactions
There are four standard properties of transactions, often referred to by the acronym ACID 
Atomicity − This ensures that all operations within a transaction are treated as a single unit. Either all the operations within the transaction are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left in its original state.
Consistency − This ensures that the database properly changes states upon a successfully committed transaction.
Isolation − This enables transactions to operate independently and transparent to each other.
Durability − This ensures that once a transaction is committed, its effects on the database are permanent and will survive system failures (e.g., power outages, hardware failures).
Transactional Statements in MySQL
In MySQL, the transactions begin with either START TRANSACTION, BEGIN or BEGIN WORK statements, and end with either a COMMIT or a ROLLBACK statement. The MySQL commands executed between the beginning and ending statements forms the main body of the transaction.
To enable or disable the auto-commit option in a transaction, you can use the SET AUTOCOMMIT command. To enable auto-commit, set the command to '1' or 'ON,' and to disable it, set the command to '0' or 'OFF.'
The COMMIT Command
The COMMIT command is a transaction control command in MySQL. When issued, it finalizes the changes made to a database table up to that point in a transaction, making those changes permanent. As a result, these changes become visible to other active sessions in MySQL.
Syntax
Following is the syntax to execute the COMMIT command in MySQL 
COMMIT;
Example
Let us create a table names CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
We are inserting some records into the above-created table 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
The CUSTOMERS table displayed is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Using the following query, start a transaction and delete the records from the CUSTOMERS table whose AGE is 25, then COMMIT the changes in the database 
START TRANSACTION;
DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;
Verification
Two rows from the table would be deleted. To verify, display the modified CUSTOMERS table using the following SELECT statement 
SELECT * FROM CUSTOMERS;
Following is the output obtained 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
3
Kaushik
23
Kota
2000.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
MP
4500.00
7
Muffy
24
Indore
10000.00
The AUTOCOMMIT Command
You can control the behaviour of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes.
When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.
The ROLLBACK Command
The ROLLBACK command is a transactional command used to undo changes made in a transaction that have not been saved (committed) to the database. This command can only reverse the effects of transactions made since the last COMMIT or ROLLBACK statement was executed.
Syntax
Following is the syntax for ROLLBACK command in MySQL 
ROLLBACK;
Example
Using the following query, delete the records from the CUSTOMERS table whose AGE is 25, then ROLLBACK the changes in the database 
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;
Verification
The table will not be affected. To verify, display the modified CUSTOMERS table using the following SELECT statement 
SELECT * FROM CUSTOMERS;
Following is the table obtained 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
You must remember that ROLLBACK only works inside a transaction. If you try to execute it without starting a transaction, the changes would not be revoked.
The SAVEPOINT Command
A SAVEPOINT is a logical rollback point within a transaction in MySQL.
When you execute the ROLLBACK command, it reverts the changes made in the transaction back to the last COMMIT or the beginning of the transaction if there haven't been any COMMITs. However, by creating save points within the transaction, you can establish specific points to which you can partially roll back the transaction. You can create multiple save points within a transaction to have multiple rollback options between two commits.
Syntax
The syntax for creating a SAVEPOINT command among transactions is as shown below 
SAVEPOINT SAVEPOINT_NAME;
﻿
The syntax for rolling back to the SAVEPOINT created is as follows 
ROLLBACK TO SAVEPOINT_NAME;
Example
In the following example, you plan to delete three different records from the CUSTOMERS table while creating SAVEPOINTs before each delete. This allows you to ROLLBACK to any SAVEPOINT at any time to restore the appropriate data to its original state 
SAVEPOINT SP1;
Query OK, 0 rows affected (0.00 sec)
﻿
DELETE FROM CUSTOMERS WHERE ID=1;
Query OK, 1 row affected (0.01 sec)
﻿
SAVEPOINT SP2;
Query OK, 0 rows affected (0.00 sec)
﻿
DELETE FROM CUSTOMERS WHERE ID=2;
Query OK, 0 rows affected (0.00 sec)
﻿
SAVEPOINT SP3;
Query OK, 0 rows affected (0.00 sec)
﻿
DELETE FROM CUSTOMERS WHERE ID=3;
Query OK, 1 row affected (0.01 sec)
Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT identified as SP2. Since SP2 was created after the first deletion, this action undoes the last two deletions 
ROLLBACK TO SP2;
Verification
If you display the CUSTOMERS table using the following SELECT statement, you will notice that only the first deletion took place since you rolled back to SP2 
SELECT * FROM CUSTOMERS;
The result obtained is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Transaction-Safe Table Types in MySQL
In MySQL, not all table types natively support transactions. To use transactions effectively, you should create your tables in a specific way. Although various table types are available, the most commonly used transaction-safe table type is InnoDB.
To enable InnoDB table support, you may need a specific compilation parameter during the MySQL source compilation. If your MySQL version doesn't include InnoDB support, you can request your Internet Service Provider (ISP) to provide a MySQL version with InnoDB support, or you can download and install MySQL-Max Binary Distribution for Windows or Linux/UNIX to work with InnoDB tables in a development environment.
If your MySQL installation supports InnoDB tables, you can create an InnoDB table as shown below 
CREATE TABLE tcount_tbl (
   tutorial_author varchar(40) NOT NULL,
   tutorial_count  INT
) ENGINE = InnoDB;
Following is the output obtained 
Query OK, 0 rows affected (0.05 sec)
You can also use other table types like GEMINI or BDB, but it depends on your installation, whether it supports these two table types or not.
Transactions Using a Client Program
We can also perform transactions using the client program.
Syntax
PHPNodeJSJavaPython
To perform the transactions through a PHP program, we need to execute three statements: "START TRANSACTION", "COMMIT", and "ROLLBACK" using the mysqli function query() as follows 
$sql = "START TRANSACTION";
$mysqli->query($sql);
...
$sql = "COMMIT";
$mysqli->query($sql);
...
$sql = "ROLLBACK";   
$mysqli->query($sql);
...
Example
Following are the programs 
PHPNodeJSJavaPython
 $dbhost = 'localhost';
 $dbuser = 'root';
 $dbpass = 'password';
 $db = 'TUTORIALS';
 $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
 if ($mysqli->connect_errno) {
     printf("Connect failed: %s", $mysqli->connect_error);
     exit();
 }
 //printf('Connected successfully.');
 //start transaction
 $sql = "START TRANSACTION";
if($mysqli->query($sql)){
 printf("Transaction started....!\n");
}
//print table record
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     printf("Table records after transaction...!\n");
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row['ID'],
         $row['NAME'],
         $row['AGE'],
         $row['ADDRESS'],
         $row['SALARY']);
         printf("\n");
     }
 }
 //let's delete some records
 $sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
 if($mysqli->query($sql)){
     printf("Records with age = 25 are deleted successfully....!\n");
 }
 //lets delete some more records..
 $sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
 if($mysqli->query($sql)){
     printf("Records with salary = 2000 are deleted successfully....!\n");
 }
 printf("Table data after second delete (before rollback)...!\n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row['ID'],
         $row['NAME'],
         $row['AGE'],
         $row['ADDRESS'],
         $row['SALARY']);
         printf("\n");
     }
 }
 $sql = "ROLLBACK";
 if($mysqli->query($sql)){
     printf("Transaction rollbacked successfully..!\n");
 }
 printf("Table data after rollback: \n");
 $sql = "SELECT * FROM CUSTOMERS";
 if($result = $mysqli->query($sql)){
     while($row = mysqli_fetch_array($result)){
         printf("ID %d, NAME %s, AGE %d, ADDRESS %s, SALARY %f",
         $row['ID'],
         $row['NAME'],
         $row['AGE'],
         $row['ADDRESS'],
         $row['SALARY']);
         printf("\n");
     }
 }
 if($mysqli->error){
     printf("Error message: ", $mysqli->error);
 }
 $mysqli->close();
Output
The output obtained is as shown below 
Transaction started....!
Table records after transaction...!
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID 4, NAME Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000
Records with age = 25 are deleted successfully....!
Records with salary = 2000 are deleted successfully....!
Table data after second delete (before rollback)...!
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000
Transaction rollbacked successfully..!
Table data after rollback:
ID 1, NAME Ramesh, AGE 32, ADDRESS Ahmedabad, SALARY 2000.000000
ID 2, NAME Khilan, AGE 25, ADDRESS Delhi, SALARY 1500.000000
ID 3, NAME kaushik, AGE 23, ADDRESS Kota, SALARY 2000.000000
ID 4, NAME Chaitali, AGE 25, ADDRESS Mumbai, SALARY 6500.000000
ID 5, NAME Hardik, AGE 27, ADDRESS Bhopal, SALARY 8500.000000
ID 6, NAME Komal, AGE 22, ADDRESS MP, SALARY 4500.000000
ID 7, NAME Muffy, AGE 24, ADDRESS Indore, SALARY 10000.000000   
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Transactions

The MySQL Transactions

Properties of Transactions

Transactional Statements in MySQL

The COMMIT Command

Syntax

Example

Verification

The AUTOCOMMIT Command

The ROLLBACK Command

Syntax

Example

Verification

The SAVEPOINT Command

Syntax

Example

Verification

Transaction-Safe Table Types in MySQL

Transactions Using a Client Program

Syntax

Example

Output