[大数据]数据库补充知识

• 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，

  • 1. 可以直接运行的动态SQL【非查询】
  • 2. 动态SQL的查询【带动态参数】
  • 3. 动态构造查询语句

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;