数据库系统原理（四） SQL查询

最新推荐文章于 2022-11-07 09:13:54 发布

MarsYYP

最新推荐文章于 2022-11-07 09:13:54 发布

阅读量774

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/MarsYYP/article/details/104828000

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库系统原理（四） SQL查询

SQL查询的基本结构

SQL查询的基本结构由3个子句构成：select，from，where

SELECT A1,A2,…,An
From r1，r2,…,rm
WHERE P

上述查询语句等价于关系代数表达式：
$\prod_{A_1,A_2,...,A_n}{(\sigma_{p}(r_1\times r_2\times .....\times r_m))}$
查询的输入是在from子句中列出的关系，在这些关系中进行where和select子句中指定的运算，然后产生一个关系作为结果。

select子句

例：找出所有的教师的名字

select name

from instructor;

表示成关系代数表达式：
$\prod_{name}{instructor}$

注意：SQL不允许在属性名称中使用字符“-”，例如，使用dept_name代替dept-name
SQL不区分字母的大小写，因此可以使用大写字母或小写字母命名表，属性等。
SQL允许在关系以及SQL表达式结果中出现重复的元组（代数表达式中不允许）
- 如果要强行去除重复，可在select后加入关键词distinct
  
  例，查询instructor关系中所有系名，并去除重复
```
select distinct dept_name
from instructor;
```
SQL也允许使用关键词all来显示知名不去除重复（SQL默认就是all）
星号 “*****” 在select语句中，可以用来表示“所有的属性”
- 例，
```
select *
from instructor;
```
select子句还可带含有+，-，*，/运算符的算数表达式，运算对象可以是常数或元组的属性
- 例
```
select ID,name,salary*1.05
from instructor
```

where子句

where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组
- 例，找出所有在Computer Science系并且工资超过70000美元的教师的姓名
```
select name
from instructor
where dept_name = C.S and salary>70000 ;
```
上述SQL查询语句，对应的关系代数表达式为：

$\prod_{name}{(\sigma_{dept_name = 'C.S'\wedge salary>70000}{(instructor)})}$

SQL允许where子句在使用逻辑连词and，or和not，也可以使用between指定范围查询。逻辑连词的运算对象可以是包含比较运算符<,<=,>,>=和<>的表达式

例: 找出工资在90 000美元和100 000美元之间的教师的姓名
```
select name
from instructor
where salary<=100000 and salary >= 90000;
```
或者：
```
select name
from instructor
where salary between 90000 and 100000;
```

from子句

from子句是一个查询求值中需要访问的关系列表，通过from子句定义了一个在该子句中所列出关系上的笛卡尔积
- 例，找出关系instructor和teaches的笛卡尔积
```
select *
from instructor,teacher;
```
一般为了去除表笛卡尔积中无用的元组，还应该对其进行限制

select name,course_id
from instructor,teaches
where instuctor.ID = teacher.ID and instructor.name = 'C.S';
-- 要求同名属性的值相同，去除无用元组

更名运算

SQL提供可为关系和属性重新命名的机制，即使用as子句：

old_name as new_name

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

例，将属性name重命名为instructor_name

select name as instructor_name
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系某一个教师的工资要高

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'
-- 为了区分

字符串运算

对字符串进行的最通常的操作是操作符 like 的模式匹配，使用两个特殊的字符来描述模式：
- 百分号（%）：匹配任意子串
- 下划线（_）: 匹配任意一个字符
例，找出所在建筑名称中包含子串"Watson"的所有系名

select dept_name
from department
where building like '%Watson%';
-- 前后可以匹配任意字符

为使模式中能够包含特殊字符（即%和_）,SQL允许定义转义字符。我们在like比较运算中使用escape关键词来定义转义字符
- 例，使用反斜线（\）作为转义字符
```
like "ab\%cd%" escape '\' --匹配所有以“ab%cd”开头的字符串
like 'ab\\cd' escape '\'  --匹配所有以"ab\cd"开头的字符串
```
SQL还允许在字符串上有多种函数，例如串联（"||"），提取子串，计算字符串长度，大小写转换（用upper（s）将字符串s转换为大写或用lower（s）将字符串s转换为小写），去掉字符串后面的空格（使用trim（s））

排列元组的显示此许

SQL为用户提供了一些对关系中元组显示次序的空值。order by子句就可以让查询结果中元组按排列顺序显示
- 例，按字母顺序列出在Physics系的所有教师
```
select name
from instructor
where dept_name='physics'
order by name;
```
order by子句默认使用升序。要说明排序顺序，我们可以用desc表示降序，或者用asc表示升序
- 例，按salary的降序列出整个instructor关系，如果有几位教师的工资相同，就将他们按姓名升序排列
```
select *
from instructor
order by salary desc,name asc;
-- 先按收入降序排列，收入相等按姓名字母升序排列
```

重复（不是很理解）

