Database | Dql&dml

SQL (structured query Language, Structured Query language), a collection-oriented, descriptive, non-procedural language for accessing and processing the standard language of a relational database.
SQL is an ANSI standard, so there are many different versions of the SQL language. However, in terms of SQL query and update, Oracle, MYSQL and other database system implementation is similar, basically can be interoperable porting.

• DQL (data query Language), the SQL query, the DQL statement is the SELECT query block.
• DML (data manipulation Language), or SQL update, DML statements are INSERT inserted, UPDATE updated, and DELETE deleted.

1. SQL query ★

General format of the SELECT statement:

SELECT [DISTINCT] <目标列表达式> [AS] <别名>FROM <表/视图> [AS] <别名>[WHERE <查询条件>][GROUP BY <分组列> [HAVING <分组条件>]][ORDER BY <排序列> [ASC|DESC]];

1.1 Single-table query 1.1.1 Simple query: SELECT

SELECT [DISTINCT] <目标列表达式> [AS] <别名>FROM <表/视图> [AS] <别名>

1. Query all columns: Use wildcard characters to * implement all queries.

SELECT *FROM <表/视图>

2. Remove duplicate rows:DISTINCT

SELECT DISTINCT <目标列>FROM <表/视图>

3. Specify aliases (alias):

<列名/表名> <别名><列名/表名> AS <别名>

1.1.2 Query Condition: WHERE

SELECT <目标列表达式> [AS] <别名>FROM <表/视图> [AS] <别名>WHERE <查询条件>

Query Criteria :

1. Comparison operation:,,,, > < = >= <= , != /<>

2. Scope of Determination:BETWEEN...AND...

<列名> [NOT] BETWEEN <下限> AND <上限>

3. Determine the collection:IN

<列名> [NOT] IN （值1, 值2...）

4. Pattern matching:LIKE

<列名> [NOT] LIKE ‘<匹配模式>‘

wildcard characters	Description
%	Override one or more characters
_	Replace one character
[charlist]	Any single character in a sequence of characters
[!charlist] [^charlist]	Any single character that is not in a sequence of characters

5. Null value:IS NULL

<列名> IS [NOT] NULL

6. Multiple conditions (logical operations): AND , OR ,NOT

<条件表达式> AND <条件表达式><条件表达式> OR <条件表达式>NOT <条件表达式>

and priority >or Priority

1.1.3 Query sort: ORDER BY

The ORDER BY clause sorts the query results by the specified column in ascending (ASC) or descending (DESC) order.

ORDER BY <排序列> [ASC|DESC]

The ORDER BY clause can only sort the results of the final query. cannot be used on the inner layer query.

1.1.4 aggregation function

Aggregation Functions	Description
COUNT(*)	Number of statistics record lines
COUNT([DISTINCT]<列名>)	Number of values in the statistics column
SUM([DISTINCT]<列名>)	Calculate the sum of column values
AVG([DISTINCT]<列名>)	Calculate the average of column values
MAX([DISTINCT]<列名>)	To calculate the maximum value of a column value
MIN([DISTINCT]<列名>)	To calculate the minimum value of a column value

Attention:
① only COUNT (*) calculates null values, and the remaining aggregate functions skip null values.
Aggregate functions cannot be used in ②where clauses, and aggregate functions can only be used with a HAVING clause in a SELECT clause and GROUP by.

1.1.5 Grouping query: GROUP BY

The GROUP BY clause groups the query results by the values of one or more columns, and the values are equal to one group.

GROUP BY <分组列> HAVING <分组条件>

The purpose of grouping is to refine the function object of the aggregate function, and the aggregation function will be used for each group, that is, each group has a value of the aggregate function.

the WHERE clause differs from HAVING clause :
The ①where clause acts on the base table/view and cannot use the aggregate function.
The ②having clause acts on the group.

1.2 Connection Query

Connection query: Using join operators to implement multi-table queries

1.2.1 Cross-Connect

Cross connection: Cartesian product.

① Implicit connection:

SELECT <目标列>FROM <表1>, <表2>

② using the JOIN connection:

SELECT <目标列>FROM <表1> CROSS JOIN <表2>

Eliminate Cartesian product: Use associated fields.

1.2.2 Internal Connection

Inner joins: Queries all rows that match the join criteria, but does not remove duplicate attribute columns.

① Implicit connection:

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性> <比较运算符> <表2>.<关联属性>

② using the JOIN connection:

1. Equivalent connection

Equivalent connection: When the comparison operator is an = inner join, the repeating attribute column is not removed.

① Implicit connection:

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性> = <表2>.<关联属性>

② using the JOIN connection:

SELECT <目标列>FROM <表1> [INNER] JOIN <表2> ON <表1>.<关联属性> = <表2>.<关联属性>

2. Non-equivalent connection

Non-equivalent joins: = do not remove duplicate attribute columns when the comparison operator is not in the inner join.

① Implicit connection:

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性> <比较运算符> <表2>.<关联属性>

② using the JOIN connection:

