05_01_-处理数据探究.ppt

Oracle Database 11g: SQL Fundamentals I 9 - * Oracle Database 11g: SQL Fundamentals I 9 - * Removing a Row from a Table The Contracting department has been removed from the DEPARTMENTS table (assuming no constraints on the DEPARTMENTS table are violated), as shown by the graphic in the slide. Oracle Database 11g: SQL Fundamentals I 9 - * DELETE Statement Syntax You can remove existing rows from a table by using the DELETE statement. In the syntax: table is the name of the table condition identifies the rows to be deleted, and is composed of column names, expressions, constants, subqueries, and comparison operators Note: If no rows are deleted, the message “0 rows deleted” is returned (in the Script Output tab in SQL Developer) For more information, see the section on “DELETE” in Oracle Database SQL Language Reference11g, Release 1 (11.1). Oracle Database 11g: SQL Fundamentals I 9 - * Deleting Rows from a Table You can delete specific rows by specifying the WHERE clause in the DELETE statement. The first example in the slide deletes the Accounting department from the DEPARTMENTS table. You can confirm the delete operation by displaying the deleted rows using the SELECT statement. SELECT * FROM departments WHERE department_name = ‘Finance; However, if you omit the WHERE clause, all rows in the table are deleted. The second example in the slide deletes all rows from the COPY_EMP table, because no WHERE clause was specified. Example: Remove rows identified in the WHERE clause. DELETE FROM employees WHERE employee_id = 114; DELETE FROM departments WHERE department_id IN (30, 40); Oracle Database 11g: SQL Fundamentals I 9 - * Deleting Rows Based on Another Table You can use the subqueries to delete rows from a table based on values from another table. The example in the slide deletes all employees in a department where the department name contains the string Admin. The subquery searches the DEPARTMENTS table to find the departme