What is the Oracle query table statement?

Oracle is an enterprise-level relational database management system, and its query table statements are the basis for database operations. When using Oracle database, query table statements can help us obtain the required data information. Therefore, learning to use Oracle query table statements is very important for database developers and data analysts.

This article will introduce Oracle query table statements from aspects such as Oracle table creation, data addition, query selection, data modification, data deletion, and permission control.

1. Creation of Oracle tables

Before creating a table in Oracle, you first need to create a database. Can be created with the following command:

CREATE DATABASE databasename;

Then, use the following command to create the table:

CREATE TABLE tablename (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

where "tablename" is the name of the table you intend to create, and "column1" to "column3" are the tables Columns in , and the data type can be specified, such as VARCHAR2(30), NUMBER(10,2), etc.

For example, the following command will create a table named "person" with four columns: "name", "age", "gender", and "address":

CREATE TABLE person (
name VARCHAR2(50),
age NUMBER,
gender VARCHAR2(10),
address VARCHAR2(200)
);

2. Data addition

After creating the table, you can add data to it. The syntax for inserting data using Oracle query table statements is as follows:

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

For example:

INSERT INTO person (name, age, gender, address)
VALUES ('John Smith', 25, 'Male', '123 Main Street');

In this way, a row of data is added to the "person" table.

3. Query selection

Query is one of the operations we often need to perform. Using query statements can help us quickly find the data we need. The format of the Oracle query table statement is as follows:

SELECT column1, column2, column3, ...
FROM table_name
WHERE conditions;

Among them, "column1" to "column3" is the name of the column from the table to be selected, and "table_name" is the name of the table from which rows are selected. The "conditions" are the conditions for retrieving the required rows, such as "age>20", "name LIKE 'J%'", etc.

For example, the following command will retrieve the names and addresses of all people older than 20 years old in the "person" table:

SELECT name, address
FROM person
WHERE age > ; 20;

4. Data modification

Use the following syntax to modify the data in the table using Oracle query table statements:

UPDATE table_name
SET column1 = value, column2 = value, ...
WHERE conditions;

Among them, "table_name" is the name of the table to be updated, and the column name and corresponding new value use "column1=value, column2= value" means. The "conditions" are the conditions that determine which data rows are to be updated.

For example, the following command will change the address of a person named "John Smith" in the "person" table:

UPDATE person
SET address = '456 Main Street'
WHERE name = 'John Smith';

5. Data deletion

Use the following syntax to delete data in the table using the Oracle query table statement:

DELETE FROM table_name
WHERE conditions;

Where "table_name" is the name of the table from which rows are to be deleted. And "conditions" specifies the conditions for the required rows.

For example, the following command will delete all people younger than 18 years old from the "person" table:

DELETE FROM person
WHERE age < 18;

6. Permission control

In Oracle, you can use Oracle query table statements to grant or deny access to specific tables for users. You can use the following statement to authorize the user:

GRANT privilege_name ON object_name TO {user_name |PUBLIC};

where "privilege_name" is the granted permission (such as SELECT, INSERT, UPDATE, etc.), "object_name" is the name of the table to be authorized, "user_name" is the name of the user to whom permissions are to be granted, and "PUBLIC" grants access permissions to all users.

For example, the following command will grant permissions to user "Tom" to SELECT and INSERT table "person":

GRANT SELECT, INSERT ON person TO Tom;

Finally, you can Use the following command to revoke access from a user:

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC};

For example, the following command will revoke access to table "person" from user "Tom" SELECT and INSERT permissions:

REVOKE SELECT, INSERT ON person FROM Tom;

Summary

This article briefly introduces the basic concepts and operations of database operations using Oracle query table statements step. Whether you are engaged in database development or data analysis, mastering these basic operations is a must. You need to pay attention to selecting the correct table when using query statements, determining the data rows to be selected, updated or deleted, and clarifying the required conditions and given constraints.

The above is the detailed content of What is the Oracle query table statement?. For more information, please follow other related articles on the PHP Chinese website!