- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Data Definition Commands in DBMS
Data definition commands are used to create, modify and remove database objects such as schemas, tables, views, indexes etc.
Common Data Definition commands −
Create
The main use of create command is to create a new table in database. It has a predefined syntax in which we specify the columns and their respective data types.
syntax
CREATE TABLE <TABLE NAME> ( <COLUMN NAME> <DATA TYPE>, <COLUMN NAME> <DATA TYPE>, <COLUMN NAME> <DATA TYPE>, <COLUMN NAME> <DATA TYPE> );
Example
Create a student table with columns student name and roll number.
CREATE TABLE STUDENT (STUDENT_NAME VARCHAR(30), ROLL_NUMBER INT );
Alter
An existing database object can be modified using the alter command. Alter command can do following changes to any table-
Add new columns.
Add new integrity constraints.
Modify existing columns.
Drop integrity constraints.
Syntax
General Syntax of the ALTER command is mentioned below −
For adding a new column
ALTER TABLE <table_name> ADD <column_name>
For renaming a table
ALTER TABLE <table_name> RENAME To <new_table_name >
For modifying a column
ALTER TABLE <table_name> MODIFY <column_name > <data type >
For deleting a column
ALTER TABLE <table_name> DROP COLUMN <column_name>
Drop
This command can delete an index, table or view. Basically, any component from a relational database management system can be removed using the Drop command. Once the object is dropped, it cannot be reused.
The general syntax of drop command is as follows −
DROP TABLE <table_name>; DROP DATABASE <database_name>; DROP TABLE <index_name>;
Truncate
Using the truncate command, all the records in a database are deleted, but the database structure is maintained.
syntax
TRUNCATE TABLE <table name>
Comment
This command is used to add comments to the data dictionary.
syntax
- Single line comments: use ‘ --‘ before any text.
- Multiline comments: /* comments in between */
Rename
The rename command renames an object
Syntax
Rename <old name> to <new name>