Database (Learning to organize)----7--oracle multi-table query, three join connections

aggregate function: (ignores null data) There are 5 commonly used: aggregating all the data in a field in one piece1, sum (field name): Sum2, AVG (field name): Averaging3, max (field name): Ask for maximum value4, min (field name): Minimum value5, Count (field name, *): Count Rows----2, by department number, query average salary, and average salary <1300 not displayed, results sorted in descending orderSelectEmpno,avg (SAL) asAvgsal fromScott.empgroup by empnohaving avg (SAL)>=1300Order by avgsal desc; Description: SQL statement execution order: must be from top to bottom! After the group by is executed, the name of the field that does not use the aggregate function in select must be written here after having had the alias of the field cannot be aggregated function, general field name order by can be followed by: field name, aggregate function, field alias note Meaning: Fault tolerance in Oracle 10g: The Having statement can be written before group by without an error, but the order of execution is actually performed after group by executes the HAVING clause description:1, the field must appear in the GROUP BY clause when there is an aggregate function and a field that does not use an aggregate function in a query! 2, GROUP By field 1, Field 2, a result set is first grouped by field 1, and then grouped by field 2!3、whereoccurs before GROUP by! 4、whereThere is no aggregation function behind it! Multi-table query: Table Connection classification: Inner connection, outer connection, cross connection1, INNER join: [inner] join on SQL syntax format: Syntax 1:Select* fromTable 1 [Inner] Join table 2 on table 1. Field 1 =table 2. Field 1; Syntax 2:Select* fromtable 1, table 2whereTable 1. Field 1 =table 2. Field 1; Description: The inner join and join in the INNER join IS equivalent! However, it is recommended to avoid omitting inner for readability of the program.!2, outer connection: Category: Left outer connection, right outer connection, full connection! 1, LEFT outer connection: Left OUTER join connection effect: All data in the table on the right is displayed, but the data on the table The fields on the left side match will be queried!            Otherwise it will show null! SQL syntax Format: Syntax 1:Select* fromtable 1 LEFT OUTER join Table 2 on table 1. Field 1=table 2. Field 1; Syntax 2:Select* fromtable 1 LEFT OUTER join table 2whereTable 1. field 1= table 2. Field 1 (+); 2, right outer connection: RIGHT outer join effect: All data in the table on the left will be displayed, but the data on the table And the fields on the right match will be queried!            Otherwise it will show null! SQL syntax Format: Syntax 1:Select* fromtable 1 Right outer join table 2 on table 1. Field 1=table 2. Field 1; Syntax 2:Select* fromtable 1 LEFT OUTER join table 2whereTable 1. Field 1 (+) =table 2. Field 1; 3, Full outer connection: full/All outer JOIN SQL syntax format:Select* fromtable 1 full outer JOIN Table 2 on table 1. Field 1=table 2. Field 1; 2, cross-linking: A Cartesian product query between table and table! SQL syntax Format: (unconditional query)Select* fromTable 1 Cross join table 2; orSelect* fromTable 1, table 2;

Database (Learning to organize)----7--oracle multi-table query, three join connections