《数据库系统概念》 以及 CMU 15-445 (二) 2023.06.09

第三章 SQL

笔记

• 文中所提关系的结构

​

​

​

​

​

1. SQL语言含有以下部分：

   

2. 支持类型：

   1. char(n) 固定长度字符串
   2. varchar(n) 变长字符串,n为最大长度
   3. int 整数类型
   4. smallint 小整数（与机器相关的整数集）
   5. numeric（p,d） 定点数，精度由用户指定,有p位数字d位在小数点右边
   6. real 浮点数与双精度浮点数 ，精度与及其相关
   7. float(n): 精度至少为n的浮点数
   8. 每个类型也存在一个空值

3. 完整性约束：

   1. 主键（primary key）（某一属性）：非空且唯一，表示该属性的构成关系，同一张表中没有两个元组在主键属性取值相同，可以声明某一非空唯一属性为主键
   2. 外键（foreign key）：声明任意元组在该属性上的取值必须对应于关系S中某元组在主键上的取值
   3. 非空（not null）:如意，不允许该属性出现空集

4. curd：

   1. 创建/插入操作 :

      

      alter增加属性

      

   2. 删除操作：

      1. 删除所有元组
         

      2. 删除关系/模式
         

      3. 查询操作

      4. 单关系查询

      5. select from

         查询结果为一个包含系名的关系
         

      6. select distinct xx from, xx

         去除重复，每个属性只能出现一个
         

      7. select from all 指明不去除重复，但是保留重复元组是默认的

      8. select 属性 from 关系 where 特定谓词条件

         

      9. 多关系查询

         1. 常见多关系查询，全部列在from子句中
            
            属性如果出现在多个关系中，通过使用关系名前缀来说明和使用的是哪个关系的属性
            如：
         2. 常见查询
            
            理解：
            
            过大，一般不可能

5. 自然连接（join）：

   1. 查询的一种，使得来自两个或多个关系的信息可以被链接

      自然连接只考虑那些在两个关系模式中都出现且属性上取值相同的元组对，与两个关系上的笛卡尔积不同风险：
      

      • 因此为避免该风险，我们采用 select 属性 from 关系 join 关系 using 属性

      

6. 附加运算

   1. 更名运算：as

      from子句中的两个关系可能存在同名属性会导致结果出现重复的属性名，在select中使用算术表达式会导致属性没有名字，因此可以通过重命名结果关系中的属性来实现上述矛盾

      

   2. 字符串运算（m）

      sql用单引号来标记字符串，并且相等运算大小写敏感，但是在mysql等数据库系统中不区分大小写

      ，为此我们选用like实现模式匹配：

      

      like比较运算中使用escape关键词来定义转义字符，使用反斜线来作为转义字符:

      如 :

      

      

   3. 星号的属性说明

      表示查找所有属性

      如:

      

   4. 显示次序

      通过 order by子句让元组按某个属性排列顺序

      如：

      

      默认升序，也可以用desc表示降序，asc表示升序

      如：

      

   5. where子句谓词

      1. between… and…比较运算符来说明一个值<=某个值且>=另一个值

      下述二者等价：

      

      not between 与between效果相反

      2. 可使用一个额外的条件查询从而不适用自然连接

      可通过记号（v1,v2,…,vn）表示一个n维元组， 其将通过字典顺序进行意义比较，如（a1,b1）<(a2,b2)等于a1<a1且b1<b2

7. 集合运算

   1. 中的 union intersect 和except对应并集，交集和差集

      1. union取并集时会自动去除重复，如果像保留重复，我们可以使用unionall 来代替union，其将保留重复元素

      

      只用union则只有一个

   2. intersect
      交集运算自动去除重复，想保留重复需使用intersect all代替

      

   3. except
      except从第一个输出不出现在第二个输入中的元组，自动去除重复 ，except all来不去除重复

      

      

