Last: Oracle Starter Three

Learning Video: https://www.bilibili.com/video/BV1tJ411r7EC?p=35

Oracle table connection: internal connection, external connection.The outer connection is divided into left connection and right connection.

In a multi-table query, if there is no conditional association between tables, all matching results will be found, for example, 6 data in table A and 7 data in table B, which will be 42.

--Cartesian product: It does not make sense to return all matched results from both tables.Therefore, for a multi-table query, the associated conditions must be written.
select * from staff,department where staff.id=department.staff_id;

If more than two tables are joined internally, each table must be directly or indirectly related.For example, if you look up tables A, B, C, and the condition A.A column = B.A column and A.A column = C.A column, tables B and C have indirect associations.

When associating multiple tables, one of the columns must be unique, such as lookup tables A, B, C, A.Column1 = B.Column2 and A.Column3=C.Column4, where at least one column of Column1 and Column2 is unique in its own table.

At least one column in Column3 and Column4 is unique in its own table.

Non-contour table join:

select ename,sal from emp e,salgrade g
where e.sal between g.losal and g.hisal 

--This losal to hisal range is unique
grade losal hisal
1    700    1200
2    1201    1400
3    1401    2000
4    2001    3000
5    3001    9999

Self-join: Associate yourself, but as a definition of different tables, self-join can generally be replaced by subqueries, which are clearer.

--Self-connect, will emp Think of it as two different tables, e Is the worksheet, m Is Leadership Table
select distinct m.empno, m.ename, m.sal
  from emp e, emp m
 where e.mgr = m.empno
--Self-join is less common and can be implemented using subqueries
  select * from emp m where m.empno in (select distinct mgr from emp)

Outer join: All data in the main table is selected, only matching data is selected from the table, and unmatched data is replaced by null.

The left outer join in the outer join, the main table on the left, and the table on the right.The right outer join in the outer join, the main table on the right, and the table on the left.

create table testa(
id number(3),
name varchar2(10),
age number(2)
);

insert into testa values(1,'a',23);
insert into testa values(2,'b',24);
insert into testa values(3,'c',25);
insert into testa values(4,'d',26);

create table testb(
id number(3),
salary number(8,2)
);

insert into testb values(1,9000);
insert into testb values(2,9000);
insert into testb values(3,9000);

select * from testa left join testb on testa.id=testb.id;

select * from testa right join testb on testa.id=testb.id;

Paging query: Paging query with the help of "rownum" pseudo column.

--Paging Query,Five entries per page, query second page data 6-10
--Use pseudo columns, not really in tables
select rownum from emp ;
select a.*,rownum from emp a;
select * from (select a.*,rownum r from emp a) x where x.r>5 and x.r<=10;

Innermost: All data that will be paged may be sorted and defined as an x-table.
select * from emp;
Middle tier: Start line numbering pseudo-columns, defined as y-tables
select x.*,rownum r from x;
Outermost layer: data filtering, paging begins
select * from y where y.r >0 and r<=10

Date addition and conversion:

--Query system date
select sysdate from dual;
--Go forward one day tomorrow
select sysdate+1 from dual;
--Go back one day yesterday
select sysdate-1 from dual;
--Today of next month
select add_months(sysdate,1) from dual;
--Today last month
select add_months(sysdate,-1) from dual;
-- Add 1 hour, 1 minute, 1 second
select sysdate+1/24+1/(24*60) + 1/(24*60*60) from dual