Oracle (1) -- & gt; Detailed description of database objects (Text explanation, first of all a small understanding ~), Oracle details

Oracle (1) --> detailed description of database objects (Text explanation, first of all a small understanding ~), Oracle details
---- Database object: a set of logical structures ----1. Synonym: alias of an existing object/*Purpose:Simplify SQL statementsHide the Object Name and ownerProvide public access to objectsCategory :*/-- Public synonym: it can be accessed by all database users. When the local object and the public object have the same name, the local object takes precedence.Create public synonym syn1 for scott. emp;Select * from syn1;-- Private synonym: It can only be accessed by the current mode and cannot have the same name as the object in the current mode.Create synonym emp1 for scott. emp;Select * from emp1;/*Conditions for creating a synonym: If you create a private synonym in your own mode, you must have the create synonym system permission,To create a private synonym in other modes, you must have the create any synonym system permission.Public synonym: You must have the create public synonym system permission.*/-- Create or replace an existing SynonymCreate or replace synonym emp1 for scott. books;
Select * from emp1;-- Delete a private SynonymDrop synonym emp1;-- Delete a public SynonymDrop public synonym syn1;-- Auto-incrementing columns in oracle tables:-- 1 sys_guid: global unique identified: globally unique identifier, used to identify a row of data, a 32-bit unique stringSelect sys_guid () from dual;

Ii. Sequence (used with trigger)-- Sequence: used to generate database objects with unique and continuous serial numbers.-- The sequence can be ascending or descending.Create sequence seq_toysStart with 10Increment by 10Max value 500Minvalue 10NocycleCache 10;/*Access the value of the sequence through the pseudo columns of the sequenceNEXTVAL returns the next value of the sequenceCURRVAL returns the current value of the sequence*/Create table toys(Toyid int primary key,Toyname char (20 ),Toyprice float );Insert into toys values (seq_toys.nextval, 'xiong da', 20 );Select * from toys;Select seq_toys.CURRVAL from dual;Insert into toys values (seq_toys.nextval, 'xiong 2', 21 );-- Use the alter sequence statement to modify the SEQUENCE. The start with parameter of the SEQUENCE cannot be modified.
Alter sequence seq_toys increment by 20 maxvalue 1000;
-- Delete SequenceDrop sequence seq_toys;
3. View: views are customized to display data from one or more tables./*View advantages:Provides another level of table security.Hiding data complexitySimplified SQL commandsChange the structure of the isolated base tableRename columns to provide data from another perspective
CREATE [or replace] [FORCE] VIEWView_name [(alias [, alias]...)]AS select_statement[With check option][With read only];Or replace: if the view exists, replace it. Otherwise, create a new view.Force: Creates a view regardless of whether the base table exists.With check option: once this restriction is used, when adding or modifying data to a view, the subquery conditions must be met.With read only: sets the view read-only, and cannot change data through the view. This view has higher security.*/
Create or replace view v_stuSELECT * FROM students WHERE sdept = 'art'With check option;
Select * from v_stu;Select * from students;
Insert into v_stu values ('20170101', 'july', 'F', null, 'art', to_date ('2017-12-11 ', 'yyyy-mm-dd '));
Create or replace view v_stuSELECT * FROM students WHERE sdept = 'art ';
-- If this option is not available, you can change the records that do not meet the conditions (in an attempt) to invisible records in the view.Insert into v_stu values ('20170101', 'juli', 'F', null, 'maths ', to_date ('2017-12-11 ', 'yyyy-mm-dd '));
-- Use the order by clause to create a viewCreate or replace view v_stusortedSELECT * FROM students order by sdept;
Select * from v_stusorted-- Force: view with errorsCreate force view v_temp as select * from t_temp;Select * from v_temp;
Create table t_temp (id varchar2 (10), name varchar2 (20 ));Insert into t_temp values ('1', 'A ');-- Single-row functions, grouping functions, and expressions can be used in the view.--- Delete ViewDrop view v_temp;-- How to ensure that the modification to the view is finally mapped to the unique base table data-- Key reserved table: the primary key of the base table is also the primary key of the view.
Iv. Index: an optional table-Related Structure/*Main role: to improve the performance of SQL statement executionReduce disk I/O
Features: logically and physically independent from table dataOracle automatically maintains Indexes
You can create an index in either of the following ways:1. Manually created2 automatic creation: Unique primary key constraint*/
Create index index_stu on students (sname );-- Re-Indexing-- RebuilD has better performance than the drop create statementAlter index index_stu rebuild;-- Delete an indexDrop index index_stu;-- Unique indexCreate unique index index_name on students (sname );
-- Composite Index: used if the WHERE clause of an SQL statement referencesAll or most columns of the combined index can improve the retrieval speed.Create index index_ss on students (sage, sdept );
-- USER_INDEXESSelect * from USER_INDEXES;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.