Provide syntax with an example for the followings:
1. Insert a single complete row
2. Insert a single partial row
3. Insert multiple rows
4. Insert the result of a query
1. Insert a single complete rowINSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)
Example:Insert into employee (emp_id, firstname, lastname) values(‘1’,’John’,’player’)
2. Insert a single partial rowExample:Insert into employee(emp_id, firstname, lastname) values (‘2’, NULL, ‘perry’);
3. Insert multiple rowsExample:Insert into employee(emp_id, firstname, lastname) values (‘2’, ‘john’ ‘perry’), (‘3’, ‘mac’,’welt’);
4. Insert the result of a queryExample:INSERT INTO new_employee
(
emp_id,
emp_name,
emp_salary
)
SELECT emp_id,
emp_name,
emp_salary
FROM old_employee;
State syntax of Inserting data into tables with an example.
Insert : Insert statement is used to insert data (row) in a table.
Syntax:Insert into table_name
Values (value1, 2 ..)
Example:Insert into customer values (1,’steve’,’james’);
Explain the DELAYED option for the INSERT statement with an example.
The data to be inserted in a table can be delayed until is free from reads.
Syntax:INSERT DELAYED INTO table (col) VALUES ('val');
The above statement when executed will store the data in memory queue until the table is free from being read. Its most useful when the client cannot wait for the insert to be completed. INSERT DEALYED is not supported for portioned tables or views.
Explain the ON DUPLICATE KEY UPDATE option for the INSERT statement with an example.
If the INSERT statement has ON DUPLICATE KEY UPDATE specified; it will cause a duplicate value on the primary key and an update on the old row.
Example:INSERT INTO table (x,y,z) VALUES (1,2,3) ON DUPLICATE KEY UPDATE z=z+1;
Here, one row is affected if the row is inserted as a new record and 2 rows are affected if the row inserted is updating existing record.
The above statement can be interpreted asUPDATE table SET z=z+1 where a=1;
