一、SQL查询基本结构

select r1,r2,r3...,rn from E where P;

• where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组
• where子句中使用逻辑连词and、or和not。逻辑连词的运算对象可以是包含比较运算符<、<=、>、>=、=和<>的表达式

三、附加的基本运算

3.1 更名运算 **
  SQL语句提供了一个重命名关系以及结果关系中属性**的方法，即使用如下形式的as子句：

old_name as new_name

【示例】

#重命名关系中的属性
select name as instructor_name,course_id
from instructor,teaches
where instructor.ID = teaches.ID;

#重命名关系
select T.name,S.course_id
from instructor as T,teaches as S
where T.ID = S.ID;

重命名关系的一个重要原因是为了适用于需要比较同一个关系中的元组的情况。为此我们需要把一个关系跟它自身进行笛卡尔积运算，如果不重命名的话，就不可能把一个元组与其他元组区分开来。
  假设我们希望查询：“找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高”，我们可以写出这样的SQL表达式：

#T和S可以被认为是instructor关系的两个拷贝
select distinct T.name
from instructor as T,instructor as S
where T.salary > s.salary and S.dept_name = 'Biology';

3.2 字符串运算
  SQL使用一堆单引号来标识字符串，而不用双引号，例如'Computer'。如果单引号是字符串的组成部分，你就用两个单引号字符来表示，如字符串“it's right”可表示为"it''s right"。
  不同数据库所提供的字符串函数集是不同的。
  在SQL标准中，字符串上的相等运算是大小写敏感的，所以表达式“'comp.sci = Comp.Sci'”的结果是false。然而一些数据库系统，如MYSQL何SQL Server，在匹配字符串时并不区分大小写，所以在这些数据库中“'comp.sci = Comp.Sci'”的结果可能是真。

①字符串匹配
  在字符串上可以使用like操作符来实现匹配模式。我们使用两个特殊的字符串描述模式：

• 百分号(%)：匹配任意子串
• 下划线(_)：匹配任意一个字符
    这里的模式匹配是大小写敏感的，也就是说，大写字符与小写字符不匹配，反之亦然。考虑下列例子：
• 'Intro%'匹配任何以"Intro"打头的字符串
• '%Comp%'匹配任何包含"Comp"子串的字符串，例如'Intro.to Computer Science'和'Computer Biology'
• '_ _ _'匹配只含三个字符的字符串
• '_ _%'匹配至少含两个字符的字符串
    在SQL中用比较运算符like来表达模式。考虑查询"查询在广州市居住的同学"，该查询的写法如下：

select name from Student where city like '%广州%'；

另外，在like比较运算中使用escape关键词来定义转义字符。为了说明这一用法，考虑以下模式，它使用反斜杠()作为转义字符：

• like 'ab%cd%' escape '' 匹配所有以"ab%cd"开头的字符串。
• like 'ab\cd%' escape '' 匹配所有以"ab\cd"开头的字符串。

②字符转换
upper(s)将字符串s转换为大写
lower(s)将字符串s转换为小写
trim(s)去掉字符串后面的空格

3.3 select子句中的属性说明
  星号*可以用在select子句中表示“所有的属性”，因而，如下查询的select子句中使用instructor.*：

select instructor.*
from instructor,teaches
where instructor.ID = teaches.ID;

表示instructor中的所有属性都被选中。形如select*的select子句表示from子句结果关系的所有属性都被选中。

3.4 排列元组中的显示次序
  order by子句就可以让查询呢结果中元组按排列顺序显示。order by子句默认使用升序。要说明排序顺序，我们可以用desc表示降序，或者用asc表示升序。
  此外，排序可在多个属性上进行。如order by A desc,B asc,C asc是先是按A降序排序(优先)，再按B升序排序，再按C升序排序。

#按salary的降序列出整个instructor关系。如果有几位教师的工资相同，就将它们按姓名升序排列。
select *
from instructor
order by salary desc,name asc;

3.5 where子句谓词
  除了>、<、<>等运算符，为了简化where子句，SQL提供between比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值。
如下所示：

select name
from instructor
where salary between 90000 and 100000;
可以取代
select name
from instructor
where salary<=100000 and salary>=90000;

类似地，我们还可以使用not between比较运算符。
  SQL允许我们用记号(v1,v2,...,vn)来表示一个分量值分别为v1,v2,...vn的n维元组。在元组上可以运用比较运算符，按字段顺序进行比较运算。例如，(a1,a2)<=(b1,b2)在a1<=b1且a2<=b2时为真。类似地，当两个元组在所有属性上相等时，它们是相等的。这样，前述查询可被重写为如下形式：

select name,course_id
from instructor,teaches
where (instructor.ID,dept_name)=(teaches.ID,'Biology');

3.7 select子句还可带含有+、-、、/运算符表达式，运算对象可以是常数或元组的属性*

#属性值salary的值是原来的1.1倍
select id,name,salary*1.1 from instructor;

4.3 having子句
  有时候，分组限定条件比元组限定条件更有用，该条件并不针对单个元组，而是针对group by子句构成的分组。为表达这样的查询，我们是用SQL的having子句。having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。与select子句的情况类似，任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中，否则查询就是错误的。一句话，having的作用就是消除那些不满足给出条件的组。

select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000;

#把平均分90以上的姓名和平均分写出来
select sname,avg(score)
from student natural join takes
group by sname
having avg(score)>90
order by avg(score) desc;

