Using Basic MySQL commands

Before starting with using basic MySQL commands, you need to connect to your MySQL-server , to know how to connect to mysql-server click here.

Creating DATABASE

Lets create a new database “school”

mysql>  CREATE DATABASE school ;

To check whether database is created, use command:

mysql> SHOW DATABASES ;


Creating TABLES

You need to select DATABASE in which you want to create Table

mysql> USE school ;

To Determine which database is in use :

mysql> SELECT DATABASE() ;


Now create Table with the following table statement :

mysql> CREATE TABLE students
     -> (
     -> student_id INT , #Unique id for record
     -> name VARCHAR(20) , #name of the student
     -> subject VARCHAR(20) , #name of the subjects
     -> PRIMARY KEY(student_id)
     -> );


To check Whether Table is created , use command :

mysql> SHOW TABLES ;

To check all the information about table use :

mysql> DESCRIBE student ;


Adding and Deleting Records into table

  • We will fill in the information into the table which we made above :
mysql> INSERT INTO student (student_id, name, subject) VALUES
     -> ('100','Nupur','Science'),
     -> ('101','Ashish','Maths'),
     -> ('102','Juhi','Hindi'),
     -> ('103','soni','History');


Now use a SELECT statement, and * to match all columns:

mysql> SELECT * FROM student ;


  • To delete a record from the table, for example if we want to delete the record of a student who has left the school .
mysql> DELETE FROM student WHERE name='Soni'; 

Again you can check your result using :

mysql> SELECT * FROM student ;


Modifying TABLES

  • For adding a new column in the table containing Marks after subject:
mysql> ALTER TABLE student ADD Marks INT AFTER subject ;

To check whether column has been added, use :

mysql> DESCRIBE student ;


  • For deleting a column from the table , here we will delete the column “Marks” :
mysql> ALTER TABLE student DROP Marks ;

To check the result use command :

mysql> DESCRIBE student ;

Deleting TABLES

To delete a table use :

mysql> DROP TABLE student ;

To check the result use :

mysql> SHOW TABLES ;

Deleting DATABASES

To delete a DATABASE use :

mysql> DROP DATABASE school ;

To check the result use :

mysql> SHOW DATABASES ;