Create, modify, add, delete, modify, and query an Oracle table-DML operation

1. Table Name and column command rules:

It must start with a letter and cannot exceed 30 characters. Oracle reserved words cannot be used. Only A-Za-z0-9 $ # and so on can be used.

TABLE: partition table, table partition cluster index table index-organized table (IOTs)

 

2. supported data types:

 

Scalar data type:

Varchar2 Max. 4000 characters variable-length bytes data

Char maximum 2000 characters, fixed length bytes data

Date data, value range: From 4712.1.1 BC to 9999.12.31 timestamp accurate to milliseconds

Number numeric data,

Clob is used to store long-byte Big Data Objects in databases, such as WORD files. The maximum size of a large object is 4 GB.

Blob stores large unstructured binary objects, such as photos or slides.

Bfile is used to store large unstructured binary objects in operating system files outside the database, such as movies.

Raw directly stores binary data in the database. The maximum length is 2000 bytes.

Logn long raw is used to store binary image text in ORACLE 8 face-to-face databases.

 

3. About ROWID

When you insert a row of data into a table, ORACLE automatically adds a ROWID to the row. Each row has a unique ROWID. ORACLE uses ROWID to locate the row. It is not explicitly stored as a column value. It is the fastest way to access the row in a table.

000000 FFF BBBBBB RRR

Data Object number corresponding file number block number row number

The data object number is assigned to each object, which is unique in a database. Each file in the same tablespace is unique relative to the file number. Block number, which is the position of the block containing data rows in the relative file. The row number that identifies the location of the row directory in the block header.

SQL> select ename, rowid from scott. emp;

ENAME ROWID

----------------------------

SMITH AAAR3sAAEAAAACXAAA

ALLEN AAAR3sAAEAAAACXAAB

WARD AAAR3sAAEAAAACXAAC

JONES AAAR3sAAEAAAACXAAD

The Data Object of the SMITH row is AAAR3s. The relative file number is AAE, the block number is AAAACX, And the row number is AAA.

#############################

ORACLE Data row Structure

The row header stores the number of columns in the row, migration information, and row lock status.

Row data, composed of a series of columns and column values

The column length is columnlength. Generally, one byte is required. The column length exceeds 250 bytes and the column length is 3 bytes.

Column value: columnvalue. the actual value of the column is stored after the long bytes of the column.

When creating a table, you should place different tables in different tablespaces.

Use local tablespace management to avoid fragmentation, and use several standard EXTENT sizes in the table to reduce tablespace fragmentation.

 

4. Table creation methods

The create table statement specifies the table name, column name, column data type, column width, and whether the default value exists.

Use a query statement as a clause to create a table. In this case, the content of the newly created table is the content of the corresponding column of the queried table.

Create table t2 as select ename name, salsalary from emp;

Create table t2 as select * from emp;

Use a false condition. Only the table structure is opposite. Do not use data.

Create table t3 (c1, c2, c3) as selectename, empno, sal from emp where 9 = 1;

Create a table manually. There are five columns, four digits of the Data Type, 20 variable-length tables, two fixed-length tables, and the birthday data type is date. The prize value can be up to seven digits with two decimal places, that is, up to 9999.99

Create table student (xh number (4), xmvarchar2 (20), sex char (2), birthday date, sal number (7,2 ));

 

5. modify a table

Modify the data type and length of a column

Alter table t2 modify (name char (4 ));

Modify the table name, the table OWNER or SYSDBA

Rename t2 to t_2;

Modify column name

Alter table t3 rename column c1 to name;

Table comment and column comment

Modify a column:

Alter table emp4 modify test varchar2 (20 );

Delete a column:

Alter table emp4 drop column test;

For more details, please continue to read the highlights on the next page:

 

Oracle DML Process

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Common MySQL DDL, DML, and DCL languages (example)

Execute batch DML exercises for Oracle basic transactions and ForAll

Oracle DML Statement (insert, update, delete) rollback Estimation

1 2 3 Next Page