Query criteria:
1) Like: Fuzzy query, need to use two wildcard characters,%: represents 0 to more characters; _: Identifies a single character.
2) in (list): Used to remove data from the matching list range.
3) not in (list): Remove data records that do not conform to this list.
4) between ... And ...: Used to query data that conforms to a range of domain criteria, most commonly using data ranges of numeric types, but also for character types and date type data. For Example:select ename, sal from EMP WHERE sal between and 3000;
5) is null: null value is a special value, you cannot use the "=" number when comparing, you must use is NULL, otherwise you will not get the correct result.
6) is not NULL: opposite to 5.
7) >any: Greater than minimum <any: less than maximum >all: greater than maximum <all: less than minimum
For Example:select empno, ename, Job, Sal, deptno from emp WHERE sal > any (345,3333,123)
8) DISTINCT: Filter repeat. For Example:select DISTINCT deptno from emp
9) Order BY: Sorts the queried data according to a certain rule, ASC specifies ascending, desc specifies descending.
GROUP BY: The resulting data is grouped by a given field, for example, by dividing the entire data table into groups by department.
ONE) The having:having clause is used to further limit the results after grouping, for example, after grouping by department, get the highest salary for each department, can continue to limit the output results. Must follow the group by and cannot exist alone. For example: SELECT deptno, Max (SAL) max_sal from emp GROUP by Deptno have MAX (SAL) >4000;
order of execution of query statements:
1. FROM clause: The order of execution is from back to forward, right to left. A table with a small amount of data is placed as far back as possible.
2. Where clause: The execution order is bottom-up, right-to-left. The criteria that will filter out the maximum number of records is written to the right of the WHERE clause.
3. GROUP BY: The execution sequence is grouped from left to right, preferably by using where before group by to filter out unwanted records before group by.
4. Having clause: consumes resources. Try to avoid it, having the result set filtered after all the records have been retrieved, ordered, and so on.
5. Select clause: Use the * number sparingly and take the field name as much as possible. During the parsing process, ORACLE converts the * number to all column names in turn by querying the data dictionary, which consumes time.
6. ORDER BY clause: The order of execution is ordered from left to right and consumes resources.
Some query statements in Oracle and their order of execution
