MySQL Tutorial

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

Clone Tables
There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table using the CREATE TABLE statement, you can clone the table without disturbing the original table.
Cloning operation in SQL allows the user to create the exact copy of an existing table along with its definition, that is completely independent from the original table. Thus, if any changes are made to the cloned table, they will not be reflected in the original table. This operation comes in handy during testing processes, where there is a need to perform sample testing using the existing database tables.
There are three types of cloning possible using SQL in MySQL RDBMS; they are listed below 
Simple Cloning: Creates a new table containing same records of existing table but void of any constraints or indexes etc.
Shallow Cloning: Creates a new empty table with the same table definition of an existing table.
Deep Cloning: Creates a new table and copies the table structure and data of an existing table to the new table.
Cloning Tables in MySQL
You can handle this situation by following the steps given below 
Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.
Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.
Example
Try out the following example to create a clone table for CUSTOMERS.
Step 1 − First of all, create the CUSTOMERS using the CREATE TABLE statement.
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)
);
Insert some records into it using the following INSERT INTO statement.
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );
Now, execute the following SHOW CREATE TABLE command to display the source table's (CUSTOMERS) structure.
SHOW CREATE TABLE CUSTOMERS;
Following is the CUSTOMERS table structure.
Table: CUSTOMERS
Create Table: CREATE TABLE `customers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Step 2 − Now, copy the above source table structure and just change the name to copyCUSTOMERS.
CREATE TABLE `copyCUSTOMERS` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `AGE` int NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Step 3 − After executing step 2, if we want to copy data from the source table then we can do it by using INSERT INTO... SELECT statement.
INSERT INTO copyCUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
   
SELECT ID,NAME,AGE,ADDRESS,SALARY FROM CUSTOMERS;
Let us verify whether the clone table copyCUSTOMERS has the same records as CUSTOMERS table or not using the following query 
SELECT * FROM copyCUSTOMERS;
As we observe the output, we have an exact clone table as CUSTOMERS.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
But to make this process simpler, we can try to perform Simple Cloning, Shallow Cloning or Deep Cloning using CREATE TABLE and INSERT INTO statements. Let us discuss them one by one in detail with suitable examples further in this tutorial.
Simple Cloning in MySQL
Simple Cloning means making a new table that contains the same data as an existing one. First, a new table is created using the CREATE TABLE statement. Then, data from selected columns in the existing table is copied into the new table using a SELECT statement.
Syntax
Following is the basic syntax to perform simple cloning in MySQL RDBMS 
CREATE TABLE new_table SELECT * FROM original_table;
Example
To perform the simple cloning operation, let us first create a table named CUSTOMERS table using the below query 
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)
);
The following query inserts 7 records into the above created table 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );
To display the records of the above created table, execute the following query 
SELECT * FROM CUSTOMERS;
Following is the CUSTOMERS table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, with the following query, we are creating a new table CUSTOMERScopy by cloning the CUSTOMERS table.
CREATE TABLE CUSTOMERScopy SELECT * FROM CUSTOMERS;
Output
Executing the query above will produce the following output 
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0
Verification
To verify whether the new table contains all the information from the existing table CUSTOMERS, we can use the following SELECT query 
SELECT * FROM CUSTOMERScopy;
Following is the CUSTOMERScopy table 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Shallow Cloning in MySQL
The Shallow Cloning operation only copies the structure of the existing table into the new table created, but it doesn't copy any of the data. So, we end up with a new empty table that has the same structure as the existing table.
Syntax
Following is the basic syntax to perform shallow cloning in MySQL RDBMS 
CREATE TABLE testCUSTOMERS1 LIKE CUSTOMERS;
Example
In the following query, we are performing shallow cloning by creating a new table named CUSTOMERScopy1 by cloning the CUSTOMERS table.
CREATE TABLE CUSTOMERScopy1 LIKE CUSTOMERS;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.03 sec)
Verification
To verify whether the new table is created or not, we can use the following SELECT query 
SELECT * FROM CUSTOMERScopy1;
As we can see in the output, the new table CUSTOMERScopy1 has been created with no data copied into it.
Empty set (0.00 sec)
Deep Cloning in MySQL
Deep Cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the attributes of the existing table and also its contents.
Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.
Syntax
Following is the basic syntax to perform deep cloning in MySQL RDBMS 
CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;
Example
In the following query, we are creating a new table CUSTOMERScopy2 by cloning the CUSTOMERS table, i.e. perform shallow cloning first.
CREATE TABLE CUSTOMERScopy2 LIKE CUSTOMERS;
Now using the following query, we are inserting data from CUSTOMERS table into new table CUSTOMERScopy2, i.e. performing simple cloning.
INSERT INTO CUSTOMERScopy2 SELECT * FROM CUSTOMERS;
Output
Executing the query above will produce the following output 
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
Verification
To verify whether the new table is created or not with all the data present in it, we can use the following SELECT query 
SELECT * FROM CUSTOMERScopy2;
As we observe the ouptut, the CUSTOMERScopy2 table has the same structure and data as CUSTOMERS table.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Cloning a table Using a Client Program
Besides using MySQL queries to clone an existing table, you can also perform the cloning operation on a table using a client program.
Syntax
Following are the syntaxes to clone a table various in programming languages 
PHPNodeJSJavaPython
To Clone a table into MySQL Database through a PHP program, we need to execute the following statement using the mysqli function query() as 
$sql = "CREATE TABLE clone_table LIKE tut_tbl";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
﻿
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
printf('Connected successfully.');
﻿
// clone table
// To inherit all table definitions, use the create table...like syntax
$sql = "CREATE TABLE clone_table LIKE tut_tbl";
if ($mysqli->query($sql)) {
    printf("Table cloned successfully.");
}
if ($mysqli->errno) {
    printf("table could not be cloned: %s", $mysqli->error);
}
﻿
// To copy the data, you'll need INSER...SELECT
$cpy_data = "INSERT INTO clone_table SELECT * FROM tut_tbl";
if ($mysqli->query($cpy_data)) {
    printf("fully cloned including data.");
}
if ($mysqli->errno) {
    printf("table could not be cloned fully: %s", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
Connected successfully.
Table cloned successfully.
fully cloned including data.
﻿
﻿
﻿
﻿
﻿
﻿
﻿

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Clone Tables

Cloning Tables in MySQL

Example

Simple Cloning in MySQL

Syntax

Example

Output

Verification

Shallow Cloning in MySQL

Syntax

Example

Output

Verification

Deep Cloning in MySQL

Syntax

Example

Output

Verification

Cloning a table Using a Client Program

Syntax

Example

Output