1.1 what is a view
- View is a mapping of virtual table and entity table.
- When to use views
- In development, there are some table structures that do not want too many people to contact, so entity tables are mapped to a view. (simplified table structure)
- In the project process, programmers mainly focus on the performance of coding and business analysis. For some complex SQL statements, the designer will package them into a view in advance for the programmer to call
1.2 basic operation of view
1.2.1 creation of simplified version
--Grammar create view view name as query statement;
--Grammar drop view view name;
1.2.3. Specific operation: CURD
-- 0.Prepare data and query the owner table select * from t_owners; -- 1 Create view create view view_owners as select * from t_owners; -- 2 Delete view drop view view_owners; -- 3 query -- Same syntax as query table select * from view_owners; -- 4 Addition, deletion and modification insert into view_owners values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 ); commit; update view_owners set name = 'RT Mart' where id = 11; commit; delete from view_owners where id = 11; commit;
1.3 view with conditions (detection)
- Add constraint checking to views
- Replace, if the view already exists, it will overwrite (replace)
- with check option to check the valid data (add / update data, which must be able to be queried)
-- Conditional view -- 0 preparation select * from t_owners where ownertypeid = 1 -- 1 Create view create or replace view view_owner1 as select * from t_owners where ownertypeid = 1 with check option -- 2 query select * from view_owner1; -- 3 Add data -- Failed to execute, constraint violation provided insert into view_owner1 values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 ); commit; -- Execution successful insert into view_owner1 values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 ); commit; delete from t_owners where id = 11; commit;
1.4. Forced creation (understanding)
- force forces the view to be created, regardless of whether the query statement is incorrect or not
--- Force creation -- 0 Preparatory work, query table data, table does not exist select * from temp; -- 1 Create view create force view view_owners2 as select * from temp;
1.5 read only view
- with read only, the view must be read-only.
-- Read-only views -- 0.Prepare data and query the owner table select * from t_owners; -- 1 Create read-only view create view view_owners3 as select * from t_owners with read only -- 2 query select * from view_owners3 -- 3 add to --not allow insert into view_owners3 values(11,'Meilianmei supermarket 666',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 ); commit;
1.6. Associated query
1.6.1 term explanation: key retention table, non key retention table
- Feature: in the view generated by the associated query, only the data in the key retention table can be added / modified.
1.6.2 SQL writing
-- Associated query (complex view) -- 0 get ready sql select ow.id Owner No,ow.name Name of owner,ot.name Type name from t_owners ow inner join t_ownertype ot on ow.ownertypeid = ot.id -- 1 Create view create or replace view view_owners4 as select ow.id Owner No,ow.name Name of owner,ot.name Type name from t_owners ow inner join t_ownertype ot on ow.ownertypeid = ot.id -- 2 Query data select * from view_owners4 -- 3 Update data -- 3.1 Key retention table update view_owners4 set Name of owner = 'Wang Xiaoqiang' where Owner No = 2; commit; -- 3.2 Non key retention table (non key value retention table)--Execution unsuccessful update view_owners4 set Type name = 'Administrative institutions' where Owner No = 2; commit;
create [or replace] [force] view View name as Query statement [with check option] [with read only] -- or replace cover -- force force -- with check option Valid data check -- with read only Read-only views
2.1 what is sequence
- Series: database objects used to produce unique numbers
- Equivalent to MySQL auto grow column (auto_increment)
- Sequence purpose: maintain automatic growth of primary key in the table.
- Simplified version:
create sequence serial number;
- Full version:
create sequence serial number start with increment by step maxvalue Max minvalue minimum
-- Simplified version create sequence seq_stuno;
-- Full version create sequence seq_stuno2 start with 2 increment by 3 maxvalue 20 minvalue 2
- Query to get sequence value
-- query , nextval Get next sequence select seq_stuno2.nextval from dual;
- Add auto maintain primary key
-- Table primary key auto maintenance create table t_demo( id int primary key, name varchar2(50) ); insert into t_demo(id,name) values(seq_stuno.nextval , 'Zhang San'); insert into t_demo(id,name) values(seq_stuno.nextval , 'Li Si'); insert into t_demo(id,name) values(seq_stuno.nextval , 'Wang Wu'); commit;
3.1 what is synonym
- Synonyms are aliases that can be given to tables, views, and so on.
- Synonym meaning:
- Synonyms allow base objects to be renamed or moved. In this case, only synonyms need to be redefined, and synonym based applications can continue to run without modification.
3.2 classification of synonyms
- Private synonym: only user can access
- Public synonyms: accessible to all users of the database
3.3 creation and deletion
create synonym name for table name view name
create public synonym name for table name view name
drop synonym name
drop public synonym name
- When you use synonym query, you need to use two users for testing, and common synonyms can share tables between two users.
--- synonym -- Create private synonyms create synonym owners for t_owners; -- After creation, you can query the data select * from owners; -- Create common synonyms create public synonym owners2 for t_owners; select * from owners2; --- delete drop synonym owners; drop public synonym owners2
4.1 what is index
- An index is a data object used to speed up data access. Reasonable use of index can greatly reduce the number of i/o and improve the performance of data access.
- Index purpose: to improve data access performance.
- Index requires physical storage. Use space for time.
4.2 index classification
- Normal index: declare a column with the keyword index
- Unique index: columns declared with the keyword unique
- Compound index: use the keyword index to declare multiple columns
4.3 general index
create index index name on table name (column name);
4.3.2 data preparation
-- 1 Prepare data -- 1.1 Create table drop table t_indextest; create table t_indextest( id number, name varchar2(30) ); -- 1.2 Import 1 million pieces of data in batch begin for i in 1..1000000 loop insert into t_indextest(id,name) values(i, 'AA' || i); end loop; commit; end;
4.3.3 test performance
-- 2 test -- 2.1 query(id,name) -- The two results are basically the same select * from t_indextest where id = 765432; select * from t_indextest where name = 'AA765432'; -- 2.2 Add index create index t_indextest_name on t_indextest(name); -- 2.3 Query again -- Index fast select * from t_indextest where id = 765432; select * from t_indextest where name = 'AA765432';
- Test id query performance
--- Implementation plan explain plan for select * from t_indextest where id = 765432; select * from table(dbms_xplan.display());
- Test name query performance (Index added)
explain plan for select * from t_indextest where name = 'AA765432'; select * from table(dbms_xplan.display());
4.4 unique index (understanding)
- If we need to create an index on a column of a table, the value of this column will not be repeated. At this point we can create a unique index.
create unique index index name on table name (column name);
4.5 composite index (understanding)
- We often need to query some columns, and we can build a composite index, that is, an index based on more than two columns
create index index name on table name (column name, column name 2,...);