SQL DML
Apart from Select, there are other important DML commands which are really handy while manipulating the Tables and Databases.
Title | Explanation |
---|---|
Select | Select command is use to fetch and retrieve the desired records from the table. It is going to be most frequently used command in our course. |
Insert | Insert command is use to insert the new values to an existing tables. |
Update | Update is use to modify and update the existing records. |
Delete | Delete is use to delete a specific record or a complete record. The main difference between delete and truncate is that in delete we can use the where clause to specify the condition. |
INSERT Command
-
We can insert values into our tables using an insert commands.
-
Make sure to insert the correct data types for the columns.
-
Insert numbers without quotes.
Syntax
INSERT INTO
table_name
(column_name_1,
column_name_2,
column_name_3)
VALUES
(value_1,
value_2,
value_3);
-
We can change the order of columns by which we insert the values or skip some columns while inserting the values.
-
WE can directly insert into tables without passing the column names but for that we have to insert record in a proper sequence and can't skip any column.
Syntax
INSERT INTO
table_name
VALUES
(value_1,
value_2,
value_3);
-
We can directly insert data into a new table from another table using the below syntax.
-
We have to make sure that the number of columns and data type of each column in both the tables should be same.
Syntax
INSERT INTO
table_2 (column_1,column_2,column_3)
SELECT
column_1,column_2,column_3
FROM
table_1;
UPDATE Command
-
Update command is use to modify and update the records present in the tables.
-
To use the update and delete commands, first we need to turn off the safe updates.
-
To turn off the safe updates, follow the below steps :
-
Edit >> Preferences >> SQL Editor >> Scroll till the end and unselect Safe Updates.
Syntax
UPDATE table_name
SET
column_1 = new_value,
column_2 = new_value,
column_3 = new_value
WHERE
conditions;
-
While updating it is not mandatory to change all the columns value in the table.
-
Make sure to use COMMIT and ROLLBACK Commands frequently while updating or deleting a record to prevent a loss of data.
-
Using a where clause is optional but it is important to use.
DELETE Command
-
It is use to delete certain records from the table.
Syntax
DELETE FROM table_name
WHERE conditions;
-
Be careful while using a delete statement. because it can be result in lot of loss data.