Category: mysql

MySQL – Table Locking Table of content Locking Tables in MySQL Unlocking Tables in MySQL Table Locking Using a Client Program ”; Previous Next MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks. A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously. A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released. Locking Tables in MySQL You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session. MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges. These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks − READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table. WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table. Syntax Following is the syntax of the MySQL LOCK TABLES Statement − LOCK TABLES table_name [READ | WRITE]; Unlocking Tables in MySQL Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again. Syntax Following is the syntax of the MySQL UNLOCK TABLES Statement − UNLOCK TABLES; Example Let us start with creating a table named CUSTOMERS that contains the details as shown below − 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”s insert 2 records into the above created table using the INSERT statement as − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); Create another table named BUYERS using the following query − CREATE TABLE BUYERS ( B_ID INT AUTO_INCREMENT, B_NAME VARCHAR(20) NOT NULL, B_AGE INT NOT NULL, B_ADDRESS CHAR (25), B_SALARY DECIMAL (18, 2), PRIMARY KEY (B_ID) ); Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table. Locking and Unlocking: Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records. LOCK TABLES CUSTOMERS READ, BUYERS WRITE; INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY) SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID = 1 AND NAME = ”Ramesh”; INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY) SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE ID = 2 AND NAME = ”Khilan”; UNLOCK TABLES; Verification We can verify the contents of the BUYERS table using the below query − SELECT * FROM BUYERS; As we can see in the BUYERS table, the records has been transferred. B_ID B_NAME B_AGE B_ADDRESS B_SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Table Locking Using a Client Program Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation. Syntax Following are the syntaxes to Lock a table in MySQL in various programming languages − PHP NodeJS Java Python To lock the table in MySQL database through a PHP program, we need to execute the Lock Tables statement using the mysqli function query() as − $sql=”LOCK TABLES table_name [READ | WRITE]”; $mysqli->query($sql); To lock the table in MySQL database through a Node.js program, we need to execute the Lock statement using the query() function of the mysql2 library as − sql = “LOCK TABLES table_name [READ | WRITE]”; con.query(sql); To lock the table in MySQL database through a Java program, we need to execute the Lock statement using the JDBC function executeUpdate() as − String sql=”LOCK TABLES table_name [READ | WRITE]”; statement.executeUpdate(sql); To lock the table in MySQL database through a Python program, we need to execute the Lock statement using the execute() function of the MySQL Connector/Python as − sql=”LOCK TABLES table_name [READ | WRITE]”; cursorObj.execute(sql); Example Following are the programs − PHP NodeJS Java Python $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.”);

