MySQL (CBSE Class XI & XII IP)

How to Create Database – 

Command:

  CREATE DATABASE database-name;

Example:

  CREATE DATABASE school;

 

Accessing (USE) Database – 

Command:

        USE database-name;

Example:

        USE school;

Drop (delete) Database – 

Command:

 DROP DATABASE database-name;

Example:

 DROP DATABASE school;

 

Create Table in Database – 

Command:

  CREATE TABLE table-name

   (

        column-name1 datatype(size),

        column-name2 datatype(size),

        column-name3 datatype(size),

        column-name4 datatype(size)

     );

Example:

   CREATE TABLE Students

   (

         Id integer,

         Name char(30),

         DOB date,

         School_Name varchar(40)

   ); 

Drop (delete) Table– using for delete table from database.  

Command:

    DROP TABLE table-name;

Example:

     DROP TABLE students;

 

Discribe Table – using for show the created table structure.

Command:

       DESC table-name; 

       DESCRIBE table-name;

 

Example:

        DESCRIBE students;

 

Inserting Data into the table – 

Command(1): insert data using with column name. In this command you can insert values in every column as well as some columns. Note that the data values are in the same order as the column name order. 

 

  INSERT INTO table-name 

  (column-name1, column-name2, column-name4) 

  VALUES(value1, value2, value4);

 

Example1:(insert single record)

   INSERT INTO Students

   (ID, Name, DOB, School_Name) 

    VALUES(1001, ‘Bobby’,  ‘1987-01-27’,   ‘KV Harda’ ); 

 

Example2:(insert multiple records using single query)

   INSERT INTO Students

    (ID, Name,  School_Name) 

    (1001, ‘Bobby’,  ‘KV Harda’ ), 

    (1002, ‘Sunny’,  ‘SSSDJHS School’ ), 

    (1003, ‘Sandeep’, ‘KV Harda’ ), 

    (1004, ‘Ajay’, ‘Govt HS School’ ), 

    (1005, ‘KK’, ‘KV Bhopal’ )  ; 

Command(2): insert data using without column name – Note that the data values are in the same order as the column name in the table.  

    INSERT INTO table-name 

    VALUES(value1, value2, value3, value4);

 

Example1:(insert single record)

   INSERT INTO Students

   VALUES(1001, ‘Bobby’,  ‘1987-01-27’,   ‘KV Harda’ ); 

 

Example2:(insert multiple records using single query)

  INSERT INTO Students

  VALUES(1001, ‘Bobby’,  ‘1987-01-27’,   ‘KV Harda’ ), 

   (1002, ‘Sunny’,  ‘1986-04-11’,   ‘SSSDJHS School’ ), 

   (1003, ‘Sandeep’,  ‘1987-01-27’,   ‘KV Harda’ ), 

   (1004, ‘Ajay’,  ‘1987-01-27’,   ‘Govt HS School’ ), 

   (1005, ‘KK’,  ‘1987-01-27’,   ‘KV Bhopal’ )  ; 

Inserting NULL values – 

To insert null value in a specific column you can use NULL keyword.

Example1:(insert single record)

  INSERT INTO Students

  (ID, Name, DOB, School_Name) 

   VALUES(1001, ‘Bobby’,  NULL,   ‘KV Harda’ );