Database Review ⑥

Database Review ⑥

June 18, 2016

18:16

Main SQL additions and deletions change

1.SQL Core Content

DDL (data definition Language) Definition language

Query Language Data Lookup statement

DML (Data manipulation language) manipulation language

DCL (DataControl language) Data Control language

2.Queries Query

Single-relation queries single-table query

Multi-relation queries multi-table query

Subqueries Sub-query

Grouping and Aggregation grouping and aggregation

3.select-from-where statements

Statement format:

SELECT [All | DISTINCT] < target column expression > [,< target column expression >] ...

From < table name or view name > [,< table name or view name >] ...

[WHERE < conditional expressions >]

[GROUP by < column name 1> [having < conditional expression >]]

[ORDER by < column name 2> [asc| DESC]]

Explanation: (1) above the contents of the brackets in the selection, you can have or not, notice that there is a comma ",";

(2) There can be no set function in the WHERE statement, while the WHERE statement may be omitted;

(3) Group BY statements are used for grouping, and the following having statements can include set functions for judging;

(4) The ORDER BY statement is used for sorting, the sort operation is actually done after the end of the query, the default is ASC ascending order, you can choose Desc to descending order;

(5) SQL query statements are not sensitive to the case of letters, so select and select can be identified, but uppercase is recommended;

(6) The GROUP BY statement should be followed by the where statement, in fact the group by statement should be at the end of the query statement, because the order by statement does not belong to the query statement, so it can be placed before order by;

(7) Although the SELECT statement is placed at the top of the query statement but is executed as final, after the group by statement, the alias inside the SELECT statement cannot be used within the group by statement;

(8) All in the SELECT statement corresponds to the distinct: The query result is kept duplicates, and the query results do not include duplicates;

(9) The SELECT statement is used to choose an attribute column, but you can use the * operator to select all the attribute columns in the table.

3.single-relation Queries Single-table query

A single-table query is the most basic query operation relative to a multi-table query.

You can select the attribute columns of the result table in the SELECT statement and use aliases and even use set functions, because the table has only one, so as long as you write the table name in the FROM statement, you can use the and keyword in the WHERE statement to make multiple judgments and filters, in the group By statement, you can select an attribute column to group, and then select the order BY statement.

To illustrate:

Range Comparison:between

Using predicates: Between ... And ... Greater than or equal to and less than or equal

Not between ... And ...

Set Operator:in

Use predicate in < value table, not in < value table >

Patterns string Matching problem

Use the keyword like

% = "any string"; _ = "any character."

4. Three logical values of TRUE = 1, FALSE = 0, UNKNOWN = 1/2

and = MIN; or = MAX; not (x) = 1-x

The null null value is special and requires special treatment

5.Aggregation Set function

Sum sum function, avg averaging function, count count function, min minimum function, max Max function, COUNT (*) tuple number

6.Grouping Grouping

Example:

7.HAVING words

8.multi-relation Queries Multi-table query

9.Subqueries Sub-query

A subquery Returns a result table subqueries that return one Tuple

Usually the return table has only one attribute column usually, the tuple has a component

If more than one property column produces a run-time error a run-time error occurs if there is no tuple or more than one tuple

Order by cannot be used in subqueries

Subquery with in: a looping tuple that is output to the result table when the value of that tuple is returned in the table

Subquery with EXISTS predicate

Example:

10.ANY and all Operator

Database Review ⑥