MySQL - Working with Tables

 

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 <tablename>(
  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 dropping primary key 

  alter table teachers drop Primary key;

  desc teachers;

Example adding the primary key again to the teachers table;

  alter table teachers add Primary key(Id);

  desc teachers;