Oracle Database Learning (iv)

11. Create a table

Crate table Tab1 (f_id number not null,f_a varchar2 (7) Does null,f_b number (6,2) not NULL);

Primary key: field cannot be empty, uniqueness;

External: can be repeated, but is the primary key of another table;

Index: An index is a list of keywords for several rows of data that can improve search performance based on index queries, with common indexes and unique indexes.

12. Data language

(1) Data Manipulation language (DML)

to the data to increase, delete, change, check and other operations;

command: SELECT, INSERT, UPDATE, DELETE, call.

(2) Data Description Language (DDL)

Describe and manage All objects in the SQL database;

command: Create, alter (modify), drop (delete), and so on.

(3) Data Control Language (DCL)

is the statement used to set or change permissions for a database user or role ;

command: Grant (Authorization), REVOKE (REVOKE), commit (Commit), ROLLBACK (rollback), savepoint (savepoint), and so on.

13.sequence

created:Create sequence table1

INCREMENT by1--add a few each time
START with1--from1Start Count
Nomaxvalue--do not set the maximum value
Nocycle--keep accumulating, not looping
CACHETen; --setting up the cacheCachea sequence if the system Downor other conditions will cause the sequence to be discontinuous, or it can be set to---------NOCACHE.

Use :sequence. Currval (returns the current value); sequence. Nextval (increases the value of sequence and returns the value).

14. Views View

created:Create or replace view P_view as select Tab1.f_id,tab1.f_a,tab2.f_id,tab2.f_z from TAB1,TAB2 where tab1.f_id= TAB2.F_ID (or replace: replace old view if view exists)

Delete:drop P_view

15.pl/sql basic elements of program syntax

(1) constants, variables, etc.

constants: Syntax: constant Name constant type identifier [not null]:= value ; constants, including the following variable names must start with a letter, no spaces, no more than 30 characters in length, and cannot have the same name as a reserved word. The constant (variable) quantity name is case-insensitive and can be followed by a number or special character after the letter. The not NULL in parentheses is an optional parameter, and if selected, indicates that the constant (variable) amount cannot be a null value.

PI constant Number (4): = 3.14.

Type identifier	Description
Number	Digital type
Int	Integer type
Pls_integer	Integer type, error occurred while generating overflow
Binary_integer	Integer that represents the signed integer
Char	Fixed-length character, maximum 255 characters
Varchar2	Variable-length character, Max 2000 characters
Long	Variable length character, maximum 2GB
Date	Date type
Boolean	Boolean (TRUE, FALSE, null three take one)

variable:Age Number (3): = 26; Basic common variable as above.

Oracle Database Learning (iv)