ORACLE _ create and manage tables

ORACLE _ create and manage tables
Zookeeper

ORACLE _ create and manage tables

① Common database objects
Table: A Basic Data Storage set consisting of rows and columns.
View: logically related data set extracted from the table.
Sequence: provides regular values.
Index: Improves query efficiency
Synonym: alias for an object

② Tables in the Oracle database
1. User-Defined table:
A group of tables created and maintained by the user, including the information required by the user
For example: SELECT * FROM user_tables; view the table created by the user
2. Data Dictionary:
A group of tables automatically created by Oracle Server
Contains database information

③ Query the data dictionary
View user-defined tables.
SELECT table_name FROM user_tables;
View User-Defined database objects
Select distinct object_type FROM user_objects;
View user-defined tables, views, synonyms, and Sequences
SELECT * FROM user_catalog

④ Naming rules for table names and column names:
1. It must start with a letter
2. It must be 1-30 characters long
3. Must contain only A-Z, a-z, 0-9, _, $, and #
4. It must not be the same as other user-defined objects.
5. It must not be a reserved character of Oracle.

⑤ Create table statement
Required:
1. create table permission
2. Storage space
Create table [schema.] table
(Column datatype [DEFAULT expr] [,...]);

3. You must specify:
Table Name
Column name, data type, size

4. Data Type: Description
VARCHAR2 (size): Variable long character data
CHAR (size): Fixed Length character data
NUMBER (p, s): Variable Length Value Data
DATE: DATE type data
LONG: variable length data, up to 2 GB
CLOB: character data, up to 4 GB
RAW (long raw): RAW binary data
BLOB: binary data, up to 4 GB
BFILE: stores binary data of external files, up to 4 GB
ROWID: Row address

5. Create a table using a subquery
Use the AS subquery option to combine table creation and data insertion
Create table table
[(Column, column...)]
AS subquery;
The specified columns must correspond to the columns in the subquery.
Use column names and default values to define Columns

Note: The data in the original table is inserted into the newly created table. If no data is required, add a FALSE condition.

⑥ Alter table statement
1. Use the alter table statement to append, modify, or delete the column syntax
Append Column
Alter table table
ADD (column datatype [DEFAULT expr]
[, Column datatype]...);

Modify columns
Alter table table
MODIFY (column datatype [DEFAULT expr]
[, Column datatype]...);

NOTE: If data in a column cannot be modified, the default value is only valid for the data added later.

Delete column
Alter table table
Drop column column_name;

Rename a column
Alter table table_name
Rename columm old_column_name TO new_column_name

Set column unavailable
Alter table table_name
Set unused COLUMM column_name

7. delete a table
Data and structure are deleted
All running related transactions are committed.
All related indexes are deleted.
The drop table statement cannot be rolled back.

Drop table table_name;

Distinct clear table
Truncate table statement:
Delete all data in the table
Release the table's storage space
The TRUNCATE statement cannot be rolled back.
You can use the DELETE statement to DELETE and roll back data.

Truncate table table_name;

Alias changes the object name.
Execute the RENAME statement to change the name of the table, view, sequence, or synonym.
Must be the object owner

RENAME old_object_name TO new_object_name;

Note: none of the above DDL languages can be rolled back. After the execution is complete, it will be submitted by default.