Oracle database table constraints, views, indexes-this record is an article of my previous Weibo blog

I. Common operation of Oracle database create a constraint when creating a table
1. Add constraints when creating a table
----constraints are rules that define what data in a table should follow or conditions that meet
----constraints are built on columns, so that there is a constraint between a column or columns of data
----Constraints Create a constraint at the same time as the table you create, or create a constraint after you create the table
----constraints are divided into table-level and column-level constraints, defined in different ways, but with the same effect
----Column-level constraints: column-name data type "default Defaults" "Column-level constraint 1 column-level constraint 2 ..."
----Table-level constraint: Constraint constraint name constraint type (column name)
----Table-level constraints can customize the name of the constraint, and the name of the column-level constraint is automatically assigned by the system SYS_CN
----If you have data constraints between multiple columns, you can only use table-level constraints to define
----NOT NULL a non-null constraint can only define column levels and cannot define table-level constraints
/*
PRIMARY KEY constraint--primary key
FOREIGN KEY constraint--foreign key
Non-null constraint--not NULL
Unique constraint--unique
Check Constraint--check
2. Column-level constraints and table-level constraints
Column-level constraint for----foreign key: References table name (column name)
Table-level constraint for----FOREIGN key: Constraint constraint name foreign key (column name) references table name (column name)
Testdeptno number (3) Constraint ADSFA foreign key (Testdeptno) References Dept (DEPTNO)
---How to create or delete a constraint after you create the table:
----ALTER TABLE name modify column name not NULL; ----Add a non-empty constraint
----ALTER TABLE name ADD CONSTRAINT constraint name constraint type (column name);----Add additional constraints
----ALTER TABLE name modify column name null;-----Delete non-null constraint
----ALTER TABLE name DROP CONSTRAINT constraint name;----Remove additional constraints
Example:
Student table:
Column name data type
ST_ID (primary key) number (8)
St_name (non-empty) varchar2 (14)
St_age (over 18 yrs) Number (2)
St_phno (cannot repeat) number (11)
St_date (default current system time) date
St_gender (non-empty, can only be male or female, default is male) char (2)
St_grade (foreign key, associated class table primary key) number (4)
CREATE TABLE Student
(
ST_ID Number (8) primary key,
St_name VARCHAR2 (+) NOT NULL,
St_age Number (2) check (st_age>=18),
St_phone number (one) unique,
St_date Date Default Sysdate,
St_gender char (2) Default ' m ' NOT null check (St_gender in (' m ', ' F ')),
St_grade Number (4) references grade (GRADE_ID)
/*constraint Constraint name foreign key (column name) references table name (column name)
St_grade number (4) Constraint Adsad foreign key (St_grade) references grade (GRADE_ID)

Second, Orcale database Operation continuation View index trigger
1. Create a View
CREATE View Empview as
SELECT * FROM EMP where comm is null;
Prompt when creating view does not have permission to operate so enter Isqlplus to authorize the Scott user by logging in with the SYS DBA account: Grant Ada to Scott, who authorizes Scott to be a DBA;
Grant CREATE view to Scott;
2. Modify the View
Update Empview set comm=comm+200 where ename= ' Tengjiang '; The basic operation and modification tables are the same;
Note: Modifying data in a view is equivalent to modifying the data in the source table;
3. Delete View
Delete Empview where empno=1001;
4, modify the view structure operation and modify the table structure, see above content, here is not repeat;
5. Joint Inquiry
(1), multi-table query see Cartesian product
Select Conut (e.ename) from EMP e,dept D
where E.deptno=d.deptno amd D.dname= ' Tengjiang ';
(2), sub-query the query results as a child table, and then query in the child table
Select COUNT (ename) from the EMP where deptno= (select Deptno from dept where Dname= ' SALES ');
(3), multi-table query and sub-table query differences:
A, the number of queries: Multi-table query query more than the number of queries:
Multi-table query: M*n times
Sub-table query: M+n times
(4), create view in query
A. Create a view:
CREATE View emp_dept as
Select E.*,d.deptno dno,d.dname,d.loc from emp e,dept D
where E.deptno=d.deptno;
Creates a emp_dept view: Links the EMP table and the Dept table;
b, then query in the view
Select COUNT (ename) from emp_dept where dname= ' sales '; the number of people with the department name ' Sales ' is counted;
6, create the index;
----index, which is a directory built on a column of data, can improve query efficiency
----A column that is frequently used as a query condition, creating an index can greatly improve query efficiency
The----index is divided into two parts: rowID and Key values
----ROWID is the physical address of each row of data storage, and the key value is the value of the specific data

There are two main types of----indexes: B-Tree index and bitmap index
---(1), B-tree index: Based on binary tree structure. The B-Tree index structure has 3 basic components: a root structure, a branch node, and a leaf node.
---Where the root node is at the top of the index structure, and the leaf node is at the lowest end of the index structure, and the middle is a branch node.
----B-Tree index is the default index type in Oracle and is a tree-like directory
----CREATE index name on table name (column name);
CREATE INDEX Index_ename on EMP (ename);
----Confirm that the index creation can be viewed here
SELECT * from User_indexes;
----indexes are created, indexes automatically help query statements to improve efficiency when querying data using related columns
---The index is automatically updated when the data in the table changes, no manual update maintenance is required
---When a large amount of data changes, the index produces some fragmented files that occupy space and affect efficiency
---can periodically rebuild the index to clean up fragmented files
(2), bitmap index
----B-Tree index is suitable for a large number of different data in a column
----when there is a large amount of duplicate data in a column, you should use a bitmap index
----Bitmap indexes are not suitable for columns with too frequent data changes
----Create bitmap index index name on table name (column name)
Create bitmap index Index_deptno on EMP (DEPTNO);
7. Trigger
----trigger trigger, actions bound to certain operations are not actively implemented,
----But when the condition is met, the operation is synchronized when the bound action is triggered.
/*
Create (or replace) trigger trigger name
Before/after
Insert or UPDATE or delete
(of column) on table
(For each row)
(When (condition range))
Begin
Trigger action (INSERT or update or delete);
End

Before and after define whether the trigger time is before or after the action
Insert or update or delete defines the action that is triggered
(of column) on table: defines which column of the table is to be triggered when the action is
For each row: Defines whether each row of data is triggered once
When: A column in the middle as a condition must be qualified with old or new, indicating
Pre-modified or modified columns
*/

-----First create a table to store the corresponding record information
CREATE TABLE Record_dept30
(
ename VARCHAR2 (20),
Old_job varchar2 (20),
New_job varchar2 (20),
Rec Date
);
SELECT * FROM Record_dept30

Create Trigger Tri1
After
Update
of job on EMP
For each row
When (old.deptno=30)
Begin
INSERT INTO Record_dept30
VALUES (: old.ename,:old.job,:new.job,sysdate);
End

----in the BEGIN section if you want to invoke data from a table, use: Old and: New to qualify

8. Block operation
----do not allow changes to the manager's payroll
/*
Trigger time: Before, after
Trigger action: UPDATE, DELETE, insert
Trigger object: The SAL column in the EMP table
Trigger Range: Position is manager
*/
/*
Block operation using Raise_application_error (as an ID, error hint) method to force an error to be committed
Error ID is custom, recommended use number between -20000~29999, error prompt custom text
*/
Create or Repaley trigger Tri2
Before
Update
of Sal on EMP
For each row
When (old.job= ' MANAGER ')
Begin
Raise_application_error ( -20001, ' Ddssfadfadsfas ');

Oracle database table constraints, views, indexes-this record is an article of my previous Weibo blog