SQL基础操作全解析

# SQL基础操作全解析 ## 1. SQL自然连接的优化形式 SQL提供了一种自然连接的构造形式，能在享受自然连接好处的同时，避免错误地对属性进行相等比较。例如以下查询： ```sql select name, title from (instructor natural join teaches) join course using (course id); ``` `join...using`操作需要指定属性名列表，两个输入关系都必须有指定名称的属性。以`r1 join r2 using(A1, A2)`操作为例，它与`r1 natural join r2`类似，但只有当`r1`中的元组`t1`和`r2`中的元组`t2`满足`t1.A1 = t2.A1`且`t1.A2 = t2.A2`时才匹配，即使`r1`和`r2`都有属性`A3`，也不要求`t1.A3 = t2.A3`。所以在上述SQL查询中，该连接构造允许`teaches.dept name`和`course.dept name`不同，并且能给出正确答案。 ## 2. 附加基本操作 ### 2.1 重命名操作 在SQL查询中，结果关系的属性名通常从`from`子句中的关系属性名派生而来，但这种方式并非总是可行。原因如下： - `from`子句中的两个关系可能有相同名称的属性，导致结果中属性名重复。 - 如果`select`子句中使用了算术表达式，结果属性没有名称。 - 即使可以从基本关系派生属性名，我们可能也希望更改结果中的属性名。 SQL使用`as`子句来重命名结果关系的属性，形式为`old-name as new-name`，`as`子句可出现在`select`和`from`子句中。 例如，若要将属性`name`重命名为`instructor name`，可将查询改写为： ```sql select name as instructor name, course id from instructor, teaches where instructor.ID= teaches.ID; ``` `as`子句在重命名关系时特别有用，主要有两个原因： - 用较短的关系名替代长关系名，方便在查询其他部分使用。例如，查询“找出大学中所有教过课程的教师的姓名和他们所教课程的ID”可改写为： ```sql select T.name, S.course id from instructor as T, teaches as S where T.ID= S.ID; ``` - 当需要比较同一关系中的元组时，需要对关系进行自笛卡尔积操作，不重命名就无法区分不同元组。例如，查询“找出工资高于生物学系至少一位教师工资的所有教师姓名”可写成： ```sql select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept name = ’Biology’; ``` 这里的`T`和`S`可看作`instructor`关系的副本，更准确地说，它们是`instructor`关系的别名，在SQL标准中称为相关名，也常被称为表别名、相关变量或元组变量。 ### 2.2 字符串操作 #### 2.2.1 字符串表示 SQL用单引号括起字符串，如`'Computer'`。若字符串包含单引号，需用两个单引号表示，如`“It”s right”`表示`“It’s right”`。 #### 2.2.2 字符串比较 SQL标准规定字符串相等操作区分大小写，如`’comp. sci.’ = ’Comp. Sci.’`结果为`false`。但有些数据库系统（如MySQL和SQL Server）在匹配字符串时不区分大小写，该表达式在这些数据库中结果为`true`，且这种默认行为可在数据库级别或特定属性级别更改。 #### 2.2.3 字符串函数 SQL支持多种字符串函数，如拼接（使用`∥`）、提取子字符串、求字符串长度、转换为大写（使用`upper(s)`）和小写（使用`lower(s)`）、去除字符串末尾空格（使用`trim(s)`）等。不同数据库系统支持的字符串函数集可能不同，可查看数据库系统手册获取详细信息。 #### 2.2.4 模式匹配 SQL使用`like`运算符进行字符串模式匹配，通过两个特殊字符描述模式： - `%`：匹配任何子字符串。 - `_`：匹配任何单个字符。 模式匹配区分大小写，示例如下： - `'Intro%'`：匹配以`“Intro”`开头的任何字符串。 - `'%Comp%'`：匹配包含`“Comp”`子字符串的任何字符串，如`'Intro. to Computer Science'`和`'Computational Biology'`。 - `'___'`：匹配长度恰好为三个字符的任何字符串。 - `'___%'`：匹配长度至少为三个字符的任何字符串。 例如，查询“找出所有建筑物名称包含子字符串`‘Watson’`的系的名称”可写成： ```sql select dept name from department where building like ’%Watson%’; ``` 若模式中要包含特殊模式字符（`%`和`_`），SQL允许指定转义字符，使用`escape`关键字定义。例如，使用反斜杠`\`作为转义字符： - `like ’ab\%cd%’ escape ’\’`：匹配以`“ab%cd”`开头的所有字符串。 - `like ’ab\\cd%’ escape ’\’`：匹配以`“ab\cd”`开头的所有字符串。 SQL还提供`not like`运算符进行不匹配搜索，有些数据库提供不区分大小写的`like`操作变体，SQL:1999提供了更强大的`similar to`操作，其模式指定语法类似于Unix正则表达式。 ### 2.3 `select`子句中的属性指定 `select`子句中可用星号`*`表示“所有属性”。例如： ```sql select instructor.* from instructor, teaches where instructor.ID= teaches.ID; ``` 表示选择`instructor`关系的所有属性。`select *`表示选择`from`子句结果关系的所有属性。 ### 2.4 元组显示排序 SQL允许用户控制关系中元组的显示顺序，使用`order by`子句可使查询结果中的元组按排序顺序显示。例如，按字母顺序列出物理系所有教师的姓名： ```sql select name from instructor where dept name = ’Physics’ order by name; ``` 默认情况下，`order by`子句按升序排列，可使用`desc`指定降序，`asc`指定升序。也可对多个属性进行排序，例如，按工资降序列出所有教师，若工资相同则按姓名升序