SQL commands are used to define the database schema in the DATA Definition language.
DDL is a set of SQL Commands used to CREATE MODIFY(UPDATE) and DELETE database objects in a database. Data is not included in DDL commands.
Noramally, a general user would not use these commands, they use an application to access the database.
DDL commands List
- CREATE: CREATE command is used to create the database or its objects e.g. TABLE, INDEX, FUNCTION, VIEWS etc.
- DROP: DROP command is used to delete the objects from the database.
- ALTER: ALTER is used to alter the structure of the database.
- TRUNCATE: TRUNCATE is used to remove all records from a table, including all spaces allocated for the records are removed.
- RENAME: RENAME is used to rename an object existing in the database.
CREATE COMMAND IN SQL
Syntax of CREATE COMMAND
CREATE DATABASE database_name;
Let's suppose you want to create a new database in the sql database. Like database name (STUDENT_INFO). To CREATE database using below command.
CREATE DATABASE STUDENT_INFO;
Now Create the object in above created database "STUDENT_INFO".
Syntax to create a new table
CREATE TABLE table_name( column_name data_type, column_name2 data_type .... column_nameN data_type );
Let's create a student table which have to store some information of "STUDENT". To create the student table the sql command is below.
CREATE TABLE student ( student_roll_no int, first_name varchar(30), last_name varchar(30), student_age int, student_address varchar(200) );
DROP COMMAND IN SQL
Syntax to remove the database
DROP DATABASE database_name;
Le'ts suppose you want to delete the "STUDENT_INFO" from the SQL database. Use below sql command.
DROP DATABASE STUDENT_INFO;
Syntax to remove table (objects) of the database
DROP TABLE table_name;
Let's suppose you want to delete the student table from from the database. You can use below command.
DROP TABLE student;
ALTER COMMAND IN SQL
Syntax to add new field in the table
ALTER TABLE table_name ADD column_name column_definition;
Let's suppose, you want o add the new column of 'student_course' in existing STUDENT table. use below SQL command.
ALTER TABLE student ADD student_course varchar(20);
Syntax to remove a column from the table
ALTER TABLE table_name DROP column_name;
Let's Suppose you want to remove the 'student_address' from the "STUDENT" table. you can do this in SQL command.
ALTER TABLE student DROP student_address;
Syntax to modify the column of the table
ALTER TABLE table_name MODIFY (column_name column_datatype(size));
Let's suppose you want to change the size of the 'student_address' of the 'STUDENT" table. Use below SQL command.
ALTER TABLE student MODIFY (student_address varchar(300));
TRUNCATE COMMAND IN SQL
Syntax of TRUNCATE command ( DELETE all records from the table)
TRUNCATE TABLE table_name;
Let's suppose you want to delete all records from the 'STUDENT' table. Use below SQL command.
TRUNCATE TABLE student;
RENAME COMMAND IN SQL
Syntax to RENAME command
RENAME old_name_of_table TO new_name_of_table;
Let's suppose you want to change the "STUDENT" table name into "STUDENT_ALL_DETAILS" then use below command.
RENAME student TO student_all_details;
DML is stands for DATA Manipulation Language. Using DML Commands, we can easily access, store, modify, update and delete the existing records in the SQL database.