-
expect是做差集,类似于union是并集。
-
交集的使用:
查询同时预定红船和绿船的人的编号和姓名
这时候得用集合的交集intersect
select S1.sid,S1.sname from sailors S1 join reserves R1 on S1.sid=R1.sid join boats B1 on R1.bid=B1.bid where B1.color='red'
intersect
select S1.sid,S1.sname from sailors S1 join reserves R1 on S1.sid=R1.sid join boats B1 on R1.bid=B1.bid where B1.color='green';
如果有的DBMS不能用intersect,那么可以嵌套查询
select s.sid, s1.sname from sailors s, boats b, reserves r
where s.sid=r.sid and r.bid=b.bid and b.color='red' and s.sid in
(select s2.sid, s2.sname from sailors s2,boats b2,reserves r2 where s2.sid=r2.sid and r2.bid=b2.bid and b2.color='green');
- 其他的运算符
in 在…范围,not in 不在…范围
exists 存在,not exists 不存在
unique 某个值在表里面是否唯一,not unique 不唯一
op any,op all,op in。op是<,>,=,<=,>=,!=。意思就是(如< any就是小于任意一个值就行,也就是说选出不是最小值的那个值。< all就是小于所有的值,也就是选出最小值)。但是这样操作有的DBMS支持
如选出级别大于Horatio的水手的信息
select * from sailors s where s.rating >any (select s2.rating from sailors s2 where s2.sname='horatio');
就是说s中的级别大于任意一个子表中的级别即可,因为子表中的姓名为horatio的水手不止一个,所以只需要大于任意一样就行
- 预定了所有船的选手,选修了所有计算机课的学生。这个“所有”使用的就是除法
如查找预定了所有船的选手的姓名:
select s1.sname
from sailors s
where not exists//4.
(select b.bid from boats b //1.所有的船
except
select r.bid from reserves r where r.sid=s.sid);//2.这个水手预定了的船
3.1-2就是这个水手没有预定的船
4.前面加上not exists表明这个水手没有预定的船不存在,即预定了所有的船。
如果数据库不支持except
select s1.sname
from sailors
where not exists//3.
(select b.bid from boats b
where not exists//2.
(select r.bid from reserves r where r.bid=b.bid and r.sid=s.sid));//1.选出水手s预定的船
2.1前面加上not exists表示选出水手没有预定的船
3.2前面加上not exists表示不存在水手没有预定的船,即水手预定了所有的船
-
group by 语句在having语句的执行之前。并且having需要和group by同时出现,这就要求having语句中必须有分组的东西。即如果group by是以deptno进行分组的,那么having中必须出现deptno字段,没有deptno字段的全部放到where中去,否则会报错。
-
cast可以给null幅值或者赋属性
如 有两张表:student(name,school)
soldiers(name,service)
现在想把这两张表合并到一起成这样的形式:
| name | school | service |
则
create view prospects(name, school, service) as
select name,school,cast(NULL as varchar(20))
from students
union
select name,cast(NULL as varchar(20)), service from soldiers;
propects后面跟的是试图的三个字段
cast(NULL as varchar(20))指的是将null变成varchar类型
- when,then,case的一些应用
有一个表officers(name,status,rank,title)
在status字段中可以用1表示active duty状态
用2表示reserve状态
3表示special assignment状态
4表示retired状态
则查询的时候可以这样写
select name, (case status
when 1 then 'active duty'
when 2 then 'reserve'
when 3 then 'special assignment'
when 4 then 'retired'
else 'unknown' end) as status
from officers;
when,then,case类似于switch,case和if,所以sql中如果想用判断语句,一般选择when,then,case
- EXCEPT和EXCEPT ALL
都是做集合差,但EXCEPT ALL 不删重复元组,也就不用排序,效率比EXCEPT高 - 递归查询
有一张表FedEmp(name, salary, manager)
查找Hoover手下的所有薪资大于100000的人
可以知道,Hoover手下的手下也有可能有人,也就是形成了类似于树的结构,因此需要用到递归查询
WITH agents(name,salary) AS //创建一张临时表:agents
((SELECT name,salary
FROM FedEmp
WHERE manager='Hoover') //查找Hoover的直接手下是谁
UNION ALL
(SELECT f.name,f.salary
FROM agents AS a,FedEmp AS f
WHERE f.manager=a.name)) //递归查询,查找所有的间接手下
SELECT name from agents where agents.salary>10000; //上面几行都是在创建临时表,这一行是在临时表中查询
再如飞机零件图(componments):
一个wing机翼需要5个struct,1个aileron…
1个struct需要10个rivet…
查询一个机翼需要多少rivet
因为wing直接间接都在用rivet,所以需要递归查询
递归查询先查出临时表:
with wingpart(subpart, qty) as
((select subpart, qty from componments where part='wing')
union all
(select c.subpart, w.qty,*c.qty from wingpart w, components c where w.subpart=c.part))
一定要注意的是w.qtyc.qty,比如一个wing机翼需要5个struct,1个struct需要10个rivet,则需要510=50个rivet
所以sql语句应该写成
with wingpart(subpart, qty) as
((select subpart, qty from componments where part='wing')
union all
(select c.subpart, w.qty,*c.qty from wingpart w, components c where w.subpart=c.part))
select sum(qty) as qty from wingpart where subpart='rivet';
-
视图
- 普通视图【虚表】
实现外模式
利用视图和逻辑模式的映射实现数据的逻辑独立性
数据库只存储视图的定义,不存储数据,数据在调用时临时计算,数据内容非永久保存
实现了数据库的安全性
当视图可以唯一映射回原表(如视图中包含原表的主键)时,才可以对视图内数据的修改 - 临时视图【公共表表达式】(如上面说的with)
没有存储视图的定义
可以实现递归查询
- 普通视图【虚表】
-
嵌入式SQL
为了使程序和数据库进行交互,有三种方法:嵌入式sql,编程的API(如odbc,jdbc),封装的类
以C语言中的嵌入式SQL为例- 以ECEC SQL,开始,以;结尾会被预编译器识别为嵌入式SQL命令
- 用宿主变量在DBMS和应用程序之间交换数据和消息
- 在SQL命令里,可以用:的方法引用宿主变量的值
- 宿主变量在C语言中就当一个普通的变量使用
- 不可以把宿主变量定义为数组或结构
- 一个特殊的宿主变量,通过SQLCA在C和DBMS进行数组交换
- SQLCA.SQLCODE 可以判断查询结果
- 用说明符来表示宿主变量的NULL
定义数组变量
EXEC SQL BEGIN DECLARE SECTION;
char SNO[7];
char GIVENSNO[7];
char CNO[6];
char GIVENCNO[6];
float GRADE;
short GRADEi;//gradei是indicator,代表null,因为c语言中没有null,所以需要用indicator来表示null
EXEC SQL END DECLARE SECTION;
执行命令的方式
连接数据库
EXEC SQL CONNECT :uid IDENTIFIED by:pwd: ;
执行DML语句
EXEC SQL INSERT INTO SC(SNO,CNO,GRADE)VALUES(:SNO,:CNO,:GRADE);//values中不在直接写值,而是引用宿主变量,引用宿主变量之前需要加冒号
查询【简单查询,返回一个值】
EXEC SQL SELECT GRADE INTO :GRADE,:GRADE1
FROM SC
WHERE SNO=:GIVENSNO AND CNO=:GIVENCNO;//SNO和CNO都没加:,说明是sql中的变量,GIVENSNO和GIVENCNO都有冒号,说明是c中的
为了处理查询返回的集合,引入游标机制
因为关系型数据库查询结果是一个集合,而c中的变量仅仅是一个变量,所以需要使用游标
定义游标
Define a cursor
EXEC SQL DECLAR<cursor name> CURSOR FOR
select...
from ...
where... ///<cursor name>就是游标的名字
打开游标
EXEC SQL OPEN<cursor name>
一旦open一个游标,就可以把游标理解成一个文件,这时候sql返回的就可以是一个集合了
取游标内每一条元组
EXEC SQL FETCH <cursor name>
INTO :hostvar1,:hostvar2;
使用fetch操作之后,就可以把游标(看成一个文件,文件里面有sql返回的数据集合)中的数据按顺序返回给c中的变量,因为文件中的数据是一个集合,很多,所以需要用到循环,在循环中进行fetch
判断查询结果是否取完
SQLCA.SQLCODE ==100 时取完
关闭CURSOR
CLOSE cursor
-
动态SQL
上一个例子运用CURSOR的SQL语句是确定的,为了实现动态的SQL,-
- 可以直接运行的动态SQL【非查询】
-
- 动态SQL的查询【带动态参数】
-
- 动态构造查询语句
-
1.可以直接运行的动态SQL【非查询】
用字符数组动态拼接出一条sql语句
EXEC SQL BEGIN DECLARE SECTION;
char sqlstring[200]; //c中的宿主变量
EXEC SQL END DECLARE SECTION;
char cond[150];
strcpy(sqlstring, "delete from student where"); //1.除了条件,其他的语句先写出来
printf("Enter search condition:");
scanf("%s", cond); //2.获得用户输出的条件这个字符串
strcat(sqlstring, cond); //1+2变成可执行的sql语句
EXEC SQL EXECUTE IMMEDIATE: sqlstring; //执行sql语句
2.动态SQL的查询【带动态参数】
EXEC SQL BEGIN DECLARE SECTION;
char sqlstring[200];
int birth_year;
EXEC SQL END DECLARE SECTION;
strcpy(sqlstring, "delete from student where year(bdate)<=:y;"); //:y是动态参数
printf("Enter birth year for delete:");
scanf("%d", &birth_year);
EXEC SQL PREPARE PURGE FROM:sqlstring; //sql命令执行准备
EXEC SQL EXECUTE PURGE USING:birth_year; //将参数替换到:y
- 存储过程机制
- 允许用户把一组常用的sql定义为一个存储过程,系统对其优化编译后可以被直接调用。
- 用户使用更加方便,应用需求发生变化时,只需要改变存储过程
- 改进性能
- 可以扩展DBMS的功能
创建一个存储过程
EXEC SQL
create procedure drop_student
(IN student_no char(7), //输入参数,即要删除的参数
OOUT message char(30)) //输出参数,即显示是否删除成功的参数
BEGIN ATOMIC //表示这个存储过程的原子性:要么一起成功,要么一起失败
delete from student where SNO=student_no;
delete from SC where SNO=student_no;
SET message=student_no || 'droped'; //显示指定的学生应该被删除了
END;