Create, Alter, Drop, Truncate, View in MySQL Table

Create, alter, drop, truncate and view in MySQL table; Through this tutorial, i am going to show you MySQL statements such as Create Table, Alter Table, Drop Table, Truncate Table, Create Views Table, Drop views table & Update views table and it’s usage.

MySQL Table Create, Alter, Drop, Truncate, View

Use the following MySQL statements to create, alter, drop, truncate and view in mysql table; as follows:

• CREATE Table
• DROP Table
• ALTER Table
• TRUNCATE Table
• CREATE MySQL VIEW
• UPDATE MySQL VIEW
• DROP MySQL VIEW

Create MySQL Table

In MySQL tables can be create using CREATE TABLE statement and you can use the following syntax to create a MySQL tables.

Syntax of MySQL CREATE TABLE statement: as follows:

CREATE  TABLE [IF NOT EXISTS] TABLENAME (fieldname dataType [optional parameters]) ENGINE = storage Engine; 

• “CREATE TABLE” is the one responsible for the creation of the table in the database.
• “[IF NOT EXISTS]” is optional and only create the table if no matching table name is found.
• “fieldName” is the name of the field and “data Type” defines the nature of the data to be stored in the field.
• “[optional parameters]” additional information about a field such as ” AUTO_INCREMENT” , NOT NULL etc

Create Table Example

CREATE  TABLE IF NOT EXISTS `MyFlixDB`.`Users` (
  `id` INT  AUTOINCREMENT ,
  `name` VARCHAR(150) NOT NULL ,
  `gender` VARCHAR(6) ,
  `dob` DATE ,
  `address` VARCHAR(255) ,
  `address` VARCHAR(255) ,
  `contact_number` VARCHAR(75) ,
  `email` VARCHAR(255) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

MySQL Drop Tables

In MySQL tables can be delete using DROP TABLE statement and you can use the following syntax to delete a MySQL tables.

Syntax of MySQL DROP TABLE statement; as follows:

DROP TABLE table_name;

OR

DROP [ TEMPORARY ] TABLE [ IF EXISTS ]
table_name1, table_name2, …
[ RESTRICT | CASCADE ];

Example of Drop Single Table

Let’s take a simple DROP TABLE example that shows how to use the DROP TABLE statement to drop single table in MySQL.

DROP TABLE users;

 OR

DROP TABLE IF EXISTS users;

Example Of Drop Multiple Tables

Let’s take a simple DROP TABLE example that shows how to use the DROP TABLE statement to drop multiple tables in MySQL.

DROP TABLE orders, users;

 OR

DROP TABLE IF EXISTS users, orders;

MySQL ALTER Table

In MySQL tables can be ALTER statement is used when you want to change/modify the name of your table or any column of tables. It is also used to add or delete an existing column in a table.

Syntax of MySQL ALTER TABLE statement; as follows:

 ALTER TABLE tablename  
 ADD newcolumn_name column_definition  
 [ FIRST | AFTER column_name ]; 

• tablename: – This specifies the name of the table that you want to modify.
• newcolumn_name: – This specifies the name of the new column that you want to add to the table.
• column_definition: – This specifies the data type and definition of columns (NULL or NOT NULL, etc.).
• FIRST AFTER column_name: – This is optional. It tells MySQL where to make the column in the table. If this parameter is not specified, then the new column will be added at the end of the table.

Example Of Alter Table

ALTER TABLE Users
DROP COLUMN Email; 

MySQL TRUNCATE Table

In MySQL TRUNCATE TABLE statement is used to remove all records from a table in MySQL without removing its structure.

Syntax of MySQL TRUNCATE TABLE statement; as follows:

TRUNCATE TABLE tablename; 

• tablename :- The table that you wish to truncate.

Example Of TRUNCATE Table

Let’s look at an example of how to use the TRUNCATE TABLE statement in MySQL.

TRUNCATE TABLE users;

Create MySQL Table VIEW

VIEWS are virtual tables. But by virtual we mean that the tables do not store any of their own data but display data stored in other tables. The view is a virtual table that is created by adding one or more tables by the query.

Syntax of CREATE MySQL VIEW TABLE statement; as follows:

 CREATE [OR REPLACE] VIEW view_name AS  
 SELECT columns  
 FROM tables  
 [WHERE conditions];  

• Or REPLACE : – This is optional. It is used when any VIEW is already present. If you do not specify this clause and VIEW already exists, then there will be an error in the CREATE VIEW statement.
• View_name : – This specifies the name of the VView that you want to create in MySQL.
• Where are the conditions : – It is also optional. This specifies the conditions that must be completed for the VIEW to be recorded for the record.

Example of CREATE VIEW Table

CREATE VIEW user_address AS  
SELECT address, address1   
FROM users; 

MySQL Update VIEW

In MYSQL, the ALTER VIEW statement is used to modify or update the already created VIEW without dropping it.

Syntax of MySQL UPDATE VIEW TABLE statement; as follows:

ALTER VIEW view_name AS  
SELECT columns  
FROM table  
WHERE conditions; 

Example of Update VIEW Table

ALTER VIEW user_address AS  
SELECT address, address1, user_id  
FROM users;  

Drop MySQL TABLE VIEW

In MySQL, You can drop the VIEW by using the DROP VIEW statement.

Syntax of MySQL DROP VIEW TABLE statement; as follows:

DROP VIEW [IF EXISTS] view_name;  

null
• View_name : – This specifies the name of the VIEW that you want to drop.
• IF EXISTS : – This is optional. If you do not specify this clause and VIEW is not present, then the DROP VIEW statement will give an error.

Example of DROP VIEW

DROP VIEW user_address;  

Recommended MySQL Tutorials