第三章 SQL

    SQL最早版本是由IBM开发的，最初叫做Sequel。20世纪70年代早期是作为System R项目的一部分。

3.1 SQL查询语言概览

• 数据定义语言
• 数据操纵语言
• 完整性
• 视图定义
• 事务控制
• 嵌入式SQL和动态SQL
• 授权

3.2 SQL数据定义

    3.2.1 基本类型

• char(n)：固定长度
• varchar(n)：可变长度
• int：整数
• smallint：小整数
• numeric(p, d)：定点数，共有p位数字，d位在右边，如numeric(3,1)可存储44.5
• real，double precision：浮点数与双精度浮点数
• float(n)：精度至少n位的浮点数

3.2.2 基本模式定义

    create table命令定义SQL关系。

create table
    (A1, D1,
     A2, D2,
     ...,
     An, Dn,
     <完整性约束>,
     ...,
     <完整性约束>); 

• primary key：主码属性必须非空且唯一
• foreign key(A1, A2, .....An)references s：声明表示关系中任意元组在属性(A1, A2, .....An)上的取值必须对应于关系s中某元组在主码属性上的取值。
• noy null：表示不允许空值

    SQL禁止破坏完整性约束的任何数据库更新。

    3.3 SQL查询的基本结构

        SQL查询的基本结构由三个子句构成：select、from和where。from->where->select

    3.3.1 关系查询

        在关系模型的形式化数学定义中，关系是一个集合。

        如：

select distinct name
from instructor
where dept_name = 'Comp.Sci.' and salary > 7000; 

        distinct强行删除重复，select子句中还可以含+、-、*、/运算符的算术表达式。

    3.3.2 自然连接

        自然连接(natural join)运算作用于两个关系，并产生一个关系作为结果。它只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id

        SQL还提供了一种自然连接的构造形式, join...using。

select name， title
from (instructor natural join teaches) join course using (course_id);

3.4 附加的基本运算

    3.4.1 更名运算

        as子句既可以出现在select子句中，也可以出现在from子句中。

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

    3.4.2 字符串运算

        SQL使用一对单引号来标示字符串，如‘Computer’。在字符串中表示单引号时用两个单引号。如‘it‘’s right’。

        SQL在字符串比较大小上是大小写敏感的，但如MySQL和SQL Server，在匹配字符串时并不区分大小写。

        SQL允许字符串函数操作，如lower(s), upper(s), trim(s)去掉字符串后面的空格。

        在字符串上可以使用like操作符来实现模式匹配。两个特殊字符来描述模式：%：匹配任意子串； _：匹配任意一个字符。

        在like比较运算符中使用escape关键词来定义转义字符。如:like 'ab\%cd%' eacape '\' 匹配所有“ab%cd”开头的字符串。

        SQL允许使用not like比较运算符搜素不匹配项。在SQL1999中海提供similar to操作，具备比like运算更强大的模式匹配能力。

    3.4.3 select子句中的属性说明

        select*的select子句表示from子句结果关系的所有属性都被选中，‘*’表示所有属性。

    3.4.4 排列元组的显示次序

        order by子句默认升序。可以用desc表示降序，asc表示降序。

select＊
from instructor
order by salary desc, name asc;

    3.4.5 where子句谓词

        between...and比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值的。

select name
from instructor
where salary between 90000 and 100000;

        还可以使用not between比较运算符。

3.5 集合运算

    union：U  intersect：∩  except：-。三个集合运算都会自动去除重复，它们的对象都是select-from-where。

    except中，如果我们想保留重复，可以用except all代替except。

3.6 空值

    SQL将1涉及空值的任何比较运算的结果视为unknown，and、or和not也被扩展到可以处理unknown值。

    and：true and unknown为unknown，false and unknown为false，unknown and unknown为unknown。

    or：true or unknown为true，false or unknown为unknown，unknown or unknown为unknown。

    not：not unknown为unknown。

    如果where子句谓词对一个元组计算出false或unknown，那么该元组不能被加入到结果集中。

    SQL在谓词中使用特殊的关键词null测试空值。也有谓词is not null。null = null会返回unknown，而不是true。

