oracle 11g delete

Oracle 11g database deletion operation

Oracle 11g is a popular relational database management system (RDBMS) that is widely used in the development and deployment of enterprise-level applications. In the process of using the Oracle 11g database management system, deletion operations are often required to clean up unnecessary data.

The purpose of the delete operation is to delete the specified data from the database to free up storage space and speed up database query and maintenance. Therefore, achieving effective deletion operations is crucial to improving database performance and operating efficiency.

This article will introduce how to perform delete operations in Oracle 11g database.

Syntax for deleting data

The deletion operation is implemented by the DELETE statement in SQL. In the Oracle 11g database, the syntax of the delete operation is as follows:

DELETE FROM table_name
[WHERE condition];

Among them,

table_name: indicates the data to be deleted Table name.

WHERE condition: Specify the conditions for deleting data.

If the WHERE condition is not specified, this delete operation will delete all data in the table, which may result in irreversible data loss. Therefore, please be sure to carefully review and confirm the conditions and scope of deleting data before performing a deletion operation.

Example of delete operation

To demonstrate the delete operation in Oracle 11g database, we will use a simple sample table containing the following four fields:

• ID: User ID, unique identifier.
• Name: User name, VARCHAR2 type.
• Age: User age, NUMBER type.
• Gender: User gender, VARCHAR2 type.

The following is the creation statement of the sample table:

CREATE TABLE users(

id number(10) PRIMARY KEY,
name varchar2(50),
age number(3),
gender varchar2(10)

);

After creating the table, we will insert Some sample data for use in delete operations. The following is the insert statement for sample data:

INSERT INTO users VALUES(1, 'John', 28, 'Male');
INSERT INTO users VALUES(2, 'Alice', 25, 'Female ');
INSERT INTO users VALUES(3, 'Bob', 35, 'Male');
INSERT INTO users VALUES(4, 'Sarah', 31, 'Female');

Now, we will explain how to perform delete operations in Oracle 11g database by demonstrating different scenarios.

Scenario 1: Delete all data

In this case, you need to delete all data in the table. To do this, use the following statement:

DELETE FROM users;

Note that the WHERE condition is not specified, so this operation will delete all data in the table. Before performing this operation, make sure that all data needs to be deleted as this operation is irreversible.

Scenario 2: Delete specified rows

If you only need to delete specific rows in the table, you can use the following statement:

DELETE FROM users WHERE id=2;

This operation will delete the row with ID 2 from the table, which is Alice's data.

Scenario 3: Deleting multiple rows

If you need to delete multiple rows, you can use the IN keyword to specify a list of IDs, as shown below:

DELETE FROM users WHERE id IN(2, 4);

This will delete the two rows of data Alice and Sarah.

Scenario 4: Delete a specific range

If you want to delete data in a specific range based on specific conditions, you can use the BETWEEN and AND keywords as follows:

DELETE FROM users WHERE age BETWEEN 30 AND 35;

This will delete user data whose age is between 30 and 35 years old, that is, the two rows of data for Bob and Shan.

Summary

In this article, we introduced the delete operation in Oracle 11g database and demonstrated different scenarios to illustrate how to use the DELETE statement to delete data. However, please note that when deleting, you should carefully review and confirm the deletion conditions, scope and data before performing the corresponding operation.

The above is the detailed content of oracle 11g delete. For more information, please follow other related articles on the PHP Chinese website!