ddsingh@infomatics.info +91 9760014754

TABLE

MySQL CREATE TABLE:-

    The MySQL CREATE TABLE command is used to create a new table into the database. A table creation command requires three things:-

 

Name of the table

Names of fields

Definitions for each field

Syntax:

 

CREATE TABLE table_name (column_name column_type...);   

 

Example:

  Here, we will create a table named “Emp" in the database “College".

 

CREATE TABLE Emp(  

   EmpId INT NOT NULL AUTO_INCREMENT,  

   EmpName VARCHAR(100) NOT NULL,  

   EmpJob VARCHAR(100) NOT NULL,  

   Salary INT,

   PRIMARY KEY (EmpId )  

);  

See the created table:

 

 

SHOW tables;   

 

See the table structure:

Use the following command to see the table already created:

DESCRIBE Emp;  

MySQL ALTER Table:-

    MySQL ALTER statement is used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.

 

    The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the situation.

1) ADD a column in the table

Syntax:

 

ALTER TABLE table_name  

ADD new_column_name column_definition  

FIRST | AFTER column_name ];  

 

Parameters

table_name: It specifies the name of the table that you want to modify.

new_column_name: It specifies the name of the new column that you want to add to the table.

column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example:

In this example, we add a new column “Age" in the existing table “Emp".

Use the following query to do this:

 

ALTER TABLE Emp  

ADD Age int NOT NULL;  

 

MODIFY column in the table:-

    The MODIFY command is used to change the column definition of the table.

Syntax:

ALTER TABLE table_name  

MODIFY column_name column_definition  

FIRST | AFTER column_name ];  

Example:

     In this example, we modify the column EmpJob to be a data type of varchar(50) and force the column to allow NULL values.

Use the following query to do this:

ALTER TABLE Emp  

MODIFY EmpJob varchar(50) NULL;  

DROP column in table

Syntax:

ALTER TABLE table_name  

DROP COLUMN column_name;  

 

ALTER TABLE Emp

DROP COLUMN Age;  

RENAME table

Syntax:

ALTER TABLE table_name  

RENAME TO new_table_name;  

Example:-

 

In this example, the table name Emp is renamed as Employee.

ALTER TABLE Emp

RENAME TO Employee;

MySQL TRUNCATE Table:-

 

  MYSQL TRUNCATE statement removes the complete data without removing its structure.

 

 The TRUNCATE TABLE statement is used when you want to delete the complete data from a table without removing the table structure.

Syntax:-

TRUNCATE TABLE  table_name;  

Example:

TRUNCATE TABLE  Emp;  

MySQL DROP Table

     MYSQL DROP table statement removes the complete data with structure

 

Syntax:

DROP TABLE  table_name;  

 

Ex:-

DROP TABLE  Emp;  

MySQL TRUNCATE Table vs DROP Table:-

 

    You can also use DROP TABLE command to delete complete table but it will remove complete table data and structure both. You need to re-create the table again if you have to store some data. But in the case of TRUNCATE TABLE, it removes only table data not structure. You don't need to re-create the table again because the table structure already exists.

MySQL View:-

    In MySQL, View is a virtual table created by a query by joining one or more tables.

 

MySQL Create VIEW:-

    A VIEW is created by SELECT statements. SELECT statements are used to take data from the source table to make a VIEW.

Syntax:-

 

CREATE [OR REPLACE] VIEW view_name AS  

SELECT columns  

FROM tables  

[WHERE conditions];  

 

Ex:-

CREATE VIEW trainer AS  

SELECT EmpName, EmpJob   

 FROM Emp;  

To see the created VIEW:

Syntax:

SELECT * FROM view_name;  

Let's see how it looks the created VIEW:

SELECT * FROM trainer;  

MySQL Drop VIEW:-

You can drop the VIEW by using the DROP VIEW statement.

Syntax:-

  DROP VIEW [IF EXISTS] view_name;  

Parameters:-

 

   view_name: It specifies the name of the VIEW that you want to drop.

   IF EXISTS: It is optional. If you do not specify this clause and the VIEW doesn't exist, the DROP VIEW statement will return an error.

Example:

DROP VIEW trainer;  

 

 

 

 

 

 


Prev Next