What are constraints
Constraints are mandatory at the table level
There are five constraints:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Table-level constraints and column-level constraints
Scope of action:
- (1) Column-level constraints can only work on one column
- (2) Table-level constraints can be applied to multiple columns (of course, table-level constraints can also be applied to one column)
Column constraints must follow the definition of columns. Table constraints are not defined with columns, but separately.
Non-null constraints can only be defined on columns
1. Adding constraints
alter table [table name] add constraint [constraint alias] [constraint category (field name)]
Add PRIMARY KEY constraint (my_emp_id_pk) to the ID column of table emp2
ALTER table emp2 ADD constraint my_emp_id_pk primary key(id);
2. Modifying constraints
alter table modify [field name] [constraint]
alter table modify [field name] [constraint] [alias] [constraint]
- For example, change the name field to not null constraint
alter table emp2 modify name not null;
- Add aliases when modifying
alter table emp2 modify salary constraint my_emp2_salary_n not null;
alter table [table name] add constraint [constraint alias] check([constraint condition])
Example: Adding constraints to the table to make salary greater than 0
alter table emp2 add constraint emp2_salary_min check(salary>0);
4. Delete constraints
alter table [table name] drop constraint [constraint alias]
5. Attention to constraints
- Non-null constraints can only be defined at column level
- The column value of unique constraint can be null
- Foreign key referenced columns must have at least one unique constraint
6. Cascade operation
When a table is operated on, it affects another table with external inspection constraints.
- ON DELETE CASCADE (cascade deletion): When the column in the parent table is deleted, the corresponding column in the child table is deleted.
- ON DELETE SET NULL (Cascade Vacancy): Corresponding Column Vacancies in Subtables
Suppose the table emp has a foreign key dept_id connected to the dept_id of the table Dept
constraint dept_fk foreign key(dept_id) references dept(dept_id) on delete cascade;
Set when creating tables
on delete cascade
on delete set null
Views are similar to subtables, except that the operation of views affects the content of the original table.
- Views are virtual tables.
- Views are based on existing tables, which are called base tables.
- A statement that provides data content to a view is a SELECT statement, which can be interpreted as a stored SELECT statement.
- Views provide users with another form of base table data
The difference between simple view and complex view:
|Characteristic||Simple view||Complex view|
|Table number||One||One or more|
|DML operation||Yes?||Sometimes you can.|
By default, no view authorization is created when scott users log in.
1. open cmd
2. User login using system: sqlplus system/root@orcl
3. Authorization: grant create view to scott;
4. At this point, scott users are granted permission to create view s
Example: Create a view empview from a query to employees
create view empview as select employee_id,last_name,salary from employees where department_id=80
desc empview take a look
SQL> desc empview Name Null? Type ----------------------------------------- -------- --------------- EMPLOYEE_ID NOT NULL NUMBER(6) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2)
At this point, we see that the created view is in line with our intentions.
Add or replace immediately
create or replace view empview as select employee_id,last_name,salary from employees where department_id=80
The view created by queries using composite functions is a complex view
Example: Query the average wages of all departments and assign them to empview2
SQL> create view empview2 as select department_id,avg(salary) avg_salary from employees group by department_id;
When creating a view, add with read only to indicate read-only.
create view empview3 as select employee_id,last_name,salary from employees where department_id=80 with read only;
A uodate operation at this point will prompt errors
SQL> update empview3 set salary=8000 where last_name='Jhonson'; update empview3 set salary=8000 where last_name='Jhonson' * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view
drop view [view image name]
Select the value of the first n rows, or the value between rows N and m
Examples show that:
Now I want to pick out the top ten salaries.
Create an empview4
SQL> create view empview4 as select employee_id,last_name,salary from employees order by salary desc;
At this time, if you want to choose the top ten, you can't start. At this time, you need to use the pseudo-row num column.
select rownum,employee_id,last_name,salary from employees order by salary desc;
However, rownum cannot be used as a condition for queries at this time, because pseudocolumns are virtual.
Then the above query results are queried again as a new table
select rownum,employee_id,salary from (select employee_id,salary from employees order by salary desc ) where rownum < 11;
ROWNUM EMPLOYEE_ID SALARY ---------- ----------- ---------- 1 100 24000 2 101 17000 3 102 17000 4 145 14000 5 146 13500 6 201 13000 7 205 12000 8 147 12000 9 108 12000 10 168 11500 10 rows selected.
For ROWNUM, only < or <= can be used, while for ROWNUM, =, >= no data can be returned.
If you want to query the staff information from 40 to 50, you can only nest it once again and use rawnum as a real list of queries.
select * from( select rownum rn,employee_id,salary from (select employee_id,salary from employees order by salary desc ) ) where rn>40 and rn<50;
RN EMPLOYEE_ID SALARY ---------- ----------- ---------- 41 154 7500 42 171 7400 43 172 7300 44 164 7200 45 179 7000 46 161 7000 47 178 7000 48 155 7000 49 113 6900 9 rows selected.