Shulou(Shulou.com)06/01 Report--

one。 SQL basic query

Basic query statement

1. FROM clause

SELECT * FROM TABLE

two。 Use aliases

3. WHERE clause

Use the comparison operator in the WHERE clause to restrict query results

4. SELECT clause

Query condition

1. Use >, =, 1000 AND job = 'clerk'

SELECT * FROM table WHERE sal > 1000 OR job = 'clerk'

When using both AND and OR, AND takes precedence over OR

3. Use LIKE condition (fuzzy query)

LIKE requires the help of two wildcards:

-%: indicates 0 to more characters

-_: identifies a single character

SELECT name, job FROM amp WHERE name LIKE'_ A% destroy-the second character of the word is unknown after A.

4. Using IN and NOT IN

The comparison operator IN (list) is used to fetch data that matches the range of the list.

List represents a list of values. When a column or expression matches any of the values in the list, the condition is TRUE and the record is displayed.

IN can be understood as a range comparison operator, but the range is a specified list of values

NOT IN (list) fetches data records that do not match this list

5. BETWEEN... AND...

BETWEEN... (small value) AND... (large value): the operator is used to query data that meets the criteria of a range.

The most common use is in the data range of numeric types, but it also applies to character type and date type data

SELECT name, sal FROM amp WHERE sal BETWEEN 1000 AND 2000

6. Using IS NULL and IS NOT NULL

You cannot use "=" when comparing NULL, you must use IS

7. Use ANY and ALL conditions

ALL and ANY cannot be used alone, but need to be combined with the single-line comparison operator >, ANY (2000, 3400, and 4000)

What it has in common with IN: give a set of data for comparison

Difference: IN is equivalent or not equivalent to given data

ANY and ALL are range comparisons with given data

8. Use expressions and functions in query conditions

Arithmetic expressions can be used in query conditions: +, -, *, /. Priority is in line with the default priority of the four operations. You can use parentheses to change the priority.

9. Use DISTINCT to filter duplicates

To remove weight from multiple columns, the effect that can be achieved is that the combination of these columns is not repeated.

SELECT DISTINCT deptno from table;-remove duplicate values

Sort

1. Use the ORDER BY clause

Sort the data according to certain rules, using the ORDER BY clause

The last clause that must appear in the SELECT

2. ASC and DESC

ASC: ascending order

DESC: descending order

3. Multi-column sorting

When sorting with multiple columns, the column on the left takes precedence over the column on the right

Eg:SELECT name,sal,deptno FROM emp ORDER BY sal,deptno DESC

First, it is arranged in ascending order of sal, and when the values of sal are the same, they are arranged in descending order of deptno. If there are no duplicate values in the sal table, the sort of the second column will be ignored.

Aggregate function

1. Aggregate function

Multiple rows of data participate in the operation to return a row of results, also known as grouping functions, multiline functions, aggregate functions.

2. MAX and MIN

Used to get the maximum or minimum value of a column or expression

Can be used to count any data type, including numbers, characters and dates

SELECT MAX (sal) max_sal, MIN (sal) min_sal FROM emp

MAX and MIN cannot appear in WHERE statements.

3. AVG and SUM

The aggregate function ignores null values. So the average will have less data.

Eg:SELECT AVG (NVL (comm, 0)) FROM - is 0 if it is null

4. COUNT

Used to count the number of records

SELECT COUNT (comm) FROM - ignores null values and is not recorded if the field comm is kong

5. The treatment of null value by aggregate function

Grouping

1. GROUP BY clause

SELECT MAX (sal), MIN (sal), SUM (sal), AVG (sal) FROM emp GROUP BY deptno;-get the maximum salary, minimum salary, average salary, total salary of each group according to the partial grouping

Make statistics according to the records with the same value as a group

Generally, the values that are duplicated in part of the data are grouped, and the grouping of data without duplication is meaningless.

As long as grouping functions are used in SELECT, columns of other non-grouping functions in SELECT must also appear in the GROUP BY clause, and vice versa.

If there are multiple columns in the GROUP BY, records with the same combined values are treated as a group.

two。 Grouping query

3. HAVING sentence

WHERE is used to filter the whole table row by row.

HAVING is used to filter data for the second time after a grouping query.

