美文网首页数据库知识点程序员
​《数据库系统概念》4-DDL、集合运算、嵌套子查询

​《数据库系统概念》4-DDL、集合运算、嵌套子查询

作者: zhixin9001 | 来源:发表于2017-11-15 22:17 被阅读0次

    一、DDL

    a) SQL Data Definition

    SQL的基本数据类型有char(n)、varchar(n)、int、smallint、numeric(p,d)、real,double precision、float(n)等,int smallint real float依赖机器的精度

    b) char(n)不够的用空格补齐,比较两个char(n)时会先补齐成一样的长度;比较char和varchar时有的数据库会先补齐,但有的不会,所以存储字符串时最好都用varchar;

    c)表结构的定义:

    类似Create table department (dept_name varchar(20), budget numeric(12,2), primary key(dept_name));

    定义表结构的通用形式为:

    Create table r

    (A1, D1,

    … ,

    integrity-constraint1,

    integrity-constraint2,

    …);

    常用的一致性约束类型有:主键、外键、非空

    二、集合运算和null

    a) 集合运算包括并集union、交集intersect、差集except。比如要查询2009年秋季开课的课程和2010年春季开课课程分别为:

    select course_id

    from section

    where semester=’Fall’ and year=2009

    select course_id

    from section

    where semester=’Spring’ and year=2010

    要得出两个季度所有的课程可以用Union;使用intersect可以查找到两个季度都开课的课程;而使用except可以得到第一个结果集中存在但第二个结果集不存在的内容,这三种操作如果不需要去重,可以对应使用union all, intersect all, except al。

    b)Null

    null与其它值类型的算术运算结果都为null;

    比较运算中,1

    AND :

    true,unknown=unknown

    false,unknown=false

    unknown, unknown= unknown

    OR:

    true, unknown=true

    false, unknown= unknown

    unknown, unknown= unknown

    NOT:

    NOT unknown= unknown

    三、嵌套子查询(Nested Subqueries)

    子查询是嵌套在另一个查询中的select-from-where表达式,用于对集合的成员资格进行检查以及对集合的比较。

    a)检查集合成员资格

    比如前面用交集操作实现的查询也可以写为:

    select course_id

    from section

    where semester=’Fall’ and year=2009 and

    course_id in (select course_id

    from section

    where semester=’Spring’ and year=2010)

    可见SQL实现同一查询目的的方法可以是多样的。

    b)集合的比较

    集合比较用到的写法有>some, >=some, =some, >all等,比如要查找比生物系中至少一位教师工资高的人,可以写为:

    select distinct T.name

    from instructor as T, instructor as S

    where T.salary > S.salary and S.dept name = ‘Biology’

    也可以使用>some的写法:

    select name

    from instructor

    where salary > some (select salary

    from instructor

    where dept name = ‘Biology’);

    c)空关系测试

    可以用exist来测试关系中是否存在元组,对应还有not exist

    前面要查询的2009秋季和2010春季都开课的课程,也可以写为:

    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);

    d)测试重复元组

    使用unique来检查关系中是否存在重复元组,对应也有not unique。比如要查找2009年秋季至多开课一次的课程:

    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 = 2009);

    对于当时没开课的课程,因为结果为empty,unique对empty的计算结果也是true

    e)From子句中的子查询

    在from子句中也可以使用子查询,因为任何select-from-where返回的结果都是关系,所以可以在其上面继续使用from子句。

    查询平均薪水超过42000的部门,如果使用having子句可以是:

    select dept name, avg (salary) as avg_salary

    from instructor

    group by dept name

    having avg (salary) > 42000;

    也可以采用From子查询的方式:

    select dept name, avg_salary

    from (select dept name, avg (salary) as avg salary

    from instructor

    group by dept name)

    where avg_salary > 42000;

    同时还可以为from子查询的的表和字段重命名:

    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;

    f)With子句

    with子句用来定义临时关系,这个定义只对包含with子句的查询有效。比如查询拥有最多预算的部门,可以使用子查询,但子查询往往结构复杂、可读性差,而使用with子句就会好很多:

    with max budget (value) as

    (select max(budget)

    from department)

    select budget

    from department, max budget

    where department.budget = max budget.value;

    虽然with子句只能在紧接着的查询中使用,但比子查询方便的是,它可以被多次使用。

    g)标量查询

    标量查询是指返回结果只是一个值的子查询,比如查询每个部门的员工人数:

    select dept_name,

    (select count(*)

    from instructor

    where department.dept_name = instructor.dept name)

    as num instructors

    from department;

    由于使用了count,这儿的子查询结果只有一个值,虽然这仍然是一张表,但数据库会自动从表中取出值使用。标量查询可应用于select, where, having等处。而且编译时无法确保子查询结果确实是一个值,如果不是,在运行时会报错。

    四、数据的修改

    a)Insert

    插入数据时可以直接使用select的结果,但下面的写法会造成死循环,插入无限多条:

    insert into student

    select *

    from student;

    而且数据库产品一般会提供批量插入的方式,用于快速地从格式化文本读取并插入大批量的数据。

    b)Update

    更新数据时可以使用case when来区分不同的情况:

    update instructor

    set salary = case

    when salary <= 100000 then salary * 1.05

    else salary * 1.03

    end

    此外,set子句也可以使用子查询

    学习资料:Database System Concepts, by Abraham Silberschatz, Henry F.Korth, S.Sudarshan

    相关文章

      网友评论

        本文标题:​《数据库系统概念》4-DDL、集合运算、嵌套子查询

        本文链接:https://www.haomeiwen.com/subject/ylqkvxtx.html