在关系模式的形式化数学定义中，关系是一个集合。因此，重复的元组不会出现在关系中。但在实践中，包含重复元组的关系是有用的
可以用关系运算符多重集版本（Multiset version）来定义SQL查询的复本定义，在此定义几个关系代数运算符的多重集版本，一直多重集关系r1和r2
- 选择 σ（r1）：如果在r1中有元组t1的c1个复本，而且t1满足选择σ，那么有c1个t1的复本出现在结果中。
- 投影 ∏a（r）：如果r1中t1的每个复本，在∏a（r1）中都有一个∏a（t1）的复本与其对应，其中∏a（t1）表示单个元组t1的投影
- 交 r1×r2：如果有c1个t1的复本在r1中且有c2个t2的复本在r2中，那么有c1*c2个t1·t2元组的复本在 r1×r2中

集合运算

SQL作用在关系union，intersect和except运算对应于数学集合论中的∪，∩，-运算
uion，intersect和except运算与select子句不同，他们会自动去除重复
如果想保留所有重复，必须使用union all，intersect all 和except all
假设一个元组在关系r中重复出现了m此，在关系s中重复出现了n次，那么这个元组重复出现
- 在 r union all s中，重复出现m+n次
- 在r intersect all s中，重复出现 min（m,n）次
- 在r except all s中，重复出现max（0，m-n）次

例1，找出在2009秋季开课，或者在2010年春季开课或两个学期都开课的所有课程

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

空值

SQL允许使用null表示属性缺失。我们在谓词中可以使用特殊的关键词null测试空值，也可以使用is not null测试非空值
- 例，找出instructor关系中元组在属性salary上取空值的教师名
```
select name
from instructor
where salary is null;
--不能使用salary=null，因为null = null结果不一定
```
控制的存在给聚集运算的处理也带来了麻烦。聚集函数根据以下原则处理空值。
- 除了count(*)外所有的聚集函数都忽略集合中的空值
- 规定：空值的count运算符值位0，其他所有聚集运算在输入为空集的情况下返回一个空值

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。SQL提供了五个固有聚集函数：
- 平均值：avg
- 最小值：min
- 最大值：max
- 总和：sum
- 计数：count
- 其中，sum和avg的输入必须是数字集，但其他运算符还可作用在非数字数据类型的集合上，如字符串
分组聚集（group by）：group by子句中给出的一个或多个属性是用来构造分组的，在group by子句中给出的一个或多个属性是用来构造分组的，在group by子句中的所有属性上取值相同的元组将被分为一个组中
having子句类似于where子句，但其是对分组限定条件，而不是对元组限定条件。having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。

例1，找出Computer Science系教师的平均工资

select avg(salary) as avg_salary  --平均后没有相应的属性名，要重命名
from instructor
where dept_name = 'C.S';

上述SQL查询等价于关系代数表达式：
$g_{avg(salary)}{(\sigma_{dept_name='C.S'}{(instructor)})}$
例2，找出每个系的平均工资

select avg(salary) as avg_salary
from instructor
group by dept_name;

注意：任何没有出现在group by子句中的属性，如果出现在select子句中的话，他只能出现在聚集函数的内部。

例3，找出教师平均工资超过42000美元的系

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

注意：同上

嵌套子查询

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

集合成员资格
集合的比较
空关系测试
重复元组存在性测试
from子句的子查询
with子句

集合成员资格

SQL允许测试元组在关系中的成员资格。连接词 in 测试元组是否是集合中的成员，集合是由select子句产生的一组值构成，对应的还有 not in

例1，找出在2009年秋季和2010年春季学期同时开课的所有课程

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

例2，找出（不同的）学生总数，他们选修了ID为10101的教师所讲授的课程

select  count(distinct stu_id)
from take
where (course_id,sec_id,semester,year) 
				in (select course_id,sec_id,semester,year
                    from teach
                    where tea_id =10101);

集合的比较

考虑“查询满足下面条件的的所有教师的姓名，他们的工资至少比Biology系某一教师的工资要高”，在前面，我们将此查询写作：

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

但是SQL提供了另一种方式书写上面的查询。短语“至少比某一个要大”在SQL中用 >some 来表示，则此查询还可写作

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

***some***子句：代表元组中的某一个值。（=some）≡ in，但是（≠some）!≡ not in

考虑查询“找出满足下面条件的所有教师的姓名，他们比Biology系每个教师的工资都高”，在SQL中，结构***>all***对应于词组“比所有都大”，则

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

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

空关系测试（不是很理解）

SQL还有一个特性可测试一个子查询的结果中是否存在元组。***exists***结构在作为参数的子查询为空时返回true值
- exists r <=> r ≠ 空集
- not exists r <=> r = 空集
我们还以使用***not exists*** 结构模拟集合包含（即超集）操作“关系A包含关系B”写成“not exists(B except A)”

例，找出在2009年秋季学期和2010年春季学期同时开课的所有课程，使用exists结构

select course_id
from section as S
where semester = 'autumn' 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);
--由于不能线性执行，该语句的时间开销比较大

例，找出选修了Biology系开设的所有课程的学生，使用except结构**（不是很理解）**

select stu_id
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));
                   
--难度较大

重复元组存在性测试

SQL提供了一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询的结果中没有重复的元组***unique***结构将返回true值

