Oracle Data types

Oracle Data types

When you create a table, you must specify the data type for each column

The following are categories of Oracle data types:

  Oracle Primary Data types
Character Data Types

Char: fixed-length alphanumeric value, can be 1-2000 bytes

VARCHAR2: variable length, 1-4000 bytes

Long: variable length, up to 2G

Number data type

can store integers, floating-point numbers, and real numbers

Maximum accuracy of 38 bits

Format: Number [(p[, S])]

Date Data Type

Store date and time values

Date: Stores the day and time portion, accurate to the entire second

Timestamp: Stores date, time, and time zone information, and seconds is accurate to 6 digits after the decimal point

raw/long RAW data Types

RAW: Stores binary data up to 2000 bytes

LONG RAW: For storing variable-length binary data, up to 2GB

LOB data Types

Called a "large object" data type that can store up to 4GB of unstructured information, such as sound clips and video files, allowing efficient, random, segmented access to data

CLOB: Character data; BLOB: Binary object, shape, video, audio, etc. BFILE: binary file (binary), which is used to store binary data in operating system files outside the database

  SQL Statement Review

SQL supports the following categories of commands:

Data Definition language (DDL): Used to change database structure

CREATE ALTER DROP

Data Manipulation Language (DML): Used to retrieve modified and inserted data

INSERT SELECT DELETE UPDATE

Transaction Control Language (TCL): Guarantee the execution of a transaction

COMMIT ROLLBACK SavePoint

Data Control Language (DCL): providing permission control commands

GRANT REVOKE

  Table Management--Create tables

Create a table with an existing table

Grammar:

CREATE TABLE <new_table_name> as SELECT column_names from <old_table_name>;

Example:

sql> CREATE TABLE newemp

As SELECT * from emp[where 1 = 2;];

sql> CREATE TABLE newemp

As SELECT empno, salary

from EMP;

  Table Management--Constraints
Five major constraints

1, non-null constraint: NOT NULL

2. PRIMARY KEY constraint: PRIMARY key, unique, and non-null

3, FOREIGN KEY constraint: FOREIGN key ... REFERENCES

4, UNIQUE constraint: Unique, unique, allow null

5. Condition constraint: CHECK

column-level constraints

Constraints as part of a column definition allow you to define all five types of constraints

Sql> CREATE TABLE clazz_table (

CID number (PRIMARY KEY),

CNAME VARCHAR2 (+) not NULL,

CDate DATE);

Sql> CREATE TABLE student_table (

Sid Number (Ten) primary key,

Clazzid Number (Ten) REFERENCES clazz_table (CID),

Sno VARCHAR2 (+) UNIQUE,

Sname VARCHAR (+) not NULL,

Sage Number (3) CHECK (sage>0 and sage<120));

Attention:

column names, data types, default values of the order can not be chaotic;

At the end of the column definition, specify the constraint for the column type;

A column of a PRIMARY KEY constraint can not specify a non-null constraint (NOT NULL).

table-level constraints

Constraints as part of a table definition, except for null constraints, allowing the definition of other four classes of constraints

Unique constraint

CONSTRAINT Nameunique (Column[,column ...])?

PRIMARY KEY constraint

CONSTRAINT name PRIMARY KEY (Column[,column ...])?

FOREIGN KEY constraints

CONSTRAINT name FOREIGN KEY (Column[,column ...]) REFERENCES table (Column[,column ...])

Conditional constraints

CONSTRAINT name CHECK (condition)?

Defining table-level constraints

Sql>create Table Account (

Name VARCHER2 (32),

Acc_type Number (1) is not NULL,

Acc_code VARCHAR2 (32),

BA number (5,2) DEFAULT 100,

CONSTRAINT pk_qrsx_account PRIMARY KEY (name),

CONSTRAINT uk_qrsx_account UNIQUE (Acc_code),

CONSTRAINT Ck_qrsx_account CHECK (ba>=100 and ba<=1000));

  Table Management--Modifying tables

ALTER TABLE: Modify tables

Column additions, modifications

Add/Remove constraints to a table

Grammar:

ALTER table[add][modify][drop Column]

Example:

ALTER TABLE users Add (pubdate date);

ALTER TABLE users add (age number)

ALTER TABLE users Modify (UserName varchar2 (20));

ALTER TABLE users drop (password);

ALTER TABLE users add primary key (USERID);

  Table Management-Other

RENAME table_name to new_name: renaming

TRUNCATE table: Deleting tables, logging unrecoverable

drop TABLE: Deleting tables

COMMENT on: Adding comments to a table

Example:

RENAME users to test

drop table users;--to delete tables structure

TRUNCATE TABLE Users-delete records, free space

Delete from emp--deletes the record, but can recover

COMMENT on TABLE EMP is ' Employee information ';

  tables in the Oracle database

User table

A collection of tables that the user creates and maintains contains information about the user

Data dictionary

A collection of tables and views created and maintained by the Oracle server, containing information about the database

USER_XXX User-owned

All_xx users have permission to view

DBA_XXX (SYS) all the information

  Data Control Language DCL

Data Control Language provides permission control commands for users

The commands for permission control are:

Grant grants permissions

REVOKE revoke a granted permission

Example:

Sql> GRANT all on the EMP to TEA;

Sql> REVOKE SELECT, UPDATE on EMP from TEA;

Oracle Data types