3.7 聚集函数

    聚集函数是以值得一个集合（集或多重集）为输入、返回单个值的函数。

    五个固有的聚集函数：平均值：avg。最小值：min。最大值：max。总和：sum。计数：count。

    count中使用distinct时，表示为：count（distinct ID）。

    分组聚集可以使用group by。在group by子句中的所以属性上取值相同的元组将被分在一个组中。

    当SQL查询使用分组时，保证出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性。

/*error*/
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

    有时候，对分组限定条件比元组限定条件更有用。having并不针对单个元组，而是针对group by子句构成的分组。having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。与select类似，任何出现在having中，但没有被聚集的属性必须出现在group by子句中。

    from->where->group by->having->select;

3.8 嵌套子查询

    3.8.1 集合成员资格

        in测试元组是否是集合中的成员，集合是由select子句产生的一组构成的。连接词not in则测试元组是否不是集合中的成员。

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
    course_id in (select course_id
                       from section
                       where semester = 'Spring' and year = 2010);

    3.8.2 集合的比较

        “至少比某一个要大”在SQL中用>some。=some等价于in，然而<>some并不等价于not in。

        >all对应于词组“比所有的都大”。<>all等价于not in，但=all并不等价于in。

select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                                     from instructor
                                     group by dept_name)

    3.8.3 空关系测试

        exists结构在作为参数的子查询非空时返回true值。

        not exists结构测试子查询结果集中是否不存在元组。“关系A包含关系B”写成“not exists(B except A)”。

select S.ID, S.name
from student as S
where not exists((select course_id
                         from course
                         where dept_name = 'Biology')
                         except
                         (select T.course_id
                         from takes as T
                         where S.ID = T.ID));

    3.8.4 重复元组存在性测试

        如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值。

        not unique结构测试在一个子查询结果中是否存在重复元组。。

select T.course_id
from course as T
where not unique(select R.course_id
                         from section as R
                         where T.course_id = R.course_id and
                                R.year = 2009);

    3.8.5 from子句中的子查询

        from子句中嵌套一个select-from-where结构等。

        SQL2003允许from子句中的子查询用关键词lateral作为前缀，以便访问from子句中在它前面的表或者子查询中的属性。

select name， salary, arg_salary
from instructor I1, lateral(select avg(salary) as avg_salary
                                    from instructor I2
                                    where I2, dept_name = I1.dept_name);

    3.8.6 with子句

        with子句提供定义临时关系的方法。实在SQL1999中引入的。

with max_budget (value) as
      (select max(budget)
       from department)
select budget
from department. max_budget
where department, budget = max_budget.value;

    3.8.7 标量子查询

        SQL允许子查询出现在返回单个值的表达式能够出现的任何地方（select、where、having），只要该子查询只返回包含单个属性的单个元组。该子查询称为标量子查询。

3.9 数据库的修改

    删除：delete语句首先从r中找出所以是P(t)为真的元组，然后把他们从r中删除。

delete from r
where P；

    插入：考虑到可能不知道关系属性的排列顺序，SQL允许insert语句中指定属性。

insert into course(course_id, title, dept_name, credits)
    values('CS-437', 'Database System', 'Comp.Sci', 4);

    更新：SQL提供case结构，可以利用它在一条update语句中防止某些问题发生。

update instructor
set aslary = case
                when salary <= 100000 then salary * 1.05
                else salary * 1.03
            end

    set：不支持同时对多个变量同时复制，当表达式返回多个值时将出错，当表达式未返回值时，变量将被赋为null。

    select：支持同时对多个变量同时复制，当表达式返回多个值时将返回的最后一个值赋给变量，当表达式未返回值时，变量保持原值。