What are constraints

There are five constraints:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK

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)

• Definition:
  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]

perhaps

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;

3.check constraint

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

• 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

view

• 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
function	No	Yes
Grouping	No	Yes
DML operation	Yes?	Sometimes you can.

Simple view

Create view

Be careful:
By default, no view authorization is created when scott users log in.
Solutions:
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.

Modify view

Add or replace immediately

create or replace view empview as 
select employee_id,last_name,salary 
from employees 
where department_id=80

Complex view

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;

Use permission

When creating a view, add with read only to indicate read-only.
For example:

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

Delete view

It's simple
drop view [view image name]

TOP-N analysis

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.
For example:

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;

Output:

    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.

Be careful:
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;

Output results:

        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.