《数据库系统概念》ch1 - ch3 数据库概念和SQL介绍

ch2 关系模型介绍

关系数据库的结构

关系数据库由 表 的集合构成

  • 关系 ：表

  • 元组 ：行

  • 属性 ：列

    • 域 (domain) ：

      每个属性允许取值的集合。

      域是原子的：域中的元素不可再分

      空值 (null value)：表示未知或者不存在

  • 关系实例 ：一个关系的特定实例 (关系实例包含一组特定的行)

码

  • 超码 (superkey) 一个或多个属性的集合，可以在一个关系中唯一地标识出一个元组

  • 候选码 (candidate key) 一个超码，满足其所有真子集都不是超码，即最小的超码

  • 主码 (primary key) 被选中作为在一个关系中区分不同元组的主要方式的候选码

  • 外码 (foreign key) 一个关系中某个属性的域 (引用关系) 和另一个关系中某个属性的域 (被引用关系) 相同

关系代数

  • σ 选择 (select)

  • Π 投影 (project)

  • ∪ 并 (union)

  • - 差 (set differencr)

  • x 笛卡尔积 (Cartesian product)

  • ρ 更名 (rename)

ch3 SQL介绍

SQL DDL

  • 基本类型

    1. char (n) 固定长度字符串，长度为 n，全称 character

    2. varchar (n) 可变长度字符串，最大长度为 n，全称 character varying

    3. int 整数，大小由机器决定，全称 integer

    4. smallint 小整数，大小由机器决定

    5. numeric (p, d) 算上符号位总共 p 位，小数点右侧由 d 位

    6. real, double precision 浮点数和双精度浮点数，大小由机器决定

    7. float (n) 精度至少为 n 位的浮点数

  • 基本定义模式

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(dept_name)); -- 外键约束

  • drop table r 彻底删除关系 r

  • delete from r 保留关系 r 而删除 r 中的记录

  • alter table r add A D 在关系 r 中新增名为 A 、类型为 D 的属性

  • alter table r drop A 在关系 r 中删除属性 A

SQL query

  • 单关系查询

    • select dept_name from instructor; 无去重查询

    • select all dept_name from instructor; 显式无去重查询

    • select distinct dept_name from instructor; 去重查询

    • select name from instructor where dept_name='Comp.Sci' and salary>70000;

      有条件地查询

  • 多关系查询

    select A1, A2, ... , An
from r1, r2, ... , rn
where P;
    • select 列出查询结果中需要出现的属性
    • from 列出查询求值中需要访问的关系列表
    • where 作用在from字句的关系上的谓词

    多关系查询会进行笛卡尔积运算，where 字句可以使用谓词来进行连接 (限制笛卡尔积所创建的组合)

    -- 作用：查询计算机科学系的教师姓名及他们教的课程标识
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and instructor.dept_name = 'Comp.Sci';

-- 取别名，在 from 语句中可以省略 as
select T.name, S.course_id
from instructor T, teaches S
where T.ID = S.ID;

-- 查询生物系，工资至少比一人高的教授的名字
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

  • 字符串运算

    标准SQL区别字符串的大小写，而MySql和SqlServer不区分大小写，这取决于数据库的具体实现。

    • like 模式匹配 (not like)

      1. % 匹配任意字符串
      2. _ 匹配任意单个字符
      'Intro%' -- 匹配以 Intro 开头的任意字符串
'%Intro%' -- 匹配任何包含 Intro 的字符串
'___' -- 匹配只含有三个字符的字符串
'---%' -- 匹配至少含有三个字符的字符串

select dept_name
from department
where building like '%Waston%';

    • escape 自定义转义字符 (用于 like 语句)

      like 'ab\%cd%' escape '\' 表示匹配以 "ab%cd" 开头的字符串

    • PostgreSql 还提供了 similar to 运算，其模式定义语法类似于正则表达式

  • 排序

    order by ... asc / desc

    • asc 升序，desc 降序，可以对多个属性进行排序

  • where 字句谓词

    • where salary between 9000 and 10000; = where salary <= 10000 and salary >= 9000;
    • not between 与 between 相反
    • 可以用(属性1，属性2，属性3 ...) 来构造元组 （行构造器） ，元组间可以进行比较

