跳转至

Chapter 3 Introduction to SQL

Data Definition

Domain Types in SQL

  • char(n): 定长字符串，长度为 n

Note

SQL 与 C 不同，SQL字符串结尾没有\0

  • varchar(n): 变长字符串，最大长度为 n

  • int: 整数

  • smallint: 小整数
  • numeric(p, d): p 位数字，其中 d 位小数
  • real, double precision: 浮点数
  • float(n): 浮点数，精度为 n

Built-in Data Types in SQL

  • date: 日期，例如：date '2001-01-01'
  • time: 时间，例如：time '12:00:00' time '18:26:46.75'
  • timestamp: 日期和时间，例如：timestamp '2001-01-01 12:00:00'
  • interval: 时间间隔，例如：interval '2' day
    • 可由 date, time, timestamp 相减得到
    • 可与 date, time, timestamp 相加
  • date, time functions:
    • current_date(), current_time()
    • year(x), month(x), day(x), hour(x), minute(x), second(x)

Create Table Construct

create table r(
    A1 D1,
    A2 D2,
    ...
    An Dn,
    (intergrity constraint1),
    (intergrity constraint2),
    ...
    (intergrity constraintn)
)
Eg 

create table instructor(
    ID char(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8, 2)
)
not null: 该属性不能为空

create table instructor(
    ID char(5),
    name varchar(20) not null,
    dept_name varchar(20),
    salary numeric(8, 2),
    primary key(ID)
    foreign key(dept_name) references department
)
primary key 要求该属性是唯一的，且不能为空 foreign key 要求该属性的值在另一个表中有对应

create table student(
    ID char(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3, 0) default 0
)

foreign key

create table course(
    course_id varchar(8) primary key,
    title varchar(50),
    dept_name varchar(20),
    credits numeric(2, 0),
    foreign key(dept_name) references department(dept_name)
);
对于外键的删除和更新操作，为保证约束完整性，有下面几种方式：

  • set null: 将外键设置为 null
  • restrict: 禁止删除或更新
  • set default: 将外键设置为默认值
  • cascade: 删除或更新外键时，级联删除或更新
    • 级联可能会产生连锁反应，将整张表都删空

Drop and Alter Table Constructs

  • drop table r : 删除表 r 及其内容

  • delete from r : 删除表 r 中的所有内容，但保留其结构

  • alter table r add A D : 在表 r 中添加属性 A，其类型为 D

    • 新加入的属性的所有元组的初值为 null

  • alter table r drop A : 删除表 r 中的属性 A

    • 很多 DBMS 不支持删除属性

SQL and Relational Algebra

Eg

select A1, A2, ..., An
from r1, r2, ..., rm
where P
对应的关系代数表达式为: \(\i_{A1, A2, ..., An}(\sigma_P(r1 \times r2 \times ... \times rm))\)

Eg

select A1, A2, sum(A3)
from r1, r2, ..., rm
where P
group by A1, A2
对应的关系代数表达式为: \(_{A1, A2}\mathical{G}_{sum(A3)}(\sigma_P(r1 \times r2 \times ... \times rm))\)

Eg

select A1, sum(A3)
from r1, r2, ..., rm
where P
group by A1, A2
对应的关系代数表达式为: \(\Pi_{A1, sum(A3)}(_{A1, A2}\mathical{G}_{sum(A3)}(\sigma_P(r1 \times r2 \times ... \times rm)))\)

Basic Query Structure

The select Clause

select 语句列举出了结果所需的属性，与关系代数中的 \(\Pi\) 操作对应

  • SQL 命名大小写不敏感

  • SQL 在查询结果中允许重复元组

    • 如果要强制删除重复元组，可以使用 select distinct
    select distinct dept_name 
from instructor
    • all 关键字表示不删除重复元组
    select all dept_name
from instructor
    • 通配符 * 表示所有属性
    select *
from instructor
    • select 语句可以包含表达式
    select name, salary / 12
from instructor

The where clause

where 语句用于规定查询的条件，与关系代数中的 \(\sigma\) 下标对应

  • where 语句中可以包含多个条件，使用 and, or, not 连接

    select name, salary
from instructor
where salary > 70000 and dept_name = 'Biology'

  • where 语句中可以使用 between进行范围查询

    select name, salary
from instructor
where salary between 60000 and 80000

  • 可以直接比较元组：

    select name, salary
from instructor
where (dept_name, salary) = ('Biology', 80000)

    The from Clause

    from 语句用于指定查询的表，与关系代数中的 \(\times\) 操作对应

  • 例如查询 instructor \(\times\) teaches 的结果

    select * 