The results of grouping functions can be used to filter in HAVING.

HAVING cannot exist on its own and must follow GROUP BY.

Eg: select departments whose average salary is higher than 1800: SELECT deptno, AVG (sal) FROM emp GROUP BY deptno HAVING AVG (sal) > 1800

SQL order: SELECT-> FROM-> WHERE-> GROUP BY-> HAVING-> ORDER BY

Query statement execution order

1. Query statement execution order (can improve code execution efficiency)

The query statements are executed in the following clause order:

1.1 from clause: execution order is from back to front, from right to left

A small number of watches should be put at the back as far as possible.

1.2 where clause: the execution order is from bottom to top, from right to left

Write the condition that can filter out the maximum number of records at the far right of the where clause

1.3 group by execution order grouped from left to right

It is best to use WHERE before GROUP BY to filter out unwanted records before GROUP BY

1.4 having clause: consume resources

Avoid using it as much as possible. HAVING will filter the results only after all the records have been retrieved, requiring sorting and other operations.

1.5 select clause: use the * sign less and take the field name as much as possible

In the process of parsing, ORACLE converts the * sign into all column names in turn by querying the data dictionary, which takes time.

1.6 order by clause: the order of execution is from left to right, consuming resources

two。 SQL Association query

Association basis

1. The concept of relevance

A query that queries two or more data tables or views is called a join query

Join queries are usually built between parent and child tables that care about each other.

two。 Cartesian product

Cartesian product means that each row of each table doing the associated operation is combined with every row of the other table. Assuming that the number of records of the two tables is X and Y respectively, the Cartesian product will return the X records.

Cartesian product is generated when two tables are not associated with join conditions.

3. Equivalent connection

SELECT table1.column, table2.column FROM table1, table2 where table1.column = table2.column; (column is the primary key and foreign key of table1 and table2, respectively)

NATURAL JOIN: automatically find two fields with the same column name (and when the two fields are related to each other as the primary foreign key) to make an equivalent join. Note: only one column with the same name in the two tables can use natural join.

Association query

1. Internal connection

two。 External connection

Application scenario: the value of a field in table An is used when the value of the corresponding field in table B cannot be found.

SELECT table1.column, table2.column FROM table1 LEFT | RIGHT | FULL | OUTER JOIN table2 ON table1.column = table2.column2

LEFT: the table on the left side shall prevail, while the table on the right side is insufficient.

RIGHT: the table on the right side shall prevail, while the table on the left side is insufficient.

3. Full external connection

The left table is not enough, and the right table is not enough.

4. Self-connection

It is most commonly used in employee superior-subordinate relationship.

Treat one table as two tables

Eg:SELECT e.name | | 'the leader is' | | m.name FROM amp e, emp m WHERE e.mgr = m.empno

three。 SQL Advanced query

Subquery

The current query needs to be based on the results of another query, and here we need to make use of the subquery

1. The subquery is in the where clause

In a SELECT query, the constraint in the WHERE query condition is not a definite value, but comes from the result of another query

The query statement that is first executed to provide data to the query is called a subquery

Subqueries are SELECT statements embedded in other SQL statements, which appear in the WHERE clause most of the time

The statement embedded in the subquery is called the main query or the parent query

The main query can be a SELECT statement or other types of statements such as DML or DDL

According to the returned results, subqueries can be divided into single-row subqueries, multi-row subqueries and multi-column subqueries.

Single-row single-column and multiple-row single-column subqueries are usually used as conditions in the WHERE clause

Multi-row and multi-column subqueries are usually used in the From clause

>, =, ANY (SELECT sal FROM amp WHERE job = 'SALESMAN');-- greater than one of them

SELECT name, sal FROM amp WHERE sal > ALL (SELECT sal FROM amp WHERE job = 'SALESMAN');-greater than the maximum

SELECT name, sal FROM amp WHERE sal > IN (SELECT sal FROM amp WHERE job = 'SALESMAN');-equal to one of them

In a subquery, you need to reference the field data of the main query, using the EXISTS keyword

If the subquery after EXISTS returns at least one row of data, the entire condition returns TRUE

Eg: query those employees whose salary is higher than the average salary of their department.

SELECT e.1deptno, e1.name, e1.sal FROM emp e1