MySQL – Derived Tables Table of content MySQL Derived Tables Using WHERE Clause Aliasing a Column in Derived Table Displaying Aggregate Functions as Derived Tables Deriving Table Using a Client Program ”; Previous Next MySQL Derived Tables The Derived tables are pretty much what their name describes: they are the tables that are derived from another MySQL database table (main table). In other words, the derived table is a virtual result-set obtained from a SELECT statement given as a subquery to another SELECT statement of the main table. This table is similar to a temporary table. But unlike temporary tables, you need not create a derived table separately; the records in it are retrieved from the main table using a subquery. Therefore, similar to the actual database table, a derived table can also be displayed as a result-set of computations, aggregate functions, etc. Syntax Following is the basic syntax to display a derived table in MySQL − SELECT column_name(s) FROM (subquery) AS derived_table_name; Example Let us see a simple example demonstrating how derived table is displayed in MySQL. In the following query, we are creating a new table CUSTOMERS − 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 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 retrieve the records of the CUSTOMERS table, execute the following query − SELECT * FROM CUSTOMERS; Following are the records present in 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, we are retrieving a derived table from this CUSTOMERS table using the following query − SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS; The derived table DERIVED_CUSTOMERS is hence obtained with ID, NAME and SALARY as its attributes. ID NAME SALARY 1 Ramesh 2000.00 2 Khilan 1500.00 3 Kaushik 2000.00 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 Using WHERE Clause We can also use the WHERE clause to filter records (or rows) from the derived table. Following is the syntax for it − SELECT column_name(s) FROM (subquery) AS derived_table_name WHERE [condition]; Example In the following query, we are retrieving a derived table from the CUSTOMERS table created initially. We are doing this by filtering rows from it using the WHERE clause − SELECT ID, NAME, SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS WHERE DERIVED_CUSTOMERS.SALARY > 5000.00; Executing the query above will produce the following output − ID NAME SALARY 4 Chaitali 6500.00 5 Hardik 8500.00 7 Muffy 10000.00 Aliasing a Column in Derived Table In derived tables, not only the table name, but we can also alias a column name while displaying the contents. Following is the syntax − SELECT column_name(s) AS alias_name(s) FROM (subquery) AS derived_table_name; Example In the example below, we are displaying the derived table from the CUSTOMERS table with the aliased columns using the following query − SELECT ID AS DERIVED_ID, NAME AS DERIVED_NAME, SALARY AS DERIVED_SALARY FROM (SELECT * FROM CUSTOMERS) AS DERIVED_CUSTOMERS; Output Executing the query above will produce the following output − DERIVED_ID DERIVED_NAME DERIVED_SALARY 1 Ramesh 2000.00 2 Khilan 1500.00 3 Kaushik 2000.00 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 Displaying Aggregate Functions as Derived Tables We can also show the result of an aggregate function or calculations performed on the main table”s records as a derived table. Following is the syntax to display aggregate functions as a derived table − SELECT function_name() FROM (subquery) AS derived_table_name; Example In the following query, we are using the aggregate SUM() function to calculate the total salary from the CUSTOMERS table − SELECT SUM(SALARY) FROM (SELECT SALARY FROM CUSTOMERS) AS DERIVED_CUSTOMERS; Output Executing the query above will produce the following output − SUM(SALARY) 35000.00 Example In the following query, we use the aggregate AVG() function to calculate the average salary of customers from the CUSTOMERS table. SELECT AVG(DERIVED_SUM) AS AVERAGE_SALARY FROM (SELECT SUM(SALARY) AS DERIVED_SUM FROM CUSTOMERS)

MySQL – Show Columns Table of content MySQL Show Columns Statement The LIKE clause The WHERE clause The FULL clause Showing Columns of a table Using a Client Program ”; Previous Next MySQL Show Columns Statement To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements. All of these statements of MySQL can be used to retrieve/display the description of all the columns of a table, as they all retrieve the same result-sets. Obtaining column information can be useful in several situations like inserting values into a table (based on the column datatype), updating or dropping a column, or to just simply know a table”s structure. In this chapter, let us understand how to use SHOW COLUMNS statement in detail. Syntax Following is the syntax of the MySQL SHOW COLUMNS Statement − SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS} {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE ”pattern” | WHERE expr] Example Let us start with creating a database named TUTORIALS using the below query − CREATE DATABASE TUTORIALS; Execute the following statement to change into TUTORIALS database − USE TUTORIALS; In the following query, we are creating a table named CUSTOMERS using the following 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) ); Now, we are using the SHOW COLUMNS statement to retrieve the information about columns of the CUSTOMERS table − SHOW COLUMNS FROM CUSTOMERS; Output Following is the information of columns in CUSTOMERS table − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Example We can also use the IN clause instead of FROM as shown in the query below − SHOW COLUMNS IN CUSTOMERS; Output As we can obeserve the output, it is exactly the same as the previous output. Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Example We can specify the name of the database along with the table name as shown in the query below − SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS; Output Following is the information of columns in CUSTOMERS table that is present in TUTORIALS database. Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Example We can replace the COLUMNS clause with FIELDS and get the same results − SHOW FIELDS IN CUSTOMERS; Output As we see the output, we got the same results as COLUMNS clause. Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL The LIKE clause In MySQL, using the LIKE clause, you can specify a pattern to retrieve info about specific columns. Example Following query retrieves the column names starting with the letter “P” from CUSTOMERS table. SHOW COLUMNS FROM CUSTOMERS LIKE ”N%”; Output Executing the query above will produce the following output − Field Type Null Key Default Extra NAME varchar(20) NO NULL The WHERE clause We can use the MySQL WHERE clause of the SHOW COLUMNS statements to retrieve information about the columns which match the specified condition. Example In the following example, we are using the WHERE clause to retrieve the columns where there type is int. SHOW COLUMNS FROM CUSTOMERS WHERE Type= ”int”; Output Executing the query above will produce the following output − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment AGE int NO NULL The FULL clause Usually, the information provided by the SHOW COLUMNS statements contains field type, can be null or not, key, default values and some extra details. If you use the full clause details like collation, privileges and comments will be added. Example

