How to create tables and common query operations in Oracle database

Oracle is a relational database management system that is widely used in enterprise-level applications. Oracle's query language is very powerful and can complete a variety of data operations. This article will introduce how to create tables and common query operations in Oracle database.

1. Create a table

To create a table in Oracle, you need to use the CREATE TABLE statement. The syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name
(column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
);

where table_name is the name of the table to be created, and the columns in the brackets are the columns of the table. Each column needs to specify the column name and data type. The NULL and NOT NULL keywords are used to specify the null attribute of a column.

For example, we want to create a student table containing three columns: student number, name and age. The data types of columns are number, string and number respectively. The CREATE TABLE statement is as follows:

CREATE TABLE students (

stu_id NUMBER NOT NULL,
stu_name VARCHAR2(50) NOT NULL,
stu_age NUMBER

);

2. Insert data

After the table is created, you need to insert it into the table data. Inserting data requires the INSERT INTO statement. The syntax of the INSERT INTO statement is as follows:

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

where table_name is the name of the table into which data is to be inserted, and the columns in brackets are the columns to be inserted. Each column needs to specify a column name and value.

For example, we want to insert a piece of student data into the student table created above. The student number is 1, the name is Zhang San, and the age is 20. The INSERT INTO statement is as follows:

INSERT INTO students (stu_id, stu_name, stu_age)
VALUES (1, 'Zhang San', 20);

3. Query data

Querying data is one of the most basic operations of Oracle. Querying data requires the use of the SELECT statement. The syntax of the SELECT statement is as follows:

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

among which column1, column2, column3, etc. are to be queried Columns, separated by commas; table_name is the name of the table to be queried; the WHERE clause is optional and is used to specify query conditions.

For example, if we want to query the data of all students in the student table created above, the SELECT statement is as follows:

SELECT *
FROM students;

If you only need to query The data in the two columns of student name and age can be written like this:

SELECT stu_name, stu_age
FROM students;

If you need to query the data of students aged 20 and below, You can write like this:

SELECT *
FROM students
WHERE stu_age <= 20;

4. Update data

Updating data requires the UPDATE statement . The syntax of the UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

where table_name is the table whose data is to be updated. The name, the SET clause is used to specify the column and new value to be updated, and the WHERE clause is used to specify the conditions for the update.

For example, we want to update the age of the student with student number 1 to 21 years old. The UPDATE statement is as follows:

UPDATE students
SET stu_age = 21
WHERE stu_id = 1 ;

5. Delete data

Deleting data requires the DELETE statement. The syntax of the DELETE statement is as follows:

DELETE FROM table_name
WHERE condition;

where table_name is the name of the table to delete data, and the WHERE clause is used to specify the condition to be deleted.

For example, if we want to delete the data of the student with student number 1, the DELETE statement is as follows:

DELETE FROM students
WHERE stu_id = 1;

Summary

This article briefly introduces creating tables in Oracle and common query, insert, update and delete data operations. As a mature enterprise-level database management system, Oracle has powerful query and operation functions and is one of the preferred databases for enterprise-level application development.

The above is the detailed content of How to create tables and common query operations in Oracle database. For more information, please follow other related articles on the PHP Chinese website!