oracle query creates table

Oracle database is one of the commonly used enterprise-level databases. It provides a rich query language and functions for users to create, query, modify and delete tables in the database. This article will introduce how to query and create tables in Oracle database.

1. View existing tables

To query existing tables in the Oracle database, you can use the following SQL statement:

SELECT table_name FROM user_tables;

After running the above SQL statement , will return the names of all tables owned by the current user. Among them, user_tables is the metadata table in the Oracle database system, which is used to store information about all tables owned by the current user.

In addition, if you need to query tables owned by other users, you can use the following SQL statement:

SELECT table_name, owner FROM all_tables;

After running the above SQL statement, information about tables owned by all users will be returned, including table names and owner.

2. Create a new table

To create a new table in the Oracle database, you can use the following SQL statement:

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

In the above SQL statement, table_name refers to the name of the table to be created, column1, column2, etc. are the column names of the table, datatype refers to the column data type. NULL and NOT NULL are used to specify whether the column is allowed to be null.

For example, the following SQL statement will create a new table named students:

CREATE TABLE students (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   age INT NULL,
   gender CHAR(1) NULL
);

In the above SQL statement, students is to be created The name of the table, id, name, age, gender are the column names of the table. INT, VARCHAR(50), CHAR(1) are the data types of these columns respectively. NOT NULL and NULL are used to specify whether the column is allowed to be empty.

3. Modify existing tables

In Oracle database, you can modify the structure and attributes of existing tables through the ALTER TABLE statement. Here are some examples of common ALTER TABLE statements:

• Add New Column

ALTER TABLE table_name ADD column_name datatype;

For example:

ALTER TABLE students ADD phone VARCHAR(50);

Above SQL The statement will add a new column named phone to the students table.

• Delete column

ALTER TABLE table_name DROP COLUMN column_name;

For example:

ALTER TABLE students DROP COLUMN age;

The above SQL statement will delete a column named# from the students table Column of ##age.

Modify the data type of the column

ALTER TABLE table_name MODIFY column_name datatype;

For example:

ALTER TABLE students MODIFY gender VARCHAR(1);

The above SQL statement will change the # in the

students table The data type of the ##gender column is changed to VARCHAR(1).

Modify the name of the column

• ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

For example:

ALTER TABLE students RENAME COLUMN gender TO sex;

The above SQL statement will change the ## in the

students

table The name of the #gender column is changed to sex. To sum up, querying and creating tables is one of the most basic and commonly used operations in Oracle database. Through the above introduction, I believe readers have already understood how to query existing tables, create new tables and modify existing tables in the Oracle database. In actual applications, developers need to flexibly use these operations according to specific needs and combine them with other powerful query languages ​​and functions to achieve richer, more efficient and safer data processing and management.

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