Manipulating Data

1. Manipulating Data

2. Objectives • At the end of this lesson, you should be able to: • Describe each DML statement • Insert rows into a table • Update rows in a table • Delete rows from a table • Control transactions

3. Data Manipulation Language • A DML statement is executed when you: • Add new rows to a table • Modify existing rows in a table • Remove existing rows from a table • A transactionconsists of a collection of DML statements that form a logical unit of work.

4. 50Development Detroit New row “…insert a new row into DEPT table…” 50Development Detroit Adding a New Row to a Table Department dept_nbrdept_name location 10 Accounting New York 20 Research Dallas 30 SalesChicago 40 Operations Boston Department dept_nbrdept_name location 10 Accounting New York 20 Research Dallas 30 Sales Chicago 40 Operations Boston

5. The INSERT Statement • Add new rows to a table by using the INSERT statement. • Only one row is inserted at a time with this syntax. INSERT INTOtable [(column [, column...])] VALUES (value [, value...]);

6. Inserting New Rows • Insert a new row containing values for each column. • Optionally list the columns in the INSERT clause. • List values in the default order of the columns in the table. • Enclose character and date values within single quotation marks. MySQL>INSERT INTO deptartment(dept_nbr, dept_name, location) 2 VALUES (50, 'Development', 'Detroit'); 1 row created.

7. Inserting Rows with Null Values • Implicit method: Omit the column from the column list. • Explicit method: Specify the NULL keyword. MySQL>INSERT INTO department (dept_nbr, dept_name) 2 VALUES (60, 'MIS'); 1 row created. MySQL>INSERT INTO deptartment 2 VALUES (70, 'FINANCE', NULL); 1 row created.

8. Inserting Special Values • TheCURDATEfunction records the current date and time. MySQL> INSERT INTO employee (employee_nbr, name, job, 2 manager, hire_date, salary, 3 commission, dept_nbr) 4 VALUES (7196, 'Green', 'Salesman', 5 7782,CURDATE(), 2000, NULL, 6 10); 1 row created.

9. Inserting Multiple Rows • Use multiple INSERT statements. • Submit all at once with each statement terminated by a semicolon. MySQL>INSERT INTO department (dept_nbr, dept_name, 2 location) 3 VALUES(50, ‘Development’, ‘Detroit’); INSERT INTO department (dept_nbr, dept_name, 2 location) 3 VALUES (60, ‘Huntsville’, ‘MIS’);

10. Inserting Multiple Rows - Alternative • As long as column names are specified or all data is in the proper order you may OMIT the additional INSERT value clauses. • Multiple sets of values require only a single VALUE clause • Each set enclosed in parentheses separated by commas. MySQL>INSERT INTO department (dept_nbr, dept_name, 2 location) 3 VALUES(50, ‘Development’, ‘Detroit’), 4 VALUES (60, ‘Huntsville’, ‘MIS’);

11. “…delete a row from DEPT table…” department dept_nbrdept_name location 10 Accounting New York 20 Research Dallas 30 SalesChicago 40 Operations Boston 60 MIS ... Removing a Row from a Table department dept_nbrdept_name location 10 Accounting New York 20 Research Dallas 30 SalesChicago 40 Operations Boston 50DevelopmentDetroit 60 MIS ...

12. The DELETE Statement • You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHERE condition];

13. Deleting Rows from a Table • Specific row or rows are deleted when you specify the WHERE clause. • All rows in the table are deleted if you omit the WHERE clause. MySQL>DELETE FROM department 2 WHERE dept_name= 'Development'; 1 row deleted. MySQL> DELETE FROM department; 4 rows deleted.

14. Deleting Rows: Integrity Constraint Error You cannot delete a row that contains a primary key that is used as a foreign key in another table. MySQL>DELETE FROM department 2 WHEREdept_nbr= 10; ERROR 1451: Cannot delete or update a pa……… • Row: a foreign key constraint foils

15. Database Transactions • May contain one of the following statements: • DML statements (INSERT, UPDATE, DELETE) that make up one consistent change to the data • One DDL statement (CREATE, ALTER, DROP) • One DCL statement (GRANT, REVOKE)

16. Explicit Transaction Processing • Begin with a START TRANSACTION statement • End with one of the following events: • COMMIT or ROLLBACK • DDL or DCL statement executes (automatic commit) • Certain errors, exit, or system crash

17. Advantages of COMMIT & ROLLBACK • Ensure data consistency • Preview data changes before making changes permanent • Group logically related operations

18. INSERT UPDATE INSERT INSERT DELETE DELETE ROLLBACK to Savepoint B ROLLBACK to Savepoint A ROLLBACK Controlling Transactions Transaction INSERT UPDATE Savepoint A Savepoint B COMMIT

19. State of the Data BeforeCOMMIT or ROLLBACK • The previous state of the data can be recovered because the database buffer is affected. • The current user can review the results of the DML operations by using the SELECT statement. • Other users cannot view the results of the DML statements by the current user. • The affected rows are locked; other users cannot change the data within the affected rows.

20. State of the Data After COMMIT • Data changes are made permanent in the database. • The previous state of the data is permanently lost. • All users can view the results. • Locks on the affected rows are released; those rows are available for other users to manipulate. • All savepoints are erased.

21. Implicit Transaction Processing • Autocommit is the default transaction processing mode in MySQL • An automatic commit (database update) occurs under the following circumstances: • A DML statement is completed • A DDL statement is issued • A DCL statement is issued • An automatic rollback occurs under an abnormal termination ofMySQL or system failure

22. Change Default Autocommit • The default setting may be changed by issuing the following statement: • SET autocommit = 0; • 0 = false • 1 = true • Setting the autocommit = 0 instructions the MySQL to not automatically commit changes, unless the flag is set back to true. • The autocommit flag is per connection, and NOT server-wide

23. Committing Data • Make the changes. • Commit the changes. MySQL>UPDATEemployee 2 SET dept_nbr= 10 3 WHEREemployee_nbr= 7782; 1 row updated. MySQL> COMMIT; Commit complete.

24. State of the Data After ROLLBACK • Discard all pending changes by using the ROLLBACK statement. • Data changes are undone. • Previous state of the data is restored. • Locks on the affected rows are released. MySQL> DELETE FROM employee; 14 rows deleted. MySQL> ROLLBACK; Rollback complete.

25. Rolling Back Changes to a Marker • Create a marker within a current transaction by using the SAVEPOINT statement. • Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement. MySQL> UPDATE... MySQL> SAVEPOINT update_done; Savepoint created. MySQL> INSERT... MySQL> ROLLBACK TO update_done; Rollback complete.

26. Summary