集合运算

  • union 并

    • unoin语句是自动去重的，想要保留重复元素需要使用 union all 代替 union
    -- 寻找在2017年秋季或者2018年春季开课的课序号
(select course_id
from section
where semester = 'Fall' and year = 2017)
unoin
(select course_id
from section
where semester = 'Sprint' and year = 2018);

  • intersect 交

    • intersect语句也是自动去重的，可以使用 intersect all 保留重复元素

  • except 差

    • 同上，except语句也自动去重，except all 可以保留重复元素

空值 (null value)

  • null 进行算术运算的结果任为 null

  • null 进行比较运算的结果是 unknown

  • where字句中可以进行and、or、not的布尔运算，对应规则：

    true and unknown -> unknown
false and unknown -> false
true or unknown -> true
false or unknown -> unknown
not unknown -> unknown

  • select distinct 对于 null 的处理：

    • 如果两个值其余属性都相同，且剩下的属性都是 null，则被视为相等

聚集函数

聚集函数(aggregate function) 以值集为输入，返回单个值的函数。

  • 基本的聚集函数

    • avg

      select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.Sci';
      • 计算平均值时一般不会去重

    • min

    • max

    • sum

    • count

      -- 查询在2018年开课的老师的id
select count(distinct id)
from teachers
where semester = 'Spring' ans year = 2018;

-- 查找元组数量
select count(*)
from course;
      • 不允许在 count(*) 中使用 distinct，在 max 、min 中 distince 是合法的，可以用 all 来显示表示无去重。

  • 分组聚集

    通过 group by 语句将所选属性上取值相同的元组分在一个组内。

    • select语句中的属性，要么作为聚集函数的参数，要么出现在 group by 语句中。

    • having 语句

      • 在 group by 分组后应用 having 语句中的谓词
      -- 寻找平均工资高于 42000 的部门
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

  • 对空值和布尔值的聚集

    • 除了 count(*) 之外所有的聚集函数都忽略 null

运算顺序

写的顺序：select -> from -> where -> group by -> having -> order by

执行顺序：from -> where -> group by -> having -> select -> order by

  1. 首先根据 from 语句计算出一个关系
  2. 如果有 where 语句，将 where 语句的谓词应用在关系上
  3. 如果有 group by 语句，满足 where 谓词的元组通过 group by 语句放入一个分组
  4. 如果用 having 语句，将其应用到每个分组，不满足条件的分组被抛弃
  5. select 字句利用剩下的分组产生查询结果，在每个分组上采用聚集函数获取单个结果元组
  6. 如果有 order by 语句，对 select 获得的查询结果进行排序

嵌套子查询

  • 集合成员资格

    • in & not int

      -- 查询在2017年秋季和2018年春季都开放的课程的课程号
select distinct course_id -- 这里使用 distince，是因为 in 是默认去重的
from section
where semester = 'Fall' and year = 2017
and course_id in (
	select course_id
    from section
    where semester = 'Spring' and year = 2018);

-- in 和 not 也可以用于枚举集合
select distinct name
from instructor
where name not in ('Mozart', 'Einstein');

-- 查找选修了ID为10101的老师的课程的学生人数
select count(distinct ID)
from tasks
where (course_id, sec_id, semester, year) in (
	select course_id, sec_id, semester
    from teaches
    where teaches.ID = '10101');

  • 集合比较

    • some or any (some 和 any 是同义的)

      -- 寻找生物系中，工资不是最低的人的姓名
select name
from instructor
where salary > some(
	select salary
    from instructor
    where dept_name = 'Biology');
      • =some 和 in 等价
      • <>some 与 not in 不等价，A <>some B 表示A中元素不全在B中

    • in & not in

      • <>all 等价于 not int
      • =all 与 in 不等价

  • 空关系测试

    可以测试一个子查询的结果中是否存在元祖。

    • exists

      exists作为参数的子查询非空时返回 true 值

      -- 查询在2017年秋季和2018年春季都开课的所有课程