SELECT <目标列>FROM <表1> [INNER] JOIN <表2> ON <表1>.<关联属性> <比较运算符> <表2>.<关联属性>

3. Self-Connection

Self-connection: a table with its own equivalent connection.

① Implicit connection:

SELECT <目标列>FROM <表> FIR, <表2> SECWHERE FIR.<关联属性> = SEC.<关联属性>

② using the JOIN connection:

SELECT <目标列>FROM <表> FIR [INNER] JOIN <表2> SECON FIR.<关联属性> = SEC.<关联属性>

4. Natural connection

Natural connection: Removes the equivalent connection of the repeating attribute column, eliminating the Cartesian product.

SELECT *FROM <表1> NATURAL JOIN <表2>

1.2.3 External Connection

Outer joins: The contents of the main table are all displayed. Not matched to, filled with null.

1. Left Outer connection

Left outer joins: The left table is the main table, the right table is from the table. All rows of the left table are returned even if there is no match in the right table.

SELECT <目标列>FROM <表1> LEFT [OUTER] JOIN <表2>ON <表1>.<关联属性> = <表2>.<关联属性>

Oracle unique notation : From a table to (+) represent.

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性> = <表2>.<关联属性>(+)

2. Right outer connection

Right outer joins: the right table is the main table, left table is from the table. All rows in the right table are returned even if there is no match in the left table.

SELECT <目标列>FROM <表1> RIGHT [OUTER] JOIN <表2>ON <表1>.<关联属性> = <表2>.<关联属性>

Oracle unique notation : From a table to (+) represent.

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性>(+) = <表2>.<关联属性>

3. Full-Outer connection

Full outer joins: Returns all data from the left and right tables. The unmatched field is displayed as null.

SELECT <目标列>FROM <表1> FULL [OUTER] JOIN <表2>ON <表1>.<关联属性> = <表2>.<关联属性>

1.2.4 Specifying associated fields

① Implicit Join-Specifies the associated field using the WHERE clause:

SELECT <目标列>FROM <表1>, <表2>WHERE <表1>.<关联属性> <比较运算符> <表2>.<关联属性>

②join connection-Use the ON specified associated field:

SELECT <目标列>FROM <表1> JOIN <表2> ON <表1>.<关联属性> <比较运算符> <表2>.<关联属性>

③join connection-Use the USING specified associated field:

SELECT <目标列>FROM <表1> JOIN <表2> USING (<关联属性>)

The using is equivalent to the on specifying an associated field with the same name .

1.3 Sub-query

Query BLOCK: Temp table

SELECT <目标列>FROM <表>WHERE <查询条件>

1.3.1 Nested queries

Nested query: Nest one query block in the where/having condition clause of another query block.

SELECT <目标列>FROM <表>WHERE <列名> <运算符/谓词>     (SELECT <目标列>     FROM <表>     WHERE <查询条件>)

1. Subqueries with comparison operators

When a subquery returns a single value, a comparison operator can be used to connect.

SELECT <目标列>FROM <表>WHERE <列名> <比较运算符>    (SELECT <目标列>     FROM <表>     WHERE <查询条件>)

Comparison operator:,,,,, > < >= <= != <> ,=

2. Subqueries with In

When a subquery returns a collection, it is generally used to connect. In is most commonly used in nested queries.

SELECT <目标列>FROM <表>WHERE <列名> IN    (SELECT <目标列>     FROM <表>     WHERE <查询条件>)

3. Subqueries with any or all

SELECT <目标列>FROM <表>WHERE <列名> <比较运算符>ANY    (SELECT <目标列表达式>     FROM <表>     WHERE <查询条件>)

< comparison operator >all: compares all values of the subquery result.
< comparison operator >any: A comparison operation with any value of the subquery result.

1.3.2 Derived queries

Derived query: A query block is nested within the FROM clause of another query block, and the derived table generated by the subquery becomes the query object for the main query.

SELECT <目标列>FROM <表>, (SELECT <目标列> FROM <表> WHERE <查询条件>) [AS] <别名>WHERE <查询条件>

Note: You must specify an alias for the derived table that is generated by the subquery.

1.4 Integrated Query

Collection query: Multiple query results are set.

SELECT <目标列> FROM <表> WHERE <查询条件>    <集合运算谓词>SELECT <目标列> FROM <表> WHERE <查询条件>

Note: The number of columns for each query result that participates in the collection operation must be the same, and the data type of the corresponding item must be the same.

Set Operation	predicate
and set (to repeat)	UNION
and set (not to repeat)	UNION ALL
Intersection	INTERSECT
Subtraction	MINUS

2. SQL Update 2.1 SQL INSERT: Insert

INSERT INTO <表> (列名, 列名...) VALUES(值, 值...)

Insert shorthand-Inserts a row of data in the column order of the table:

INSERT INTO <表> VALUES(值, 值...)

2.2 SQL Modification: UPDATE

UPDATE <表> SET 列名 = 值, 列名 = 值... [WHERE <修改条件>]

2.3 SQL Delete: delete

DELETE FROM <表> [WHERE <删除条件>]

Database | Dql&dml