ch3-SQL

基本关系模式定义

create table命令定义SQL关系:

  create table r( A1 D1 , 
                    ..., 
                    An Dn,
                <完整性约束1>,
                    ...,
                <完整性约束k>)；(分号结束)

r 是关系名，Ai是关系r模式中的一个属性名，Di是属性 Ai的域（即取值类型、范围）

SQL禁止破坏完整性约束的数据库更新，例如：

· 新插入的元组主码属性为空值、或取值与关系中的另一元组主码属性相同

· 新插入的student元组所在的dept_name未出现在department关系中，破坏外码约束

删除和修改表

Ø drop table student
//删除表和其中内容
Ø delete from student
//删除表里的内容，保留表（关系模式）
Ø alter table 增加、删除属性
    alter table r add A D
    • 其中A是要被添加到关系r的属性的名称，并且D是A的域
    • 关系中所有元组使用null作为新的属性值
    alter table r drop A 
    • 其中，A是关系r的属性的名称
    • 许多数据库都不支持删除属性，但是支持drop整个表

select子句

select * from instructor;
//选择所有内容
select distinct dept_name from instructor；
//消除重复内容，找出所有教师所在的系名，并且结果中没有重复的名字
select all dept_name from instructor；
//不消除重复，选择所有教师的系名

自然连接

自然连接结果＝共同属性＋第一个关系属性＋第二个关系属性

例如：select * from instructor natural join teaches;

更名运算

例：找出满足下面条件的所有教师的姓名，他们至少比某一个Comp.Sci.系教师的工资要高

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name =‘Comp. Sci.’

关键字as是可选的，可以省去 instructor as T ≡ instructor T

字符串运算

like运算符可实现模式匹配，模式匹配用两个特殊的字符来表示：

百分号(%)匹配任意子字符串

下划线(_)匹配任意一个字符

SQL字符串用单引号；关系代数字符串用双引号

select name
from instructor
where name like ‘%dar%’；

当匹配模式中含有特殊字符（如”％”、”_”、”\”）时，须使用转义字符（通过escape定义）

like ‘100\%%’ escape ‘\’;
//匹配“100%”开头的字符串

排列元组的显示次序

使用order by，可以用 desc 表示降序，使用 asc 表示升序；默认使用升序，排序可以在多个属性上进行

集合运算

或：union

(select course_id  from section where sem = ‘Fall’ and year = 2009)
union
(select course_id  from section where sem = ‘Spring’ and year = 2010)
//找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程id号

与：intersect

(select course_id  from section where sem = ‘Fall’ and year = 2009)
intersect
(select course_id  from section where sem = ‘Spring’ and year = 2010)
//找出在2009年秋季和2010年春季同时开课的所有课程id号

非：except

(select course_id  from section where sem = ‘Fall’ and year = 2009)
except
(select course_id  from section where sem = ‘Spring’ and year = 2010)
//找出在2009年秋季学期开课但不在2010年春季学期开课的所有课程id号

以上三种运算将自动消除冗余，如果要保留需要使用union all, intersect all 和 except all

空值

涉及空值的任何比较运算的结果返回unknown

在count时空值按0计算

分组聚集

having子句中的谓词在形成分组之后才起作用，因此可以使用聚集函数

select dept_name, avg (salary)
from instructor
group by dept_name
having avg (salary) > 42000;
//找出所有教师平均工资超过 42000 美元的系的名字和平均工资

嵌套子查询

select，from，where后面可以跟子查询

in，not in后面跟select子句

数据库的修改

删除：delete

delete from instructor
where dept_name in (select dept_name
        from department
        where  building =‘Watson’);
//删除所有在位于Watson大楼的系工作的教师
delete from instructor
where salary < (select avg (salary) 
           from instructor);
//删除工资低于大学平均工资的教师记录

当删除时涉及计算，会因为元组被删除导致计算结果出现问题

SQL解决方案：

1.首先,计算平均工资，找出并标记需删除的元组

2.然后,删除需删除的元组 (无需重新计算平均值或重新测试元组)

插入：insert into values

更新：update

update instructor
    set salary = case
        when salary <= 100000 then salary * 1.05
        else salary * 1.03
    end
//给工资超过$100,000的教师涨3%的工资,其余教师涨5%