This is the 2nd sub language in SQL,
which is used to manipulate the data within the database. This Language
contains 4 commands
- Inser
- Update
- Delete
- Select
1}Insert:
Using this command we can Insert the records into the existing tableWe can insert the records into the table in two methods1. Explicit method2. Implicit method
Explicit method:
In this method user has to enter all the values into all the columns without anything omitting (or) left any column dataSy: Insert Into <Table Name> Values <Val1, Val2, ….Valn>;EX: insert into employee values(1,'om')—inserting a record into the Table(Explicit)insert into Employee values(2,'sampath')—inserting one more record(Explicit)
Implicit method:
In this method we can enter the values into the required columns in the table, so that user can omit some columns data while he enters the records into the tableIf the user omit any column data in the table then it automatically takes NULLSy: Insert Into <Table Name> (Col1, Col2….Coln) Values (Val1, Val2,… Valn);Ex: insert into employee(EmpId) values(20)—insert using(Implicit)
adding new
column salary into employee table
alter table Employee add Salary money2}Update:This command is used to modify the data in the existing tableBy using this command we can modify all the records in the table & also specific records in the table (Using ‘where’ clause)Sy: Update <Table Name> Set Col=Value;Ex: update Employee set salary=10000—this Update will make effect on all the records
Syntax changes for more
than one data simultaneously
Sy: Update <Table Name> Set Col1=Value, Col2=Value , …. Coln=Value;Ex: update employee set salary=50000,EmpId=5
3}Delete
- This command is used to delete the records from existing table
- Using this command we can delete all the records and also to delete specific record (by using ‘where’ clause)
Sy: Delete From <Table Name>Ex: delete from Employee –deletes all records from the tablehere again iam inserting the records likeinsert into Employee values(1,'sampath',10000)insert into Employee values(2,'kumar',15000)insert into Employee values(3,'vaddepally',40000)
To Delete a particular column value we use
delete from employee where empid=2 -—deletes only one record with empid
Differences between Truncate & Delete
SNO
|
TRUNCATE
|
DELETE
|
01
|
It is a DDL command
|
It is a DML command
|
02
|
It is a permanent
deletion(Truncate Releases Memory)
|
It is temporary
deletion(Delete will not releases memory)
|
03
|
Specific record
deletion is not possible
|
We can delete the
specific record or Particular Record
|
04
|
In Truncate Restoring
is Not Possible
|
In Delete, Restoring
is possible
|
05
|
Truncate will Reset
Identity
|
Delete Will not Reset
Identity
|
4}Select:
This command is used to retrieve the data from existing table.Using this command we can retrieve all the records & also specific records from existing table (by using ‘where’ clause)Using this command we can retrieve the data from the table in 3 waysProjectionSelectionJoinsSy: SELECT * FROM <TABLE NAME>Ex: select * from Employee –Displaying all records from the Table.* represents all columns
Projection:
Retrieving the data from specific columns is known as ProjectionSy: Select Col1,Col2……..Coln From <Table Name>Ex: selectEmpId,EName from Employee where
Selection:
Retrieving the data based on some condition is called selectionIn SQL, whenever we need to check a condition, we need to use a special clause called ‘where’
Where clause:
This clause is used to check the condition based on the condition, we can retrieve, update, delete specific records in the tableSo we can apply the where clause only in select, update & delete
Select- where clause:
Sy: Select * From <Table Name> Where <Condition>Ex:Select * from Employee Where EmpId=1;
No comments:
Post a Comment