Multi-table Query SQL statement

Multi-table Query SQL statement

1--Unlock Scott users 2 alter userScott account unlock 3--Retrieves the specified column 4 select Job,ename,empno fromEmp 5--With expression Yes SELECT clause 6 select sal* (1+0.2), Sal fromEmp 7--Show non-repeating records 8 select distinct job fromEmp 9--Comparison filter <> =10 select Empno,ename,sal from emp where sal>1000; Select Empno,ename,job fromEMP;12 Select Empno,ename,sal from emp where Sal <>all (3000,950,800); 13--Special keyword filter--like fuzzy query for empno,ename,job from EMP where job like '%s ';--in--varchar17 Select Empno,ename,job from emp where job in (' President ', ' MANAGER ', ' ANALYST ')--not IN19 select Empno,ename,job from emp where job is not in (' President ', ' MANAGER ', ' ANALYST ' );--betwee N-numer, Inter21 select Empno,ename,sal from emp where Sal between and ;--not BETWEEN23 Select Empno,en Ame,sal from EMP where Sal not between and--is , null/is not null25 select * from EMP where Comm was not Null ; 26--Logical filter--and, or,not relationship to-or--non-select empno,ename,sal from EMP where (sal>=2000 and sal<=3000; Select Empno,ename,sal from emp where sal<2000 or sal>3000 ; 30--Group query to select Deptno,job from EMP gr OUP by Deptno,job ORDER by  Deptno; Select Deptno as department number, AVG (SAL) as average salary from EMP GROUP by  deptno;33 Selec T Deptno as department number, AVG (SAL) as average wage from EMP GROUP by DEPTNO have avg (SAL) >2000; --group by sub-condition having 34--sort query order by; DESC: Reverse ASC default of Select Deptno,empno,ename from emp order by Deptno,empno;       

Multi-table Query SQL statement Seven sample diagramWhen creating relational data tables, according to the requirements of the database paradigm, in order to reduce the data redundancy, provide data maintenance flexibility to divide the data into multiple tables for storage, the actual work requires more than one table of information, need to merge multiple tables display

Multi-table Query SQL statement code

1--Internal connection 2 Select E.empno as employee number, E.ename as employee name, D.dname asDepartment 3 from EMP e inner joins dept D on E.deptno=D.deptno; 4 5--LEFT outer connection 6 INSERT INTO EMP (empno,ename,job) VALUES (9527, ' EAST ', ' salesman '); 7 8 Select E.empno,e.ename,e.job,d.deptno,d.dname from emp e left JoinDept D 9 on e.deptno=D.DEPTNO;10--right outer connection one to one select E.empno,e.ename,e.job,d.deptno,d.dname from emp e r joinDept D12 on E.deptno=D.deptno;13 14--Fully connected to the E.empno,e.ename,e.job,d.deptno,d.dname from EMP e full joinDept D16 on E.deptno=d.deptno;17 18--Natural connection (a common attribute that removes duplicate columns) Select Empno,ename,job,dname from EMP Natural Join dept where sal>2000 ; If you do not commit, the following tablespace will be written all the time. Redo (log tablespace) undo (log backup tablespace) 23 commits: Commit rollback: rollback24 25--Right OUTER join filter * From the EMP e right Join dept D in e.deptno=d.deptno27 where e.deptno is null; 28--Left outer connection filtering, select * from emp E Join Dept D on E.deptno=d.deptno30 where d.deptno is null; 31--Full outer join filter from + SELECT * from emp e fully join dept D on E. deptno=D.deptno, where d.deptno is null or E.DEPTNO is null; 34 35 36/* Self-join is a frequently used connection in SQL statements, and self-joins can be used to A mirror image of a table is treated as another table, allowing for some special data to be obtained. 37 Each of the employees in the EMP has their own MGR (manager), and each manager himself is the employee of the company and has his own manager. */38 Select Em2.enname Manager, em1.enname subordinate employee from EMP Em1 left join EMP em2 on Em1.mgr=em2.empno ORDER by em1.mgr;39 40 /* Cross join without a WHERE clause, which returns the Cartesian product of all data rows of the two connected tables, returns the number of rows in the result set equal to the number of data rows in the first table that match the query criteria, multiplied by the number of data rows in the second table that meet the query criteria */41 SELECT COUNT (*) from Dept Cross join EMP; 

RowNum Pseudo-Column

1 Select Rownum,empno,ename  from emp where deptno=20; 2--Returns the first 5 employee messages in the EMP table 3 select RowNum seq,empno,ename,sal from  EMP where rownum<=5; 4  5--query data from 2 to 5th of the EMP table 6 select RowNum seq,empno,ename,sal from EMP where rownum>=2 and Rownum<=5; 7  8/*rownum The value of the column is reset, for example, in the 1th row, where the sub condition is not true, the 1th row is discarded, 9 but when a row is removed, the rownum is reset to 1 instead of 2, resulting in RowNum can never fetch the correct value 10 cannot return any row data */11 select * from emp;12 select Seq,empno,ename,sal from (select RowNum seq,empno,ename, Sal from EMP)-where seq>=2 and seq<=5    

The role of foreign KEY constraints
A foreign key is a field in which the table is joined by another table, and the foreign key must be the primary key foreign key in another table to ensure the integrity of the data. It typically includes the following: entity integrity, ensuring that each entity is unique (implemented by a primary key) 1 Domain integrity, ensuring that attribute values are selected only from a specific set of optional collections2 Association integrity, ensuring that each foreign key or null (if allowed) or containing a value that matches the associated primary key value
Table CRUD Operations with foreign KEY constraints
1 Add Data: Insert the appearance first, then insert the main table
2 Delete data: First delete the main table and then delete the appearance,
3 has the corresponding record value, cannot revise the forefront;

Multi-table Query SQL statement