1. What is a view
- In short, a view is a virtual table, a mapping of entity tables
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 process of project, programmers mainly focus on coding performance and business analysis. For some complex SQL statements, the designer will encapsulate them into a view in advance for the programmer to call
2. Basic operation of view:
1) Simplified version creation
--Grammar create view view name as query statement;
create view view_owners as select * from t_owners;
Location after view creation:
2 delete view
drop view view name;
drop view view_owners;
select * from view_owners;
3) Specific operation: add, delete and modify
--4.Addition, deletion and modification --increase insert into view_owners values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 ); commit; --change update view_owners set name='Hair and hair' where id=11; commit; --Delete delete from view_owners where id=11; commit;
3. View with conditions (detection)
Add constraint checking to views
re 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 --Prepare data select * from t_owners ow where ow.ownertypeid=1; --1;Create view -- or replace, If the view already exists,Will overwrite(replace) --with check option ,Check valid data(add to/Update data,Must be able to find out) create or replace view view_owners1 as select * from t_owners ow where ow.ownertypeid=1 with check option; --2.Query data select * from view_owners1 ; --3.Add data(Wrong conditions,ownertypeid=3 Does not match the original condition,report errors;Violation of unique constraints) ----error; insert into view_owners1 values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 ); --After adding successfully; It will be in the original t_owners Add a piece of data first,then view_owners1 Also add,We can find out in the end insert into view_owners1 values(11,'Meimei supermarket',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 ); commit; --delete drop view view_owners1; delete from t_owners ow where ow.id=11;
4. Forced creation
force forces the view to be created, regardless of whether the query statement is incorrect or not
--0.preparation,Query table data,Table does not exist select * from temp; --1;Create view create force view view_owners2 as select * from temp;
5. Read only view
- with read only, the view must be read-only.
--0.preparation,Query 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(12,'Meimei supermarket 11',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),1 );
6. Associated query
1) Key retention table, non key retention table
- --Key reservation table; which physical table does the view primary key come from? This table is called 'key reservation table'
- --Non key retention tables; other tables are called 'non key retention tables'
- --Feature: in the view generated by associated query, only data in 'key retention table' can be added / modified
Summary: which views cannot be updated?
1 simple view:
* conditions cannot be modified for those with check constraints
* read only view
2 complex view
* non key retention table
- Key retention table: the primary key in the original table and also in the view
* views using aggregate functions
--grammar: 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 -- 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 Xiao' 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 override
--with check option
--with read only view
- View 1 is actually a virtual table, and its data is actually from the table.
- 2 if you change the data of the view, the data of the table will change naturally
- 3 if the data of the table is changed, the view will change naturally.
- 4 what a view stores is not data, but an SQL statement.
1. What is sequence
Sequence: database object used to generate unique numbers.
Equivalent to MySQL auto grow column (auto_increment)
Sequence purpose: maintain automatic growth of primary key in the table.
create sequence serial number;
create sequence serial number start with increment by step maxvalue Max minvalue minimum
create sequence seq_stuno;
-- Full version create sequence seq_stuno2 start with 2 increment by 3 maxvalue 20 minvalue 2
--use ---Query to get sequence value --query, nextval Get next sequence select seq_stuno2.nextval from dual; --Add auto maintain primary key create table tt_demo( id int primary key, name varchar2(50) ); insert into tt_demo(id,name)values(seq_stuno.nextval,'Zhang San'); insert into tt_demo(id,name)values(seq_stuno.nextval,'Li Si'); insert into tt_demo(id,name)values(seq_stuno.nextval,'Wang Wu'); commit;
1. What are synonyms?
Synonyms are aliases. You can alias tables, views, and so on.
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.
2. Synonym classification
Private synonym: only user can access
Public synonyms: accessible to all users of the database
Synonym itself does not involve security. When you give a synonym object permission, you are essentially giving permission to the base object of synonym. Synonym is just an alias of the base object.
- Easy access
- Shorten the length of object name
- Improve security
3. Create and delete
create synonym name for table name view name
- Common synonyms
create public synonym name for table name view name
drop synonym name
- Common synonyms
drop public synonym name
Note: - when using synonym query, you need to use 2 users. Public synonyms can share tables between two users
--practice --1 Create private synonyms create synonym owners for t_owners ; --After creation,Data can be queried select * from owners; ---2 Create common synonyms create public synonym owners2 for t_owners; --After creation,Data can be queried select * from owners2; ---delete drop synonym owners; drop public synonym owners2;
1. What is index?
- --Index is a sort data structure in database management system, which helps to query and update data in database tables quickly
- --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; improve data access performance
- --Index needs physical storage, using space for time
2. Index classification
Normal index: declare a column with the keyword index
Unique index: columns declared with the keyword unique
Compound index: declare multiple columns with the keyword index
3. General index
create index index name on table name (column name);
2) Prepare data
--2 Prepare data --1).Create table create table tt_indextest( id number, name varchar2(30) ); --2).Import 1 million pieces of data in batch begin --Batch start for i in 1..1000000 --loop: for variable in Start value..end loop --Beginning of circulatory body insert into tt_indextest(id,name) values(i,'AA'||i); end loop; --End of circulatory body commit; end; --Batch end
--How many data to view
select count(*) from tt_indextest;
3) Test performance
--3.Test performance --3.1 query(id,name)--The two results are basically the same --0.049 select * from tt_indextest where id=765432; --0.047 select * from tt_indextest where name='AA765432'; --3.2 Add index create index tt_indextest_name on tt_indextest(name) --3.3 Query again --Indexed select * from tt_indextest where id=765432; select * from tt_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. Unique index
If we need to create an index on a column of a table, the value of this column will not be repeated. This is the only index we can create.
create unique index index name on table name (column name);
--Requirement: create a unique index in the water table number column of the owner's table
create unique index index_owners on t_owners(watermeter);
5. Composite index
We often need to query some columns, and we can build a composite index, that is, build an index based on more than two columns
create index index name on table name (column name, column name 2,...);
Index the owner table according to the address and house number. The statement is as follows:
create index owners_index_ah on t_owners(addressid,housenumber);
After watching, Congratulations, and know a little bit!!!
The more you know, the more you don't know
~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!