Skip to content

Instantly share code, notes, and snippets.

@iguoli

iguoli/SQL3.md

Last active March 6, 2018 08:36
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save iguoli/6d93a8345926464bc1137b058dc0c811 to your computer and use it in GitHub Desktop.
Save iguoli/6d93a8345926464bc1137b058dc0c811 to your computer and use it in GitHub Desktop.
Download ZIP
数据库系统概念第3章 SQL
Raw
SQL3.md

第三章 SQL

3.3 SQL查询的基本结构

3.3.2 多关系查询（SQL查询的通用形式）

select子句，from子句，where子句。每种子句作用如下：  

  • select子句用于列出查询结果中所需要的属性
  • from子句是需要访问的关系列表
  • where子句是一个作用在from子句中关系的属性上的谓词

3.3.3 自然连接

自然连接只考虑那些在两个关系模式中都出现的属性（两个关系中具有相同名称的所有属性）上取值相同的元组对。

--找出授课教师的的姓名及他们讲授的所有课程ID
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

--更简洁的写法

select name, course_id
from instructor natural join teaches;

SQL提供了一种自然连接的构造形式(join...using)，允许用户来指定需要哪些列相等。

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

3.4 附加的基本运算

3.4.1 更名运算

as子句：old_name as new_name as子句即可以出现在select子句中，也可以出现在from子句中。

3.4.2 字符串运算

SQL使用单引号来标示字符串，例如'Computer'。如果单引号是字符串组成部分，就用两个单引号来表示。   在SQL标准中，字符串上的相符运算是大小写敏感的。
通过like操作符来实现模式匹配，模式是大小写敏感的。

  • 百分号(%)：匹配任意字符串
  • 下划线(_)：匹配任意字符
select dept_name
from department
where builiding like '%Watson%';

为使模式中能够包含特殊字符（即%和_)，SQL允许定义转义字符。   在like语句中使用escape关键字来定义转义字符。

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

3.4.4 排列元组的显示次序

order by子句

按字母顺序列出在Physics系的所有教师
select name
from instructor
where dept_name 'Physics'
order by name;

order by子句默认使用升序，可以使用desc表示降序，或显式的使用'asc'表示升序。

3.5 集合运算

SQL作用在关系上的union、intersect和except运算对应于数学集合论中的∪(并集)、∩（交集)和-(差）

3.5.1 并运算

--找出在2009年秋季或2010年春季或两个学期都开课的所有课程
(select course_id
from section
where semester='Fall' and year=2009)
union
(select course_id
from section
where semester="Sprint' and year=2010);

与select子句不同，union运算自动去除重复。如果希望保留重复，需要使用union all

3.6 空值

SQL在谓词中使用特殊的关键字null测试空值。

select name
from instructor
where salary is null;

select name
from instructor
where salary is not null;

当一个查询使用select distinct子句时，重复元组将被去除。

3.7 聚集函数

聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。SQL提供了5个固有聚集函数：

  • 平均值：avg
  • 最小值：min
  • 最大值：max
  • 总和：sum
  • 计数：count sum和avg必须是数字集，但其它运算符还可以用在非数字数据类型的集合上，如字符串。

3.7.1 基本聚集

--找出Computer Science系的老师平均工资
select avg(salary) as avg_salary
from instructor
where dept_name='Comp. Sci.';

某些情况下，在计算聚集函数前还应该先去掉重复元组。

--找出在2010年春季讲授课程的老师总数
select count(distinct ID)
from teaches
where semester='Sprit' and year=2010;

计算关系中的元组个数

select count(*)
from course;

3.7.2 分组聚集

有时候希望将聚集函数作用到一组元组集上，可以使用group by子句来构造分组。

--找出每个系的平均工资
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

--找出每个系在2010年春季讲授课程的教师人数
select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester='Spring' and year=2010
group by dept_name;

注意：在select子句中没有被聚合的属性，只能是group by子句中用来分组的属性。

3.7.3 having子句

having子句针对group by子句构成的分组进行条件限定。

--对于在2009年讲授的每个课程段，如果该课程段至少有两名学生选课，找出选修该课程段的所有学生总学分的(tot_cred)的平均值
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;

注意：与select子句类似，having子句中没有被聚合的属性，只能是group by子句中用来分组的属性。 上述查询可以通过下述操作序列来说明：

  1. 先根据from子句来计算出一个关系；
  2. 如果出现where子句，where子句中的谓词将应用到from子句的结果关系上；
  3. 如果出现group by子句，满足where子句的元组通过group by子句形成分组；
  4. 如果出现having子句，它将应用到每个分组上，不满足having子句谓词的分组将被丢弃；
  5. select子句利用剩下的分组产生出查询结果中的元组，然后在每个分组上应用聚集函数来得到单个的结果元组。

3.8 嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式。
子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

3.8.1 集合成员资格

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

--查找2009年秋季和2010春季同时开课的课（可以使用集合的交运算，但这次使用`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 集合的比较

  • > some，表示至少比某一个要大
  • < some
  • <= some
  • >= some
  • = some，等价于in
  • <> some，不等价于not in
  • > all，表示比所有的都大
  • < all
  • <= all
  • >= all
  • = all，不等于in
  • <> all，等价于not in

3.8.3 空关系测试

exsits关键字测试一个子查询的结果中是否存在元组，子查询非空时返回true。

select course_id
from section as S
where semester='Fall' and year=2009 and
exists (select course_id
        from section as T
        where semester='Spring' and year=2010 and
        S.course_id=T.course_id);

使用来自外层查询相关名称的子查询被称作相关子查询(correlated subquery)

3.8.4 重复元组存在性测试

unique用于测试在一个子查询的结果中是否存在重复元组，如果没有重复元组，返回true。



3.8.5 from子句中的子查询


任何select-from-where表达式返回的结果都是关系，因而可以插入到另一个select-from-where中任何关系可以出现的位置。


--找出系平均工资超过42000的那些系的教师平均工资，使用having子句
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

--使用from子查询
select dept_name, avg_salary
from (select dept_name avg(salary) as avg_salary
      from instructor
      group by dept_name)
where avg_salary > 42000;

--找出在所有系中工资总额最大的系
select dept_name, max(total_salary)
from (select dept_name, sum(salary) as total_salary
	  from instructor
	  group by dept_name);


3.8.6 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_total.value >= dept_total_avg.value;


3.8.7 标量子查询


SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组，这样的子查询称为标量子查询(scalar subquery)。


3.9 数据库的修改


3.9.1 删除


delete from R
where P;


delete命令只能作用于一个关系


3.9.2 插入


--在查询结果的基础上插入元组，让Music系每个修满144学习的学生成为Music系的教师，其工资为18000。
insert into instructor
	select ID, name, dept_name, 18000
	from student
	where dept_name='Music' and tot_cred>144



3.9.3 更新


update instructor
set salary = salary * 1.05;
where salary > 70000;



  


  





      
      

        

          

            









          


          

            

  
    Sign up for free
    to join this conversation on GitHub.
    Already have an account?
    Sign in to comment