The MySQL Cursors

A MySQL cursor is a pointer that is used to iterate through a table”s records. They are used within stored programs such as procedures and functions and have the following features −

• READ ONLY − Cursors only allow you to read data; you can”t make changes to it.

• Non-Scrollable − Cursors move through records in one direction, from the top to the bottom.

• Asensitive − Cursors are sensitive to the changes made in the table. Any modification done in the table will be reflected in the cursor.

The following four operations are used to manage cursors in MySQL:

• Declare Cursor

• Open Cursor

• Fetch Cursor

• Close Cursor

Let us now see each of these operations in detail.

Declare Cursor

The DECLARE statement is used to declare a cursor in a MySQL. Once declared, it is then associated with a SELECT statement to retrieve the records from a table.

Following is the syntax to declare a cursor −

DECLARE cursor_name CURSOR FOR select_statement;

Open Cursor

The OPEN statement is used to initialize the cursor to retrieve the data after it has been declared.

Following is the syntax to open a cursor −

OPEN cursor_name;

Fetch Cursor

The FETCH statement is then used to retrieve the record pointed by the cursor. Once retrieved, the cursor moves to the next record.

Following is the syntax to fetch a cursor −

FETCH cursor_name INTO variable_list;

Close Cursor

The CLOSE statement is used to release the memory associated with the cursor after all the records have been retrieved.

Following is the syntax to close a cursor −

CLOSE cursor_name;

Example

In this example, we see how to manage a cursor in a stored procedure.

Assume two tables, CUSTOMERS and BACKUP, are created using the CREATE TABLE statement. The CUSTOMERS table holds information like ID, name, age, address, and salary as shown below −

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)
);

Now, we are creating the BACKUP table, having the same structure as the CUSTOMERS table to store a copy of the records from the CUSTOMERS table −

CREATE TABLE BACKUP (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Now let us insert some records into the CUSTOMERS table using the INSERT statement as follows −

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 following SQL query creates a cursor on the CUSTOMERS table −

DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS;

Now, we are creating a stored procedure named ExampleProc to manage the cursor from declaration to closure −

DELIMITER //
CREATE PROCEDURE ExampleProc()
   BEGIN
      -- Variable declarations
      DECLARE done INT DEFAULT 0;
      DECLARE cust_id, cust_age INTEGER;
      DECLARE cust_name VARCHAR(20);
      DECLARE cust_address CHAR(25);
	  DECLARE cust_salary DECIMAL(18,2);

	  -- Cursor declaration
      DECLARE cur CURSOR FOR SELECT * FROM CUSTOMERS;

	  -- Handler for no more records
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	  -- Open the cursor
      OPEN cur;

      -- Loop to fetch and insert records
      label: LOOP
      FETCH cur INTO cust_id, cust_name, cust_age, cust_address, cust_salary;
      INSERT INTO backup VALUES(cust_id, cust_name, cust_age, cust_address, cust_salary);
      IF done = 1 THEN LEAVE label;
      END IF;
      END LOOP;

	  -- Close the cursor
      CLOSE cur;
   END//
DELIMITER ;

Output

Finally, if we call the procedure using CALL ExampleProc(); and check the contents of the BACKUP table, it will contain the same records as CUSTOMERS −

SELECT * FROM BACKUP;

The BACKUP table obtained is as follows −

A sequence is a series of integers, starting from 1 and incrementing by 1 with each successive value. These sequences are usually used in databases, as many applications require each row in a table to have a unique identifier, and sequences provide an easy way to generate such values.

Sequences in MySQL

MySQL does not have a built-in sequence feature but provides an alternative in the form of the AUTO_INCREMENT column, which serves a similar purpose.

In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique integer values (sequences) for a column. By default, this sequence begins with an initial value of 1 and increments by 1 for each new row that is added.

Syntax

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

CREATE TABLE table_name (
   column1 datatype AUTO_INCREMENT,
   column2 datatype,
   column3 datatype,
   ...
   columnN datatype
);

Example

In the following example, we are creating a table named “CUSTOMERS” and, in addition, defining the AUTO_INCREMENT attribute for the “ID” column of the table −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert some records into the above-created table −

INSERT INTO CUSTOMERS VALUES
(NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(NULL, ''Khilan'', 25, ''Delhi'', 1500.00),
(NULL, ''Kaushik'', 23, ''Kota'', 2000.00),
(NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(NULL, ''Hardik'', 27, ''Bhopal'', 8500.00),
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00),
(NULL, ''Muffy'', 24, ''Indore'', 10000.00);

Output

We can see in the table displayed below that the values in the “ID” column are automatically incremented −

Retrieving AUTO_INCREMENT Values

To obtain AUTO_INCREMENT values in MySQL, you can use the LAST_INSERT_ID() SQL function. This function can be used in any client that can issue SQL statements. Alternatively, in PERL and PHP scripts, specific functions are available to retrieve the auto-incremented value of the last record.

PERL Example

You can access the AUTO_INCREMENT value generated by a query using the mysql_insertid attribute. This attribute can be accessed either through a database handle or a statement handle, depending on how you execute the query.

The following example references it through the database handle −

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')");
my $seq = $dbh->{mysql_insertid};

PHP Example

After executing a query that generates an AUTO_INCREMENT value, you can retrieve the value using the mysql_insert_id( ) command −

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

In some cases, you may need to re-sequence records in a table, especially if you have deleted many records. Be careful when resequencing if your table is related to other tables through joins.

If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the AUTO_INCREMENT column from the table, then add it again.

Example

The following example shows how to renumber the id values in the table using this technique.

ALTER TABLE CUSTOMERS DROP id;
ALTER TABLE CUSTOMERS
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

Output

Following is the output obtained −

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Starting a Sequence at a Specific Value

By default, MySQL starts sequences from 1, but you can specify a different initial value when creating the table.

Example

The following example demonstrates how to start the sequence from 100 during table creation −

CREATE TABLE CUSTOMERS (
   ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (ID),
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
)AUTO_INCREMENT = 100;

Output

Output of the above code is as shown below −

Query OK, 0 rows affected (0.04 sec)

Alternatively, you can create the table first and then set the initial sequence value using the ALTER TABLE command as shown below −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

Sequence Using a Client Program

We can also create a sequence using the client program.

Syntax

$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
$mysqli->query($sql);

sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
con.query(sql);

String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
statement.execute(sql);

create_table_query = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)"
cursorObj.execute(create_table_query)

Example

Following are the programs −

$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.
'');
//create table with autoincrement sequene
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
    printf("Table created successfully....!n");
}
//let''s insert some record
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
printf("Table records: n");
$sql = "SELECT * FROM insect";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin %s",
        $row[''id''],
        $row[''name''],
        $row[''date''],
        $row[''origin'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
Table record:
Id: 1, Name: housefly, Date: 2001-09-10, Origin kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin front yard

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some record
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
    con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Third record inserted successfully...!");
    });