包含聚集，group by或having子句的查询的含义可通过下述操作序列来定义：
  1.根据from自己来计算出一个关系。
  2.如果出现了where子句，where子句中的谓词将应用到from子句的结果关系上。
  3.如果出现了group by子句，满足where谓词的元组通过group by子句形成分组，否则整个元组集被当做一个分组。
  4.如果出现了having子句，它将应用到每个分组上，即在每个分组上应用聚集函数来得到单个结果元组。不满足having子句谓词的分组将被抛弃。
  5.select子句利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

#对于在2009年讲授的每个课程段，如果该课程段有至少2名学生选课，找出选修该课程段的所有学生的总学分的平均值
select course_id,semester,year,sec_id,avg(tot_cred)
from takes natural join student
where year=2009
group by course_id,semester,year,sec_id
having count(ID)>=2;

小Tips
①sql 的执行顺序为：where中的部分先执行 －> 如果有on，执行on －>如果有group by，接着执行group by －>如果有having,接着执行,having －>select中的函数计算、别名指定再运行－> 最后order by 对视图进行排序。
②由sql 的执行顺序可知，字段、表达式的别名在where子句和group by子句都是不能使用的，而在order by中不仅可以使用别名，甚至可以直接使用栏位的下标来进行排序，如：order by 1 desc,2 asc。
③在sql中，where优先级高于聚合函数（比如这里的sum）。不能在where子句中使用聚合函数。having的优先级低于聚合函数，故可以在having中使用聚合函数。
④因为having的计算顺序优先于select，所以在where之后先由having过滤分组，再执行select中的聚合函数(如果有的话)。

五、集合运算

SQL作用在关系上的union、insert和except运算对应于数学集合论中的∪、∩和-运算。

5.1 并运算
  union运算可以对两个结果集进行并运算，还可以自动去除重复。
  例子：查找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程，我们可写查询语句：

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

如果我们想保留所有重复，就必须用union all代替union。

5.2 交运算
  intersect可以进行交运算，并自动去除重复。
  例子：查找出在2009年秋季和2010年春季同时开课的所有课程的集合，我们可写查询语句：

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

如果我们想保留所有重复，就必须用intersect all代替intersect。

5.3 差运算
  except运算可以对两个结果集进行并运算，还可以自动去除重复。
  例子：查找出在2009年秋季但不在2010年春季开课的所有课程的集合，我们可写查询语句：

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

如果我们想保留所有重复，就必须用except all代替except。

六、嵌套子查询

6.2 集合的比较
  SQL中有三个关键字可以修改比较运算符：all、any和some，其中some和any等价。
(1)some
>some：至少比某一个要大
<>some：不等价于not in
=some：与in等价

示例1：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高。

select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');

(2)all
>all：比所有的都大
<>all：与not in等价
=all：不等价于in

示例1：找出满足下面条件的所有教师的姓名，他们的工资都比Biology系每一个教师的工资要高。

select name
from instructor
where salary > all (select salary
                    from instructor
                    where dept_name = 'Biology');

示例2：找出平均工资最高的系

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

6.3 空关系测试
  SQL还有一个特性可测试一个子查询的结果中是否存在元组。exist结构在作为参数的子查询非空时返回true值；not exist结构测试子查询结果集中是否不存在元组。
示例一：找出在2009年秋季学期和2010年春季学期同时开课的所有课程。

#where的循环是针对S的
select course_id
from section as S
where semester = 'Fall' and year = 2009 and
        exist(select * 
              from section as T
              where semester = 'Spring' and year = 2010 and
              S.course_id = T.course.id);

上述查询还说明了SQL的一个特性，来自外层查询的一个相关名称(上述查询中的S)可以用在where子句的子查询中。使用了来自外层查询相关名称的子查询被称作相关子查询。(隐含两个循环，可理解为两层for循环)。
  此外，我们可将“关系A包含关系B”写成“not exist(B except A)”。开率查询“找出选修了Biology系开设的所有课程的学生”。使用except结构，我们可以书写此查询如下：

#第一个子查询查询生物系全部课程；第二个子查询查询学生选的课程
select S.ID,S.name
from student as S
where not exist((select course_id
                 from course
                 where dept_name = 'Biology')
                 except
                 (select T.course_id
                  from takes as T
                  where S.ID = T.ID));

6.4 重复元组存在性测试
  SQL提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值。
示例一：找出所有在2009年最多开设一次的课程。

#unique结构可以当成循环理解
select T.course_id
from course as T
where unique(select R.course_id
             from section as R
             where T.course_id = R.course_id and R.year = 2009);

我们可以用not unique结构测试在一个子查询结果中是否存在重复元组。
示例二：找出所有在2009年最少开设两次的课程。

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);

6.6 with子句
  with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效。考虑下面的查询，它找出具有最大预算值的系。

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

with子句还允许在一个查询内的多个地方使用视图定义。例如，假设我们要查出所有工资总额大于所有系平均工资总额的系，我们可以利用如下with子句写出查询：

with dept_total(dept_name,value) as
     (select dept_name,sum(salary)
       from instructor
       group by dept_name),
     dept_total_avg(value) as
      (select avg(value)
       from dept_total)
select dept_name
from dept_total,dept_total_avg
where dept_tota.value>=dept_total_avg.value;