MySql- Working with tables

  Working with tables Show all tables in a current database. SHOW TABLES ; Code language: SQL (Structured Query Language) ( sql ) Create ...

 

Working with tables

Show all tables in a current database.

SHOW TABLES;
Code language: SQL (Structured Query Language) (sql)

Create a new table

CREATE TABLE [IF NOT EXISTS] table_name(
  column_list
);
Code language: SQL (Structured Query Language) (sql)

Example: Create a table student.

            create table student(Id int, name varchar(30),marks int);

Example : To see all the fields of student table;

     desc student

Add a new column into a table:

ALTER TABLE table 
ADD [COLUMN] column_name;
Code language: SQL (Structured Query Language) (sql)

Example: To add a new column Grade varchar(2) in student table.

         Alter table student add Grade varchar(2);

         desc student;            

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;
Code language: SQL (Structured Query Language) (sql)

Example: to Delete Grade column

        Alter table student drop Grade ;

        desc student;

Add index with a secific name to a table on a column:

ALTER TABLE table 
ADD INDEX [name](column, ...);
Code language: SQL (Structured Query Language) (sql)

Add primary key into a table:

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name,...);

Example: To create teachers table and add primary key to the Id column

    create table teachers( id int Primary key, name varchar(30));

    desc teachers;

Remove the primary key of a table:

ALTER TABLE table_name
DROP PRIMARY KEY;

Example 

   Alter table teachers drop Primary key;

  desc teachers;

Example Again add the Primary key to the teachers table;

   Alter table teachers Add Primary key(Id);

   desc teachers;

Drop a table:

DROP TABLE [IF EXISTS] table_name;
Code language: SQL (Structured Query Language) (sql)

Example:- Delete teachers table

    Drop table teachers;

Show the columns of a table:

DESCRIBE table_name;
Code language: SQL (Structured Query Language) (sql)

Show the information of a column in a table:

DESCRIBE table_name column_name;