MySQL – ALTER Command Table of content MySQL ALTER Command Dropping a Column Adding a Column Repositioning a Column Altering a Column Definition or a Name Altering a Column”s Default Value Altering (Renaming) a Table Altering Table Using a Client Program ”; Previous Next MySQL ALTER Command The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table. Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table. Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure. Syntax Following is the syntax of ALTER command in MySQL − ALTER TABLE table_name [alter_option …]; Example Let us begin with the creation of a table named CUSTOMERS. CREATE TABLE CUSTOMERS ( ID INT, NAME VARCHAR(20) ); Now, execute the following query to display information about the columns in CUSTOMERS table. SHOW COLUMNS FROM CUSTOMERS; Output Following are the details of the columns of the CUSTOMERS table − Field Type Null Key Default Extra ID int YES NULL NAME varchar(20) YES NULL Dropping a Column To drop a column in an existing table, we use the ALTER TABLE command with DROP clause. Example In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table − ALTER TABLE CUSTOMERS DROP ID; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query − SHOW COLUMNS FROM CUSTOMERS; As we can see in the output below, there is no ID column present. Hence it is dropped. Field Type Null Key Default Extra NAME varchar(20) YES NULL Note: A DROP clause will not work if the column is the only one left in the table. Adding a Column To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command. Example In the following query, we are adding a column named ID into an existing table CUSTOMERS. ALTER TABLE CUSTOMERS ADD ID INT; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column. Verification Let us verify using the following query − SHOW COLUMNS FROM CUSTOMERS; As we can see in the output below, the newly added ID column is inserted at the end of the table. Field Type Null Key Default Extra NAME varchar(20) YES NULL ID int YES NULL Repositioning a Column If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name. Example Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column. In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword − ALTER TABLE CUSTOMERS DROP ID; ALTER TABLE CUSTOMERS ADD ID INT FIRST; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us verify the positions of the column in the CUSTOMERS table − SHOW COLUMNS FROM CUSTOMERS; As we can see in the output below, the ID column is positioned first. Field Type Null Key Default Extra ID int YES NULL NAME varchar(20) YES NULL Example Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword. ALTER TABLE CUSTOMERS DROP ID; ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us verify the positions of the column in the CUSTOMERS table − SHOW COLUMNS FROM CUSTOMERS; As we can see in the output below, the ID column is positioned first. Field Type Null Key Default Extra NAME varchar(20) YES NULL ID int YES NULL Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position. Altering a Column Definition or a Name In MySQL, to change a column”s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command. Example In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause − ALTER TABLE CUSTOMERS MODIFY NAME INT; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us verify the definition of the NAME column in the CUSTOMERS table − SHOW COLUMNS FROM CUSTOMERS; As we can observe, the definition for NAME column has been changed to INT. Field Type Null Key Default Extra NAME int YES NULL ID int YES NULL Example We can also change the column definition using CHANGE,