from instructor, teaches
    • 会产生所有可能的组合

  • 和where一起使用更有用一些

Additional Basic Operations

Nature Join

自然连接是一种特殊的连接，它只连接那些在连接属性上具有相同值的元组

  • 例如查询 instructor \(\bowtie\) teaches 的结果

    select * 
from instructor natural join teaches
    • instructor(ID, name, dept_name, salary)
    • teaches(ID, course_id, sec_id, semester, year)

  • 又如查询导师名字以及他们教授的课程ID：

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

    select name, course_id
from instructor natural join teaches

  • 另一例: 查询跨系选课的学生

    • student(ID, name, dept_name, tot_cred)
    • takes(ID, course_id, sec_id, semester, year, grade)
    • course(course_id, title, dept_name, credits)
    select distinct student.ID
from (student natural join takes) 
    join course using (course_id)
where student.dept_name <> course.dept_name

  • 有时会出现不同表中两个毫不相关的属性具有相同名称的情况，此时自然连接会出现问题

The Rename Operation

重命名操作用于给表或属性取别名

  • old name as new name

    select ID as instructor_id
from instructor
    + as 可以省略，instuctor as T 等价于 instuctor T + 在Oracle数据库系统中，as 必须省略

String Operations

  • like 操作符用于模糊查询:

    • % 表示任意长度的字符串
    • _ 表示任意一个字符

    select name
from instructor
where name like '%dar%'
    + 单引号表示字符串 + 字符串大小写敏感

  • SQL 支持很多字符串操作例如：

    • concatenate: ||
    • 大小写转换
    • 获取字符串长度、子串等

Ordering the Display of Tuples

order by 语句用于对查询结果进行排序(如字典序等)

  • asc 升序，desc 降序

    select name, salary
from instructor
order by salary desc
    + order by 语句可以包含多个属性

    select name, salary
from instructor
order by dept_name, salary desc

The limit Clause

limit 语句用于限制查询结果的数量

  • limit 语句包含一个或者两个参数，且必须是非负整数
    • limit offset, row_count
    • limit row_count == limit 0, row_count

Set Operations

  • union, intersect, except 这三种都会自动删除重复元组
  • union all, intersect all, except all 不会删除重复元组

Null Values

null 表示未知或者不存在的值

  • 任何包含null的表达式都会返回null
  • null 与任何值的比较都是unknown
unknown 相关逻辑运算
  • OR:
    • true or unknown = true
    • false or unknown = unknown
    • unknown or unknown = unknown
  • AND:
    • true and unknown = unknown
    • false and unknown = false
    • unknown and unknown = unknown
  • NOT:
    • not unknown = unknown

Aggregate Functions

Eg 
select avg(salary)
from instructor
where dept_name = 'Biology'

select count(distinct ID)
from teaches
where semester = 'Fall' and year = 2009

  • 在 select 语句中使用聚集函数时，select 语句中的属性如果不在聚合函数内则必须在 group by 语句中 原因是 SQL 会先对属性进行分组，然后再对每个组进行聚合操作

    • 下面是一个错误的例子，属性ID不在聚合函数中，也不在 group by 语句中
    select dept_name, ID, avg(salary)
from instructor
group by dept_name;

Having Clause

having 语句用于对分组后的结果进行过滤

  • having 语句中的条件与 where 语句类似， 但是 where 语句是对原始数据进行过滤，而 having 语句是对分组后的数据进行过滤

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

  • having 语句中的条件可以包含聚合函数

    select dept_name
from student
group by dept_name
having 1-count(distinct name) / count(id) < 0.001;

Nested Subqueries

  • A subquery is a select-from-where expression that is nested within another query

set membership

  • in / not in: 判断一个或多个值是否在一个集合中

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

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

set comparisons

  • some / all : 选择集合中的某些或者全部元素

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

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

scalar subqueries

  • 一个标量子查询返回一个单一的值，此时不能用some或all关键字

    ```sql select name from instructor where salary*10 > ( select budget from department where department.dept_name = instructor.dept_name )

test for empty relation

  • exists / not exists : 判断子查询是否为空

    select dept_name
from department
where not exists (
    select *
    from instructor
    where instructor.dept_name = department.dept_name
)
    • \(X - Y = \emptyset\) 等价于 \(X \subset Y\): 
      select distinct S.ID, S.name
from student as S 
where not exists (
    (select course_id
    from course 
    where dept_name = 'Biology')
    except
    (select course_id
    from takes as T
    where S.ID = T.ID)
)
      查询选择了所有生物系的课程的所有学生

Modification of the Database

最后更新: 2025年3月6日 21:01:13
创建日期: 2025年3月3日 18:48:43