例1，找出所有在2009年最多开设一次的课程

select course_id
from course as T
where unique (select course_id
              from course as S
              T.couse_id = S.course_id and S.year = 2009
);

注意：也可以将上述语句中的***unique***换成***1>=***

例2，找出所有在2009年最少开设两次的课程

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

unqiue,not unique 在oracle8，sql sever7中不支持

from子句中子查询

SQL允许在from子句中使用子查询表达式。任何select-from-where表达式返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置
- 例，找出系平均工资超出42000美元的那些系的教师的平均工资。
  
  在前面的的聚集函数中，我们用了having 写此查询，现在用***from***子句中使用子查询来重写
```
select dept_name,avg_salary
from (select dept_name,avg(salary)
      from instructor
      group by dept_name
     )as dept_avg(dept_name,avg_salary) --必须用as生成临时视图
where avg_salary>42000;
```
- 例，找出所有系中工资总额最大的系
  
  在此，***having***语句无能为力。但我们可以使用***from***的子查询写出：
```
select max(dept_salary)
from (select dept_name,sum(salary)
      from instructor
      group by dept_name
     )as dept_sum(dept_name,dept_salary);
```

with子句（不是很理解）

**with子句提供定义临时关系的方法，这个定义只对包含***with***子句的查询有效

例，找出具有最大预算值的系

with max_budget(value) as --定义临时关系
		(select max(budget)
         from departement)    
select budget   --使用临时关系
from department,max_budeget
where departmen.budget = max_budget.value;

例，找出工资总额大于平均值的系**（不是很理解）**

with dept_total(dept_name,dept_sum) as--每个系的工资总和
		(select dept_name, sum(salary)
         from instructor
         group by dept_name)
with dept_avg(avg_sum) as --所系的平均工资
		(select avg(dept_sum)
         from dept_total)
select dept_name
from dept_total,dept_avg  --ppt中是from dept_total A,dept_avg 
where dept_total>=dept_avg; -- where A.value>=B.value

数据库的修改

删除

删除请求的表达与查询非常类似。我们只能删除整个元组，而不能只删除某些属性上的值。SQL用以下语句表示删除：
```
delete from r
where p;
-- p代表一个谓词，r代表一个关系
```
- 例1，从instructor关系中删除与Finance系教师相关的所有元组
```
delete from instructor
where dept_name = 'Finance';
```
- 例2，从instructor关系中删除所有这样的教师元组，他们在位于Wastion大楼的系工作
```
delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Watson')
```
- 例3，删除工资低于大学平均工资的教师记录
```
delete from instructor
where salary < (select avg(salary)
                from instructor);
```
  问题:当我们从instructor关系中删除元组时，平均工资就会改变
  
  SQL的解决方案：
  
  首先，计算出平均工资，并找出要删除的所有元组
  
  然后，删除上述找到的所有元组（不重新计算平均工资，也不重新测试元组是否符合删除条件）
  
  在同一SQL语句内，除非外层查询的元组变量引入内层查询，否则内层查询只进行一次。

插入

SQL允许使用***insert***语句，向关系中插入元组，形式如下：

insert int r [(c1,c2,...)]
	values(e1,e2,...)
	
insert into r[(c1,c2,...)]
	select e1,e2,...from...;

例1，假设我们要插入的信息时Computer Science系开设的名为"Database System"的课程CS-437，他有4个学分

insert into course
	values ("CS-437","Database System",'C.S',4);
	
--或者
insert into course(course_id,title,dept_name,credits)--指定属性
	values ("CS-437","Database System",'C.S',4);
	
--未知属性的值可以设为null

假设我们想让Music系每个修满144学分的学生称为Music系的教师，其工资为18000美元

insert into instructor
	select ID,name,dept_name,18000 --插入搜索到的值
	from student
	where dept_name='Music' and tot_cred >144;

更新

SQL允许使用***update***语句，在不改变整个元组的情况下改变其部分属性的值，形式如下：

update r
set <c1=e1,[c2=e2,...]>
[where <condition>];

例1，假设给工资超过100000美元的教师涨3%的工资其余教师涨5%，可以写:

update instructor
set salary = salary*1.03
where salary>100000;
update instructor
set salary = salary*1.05
where salary<=100000;
--两条语句顺序十分重要，不能调换

SQL提供***case***结构，避免更新次序引发的问题，形式如下

case 
when pred1 then result1
when pred2 then result2
...
when predn then resultn
else result0
end

因此上例查询可重新写为

update instrutor
set salary = case
			when salary<=100000 then salary*10.5
			else salary*1.03
end

MarsYYP

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
数据库系统原理（四） SQL查询

数据库系统原理（四） SQL查询文章目录数据库系统原理（四） SQL查询SQL查询的基本结构select子句where子句from子句更名运算字符串运算排列元组的显示此许重复（不是很理解）集合运算空值聚集函数嵌套子查询集合成员资格集合的比较空关系测试**（不是很理解）**重复元组存在性测试from子句中子查询with子句（不是很理解）数据库的修改删除插入更新SQL查询的基本结构SQL查询的基...
复制链接

扫一扫