WHERE e1.sal > (SELECT AVG (sal) FROM emp e2 WHERE e1.deptno = e2.deptno)

two。 The subquery is in the having clause

Query the department information that lists the minimum salary that is higher than 30% of the department:

SELECT deptno, MIN (sal) min_sal FROM emp

GROUP BY deptno

HAVING MIN (sal) > (SELECT MIN (sal) FROM emp WHERE deptno = 30)

3. The subquery is in the FROM section

If a subquery appears in FROM, you need to treat the result of the subquery as a table and query the desired results from it. Note at this point: fields that are not field names appear in the SELECT statement of the subquery, usually expressions or functions, so be sure to give them aliases.

Eg: find out the information about employees whose salary is higher than that of their department:

SELECT e.deptno, e.name, e.sal FROM emp e, (SELECT deptno, AVG (sal) avg_sal FROM emp GROUP BY deptno) x

WHERE e.deptno = x.deptno and e.sal > x.avg_sal ORDER BY e.deptno

4. The subquery is in the SELECT section

Another way to write external connections, which is not commonly used.

Eg: query name, salary, department name

SELECT name, sal, (SELECT name FROM deptno d WHERE d.deptno = e.deptno) FROM amp e

Paging query

1. ROWNUM

Called pseudo columns, it returns numbers that identify the order of row data.

Can only be counted from 1 and cannot be intercepted directly from the result set

ROWNUM is when oracle select select when querying table data, it is determined that a piece of data needs, it will be numbered (fake and inferior value). As a result, rownum will never have a value until no data is found. Therefore, rownum cannot be used as a where condition in the first query.

Rownum: only when there is data, there is value.

Appears in where: only when there is value, there is data.

Eg:SELECT ROWUM,empno,name,sal FROM emp WHERE rownum > 3Mutual-query result not available

two。 Paging using subqueries

There are three steps in paging:

a. Sort

b. Serial number

c. Fetch range

Eg:

A. SELECT empno, name, sal FROM emp ORDER BY empno

B. SELECT ROWNUM rw, e.* FROM () e

C. SELECT * FROM () WHERE rw BETWEEN 1 AND 3

SELECT * FROM (SELECT ROWNUM rw, e.* FROM (SELECT empno, name, sal FROM emp ORDER BY empno) e) WHERE rw BETWEEN 1 AND 3

Paging algorithm: number of page pages, number of pagesize pages

Starting position: (page-1) * pagesize+1

End position: page*pagesize

MYSQL:limit (1pr 3)

3. Paging and ORDER BY

DECODE function

1. Basic syntax of DECODE function

DECODE (expr, search2, result1 [, search3, result2...] [, default])

DECODE is used to compare the value of parameter expr. If any search condition is matched, the corresponding result result is returned.

There can be multiple sets of search and result correspondence. If none of the search conditions match, the value of the last default is returned.

The default parameter is optional. If no default parameter value is provided, NULL will be returned if there is no match.

2. Application of DECODE function in grouping query

Eg: check the staff table and calculate the bonus amount according to the position of the employee. When the position is MANAGER,ANALYST,SALESMAN, the reward amount is 1.2,1.1,1.05 times of the salary, respectively. If it is not for these three positions, the reward amount will be paid.

SELECT name, job, sal, DECODE (job, 'MANAGER', sal * 1.2)

'ANALYST', sal * 1.1

'SALESMAN', sal * 1.05

Sal) bonus

FROM emp

Similar to the DECODE function is the CASE statement, which implements an operation similar to if-else:

SELECT name, job, sal, CASE job WHEN 'MANAGER' THEN sal * 1.2

WHEN 'ANALYST' THEN sal * 1.1

WHEN 'SALESMAN' THEN sal * 1.05

ELSE sal END bonus

FROM emp

When DECODE is used in ORDER BY, you can customize the collation:

SELECT * FROM dept ORDER BY DECODE (name, 'OPERATIONS', 0,' ACCOUNTING', 1, 'SALES', 2, 3)

Sorting function

1. ROW_NUMBER

ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col2)

Indicates grouping according to col1 and sorting according to col2 within the grouping

The value calculated by this function represents the ordered number within each group, which is continuous and unique within the group.

