SQL笔记

一、数据定义
1.数据模式的创建和撤销

Create schema <模式名> authorization <用户名>
Eg:create schema st_co authorization lismith;

Drop schema <模式名> [cascade|restrict]
Eg:drop schema st_co cascade;

一般用法：create database … drop database …

2.基本数据类型
a.数值型
Integer 长整型 (int)
Smallint 短整型
Real 浮点数
Double precision 双精度浮点数
Float(n) 浮点数，精度至少为n位数字
Numeric(p,d) 定点数，有p位数字组成（不包括符号，小数点），小数点后有d位数字
也可以写作：decimal(p,d)或dec(d,d)

b.字符串型
char(n) 长度为n的定长字符串
varchar(n) 具有最大长度为n的变长字符串
c.位串型
bit(n) 长度为n的二进制位串
Bit varying(n) 最大长度为n的变长二进制位串
d.时间型
Date 日期，包含年、月、日，形式为YYYY-MM-DD
Time 时间，包含一日的时、分、秒，形式为HH:MM:SS
Interval 时间间隔

e.定义新数据类型 create domain …
Create domain person_name char(8);

3.基本表的创建、修改、撤销

a.创建表
Create table <基本表名>
（<列名 类型>，… <完整性约束>，…)
完整性约束：主键子句（primary key）、外键子句（foreign key）、检查子句（check）

