当前位置：首页 > article >正文

Introduction to SQL

article 2025/8/18 22:22:52

SQL特点

编辑

Select-From-Where Statements

Meaning of Single-Relation Query

Operational Semantics

* In SELECT clauses

Complex Conditions in WHERE Clause

PATTERNS

NULL Values

Three-Valued Logic

Multirelation Queries

Aggregations

NULL’s Ignored in Aggregation

Grouping

HAVING Clauses

SQL特点

SQL是一种结构化查询语言
SQL语言包括DQL（数据查询）、DCL（数据控制）、DDL（数据定义）、DML（数据操纵）
- 因为关系模式中，实体与实体之间用关系表示联系，所以操作符比较简单，每种操作只需要一种操作符表示
SQL语言的操作对象都是集合（查询、插入、修改、删除）
SQL语言既是自含式语言，同时也是嵌入式语言
语言简洁
- 核心功能只有九个

Select-From-Where Statements

SELECT desired attributes

FROM one or more tables

WHERE condition about tuples of

the tables

Meaning of Single-Relation Query

Begin with the relation in the FROM clause.
Apply the selection indicated by the WHERE clause.
Apply the extended projection indicated by the SELECT clause

FROM确定数据源，WHERE进行数据的筛选，SELETE对属性进行投影

Operational Semantics

Think of a tuple variable visiting each tuple of the relation mentioned in FROM.
Check if the “current” tuple satisfies the WHERE clause.
If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.

可以认为元组变量将访问所有FROM中涉及的关系，然后循环的检验是否满足WHERE中的条件，最后将满足条件的数据放到返回结果中，并且进行扩展投影

* In SELECT clauses

When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.”

SELETE中既可以是属性也可以是常量

Complex Conditions in WHERE Clause

PATTERNS

A condition can compare a string to a pattern by: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>
Pattern is a quoted string with % = “any string”; _ = “any character. ”

% 0~任意多个字符
_ 任意单个字符
[] 集合范围内的任意单个字符
[^] 不在集合范围内的任意单个字符
[…-…] 前一字符至后一字符中的任一字符
ESCAPE 取消后面通配字符的通配作用（也就是说有时候需要使用到字符本身）

NULL Values

Tuples in SQL relations can have NULL as a value for one or more components.（SQL关系中允许元组有一种或多种属性是NULL）
Meaning depends on context. Two common cases:

Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is.（缺省值）

Inapplicable : e.g., the value of attribute spouse for an unmarried person.（不适用值）

Three-Valued Logic

To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½.
AND = MIN; OR = MAX, NOT(x) = 1-x.
Example:

TRUE AND (FALSE OR NOT(UNKNOWN)) =

MIN(1, MAX(0, (1 - ½ ))) =

MIN(1, MAX(0, ½ )) = MIN(1, ½ ) = ½.

Multirelation Queries

Interesting queries often combine data from more than one relation.
We can address several relations in one query by listing them all in the FROM clause.
Distinguish attributes of the same name by “<relation>.<attribute>” .

Example: Joining Two Relations

Using relations Likes(drinker, beer) and Frequents(drinker, bar), find the beers liked by at least one person who frequents Joe’s Bar.

SELECT beer
ROM Likes, Frequents
WHERE bar = ’Joe’’s Bar’ AND
Frequents.drinker = Likes.drinker;

Aggregations

SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.
Also, COUNT(*) counts the number of tuples.

NULL’s Ignored in Aggregation

NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column.
But if there are no non-NULL values in a column, then the result of the aggregation is NULL.

NULL值不参与聚合函数的运算

当表为空集时，返回结果为0

Grouping

We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of attributes.（在select-from-where语句后面接GroupBy和一系列属性）
The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is
applied only within each group.（select返回的结果将会被Group语句进行分组，并且groupby只在分组内应用）

select语句后面是可以接单属性的，当且仅当group后面接的属性