Rownum is a pseudo column, and ROW_NUMBER is more powerful, so you can fetch a subset directly from the result set.

Eg: grouped according to department, salary in reverse order within department, continuous and unique number generated within department

SELECT name, sal, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM emp

2. RANK

Generate discontinuous and non-unique numbers within the group. If the sorted columns have the same value, you will get the same number.

Discontiguous sort, if there is a tie for the second, the next sort will be four

Eg: grouped by department, salary in reverse order within department, discontinuous and non-unique number generated within department

SELECT name,sal,deptno RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM emp

3. DENSE_RANK

Generate consecutive but not unique numbers within the group. If the sorted columns have the same value, you will get the same number.

Continuous sort, if there is a juxtaposition of the second, the next sort will be three, which is different from RANK, RANK is a jump sort

Eg: grouped according to department, salary in reverse order within department, continuous but not unique number generated within department

SELECT name,sal,deptno DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM emp

Set operation

1. UNION,UNION ALL

In order to merge the results of multiple SELECT statements, you can use the set operator to achieve the union, intersection, and difference of the set.

Collection operators include UNION,UNION ALL,INTERSECT and MINUS

The number of columns and data types of multiple SELECT statements for collection must match

The ORDER BY clause can only be placed in the last query statement

Syntax for collection operations:

SELECT statement1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT statement2

The repeated results of UNION:statement1 and statement2 are merged, and the repeated results appear once.

The repeated results of UNION:statement1 and statement2 are not merged, and several repeated results appear several times.

2. INTERSECT

The intersection of two results is obtained, and only the data that exists in both result sets is displayed and output.

The result set after using the INTERSECT operator sorts in ascending order with the data in the first column

3. MINUS

Get the difference between two result sets

Only if it exists in the first result set, the data that does not exist in the second result set will be displayed, that is, the result of result set one minus result set two.

Advanced grouping function

1. ROLLUP

Eg: calculate the monthly sum, the annual sum

SELECT year_id, month_id, SUM (sales_value) AS sales_value FROM sales_tab GROUP BY ROLLUP (year_id, month_id) ORDER BY year_id, month_id

2. CUBE

GROUP BY CUBE (a _

For each parameter of CUBE, it can be understood that the value is a dimension that participates in grouping and does not participate in grouping, and the set of values of all dimensions is the set after grouping.

For the CUBE with n parameters, the combination form has 2 ^ n times, and if it is a pharem, it is 2 ^ 3 combinations.

First, GROUP BY (GROUP BY CUBE), then (a), (b), (b), (c), (a), (b), (c), (a), (b), (c), (b), (c),

Eg:SELECT is equivalent to: (equivalent to, but the internal operation mechanism is different from UNION ALL, its efficiency is much higher than UNION ALL).

SELECT a recital breco creco sum (D) FROM test GROUP BY a recorder breco c

UNION ALL

SELECT arect breco null mam (D) FROM test GROUP BY areco b

UNION ALL

SELECT a FROM test GROUP BY null cmam (D) null

UNION ALL

SELECT a _ null _ null _ sum (D) FROM test GROUP BY a

UNION ALL

SELECT NULL,b,c,SUM (D) FROM test GROUP BY bpenny c

UNION ALL

SELECT NULL,b,NULL,SUM (D) FROM test GROUP BY b

UNION ALL

SELECT NULL,NULL,c,SUM (D) FROM test GROUP BY c

UNION ALL

SELECT NULL,NULL,NULL,SUM (D) FROM test

3. GROUPING SETS

The GROUPING SETS operator can produce the same result set as a result set generated using a single GROUP BY,ROLLUP or CUBE operator

If you do not need to get all the groupings generated by the complete ROLLUP or CUBE operators, you can use GROUPING SETS to specify only the groups you want

The GROUPING SETS list can contain duplicate groups, that is, allowing parameters to repeat

Eg:GROUP BY GROUPING SETS (a), then GROUP BY a twice.

Eg: calculate the statistics of each year, every month, every day of the year

SELECT year_id,month_id, SUM (sales_value) AS sales_value FROM sales_tab GROUP BY GROUPING SETS ((year_id,month_id,day_id), (year_id,month_id)) ORDER BY year_id,month_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.