(3) Oracle Fundamentals-Table

· Understanding Tables

　　The tables in Oracle are stored in a table space and have the following characteristics:

Basic storage unit for <1> data

<2> Two-dimensional structure

Line: Also known as ' record '

Column: Also called ' field or domain '

<3> conventions

① Each column must have the same data type

② Column Name Unique

③ each record is unique

· Data type

　<1> character type

①char (n), NCHAR (n)

Fixed length character type, if n=10, deposit 3 characters, then will be supplemented 7 spaces. General storage length fixed data, such as mobile phone number.

The difference is that n in CHAR (n) is the maximum of1000, and N in NCHAR (n) is maximum.

( Note: n is the maximum stored character length set )

②VARCHAR2 (n), nVARCHAR2 (n)

A variable-length character type that consumes only the space that is actually stored, saving space.

　<2> Numerical type

①number (P,s)

P is a valid number, S is the number of digits after the decimal point

For example: Number (5,2), which represents 5 digits, retains 2 decimal places, such as 123.45

②float (N)

Mainly used to store binary data, can store 1-126 bits, n represents the number of bits.

Multiply by 0.30103 to convert it to decimal.

<3> Date Type

①date

Accurate to seconds

Date representation range: January 1, 4712 A.D.-December 31, 9999 CE

②timestamp

Accurate to fractional seconds

<4> Other types

①blob

Maximum storage size 4GB, stored in binary form

②clob

Maximum storage size 2GB, stored as a string

· Manage tables

　<1> Create a table

Basic syntax: CREATE TABLE table_name

(

column_name data_type, ...

);

Example: CREATE TABLE userinfo

(

ID Number (6,0) primary key,

Username VARCHAR2 (20),

Password VARCHAR2 (20),

RegDate Date

);

　　<2> Modify a table

① Adding fields

Syntax: ALTER TABLE table_name ADD column_name data_type;

　　　　　　Example: ALTER TABLE userinfo ADD remarks varchar2 (+);

② Changing the field data type

Syntax:ALTER TABLE table_name MODIFY column_name data_type;

Example: ALTER TABLE userinfo MODIFY remarks varchar2 (+);

　　　　　　( Note: There is no data to modify in this field )

③ Delete a field

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

　　　　　　Example: ALTER TABLE userinfo DROP COLUMN remarks;

④ modifying field names

Syntax: ALTER TABLE table_name RENAME COLUMN column_name to New_column_name;

　　　　　　Example: ALTER TABLE userinfo RENAME COLUMN regdate to New_regdate;

⑤ Modifying table names

　　　　　Syntax:RENAME TABLE table_name to new_table_name;

　　　　　　Example: RENAME TABLE userinfo to New_userinfo;

　　<3> Delete a table

① Delete Only the data in the table, preserving the table structure

　　　　　　TRUNCATE TABLE table_name;

② both delete the table data and delete the table structure

　　　　　　DROP TABLE table_name;

(3) Oracle Fundamentals-Table