MySQL – Rename Columns Table of content Using the RENAME COLUMN Using CHANGE COLUMN Renaming a Column of a Table Using a Client Program ”; Previous Next The ALTER TABLE statement in MySQL can be used to change the structure of a table. For instance, we can add, delete, or rename the columns, etc. using this statement. Following are the two options that can be used with the ALTER TABLE statement to rename a column(s) of a table: RENAME COLUMN CHANGE COLUMN Note: Renaming a column(s) of table requires ALTER and DROP privilages. Using the RENAME COLUMN In MySQL, we can change the name of one or multiple columns of a specified table using the ALTER TABLE RENAME COLUMN command. Syntax Following is the syntax to rename a column in MySQL table − ALTER TABLE table_name RENAME COLUMN old_column1_name TO new_column1_name, RENAME COLUMN old_column2_name TO new_column2_name, …; Example First of all, let us create a table named CUSTOMERS using the query below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL ); Here, we are using the DESCRIBE command to display the information about the above created table structure − DESCRIBE CUSTOMERS; As we can see in the table below, we have three columns present in CUSTOMERS table − Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL Now, we are renaming the column named ID to cust_id using following query − ALTER TABLE CUSTOMERS RENAME COLUMN ID TO cust_id; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not − DESCRIBE CUSTOMERS; As we observe in the output table, the ID column is renamed to stud_id successfully. Field Type Null Key Default Extra cust_id int NO NULL NAME varchar(20) NO NULL AGE int NO NULL Example Now, we are renaming the other two columns in CUSTOMERS table named NAME and AGE to cust_name and cust_age − ALTER TABLE CUSTOMERS RENAME COLUMN NAME TO cust_name, RENAME COLUMN AGE TO cust_age; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification To verify whether column names have been renamed or not, execute the following query − DESCRIBE CUSTOMERS; As we observe in the output table, the above mentioned columns are successfully renamed. Field Type Null Key Default Extra cust_id int NO NULL cust_name varchar(20) NO NULL cust_age int NO NULL Using CHANGE COLUMN In MySQL, we can change the name of one or more columns along with their datatypes using the ALTER TABLE … CHANGE COLUMN command. Syntax Following is the syntax of the ALTER TABLE … CHANGE commnad in MySQL − ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name Data Type; Example Consider the previously updated CUSTOMERS table and, let us change the name and the datatype of cust_id column − ALTER TABLE CUSTOMERS CHANGE COLUMN cust_id ID varchar(10); Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Using the following query, we can verify whether the column cust_id has changed its name and datatype or not − DESCRIBE CUSTOMERS; The name of the column and datatype has been changed successfully. Field Type Null Key Default Extra ID varchar(10) NO NULL cust_name varchar(20) NO NULL cust_age int NO NULL Example Here, we are changing the names and datatypes of multiple columns (cust_name and cust_age) in the CUSTOMERS table − ALTER TABLE CUSTOMERS CHANGE COLUMN cust_name NAME DECIMAL(18,2), CHANGE COLUMN cust_age AGE VARCHAR(20); Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Let us retrive the CUSTOMERS table description to verify whether the columns name and datatype are changed or not − DESCRIBE STUDENTS; As we observe in the output table, the names and datatypes of above mentioned columns are successfully changed. Field Type Null Key Default Extra ID varchar(10) NO NULL NAME decimal(18,2) NO NULL AGE varchar(20) NO NULL Renaming a Column of a Table Using a Client Program In addition

MySQL – Temporary Tables Table of content What are Temporary Tables? Creating Temporary Tables in MySQL Dropping Temporary Tables in MySQL Creating Temporary table Using a Client Program ”; Previous Next What are Temporary Tables? The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually. You can perform various SQL operations on temporary tables, just like you would with permanent tables, including CREATE, UPDATE, DELETE, INSERT, JOIN, etc. Temporary tables were introduced in MySQL version 3.23. If you”re using an older version of MySQL that”s older than 3.23, you won”t be able to use temporary tables, instead you can use the Heap Tables. As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table. Creating Temporary Tables in MySQL Creating a temporary table in MySQL is very similar to creating a regular database table. But, instead of using CREATE TABLE, we use CREATE TEMPORARY TABLE statement. Syntax Following is the syntax to create a temporary table in MySQL − CREATE TEMPORARY TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); Example First of all, let us create a temporary table named CUSTOMERS using the below query − CREATE TEMPORARY 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) ); Similar to normal tables, we can insert records into a temporary table using the INSERT statement. Here, we are inserting three records into the above created temporary table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”kaushik”, 23, ”Kota”, 2000.00 ); Execute the following query to display all the records of the temporary table CUSTOMERS. SELECT * FROM CUSTOMERS; Following are the records of 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 When we issue a SHOW TABLES command, our temporary table won”t be displayed in the list of tables. To verify if the temporary table exists, we need to use the SELECT statement to retrieve its data. Temporary tables will be deleted when we ends our session, so if we log out of MySQL and then try to issue the SELECT command, we won”t find the temporary table in the database. Dropping Temporary Tables in MySQL Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to. Syntax Following is the syntax for dropping a temporary table in MySQL − DROP TEMPORARY TABLE table_name; Example In the following query, we are dropping the temporary table CUSTOMERS that was created in the previous example − DROP TEMPORARY TABLE CUSTOMERS; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.00 sec) Verification Now, let us verify the temporary table CUSTOMERS by retrieving it”s records using the following query − SELECT * FROM CUSTOMERS; Since we removed the the temporary table CUSTOMERS, it will generate an error saying the table does not exist. ERROR 1146: Table ”TUTORIALS.CUSTOMERS” doesn”t exist Creating Temporary table Using a Client Program In addition to create a temporary table in MySQL Database using the MySQL query, we can also perform the “TEMPORARY TABLE” operation on a table using a client program. Syntax Following are the syntaxes to create a temporary table into MySQL Database in various programming languages − PHP NodeJS Java Python To create a temporary table into MySQL database through a PHP program, we need to execute the Create Temporary Table statement using the mysqli function query() as − $sql=”CREATE temporary Table table_name(column_name, column_type, …)”; $mysqli->query($sql); To create a temporary table into MySQL database through a Node.js program, we need to execute the Create Temporary Table statement using the query() function of the mysql2 library as − sql=”CREATE temporary Table table_name(column_name, column_type, …)”; con.query(sql); To create a temporary table into MySQL database through a Java program, we need to execute the Create Temporary Table statement using the JDBC function executeUpdate() as − String sql=”CREATE temporary Table table_name(column_name, column_type, …)”; statement.executeQuery(sql); To create a temporary table into MySQL database through a Python program, we need to execute the Create Temporary Table statement using the execute() function of the MySQL Connector/Python as − sql=”CREATE temporary Table table_name(column_name, column_type, …)”; cursorObj.execute(sql); Example Following are the programs − PHP NodeJS Java Python $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.”); $sql = “CREATE TEMPORARY TABLE SalesSummary (” . ” product_name VARCHAR(50) NOT NULL, ” . ” total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, ” . ” avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, ” . ” total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )”; if ($mysqli->query($sql)) { printf(“temporary table created successfully.”); } if ($mysqli->errno) { printf(“temporary table could not be created: %s”, $mysqli->error); } $mysqli->close(); Output The output obtained is as follows − temporary table created successfully. var mysql = require(”mysql2”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “Nr5a0204@123” }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log(“Connected!”); console.log(“————————–“); //Creating a Database sql = “CREATE DATABASE testdb” con.query(sql); //Selecting a Database sql = “USE testdb” con.query(sql); //Creating table sql = “CREATE

