Interpretation and application of select from where group by having order by in oracle query statements

In the oracle query statement, the interpretation of select from where group by having order by and the keywords used in the application query mainly include six, and their order is select -- from -- where -- group by -- having -- order by, where select and from are required, and other keywords are optional, the execution sequence of these six keywords is not the same as that of the SQL statement, but is executed in the following order: from -- where -- group by -- having -- select -- order, from: The data table from which to retrieve the data where: filter the table data condition group by: How to group the data filtered above having: filter the data grouped above select: view the column in The result set, or the calculation result order by of the column: view the returned data in what order.
Select keyword www.2cto.com 1. Use * to replace all columns select * from emp; 2. Specify the columns to be returned select ename, deptno, job from emp; 3. select ename emloyee_name as the column alias, job, deptno from emp; 4. Remove the same data in a column select distinct deptno, job from emp; 5. Return the data created in the two columns after calculation. create table tab1 (first_col number (3), second_col number (3); insert into tab1 values (3, 6 ); insert into tab1 values (); select first_col, second_col, first_col + second_col from tab1; the returned results are as follows: FIRS T_COL SECOND_COL FIRST_COL + SECOND_COL ---------- ------------------------ 3 6 9 1 10 11 6. After performing an operation on a single column, return select first_col, second_col, first_col * 10 from tab1; the result is as follows: FIRST_COL SECOND_COL FIRST_COL * 10 ---------- ------------ 3 6 30 1 10 10 7. select 1 + 2 from dept; the returned result is www.2cto.com 1 + 2 --------- 3 3 3 3 3, which seems meaningless. Four rows appear because there are four departments, execute 1 + 2 for each department and return. If a table is created with only one data entry, only one data entry will be returned. This table dual exists in oracle. In the past, we often saw such a statement select sysdate from dual; sysdate----------------05-february 12-12 indicates that the system date is returned. If such a statement is executed, use the tab1select sysdate from tab1 table created above; let's see what the result will be. sysdate-----------05-february 1, August-1205-8-12 returns two dates, because table tab1 contains two data, which also indicates that the returned data is only a constant, it is not obtained from the dual table. It only utilizes the characteristics of a data entry in the dual table. This table can be created by itself, the results will also be the same as those of www.2cto.com dual. A simple where keyword query condition generally contains a comparison operator (<<=>+=<>) A statement composed of comparison operators can be judged to be true and false. If it is true, the row is combined with the condition and returned to the user as the data retrieved by the user, for example, select * from emp where sal> 4000; if you want to retrieve the returned data by the order keyword, you need to use the order by keyword, select * from emp order by ename desc nulls last; select * from emp order by ename asc nulls first; asc means to sort by specified column in ascending order, desc is arranged in descending order. nulls first indicates to display blank rows at the beginning, and nulls last indicates to display the row where empty data is located at the end, for nulls in ascending order, the last value is recognized. If nulls first is sorted in descending order, the default value is select * from emp order by comm; equivalent to select * from emp order by comm asc nulls last; select * from emp order by comm desc; equivalent to select * from emp order by comm desc nulls first; if select * from emp order by deptno asc, ename desc is sorted by multiple fields, deptno is first sorted in ascending order, the same deptno sort operator in descending order of names and or not conditions are judged to be true or falsetrue or... --> truetrue and true --> truefalse or false --> falsefalse and... --> false for example, select 'true or false' from dual where (1 = 0) or (1 = 1 ); the returned result is 'trueorfalse' ------------- true or false select 'true or false' from dual where (1 = 0) and (1 = 1 ); if no rows selected is returned, the not operator can be placed in front of any condition. true and false are reversed, for example, www.2cto.com select * from emp where deptno> 20; select * from emp where not deptno> 20; (Note that not must be placed before the entire condition) The between in likebetween operator uses a range value to filter data, the two sides of this range are closed intervals, such as select * from emp where sal between 2850 and 4000; the between operator in is used to compare the values of a column or the results calculated using expressions with several separate value lists, such as select * from emp where sal in (2850,3000, 5000 ); the like operator, the expression behind the like operator can be in the following situations: the value of the '% AB' column is the last two letters of the AB '% AB %' column, the value contains AB, note that AB is the value of the 'AB %' column. The first two letters are the value of the 'AB' _ AB % 'column. The second and third letters are AB, where _ is a placeholder, select * from emp where ename like '_ MI %'; case expression, simple syntax format such as select ename, case deptnowhen 10 then '10 Department 'when 20 then' 20 Department 'when 30 then' 30 Department 'when 40 then' 40 Department 'else' does not have this department 'endfrom emp; the second expression of case, such as select ename, deptno, sal, case www.2cto.com when sal between 1000 and 2000 then 'salary relatively low 'when sal> 2000 and sal <3000 then' salary can also 'when not sal <3000 then' salary relatively high 'else 'salary too low 'end as salaryfrom emp order by sal; the difference between a group bygroup by statement and a query statement without group by is that, without group by, no matter what the select clause is, all operations are performed on a row of data that meets the where condition. After group by is used to group data based on a column, we can perform statistics and query on the group, for example, it is limited to count the total number of the group and query the maximum and average values of the group using group, that is, the columns following the select statement can only be grouped by the column. The grouping function (count sum max min avg median stats_mode stddev variance) is used for the column) select count (*), avg (sal), deptno from emp group by deptno; Result: COUNT (*) AVG (SAL) DEPTNO ---------- 6 1566.66667 30 5 2175 20 3 2916.66667 10 having group by grouping data by a column, some groups are not what we want, in this case, you can use having to filter groups. For example, if you want a department with more than five employees, you can select count (ename ), deptno from emp group by deptno having count (ename)> 5; the result is as follows: www.2cto.com COUNT (ENAME) DEPTNO ------------ ---------- 6 30 only shows departments with more than 5 employees, having filters out the difference between where and having for departments with less than or equal to 5. Although the difference between where and having serves as a filter, where is for a row of data, and having is for a group of data, where can determine which row to return, having can determine which group to return, and the grouping function is not allowed in the where clause, but the non-grouping function select deptno can be used in the having clause, count (*) from emp group by deptno having deptno> 20; however, the execution efficiency of such statements is lower than that of select count (*), deptno from emp where deptno> 20 group by deptno; Author: wudiisss