eg. Create table T
(T# char(4) not null, Tname char(8) not null, title char(10),
primary key(T#));
Create table C
( C# char(4), Cname char(10) not null, T# char(4),
primary key(C#), foreign key (T#) references T(T#));

b.基本表结构修改
增加列 alter… add…
Alter table <基本表名> add <列名> <类型>
eg.Alter table S sdd sddress varchar(30);
删除原有列 alter … drop …
Alter table <基本表名> drop <列名> [cascade|restrict]
eg.alter table S drop age cascade;
修改原有列类型、宽度 alter…modify…
Alter table <基本表名> modify <列名> <类型>
eg.alter table S modify s# char(6);

c.基本表的撤销
撤销语句 drop table…
Drop table <基本表名> [cascade|restrict]
eg.Drop table S restrict;

4.索引的创建和撤销 (索引属于物理存储路径概念)
a.索引的创建 create index…
Create [unique] index <索引名> on <基本表名> (<列名序列>)
eg:如在创建S表时未使用主键字句，可以建索引的方法起到主键作用
Create unique index S#_index on S(S#);
eg.一个索引建可以对应多个列，索引排列可以用ASC升序，DESC降序
Create unique index sc_index on sc(s# asc,c# desc)
b.索引撤销 drop index …
Drop index <索引名>
eg.drop index S#_index,sc_index;

二、数据查询
5.select 查询的结构
Select <目标表列名>
from<基本表名或视图序列>
[ where <行条件表达式>]
[ group by <列名序列>
[ having <组条件表达式>]]
[ order by <列名[ASC|DESC]>,…]

a.where子句表达式可使用以下运算符：
算术比较运算：< <= > >= = <> !=
逻辑运算符： and or not
集合成员资格运算符：in not in
谓词：exists (存在量词) all some unique
聚合函数：avg min max sum count
嵌套另一个select语句
b.执行过程：（1）读取from子句中基本表、视图的数据，执行执行笛卡尔积操作；
（2）选取满足where子句中给出的条件表达式元组；
（3）按group子句中指定列的值分组，同时提取满足having子句中组条件表达式的那些组；
（4）按select子句中给出的列名或列表达式求值输出；
（5）order子句对输出的目标进行排序，按附加说明asc升序，desc降序；
where子句称为条件子句，group子句称为分组子句，having子句称为组条件子句，order子句称为排序子句；
c.select语句使用技术 ：
连接查询、嵌套查询、带存在量词的嵌套查询
eg:教学数据库
教师关系 T(T#, tname, title)
课程关系 C(C#, cname, T#)
学生关系 S(S#, sname, age, sex)
选课关系 SC(S#, C#, score)

检索学习课程号为C2的学生学号与姓名，这个查询要从基本表S和SC中检索数据
第一种写法（连接查询）：
Select S.S#, sname from S,SC where S.S#=SC.S# AND C#=‘C2’;
第二种写法（嵌套查询）：
Select S#,sname from where S# IN
(select S# from SC where C#=‘C2’);
嵌套查询的执行效率比连接查询的笛卡尔积效率高
第三种写法（使用存在量词的嵌套查询）：
select S#,sname from where exists
(select * from SC where SC.S#=S.S# AND C#=‘C2’)
6.查询中的限制和规定
a.select子句的规定
select子句用于描述查询输出的表格结构 select [all|distinct] <列名或表达式序列> | *
distinct去重，all默认选项；允许表达式包含+、-、*、/
b.集合的并、交、差操作 union 、intersect、except
(select 查询语句1) union all (select 查询语句2)
(select 查询语句1) intersect all (select 查询语句2)
(select 查询语句1) except all (select 查询语句2)
c.条件表达式中的比较操作
(1)算术比较操作
算术比较运算符: < , <=, >, >=,=, != between…and…/ not between… and…
(2)字符串匹配操作
字符串匹配操作符: like / not like ,可使用两种通配符: “%” “ _”
为使字符串包含 “%” “ _” ，可以定义转义字符，在like比较中使用escape关键字来定义转义字符，如果使用反斜线（\）作为转义字符，如:like ‘ab%cd%’ escape ‘\’ 匹配所有以“ab%cd”开头字符串；
(3)空值比较
测试是否值为空，is null / is not null;
涉及到含有算术运算+ - * / ，表达式里有一个空值时，表达式的值也为空；
(4)集合成员比较
判断元组是否在集合中：<元组> [not] in (<集合>)
集合成员算术比较 : <元组> 比较运算符 all/some/any (<集合>)
(5)集合空否测试
使用谓词exists测试集合是否为空/非空，
[not] exists (<集合>)
(6)集合中重复元组是否存在测试
[not] unique (<集合>)
7.嵌套查询的改进和写法
嵌套使查询显得非常复杂，难于理解，为降低复杂度，使用两个方法改进：导出表和临时视图；
导出表的使用
Select SC.S#
from SC,(select avg(score) from SC group by S#) as result (avg_score)
Group by SC.S#
having avg (SC.score) >= all (result.avg_score);
可以把子查询定义为导出表(命名为result)，移到外层查询的from子句；
b.with子句和临时视图
SQL允许用户用with子句定义一个临时视图(result,即子查询)，置于select语句的开始处；
With result (avg_score) as
select avg(score)
from sc group by S#
Select S# from SC,result
Group by S# having avg(score) >=all (result.avg_score);

8.基本表的连接操作

a.连接类型
[inner] jion 内连接
Left [outer] jion 左外连接
Right [outer] jion 右外连接
Full [outer] jion 完全外连接
b.连接条件
Natural 写在连接类型左边
On 等值连接条件 写在连接类型右边
Using (A1,A2,…An) 写在连接类型右边

9.递归查询
用with recursive子句可定义递归查询，先创建一个视图，再附加关键字recursive，指明这个临时视图是递归的。
eg. 假设课程见间有先修与后继的联系，关系模式 course (c#,cname,pc#),其属性表示表示课程号、课程名、直接先修课的课程号。
select语句表示这个递归查询：
With recursive pre (c#,pc#) as
((select c# ,pc# from course)
union
(select course.c#,per.pc# from course ,pre where course.pc# =pre.c#))
Select * from pre;

三、数据更新
1.数据插入
a.单组/多组数据插入
Insert into <基本表名> [(列名序列)]
values(<元组值>…<元组值>)
b.查询结果插入
Insert into <基本表名> [(列名序列)]
<select 查询语句>
c.表插入
Insert into <基本表1> [(列名序列)]
table <基本表2>
eg.基本表SC中，把平均成绩大于80分的男同学和平均成绩存入另一已存在的基本表
S_SCORE(S#,AVG_SCORE)中；
Insert into S_SCORE (S#,AVG_SCORE)
select S#,avg(SCORE)
from sc
where S# in
(select S# from S where SEX=‘M’)
group by S#
having avg(SCORE)>80;
2.数据删除 从基本表中删除元组
Delete from <基本表名>
[where <条件表达式>]
eg.把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除
Delete from SC
where C#=‘C4’ and
score <(select avg(score) from SC where C#=‘C4’);
3.数据修改 修改基本表元组的某些列的值
Update <基本表>
Set <列名>=<值表达式> / row=(<元组>)
[where <条件表达式>]
eg.
基本表SC中，当课程C4的成绩低于该课程的平均成绩时，成绩提高5%
Update SC
Set score =score * 1.05
Where C# =‘C4’
and score <(select avg(score) from SC where C# =‘C4’);
两次引用的SC是不相关的，内层select语句引用后，随后对成绩修改都以初始平均成绩做依据。

基本表C中，把课程号为C5的元组修改为（C5,DB,T3）
Update C
Set row =(‘C5’,’DB’,’T3’)
Where C# =‘C5’;
四、视图 View
1.视图的创建
Create view <视图名> (<列表序列>)
as <select 查询语句>
eg.数据库基本表S、SC、C，用户经常要用到S#、Sname、Cname、Score等列数据，可以建立视图;
Create view student_score(S#,Sname,Cname,Score)
as select S.S#,Sname,Cname,Score
from S,SC,C
where S.S# = SC.S# and SC,C#=C.C# ;
视图中的列名、顺序与select子句的列名、顺序一致，因此视图的列名可以省略。
2.视图的撤销
Drop view <视图名>
eg. Drop view student_score;
3.对视图的操作
如果视图是从单基本表中使用选择、投影操作导出的，并包含了基本表的主键，这样的视图称为：行列子集视图，行列子集视图可以被执行更新操作；允许用户更新的的视图在定义时必须加上“with check option” 短语。
eg.create view s_male as select S#,Sname,age from S where sex=‘M’;
Insert into s_male values (‘s28’,’wu’,’18’)
五、嵌入式SQL
多数系统采用预处理方式，先用预处理程序对源程序进行扫描，识别出SQL语句，并处理成主语言的函数调用形式，然后再用主语言的编译程序编译成目标程序；
数据库和主语言程序间信息的传递是通过共享变量实现的；
SQL_STATE是特殊的共享变量，有5位字符组成的字符组，表示执行语句发生的各种错误情况，
当SQL语句执行成功 ，SQL_STATE=00000；当未找到元组，SQL_STATE=02000；
1.嵌入式SQL语句格式：
Exec SQL <SQL语句> end_exec C语言程序规定结束标志不用end_exce,而是分号 ；。
允许嵌入式的SQL语句引用主语言的程序变量（共享变量），规定：引用时变量前必须加冒号：，
这些变量由主语言定义，并用SQL的declare语句说明；
eg.
Exec SQL begin declare section;
char sno[5],name[9];
char SQL_STATE [6];
Exec SQL end declare section;
STATE的长度是6，而不是5，是由于C语言规定变量值在做字符串使用是有结束符”\0” 引起；
2.游标 Cursor
SQL语句处理的是记录集合，而主语言语句一次只能处理一个记录，因此需要用游标机制，把集合操作转换成单记录处理方式；
a.游标定义语句（declare）
Exec SQL declare <游标> cursor for
<select 语句>
End_exec

b.游标打开语句 (open),该语句执行游标定义中的select语句，同时游标处于活动状态；
Exec SQL open <游标名> end_exec
游标处于活动状态时，可以修改和删除游标指向的元组；
c.游标推送语句(fetch),此时游标推进一行，并把游标指向的行（当前行）中的值取出，送到共享变量；
Exec SQL fetch from <游标> into <变量表> end_exec
d.游标关闭 （close）
Exec SQL close <游标名> end_exec
3.嵌入式SQL的使用技术
a.不涉及游标的SQL DML语句
由于insert、delete、update语句不返回数据结果，只对数据库进行操作，因此只加上前缀标识
exec SQL 和end_exec,就能嵌入在主语言程序中使用。对于select语句，如果已知查询结果是单元组，可以在语句中增加一个into子句，把找到的值送到相应的共享变量中。
eg.
a.在基本表S中，根据共享变量givensno的值检索学生的姓名、年龄和性别
Exec SQL select sname, age, sex
Into :sn, :sa , :ss
From s
Where s# =: givensno;
b.基本表S中插入一个新学生，属性值已在相应的共享变量中
Exec SQL insert into S(s#, snane, age)
values(:givensno, :sn, :sa);
c.从基本表SC中删除一个学生的各个成绩，学生姓名在共享变量sn中给出
Exec SQL delete from SC
Where s# = (select s# from s where sname =:sn)
d.课程名为maths的成绩增加某个值，改值在共享变量raise中给出
Exec SQL update sc
set score =score +:raise
Where c# in (select c# from c where cname =‘maths’)

b.涉及游标的SQL DML语句
当select语句查询结果是多个元组时，此时主语言程序无法使用，要用游标机制把多个元组一次一次传送给主程序；具体过程如下：
1.先用游标定义语句定义一个游标与某个select语句对应。
2.游标用open语句打开后，处于活跃状态，此时游标指向查询结果的第一个元组之前。
3.每次执行一次fetch语句，游标指向下一个元组，并把其值送到共享变量，供程序处理；如此重复，直至所有查询结果处理完毕。
4.最后用close语句关闭游标。关闭的游标可以重新被打开，与新的查询结果相联系，但在没有打开之前，不能使用。
eg.在基本表SC中检索某学生（学号由共享变量givensno给出）的学习成绩信息（s#,c#,score）,下面是该查询的C函数,(这里使用C语言中宏定义NO_MORE_TUPLRES,表示找不到元组时，值为1);

#define NO_MORE_TUPLES  !(strcmp(SQLSTATE,”02000”))
Void sel( )
{ exec SQL begin declare section;
    char sno[5] , cno[5] , givensno[5];
    int g;
    char SQLSTARE[6];
Exec SQL end declare section;
Exec SQL declare scx cursor for
    select s#, c#, score
    from sc
    where s# =:givensno;
Exec SQL  open  scx;
while(1)
    {exec SQL fetch from scx
        into :sno, :cno, :g ;
    if (NO_MORE_TUPLES) break;
printf(“%s, %s, %d”,sno,cno,g)
}
Exec SQL close scx;

}

对游标指向元组的修改或删除操作
eg.如果对找到的成绩做如下处理：删除不及格的成绩，60-69分改为70分，再显示该学生信息,那么例中 while(1) {…} 语句改写为下面形式；

while(1)
      {exec SQL fetch from scx
    into :sno, :cno, :g
    if (NO_MORE_TUPLES) break;
    if (g<60)
    exec SQL delete from sc
        where current of scx;
    else
    {if (g<70)
        {exec SQL update sc
         set score =70 where current of scx;
        g=70;
        }
    printf(“%s, %s, %d”,sno,cno,g)
    }
        }

六、SQL数据控制
1.SQL的完整性约束
a.域约束
SQL可以用 create domain 语句定义新的域，还可以出现check子句
Create domain color char(6) default ‘???’
constraint valld_colors
check (value in (‘red’,’yellow’,’blue’,’???’))
Create table part (…, color color,…)
b.基本表约束
候选键约束：unique(<列名序列>) 或 primary key (<列名序列>)
外键定义：foreign key (<列名序列>)
references <参照表> [(<列名序列>)] [on delete <参照动作>] [on update <参照动作>]
参照动作有5种方式：
no action (默认) ：修改(删除)参照表时对依赖表没有影响；
cascade方式：将依赖表中与参照表表中要修改(删除)的主键值相应的所有外键一起修改(删除)；
restrict方式：只有当依赖表中没有外键值与参照表中要修改(删除)的主键值相对应时，系统才能修改(删除)参照表中的主键值，否则拒绝修改(删除)此操作；
set null方式：修改(删除)参照表中主键值时，将依赖表中所有与这个主键值相对应的外键值均设为空值；
set default方式：与set null方式类似，只是把外键值均置为预先设置默认值。
检查约束的定义 check(<条件表达式>)
插入元组或修改元组时，若元组不满足条件，系统拒绝插入或修改操作；
2.触发器
触发器称为事件-条件-动作规则(event-condition-action rule,ECA规则)
一个触发器由3部分组成：
事件，事件是针对数据库发生插入、删除、修改等操作时，将开始工作；
条件，触发器将测试条件是否成立，如果条件成立，就执行相应的动作，否则什么也不做。
动作，如果触发器测试满足预定的条件，那么就有DBMS执行这些操作；这些动作能使触发事件不发生，即撤销事件；

eg.元组级触发器
选课关系SC的一个触发器，在修改关系SC的成绩时，要求修改后的成绩一定不能比原来的低，否则就拒绝修改；

create trigger trig1
after update of grade on sc
referencing
  old as oldtudle
  new as newtuple
for each row
when (oldtuple.grade>newtuple.grade)
  update sc
  set grade=oldtuple.grade
  where c#=newtuple.c#

触发器结构组成
触发器的命名
动作时间 触发事件 目标表名
旧值和新值的别名表
触发动作：动作间隔尺寸 动作时间条件 动作体
动作时间：触发器的动作时间定义了何时想要执行触发器动作，在SQL标准规定可以是before、after；before表示在触发事件进行以前，测试when条件是否满足，若满足则先执行动作部分操作，再执行触发事件操作（可忽略when条件是否满足）；after表示在触发事件完成以后，测试when条件是否满足，若满足则执行动作部分操作。
在oracle系统中，还规定另一种动作事件instead of ,表示在触发事件发生时，只要满足when条件，执行动作的操作，而触发事件的操作不再执行。
触发事件：触发事件定义了激活触发器的SQL数据更新语句的类别，触发事件有3类，update、delete和insert 。只有在update时，允许后面跟“of <属性表>”短语；其他两种情况是对整个元组操作，不允许跟“of <属性表>”短语；
目标表（on子句）：当目标表的数据被更新（插入、删除、修改），将激活触发器；
旧值和新值的别名表（references子句）：
如果触发事件是update ，应该用old as 、new as 子句定义修改前后的元组变量。如果是delete,那么只要用old as子句定义元组变量；如果是insert ,那么只要用new as子句定义元组变量。
触发动作：
动作间隔尺寸，for each子句定义，
两种形式：for each row 元组级触发器，每个修改的元组都要检查一次
for each statement 语句级触发器，对SQL语句的执行结果进行检查。
动作时间条件，用when子句定义，可以是任意表达式；当触发器被激活时，如果条件是ture，则执行，否则不执行；
动作体，定义触发动作本身，即当触发器被激活时想要DBMS执行的SQL语句，动作体若是一个SQL语句，直接写上即可。若是一系列SQL语句，则用分号定界，再使用begin atomic...end限定。
语句级触发器
在关系SC中修改课程号C#，也就是学生的选课登记需做变化，，在关系SC中有个约束，要求保持每门课程选修人数不超过50.如果修改课程号后，违反这个约束，那么不做这个更改。

create trigger trig2
instead of update of c# on sc
referencing
  old_table as oldstuff
  new_table as newstuff
when (50>=all(select count (s#)
                        from ((sc except oldstuff) union newstuff)
                        group by c#))
begin atomic
  delete from sc
  where (s#,c#,grade) in oldstuff;
  insert into sc
    select * from newstuff
end

在语句级触发器中，不能直接引用修改前后的元组，但可以引用修改前后的元组集。设旧元组集由被修改(删除)的元组，新的元组集由修改元组新值或插入元组组成。那么可以用old_table as oldstuff 和 new_table as newstuff 说明两个关系变量oldsteff 和newstuff。
撤销触发器语句 drop trigger trig1;
3.权限
DBMS的权限子系统允许有特定存取权的用户有选择地和动态地把这些权限授予其他用户。
用户选择使用6类权限：select、insert 、delete 、update 、references 、usage.
references允许用户定义新关系，引用其他关系的主键作为外键；
usage权限允许用户使用已定义的域。
授予其他用户使用关系和视图权限的语句格式如下：
grant <权限表> on <数据库元素> to <用户名表> [with grant option]
如果权限表包括全部6种权限，可以用关键字all privileges代替；数据库元素可以是关系、视图、域。短语with grant option表示获得权限的用户还能获得传递权限，把获得的权限转授给其他用户。
grant select,update on sc to wang with grant option
grant update(grade) on sc to wen
回收语句
revoke <权限表> on <数据库元素> from <用户名表> [restrict|cascade]
cascade,表示回收权限时要连锁回收
restrict,不存在回收权限时才能回收权限，否则系统拒绝回收。
回收语句revoke可用revoke grant option for代替，回收转授出去的转让权限，而不回收转授出去的权限。
revoke select,update on s from wang cascade