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 (Data Manipulation Language) IN SQL
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.