Scribd Logo
Upload

Welcome to Scribd!

  • 2 views

    DDL COMMANDS

    Uploaded by

    max12342732k

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    DDL COMMANDS

    Uploaded by

    max12342732k
    2 views2 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    2 views2 pages

    DDL COMMANDS

    Uploaded by

    max12342732k

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 2

    DDL COMMANDS

    DDL commands in MySQL are:


    1. Create Table
    2. Alter Table
    3. Drop Table
    CREATE TABLE COMMAND:
    The CREATE TABLE statement is used to create a table in a database.

    CREATE TABLE <table_name>( <column_name1><data_type> [(size)] [constraint],


    <column_name2><data_type> [(size)] [constraint],
    <column_name3><data_type> [(size)] [constraint],...);
    For ex- create table student (Roll INT Primary Key,
    Name CHAR(20),
    Age INT(2),
    City CHAR(10) ) ;
    Primary key restrict a column to have unique values only.

    DATA INTEGRITY BY CONSTRAINTS

    The checks or conditions applied on one or more columns of a table are known as constraints .

     Unique Constraint : This constraint ensure that all the data of the column should be unique. It
    allows null values.
     Primary key Constraint : This constraints is used to uniquely identify data. It does not accept null
    values.
     Default Constraint : This constraint is used to set a default values in case no value is provided by
    user for a column.
     Check Constraint: This constraint is used to set a limit for a column. for ex- no data should be
    inserted less than 20 in age column.
     Foreign key Constraint : it is a non –key attribute of one table derived from primary key from other
    table.
     Not Null : Ensures that a column cannot have NULL value.

    For Ex:
    mysql> create table student (
    Roll INT(4) PRIMARY KEY,
    Name CHAR(20) NOT NULL,
    Age INT(2) CHECK (Age>5),
    Class CHAR(3) Default ‘I’,
    City CHAR(10) Not Null
    );
    Creation of a Table (Table level constraints Setting )

    mysql> create table student (Enroll INT(4), Roll INT(4) ,


    NAME CHAR(20) NOT NULL, Age INT(2),
    Class CHAR(3) Default ‘I’, City CHAR(10), PRIMARY KEY (Enroll, Roll)
    CHECK (Age > = 5) );
    VIEWING TABLE STRUCTURE
    DESC <TableName>; OR DESCRIBE <table_name>;
    For ex - DESC Student;
    ALTER TABLE COMMAND: Modification in Table structure

    The ALTER TABLE command is used to perform the following operations:


     To add a column to an existing table.
     To rename any existing column.
     To change the datatype of any column or to modify its size.
     To remove or physically delete a column.

    Adding a column to an existing table:


    For Ex : Alter Table Student Add Mobile INT;

    Adding a column with default value:


    For Ex: ALTER TABLE student ADD(City char(6) DEFAULT “DELHI”);
    Modifying an existing column definition:
    The MODIFY clause can be used with ALTER TABLE command to change the datatype, size, constraint
    related to any column of the table.
    For Ex: ALTER TABLE student MODIFY (Name varchar(25));
    The above command will modify the datatype size for the Name field from 20 to 25 characters.

    Renaming a column:
    ALTER TABLE <table_name> CHANGE [COLUMN] <old-column-name> <new-column-name>
    column_definition;

    For ex : ALTER TABLE student CHANGE Name FullName varchar(30);

    Removing a column:
    ALTER TABLE <table-name> DROP <column-name>;
    For example,
    ALTER TABLE student DROP Mobile;

    DROP TABLE Command


    DROP TABLE command is used to remove/delete a table permanently.
    DROP TABLE <table-name>;
    For example : DROP TABLE student;

    You might also like