数据库系统-SQL

一、历史

1974年，Boyce&Chamber提出
1975-1979年，IBM的San Jose再System R上首次实现，称为Sequel(Structured Engligsh Query Language) --> SQL
1986,ANSI/ISO 推迟标准：SQL-86
1989,SQL-98
1992,SQL92(SQL2)(标准的关系数据库语言)
1999,SQL99(SQL3)(面向对象数据库&对象关系数据库)
SQL2003，SQL2006，SQL2008（数据库应用程序）

1.2 功能概述

SQL= DDL + DML + DCL

• DDL（Data Definition Language）数据库定义语言：Create Alter Drop
• DML（Data Manipulation Language）数据操纵语言：Insert Delete Update
• DCL（Data Control Language）数据库控制语言 ：Grant、Revoke
• TCL（Transaction Control Language）事务控制语言

二 、表定义

2.1 数据类型

• char(n):固定长度的字符串
• varchar(n):可变长度的字符串
• int(integer):整数
• numeric(p,q):固定精度数字，小数点左边p位，右边p-q位
• float(n):有的也写作real，浮点精度数字，小数点后保留n位
• date：日期
• time：时间

2.2 约束

• Primary Key：主键约束，表中只能有一个
• Unique：唯一性约束，表中可以有多个
• Not null：不为空

三、修正表

3.1 修改表的定义

示例：

Student表上，增加列 Saddr char[40], PID char[18]
Alter Table Student add Saddr char(40),PID char(18);

Student表上，修改 Sname的类型为 char(10)
Alter Table Student modify Sname char(10);

Student表上，删除学生姓名必须取唯一值的约束
Alter  Table Student drop unique(Sname);

删除主键
Alter Table Student  drop primary key;

3.2 撤销：

delete 只会删除行数据，撤销会删除表定义

Drop Table Student;

--撤销数据库
Drop database 数据库名；

四、 SQLServer

4.1 系统数据库文件

每一个数据库 在硬盘中就是一组大文件
文件

• .mdf(主数据库文件): 存储数据库的启动信息和部分或全部数据，只能有一个
• .ndf(辅助数据文件)：主数据外的数据，可以多个
• .ldf（日志文件）: 每个DB至少有一个log文件

页面
最小存储单位，一页为8k or 8192字节

空间extent
连续8个页面，64k，是分配数据表存储空间的一种单位

4.2 数据库维护

备份数据库

• 完全备份：完全备份数据文件& 日志文件
• 差异(增量)备份：在完全备份的基础上，进行增量的备份，速度快
• 事务日志备份：所发生事务进行备份
• 数据库文件和文件组备份：数据库相当大的情况

恢复数据库

将备份加载到系统中的过程，静态的单用户模式(不让其他用户操作)

Oracle 数据库授权

1. 进入orcale控制台

输入命令：sqlplus /nolog  进入oracle的控制台

2. 以DBA角色进入

输入命令：conn /as sysdba;

其他角色还有：
sys:系统管理员
system:本地管理员
scott:普通用户

select ，update，insert，delete，exec，dir

grant 权限 on 表明 to 用户名

管理用户：
create user zhangsan;//在管理员用户下创建zhangshan
alter suer scott identified by tiger;//修改密码

4.3 创建约束

约束是SQL提供的总保持数据库完整性的一种方法，有一下五中

• primary key constraint(主键值)
• unique constarint(唯一值)
• check constraint（检查性）
• default constraint（默认）
• foreign key constraint(外键)

constarint 约束名 约束类型(列名)

