----Database objects: A collection of logical structures----
synonyms: Aliases for existing objects
/*
function:
Simplifying SQL statements
hide the name and owner of an object
provides public access to an object
Category: */
--public synonym: can be accessed by all database users, local objects take precedence when local objects have the same name as public objects
create public synonym syn1 for scott.emp;
select * from Syn1;
--private synonym: can only be accessed by the current mode and cannot have the same name as an object in the current mode
create synonym emp1 for scott.emp;
select * from EMP1;
/*
synonym creation Condition: In its own mode to create a private synonym, users need to have the Create synonym system permissions,To
create a private synonym in other schemas, the user needs to have the Create any synonym system permission
public synonym: User needs to have create public synonym system permissions
*/
--Create or replace existing synonyms
Create or replace synonym emp1 for scott.books;
select * from EMP1;
--delete private synonyms
drop synonym emp1;
--delete public synonyms
drop public synonym syn1;
the columns in the--oracle table implement self-increment:
--1 Sys_guid:global Unique identified: Globally unique identifier, a unique 32-bit string that identifies a row of data
Select Sys_guid () from dual;
ii. sequence (with trigger)
--sequence: A database object used to generate a unique, sequential ordinal
--The sequence can be ascending or descending
Create sequence Seq_toys
start with ten
Increment by ten
MaxValue
MinValue Ten
nocycle
Cache Ten;
/*To
access the value of a sequence through a pseudo-column of a sequence
Nextval returns the next value of the sequence
Currval Returns the current value of the sequence
*/
CREATE TABLE Toys
(Toyid int primary KEY,
Toyname char (+),
toyprice float);
INSERT into toys values (Seq_toys.nextval, ' Big Bear ', +);
select * from Toys;
Select Seq_toys. Currval from dual;
INSERT into toys values (Seq_toys.nextval, ' Bear II ', +);
--Modify the sequence with the ALTER SEQUENCE statement and cannot change the start with parameter of the sequence
alter sequence seq_toys increment by MaxValue;
--Delete sequence
drop sequence seq_toys;
Three: View: View displays data from one or more tables in a customized way
/*
The advantages of the view are:
provides another level of table security
hides the complexity of the data
simplifies the user's SQL commands
changes in the structure of the isolated base table
provide data from another angle by renaming the column
CREATE [OR REPLACE] [force] VIEW
view_name [(alias[, alias] ...)]As
select_statement
[with CHECK OPTION]
[with READ only];
or replace: Replace if the view exists, or create a new viewForce
: Creates a view regardless of whether the base table exists
with CHECK option: When you use this restriction, you must satisfy the criteria of the subquery when you add or modify data to the view
with Read only: Sets the view to read-only and cannot change data through the view, so that the 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 (' 20030105 ', ' July ', ' F ', null, ' Art ', to_date (' 1996-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 non-qualifying record (in the attempt) to a non-visible record in the view
INSERT into v_stu values (' 20030106 ', ' Juli ', ' F ', null, ' Maths ', to_date (' 1996-12-11 ', ' yyyy-mm-dd '));
--Create a view using the ORDER by clause
CREATE OR REPLACE VIEW v_stusorted as
SELECT * from students ORDER by sdept;
SELECT * from v_stusorted
--force: A View with errors
Create force View v_temp as SELECT * from T_temp;
select * from V_temp;
CREATE TABLE T_temp (ID varchar2 (Ten), name VARCHAR2);
INSERT into t_temp values (' 1 ', ' a ');
--single-line functions, grouping functions, and expressions can be used in views
---Delete a view
drop view v_temp;
--How to ensure that changes to the view are finally mapped to unique base table data
--Key Reservation table: The primary key of the base table is also the primary key of the view
Iv. Index: An optional structure associated with the table
/*
primary role: To improve performance of SQL statement execution
reduce disk I/O
Features: Data that is logically and physically independent of the table
Oracle Automatic Maintenance index
There are two ways to create the index:
1 Manually created
2 Auto-create: PRIMARY key UNIQUE constraint
*/
CREATE INDEX Index_stu on students (sname);
--Rebuilding the index
--rebuild performance is better than drop CREATE statement
ALTER index INDEX_STU rebuild;
--Delete index
DROP index Index_stu;
--Unique index
Create unique index index_name on students (sname);
--Combined index: function if the SQL statement is referenced in the WHERE clause
all columns of the combined index or most of the columns, you can increase the retrieval speed
CREATE INDEX INDEX_SS on students (sage,sdept);
--user_indexes
select * from User_indexes;
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Oracle (i)--a detailed explanation of the database objects (text interpretation, first a little understanding ~)