8. 空值

   1. 如果算术表达式任一输入为空，则算术表达式结果恒为空，并且空值一般参与比较运算的比较时通常显示unknown

   2. 因此sql通常含有：true false和null三个逻辑值

   3. 布尔运算的结果

      

   4. 我们可以通过 is null 或者 is not null来判断是否为空值（is unknown/is not unknown 用来判断表达式结果）

   5. 元组如果所有属性相等，即使某属性值为空也被视为相同元组

   6. 用select.distinct时同上

9. 聚集函数

   是以一个值的集合为输入并返回单个值的函数

   5个固有函数：

   • 平均值：avg

   • 最小值：min

   • 最大值：max

   • 总和：sum

   • 计数：count

     除了sum和avg输入必须为数据集其他都可用于非数字数据类型集合中

     1. 基本聚集

        举例：平均工资

        

        我们也可在聚集表达式中使用 select count（ *）来计算某一关系中元组的个数

        y也可以用distinct来计算非重复的元组个数

        

     2. 分组聚集:

        可以通过group by酱聚集函数作用在一组元组集上

        • 该子句中给出的一个或者多个属性用来构造分组
          如：
          

        • 当不使用group by时默认当作为一个分组，
          如：
          
          ，或者：

        下例子说明了用与不用groupy by 的差距：

        

        不使用guroup by

        

        • 原关系：

          

          使用group by

        • 使用分组时，保证出现在select中但没有被聚集的属性只能出现在group by的子句中，例如下列的查询就是错误的

        

        因为ID并未出现在聚集函数中也没出现在groupby

     3. having子句

        分组限定条件，用来限定group by构成的分组，在某些特定场合相对于元组限定条件更加有用

        

        注：分组查询在谓词形成分组后才起作用

        与select类似，任何出现在having子句中，但没有被聚集的属性必须出现在group by语句中，否则间隔鄙人位数错误的

        

        该式子表达：在2009年讲授的每个每个课程段，如果该课程段至少有两名学生选课则该课程段所有学生的总分的平均值，

        并且，我们可以得知，一个查询中:我们先通过from子句计算一个关系然后通过where子句中的谓词应用到结果关系上,然后满足where条件的元组再依据group by进行分组，接着再进行having子句的筛选，最后利用select来查询结果中的元组产生最后的单结果元组

     4. 对空与布尔值的聚集

        如果查询的值中包含了空值，聚集运算会自动忽略输入中的null值

        

