数据库概念 SQL（一） 第三课笔记

Posted on | In |

2018年3月8日
13:35

写在前面

本笔记仅仅是本人在上课时的一些随手记录，并不完整也不完全正确。
如有错误，请在评论中或直接联系我指正，谢谢！

原始文件下载：(mht)(pdf)

What is SQL?

DDL Data-definition language DDL
DML Data-manipultaion language DML

e.g:

9OYuv9.png

ID: Attributes of the resulting relation

Instructor: relation to which the query refers

Salary>65000: Condition that must be satisfied

Define

9OYngJ.png

Retrieve

9OYMuR.png

Insert, delete or update

9OYlHx.png

Data Control Language

9OYYCD.png

SQL: History

  • Commercial systems offer most, if not all, SQL-92 features, plus varying
    feature sets from later standards and special proprietary features

    • Not all examples here may work on your particular system.

An Action

9OYDVP.png

Main contents of SQL

  • Data definition
  • Basic query structure
  • Set operations
  • Aggregate functions
  • Null values
  • Nested subqueries
  • Complex queries
  • Views
  • Modification of the database

Data definition - data definition language

  • The schema for each relation
  • The domain of values associated with each attribute
  • Integrity constraints
  • The set of indices to be maintained for each relations
  • Security and authorization information for each relation
  • The physical storage structure of each relation on disk

Basic Domain Types

9OYt8e.png

Schema definition in SQL - Create Table Construct

9OYdKA.png

Integrity Constraints in Create Table

9OYNgH.png

Check(P)： 每一个元组必须满足P条件，是一个创建表时使用的约束条件

9OY2vj.png

9OYs58.png

Drop and Alter Table Constructs

9OYc8g.png

Basic Query Structure

9OYhbq.png

SQL查询的结果仍是一个关系

The select Clause

  • select语句列出一次查询所想要的属性
    • 与关系代数的投影运算相对应

e.g.：find the names of all instructors

1
2
select name
from instructor
  • 注意：SQL名字是大小写不敏感的 （i.e., 大小写字母都可使用）

  • SQL允许关系中有重复，查询结果中亦是如此

  • 为了强制消除重复，必须在选择后加入关键词 DISTINCT

e.g. Find the names of all departments with instructor, and remove
duplicates

1
2
select DISTINCT dept_name
from instructor
  • ALL关键词指的是重复结果不会被删除
1
2
select all dept_name
from instructor
  • select语句中使用星号（*）表示“所有属性”
1
2
select *
from instructor
  • select语句可以包含算数表达式，
    包括运算（+，-，*，/）以及对元组的常量或属性的运算

e.g.

1
2
select ID, name, salary/12
from instructor

这个语句表示获得一个关系，其与instructor关系相同，但是salary属性的值被除以12

The where Clause

  • where语句规定结果必须满足的条件
    • 与关系代数的选择运算对应

e.g. find all instructors in Comp. Sci. dept with salary > 80000

1
2
3
select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000

  • 比较结果可以与逻辑连接词（AND OR NOT）一起用
  • 算术表达式结果可以被比较

The from Clause

  • from语句是查询所处的关系
    • 与关系代数中的笛卡尔乘积相对应

e.g. Instructor X teaches

1
2
select *
from instructor, teaches

产生所有可能的instructor - teaches对

  • 笛卡尔乘积的输出并不直接有用，不过与where语句一起用之后就有用了

Joins

9OYWKs.png

Natural Join

  • 即关系代数中的natual join运算，可以直接关键词使用
1
2
select *
from instructor natural join teaches

9OYIaV.png

e.g. List the names of instructors along with the course ID of the courses that they taught

1
2
3
4
5
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID
select name, course_id
from instructor natural join teaches

  • 注意：两张表之间可能会存在同属性名，语义却不同的情况

List the names of instructors along with the titles of courses that they teach

注意course的dept_name和instructor的dept_name表达的语义不相同

  • Incorrect version (makes course.dept_name = instructor.dept_name) 

    1
    2
    select name, title  
    from instructor natural join teaches natural join course;

  • Correct version

    1
    2
    select name, title  
    from instructor natural join teaches, course   where teaches.course_id = course.course_id;

  • Another correct version

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

The Rename Operation

  • SQL允许重命名关系或属性（关键词as）
1
old-name as new-name

e.g.

1
2
select ID, name, salary/12 as monthly_salary
from instructor

e.g. Find the names of all instructors who have a higher salary than some instructor in ‘Camp. Sci’.

1
2
3
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.'

  • as关键词是可选的，可以忽略
    • 在Oracle中，as必须忽略

String Operations

  • SQL包含了一个字符串比对运算符，用来对字符串进行比较。”Like”
    运算符使用两个特殊字符

    • % 与所有字串配对

    • _ 与所有字符对应

