Summary of oracle simple learning (4)-advanced Query

Oracle simple learning Summary (4) -- Advanced query links: oracle simple learning Summary (1) -- sqlhttp: // www.bkjia.com/database/201210/159545.html#oraclesimple learning Summary (2) -- Query Summary (3) -- function http://www.bkjia.com/database/201210/159561.html

1. Multi-table joint query multi-Table query can be established through a connection. Data queried in multiple tables can come from multiple tables, but appropriate connection conditions must exist between tables .. Generally N tables to join, need at least N-1 connection conditions, in order to correctly connect two, equal connection two tables with the same meaning of the columns, establish equal connection conditions. When two tables are queried using equal join, only rows with the same value in the two tables with the same join column will appear in the query results.
2.1 CREATE a TABLE: SQL: CREATE TABLE bookInfortable (id int NOT NULL primary key, pidint null, name varchar (50) NULL, publishdate date NOT NULL); (id, pid corresponds to the type, name, and publishdate of the bookTypetable id. SQL: CREATE TABLEbookTypetable (idint NOT NULL primary key, type varchar (50) NOT NULL, manager varchar (50) not null, payint not null );
(Id, type, manager Administrator,) SQL: CREATE TABLEpayGradeTable (id int NOT NULL primary key, Grade int NOT NULL, LOSAL NUMBER NOT NULL, HISAL NUMBER NOT NULL ); (id, Grade level, LOSAL minimum standard, HISAL highest standard)
2.2 equal connection SQL: SELECT. id,. name,. publishdate,. pid, B. id, B. type, B. manager FROMbookinfortable a, booktypetable bWHERE. pid = B. id. Note: The names of two tables are listed in sequence in the FROM clause. You must add the table name before each column of the table. columns are separated to indicate different tables. In the WHERE condition, specify the columns for equal join. If the table name is long, you can create an alias for the table to simplify the process. aliases are separated by spaces after the table name. (Bookinfortable a refers to bookinfortable alias a) 2.3 Equal connection additional Judgment condition SQL: SELECT. id,. name,. publishdate,. pid, B. id, B. type, B. manager FROMbookinfortable a, booktypetable bWHEREa. pid = B. id and. name! = 'Reader'; 3. External Connection
In addition to displaying records that meet equal connection conditions, the rows that do not meet the connection conditions are displayed at the end. The outer join operator is (+), which can appear on the left or right of equal join conditions. The meaning on the left or right is different. SQL: SELECT. id,. name,. publishdate,. pid, B. id, B. type, B. manager FROMbookinfortable a, booktypetable bWHEREa. pid (+) = B. id; SQL: SELECT. id,. name,. publishdate,. pid, B. id, B. type, B. manager FROMbookinfortable a, booktypetable bWHEREa. pid = B. id (+); Description: the rows in the table that do not meet the connection conditions are displayed after the left connection. The right link displays the rows that do not meet the connection conditions in the previous table. 4. Unequal connection SQL: SELECT B. id, B. type, B. manager, B. pay, p. grade, p. losal, p. hisal FROM booktypetable B, paygradetable pWHE RE B. pay BETWEEN p. losal AND p. hisal; Description: query the postmaster's salary level. Compare the postmaster's salary with the upper and lower limit of different salaries to obtain the salary level, and display the employee's salary level in the query results. 5. Self-join a table is connected with itself. For a self-join, you can imagine that two identical tables (copies of tables) exist. You can use different aliases to distinguish two identical tables. 5.1 create table SQL: create table workertable (idint not null primary key, name varchar (50) not null, manageridint NULL); 5.2 display employee name and employee manager name SQL: SELECT worker. id, worker. name | 'the manager is '| manager. name AS employee manager FROM workertable worker, workertable manager WHERE worker. managerid = manager. id order by worker. id. Note: Two aliases are created for the workertable: worker and manager. As you can imagine, the first table is the employee table, and the second table is the manager table, because the manager is also an employee. Then, the managerid field of the worker table is connected to the id field of the manager table. 6. Statistics query Oracle provides some functions to complete statistics. These functions are called group functions, group functions are different from the previous functions (single-row functions ). Group functions can sum and calculate the average value of grouped data. Group functions can only be used in SELECT clause, HAVING clause, or order by clause. Group functions can also be called statistical functions. Function Description AVG average COUNT, return non-empty rows, * returns all rows max min sum stddev for standard deviation, is used to calculate the VARIANCE Based on the VARIANCE obtained from the square root of the difference.

In grouping functions, SUM and AVG are only used for numeric columns. MAX, MIN, and COUNT can be used for character, value, and date columns. Group functions ignore column null values. You can use the group by clause to GROUP data. Grouping refers to dividing records into groups based on the same content of columns, and grouping functions can be applied to groups. If grouping is not used, the group function is applied to the entire table or the records that meet the conditions. The DISTINCT or ALL keyword can be used in group functions. ALL indicates that ALL non-NULL values (repeatable) are operated (except COUNT ). DISTINCT indicates a non-NULL value. If a duplicate value exists, the group function operates only once. If the preceding keyword is not specified, the default value is ALL. 6.1 COUNTsql: select count (*) FROM bookinfortable t;

Note: Use the group function COUNT to COUNT the number of records. * indicates that all rows are returned. Otherwise, non-NULL rows are returned. SQL: select count (pid) FROM bookinfortable t; Description: The number of non-null pid values returned. SQL: SELECT COUNT (DISTINCT pid) FROM bookinfortable; description: this query returns the number of different PIDs in the bookinfortable table, excluding null. If DISTINCT is not added, the number of non-null values is returned. 6.2 AVGsql: select avg (t. pay) FROM booktypetable t WHERE t. pay> 2000; Note: The WHERE condition is added. The WHERE condition is executed first. The maximum dates of 6.3 MAX (), MIN () SQL: SELECT MAX (publishdate) are evenly distributed, MIN (publishdate) Minimum date FROM bookinfortable; 6.4 grouping statistics prerequisites: Add a row of data of the same type to booktypetable SQL: SELECT type, SUM (pay) FROM booktypetable GROUP BY type; description: group by type. groups are divided into different types, and the wages of the same group are summed. Group query allows you to include group columns in the query list. You cannot use columns other than group columns in the query column. Otherwise, an error is generated. 6.5 HAVING: it is important to distinguish having from where 1. having clauses and where are statements with set conditions.
2. execution sequence: where clause> (sum, min, max, avg, count)> having Clause 3. having is used for grouping data determination again. Having is not used if you do not use these links. Simply use where. Where SQL: select t. type, t. manager, max (t. pay) from booktypetable t where t. manager! = 'Li yun' group by (t. type, t. manager); having SQL: select t. type, max (t. pay) from booktypetable t group by t. type having max (t. pay)> 3500; where and having SQL: select t. type, t. manager, max (t. pay) from booktypetable t where t. manager! = 'Li yun' group by (t. type, t. manager) having max (t. pay)> 3500; Note: group functions must appear in the limitations of HAVING clauses. If both the WHERE condition is used, the WHERE condition is executed before the group, and the HAVING condition is executed after the group. 6.6 order by sorting SQL: select t. type, t. manager Administrator, t. pay salary from booktypetable t order by salary; Note: order by can be followed by an alias or nested with the database Field 6.7 group of functions using SQL: select min (avg (t. pay) from booktypetable t group by t. type; error: select t. type, min (avg (t. pay) from booktypetable t group by t. type; this statement is incorrect, because the minimum average salary of each type does not belong to a certain department. Note: This query first calculates the average salary of each department and then obtains the maximum value. Although there are grouping columns in the query, grouping Columns cannot appear in the query field. 6.8 subqueries use the results of one query as part of another query .. A subquery is executed before the primary query. The results are used as the conditions for the primary query. in writing, the subquery is expanded with parentheses and placed on the right of the comparison operator. Subqueries can be nested, with the latest layer of queries first executed. Subqueries can be used in SELECT, INSERT, UPDATE, DELETE, and other statements. SQL: SELECT B. * FROM booktypetable B WHERE B. pay> (SELECT t. pay FROM booktypetable t WHERE t. manager = '王'); SQL: SELECT B. * FROM booktypetable B WHERE B. pay> (SELECT t. pay FROM booktypetable t WHERE t. manager = 'zhao yuan') and B. type = (SELECT t. type FROM booktypetable t WHERE t. manager = 'zhao yuan'); Note: The two subqueries appear in two conditions, AND are connected with AND to indicate that they must be met at the same time. You can also use group functions in subqueries. SQL: SELECT t. manager, t. pay FROM booktypetable t WHERE t. pay> (select avg (pay) FROM booktypetable); 6.9 add data SQL to the original table for multi-row subqueries: SELECT B. * FROM booktypetable B WHERE B. pay <ANY (SELECT t. pay FROM booktypetable t WHERE t. type = 'journal ') AND B. type <> 'journal '; Description: If a subquery returns results of multiple rows, it is called a multi-row subquery. Multi-row subqueries use different comparison operators, including IN, ANY, and ALL. 6.10 multi-column subquery if the subquery returns multiple columns, multiple columns should also appear in the corresponding comparison condition. This query is called a multi-column subquery. For example, query information about employees with the same title and department as SCOTT. * Emphasize rownum query again
6.10 Set Operations describe the UNION, merge the results of the two operations, remove the duplicate part of the union all union set, merge the results of the two operations, and retain the duplicate part of the MINUS difference set, remove the INTERSECT intersection that is the same as the result of the subsequent operation from the previous operation result, and take the same part of the two operation results.