select course_id
from section as S
where semeste = 'Fall' and year = 2017
and exists (
	select *
	from section as T
	where semester = 'Spring' and year = 2018
	and S.course_id = T.course_id);
      • 使用了来自外层查询的相关名称的子查询被称为相关子查询。
      • 如果一个相关名称既在子查询中局部定义，有在包含该子查询的查询中全局定义，则局部定义有效。

    • not exists

      • 可以使用 not exists 模拟包含(超集)运算：关系A包含关系B not exists(B except A)

        -- 查询选修了生物系所有课程的学生
select S.ID, S.name
from student as S
where not exists(
	(select course_id
    from course
    where dept_name = 'Biology')
    except
    (select T.course_id
    from tasks as T
    where S.ID = T.ID)
);

  • 重复元组存在性测试

    • unique 在作为参数的子查询结果中没有重复元组时返回 true

      -- 查询在2017年最多开设一次的课程
select T.course_id
from course as T
where unique (
	select R.course_id
	from section as R
	where T.course_id = R.course_id
	and R.year = 2017);
      • 将 unique 改为 not unique 即可查找至少开设了两次的课程

  • from 子句中的子查询

    任何 select-from-where 表达式返回的结果都是关系，可以被插入另一个表达式的任意位置；

    -- 查询部门平均高于42000的部门及其平均工资
-- 这个代码在postgresql中无法运行，因为没有为子查询结果取别名
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
     from instructor
     group by dept_name)
where avg_salary > 42000;

-- 可以使用as给这个关系、属性取别名
select dept_name, avg_salary
from (select dept_name, avg(salary)
     from instructor
     group by dept_name)
     as dept_avg(dept_name, avg_salary)
where avg_salary > 42000;

    • 并非所有的sql都支持在from中嵌套子查询，mysql和postgresql要求每个子查询结果关系必须被命名。

    • SQL:2003开始，可以用lateral关键字作为前缀，from子句中的子查询可以访问在它前面的表或子查询的属性。

      -- 查询每位教师的名字、工资及所在系的平均工资
select name, salary, avg_salary
from instructor I1, lateral (select avg(salary) as avg_salary
                            from instructor I2
                            where I2.dept_name = I1.dept_name);

  • with 子句

    用于定义临时关系，仅对包含 with 的子查询有效。

    其目的仅仅是使逻辑更清晰。

    -- 查出工资总额大于平均工资总额的部门
with dept_total (dept_name, value) as
	(select dept_name, sum(salary)
    from instructor
    group by dept_name),
dept_total_avg as
	(select avg(value)
    from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

  • 标量子查询

    返回单个值的表达式可以出现在任何地方。

    其本质任是关系，SQL会从单个元组中隐式地取出相应的值。

  • 不带from子句的标量

    -- 查询平均每位教师讲授的课程段数 (整数除法会向下取整)
select((select count(*) from teaches) / (select count(*) from instructor));

    • 有些sql实现会报出缺少from子句错误，oracle中可以添加一个from dual(一个预定义关系，只包含单个属性)

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

数据库修改

  • 删除 delete

    • delete from r where p; 先找出所有满足p的关系，然后将其删除
    • delete from p 删除所有元组，保留关系

  • 插入 insert

    • insert into {talbe} (属性1，属性2，...) values (值1，值2，...)

      属性的值的顺序无关精要，值会一一对应，属性的数量可以小于关系中属性的数量。

      没有被赋值的属性会被默认置为null。

      insert into {table} values (值1,值2,...) 会按照顺序进行插入

      • 不推荐这种方法，因为数据库管理员可能会alter修改属性，这样之前的测试方案就无法使用了。

    • 这行代码如果没有主码约束，会进入死循环，不断复制自身

      insert into student select * from student;

  • 更新 update

    • update 属性 set 新值 update instructor set salary = salary * 1.05;

    • case 结构可以对2一个属性，根据条件进行不同的调整

      -- 工资低于10000元的人涨5%工资，否则涨3%工资
update instructor
set salary = case
	when salary <= 10000 then salary * 1.05
	else salary * 1.03
end;

      • 如果使用两条update语句，可能会对一个人增加两次工资

      • case 的基本结构：

        case
	when pred1 then result1
	when pred2 then result2
	...
	when predn then resultn
	else result0
end
posted @ 2022-03-25 11:00  PigPigHero  阅读(126)  评论(0)    收藏  举报