Sql Commands

commandIntroduction

Sql Commands are used to communicate with the database to perform specific tasks, work, functions and queries with data. SQL commands are grouped into four major categories depending on their functionality:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Data Control Language (DCL)

Data Definition Language (DDL)

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

  • CREATE
    • Create a new Table, Database, Schema,Views..
    • CREATE TABLE table_name(
      column_name1 data_type(size),
      column_name2 data_type(size),
      column_name3 data_type(size),
      ....
      );
  • ALTER
    • Alter existing table, column description
    • ALTER TABLE table_name
      ADD column_name datatype
  • DROP
    • Delete existing objects from database
    • DELETE FROM table_name
      WHERE some_column=some_value;
  • TRUNCATE
    • Delete complete data from an existing table.
    • TRUNCATE TABLE  table_name;
  • RENAME
    • Rename command is used to rename a table
    • rename table old-table-name to new-table-name

Data Manipulation Language (DML)

DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

  • SELECT
    • SELECT statement is used to select data from a database.
    • SELECT column_name,column_name
      FROM table_name;
  • UPDATE
    • UPDATE statement is used to update existing records in a table.
    • UPDATE table_name
      SET column1=value1,column2=value2,...
      WHERE some_column=some_value;
  • INSERT
    • INSERT INTO statement is used to insert new records in a table.
    • INSERT INTO table_name (column1,column2,column3,...)
      VALUES (value1,value2,value3,...);
  • DELETE
    • DELETE statement is used to delete records in a table.
    • DELETE FROM table_name
      WHERE some_column=some_value;
  • MERGE
    • MERGE statement to select rows from one or more sources for update or insertion into a table or view
  • LOCK TABLE
    • LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.
    • LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

Transaction Control Language (TCL)

The commands of SQL that are used to control the transactions made against the database.

  • COMMIT
    • Commit is used for the permanent changes
    • COMMIT;
  • ROLLBACK
    • Rollback is used to undo the changes made by any command but only before a commit is done
    • ROLLBACK;
  • SAVE POINT
    • Creates points within groups of transactions in which to ROLLBACK
    • SAVEPOINT SAVEPOINT_NAME;

Data Control Language (DCL)

The commands of SQL that are used to control the access to data stored in the database.

  • GRANT
    • All users access previleges to database.
    • GRANT privilege_name
      ON object_name
      TO {user_name |PUBLIC |role_name}
      [WITH GRANT OPTION];
  • REVOKE
    • Withdraw users access previleges given by using the Grant command.
    • REVOKE privilege_name
      ON object_name
      FROM {user_name |PUBLIC |role_name}

 

Leave a Reply

Your email address will not be published. Required fields are marked *