Create Table Course (C# char(3),Cname char(12),Chourse integer,Credit float(1),
								T# char(3)) constraint pk primary key (C#)

五、复杂查询

其实也有any，但是any有歧义 （任意 还是 所有？）所以被some代替了

• intersect ：只返回交集 ，自动去重

• union 返回并集，自动去重
  

• except ：T1和T2差集结果将返回1，它是在T1结果集中，并T2结果集中没有出现的行，自动去重
  

所有操作 都可以 + all ，代表不去重

5.2 空值处理

• 空值：不知道、不确定、不存在的值
• 空值，会影响许多方面：聚集函数运算的正确性，不能参加算数、比价、逻辑运算
  
  现行DBMS的处理小结
• 除了is null 之外，空值不满足任何查询条件
• 如果null参与算数运算，该算数运算表达式的值为null
• 如果null参与比较运算，结果为false
• null参与聚集运算，count(*)会包含null，其他聚集函数都会忽略null

5.3 视图

create view stu_view as(
	select sname,sage from student;
);

视图的更新
view不一定能更新的，比如

create view s_g(s#,savg) as (
	select S#, avg(age) from student group by s#
)
如果更新 savg ：
update s_g set savg = 85 where s# = '202012'
那么就不能直接更新


insert into stu_view Values('zhangsan',20)
但是 student 表中#sid 是主键，那么就不能更新

• view中有聚集函数，不能update
• unique distinct ，不能
• group by 不能
• 有算数表达式算出来的列，不能
• view不包含主键，不能

drop view s_g; 撤销视图 

五、数据库完整性

DB integrity ：DBMS应该保证DB的一种特性，任何情况下的 正确性、有效性、一致性

广义完整性：语义~、并发控制、安全控制、DB故障恢复…
狭义完整性：专指语义完整性

关系模型中的完整性：

1. 实体完整性
2. 参照完整性
3. 用户自定义

完整性的分类

约束对象来分

• 域完整性
• 关系完整性

约束来源分类

• 结构约束
• 内容约束

约束状态分类

• 静态约束
• 动态约束：标准是动的，例如工资只升不降

5.1 SQL实现静态完整性

5.1.1 静态约束

列完整性
表完整性

列完整性

Create Table(
	S# char(8) not null unique,
	Sname char(10),
	Ssex char(2) constraint ctssex check(Ssex='男' or Ssex = ’女‘),
	Sage integer check(Sage >= 1 and Sage < 150),
	D# char(2) references Dept(D#) on delete cascade,
	Sclass char(6);
)
--references Dept(D#)  ： 外键 依赖 Dept的(D#)
--on delete cascade :  Dept被删除的时候，设置为null

Create Table Course (
	 C# char(3),Cname char(12),Course integer,
	 Credit float constraint ctcredit check (
		Credit >= 0.0 and Credit <= 5.0
	),
	T# char(3) references Teacher(T#) on delete cascade
);

表完整性

Create Table Student(
	S# char(8) not null unique,
	Sname char(10) ,
	Ssex char(2) constraint ctsssex check (Ssex = '男' or Ssex = ‘女’),
	Sage integer check (Sage > 1 and Sage < 150),
	D# char(2) references Dept(D#) on delete cascade,
	Sclass char(6)
--表约束部分
,Primary Key(C#)constraint ctcc check (Chours/Credit = 20),
,forergn key(S#) references student(S#) on delete cascade);
	
)

撤销追加约束

create table 中定义的表 列 约束 ，后续可以追加 撤销

--drop contraint
Alter Table SC
	Drop Contraint ctscore;

-- add contraint
Alter Table SC Modify(
	Score float(1) contraint nctscore check(Score >= 0.0 and Score <= 150.0)
)

--

5.1.2 断言Assertion

一个断言就是一个谓词表达式，表达了希望数据库总能满足的条件。
表 列约束是一些特殊的断言

5.2 SQL实现动态完整性

5.2.1 触发器

为了实现动态约束以及多个元祖之间的 完整性约束，就需要触发器Trigger

Trigger 是一种过程完整性约束，CreateTable中的定义都是 非过程的

Trigger的意义：事件发生时，对某一个元祖 or 全部元祖 ，进行检查条件search_condition,满足 则执行后面的程序段。条件或程序中引起的变量可用 corr_name_def 来定义。

事件：before| After {insert | delete | update}

corr_name_def的定义

OLD [ROW] [AS] old_row_corr_name //更新前旧元祖别名
NEW [ROW] [AS] new_row_corr_name //更新后的新元祖命名
OLD TABLE [AS] old_table_corr_name //更新前的table 别名
NEW TBALE [AS] new_table_corr_name//更新后的table别名

example

--Trigger:工资只能升不能降
create trigger teache_chgsal before 
	update of salary on teacher
	referencing new x,old y
	for each row when(x.salary < y.salary)
		begin
	raise_application_error(-20003,'invalid salary on update')
		end;

--每次添加，sum + 1
Create trigger sumc after insert on sc
referencing new row newi --只关注新添加的行 别名newi
for each row 
begin
update student set SumCourse = SumCourse + 1
where S# =:newi.S#;
end;

--更新Student的S#,那么SC表中的值也更新
create trigger upds after update of S# on Student
reference old oldi,new newi -- 更新前 更新后的值
for each row
begin 
update sc set S# = newi.S# where S# = oldi.S#;
end;

删除Student ，SC也跟随删除
create trigger delS# after delete on Student
reference old oldi
for each row
begin
delete sc where S# = oldi.S#
end;

--Jaden 一定是工资最高的
create trigger UpSalary before update Jaden on Dept
reference old oldi,new newi
for each row when(
	Jaden not in (select Tname from teache 
	--where D# = :newi.D#
	and salary >= all(select saray from Teacher where D# =:newi.D#)
	)
	begin
	raise_application_error(-20003,'invalid Jaden on update')
	end;
)

六、数据库安全性

DB安全性是指DBMS应该保证数据库的一种特性(机制或手段)：免受非法、非授权用户的使用、泄露、更改、破坏

DBMS的安全机制：

• 自助安全性机制：存取控制（Access Control）
• 强制安全机制：通过对数据&用户强制分类，使得不同类别用户能够访问不同类别的数据
• 推断控制：通过开放数据，推断出隐私数据
• 数据加密存储

DBA的职责：

• 熟悉相关法律、政策、协助组织制定好安全政策
• 规划好安全控制保障机制
• 划分好数据的安全级别&用户的安全级别
• 实施安全控制：DBA账户(系统账户)

分为 ：强制安全性 、自主安全性

6.1 自主安全性机制

通常情况下，安全机制通过 授权 来解决
授权者：决定用户权限的人
授权：授予用户访问权限

DBMS自动实现自主安全机制

• 用户定义一些安全性控制
• DBMS按照安全性控制规则进行检查，通过则允许访问

用户 + 权利 = 访问规则表（AccessRule）

• AccessRule放在数据字典（系统目录）中
• 可以按照组来管理用户
• 控制的粒度可大可小：属性、元祖
• 权利：CRUD
• 谓词：拥有权利需要满足条件

例子
SOTP表：

第1种：存储矩阵

大多数DBMS都采用这种方式，但是对于 谓词§ 没有办法处理

第2种：视图

可以解决 谓词§ 的问题

6.2 SQL实现安全性控制

DB的安全性控制数据 DCL
级别分三级：

• 1 R（关系级别）
• 2 U（关系级别）
• 3 C（账户级别）

3级自动拥有1 2 级权限

6.2.1 授权

Grant All priviledges on employee to emp2001;
Grant select on employee to emp5001;
Grant select on employee to public; // 面向全部有效用户
Grant select on employee to UserB with grant option;//给与B可以授权的权限

6.2.2 收回权限

Revoke select on employee from User8;

6.3 安全控制遇到的问题

授权过程

权限传播就分成了：

• 水平传播：授权者的再授权数据(数的广度)
• 垂直传播：被授权者又可以传播给其他授权

权限收回时，通过其传播的权限也收回

6.2 强制安全性机制

• 强制安全性对数据对象进行安全分级
  • 绝密(Top Secret)
  • 机密(Secret)
  • 可信(Confidential)
  • 无分类(UnclassField)
• 用户也有以上的等级

规则：

1. 用户S，只能R数据O ：level(S) >= level(O)
2. 用户S，只能U数据O ：level(S) <= level(O)

七、嵌入式SQL

交互式SQL的优点：

• 记录集合操作
• 非过程性操作：指出要做什么，不需要指出怎么做
• 一条语句就可以实现复杂的查询结果

缺点：

• 复杂的难度高
• 特别复杂的结果，不能用一条SQL完成
• 程序上直接操作麻烦

嵌入式SQL = 高级语言 + SQL

• 既有高级语言的过程性控制
• 又结合SQL语言的复杂性结果的非过程性
• 为DB操作者提供安全可靠的方式：应用程序

--交互式SQL
select Sname,Sage from Student where Sname = 'ZhangSan';

--嵌入式SQL
exec sql Select Sname,Sage into :vSname,:vSage from Student where Sname = 'ZhangSan'
--exec sql 引导SQL语句：提供给C编译器，便于对SQL语句编译成C编译器可识别的语句
--指出接受SQL语句检索结果的程序变量
--冒号引导的程序变量：‘:vSname，:Sage’

7.1 变量声明

在嵌入式SQL语句中可以出现宿主的变量

--这些变量需要特殊声明
exec sql hegin declare section:
	char vSanme[10],specName[10] = 'ZhangSan';
	int vSage;
exec sql end declare section;

--
exec sql select Sanme,Sage into :Svname,:Svage 
from Student where Sname = :SpecName;

--这样直接在赋值的时候改变:specName的值就可以改变SQL的结果

7.1.2 DB连接

不同的DBMS，连接语句的语法有差异
SQL标准中建议的语法连接为:

exec sql connect to target-server 
as connect-name user user-name;

exec sql disconnect connect-name;(current)

Oracle中的数据库连接：

exec sql connect :user_name identify by :user_pwd;

--disconnect
exec sql commit relase;
//exec sql rollback release;

DB2 UDB中数据库链接

exec sql connect to mydb user :user_name using :user_pwd;

7.1.3 SQL提交与撤销

提交commit & 撤销rollback，才能确认操作结果

很多DB，断开前必须提交撤销

--Oracel
exec sql commit release;
exec sql rollback release;

7.2 事务

定义：存取orupdate的一次执行，或者说 一条or多条的执行被看做一个事务

事务由程序员定义，有开始和结束，结束前需要提交or撤销

Begin Transcation
	exec sql...
	exec sql2...
	exec commit work | commit rollback work
End Transcaiton

从DBMS角度，事务是一组sql的集合，便于提供一致性状态转换的保证

事务的特性ACID：

• 原子性Atomicity：一组事务的更新操作是原子不可再分的，对DB而言，要么全做，要么都不做
• 一致性Consistency：DBMS保证操作状态是正确的，符合一致性的操作规则
• 隔离性Isolation：多个事务不影响
• 持久性Durability：已经提交的事务的影响是持久的，被撤销事务的影响是可恢复的
  
  

7.3 游标

之前举的例子，检索单行结果Sname = :specName，可以直接将单行结果返回到宿主变量中

检索多行结果，则需要使用游标(Cursor)

• 游标是指向检索记录集的指针
• 通过这个指针的移动，每次读一行，处理一行，再次读一行…处理完毕
• 读取一行的操作是通过fetch…into语句实现的：每一次fetch，都是先向下移动指针，然后再读取
• 结果集有结束标识EOF，用来标记后面没有记录了

游标Cursor先定义后使用，一条一条处理数据，最后关闭

exec sql declare cur_student cursor for 
	select Sno,Sname,Sclass from Student where Sclass = '035011'
	
	exec sql open cur_student;
	exec sql fetch cur_student into :vSno,:vSanem,:vSclass;
	...
exec sql colse curstudent;

游标可以定义一次，打开多次，关闭多次

--游标Cursor的定义
exec sql declare cur_student cursor for
	select Sno,Sname,Sclass from Student where Sclass = :vClass
	order by Sno
	for read only;
--游标的数据读取
exec sql open cur_student;
...
exec sql ferch cur_student into :vSno,:vSname,:vSage
...
exec sql close cur_student;

7.3.1 可滚动游标及数据库的增删改

ODBC支持可滚动的Cursor
可滚动：指针可以向上 (从头) 移动

• next：下一跳
• prior：前一条
• first：第一条
• last：最后一条
• absolut value_spec : 绝对行，（1~current row）
• relative value_spec :相对行，（相对当前，+ - 前后移动）

7.3.2 游标对数据库记录的删改动

--查找删除
exec sql delete from customers c where c.city = 'Harbin' 
	and not exists(select * from orders o where o.id = c.id) 

--定位删除
exec sql declare delcust cursor for
	select cid from customers c where c.city = 'Harbin' 
	and not exists (select * from orders o where o.cid = c.cid)
	for update of cid;

exec sql open delcust
While(True){
	exec sql fetch delcust into :cust_id;
	exec sql delete from cutomers where current of delcust;
}

exec sql insert into student(sno,sname,sclass)
	value('03510128','张三','035101')

7.4 状态捕获&错误处理

八、动态SQL

把参数动态的传递给SQL

8.1 动态SQL的实现方式

拼接 String 的 SQL

8.2 动态SQL2种执行方式

• 立即执行：运行时编译并执行
  exec sql execute immediate:host-variable;

• Prepare-Execute-Using语句：prepare先编译语句，允许动态参数，execute语句执行，using传递动态参数

8.3 数据字典SQLDA

数据字典：定义的信息，包括：表、View、索引、列、权限、约束…

数据字典的构成

• 关系相关的信息
  • 关系的名字
  • 属性名及其类型
  • 视图的名字&定义
  • 完整性约束
• 用户&账户信息，包括密码
• 统计与描述性数据：每个关系中元祖的数目
• 物理文件组织信息
  • 关系如何存储的（有序、无须、散列…）
  • 关系的物理位置
• 索引

数据字典的结构

• 也是一个表
• 用 “为内存高速访问的数据结构 ”上存储

Oracle中数据字典由视图来构成，分成三种形式，不同的前缀来标识

• USER_:用户视图
• ALL_:扩展用户视图，用户可以访问的对象
• DAB_:DBA视图(所有用户都可以访问的DBA对象的子集)
  
  同样也有列的信息的3个视图：User_TAB_Columns、ALL_TAB_Columns、DBA_TAB_Columns供DBA和用户使用数据字典中关于表的列的信息
  

select Column_Name from All_TAB_Columns
where Table_Name = 'Student'

Oracle数据字典中还有其他视图：

• TABLE_PRIVILEDGE（| ALL_TAB_GRANTS）
• COLUMN_PRIVILEDGE（| ALL_COL_GRANTS）:可访问列的权限
• CONSTRAINT_DEFS(| ALL_CONSTRAINTS):可访问的约束的
  …

Select View_Name from all_views where owner = 'SYS' 
and view_name like 'ALL_%' or view_name like 'USER_%'

SQLDA:SQL Decriptor Area:SQL描述区域

九、 ODBC

open database connection
不同语言的应用程序 & 不用的数据库之间的通信

使用前，需要DBMS的dirver 安装到ODBC的环境中去。
App调用ODBC的API时，API会调用具体的Driver的函数库

App ---> ODBC ---> Driver的函数库 ---> DBMS ---> DB

int ODBCexample(){
	RETCODE error; //返回状态码
	HENV env; //环境变量
	HDBC conn; //链接句柄
	SQLAllocEnv(&env);
	SQLAllocConnect(env,&conn);
	//分配数据库连接环境
	SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, avipasswd", SQL_NTS);
	//打开一个数据库连接
	{ .... Do actual work ... }
	//与数据库通讯
	SQLDisconnect(conn);
	SQLFreeConnect(conn);
	SQLFreeEnv(env);
	//断开连接
}

SQLExecDirect()发送SQL命令
SQLFetch() 获取结果元祖
SQLBindCol() 绑定C语言变量与结果中的属性

JDBC

Java DataBase Connection
Java版本的应用程序接口API，提供Java程序与DB的链接服务

JDBC API有两个包：

• Java.sql :核心API 连接到数据库
  • java.sql.DriverManager、
  • java.sql.Driver、
  • java.sql.Connection
• Javax.sql 扩展API
  • JNDI(java Naming and Directory interface,java 命名和目录接口)
  • 管理连接池、分布式事务、
  • DataSource接口来连接到数据库

OB JB 对比