• 数据库补充知识


    • 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';
    
    • 1
    • 2
    • 3

    如果有的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');
    
    • 1
    • 2
    • 3
    • 其他的运算符
      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');
    
    • 1

    就是说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.这个水手预定了的船
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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预定的船
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    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;    //上面几行都是在创建临时表,这一行是在临时表中查询
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    再如飞机零件图(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))
    
    • 1
    • 2
    • 3
    • 4

    一定要注意的是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';
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 视图

      • 普通视图【虚表】
        实现外模式
        利用视图和逻辑模式的映射实现数据的逻辑独立性
        数据库只存储视图的定义,不存储数据,数据在调用时临时计算,数据内容非永久保存
        实现了数据库的安全性
        当视图可以唯一映射回原表(如视图中包含原表的主键)时,才可以对视图内数据的修改
      • 临时视图【公共表表达式】(如上面说的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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    执行命令的方式
    连接数据库

    EXEC SQL CONNECT :uid IDENTIFIED by:pwd: ;
    
    • 1

    执行DML语句

    EXEC SQL INSERT INTO SC(SNO,CNO,GRADE)VALUES(:SNO,:CNO,:GRADE);//values中不在直接写值,而是引用宿主变量,引用宿主变量之前需要加冒号
    
    • 1

    查询【简单查询,返回一个值】

    EXEC SQL SELECT GRADE INTO :GRADE,:GRADE1
    FROM SC
    WHERE SNO=:GIVENSNO AND CNO=:GIVENCNO;//SNO和CNO都没加:,说明是sql中的变量,GIVENSNO和GIVENCNO都有冒号,说明是c中的
    
    • 1
    • 2
    • 3

    为了处理查询返回的集合,引入游标机制
    因为关系型数据库查询结果是一个集合,而c中的变量仅仅是一个变量,所以需要使用游标
    定义游标

    Define a cursor
    	EXEC SQL DECLAR<cursor name> CURSOR FOR
    	select...
    	from ...
    	where...  ///就是游标的名字
    
    • 1
    • 2
    • 3
    • 4
    • 5

    打开游标

    EXEC SQL OPEN<cursor name>
    
    • 1

    一旦open一个游标,就可以把游标理解成一个文件,这时候sql返回的就可以是一个集合了

    取游标内每一条元组

    EXEC SQL FETCH <cursor name>
    	INTO  :hostvar1,:hostvar2;
    
    • 1
    • 2

    使用fetch操作之后,就可以把游标(看成一个文件,文件里面有sql返回的数据集合)中的数据按顺序返回给c中的变量,因为文件中的数据是一个集合,很多,所以需要用到循环,在循环中进行fetch

    判断查询结果是否取完

    SQLCA.SQLCODE ==100 时取完
    
    • 1

    关闭CURSOR

    CLOSE cursor
    
    • 1
    • 动态SQL
      上一个例子运用CURSOR的SQL语句是确定的,为了实现动态的SQL,

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

    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语句
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    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
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 存储过程机制
      • 允许用户把一组常用的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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
  • 相关阅读:
    Java ByteArrayOutputStream.toString()方法具有什么功能呢?
    C++内存空间
    AI副业赚钱变现项目 做高清历史老照片单月变现5W+(攻略+提示词)
    Spring - IoC 容器之 Bean 的生命周期
    【一文秒懂——YAML配置文件】
    python3GUI--详细讲解一个QQ音乐组件的制作By:PyQt5(详细介绍、附源代码)
    Java基础27,28(多线程,ThreadMethod ,线程安全问题,线程状态,线程池)
    使用sql profile 稳定执行计划的案例
    阿里十五位顶级架构师联合编写的《微服务架构解析手册》大厂面试必问
    libyuv 再次封装打包与测试
  • 原文地址:https://blog.csdn.net/qq_57780419/article/details/126922786