10. 嵌套子查询

    子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

    1. 集合成员资格

       通过连接词in来测试元组是否为集合中的成员，也可用not in测试是否不是成员

       如找出2009秋季和2010春季同时开课的所有课程

       可以采用：

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

       其与2009年秋季开课的课程集合与 2010年春季开课的课程集合的交运算的结果相等，体现sql具有一定的灵活性

       同理，not in表明 与内嵌子集补集的集合成员资格的检查

       同时，in和not in 也用用于枚举集合中，如下：

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

       该查询语句表示 查询找出既不叫“ Mozart”，也不叫“Einstein”的教师 的姓名

       in与not in不止可以测试单属性关系中中的成员资格，也可以用于测试任意关系的成员资格

       如：

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

       即：找出(不同的)学生总数，他们选修了ID为10101的教师所讲授的课程段

    2. 集合的比较

       嵌套子查询也能够对集合进行比较

       如考虑查询“找出满足下面条件的所有教师 的姓名，他们的工资至少比Biology系某一个教师的工资要高”，：

       select name
       from instructor
       where salary > some ( select salary
       					  from instructor
       					  where dept_name - ' Biology')
       #等价于
       select distinct T.name
       from instructor as T, instructor as S
       where T. salary > S. salary and S. dept_name = 'Biology'

       其中的 > some()表达：“至少比某一个要大”

       同理，sql也允许：<some,<=some,>=some,=some和<>some的比较，其中：

       • =some 等价于in(即查询两者的并集)
       • <>some并不等价于 not in,因为<>some表示为有部分不相等即满足条件的交集，其仍在biology中
       • 备注:any和some等价

       如要找到都大，我们可以采用：>all，SQL也允许 = all. = all和<> all的比较。作为练习，<>all等价于not in,但=all并不等价于in

    3. 空关系测试

       我们可以通过exists结构来对参数进行子查询，如果非空则返回true，我们使用exists结构还可以找出在2009年秋季学期和2010 年春季学期同时开课的所有课程

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

       可以发现外层查询的相关名称也可以用在where子句的子查询中

       我们对于 not exist来表明包含关系

       如：

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

       表明找岀选修了 Biology系开设的所有课程的学生

    4. 重复元组存在性测试

       通过 unique结构来检测是否存在重复元组，无重复返回true

       如例子：找出所 有在2009年最多开设一次的课程

       select T. course
       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)；
       #其等价于
       select T. course_id
       from course as T
       where I >= (select count( R. course_id)
       				from section as R
       				where T. course_id = R. courseand
       				R. year = 2009)

       not unique同理，我们也可以知道，如果unique为假，其定义为：当且仅当在关系中存在着两个元组t1和t2 且t1 =t2.由于在t1或t2的某个域为空时，判断t1=t2为假，所以尽管一个元组有多个副本.只要 该元组有一个属性为空，unique测试就有可能为真。

    5. from子句的子查询

       sql也允许在from子句查询

       例如 找岀系平均工资超过42 000美元的那些系中教师的平均工资：

       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, avg ( salary ) as avg_salary
       from instructor
       group by dept_name
       having avg (salary ) > 42000；

       同时，如果要找出工资总额最大的系，我们也可以轻易的用form子句的子查询查找,：

       select max (tot_salar))
       from ( select dept_name, sum( salary)
       		from instructor
       		group by dept_name)
         	  as dept_total ( dept_name, tot_salary )

       但这无法用having子句实现

       我们可以用lateral作为前缀，从而使from子句再嵌套子查询中使用来自from子句其他关系的相关变量

    6. with子句

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

       如：

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

       上述语句定义了临时关系maxjbudget,此关系在随后的查询中马上被使用了

       其相对于嵌套子查询在逻辑上更加清晰

       如 要査出所有工资总额大于所有系平均工资总额的系

       with deptjLotal ( 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 ； 

    7. 标量子查询

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

       如列出所有的系以及它们拥有的教师数：

       select dept_name,(select count( * )
       				  from instructor
       				  where department. dept_name = instructor. depl_name)
       	 as numinstructors
       from department;

       表量子查询可以出现在select,where和having子句中，如果子查询被执行后结果不止一个元组则会产生运行错误，

       • 注意从技术上讲标量子查询的结果类型仍然是关系，尽管其中只包含单个元组。然而，当在表达 式中使用标量子査询时，它出现的位置是单个值出现的地方

11. 数据库的修改（curd）

    1. 删除

       我们只能删除整个元组而不能之删除某些属性上的值

       SQL中 使用 delete from r where p来删除，r表示关系，p表示谓词

       如果无where子句，则r中的所有元组都会被删除

       • delete命令只能作用于一个关系。如果我们想从多个关系中删除元组，必须在每个关系上使 用一条delete命令。where子句中的谓词可以和select命令的where子句中的谓词一样复杂

         例子：除所有这样的教师元组，他们在位于Watson大楼的系工作

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

         delete虽然只能一次从一个关系中删除元组，但是我们可以在where子句中嵌套来引用任意数目的关系关系

    2. 插入

       要往关系中插入数据，我们可以指定待插入的元组，或者写一条查询语句来生成待插入的元组集合

       如常见的：

       insert into course values ( ' CS-437 '，‘ Database Systems' , Comp. Sci. ' , 4)；

       我们也可以指定属性插入：

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

       更通常的情况是：我们可以在查询结果基础上插入元组：

       如: 想让Music系每个修满144 学分的学生成为Music系的教师.其工资为18 000美元

       insert into instructor
       	select ID, name, dept_name, 18000
       	from student
       	where dept_name = ' Music' and tot_cred > 144； 

       我们并没有制定一个元组而是用select选出一个元组组合

       • 执行插入前需要先执行完select语句，如果同时执行插入动作，且没有主键约束的话，肯呢个会擦混入无数元组

         如： insert into student select' from student ；

         这样的请求就可能会插入无数元组。如果没有主码约束，上述请求会重新插入Mudem中的第一个 元组，产生该元组的第二份拷贝。由于这个副本现在是smdem中的一部分，select语句可能找到 它，于是第三份拷贝被插入到student中。第三份拷贝又可能被select语句发现，于是又插入第四 份拷贝，如此等等，无限循环

         但是，先完成select语句的执行可以避免这样的问题。 这样，如果在student关系上没有主码约束，那么上述insert语句就只是把student关系中的每个元 组都复制一遍

         元组中未被插入的属性值我们赋null

    3. 更新

       我们可能希望在不改变整个元组的情况下改变其部分属性的值。为达到这一目的， 可以使用update语句

       如常见的：

       update instructor set salary = salary * 1.05 ；

       或者

       update instructor
       set salary = salary * 1.05
       where salary < 70 000 ； 

       我们也可以用嵌套来引用待更新的关系

       update instructor
       set salary = salary ' 1. 05
       where salary < (select avg (salary)
       from instructor)

       如果写两条update语句时要注意顺序，否则会导致更新值的混乱

       因此我们采用case结构避免次序引发的问题

       其一般语句如下

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

       当i是第一个满足的pred1, pred2…pred n，此操作就会返回result i,如果没有一个谓词可以满足，则返回result0。case语句可以用在任何应该出现值的地方

       例子：

       update instructor
       set salary = case
       when salary <= 100000 then salary 1. 05
       else salary * 1.03
       end

       标量子查询也可用于set子句中

       如：我们把每个student元组的tow_cred属性值设为该生成功学完的课程学分的总和。我们假设如果一个学生在 某门课程上的成绩既不是’F’,也不是空，那么他成功学完了这门课程。

       则有：

       update student S
       set tot_cred =(
       	select case
       		when sum(credits) is not null then sum(credits)
       		else 0
       		end
       	from takes natural join course
       	where S. ID = takes. ID and
       		takes, grade <> ' F' and
       		takes, grade is not null)； 

    ​

总结

1. SQL是最有影响力的商用市场化的关系査询语言。SQL语言包括几个部分:
   • 数据定义语言(DDL),它提供了定义关系模式、删除关系以及修改关系模式的命令
   • 数据操纵语言(DML),它包括查询语言，以及往数据库中插入元组、从数据库中删除元组和修改 数据库中元组的命令
2. SQL的数据定义语言用于创建具有特定模式的关系。除了声明关系属性的名称和类型之外，SQL还 允许声明完整性约束，例如主码约束和外码约束
3. SQL提供多种用于查询数据库的语言结构，其中包括select, from和where子句。SQL支持自然连接 操作
4. SQL还提供了对属性和关系重命名，以及对査询结果按特定属性进行排序的机制
5. SQL支持关系上的基本集合运算，包括并、交和差运算.它们分别对应于数学集合论中的U、G和 -运算。
6. SQL通过在通用真值true和false外增加真值“unknown”，来处理对包含空值的关系的查询
7. SQL支持聚集，可以把关系进行分组，在每个分组上单独运用聚集。SQL还支持在分组上的集合 运算。
8. SQL支持在外层査询的where和from子句中嵌套子查询。它还在一个表达式返回的单个值所允许出 现的任何地方支持标量子查询
9. SQL提供了用于更新、插入、删除信息的结构

术语

后记

怎么SQL这一章这么多，要死了，还以为能这个月能看完这本书的