Oracle INNER JOIN | Outer JOIN | Full Join | Add Constraint | Drop Constraint | Disable Constraint | Enable constraint

--Build a table statement
CREATE TABLE SCOTT. DEPT
(
Deptno Number (2) is not NULL,
Dname VARCHAR2 (15),
Loc VARCHAR2 (13)
)
Tablespace USERS
;
Comment on table dept
Is ' Department table ';
Comment on column Deptno
Is ' department number ';
Comment on column Loc
Is ' department seat ';

--add PRIMARY KEY constraint
ALTER TABLE Dept
Add constraint Pk_dept primary key (DEPTNO);

CREATE TABLE SCOTT. Emp
(
Empno Number (4) is not NULL,
Ename VARCHAR2 (10),
Age Number (3),
Job VARCHAR2 (9),
Mgr Number (4),
HireDate DATE,
Sal Number (7,2),
Comm Number (7,2),
Deptno Number (2)
)
Tablespace USERS
;

CREATE TABLE SCOTT. Emp
(
Empno Number (4) primary key NOT NULL,--add PRIMARY KEY constraint, NOT NULL constraint
Ename VARCHAR2 (TEN) not null unique,--add UNIQUE constraint
Age Number (3) is not NULL check (between 0 and--add), check constraint
Job VARCHAR2 (9),
Mgr Number (4),
HireDate DATE,
Sal Number (7,2),
Comm Number (7,2),
Deptno Number (2)
Constraint Fk_emp_deptno foreign KEY (DEPTNO) References Dept (DEPTNO)--add foreign KEY constraint
)
Tablespace USERS
;
Comment on table emp
Is ' Employee Information form ';
Comment on column empno
Is ' employee number ';
Comment on column ename
Is ' employee name ';
Comment on column job
Is ' employee position ';
Comment on column Mgr
Is ' direct superior number ';
Comment on column hiredate
Is ' date of entry ';
Comment on column Sal
Is ' employee salary ';
Comment on column Deptno
Is ' department number ';

--Add a FOREIGN KEY constraint when building a table, plus cascade Delete
Constraint Fk_emp_deptno foreign KEY (DEPTNO) References Dept (deptno) on DELETE cascade

--add PRIMARY KEY constraint
ALTER TABLE EMP
Add constraint Pk_emp primary key (EMPNO);

--add FOREIGN KEY constraint
ALTER TABLE EMP
Add constraint fk_emp foreign key (DEPTNO)
References Dept (DEPTNO);

--Add a FOREIGN key constraint with cascading delete
ALTER TABLE EMP
Add constraint Fk_emp_deptno foreign key (DEPTNO)
References Dept (DEPTNO)
ON DELETE cascade;

--add Unique KEY constraint
ALTER TABLE EMP
Add constraint uk_emp_name unique (ename);

--ADD CHECK Constraint
ALTER TABLE EMP
Add constraint ck_emp_age Check (age between 0 and 150);

--add NOT NULL constraint
ALTER TABLE EMP
Modify ename NOT null;

--drop NOT NULL Contraint
ALTER TABLE EMP
Modify ENAME null;

--drop constraint
ALTER TABLE EMP
Drop constraint unique (name);

ALTER TABLE EMP
Drop constraint Fk_emp_deptno;

--Disabling constraints
ALTER TABLE EMP
Disable constraint Fk_emp_deptno;

--Enable constraints
ALTER TABLE EMP
Enable constraint Fk_emp_deptno;

--oracle connection divided into inner join (inner join) outer JOIN (outer join) Full join
--1.inner Join, Intersection
SELECT * FROM A inner join B on a.field1 = b.field2;

SELECT * from A, B where a.field1 = B.field2;

--Internal connection query can query the matching records, matching records cannot be queried

SELECT * FROM dept INNER join emp on dept.deptno = Emp.deptno;

SELECT * FROM dept, emp where dept.deptno = Emp.deptno;

--2.outer join, outer join, can be divided into left outer join right outer join outer joins starboard outer JOIN
-Requirements: (1). 2 tables, departmental tables and employee tables, one department has multiple employees, one employee can only correspond to one department, department and staff are 1 to many relationships
--(2) the departmental table and the employee table are related to the query, and to find out all the information of the Department
--Use the left connection query, the left JOIN query with the table in front of the main table, even if the record is not associated, the main table information can be queried
SELECT * from A LEFT join B on a.field1 = b.field2;

SELECT * FROM dept LEFT join EMP dept.deptno = Emp.deptno;

SELECT * from A, B where a.field1 = B.field2 (+);

SELECT * FROM dept, emp where Dept.deptno = Emp.deptno (+);

--the departmental and employee tables are associated with queries and are queried for all information about the employee table
---The right connection is the main table after you join the table, even if the record is not associated, the information of the primary table can be queried.

SELECT * from A right join B on a.field1 = b.field2;

SELECT * FROM dept right join EMP on dept.deptno = Emp.deptno;

--The external connection is not connected to the time, the part of the information to query out

--Fully connected full join, full join ... on ..., fully connected query result: Left OUTER join and right outer join query result set,
--even if some records are not associated, they can be queried for some information.
SELECT * from A full join B on a.field1 = b.field2;

SELECT * FROM dept full join EMP on dept.deptno = Emp.deptno;

SELECT * FROM dept full join EMP on 1 = 1;

Oracle INNER JOIN | Outer JOIN | Full Join | Add Constraint | Drop Constraint | Disable Constraint | Enable constraint