A deep dive into how to query and insert data using Oracle

Oracle is a very popular relational database management system that can be used to manage large amounts of data and provide data query and processing functions. In Oracle, query and insert are one of the most common operations. In this article, we will delve into how to query and insert data using Oracle.

1. Query data

In Oracle, the query statement uses the SELECT keyword. The following are some common query examples:

1. Query all rows and all columns:

SELECT * FROM table_name;

2. Query specified columns ：

SELECT column1,column2,column3 FROM table_name;

3. Use WHERE clause to query specified conditions:

SELECT column1,column2, column3 FROM table_name WHERE condition;

In the above query statement, "column1", "column2", etc. are the column names in the table, "table_name" is the table name, and "condition" is the query condition.

For example, the following query will return information about all employees whose salary is higher than 5000 in the table named "employees":

SELECT * FROM employees WHERE salary>5000;

This query statement will return information about all rows and columns that meet the conditions.

2. Insert data

Inserting data is the operation of adding new data to an existing table. In Oracle, insert statements use the INSERT keyword. The following are some common insertion examples:

1. Insert specified column values:

INSERT INTO table_name (column1,column2,column3) VALUES (value1,value2,value3);

In the above insert statement, "table_name" is the name of the table, "column1", "column2", etc. are the column names in the table, and "value1", "value2", etc. are the values ​​to be inserted.

For example, the following statement will create a row of new employee information in the table named "employees":

INSERT INTO employees (employee_id,last_name,first_name,email,phone_number,hire_date,job_id, salary) VALUES (101,'Smith','John','jsmith@email.com','1234567890',TO_DATE('2019-01-01','yyyy-mm-dd'),'IT_PROG',6000 );

This insert statement will create a row in the table named "employees" specifying the employee ID, name, email address, phone number, date of joining, job ID, and salary value.

2. Insert values ​​of all columns:

INSERT INTO table_name VALUES (value1,value2,value3);

In this insert statement, "table_name " is the name of the table, "value1", "value2", etc. are the values ​​to be inserted.

For example, the following statement will create a row of new employee information in the table named "employees", in which all column names will be unspecified:

INSERT INTO employees VALUES (102,'Jones', 'Samantha','sjones@email.com','0987654321',TO_DATE('2019-02-01','yyyy-mm-dd'),'IT_PROG',8000,NULL,NULL,NULL,NULL);

This insert statement will create a row in the table named "employees" specifying the employee ID, name, email address, phone number, date of joining, job ID, and salary values, also including columns with null values .

3. Insert data after query

In Oracle, you can use the rows and columns selected by the query statement to insert new data. The following is an example of inserting data based on query results:

INSERT INTO table_name (column1,column2,column3) SELECT column4,column5,column6 FROM table_name2 WHERE condition;

In this insert statement, " "table_name" is the name of the table where values ​​are to be inserted, "column1", "column2", etc. are the column names in the table, "table_name2" is the name of the table for querying data, "column4", "column5", etc. are the column names to be queried, "condition" is the query condition.

For example, the following statement will query the department information with ID 10 in the table named "departments" and insert the information into the table named "employees":

INSERT INTO employees (employee_id,last_name,first_name,email,phone_number,hire_date,job_id,salary) SELECT department_id,department_name,NULL,NULL,NULL,NULL,NULL,NULL FROM departments WHERE department_id=10;

This insert statement will Query the department information with ID 10 in the table named "departments" and insert it into the table named "employees". All remaining columns do not specify values.

Summary

In this article, we took a deep dive into how to query and insert data using Oracle. By using the SELECT keyword and WHERE clause, we can query the data of the specified table. By using INSERT keyword and VALUES clause, we can insert new data into the existing table. By using the SELECT keyword and INSERT keyword, we can insert the query result data into another table. For beginners of Oracle database management, these operations are the most basic, but also the most common. We hope this article helps you better understand Oracle's query and insert operations.

The above is the detailed content of A deep dive into how to query and insert data using Oracle. For more information, please follow other related articles on the PHP Chinese website!