笔记 April 28, 2019

SQL基础

Words count 7.3k Reading time 7 mins. Read count 0

introduction

SQL

  • select distinct (去除重复的结果)

    Natural Join

  • Danger in natural join: beware of unrelated attributes with same name which get equated incorrectly
    select `name`, `title` from (`instuctor` natural join `teachers`) join `course` using(`course_id`);
    
  • join ··· using 子句是一种自然连接的形式,只需要在指定属性上的取值匹配。

基本运算

字符串运算

  • 用escape关键字来定义转义字符

    like '100 \%'  escape  '\'
    
  • 匹配至少含三个字符的字符串

    like '_ _ _ %'
    
  • 匹配只含三个字符的字符串

    like '_ _ _'
    

结果顺序

  • order by子句默认使用升序,并且可以在多个属性上进行

where子句谓词

select name  from instructor  where salary between 90000 and 100000
  • Tuple comparison
    select `name`, `course_id` from `instructor`, `teaches` where (`instructor.ID`, `dept_name`) = (`teaches.ID`, 'Biology');
    

集合运算

SQL作用在关系上的unionintersectexcept对应于集合论中的析取、合取、差运算。

并运算

  • 自动去除重复项

    (select course_id from section where sem = ‘Fall’ and year = 2009) 
    union
    (select course_id from section where sem = ‘Spring’ and year = 2010)
    
  • 保留重复项

    (select course_id from section where sem = ‘Fall’ and year = 2009) 
    union all
    (select course_id from section where sem = ‘Spring’ and year = 2010)
    

交运算

  • 自动去除重复项
(select course_id from section where sem = ‘Fall’ and year = 2009) 
intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)
  • 保留重复项
    (select course_id from section where sem = ‘Fall’ and year = 2009) 
    intersect all
    (select course_id from section where sem = ‘Spring’ and year = 2010)
    

    差运算

空值

判断是不是空值用谓词is null/not null,一般不能用=

聚集函数

  • avg()
  • min()
  • max()
  • sum()
  • count()

  • sum()\avg()必须输入数字集,其他运算符无此要求

分组聚集

  • group by
    group by子句中所有属性上取值相同的元组将被分到一个组中

  • 保证出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性。
    例如:
    ID出现在select语句中,且没有被聚集,而且没有出现在group by语句中

    select `dept_name`, `ID`, avg(`salary`) from `instructor` group by `dept_name`
    

having子句

  • select子句的情况类似,任何出现在having 子句中却没有被聚集的属性必须出现在group by子句中。

嵌套子查询

  • 短语“至少比一个大”在SQL中用>some表示
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ’Biology’;

等价于

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

空关系测试

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

重复元组存在性测试

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

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;

标量子查询

更新

case 
    when pred_1 then result_1
    whrn pred_2 then result_2
    ...
end

update instructor set salary = 
case                                              
        when salary <= 100000 then salary * 1.05   
        else salary * 1.03             
end

SQL 2

连接条件

引入on的两个优点:

  • 在外连接中,on与where不同
  • 在on中限制连接条件,在where中定义其他条件可读性更佳
select * from student join takes on student.ID = takes.ID
select * from student, takes where student.ID = takes.ID

以上两者的区别:后者ID字段会出现两次。

外连接

  • 通过在结果中创建包含空值元组的形式,保留了那些在连接中丢失的元组
  • 有三种形式的外连接(outer join)
    • 左外连接:只保留左边关系中的元组
    • 右外连接:只保留右边关系中的元组
    • 全外连接:保留两个关系中的元组
      相较而言,我们之前学习的不保留未匹配元组的连接运算被称为内连接(inner join)

eg.一门课程也没有选的同学

select ID from student natural left outer join takes where course_id is null
  • 右连接和左连接是对称的,交换顺序结果一致(只有顺序变化)

  • on在外连接中的作用:
    所有的连接条件都必需要放在on后面,不然前面的所有leftright关联将作为摆设,而不起任何作用。

视图

创建视图

create view v as < query expression >
0%