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.
In MySQL CREATE TABLE statement syntax is :-
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.
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.
In MySQL ALTER TABLE statement syntax is :-
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.
In MySQL TRUNCATE TABLE statement syntax is :-
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.
In CREATE MySQL VIEW TABLE statement syntax is :-
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.
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.
In MySQL DROP VIEW TABLE statement syntax is :-
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;