MYSQL COMMANDS SYNTAX AND EXAMPLE
1. To Find Out Which Databases Currently Exist On The Server.
Syntax - Show Databases;
2. Creating Database.
Syntax - Create Database Database-Name;
Example - Create Database Trinity;
3. Opening Database
Syntax - Use Database-Name;
Example - Use Trinity
4. Removing Database
Syntax - Drop Database Database-Name;
Example - Drop Database Trinity
5. Creating A Table
Syntax - Create Table Table-Name ( Column1 Datatype Definition, Column2 Datatype
Definition, Column N Datatype Definition);
Example - Create Table Student ( Admno Int(10) Primary Key, Name Varchar(25) NOT
NULL, Marks Int(5), Practical Int(5));
6. To Find Out Which Tables Currently Exist On The Database.
Syntax - Show Tables;
7. Viewing A Table Structure
Syntax - Describe Table-Name; Or Desc Table-Name;
8. Insert Data Into Table
Syntax - Insert into Table-Name Values(Value1,Value2,Value3……Valuen);
Example - Insert into Student Values(101,’Ravi’,85,26);
Syntax - Insert into Table-Name(Column1, Column2,Column3….Columnn)
Values(Value1,Value2,Value3……Valuen);
Example - Insert into Student (Name, Admno,Marks,Practical)Values(’Ravi’,102,85,26);
Inserting Null Values Into A Table.
Insert Into Student Values(103,’Tina’,88,NULL);
9. Modifying Data In A Table.
Syntax - Update Table-Name Set Column1=Value, Column2=Value, Column3=Value
Where Condition;
Example - Update Student Set Name=’Ravi.K’,Marks=95 Where Admno=101;
[Link] Null To Value
Example - Update Student Set Practical=29 Where Admno=103;
[Link] Using An Expression Or Formula
Example - Update Student Set Marks=Marks+10 Where Admno=101;
[Link] Data From A Table
Syntax - Delete From Table-Name Where Condition;
Example - Delete From Student Where Name=’Tina’; - Particular Record Will Be
Removed From A Table.
Example - Delete From Student; - All Records Will Be Removed.
[Link] Table From A Database.
Syntax - Drop Table Table-Name;
Example - Drop Table Student;
14. Alter Table- Add A Column
Syntax – Alter Table Table-Name Add Column-Name Datatype Definition;
Example – Alter Table Student Add DOB Date;
15. Renaming A Column
Syntax – Alter Table Table-Name Modify Column Old Column-Name New Column-
Name;
Example – Alter Table Student Modify Column DOB Date_Of_Birth;
16. Adding A Column With Default Value.
Syntax – Alter Table Table-Name Add Column-Name Datatype Dafult ‘Value’;
Example – Alter Table Student Add City Varchar(15) Default ‘Delhi’;
17. Modifying An Existing Column Definition.
Syntax – Alter Table Table-Name Modify Column-Name Datatype Definition;
Example – Alter Table Student Modify City Varchar(25);
18. Rename A Column
Syntax – Alter Table Table-Name Rename Old Column-Name To New Column-Name;
Example – Alter Table Student Rename DOB To Date_Of_Birth;
19. Remove A Column
Syntax – Alter Drop Table-Name Drop Column-Name;
Example – Alter Drop Student Drop Date_Of_Bith;
[Link] A View
Syntax – Create View View-Name As Select Column-Name(S) From Table-Name
Where Condition;
Example – Create View Stu As Select Admno,Name, Marks, City From Student;
Create View Stu As Select Admno,Name, Marks, City From Student Where
City=’Chennai’;
[Link] View From Database.
Syntax – Drop View View-Name;
Example – Drop View Stu;