MySQL – Clone Tables Table of content Cloning Tables in MySQL Simple Cloning in MySQL Shallow Cloning in MySQL Deep Cloning in MySQL Cloning a table Using a Client Program ”; Previous Next 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

MySQL − Show Tables Table of content MySQL Show Tables Statement SHOW TABLES with FULL modifier SHOW TABLES in different Database SHOW TABLES using Pattern Matching Showing tables Using a Client Program ”; Previous Next MySQL Show Tables Statement In MySQL, we use the SHOW TABLES command to retrieve the names of tables that are present in a specific database. This command is useful in various situations, such as: When we want to view names of tables present in a database to verify if a specific table exists or not. When we want to display additional information about each table present in a database, we use the SHOW TABLES command with the MySQL FULL modifier. Additionally, we can use the SHOW TABLES command with WILDCARDS to filter and display only the tables that match a specific pattern. Syntax Following is the syntax of MySQL SHOW TABLES command− SHOW TABLES; Before proceeding with the examples, assume that the following tables exist in two databases, testdb1 and testdb2: Databases testdb1 testdb2 Tables employee_remarks employee_age employee_salary students_marks students_attendance students_fees students_remarks Example First of all, we are changing the database to testdb1 to perform the SHOW TABLES operation on it. mysql> USE testdb1; Database changed Now, execute the following query to list down all the tables from testdb1 database. SHOW TABLES; Output Following are the tables that are present in the testdb1 database − Tables_in_testdb1 employee_remarks employee_salary students_attendance students_fees students_remarks SHOW TABLES with FULL modifier In MySQL, we use the optional FULL modifier along with the SHOW TABLES command to display a second output column that contains additional information about the tables present in a database, such as their types: BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table. Example In the following query, we are using the FULL modifier along with the SHOW TABLES command to list the tables and their types in the testdb1 database. SHOW FULL TABLES; Output Following is the output of the above query − Tables_in_testdb1 Table_type employee_remarks BASE TABLE employee_salary BASE TABLE students_attendance BASE TABLE students_fees BASE TABLE students_remarks BASE TABLE SHOW TABLES in different Database In MySQL, we can retrieve the list of tables present in another database. To do so, we need to use the IN operator or the FROM clause in conjunction with the SHOW TABLES statement. Example In the following query, we are fetching the list of tables that exist in another database testdb2, using the SHOW TABLES command with IN operator. SHOW TABLES IN testdb2; Output Following are the names of the tables that are present in testdb2 database − Tables_in_testdb2 employee_age students_marks Example We can also perform the above operation using the SHOW TABLES command with FROM clause. SHOW TABLES FROM testdb2; Output As we can observe, both outputs are the same. Tables_in_testdb2 employee_age students_marks SHOW TABLES using Pattern Matching In some scenarios where there are large amount of tables present in a database, and we want to retrieve only specific tables, we use the LIKE operator with WILDCARD characters such as ”%”. These wildcards will filter and display only the tables that match a specific pattern.” Example In the following query, we are using the LIKE operator with SHOW TABLES command to select all the tables (in testdb1 database) where the name starts with “stud”. SHOW TABLES IN testdb1 LIKE “stud%”; Output Following are the tables present in testdb1 database whose name starts with “stud” − Tables_in_testdb1 (stud%) students_attendance students_fees students_remarks Example Here, we are trying to retrieve the tables from testdb2 database where the name starts with “stud” − SHOW TABLES IN testdb2 LIKE “stud%”; Output This will produce following result − Tables_in_testdb2 (stud%) students_marks Example We are using the SHOW TABLES along with WHERE clause to check if there is a table named “employee_remarks” in testdb1 database − SHOW TABLES FROM testdb1 WHERE Tables_in_testdb1 = “employee_remarks”; Output This will produce following result − Tables_in_testdb1 employee_remarks Showing tables Using a Client Program Besides showing the list of tables present in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW TABLES operation. Syntax Following are the syntaxes to Show list of tables in MySQL Database in various programming languages − PHP NodeJS Java Python To show the list of tables in MySQL Database through a PHP program, we need to execute SHOW TABLES statement using the mysqli function query() as − $sql = “SHOW TABLES FROM DATABASE”; $mysqli->query($sql); To show the list of tables in MySQL Database through a Node.js program, we need to execute SHOW TABLES statement using the query() function of the mysql2 library as − sql=

