Oracle BASICS (5): Multi-Table query and oracle basic table Query
I. Multi-Table query
(1) simple multi-Table query
1. Multi-Table query mechanism 1) SQL:
SELECT * FROM emp; -- 14 records SELECT * FROM dept; -- four records SELECT * FROM emp, dept; -- show 56 data records ?? Why?
2) Analysis:
- Select a record (deptno = 10) from dept and match the 14 records in emp respectively. 14 records are displayed.
- Select a record (deptno = 20) from dept and match the 14 records in emp respectively. 14 records are displayed.
- And so on.
2. Cartesian set,
- In multi-table queries, if no conditions are specified, the Cartesian set is used.
- Avoid: the query condition cannot be less than one minus the number of tables. (Two tables, must have one condition)
3. Multi-Table query instance 1) displays the employee name, employee salary, and department name
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno;
- The Department ID must be matched.
2) display the Department name, employee name, and salary with the department Number 10
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno and a.deptno=10;
3) display the name, salary, and salary level of each employee
SELECT a.ename,a.sal,b.grade FROM emp a, salgrade b WHERE a.sal between b.losal and b.hisal;
4) display the employee name, employee salary, and department name, and sort by department
SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno order by b.dname;
(2) Self-connection
1. Meaning: Self-join refers to the connection query on the same table, that is, a table is regarded as two tables.
2. Example 1) display the name of employee Ford's superior leadership
SELECT a.* FROM emp a WHERE a.empno = (SELECT a.mgr FROM emp a WHERE a.ename = 'FORD') ;
Analysis:
- First, the employee ID of FORD's supervisor is queried.
- Then, query the employee information based on the employee ID.
- A single subquery can be used to solve the problem, which is prone to errors: [multiple rows are returned for a single row subquery]
2) display the names of employees and their superiors
SELECT distinct. ename, B. ename, B. * FROM emp a, emp B WHERE. mgr = B. empno; -- SELECT distinct. ename, B. ename, B. * FROM emp a, emp B WHERE. mgr = B. empno (+); -- 14 data records
- [+] Is written on the right, indicating a left Outer Join. All the columns on the left are displayed.
- Analysis:
- Consider one emp table as two tables, and the mgr of the employee table is equal to the empno of the lead table.
- [King] No superiors, not displayed. External connections are required for display.
- Use self-connection to solve the problem.
(3) subquery *
1. Meaning: 1) subquery refers to the select statement embedded in other SQL statements, also known as nested query 2) Single Row subquery: A single row subquery is a subquery statement that returns only one row of data. 3) multi-row subquery: a multi-row subquery is a subquery that returns multi-row data.
2. instance 1) displays all employees (including SMITH) in the same department as SMITH (single row subquery: "= ")
SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' );
- Analysis
- SELECT a. deptno FROM emp a WHERE a. ename = 'Smith ', returns a row of records, that is, a single row of subqueries.
- By default, SQL statements are parsed from right to left, that is, subquery statements are parsed first.
- Note: order by cannot be used in the where subquery.
- SELECT a. * FROM emp a WHERE a. deptno = (SELECT a. deptno FROM emp a WHERE a. ename = 'Smith 'order by a. empno );
- Error: "missing right brackets"
2) query the names, positions, salaries, and department numbers of employees with the same job positions as department 10 (multi-row subquery: "in ")
SELECT a.ename,a.job,a.sal,a.deptno FROM emp a WHERE a.job in (SELECT distinct a.job FROM emp a WHERE a.deptno = 10) ;
- Analysis:
- First, query all jobs in department 10 and return multi-row results for multi-row subqueries.
- It shows that there is a same employee as his position.
- Easy to use error: [multiple rows are returned for a single row subquery]
3. all and any operators 1) display the names, salaries, and department numbers of employees whose salaries are higher than those of all employees in department 30.
- Method 1: Use max () Aggregate functions
SELECT a.* FROM emp a WHERE a.sal > (SELECT max(a.sal) FROM emp a WHERE a.deptno=30);
SELECT a.* FROM emp a WHERE a.sal >all (SELECT a.sal FROM emp a WHERE a.deptno=30);
2) display that the salary is higher than the name, salary, and department number of any employee in department 30.
- Method 1: Use the min () Aggregate Function
SELECT a.* FROM emp a WHERE a.sal > (SELECT min(a.sal) FROM emp a WHERE a.deptno=30);
SELECT a.* FROM emp a WHERE a.sal >any (SELECT a.sal FROM emp a WHERE a.deptno=30);
4. Multi-column subquery 1) Difference
- Single Row subquery: Single Row Single Column
- Multi-row subquery: multiple rows and single Columns
- Multi-column subquery: multiple columns
2) instance: Query all employees with the same department and position as SMITH
SELECT a.* FROM emp a WHERE (a.deptno,a.job) = (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );SELECT a.* FROM emp a WHERE (a.deptno,a.job) in (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
- "=" Is the same as that of "in.
5. Use subquery in the From clause. 1) display the information of employees higher than the average salary of their own departments.
SELECT a.ename,a.sal,b.avg_sal,a.deptno FROM emp a ,(SELECT avg(a.sal) as avg_sal,a.deptno as deptno FROM emp a group by a.deptno) b WHERE a.deptno = b.deptno and a.sal > b.avg_sal;
- Analysis:
- First, you need to know the average salary of each department, and then use it as a temporary table.
- Emp and temporary table multi-Table query
2) Find detailed information about the persons with the highest salaries in each department
SELECT a.ename,a.sal,t.max_sal , a.deptno FROM emp a, (SELECT max(a.sal) as max_sal,a.deptno as deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno and a.sal = t.max_sal;
- Get the highest salary of each department, as a temporary table
- Emp and temporary table multi-Table query
SELECT a.* FROM emp a WHERE a.sal = (SELECT max(b.sal) FROM emp b WHERE b.deptno = a.deptno ) ;
- This method does not display column data in the subquery.
3) display the information and personnel quantity of each department
SELECT a.*,t.count_empno FROM dept a, (SELECT count(a.empno) as count_empno,a.deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno(+);
- Obtain the number of employees in each department as a temporary table.
- Use dept tables and temporary tables for multi-table queries
- +: Indicates an external connection.
6. Tips
1) Row migration
create table temp# as select empno, ename from emp where ename like 'J%';
2) Worm Replication
insert into myTest (empno,ename,job) select empno,ename,job from myTest;
3) subquery updates data
update emp set (empno,ename,job) = (select empno,ename,job from emp where ename = 'SMITH') where ename='SCOTT';
(4) Merge Query
1. UNION: UNION set deduplication, sorting 1) Explanation: This operator is used to obtain the UNION of two result sets. When this operator is used, repeated rows in the result set are automatically removed.
SELECT a.* FROM emp a WHERE a.sal> 2500 UNION SELECT b.* FROM emp b WHERE b.job='MANAGER';
2. union all: the UNION is not heavy and not sorted.
SELECT a.* FROM emp a WHERE a.sal> 2500 UNION ALL SELECT b.* FROM emp b WHERE b.job='MANAGER';
3. INTERSECT: intersection
4. MINUS: The difference set. In short, the data in the second set is not displayed. 1) there is an intersection:
2) No intersection:
Ii. Paging
(1) paging of other databases
1. MySQL
1) Syntax:
Select * from table name where condition limit from the nth number, take several select * from emp where empno = 1234 limit;
2. SQL server1) Syntax:
Select top 2 * from table name where id not in (select top 4 id from table name where condition) to exclude the first 4, then take 2, actually take 5-6.
(2) Oracle:
1. Syntax: rownum
SELECT rownum, emp.* FROM emp where rownum <6；SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4；
2. Analysis:
- Analysis: rownum cannot directly use a greater than sign (>)
- Using layer-3 Nesting is actually using subqueries in from.
- Rownum layer-3 nesting, which has the highest efficiency in layer-2 and layer-3 respectively.
- Level 1: SELECT * FROM emp
- Layer 2: SELECT t. *, rownum num FROM (SELECT * FROM emp) t WHERE rownum <10
- Layer 3: SELECT * FROM (SELECT t. *, rownum num FROM (SELECT * FROM emp) t WHERE rownum <10) t2 WHERE t2.num> 4;
3. instance 1) query the number of instances from 7th to 10th in the order of entry time.
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT a.* FROM emp a order by a.hiredate ) t WHERE rownum < 10) t2 WHERE t2.num > 7；
- Sort and other special processing conditions, please process in the innermost layer
2) test and simulate 0.1 million of data
- Create tables and data:
- Create a new table using an existing table as the template: (row migration)
create table myTest as select empno,ename,job from emp;
- Self-replication (worm replication)
insert into myTest (empno,ename,job) select empno,ename,job from myTest;
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM myTest ) t WHERE rownum < 51010) t2 WHERE t2.num > 50997;
3. Internal and external connections
(1) Inner join 1. Overview: inner join is to use the where clause to filter Cartesian sets formed by two tables. 2. Format 1) common:
- Syntax: select column name... from table 1 Table 2... where Condition
SELECT a.ename,a.deptno,b.dname FROM emp a,dept b WHERE a.deptno = b.deptno;
2) [inner] Join on
- Syntax: select column name... from table 1 inner Join table 2 on (condition)
SELECT a.ename,a.deptno,b.dname FROM emp a inner join dept b on (a.deptno = b.deptno)
3. Internal Connection features:
- Only the records matching the two tables are displayed.
(2) outer join left | right | full [outer] join on 1, create 2 new tables:
2. left Outer join 1) features: left join
- Show all on the left table
- Right table matching
2) display the score of all people. If no score is displayed, the name and ID of the person must be displayed. The score is blank.
- External Connection:
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id(+); SELECT a.id,a.name,b.grade FROM stu a left join exam b on( a.id = b.id);
- Analysis:
- For internal join, two tables must match the records.
- Left Outer Join: The stu (left table) table does not match exam and needs to be displayed.
3. right Outer join 1) features: right join
- Match the left table
- Show all right tables
2) displays the left and right score. If no name matches, the result is blank.
SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id(+) = b.id; SELECT a.id,a.name,b.grade FROM stu a right join exam b on( a.id = b.id);
- Analysis:
- Right outer join: exam (right table) table does not match the stu table and needs to be displayed.
4. full outer connection 1) features: full outer join
- Show all on the left table
- Show all right tables
2) display the names of all scores and all people. If no matching value exists, the display is blank.
SELECT a.id,a.name,b.grade FROM stu a full join exam b on( a.id = b.id); SELECT a.id,a.name,b.grade FROM stu a full outer join exam b on( a.id = b.id);
- Analysis:
- Right outer join: exam (right table) table does not match the stu table and needs to be displayed.
5. Conclusion: 1) the Left and Right outer connections can be converted to each other. You only need to change the table location.
SELECT a.id,a.name,b.grade FROM stu a right join exam b on(a.id = b.id); SELECT a.id,a.name,b.grade FROM exam b left join stu a on( a.id = b.id);
2) Summary
6. exercises:
1) List department names and employee information of these departments, and list departments without employees
SELECT b.dname,a.* FROM emp a right join dept b on (a.deptno = b.deptno) order by b.deptno;SELECT b.dname,a.* FROM dept b left join emp a on (a.deptno = b.deptno) order by b.deptno;SELECT b.dname,a.* FROM emp a, dept b WHERE b.deptno = a.deptno(+) order by b.deptno;SELECT b.dname,a.* FROM emp a, dept b WHERE a.deptno(+) = b.deptno order by b.deptno;
Iv. Exercises
1. display the total number of employees and the number of employees who receive subsidies
<span style="font-family:Arial;font-size:18px;">SELECT count(*) ,count(comm) FROM emp;</span>
2. display the number of managers
SELECT count(distinct mgr) FROM emp;
3. display the average salary of each position in each department, the average salary of each department, and the average salary of each position.
<span style="font-family:Arial;font-size:18px;">SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );</span>
- Cube: cube function, which groups deptno, job, And deptno.
