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

Oracle (1) --) Detailed description of database objects (Text explanation, first of all a small understanding ~)
---- Database object: a set of logical structures ---- 1. Synonym: alias of an existing object/* function: simplify the SQL statement to hide the Object Name and provide the owner with a public access classification for the object: */-- 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 preferentially creates public synonym syn1 for scott. emp; select * from syn1; -- Private synonym: It can only be accessed by the current mode, and cannot create synonym emp1 for scott with the same name as the object in the current mode. emp; select * from emp1;/* conditions for creating Synonyms: 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 synonyms: You must have the create public synonym system permission */-- create or replace an existing synonym, create or replace synonym emp1 for scott. books;
Select * from emp1; -- delete private synonym drop synonym emp1; -- delete public synonym drop public synonym syn1; -- auto-increment for columns in the oracle table: -- 1 sys_guid: global unique identified: globally Unique Identifier, used to identify a row of data, a 32-bit unique string select sys_guid () from dual;

Ii. sequences (used with triggers) -- sequences: database objects used to generate unique and continuous numbers -- sequences can be ascending, it can also be a descending create sequence seq_toys start with 10 increment by 10 maxvalue 500 minvalue 10 nocycle cache 10; /* access the value of the series through the pseudo columns of the series NEXTVAL return the next value of the series CURRVAL return the current value of the series */create table toys (toyid int primary key, toyname char (20), toyprice float); insert into toys values (seq_toys.nextval, 'xiongda', 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 changed.
Alter sequence seq_toys increment by 20 maxvalue 1000;
-- Delete the sequence drop sequence seq_toys;
3. View: views are customized to display data from one or more tables. * views have the following advantages: provides another level of table security, hiding the complexity of data, simplifying the user's SQL command isolation, changing the base table structure by renaming columns, providing data from another perspective
CREATE [or replace] [FORCE] VIEW view_name [(alias [, alias]...)] AS select_statement [with check option] [with read only]; or replace: If a 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 you add or modify data to a view, you must meet the subquery condition with read only: Set read-only views. You cannot change data through views, such a view has higher security */
Create or replace view v_stu as select * 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_stu as select * 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 ('20140901', 'juli ', 'F', null, 'maths ', to_date ('2017-12-11', 'yyyy-mm-dd '));
-- Create or replace view v_stusorted as select * FROM students order by sdept using the order by clause;
Select * from v_stusorted -- force: create 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 '); -- a single row function, grouping function, and expression can be used in the view to delete the view drop view v_temp; -- how to ensure that the modification to the view is finally mapped to the unique base table data -- Key-preserving table: the primary key of the base table is also the primary key of the view.
Iv. Index: an optional table-Related Structure/* mainly serves to improve the performance of SQL statement execution and reduce disk I/O.
Features: Oracle automatically maintains indexes for table-independent data logically and physically.
There are two ways to create an index: 1 manual creation 2 automatic creation: Unique primary key constraint */
Create index index_stu on students (sname); -- Re-index -- rebuilD performance is better than drop create statement alter index index_stu rebuild; -- delete index drop index index_stu; -- create unique index index_name on students (sname );
-- Composite index: If the WHERE clause of the SQL statement references all or most columns of the composite index, the search speed can be improved. create index index_ss on students (sage, sdept );
-- USER_INDEXES select * from USER_INDEXES;