数据库系统概念 | 第三章：SQL介绍

📚SQL语言概览

🔑SQL语言组成

🔑SQL语法结构

🔑SQL语法要点

🔑SQL语言特点

📚SQL数据定义

🐇基本数据类型

🐇基本模式定义

🥕create table

🍃定义SQL关系
🔑完整性约束

• primary key声明主码

• foreign key references s声明外码

• check详见第四章check语句

• not null : 属性不允许空值

 create table 表名
       (属性名1 数据类型 (域值) <not null>,
       (属性名2 数据类型 (域值) <not null>,
          ......
	   <primary key (属性名1属性名2...)>,
	   <foreign key (属性名3属性名4...) references s>,
	   <check (条件)>);

🔑代码示例大学生数据库部分SQL数据定义

create table department
      (dept_name varchar(20),
	   building varchar(15),
	   budget numeric(12,2),
	   primary key (dep_name));
create table course
      (course_id varchar(8),
	   title varchar(50),
	   dept_name varchar(20),
	   credits numeric(2,0),
	   primary key (course_id),
	   foreign key (dept_name) references department);
create table instructor
	  (ID varchar(5),
	   name varchar(20) not null,
	   dept_name varchar(20),
	   salary numeric(8,2),
	   primary key (ID),
	   foreign key (dept_name) references department);
create table section
	  (course_id varchar(8),
	   sec_id varchar(8),
	   semester varchar(6),
	   year numeric(4,0),
	   building varchar(15),
	   room_number varchar(7),
	   time_slot_id varchar(4),
	   primary key (course_id,sec_id,semester,year),
	   foreign key (course_id) references course);
create table teaches
	  (ID varchar(5),
	   course_id varchar(8), 
	   sec_id varchar(8),
	   semester varchar(6),
	   year numeric(4,0),
	   building varchar(15),
	   primary key (ID, course_id,sec_id, semester,year),
	   foreign key (course_id , sec_id,semester,year) references section,
	   foreign key (ID) references instructor);

🥕create domain

🍃定义域
🔑类似于C/C++中的宏定义域定义需要置于create table之前

create domain 属性名 数据类型(域值)

🥕drop table

🍃删除关系和关系中的元组

drop table 关系名

🥕delete table

🍃仅删除元组而保留关系

delete table 关系名

🥕alter table

🍃为已有关系修改属性

alter table 关系名
     <add 子句>		增加新属性
	 <drop 子句>		删除属性
	 <change 子句>	修改属性名称、类型
	 <modify 子句>	修改属性类

📚SQL查询的基本结构

🐇select基本结构

select 属性名1,属性名2,...,属性名n
from 关系名1,关系名2,...,关系名n
<where P>

• select子句用于列出查询结果中所需要的属性
• from子句是在查询求值中需要访问的关系列表
• where子句是作用在from子句中的关系属性上的谓词
  • where⼦句⽤于过滤记录即缩小访问数据的范围。
  • where后跟⼀个返回 true 或 false 的条件。
• P是一个谓词可以理解为条件表达式

对应的形式化关系语言

🐇select详细结构解题🔑

select <ALL｜DISTINCT> 目标列表达式1,目标列表达式2,...
from 关系名1,关系名2,...
<where 行条件表达式>
<group by 属性名序列 <having 组条件表达式>>
<limit a,b>
<order by 属性名 <ASC｜DESC>>

过程化结构解读

• 目标列表达式包括属性名算术表达式聚集函数* * 表示“所有的属性”

• 读取from子句的表或视图做笛卡尔积from子句列出查询需要访问的关系列表

• where子句找出满足条件表达式的元组

  • 比较运算符<<=>>== <>!=
  • 逻辑运算符andornot

• 按group by子句指定列名分组值相等的元组为一组每个组产生结果表中的一条记录。可在每组中用聚集函数。如果group by子句带having短语则只有满足指定条件的组才予输出

• 按select子句中给出的列名或表达式求值输出

• 若要去掉重复元组可用关键字distinct指明

• 为保留重复元组可用关键字all显式指明

• limit子句限制返回的行数。第⼀个参数a为起始行从 0 开始第⼆个参数b为返回的总行数即返回第a+1~a+b行

• 按order子句列名的值升或降序,

  • ASC 升序默认

  • DESC 降序

在后面会对*、order by、group by进行进一步的阐释和说明

🐇关系自然连接

在第四章连接表达式中会对自然连接有进一步的认识和了解

🥕自然连接natural join运算

select 属性名1,属性名2,...,属性名n
from 关系名1 natural join 关系名2 natural join 关系名3... natural join 关系名n

• 在from中作用于多个关系产生一个关系作为结果

• 在结果关系中的两个连接关系的元组在公共属性上取值相等不等的自动剔除

• 结果关系中公共属性只出现一次。

🥕连接构造join using运算

select 属性名1,属性名2,...,属性名n
from 关系名1 join 关系名2 using (属性名1,属性名2,...属性名n)

• 按指定属性连接不考虑未指定的共同属性
• 只考虑关系名2与关系名1在属性名1…属性名n上的匹配其他的共有属性不考虑
• 这里的关系名可以是自然连接后的关系即from关系名1 join 关系名2join 关系名3 using 属性名1,属性名2,...属性名n

📚附加的基本运算

🐇as更名运算

属性更名

• 格式select 旧属性名 as 新属性名

• 举例

  select name as instructor_name,course_id
  from instructor,teaches
  where instructor.ID=teaches.ID
  

关系更名

• 格式from 旧关系名 as 新关系名

• 举例

  select name as instructor_name,course_id
  from instructor,teaches
  where instructor.ID=teaches.ID
  

关系更名适应于比较同一关系中的元组的情况

• 举例1找出工资至少比Biology系某一个教师的工资要高的老师姓名

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

• 举例2找出工资比所在系主任工资高的老师姓名及工资

  select P1.PNAME, P1.SAL
  from PROF as P1,PROF as P2,DEPT
  where P1.DNO = DEPT.DNO
  and DEPT.DEAN = P2.PNO
  and P1.SAL > P2.SAL
  

🐇字符串运算

• SQL使用一对单引号标识字符串

  • 使用两个单引号表示字符串中的单引号如“It’s right”表示为’It’‘s right’

• 字符串运算大小写敏感

• SQL允许字符串上有多种函数

🥕like运算符实现模式匹配

select 属性名
from 关系名
where 属性名 <not> like 字符串模式

• 描述字符串模式的两个特殊字符

  • % 百分号匹配零个或多个字符

  • ＿ 下划线匹配任意单个字符

    • 举例说明
    • Intro% 匹配任何以“Intro”开头的字符串
    • %comp% 匹配任何包含“comp”的字符串
    • ___% 匹配至少包含三个字符的字符串
    • ___ 匹配只包含三个字符的字符串

🥕escape转义字符

转义字符放在特殊字符前面表示该特殊字符被当成普通字符

例如

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

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

🥕'^[0-9]+$'纯数字字符串查找

• 示例检查字符串上是否全为数字

  select 属性名
  from 关系名
  where 属性名 like (属性名,'^[0-9]+$')
  

🐇*表示所有属性

示例1列出姓名以“张”打头的教师的所有信息

select *
from PROF
where PNAME like '张%'

示例2列出姓名中含有4个字符以上且倒数第3个字符是d倒数第2个字符是_的教师的所有信息

select *
from PROF
where PNAME like '% ＿d \＿＿' escape '\'

🐇order by显示次序说明

可以按多个列进行排序并且为每个列指定不同的排序方式默认为升序

这里我们用一个示例解释order by子句中如何排列元组的显示次序

示例按系名升序列出老师姓名所在系名同一系中老师按姓名降序排列

select DNAMEPNAME
from PROFDEPT
where PROF.DNO = DEPT.DNO
order by DNAME ascPNAME desc 

🐇where子句

🥕between比较运算符

between

表示大于等于条件1并且小于等于条件2的条件

not between

表示既不大于等于条件1也不小于等于条件2的条件

格式where 属性 <not> between 条件1 and 条件2...and 条件n

示例列出工资在90000和100000之间的教师名称

select name
from instructor
where salary between 90000 and 100000

🥕n维元组表达

表达有多个分量的n维元组

示例列出Biology系教课的教师名和课程标识

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

📚集合运算

集合操作自动去除重复元组如果要保留重复元组的话必须用all关键词指明union all, intersect all, except all

一定要用括号括起来

🐇并运算union

• UNION 运算符将两个或更多查询的结果组合起来并⽣成⼀个结果集其中包含来⾃ UNION 中参与查询的提取行

• 🔑UNION 基本规则后续intersect和except/minus规则类似

  • 所有查询的列数和列顺序必须相同。

  • 每个查询中涉及表的列的数据类型必须相同或兼容。

  • 通常返回的列名取自第⼀个查询。

  • 默认会去除相同行只保留一行。如果需要保留相同行使⽤用UNION ALL。
    
    

  • 只能包含⼀个 ORDER BY子句并且必须位于语句的最后。

  • UNION 将查询之后的行放在一起垂直放置

• 应⽤场景

  • 在⼀个查询中从不同的表返回结构数据。

  • 对⼀个表执行多个查询按⼀个查询返回数据。

示例1找出2017年秋季开课或2018春季开课的课程 没有all

(select coursr_id
from section
where semester='Fall' and year=2017)
union
(select coursr_id
from section
where semester='spring' and year=2018)

示例2找出2017年秋季开课或2018春季开课的课程或者两个学期都开设的课程 有all

(select coursr_id
from section
where semester='Fall' and year=2017)
union all
(select coursr_id
from section
where semester='spring' and year=2018)

代码2的结果里两个学期都开课的课程会出现两次

🐇交运算intersect

示例找出在2017年秋季和2018春季都开课的课程

(select coursr_id
from section
where semester='Fall' and year=2017)
intersect
(select coursr_id
from section
where semester='spring' and year=2018)

🐇差运算except/minus

示例找出在2017年秋季开课但是不在2018春季开课的课程

(select coursr_id
from section
where semester='Fall' and year=2017)
minus
(select coursr_id
from section
where semester='spring' and year=2018)

📚空值

🐇算术运算里的空值

算数表达式的任一输入为空则结果为空

如关系R中A属性为空 则该属性+5的结果也为空

🐇比较运算里的空值

涉及空值的任何比较运算的结果为unknown(第三逻辑值

如关系R中A属性为空 则1<该属性为unknown

🐇where子句谓词

🥕布尔运算里的空值

and、or、not

谓词中null=null的比较结果为unknown

• and、or、not 是⽤于对过滤条件的逻辑处理指令
  • and 优先级高于 or为了明确处理顺序可以使用
  • and 操作符表示左右条件都要满足
  • or 操作符表示左右条件满足任意⼀个即可
  • not 操作符用于否定⼀个条件

🥕空值测试

is <not> null

测试指定列的值是否为空值若is not null所作用的值非空则谓词为真

🐇集合运算里的空值

• 如果元组在所有属性上取值相同或都为空值就被当作相同组
  • 如 select distinct 子句以及集合运算中的元组比较

---

🔑注意事项

• 除is [not] null之外空值不满足任何查找条件
• 如果null参与算术运算则该算术表达式的值为null
• 如果null参与比较运算则结果可视为unknown
• 如果null参与聚集运算则除count(*)之外其他聚集函数都忽略null
• 对于聚集函数若输入集合为空count返回0其他函数返回null

📚聚集函数

聚集函数定义以值集集合或多重集合为输入并返回单个值的函数

🐇基本聚集

select 函数符号(<distinct> 目标列表达式) <as 新属性名>
from 关系名
<where P>

🐇group by分组聚集

• 格式group by 列名 <having 条件表达式>

  • group by 将表中的元组按指定列值相等的原则分组然后在每一分组上使用聚集函数得到单一值

  • having 则对分组进行选择只将聚集函数作用到满足条件的分组上

• 注意事项

  • 出现在select语句中没有被聚集的属性必须出现在group by子句中 分组属性组内值相同

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

  • 出现在having子语中但没有被聚集的属性必须出现在group by子句中 小组筛选也必须用分组属性

🐇having子句

🔑 having和where的区别

• where执行在group by之前针对原始的数据筛选
• having只能用在group by之后针对分组后的内容筛选
• where后的条件表达式里不允许使用聚集函数而having可以。

🔑 代码逻辑

• 最先根据from子句计算出一个关系
• 如果有where将where中的谓词应用到关系上
• 如果有group by满足where谓词的元组通过group by子句形成分组没有group by,则满足where谓词的整个元组当作一个分组
• 如果有having将应用到每个分组上不满足having谓词的将被抛弃
• select利用剩下的分组产生查询结果元组在每个分组上应用聚集函数来得到结果元组

示例1找出2009年讲授的每个课程段至少有2名学生选课的总学分平均值

select course_id,sec_id,semester,year,avg(tot_cred)
from takes natural join student
where year=2009
group by course_id,semester,year,sec_id
having count(ID) >= 2

示例2列出每一年龄组中男生超过50人的人数

 select SAGEcount(SNO)
 from S
 where SEX = ‘男’
 group by SAGE
 having count(*) > 50

🐇对空值和布尔值的聚集

🔑 空值

• null参与聚集运算除了count(*)以外的所有聚集函数都忽略输入中的空值
• 对于聚集函数若输入集合为空count返回0其他函数返回空值

🔑 布尔值

• 布尔数据类型true、false、unknown

• 聚集函数some和every分别计算布尔值的析取or和合取and

📚嵌套子函数

子查询是嵌套在另一个查询中的select-from-where表达式通过将子查询嵌套在where子句中可以实现对集合成员资格的测试、对集合的比较以及对集合基数的确定

🐇集合成员资格常用

🥕单属性关系中测试集合成员资格

• 连接词in测试元组是否是集合中的成员

  • 示例找出2017秋季和2018春季都开课的课程id

    select distinct course_id
    from section
    where semester= 'Fall' and year=2017 and course_id in (select course_id
    				                                       from section
                                                           where semester = 'Spring' and year=2018)
    

• 连接词not in测试元组是否不是集合中的成员

  • 示例找出2017秋季开课但2018春季不开课的课程id

    select distinct course_id
    from section
    where semester= 'Fall' and year=2017 and course_id not in (select course_id
    				                                           from section
                                                               where semester = 'Spring' and year=2018)
    

• in/not in可以用于枚举集合

  • 示例列出张军和王红同学的所有信息

    select *
    from S
    where SNAME in ('张军','王红')
    

  • 示例列出既不叫Mozart, 也不叫Einstein的教师姓名

    select distinct name
    from instructor
    where name not in ('Mozart','Einstein')
    

🥕任意关系中测试集合成员资格

• 示例找出选修了ID为10101教师的课的不同的学生总数

  select count(distinct ID)
  from takes
  where (course_id,sec_id,semester,year) in (select course_id,sec_id,semester,year
                                             from teaches
                                             where teaches.ID=10101)
  

🐇集合比较

🥕比较运算符 some 子查询

• 含义“至少比某一个要大”
• 形式(<some,<=some,=,<>some,>=some, >some)
• 特殊 =some 等价于in; 但<> some不等价于not in

示例找出工资至少比Biology系某一个教师的工资要高的老师姓名

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

🥕比较运算符 all子查询

• 含义意思为“比所有的都大”
• 形式 (<all ,<=all ,=all ,<>all ,>=all , >all )
• 特殊<>all 等价于 not in; 但=all不等价于in

示例找出平均成绩最高的学生号

select SNO
from SC
group by SNO
having avg(GRADE) >= all (select avg(GRADE)
				          from SC
     		              group by SNO)

🐇空关系测试(exists )

🥕exists

• 可以测试一个子查询的结果集合是否为空非空时返回true

• 示例列出2009秋季和2010同时开课的所有课程id

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

🥕not exists

• 可以测试一个子查询的结果集合是否为空非空时返回true

• 用于表示集合包含超集的逻辑测试

  • 若关系A包含关系BA为B的超集则not exists (B except A)为 true
  • 对于B except A可以表达在B中存在但在A中不存在的记录如果B是A的子集则B except A结果为空集则not exists (B except A)为true.

• 示例列出选修了全部课程的学生姓名

   select SNAME
   from S
   where not exists ((select CNO
  			        from Course)
                      except
     	               (select CNO
  			        from SC
     	                where SC.SNO = S.SNO))
  

  代码解释所求学生的选课集合为所有课程集合的超集

🥕集合成员资格与空关系测试的区别

• in后的子查询与外层查询无关每个子查询执行一次而exists后的子查询与外层查询有关使用了来自外层查询的相关名称需要执行多次称之为相关子查询

🥕集合包含测试的两种表达选讲

• 用两个not exists的嵌套来表达

  • 示例列出选修了全部课程的学生姓名

    select SNAME 
    from S
    where not exists (select CNO
    		          from Course
       	              where  not exists (select *
    			                         from SC
       		                             where SC.CNO = Course.CNO and SC.SNO = S.SNO))
    

    代码解释不存在任何一门课程所求学生没有选之

• 用两个not in的嵌套来表达

  • 示例列出选修了全部课程的学生姓名

    select SNAME
    from S
    where SNO not in (select SNO
    	              from  Course , S
       	              where (SNO,CNO) not in (select SNO,CNO
    				                          from SC))
    

    代码解释所求学生不在如下集合中——学生学号与任一课程的组合不全包含在SC中

🐇重复元组存在性测试(unique)

• 测试一个子查询的结果集合中是否存在重复元组如果没有则返回true

🥕unique

• 含义至多一个<=1

• 示例找出所有只教授一门课程的老师姓名多于一门的不符合条件

  select PNAME
  from PROF
  where unique (select PNO
  			  from PC
  	  	      where PC.PNO = PROF.PNO)
  

🥕not unique

• 含义最少两个>1

• 示例找出至少选修了两门课程的学生姓名只有一门的也不符合条件

  select SNAME
  from S
  where not unique (select SNO
  			       from SC
  	  	           where SC.SNO = S.SNO) 
  

🐇from子句中的子查询

格式from <关系名,关系名,...关系名,lateral>子查询 as 关系名属性名属性名…)

关于lateral的解释

from后除子查询外需要添加其他关系时需要在子查询前加上lateral以此访问子查询前的关系

示例找出平均成绩及格的学生

select SNAME,AVG_GRADE
from (select SNAME,avg(GRADE)
      from S,SC
	  where SC.SNO = S.SNO
	  group by SC.SNO,SNAME)
      as result(SNAME,AVG_GRADE )
where AVG_GRADE >= 60 

🔑 代码思路
(1)先求出每个学生平均成绩作为新的from关系
(2)再从中找出及格的学生代替之前的having子句

🐇with子句

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

🐇标量子查询

🔑标量子查询概述

• 只返回包含单个属性的单个元组的子查询。

• 可出现在返回单个值的表达式能够出现 的任何地方

• 可以出现在select、where和having子句中

• 标量子查询中可以使用外层的关系

示例

select dept_name, (select count(*)
                   from instructor
	               where department.dept_name =instructor.dept_name) 
	               as num_instructors
from department

代码解读该例中的子查询保证只返回单个值因为它使用了不带group by的count(*)聚集函数

🐇不带from子句的标量

预定义关系dual虚拟关系实际不存在

示例假设我们想要查找平均每位教师所讲授无论是学年还是学期的课程段数其中由多位教师所讲授的课程段对每位教师计数一次。我们需要对teaches中的元组进行计数来找到所授课程段的总数并对instructor中的元组进行计数来找到教师总数。然后一次简单的除法就能给出我们想要的结果。可能有人将此查询写为

(select count(*) from teaches)/(select count(*) from instructor)

尽管在一些系统中这样写是合法的但其他系统会由于缺少from子句而报错。此时我们可以创建一个特殊的虚拟关系例如创建包含单个元组的dual关系。这使得前面的查询可以写为

(select count(*) from teaches)/(select count(*) from instructor)
from dual

📚数据库的修改

🐇删除

• 元组的删除delete from 关系名 <where 条件表达式>
• 清空表中的数据truncate 关系名

• 示例1删除instructor中所有元组

  delete from instructor
  

• 示例2删除instructor中Finance系的教工元组

  delete from instructor
  where dept_name = 'Finance'
  

• 示例3删除instructor中所在系位于Watson大楼的教工元组

   delete from instructor
   where dept_name in (select dept_name                                    
                     from department                                                     
                     where building = 'Watson')
  

🔑注意事项

• 从表中删除符合条件的元组如果没有where语句则删除所有元组

• delete处理结果取决于元组被处理的顺序先遍历每一个符合条件的元组再进行删除操作

🐇插入

• 插入一条指定的元组

  • 格式insert into 关系名 <(属性名[,属性名]...)> values (值[,值]...)

  • 示例1插入完整的一行

    insert into PROF  
    	   values (‘P123’,‘王明’, 35,‘D08’, 498 )
    

  • 示例2插入部分元组

    insert into PROF (PNO, PNAME, DNO)
    	   values (‘P123’,‘王明’, ‘D08’ )
    

• 插入子查询结果中的若干条元组

  • 格式insert into 关系名 <(属性名[,属性名]...)> ()子查询

  • 示例将平均成绩大于90的学生加入到EXCELLENT中

    insert into EXCELLENT (SNO,GRADE)
           select SNO,avg(GRADE)
           from SC
           group by (SNO)
           having avg (GRADE) > 90
    

• 🔑注意事项

  • 系统在执行任何插入前需要先执行完select语句注意逻辑顺序

🐇更新

• 格式update 关系名 set 属性名 = 表达式/子查询 <,属性名= 表达式/子查询...> <where 条件表达式>

  • set后为需要更新的内容where后为需要更新的对象

• 示例将D01系系主任的工资改为该系的平均工资

  update PROF
  set SAL = (select avg(SAL)
  		   from PROF				
  		   where DNO = D01)
  where PNO = (select DEAN
  		     from DEPT				
  			 where DNO = D01)
  

• 🔑注意事项

  • SQL首先检查关系中符合条件的所有元组是否应该被更新然后才执行update操作
  • update操作存在操作顺序的逻辑问题

🐇case语句

• 基本用法
  

• 在update中使用
  

• 在select查询中使用
  

• 在where条件中使用
  

• 在group by排序中使用
  

• 在order by排序中使⽤
  

---

一个例题

   /*关系表
   person(driver_id,name,address)
   car(liscense_plate,model,year)
   accident(report_number)
   owns(driver_id,liscense_plate)
   participated(report_number,liscense_plate,driver_id,damage_amount)*/
   
   /*题目一找出2017年出过交通事故的人员ID及其发生的事故次数*/
   select distinct driver_id as '车主ID' , count(license_plate) as '事故次数'
   from participated
   where report_number in (select report_number
                           from accident
                           where year = 2017)
   group by driver_id
   having report_number is not null
   /*题目二删除ID为12345的人拥有的年份为2010的所有汽车*/
   delete from car
   where license_plate = (select license_plate
                          from owns
                          where driver_id = '12345')

​