e.g. find the names of all instructors whose name includes the substring “dar”

1
2
3
select name
from instructor
where name like '%dar%'

Match the string “100 %”

1
like '100 \%' escape '\'

  • 这些形式是大小写敏感的（与select中name大小写不敏感不同）

  • 形式对应的例子

    • ‘Intro%’ 开头为”Intro”的串
    • ‘%Comp%’ 有”Comp”子串的串
    • ‘_ _ _‘ 三字符串
    • ‘_ _ _ %’ 至少三字符的串

  • SQL支持一些字符串操作

    • 级联（并列）”||“
    • 大小写转换
    • 串长，抓取子串等

Ordering the Display of Tuples

List in alphabetic order the names of all instructors

1
2
3
select distinct name
from instructor
order by name

  • 可以使用desc或asc关键词来确定排序的升降 默认为升序

    1
    order by name desc

  • 可以同时对多个属性进行排序

Where Clause Predicates

  • SQL包含了一个between比较运算符

e.g. Find the names of all instructors with salary between $90,000 and $100,000 (that is, $90,000 and $100,000)

1
2
3
select name
from instructor
where salary between 90000 and 100000

元祖比较

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

Duplicates

  • 在有重复项的关系中，SQL可以定义在结果中显示多少份元组复制项

  • Multiset版本的关系代数运算符 - 以关系r1与r2为例

    • σθ (r1): If there are c1 copies of tuple t1 in r1, and t1 satisfies
      selections σθ,, then there are c1 copies of t1 in σθ(r1).

    • ΠA (r ): For each copy of tuple t1 in r1, there is a copy of tuple
      ΠA (t1) in ΠA (r1) where ΠA (t1) denotes the projection of
      the single tuple t1.

    • r1 x r2 : If there are c1 copies of tuple t1 in r1 and c2 copies of
      tuple t2 in r2, there are c1 x c2 copies of the tuple t1. t2 in r1 x r2

9OYb24.png

Set Operations

  • 集合操作并集 (union)，交集 (intersect)和补集 (except)
    基于关系操作，与关系代数的交并补相对应

  • 上述所有的操作自动去除重复项；为了保留重复项，必须使用对应的multiset版本运算符union
    all, intersect all 和 except all

9OY7PU.png

e.g.

9OYQD1.png

Null Values

  • 元组的一些属性值可以为空值，用null来表示
  • null表示未知值或该值不存在
  • 所有带null的算数运算的结果都是null
    • e.g: 5 + null returns null
  • 谓词is null可用来检查空值

    e.g. find all instructors whose salary is null.

    1
    2
    3
    select name
    from instructor
     where salary is null

Null Values and Three Valued Logic

  • 所有与空值的比较结果都为unknown
    • Example: 5 < null or null > null or null = null

  • 使用真值unknown的三值逻辑

    • OR:
      • (unknown or true) = true
      • (unknown or false) = unknown
      • (unknown or unknown) = unknown
    • AND:
      • (T and U) = U
      • (F and U) = F
      • (U and U) = U
    • NOT:
      • (not unknown) = unknown
    • 如P为U “P is unknown” 为真

  • 如果where谓词后为U，那此谓词为F

Aggregate Functions

  • 这些函数在一个关系的一列中的多个值上操作

    • Avg: average value
    • Min: minimum value
    • Max: maximum value
    • Sum: sum of values
    • Count: number of values

e.g. find the average salary of instructors in the Computer Science department

1
2
3
select avg (salary)
from instructor
where dept_name = 'Comp. Sci.';

Find the total number of instructors who teach a course in the Spring 2010 semester

1
2
3
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2010

Find the number of tuples in the course relation

1
2
select count (*)
from course;

Aggregate Functions - Group By

Find the average salary of instructors in each department

1
2
3
select dept_name, avg (salary)
from instructor
group by dept_name

  • Note: departments with no instructor will not appear in result

9OY3E6.png

  • select语句中的属性（且在集合函数外的）必须在group by中出现

/* erroneous query */

1
2
3
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

Aggregate Functions - Having Clause

Find the names and average salaries of all departments whose average salary is greater than 42000

1
2
3
4
select dept_name, avg (salary)
from instructor
group by dept_name
having avg (salary) > 42000;

  • 注意：having语句中的谓词是在组形成之后再进行的；相对地，where语句则是在之前进行

e.g.

9OY8UK.png

为上述代码

操作顺序

  1. From
  2. Where
  3. Group by
  4. Having
  5. select

Null Values and Aggregates

Total all salaries

1
2
select sum (salary)
from instructor

  • 上述语句忽略了空值
  • 如果没有非空值，结果为空值
  • 所有集合运算，除去count(*)，都忽略操作属性值中带空值的元组
  • 如果其中只有空值
    • Count 返回 0
    • 其他集合运算返回 null