Thursday, December 20, 2012

SQL Statements Overview – DDL & DML

SQL statements are categorized into two categories: DDL statements & DML statements. DDL is abbreviation for data definition language while DML is abbreviation for data manipulation language. Data definition language statement is used to define the data structure of database, in addition to this; it can modify the structure of the table or other objects like view, stored procedure and many more. Data manipulation language statement is used to retrieve data or work with data in SQL server. Both of these categories contain several statements than I have present in this article.

Data Definition Language (DDL): You can use this statement for create, drop, truncate, and alter data structure in an instance of SQL server database. Some of the popular DDL statements are described below.

Create Statement: This statement is used for creating a new entity; like table, view, database, index, and many more.

CREATE TABLE <table name> ( <attribute name 1> <data type 1>, ...<attribute name n> <data type n>);

Alter Statement: This statement is used for modifying the definition of a an entity; like table, view, database, index, and many more.

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);

Drop Statement: You can use this statement to remove an existing entity; like table, view, database, index, and many more.

DROP TABLE <table name>;

Truncate Statement: This statement removes all the rows from the table. It is similar to the DELETE statement with 'Where' clause.

TRUNCATE TABLE <table name>;

Note: In all above examples; I have taken table as an entity.

Data Manipulation Language (DML): Basically this statement is used to work with data in the SQL server database. Some of the popular DML statements are as follows:

Insert Statement: As the name suggests; it is used to insert one or more rows in the table.

INSERT INTO <table name> VALUES (<value 1>, ... <value n>);

Update statement: You can use this statement to change the data from in a table.

UPDATE <table name> SET <attribute> = <expression> WHERE <condition>;

Delete Statement: As I have discussed above; it is similar to the truncate statement but with 'Where' clause. It removes one or more rows from the table.

DELETE FROM <table name> WHERE <condition>;

No comments:

Post a Comment