MySQL – Grant Privileges Table of content The MySQL Grant Privileges The MySQL GRANT Statement Granting Privileges on Stored Routines Privileges to Multiple Users Global Privileges Database Level Privileges Column Level Privileges Proxy User Privileges Granting Roles Granting Privileges Using a Client Program ”; Previous Next As we learnt earlier, a root user is connected to the server (using a password) immediately after installing MySQL. The privileges available to this user are default. The user accessing MySQL using root account has enough privileges to perform basic operations on the data. However, in exceptional cases, the user must manually request the host to grant privileges. The MySQL Grant Privileges MySQL provides several SQL statements to allow or restrict administrative privileges for users to interact with the data stored in the database. They are listed below − GRANT statement REVOKE statement In this tutorial, let us learn about the GRANT statement in detail. The MySQL GRANT Statement The MySQL GRANT statement is used to assign various privileges or roles to MySQL user accounts. However, it”s important to note that you cannot assign both privileges and roles in a single GRANT statement. To grant privileges to users using this statement, you need to have the GRANT OPTION privilege. Syntax Following is the syntax of the MySQL GRANT Statement − GRANT privilege1, privilege2, privilege3… ON object_type TO user_or_role1, user_or_role2, user_or_role3… [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role ] … | role [, role ] … ] ] Example Assume we have created a user named ”test_user”@”localhost” in MySQL using the CREATE USER statement − CREATE USER ”test_user”@”localhost” IDENTIFIED BY ”testpassword”; Following is the output of the above code − Query OK, 0 rows affected (0.23 sec) Now, let us create a database − CREATE DATABASE test_database; The output produced is as follows − Query OK, 0 rows affected (0.56 sec) Next, we will use the created database − USE test_database; We get the output as shown below − Database changed Now, let us create a table in the database − CREATE TABLE MyTable(data VARCHAR(255)); The output obtained is as follows − Query OK, 0 rows affected (0.67 sec) Following query grants SELECT privileges on the table created above to the user ”test_user”@”localhost” − GRANT SELECT ON test_database.MyTable TO ”test_user”@”localhost”; After executing the above code, we get the following output − Query OK, 0 rows affected (0.31 sec) Verification You can verify the granted privileges using the SHOW GRANTS statements − SHOW GRANTS FOR ”test_user”@”localhost”; The output we get is as shown below − Grants for test_user@localhost GRANT USAGE ON *.* TO `test_user`@`localhost` GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost` Granting Various Privileges We know that the MySQL GRANT statement allows a wide range of privileges to user accounts. Here is a list of some commonly used privileges that can be granted using the GRANT statement − Privileges Description ALTER Allows users to modify table structures using the ALTER TABLE statement. CREATE Grants the ability to create new objects such as tables and databases. DELETE Enables users to delete rows from tables. INSERT Allows users to insert new records into tables. SELECT Provides read access to tables, allowing users to retrieve data. UPDATE Allows users to modify existing data in tables. SHOW DATABASES Grants the ability to see a list of available databases. CREATE USER Allows users to create new MySQL user accounts. GRANT OPTION Provides users with the authority to grant privileges to other users. SUPER Grants high-level administrative privileges. SHUTDOWN Allows users to shut down the MySQL server. REPLICATION CLIENT Provides access to replication-related information. REPLICATION SLAVE Enables users to act as a replication slave server. FILE Grants permission to read and write files on the server”s file system. CREATE VIEW Allows users to create new database views. CREATE TEMPORARY TABLES Allows the creation of temporary tables. EXECUTE Enables users to execute stored procedures and functions. TRIGGER Provides the ability to create and manage triggers. EVENT Grants the ability to create and manage events. SHOW VIEW Allows users to see the definition of views. INDEX Enables users to create and drop indexes on tables. PROXY Provides the capability to proxy or impersonate other users. Example To GRANT all the available privileges to a user, you need to use the ”ALL” keyword in the GRANT statement − GRANT ALL ON test_database.MyTable TO ”test_user”@”localhost”; Output After executing the above code, we get the following output − Query OK, 0 rows affected (0.13 sec) Granting Privileges on Stored Routines To grant privileges on stored routines, such as tables, procedures or functions, in MySQL, you need to specify the object type (PROCEDURE or FUNCTION) after the ON clause followed by the name of the routine. You can grant ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges on these stored routines. Example Assume

MySQL – Truncate Table Table of content MySQL TRUNCATE TABLE Statement TRUNCATE vs DELETE TRUNCATE vs DROP Truncating Table Using a Client Program ”; Previous Next MySQL TRUNCATE TABLE Statement The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table. This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process. You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table”s structure from the database. If you want to store some data again, you would need to re-create this table once again. Syntax Following is the basic syntax of the TRUNCATE TABLE statement − TRUNCATE TABLE table_name Where, table_name is the name of the table you need to delete all the records from. Example First of all, let us create a table with 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), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, we are inserting 7 records into the above-created table using the following INSERT 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 ); Using the following query, we are displaying the records of CUSTOMERS table − SELECT * FROM CUSTOMERS; Following are the records of 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 In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table − TRUNCATE TABLE CUSTOMERS; Output The records have been truncated from the CUSTOMERS table without any error. Query OK, 0 rows affected (0.02 sec) Verification To verify whether the records have been truncated, let us retrieve the records using the following query − SELECT * FROM CUSTOMERS; As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated. Empty set (0.00 sec) TRUNCATE vs DELETE Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically: DELETE TRUNCATE The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. It is a DML(Data Manipulation Language) command. It is a DDL(Data Definition Language) command. There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. When you use the TRUNCATE command, the modifications made to the table are committed automatically. It deletes rows one at a time and applies some criteria to each deletion. It removes all of the information in one go. The WHERE clause serves as the condition in this case. There is no necessity of using a WHERE Clause. All rows are locked after deletion. TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. It makes a record of each and every transaction in the log file. The only activity recorded is the deallocation of the pages on which the data is stored. It consumes a greater amount of transaction space compared to TRUNCATE command. It takes comparatively less amount of transaction space. If there is an identity column, the table identity is not reset to the value it had when the table was created. It returns the table identity to a value it was given as a seed. It requires authorization to delete. It requires table alter permission. When it comes to large databases, it is much slower. It is faster. TRUNCATE vs DROP The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table”s records, whereas DROP command deletes the table entirely from the database. However, there are still some differences between these commands, which are summarized in the following table − DROP TRUNCATE The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. It is a DDL(Data Definition Language) command. It is also a DDL(Data Definition Language) command. The table space is