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