sql = "SELECT * FROM INSECT";
con.query(sql, function(err, result){
console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records(with ID auto_increment sequence).
[
  {
    id: 1,
    name: ''housefly'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''kitchen''
  },
  {
    id: 2,
    name: ''millipede'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''driveway''
  },
  {
    id: 3,
    name: ''grasshopper'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''front yard''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Sequence {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
            st.execute(sql);
            System.out.println("Table ''insect'' created successfully...!");
            //lets insert some records into the table
            String sql1 = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen''), (NULL,''millipede'',''2001-09-10'',''driveway''), (NULL,''grasshopper'',''2001-09-10'',''front yard'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM INSECT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String date = rs.getString("date");
               String origin = rs.getString("origin");
               System.out.println("Id: " + id + ", Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table ''insect'' created successfully...!
Records inserted successfully...!
Table records:
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard

import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)
"""
cursorObj.execute(create_table_query)
print("Table ''insect'' is created successfully!")
# Inserting records
sql = "INSERT INTO insect (name, date, origin) VALUES (%s, %s, %s)"
values = [
    (''housefly'', ''2001-09-10'', ''kitchen''),
    (''millipede'', ''2001-09-10'', ''driveway''),
    (''grasshopper'', ''2001-09-10'', ''front yard'')
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''insect'' table.")
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM insect")
records = cursorObj.fetchall()
# Printing the records
print("Table records(with ID auto_increment sequence).")
for record in records:
    print(record)
# Closing the connection
cursorObj.close()
con.close()

Table ''insect'' is created successfully!
Data inserted into the ''insect'' table.
Table records(with ID auto_increment sequence).
(1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'')
(2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'')
(3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')

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 −

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 −

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 −

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 −

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

$sql = "START TRANSACTION";
$mysqli->query($sql);
...
$sql = "COMMIT";
$mysqli->query($sql);
...
$sql = "ROLLBACK";
$mysqli->query($sql);
...

sql = "START TRANSACTION";
con.query(sql);
...
sql = "COMMIT";
con.query(sql);
...
sql = "ROLLBACK";
con.query(sql);
...

String sql = "START TRANSACTION";
statement.execute(sql);
...
String sql = "COMMIT";
statement.execute(sql);
...
String sql = "ROLLBACK";
statement.execute(sql);
...

connection.start_transaction()
...
connection.commit()
...
connection.rollback()
...

Example

Following are the programs −

 $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();

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

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "START TRANSACTION";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction started....!");
});
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after transaction...!");
    if (err) throw err;
    console.log(result);
    });
//delete record
sql = "DELETE FROM CUSTOMERS WHERE AGE  = 25";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with age = 25 are deleted successfully....!");
});
//now lets delete more records
sql = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Records with salary = 2000 are deleted successfully....!");
});
//print table records before rollback;
sql = "SELECT * FROM CUSTOMERS";
con.query(sql, function(err, result){
   console.log("Table Data After Second Delete (Before Rollback)");
   if (err) throw err;
   console.log(result);
   });
//rollback the transaction
sql = "ROLLBACK";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Transaction rollbacked successfully..!");
});
//print table data after rollback;
sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
    console.log("Table records after rollback...!");
    if (err) throw err;
    console.log(result);
    });
});

Transaction started....!
Table records after transaction...!
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
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.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
Transaction rollbacked successfully..!
Table records after rollback...!
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Transaction {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //start transaction
            String sql = "START TRANSACTION";
            st.execute(sql);
            System.out.println("Transaction started....!");
            //print customers record after starting transaction
            String sql1 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql1);
            System.out.println("Table records after starting transaction: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
            //lets delete some records
            String sql2 = "DELETE FROM CUSTOMERS WHERE AGE = 25";
            st.execute(sql2);
            System.out.println("Customer with age 25 deleted successfully...!");
            String sql4 = "DELETE FROM CUSTOMERS WHERE SALARY  = 2000";
            st.execute(sql4);
            System.out.println("Customer with age 2000 deleted successfully...!");
            String sql5 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql5);
            System.out.println("Table records before rollback: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
            //lets roll-back the transaction
            String r = "ROLLBACK";
            st.execute(r);
            System.out.println("Transaction rollbacked successfully...!");
            String sql6 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("Table records after rollback: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String age = rs.getString("age");
               String address = rs.getString("address");
               String salary = rs.getString("salary");
               System.out.println("Id: " + id + ", Name: " + name +   ", Age: " + age +   ", Address: " + address +  ", Salary: " + salary);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Transaction started....!
Table records after starting transaction:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Customer with age 25 deleted successfully...!
Customer with age 2000 deleted successfully...!
Table records before rollback:
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Transaction rollbacked successfully...!
Table records after rollback:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Start the transaction
connection.start_transaction()
# Displaying the table before deleting
select_query = "SELECT * FROM customers"
cursorObj.execute(select_query)
print("Table Data after starting Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Execute the DELETE statement
delete_query = "DELETE FROM customers WHERE AGE = 25"
cursorObj.execute(delete_query)
print("Rows with AGE = 25 are deleted.")
# Commit the transaction
connection.commit()
print("Transaction committed successfully.")
# Displaying the table after deleting (changes are permanent)
cursorObj.execute(select_query)
print("Table Data After Transaction:")
for row in cursorObj.fetchall():
    print(row)
# Now, let us delete more records
delete_query1 = "DELETE FROM customers WHERE SALARY = 2000"
cursorObj.execute(delete_query1)
print("Rows with SALARY = 2000 are deleted.")
# Display the table after the second delete operation (changes are not committed yet)
cursorObj.execute(select_query)
print("Table Data After Second Delete (Before Rollback):")
for row in cursorObj.fetchall():
    print(row)
# Rollback the transaction
connection.rollback()
print("Transaction rollbacked successfully.")
# Displaying the table after rollback (changes are reverted)
cursorObj.execute(select_query)
print("Table Data After Rollback:")
for row in cursorObj.fetchall():
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table Data after starting Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(2, ''Khilan'', 25, ''Delhi'', Decimal(''1500.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(4, ''Chaitali'', 25, ''Mumbai'', Decimal(''6500.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with AGE = 25 are deleted.
Transaction committed successfully.
Table Data After Transaction:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Rows with SALARY = 2000 are deleted.
Table Data After Second Delete (Before Rollback):
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))
Transaction rollbacked successfully.
Table Data After Rollback:
(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(3, ''kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))

Tables or result sets in a database usually contain duplicate records. While duplicates are generally allowed, there are situations where it is necessary to prevent them. In such cases, it becomes essential to identify and remove duplicate records from a database table.

Importance of Handling MySQL Duplicates

There are various reasons why handling duplicates in a database becomes necessary. One of the main reasons is that the existence of duplicates in an organizational database will lead to logical errors. In addition to it, we need to handle redundant data to prevent the following consequences −

• Duplicate data occupies storage space, reducing the efficiency of database usage and increasing storage costs.
• Dealing with duplicate records consumes additional resources, driving up the overall cost of maintaining the database.
• Duplicates in a database can lead to logical errors in data, affecting the integrity and reliability of the information stored.

Preventing Duplicate Entries

You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to prevent duplicate record entries into a table.

Example

The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.

CREATE TABLE CUSTOMERS (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it is also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values −

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Using INSERT IGNORE Query −

The existence of a unique index in a table normally causes an error when attempting to insert a record that duplicates an existing record in the indexed column(s).

To handle this situation without generating an error, you can use the “INSERT IGNORE” command. When a record is not a duplicate, MySQL inserts it as usual. However, if the record is duplicate, the “IGNORE” keyword instructs MySQL to discard it without producing an error.

The provided example does not result in an error, and it also ensures that duplicate records are not inserted −

INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES (''Jay'', ''Thomas''), (''Jay'', ''Thomas'');

We get the following output −

Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

Using REPLACE Query −

Instead of using the INSERT command, consider using the REPLACE command. When dealing with a new record, it is inserted just as with INSERT. However, if it is a duplicate, the new record replaces the old one.

REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES ( ''Ajay'', ''Kumar''), ( ''Ajay'', ''Kumar'');

Following is the output of the above code −

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Your choice between the INSERT IGNORE and REPLACE commands should depend on the specific duplicate-handling behaviour you wish to achieve. The INSERT IGNORE command retains the first set of duplicated records and discards the remaining. On the other hand, the REPLACE command keeps the last set of duplicates and removes any earlier instances.

Using UNIQUE Constraint −

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table −

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

Counting and Identifying Duplicates

You can use the COUNT function and GROUP BY clause to count and identify duplicate records based on specific columns.

Example

Following is the query to count duplicate records with first_name and last_name in a table −

SELECT COUNT(*) as repetitions, last_name, first_name
FROM CUSTOMERS
GROUP BY last_name, first_name
HAVING repetitions > 1;

This query will return a list of all the duplicate records in the CUSTOMERS table. In general, to identify sets of values that are duplicated, follow the steps given below.

• Determine which columns may contain duplicated values.

• Include those columns in the column selection list, along with COUNT(*).

• List the columns in the GROUP BY clause as well.

• Apply a HAVING clause to filter unique values by requiring the group counts to be greater than one.

Eliminating Duplicates from a Query Result

You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table.

SELECT DISTINCT last_name, first_name
FROM CUSTOMERS
ORDER BY last_name;

An alternative to the DISTINCT command is to add a GROUP BY clause that specifies the columns you are selecting. This approach eliminates duplicates and retrieves only the unique combinations of values from the specified columns.

SELECT last_name, first_name
FROM CUSTOMERS
GROUP BY (last_name, first_name);

Removing Duplicates Using Table Replacement

If you have duplicate records in a table and you want to remove all the duplicate records from that table, then follow the procedure given below −

CREATE TABLE tmp AS
SELECT DISTINCT last_name, first_name, sex
FROM CUSTOMERS;

DROP TABLE CUSTOMERS;
ALTER TABLE tmp RENAME TO CUSTOMERS;

Handling Duplicates Using a Client Program

We can also handle duplicate using the client program.

Syntax

$sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
$mysqli->query($sql);

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
con.query(sql);

String sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
statement.execute(sql);

sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
cursorObj.execute(sql)

Example

Following are the programs −

$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.
'');
$sql = "CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
if($mysqli->query($sql)){
    printf("Table created successfully...!n");
}
//let''s insert some records
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
//now lets insert duplicate record with IGNORE keyword
$sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
if($mysqli->query($sql)){
    printf("Duplicate record inserted successfully using IGNORE keyword...!n");
}
$sql = "SELECT * from person_tbl";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("First Name: %s, Last name: %s, Sex: %s",
        $row[''first_name''],
        $row[''last_name''],
        $row[''sex'']);
        printf("n");
    }
}
//lets insert a duplicate record
$sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
if(!$mysqli->query($sql)){
    printf("You can''t insert any duplicate records...!n");
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Duplicate record inserted successfully using IGNORE keyword...!
Table records:
First Name: Thomas, Last name: Jay, Sex:
First Name: Smith, Last name: John, Sex:
PHP Fatal error:  Uncaught mysqli_sql_exception: Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY'' in D:testhandlingduplicates.php:48

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some records
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
//now lets insert duplicate record with IGNORE keyword
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record with IGNORE keyword")
    if (err) throw err;
    console.log("Duplicate record inserted successfully with the help of IGNORE keyword");
    });
//lets insert a duplicate record
sql = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')";
    con.query(sql, function(err, result){
        console.log("Insert duplicate record");
    if (err) throw err;
    console.log("You can''t insert the duplicate record because columns are primary key");
    });
sql = "SELECT * FROM person_tbl";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Insert duplicate record with IGNORE keyword
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record
Insert duplicate record
D:test1duplicate.js:43
    if (err) throw err;              ^

Error: Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY''

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HandleDuplicates {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name))";
            st.execute(sql);
            System.out.println("Table created successfully....!");
            //let''s insert some records
            String sql1 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
            st.execute(sql1);
            System.out.println("Record inserted successfully...!");
            String sql2 = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')";
            st.execute(sql2);
            System.out.println("Duplicate record inserted successfully...!");
            //lets print the table records
            String sql3 = "SELECT * FROM PERSON_TBL";
            rs = st.executeQuery(sql3);
            System.out.println("Table records: ");
            while(rs.next()) {
               String fname = rs.getString("first_name");
               String lname = rs.getString("last_name");
               System.out.println("First name: " + fname + ", Last name: " + lname);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table created successfully....!
Record inserted successfully...!
Duplicate record inserted successfully...!
Table records:
First name: Thomas, Last name: Jay

import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE person_tbl (
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    sex CHAR(10),
    PRIMARY KEY (last_name, first_name)
)
"""
cursorObj.execute(create_table_query)
print("Table ''person_tbl'' is created successfully!")
# Inserting some records
first_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
print("First record inserted successfully!")
cursorObj.execute(first_record)

second_record = "INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')"
print("Second record inserted successfully!")
cursorObj.execute(second_record)
# Insert duplicate record with IGNORE keyword
sql = "INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( ''Jay'', ''Thomas'')"
print("Duplicate record inserted successfully with the help of IGNORE keyword")
cursorObj.execute(sql)
# Insert a duplicate record (this will throw an error)
try:
    cursorObj.execute("INSERT INTO person_tbl (last_name, first_name) VALUES( ''John'', ''Smith'')")
    print("Insert duplicate record")
except mysql.connector.Error as err:
    print("Insert duplicate record error:", err)
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the connection
cursorObj.close()
con.close()

First record inserted successfully!
Second record inserted successfully!
Duplicate record inserted successfully with the help of IGNORE keyword
Insert duplicate record error: 1062 (23000): Duplicate entry ''John-Smith'' for key ''person_tbl.PRIMARY''
Table records.
(''Thomas'', ''Jay'', None)
(''Smith'', ''John'', None)

The SQL Injection in MySQL is a harmful approach where an attacker inserts or “injects” harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database.

How SQL Injection Works

Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below −

SELECT * FROM users
WHERE username = ''user'' AND password = ''password

In a secure application, the ”user” and ”password” would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code.

For example, they might enter the following as the username −

'' OR ''1'' = ''1

Now, the SQL query becomes −

SELECT * FROM users
WHERE username = '''' OR ''1'' = ''1'' AND password = ''password

Because ”1” always equals ”1”, this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password.

Preventing SQL Injection

To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this −

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
// escape input characters
$name = mysql_real_escape_string($name);

// Perform the MySQL query with the escaped ''name''
mysqli_query("SELECT * FROM CUSTOMERS WHERE name=''{$name}''");

The LIKE Quandary

Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include ”%” and ”_” characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining “mysql_real_escape_string()” function with “addcslashes()” function, which allows you to specify a character range to escape. Following is an example of how you can do it −

// Escape and convert ''%'' and ''_'' in the user-provided string
$sub = addcslashes(mysql_real_escape_string("%str"), "%_");

// $sub will be equal to %str_

// Use the escaped string in the LIKE query
mysqli_query("SELECT * FROM messages
   WHERE subject LIKE ''{$sub}%''");

In this way, you ensure that the ”%” and ”_” characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations.

The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.

Subquery with the SELECT Statement

A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery.

Syntax

Following is the basic syntax of a subquery within a SELECT statement −

SELECT column1, column2, ...
FROM table1
WHERE columnN operator
(SELECT column_name FROM table2 WHERE condition);

Example

First, let us create a table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Now, let us insert values into the above-created table using the INSERT statement as shown below −

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 shown below −

The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID”s match with the ID”s in the same table −

SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);

Output

The output for the query above is produced as given below −

Subquery with the INSERT Statement

We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table.

Syntax

Following is the basic syntax of a subquery within an INSERT statement −

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

Example

Before performing the subqueries with INSERT statement, let us create a table named “CUSTOMERS_BKP” with a similar structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_BKP(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Now, let us insert all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query −

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);

Output

The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement −

SELECT * FROM CUSTOMERS_BKP;

As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP table −

Subquery with Comparison Operator

The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators.

Syntax

Following is the basic syntax of a subquery with comparison operators −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE] .....)

Example

The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs.

SELECT * FROM CUSTOMERS_BKP
WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP
WHERE AGE > 23);

Output

The output for the query above is produced as given below −

Subquery with IN or NOT-IN Operator

The MySQL subqueries with IN/NOT-IN operators are used to filter data based on whether values from one query match or do not match values from another query −

• IN matches any value from the list

• NOT-IN excludes any value from the list.

Example

The following query retrieves all the records from the CUSTOMERS table where the ADDRESS is not “Hyderabad” by comparing it to addresses in the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS NOT IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

Following is the output of the above query −

Example

Now, the following query retrieves all the rows from the CUSTOMERS table where the ADDRESS is “Hyderabad” by using a subquery to fetch all addresses that match “Hyderabad” from the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

On executing the given query, the output is displayed as follows −

Subquery Using a Client Program

We can also perform Subquery using the client program.

Syntax

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
$mysqli->query($sql);

sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
statement.executeQuery(sql);

sub_query = "SELECT SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS)"
cursorObj.execute(sql)

Example

Following are the programs −

$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.
'');
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
printf("Table records: n");
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();

Table records:
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: Hyderabad, SALARY: 4500.000000
Id: 7, NAME: Muffy, AGE: 24, ADDRESS: Indore, SALARY: 10000.000000

NodeJS program
var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
 con.query(sql, function(err, result){
    console.log("Subquery executed successfully...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});

Subquery executed successfully...!
Table records:
[
  { NAME: ''Chaitali'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: ''6500.00'' },
  { NAME: ''Hardik'', AGE: 27, ADDRESS: ''Bhopal'', SALARY: ''8500.00'' },
  { NAME: ''Komal'', AGE: 22, ADDRESS: ''Hyderabad'', SALARY: ''4500.00'' },
  { NAME: ''Muffy'', AGE: 24, ADDRESS: ''Indore'', SALARY: ''10000.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SubQuery {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 2000)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Hyderabad, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Subquery to fetch the salaries of all customers whose ID is present in the same table
sub_query = f"""
SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);
"""
cursorObj.execute(sub_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(Decimal(''2000.00''),)
(Decimal(''1500.00''),)
(Decimal(''2000.00''),)
(Decimal(''6500.00''),)
(Decimal(''8500.00''),)
(Decimal(''4500.00''),)
(Decimal(''10000.00''),)

The MySQL Comments

The MySQL Comment is a textual explanation added to a piece of code to provide additional information about the code. Comments are not meant to be executed as part of the code. It serve as notes for readers, including developers, to understand the purpose of the code, functionality, or any other relevant details.

There are two types of comments in MySQL: Single-line comments and Multi-line comments

The MySQL Single Line Comments

Single-line comments are used for brief explanations on a single line. To create a single-line comment in MySQL, use two hyphens (–) followed by your comment text.

Example

In the following query, we are using a single line comment to write a text.

SELECT * FROM customers; -- This is a comment

The MySQL Multi-line Comments

Multi-line comments in MySQL are used for longer explanations or to comment out multiple lines of code. These comments start with /* and end with */. Everything between them is considered a comment.

Example

The following example uses multi-line comment as an explanation of the query −

/*
This is a multi-line comment.
You can use it to explain complex queries or comment out multiple lines of code.

SELECT *
FROM products
WHERE price > 50;
*/

Where to Place Comments

You can place comments almost anywhere in your SQL code. Common places include −

• Before or after a SQL statement.

• Within a SQL statement to explain a specific part of it.

• At the beginning of a script or stored procedure to describe its purpose.

-- This is a comment before a query
SELECT * FROM orders;

SELECT /* This is an inline comment */ customer_name
FROM customers;

/* This is a comment block at the beginning of a script */
DELIMITER //
CREATE PROCEDURE CalculateDiscount(IN product_id INT)
BEGIN
    -- Calculate discount logic here
END //
DELIMITER ;

Comments Using a Client Program

We can also comment any value using the client program.

Syntax

single line comment
--
multiline comment
/**/

(using Query)

$sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
$mysqli->query($sql);

single line comment
--
multiline comment
/**/

(using Query)

sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
con.query(sql);

single line comment
--
multiline comment
/**/

(using Query)

String sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
statement.executeQuery(sql);

single line comment
--
multiline comment
/**/

(using Query)

comments_query = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''"
cursorObj.execute(comments_query)

Example

Following are the programs −

$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.
'');
$sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
if($mysqli->query($sql)){
    printf("Select query executed successfully...!n");
}
printf("Table records: n");
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d",
        $row[''ID'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Select query executed successfully...!
Table records:
Id: 4

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
 con.query(sql, function(err, result){
    console.log("Select query executed successfully(where we commented the name and address column)...!");
    console.log("Table records: ")
    if (err) throw err;
    console.log(result);
    });
});

Select query executed successfully(where we commented the name and address column)...!
Table records:
[ { ID: 4 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Comments {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              System.out.println("Id: " + id);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table records:
Id: 4

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query with comments
comments_query = """SELECT ID /*NAME, ADDRESS*/ FROM CUSTOMERS WHERE ADDRESS = ''Mumbai''"""
cursorObj.execute(comments_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Printing the result
print("IDs of customers from Mumbai:")
for row in filtered_rows:
    print(row[0])
cursorObj.close()
connection.close()

IDs of customers from Mumbai:
4

The MySQL Storage Engines

As we already know, a MySQL database is used to store data in the form of rows and columns. The MySQL storage engine is a component that is used to handle the SQL operations performed to manage this data. They work with simple tasks like creating a table, renaming it, updating or deleting it; which is necessary to increase the database performance.

There are two categories of storage engines used: transactional engines and non-transactional engines. Many common storage engines fall into either type of these categories. In MySQL, however, the default storage engine is InnoDB.

Common Storage Engines

Various common storage engines that are used to work with MySQL are as follows −

InnoDB Storage Engine

• ACID Compliant − InnoDB is the default storage engine in MySQL 5.5 and later versions. It is a transactional database engine, ensuring ACID compliance, which means it supports operations like commit and rollback.
• Crash-Recovery − InnoDB offers crash-recovery capabilities to protect user data.
• Row-Level Locking − It supports row-level locking, which enhances multi-user concurrency and performance.
• Referential Integrity − It also enforces FOREIGN KEY referential-integrity constraints.

ISAM Storage Engine

• Deprecated − ISAM, which stands for Indexed Sequential Access Method, was supported by earlier MySQL versions but has been deprecated and removed from recent versions.
• Limited Size − ISAM tables were limited to a size of 4GB.

MyISAM Storage Engine

• Portability − MyISAM is designed for portability, addressing ISAM”s non-portable nature.
• Performance − It offers faster performance compared to ISAM and was the default storage engine before MySQL 5.x.
• Memory Efficiency − MyISAM tables have a small memory footprint, making them suitable for read-only or read-mostly workloads.

MERGE Storage Engine

• Logical Combination − MERGE table enables a MySQL developer to logically combine multiple identical MyISAM tables and reference them as one object.
• Limited Operations − Only INSERT, SELECT, DELETE, and UPDATE operations are allowed on MERGE tables. If DROP query is used, only the storage engine specification gets reset while the table remains unchanged.

MEMORY Storage Engine

• In-Memory Storage − MEMORY tables store data entirely in RAM, optimizing access speed for quick lookups.
• Hash Indexes − It uses hash indexes for faster data retrieval.
• Decreasing Use − Its use cases are decreasing; other engines, like InnoDB”s buffer pool memory area provide better memory management.

CSV Storage Engine

• CSV Format − CSV tables are text files with comma-separated values, useful for data exchange with scripts and applications.
• No Indexing − They are not indexed, and generally used during data import or export alongside InnoDB tables.

NDBCLUSTER Storage Engine

• Clustering − NDBCLUSTER, also known as NDB, is a clustered database engine suitable for applications that require the highest possible degree of uptime and availability.

ARCHIVE Storage Engine

• Historical Data − ARCHIVE tables are ideal for storing and retrieving large amounts of historical, archived, or secure data. The ARCHIVE storage engines support supports non-indexed tables

BLACKHOLE Storage Engine

• Data Discard − BLACKHOLE tables accept data but do not store it, always returning an empty set.
• Usage − Used in replication configurations, where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

FEDERATED Storage Engine

• Distributed Databases − FEDERATED allows linking separate MySQL servers to create a logical database from multiple physical servers, useful in distributed environments.

EXAMPLE Storage Engine

• Development Tool − EXAMPLE is a tool in the MySQL source code that serves as an example for developers to start writing new storage engines. You can create tables with this engine, but it doesn”t store or retrieve data.

Even though there are so many storage engines that can be used with databases, there is no such thing called a perfect storage engine. In some situations, one storage engine could be a better fit to use whereas in other situations, other engines perform better. Therefore, one must carefully choose what Storage engine to use while working in certain environments.

To choose an engine, you can use the SHOW ENGINES statement.

SHOW ENGINES Statement

The SHOW ENGINES statement in MySQL will list out all the storage engines. It can be taken into consideration while choosing an engine that are supported by the database and are easy to work with.

Syntax

Following is the syntax of the SHOW ENGINES statement −

SHOW ENGINESG

where, the ”G” delimiter is used to vertically align the result-set obtained from executing this statement.

Example

Let us observe the result-set obtained by executing the SHOW ENGINES statement in a MySQL database using the following query −

SHOW ENGINESG

Output

Following is the result-set obtained. Here, you can check which storage engines are supported by the MySQL database and where they can be best used −

*************************** 1. row ************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ************************
      Engine: ndbinfo
     Support: NO
     Comment: MySQL Cluster system information storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 10. row ************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 11. row ************************
      Engine: ndbcluster
     Support: NO
     Comment: Clustered, fault-tolerant tables
Transactions: NULL
          XA: NULL
  Savepoints: NULL
11 rows in set (0.00 sec)

Setting a Storage Engine

Once a storage engine is chosen to be used on a table, you might want to set it while creating the database table. This is done by specifying the type of engine you want to use by adding its name in the CREATE TABLE statement.

If you do not specify the engine type, the default engine (InnoDB for MySQL) will be used automatically.

Syntax

Following is the syntax to set a storage engine in CREATE TABLE statement −

CREATE TABLE table_name (
   column_name1 datatype,
   column_name2 datatype,
   .
   .
   .
) ENGINE = engine_name;

Example

In this example, let us create a new table ”TEST” on MyISAM storage engine using the following query −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = MyISAM;

The result obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

But if we create a table on an engine that is not supported by MySQL, say FEDERATED, an error is raised −

CREATE TABLE TEST (
   ROLL INT,
   NAME VARCHAR(25),
   MARKS DECIMAL(20, 2)
) ENGINE = FEDERATED;

We get the following error −

ERROR 1286 (42000): Unknown storage engine ''FEDERATED''

Changing Default Storage Engine

MySQL also has provisions to change the default storage engine option in three ways −

• Using ”–default-storage-engine=name” server startup option.

• Setting ”default-storage-engine” option in ”my.cnf” configuration file.

• Using SET statement

Syntax

Let us see the syntax of using SET statement to change the default storage engine in a database −

SET default_storage_engine = engine_name;

Note − The storage engine for temporary tables, which were created with the CREATE TEMPORARY TABLE statement, can be set separately by setting the ”default_tmp_storage_engine”, either at startup or at runtime.

Example

In this example, we are changing the default storage engine to MyISAM using SET statement given as follows −

SET default_storage_engine = MyISAM;

The result obtained is as follows −

Query OK, 0 rows affected (0.00 sec)

Now, let us list the storage engines using SHOW ENGINES statement below. The support column for MyISAM storage engine is changed to default −

SHOW ENGINESG

Output

Following is the result-set produced. Here, note that we are not displaying the entire result-set and only the MyISAM row for simpler understandability. The actual result-set has 11 total rows −

*************************** 6. row ************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
11 rows in set (0.00 sec)

Altering Storage Engine

You can also alter the existing storage engine of a table to another storage engine using the ALTER TABLE command in MySQL. However, the storage engine must be changed to one that is supported by MySQL only.

Syntax

Following is the basic syntax to change the existing storage engine to another −

ALTER TABLE table_name ENGINE = engine_name;

Example

Consider the previously created table TEST on MyISAM database engine. In this example, using the following ALTER TABLE command, we are changing it to InnoDB engine.

ALTER TABLE TEST ENGINE = InnoDB;

Output

After executing the above query, we get the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the storage engine is changed or not, use the following query −

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''testDB

The table produced is as shown below −

Storage Engines Using a Client Program

We can also perform storage Engines using the client program.

Syntax

$sql = "SHOW ENGINES";
$mysqli->query($sql);

sql = "SHOW ENGINES";
con.query(sql);

String sql = "SHOW ENGINES";
statement.executeQuery(sql);

sql = "SHOW ENGINES"
cursorObj.execute(sql)

Example

Following are the programs −

$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.
'');
$sql = "SHOW ENGINES";
if($mysqli->query($sql)){
    printf("Show query executed successfully....!n");
}
printf("Storage engines: n");
if($result = $mysqli->query($sql)){
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Show query executed successfully....!
Storage engines:
mysqli_result Object
(
    [current_field] => 0
    [field_count] => 6
    [lengths] =>
    [num_rows] => 11
    [type] => 0
)

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "SHOW ENGINES";
 con.query(sql, function(err, result){
    console.log("Show query executed successfully....!");
    console.log("Storage engines: ")
    if (err) throw err;
    console.log(result);
    });
});

Show query executed successfully....!
Storage engines:
[
  {
    Engine: ''MEMORY'',
    Support: ''YES'',
    Comment: ''Hash based, stored in memory, useful for temporary tables'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''MRG_MYISAM'',
    Support: ''YES'',
    Comment: ''Collection of identical MyISAM tables'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''CSV'',
    Support: ''YES'',
    Comment: ''CSV storage engine'',
    Transactions: ''NO'',
    XA: ''NO'',
    Savepoints: ''NO''
  },
  {
    Engine: ''FEDERATED'',
    Support: ''NO'',
    Comment: ''Federated MySQL storage engine'',
    Transactions: null,
    XA: null,
    Savepoints: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class StorageEngine {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "SHOW ENGINES";
            rs = st.executeQuery(sql);
            System.out.println("Storage engines: ");
            while(rs.next()) {
              String engines = rs.getNString(1);
              System.out.println(engines);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Storage engines:
MEMORY
MRG_MYISAM
CSV
FEDERATED
PERFORMANCE_SCHEMA
MyISAM
InnoDB
ndbinfo
BLACKHOLE
ARCHIVE
ndbcluster

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query to get information about storage engines
storage_engines_query = "SHOW ENGINES"
cursorObj.execute(storage_engines_query)
# Fetching all records about storage engines
all_storage_engines = cursorObj.fetchall()
for row in all_storage_engines:
    print(row)
# Closing the cursor and connection
cursorObj.close()
connection.close()

(''MEMORY'', ''YES'', ''Hash based, stored in memory, useful for temporary tables'', ''NO'', ''NO'', ''NO'')
(''MRG_MYISAM'', ''YES'', ''Collection of identical MyISAM tables'', ''NO'', ''NO'', ''NO'')
(''CSV'', ''YES'', ''CSV storage engine'', ''NO'', ''NO'', ''NO'')
(''FEDERATED'', ''NO'', ''Federated MySQL storage engine'', None, None, None)
(''PERFORMANCE_SCHEMA'', ''YES'', ''Performance Schema'', ''NO'', ''NO'', ''NO'')
(''MyISAM'', ''YES'', ''MyISAM storage engine'', ''NO'', ''NO'', ''NO'')
(''InnoDB'', ''DEFAULT'', ''Supports transactions, row-level locking, and foreign keys'', ''YES'', ''YES'', ''YES'')
(''BLACKHOLE'', ''YES'', ''/dev/null storage engine (anything you write to it disappears)'', ''NO'', ''NO'', ''NO'')
(''ARCHIVE'', ''YES'', ''Archive storage engine'', ''NO'', ''NO'', ''NO'')

The MySQL Check Constraint

The MySQL Check Constraint is a condition that can be applied to a column to ensure that the inserted or updated data in that column meets the specified condition. The database rejects the operation if the condition is not met to maintain data integrity.

Check Constraint with a Trigger

A trigger in MySQL is used to automatically execute a set of SQL statements in response to specific events in the database, such as an INSERT, UPDATE, or DELETE operation.

A check constraint with a trigger allows us to perform actions automatically based on data changes.

Example

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below −

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)
);

Following query inserts values into CUSTOMERS table using the INSERT statement −

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 table obtained is as shown below −

Now, we will add a check constraint to ensure that the age of customers in the CUSTOMERS table should be greater than or equal to 18. Additionally, we will create a trigger that, when an attempt is made to insert a record with an age less than 18, it will raise an error and prevent the insertion −

-- Creating a Trigger
DELIMITER //
CREATE TRIGGER check_age_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
   IF NEW.AGE < 18 THEN
      SIGNAL SQLSTATE ''45000''
      SET MESSAGE_TEXT = ''Age must be 18 or older
   END IF;
END;
//
DELIMITER ;

-- Adding a Check Constraint
ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18);

Output

We get the output as shown below −

Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

Adding Check Constraint on Single Column

We can apply a check constraint on a column by specifying the check constraint after the column name at the time of table creation.

Syntax

Following is the syntax to specify the check constraint on column −

CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName
   CHECK Check(columnName condition value),..., column datatype (size)
);

Example

In this example, we are creating a table named EMPLOYEES and specifying a column-level check constraint on one column −

CREATE TABLE EMPLOYEES(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE
);

We can verify if the check constraint is working correctly by inserting a value into the EMPLOYEES table which does not satisfy the condition −

INSERT INTO EMPLOYEES
VALUES (1, ''John'', 19, ''New York'', ''09182829109'');

Output

The output obtained is as follows −

ERROR 3819 (HY000): Check constraint ''employees_chk_1'' is violated.

Adding Check Constraint on Multiple Columns

We can add check constraint on multiple columns of a table by specifying the constraints for each column after the column name.

Example

In the following example, we are creating a table named STUDENTS and specifying a column-level check constraint on multiple columns (AGE and FEE) −

CREATE TABLE STUDENTS(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000)
);

Now, we can insert records, but if we attempt to insert a record that violates these constraints, the database will reject it.

Here, we are inserting a valid record −

INSERT INTO STUDENTS
VALUES (001, ''Robert'', 21, ''LA'', 17000);

We can see in the output below that the insertion is successful because the age is within the allowed range, and the fee meets the specified condition −

Query OK, 1 row affected (0.01 sec)

In here, we are attempting to insert a record violating constraints −

INSERT INTO STUDENTS
VALUES (002, ''James'', 25, ''Barcelona'', 10000);

We can see that the insertion fails since the age exceeds 24, violating the constraint.

ERROR 3819 (HY000): Check constraint ''students_chk_1'' is violated.

Adding Check Constraint on an Existing Table

We can also add a check constraint on an existing table in MySQL by using the ALTER statement. We must ensure that the constraint satisfy for the existing records in the table.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT ConstraintName
CHECK(ColumnName condition Value);

Example

In the following example, we are adding a check constraint to the AGE column of the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT Constraint_Age
CHECK (AGE >= 21);

Output

Following is the output of the above code −

Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

Dropping Check Constraint

We can remove an existing constraint by using the ALTER statement with the DROP statement.

Syntax

Following is the syntax to remove a constraint from the table −

ALTER TABLE table_name
DROP CONSTRAINT constraint_set;

Example

Following example removes an existing constraint from the AGE column in the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Output

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check-Constraints Using a Client Program

We can also perform check-constraints using the client program.

Syntax

$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)"
$mysqli->query($sql);

sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
con.query(sql);

String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
statement.execute(sql);

create_table_query = ''CREATE TABLE EMPLOYEES(EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE);
cursorObj.execute(create_table_query)

Example

Following are the programs −

$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.
'');
$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
if($mysqli->query($sql)){
    printf("Table created successfully...!n");
}
//let''s insert some records... whose age is greater than 20
$sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')";
if($mysqli->query($sql)){
    printf("First record(age>20) inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')";
if($mysqli->query($sql)){
    printf("Second record(age>20) inserted successfully...!n");
}
//table record before inserting employee record whose age is less than 20;
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records(before inserting emp record agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row[''EID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''CITY''],
        $row[''C_Phone'']);
        printf("n");
    }
}
//let''s insert some records... whose age is less than 20
$sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')";
if($mysqli->query($sql)){
    printf("Third record(agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row[''EID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''CITY''],
        $row[''C_Phone'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully...!
First record(age>20) inserted successfully...!
Second record(age>20) inserted successfully...!
Table records(before inserting emp record age

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table with check constraints
 sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some records(age greater than 20)
sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')";
    con.query(sql, function(err, result){
        if (err) throw err;
        console.log(result);
        });
sql = "SELECT * FROM EMPLOYEES";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
D:test1checkcons.js:34
        if (err) throw err;                 ^
Error: Check constraint ''employees_chk_1'' is violated.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CheckConstraints {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
            st.execute(sql);
            System.out.println("Employees table created successfully...!");
            //let''s insert some records
            String sql1 = "INSERT INTO EMPLOYEES VALUES (1, ''John'', 19, ''New York'', ''09182829109'')";
            st.execute(sql1);
            System.out.println("Record inserted successfully....!");
            //lets print table records
            String sql2 = "SELECT * FROM EMPLOYEES";
            rs = st.executeQuery(sql2);
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String city = rs.getString("city");
                String c_phone = rs.getString("C_Phone");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", City: " + city + ", C_phone: " + c_phone);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Employees table created successfully...!
java.sql.SQLException: Check constraint ''employees_chk_1'' is violated.

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table
create_table_query = ''''''
CREATE TABLE EMPLOYEES(
    EID INT NOT NULL,
    NAME VARCHAR(40),
    AGE INT NOT NULL CHECK(AGE>=20),
    CITY VARCHAR(30),
    C_Phone VARCHAR(12) NOT NULL UNIQUE
)
''''''
cursorObj.execute(create_table_query)
print("Table ''EMPLOYEES'' is created successfully!")
# Describing the EMPLOYEES table
describe = "DESC EMPLOYEES"
cursorObj.execute(describe)
print("Table Description:")
for column_info in cursorObj.fetchall():
    print(column_info)
# Inserting the first record
try:
    sql = "INSERT INTO EMPLOYEES VALUES(1, ''Jay'', 30, ''Hyderabad'', ''223233'')"
    cursorObj.execute(sql)
    connection.commit()
    print("First record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# Inserting the second record
try:
    sql = "INSERT INTO EMPLOYEES VALUES(2, ''John'', 35, ''Lucknow'', ''213032'')"
    cursorObj.execute(sql)
    connection.commit()
    print("Second record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# Inserting the third record with age less than 20 (this will raise an error)
try:
    sql = "INSERT INTO EMPLOYEES VALUES(3, ''Vinnet'', 18, ''Hyderabad'', ''228151'')"
    cursorObj.execute(sql)
    connection.commit()
    print("Third record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table ''EMPLOYEES'' is created successfully!
Table Description:
(''EID'', b''int'', ''NO'', '''', None, '''')
(''NAME'', b''varchar(40)'', ''YES'', '''', None, '''')
(''AGE'', b''int'', ''NO'', '''', None, '''')
(''CITY'', b''varchar(30)'', ''YES'', '''', None, '''')
(''C_Phone'', b''varchar(12)'', ''NO'', ''PRI'', None, '''')
First record inserted successfully!
Second record inserted successfully!
Error: 3819 (HY000): Check constraint ''employees_chk_1'' is violated.
Table records.
(''Thomas'', ''Jay'', None)
(''Smith'', ''John'', None)

Import MySQL CSV into Database

Before importing the CSV file into the database server, we must ensure the following things −

• Database Table − Ensure you have a database table already set up to receive the incoming data.
• CSV File − You need a CSV file containing the data to be imported.
• User Privileges − Ensure your account has the necessary privileges, specifically FILE and INSERT, to perform this operation.
• Matching Columns − The target table and the CSV file should have matching columns with the same data types.
• CSV Format − The CSV file should be in a comma-separated format, with each row representing a record.

Syntax

Following is the syntax of the LOAD DATA INFILE statement in MySQL −

LOAD DATA INFILE file_path
INTO TABLE table_name
FIELDS TERMINATED BY delimiter
ENCLOSED BY enclosure
LINES TERMINATED BY line_separator
IGNORE number_of_lines_to_skip;

Where,

• file_path is the path to the CSV file that contains the data to be imported.

• table_name is the name of the target table, where the data will be imported.

• delimiter is a character that separates each record in the CSV file.

• Enclosure is a character that encloses string records in the CSV file.

• line_seperator is a character that marks the end of a line in the CSV file.

• number_of_lines_to_skip is the number of lines to ignore at the beginning of the CSV file.

Example

First of all, let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Now, let us insert rows into the above created table −

INSERT INTO EMPLOYEES 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, ''MP'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The EMPLOYEES table obtained is as shown below −

Export Data to CSV −

Now, we export the data from the EMPLOYEES table into a CSV file named “EMPLOYEES_BACKUP” using the following query −

SELECT * FROM EMPLOYEES
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

Following is the image of “EMPLOYEES_BACKUP.csv” file when we opened it −

Create Another Table −

Now, let us create another table named “CUSTOMERS” with the same columns and data types as EMPLOYEES table −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Following is the output of the above code −

Query OK, 0 rows affected (0.03 sec)

Import Data from CSV −

Now, we import all the data from “EMPLOYEES_BACKUP.csv” file into the CUSTOMERS table using the following query −

LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv''
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''n

The result obtained is as follows −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

Verify Data Import −

To verify that the data has been successfully imported into the CUSTOMERS table, we can use the following SELECT statement −

SELECT * FROM CUSTOMERS;

As we can see the output below, the CUSTOMERS table contains the same data as the EMPLOYEES table, as the CSV data has been imported successfully −

Importing a CSV File Using Client Program

We can also import CSV file into database using Client Program.

Syntax

$sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''";
$mysqli->query($sql);

sql = `LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''`;
con.query(sql);

String sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''\n''";
statement.execute(sql);

import_sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''rn''"
cursorObj.execute(import_sql)

Example

Following are the programs −

$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.
'');
$sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Employees table created successfully...!n");
}
//now lets insert some data into it..
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',
32, ''Ahmedabad'', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'',
25, ''Delhi'', 1500.00 )";
 if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'',
23, ''Kota'', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
//display table record
$sql = "SELECT * FROM EMPLOYEES";
if($result = $mysqli->query($sql)){
    printf("Table records: 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");
    }
}
//now let''s export the table data into csv file
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!n");
   print_r($result , "n");
}
//now let''s create a customers table
$sql = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!n");
}
//now lets import employees table data csv file into customers table
$sql = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''";
if($mysqli->query($sql)){
    printf("CSV file data imported successfully into Customers table....!n");
}
//print customers table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Customers table records after importing csv file data into it: 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");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
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
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
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

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create employees table
 sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
 if(con.query(sql)){
    console.log("Employees table created successfully...!");
 }
