MYSQL Table – CREATE, ALTER, DROP, TRUNCATE, VIEW

In this MySQL tutorial point – you will learn about MySQL statements such as Create Table, Alter Table, Drop Table, Truncate Table, Create Views Table, Drop views table & Update views table.

Here, first we will demostrate about creating and deleting mysql tables. We will explain how to create tables using the MySQL statement CREATE table and how to DROP or delete MySQL tables using the MySQL statement DROP table.

Next we will explain how to modify the table column using the MySQL statement ALTER TABLE and how to empty the table data using the MySQL statement TRUNCATE TABLE.

After that, we will describe how to create, update, and drop MySQL view tables using the MySQL statement CREATE VIEW, UPDATE VIEW, DROP VIEW.

Now you will learn how to use the following MySQL statements.

Table Of Content

• 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

In MySQL CREATE TABLE statement syntax is :-

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

Here

• “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

In MySQL DROP TABLE statement syntax is :-

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

In MySQL ALTER TABLE statement syntax is :-

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

Here

null
• 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

In MySQL TRUNCATE TABLE statement syntax is :-

TRUNCATE TABLE tablename; 

Here

• 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

In CREATE MySQL VIEW TABLE statement syntax is :-

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

Here

• 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

In MySQL UPDATE VIEW TABLE statement syntax is :-

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

In MySQL DROP VIEW TABLE statement syntax is :-

DROP VIEW [IF EXISTS] view_name;  

Here

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