As promised here is a quick guide to some of the most frequently used commands in any SQL environment. You can find a more detailed description in the introduction to relational databases. 

Commands are not case-sensitive - they do not need to be capitalized. It's common practice to use all caps for commands so that dynamic data (table names, inserted values) can be noticed with ease.

• CREATE Command - is used to create a database/table.
  
• SELECT Command - is used to retrieve data from the database.
  
• DELETE Command - is used to delete data from the database.
  
• INSERT Command - is used to insert data into a database.
  
• UPDATE Command - is used to update the data in a table.
  
• DROP Command - is used to delete or drop the database/table.
  

 Syntax for Query Commands

CREATE Command
The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:

mysql> CREATE TABLE tablename;

Example:

mysql> CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0' ;

SELECT Command
The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:

Syntax:

mysql> SELECT field_names FROM tablename;

Example:

mysql> SELECT * FROM tblstudent;

DELETE Command
The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:

mysql> DELETE * FROM tablename WHERE condition;

Example:

mysql> DELETE * FROM tblstudent WHERE fldstudid=2";

INSERT Command
The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:

mysql> INSERT INTO tablename(fieldname1,fieldname2..) VALUES(value1,value2,...) ;

Example:

mysql> INSERT INTO Tblstudent(fldstudName,fldstudmark) VALUES(Baskar,75) ;

UPDATE Command
The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:

mysql> UPDATE Tablename SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber;

Example:

mysql> UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2;

DROP Command
The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:

mysql> DROP tablename;

Example:

mysql> DROP tblstudent;

Labels: command syntax, Databases, Lesson, MySQL, relational databases

This entry was posted on Monday, December 1, 2008 at 2:55 PM.