//now let''s insert some values
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'',
32, ''Ahmedabad'', 2000.00 )`;
if(con.query(sql)){
    console.log("First record inserted successfully...!");
}
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'',
    25, ''Delhi'', 1500.00 )`;
if(con.query(sql)){
    console.log("Second record inserted successfully...!");
}
sql = `INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'',
    23, ''Kota'', 2000.00 )`;
if(con.query(sql)){
    console.log("Third record inserted successfully...!");
}
sql = "SELECT * FROM EMPLOYEES";
con.query(sql, function(err, result){
    console.log("Employees table records: ");
    if (err) throw err;
    console.log(result);
    });
sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table data exported successfully....!");
    });
sql = `CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Customers table created successfully...!");
    });
sql = `LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''n''`;
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("CSV file data imported successfully into Customers table....!");
    });
sql = "SELECT * FROM CUSTOMERS";
con.query(sql, function(err, result){
    console.log("Customers table records after importing csv file data into it: ")
    if (err) throw err;
    console.log(result);
});
});

Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Employees table records:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ImportCSVToDatabase {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql);
            System.out.println("Table EMPLOYEES created successfully....!");
            //let''s insert some records into it...
            String sql1 = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''kaushik'', 23, ''Kota'', 2000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM  EMPLOYEES";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets export the table records into csv file
            String sql3 = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
            st.execute(sql3);
            System.out.println("Table records exported successfully into CSV file....!");
            //let''s create one more table name Customers;
            String sql4 = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID))";
            st.execute(sql4);
            System.out.println("Table Customers created successfully...!");
            //lets import csv file into database
            String sql5 = "LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'' INTO TABLE CUSTOMERS FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '''' LINES TERMINATED BY ''\n''";
            st.execute(sql5);
            System.out.println("Data imported successfully...!");
            //now lets print customers table records
            String sql6 = "SELECT * FROM  CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("id");
              String name = rs.getString("name");
              String age = rs.getString("age");
              String address = rs.getString("address");
              String salary = rs.getString("salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Table EMPLOYEES created successfully....!
Records inserted successfully...!
Table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Table records exported successfully into CSV file....!
Table Customers created successfully...!
Data imported successfully...!
Table records:
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table ''EMPLOYEES''
create_table_query = ''''''
CREATE TABLE EMPLOYEES(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25) NOT NULL,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
''''''
cursorObj.execute(create_table_query)
print("Table ''EMPLOYEES'' is created successfully!")
# Inserting records into ''EMPLOYEES'' table
sql = "INSERT INTO EMPLOYEES (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
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, ''MP'', 4500.00),
    (7, ''Muffy'', 24, ''Indore'', 10000.00)
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''EMPLOYEES'' table.")
# Export the table data into a CSV file
export_sql = """
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY '',''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn
"""
cursorObj.execute(export_sql)
print("Table data exported successfully!")
# Now let''s create a ''CUSTOMERS'' table
create_another_table = ''''''
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25) NOT NULL,
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);
''''''
cursorObj.execute(create_another_table)
print("Table ''CUSTOMERS'' is created successfully!")
# Now let''s import ''EMPLOYEES'' table data from the CSV file into ''CUSTOMERS'' table
import_sql = """
LOAD DATA INFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEE_BACKUP.csv''
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn''
"""
cursorObj.execute(import_sql)
print("CSV file data imported successfully into Customers table....!n")
# Print ''CUSTOMERS'' table records
cursorObj.execute("SELECT * FROM CUSTOMERS")
records = cursorObj.fetchall()
# Printing the records
print("Customers table records after importing CSV file data into it: n")
for record in records:
    print(record)

# Closing the cursor and connection
cursorObj.close()
connection.close()

Table ''EMPLOYEES'' is created successfully!
Data inserted into the ''EMPLOYEES'' table.
Table data exported successfully!
Table ''CUSTOMERS'' is created successfully!
CSV file data imported successfully into Customers table...!

Customers table records after importing CSV file data into it:

(1, ''Ramesh'', 32, ''Ahmedabad'', Decimal(''2000.00''))
(2, ''Khilan'', 25, ''Delhi'', Decimal(''1500.00''))
(3, ''Kaushik'', 23, ''Kota'', Decimal(''2000.00''))
(4, ''Chaitali'', 25, ''Mumbai'', Decimal(''6500.00''))
(5, ''Hardik'', 27, ''Bhopal'', Decimal(''8500.00''))
(6, ''Komal'', 22, ''MP'', Decimal(''4500.00''))
(7, ''Muffy'', 24, ''Indore'', Decimal(''10000.00''))