SQL概述学习笔记

关于这个笔记

数据库的学习首先就是关于SQL查询语言的学习，下面的学习笔记依据于数据库系统概念（本科教务版） 这本书，笔记中的语法示例也都使用的是该书的数据和例子。这篇博客只从整体上了解SQL，尤其不涉及高级SQL的具体用法，很多地方都是一句带过，只为提到这个东西。也有些东西并没有记录下来。具体详细的学习以后细分为各个部分的博客再做记录。

要注意该书介绍的都是SQL标准的写法，而各个数据库有时都有各自自己的具体实现，可能在语法上与标准不同，但概念却是相通的。

数据类型

• char(n)：定长字符串
• varchar(n)：变长字符串，最大长度为n
• int
• smallint：小整数
• numeric(p, d)：定点数，指定精度。这个数有p位，其中d位在小数点右边
• float(n)：精度至少为n的浮点数

时间类型

• date：年月日，如’2020-04-06’
• time( p )：小时、分、秒。p是秒的小数点后数字的位数，默认为0位。如’23:03:00’
• timestamp( p )：date和time的组合。p同上，但默认为6位。如：‘2020-04-06 23:03:00’。注意若不赋值或为NULL时，其默认为系统时间。

利用cast e as t将字符串e转化为类型t，其可为date,time或timestamp。

大对象类型

• clob：字符数据的大对象。如book clob(10KB)
• blob：二进制数据的大对象。如movie blob(2GB)

一个应用通常用一个SQL查询检索出一个大对象的“定位器”，然后在宿主语言中用定位器操作对象以提高效率。

字符串

用单引号标示字符串，字符串中的’表示为’'，如：it’s right表示为 ‘it’‘s right’
使用like操作符来实现模式匹配。

• %：匹配任意子串
• _：匹配任意一个字符
• [ ]：匹配[]中的任意一个字符

如：查询姓张、王的学生select from student where name like '[张王]%'。
查询姓张的三个字的同学select from student where name like '张__'。但会有一些两个字的人的名字后可能因为有空格而被选中，为了解决尾随空格的问题，应该使用rtrim()函数where rtrim(name) like '张__'。

在like中使用escape定义转义字符。如like 'ab\%cd\\ef%' escape '\'匹配以“ab%cd\ef”开头的字符串。
使用not like搜寻不匹配项。

也可以使用regexp来使用正则表达式搜索。

select name
from student
where name regexp 'A'

会发现上面的语句不区分大小写，如果要区分则应该使用regexp binary。

用户定义的类型

虽然对于student来说，name和dept_name都是字符串类型，但如果从概念层而不是物理层来看待数据库的话，name和dept_name应该有不同的域。例如，如果将’Music’赋值给一个学生的名字，应该是一种错误的赋值，为了支持这种检测，SQL提供了独特类型。可以用create type定义新类型。例如：

create type Dollars as numeric(12, 2) final;
create type Pounds as numeric(12, 2) final;

当尝试将一个Dollars变量赋值给Pounds变时，就会产生编译错误。
一种类型的数值可以被转换到另一个域，如：
cast(department.budget to numeric(12, 2))

空值与unknown

如果算术表达式的任意输入为null，则结果为null。

将涉及null的任何比较运算结果定义为unknown，unknown是第三个逻辑值。
false and unknown结果是false，false or unknown结果是unknown，not unknown结果是unknown。null = null会返回unknown。

{(‘A’, null), (‘A’, null)}这样两个元组被认为是重复的，因此distinct子句只会保留其中一份拷贝。

DDL

创建数据库和表

使用create table A like B创建与表B模式相同的表A。
把查询的结果存储成一个新表是很有用的，如：

create table t1 as
    (select *
    from instructor
    where dept_name = 'Music')
with data;

如果省略with data，表会被创建，但不会载入数据。

完整性约束

可以将initially deferred加入约束说明，这样完整性约束就不再在中间步骤检查，而是一个事务结束时候检查。
在foreign key中可以指明如果被参照关系上的删除或更新违反了约束后可以采取的行为，而不是拒绝这样的操作。如：

foreign key (dept_name) references department (dept_name)
    //on delete cascade，采取级联删除操作，即删除参照了被删除的元组
    //on update cascade, 级联更新
    //on delete set null，设为null
    //on delete set default，设为默认值

实体完整性

• primary key：主码。必须是非空且唯一的。
• unique：唯一。可为空且唯一，但只能有一个null。
• not null
• default：默认赋值。
  alter table table_name modify salary int default 0;

使用alter table table_name add constraint来给已有关系增加约束关系，其中constraint是要添加的约束。

参照完整性

• foreign key：外码。依赖于另一个关系的某属性。
  foreign key (dept_name) references department (dept_name)

用户自定义完整性

• check：关系中的每个元组都必须满足check后的谓词。

DML

删除

delete用于删除。delete from table_name删除表中所有元组。如果要连该表也一起删除则使用drop table table_name。也可以指定元组删除delete from r where P

修改

alter用于修改。
alter table table_name add A D其中A是待添加属性的名字，D是该属性的域。
alter table table_name drop A表示从该表中去掉属性A。
alter table table_name modify colomn column_name new_type表示修改字段的数据类型。

插入

默认按照表中属性顺序插入

insert into table_name
values(...)

也可以显式地指定属性

insert into table_name(name, ID, ...)
values(...)

更新

使用update。如：

update instructor
set salary = salary * 1.5;

case

case语句的格式为：

case
    when P1 then result1
    //...
    when Pi then resulti
    else result0
end

类似于C中的switch语句，else就是其default。如下面这样使用：

select case
            when sum(credits) is not null then sum(credits)
            else 0
       end

查询

distinct

distinct表示去重，select distinct A
*表示所有的属性，select A.*

order by

order by表示排序，默认使用升序，可以显式地用desc表示降序或asc表示升序。

select *
from instructor
order by salary desc, name asc

聚集函数

• 平均值：avg
• 最小值：min
• 最大值：max
• 总和：sum
• 计数：count

可以在聚集函数中使用distinct，如：select count(distinct ID)，但不能在count(*)时使用distinct。
除了count(*)以外的聚集函数会忽略值为null的元组，而count(*)则会照常计数。

聚合函数不能用在where子句中。

集合运算

• union表示$\cup$
• intersect表示$\cap$
• expect表示集合-运算

三个运算都自动去重，如果要保留重复，就要用union all
“至少比一个大”用 > some表示，“比所有都大”用 > all表示。

//查询工资比音乐系每个教师工资都高的人
select name
from insturctor
where salary > all (select salary
                    from instructor
                    where dept_name = 'Music')

另外，有 < some, <= some, <> some等（<>即!=）。其中，= some等价于in，但<> some 不等价于not in，而是<> all等价于not in，并且= all也不等价于in。

group by

group by子句中的所有具有相同属性的元组被分到一组，having对分组再进行条件限定。
如：

//查询平均工资，按照系名分组，只留下平均工资大于42000的分组。
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000

需要保证任何没有出现在group by或having中，而出现在select中的属性，必须是在聚集函数的内部的。

limit

用于分页查询，是MySQL独有的。

// 语法为limit 开始的索引, 每页查询条数
select * from student limit 0, 3;	--第一页
select * from student limit 3, 3;	--第二页

in

in测试元组是否是集合中的成员，也能用于枚举类型，如：

select distinct name
from student
where name not in ('Arisa', 'Faker')

exists

exists测试一个子查询的结果中是否存在元组。可将关系A包含关系B写成not exists(B expect A)。

unique

unique测试在一个子查询的结果中是否存在重复元组。由于在t1或t2的某个域为空时，判断t1=t2为假，所以只要元组有一个属性为空，unique就有可能返回true。

with

with子句定义临时关系，可以使查询语句更加清楚。

//查询有最大预算的系
with max_budget (value) as  //max_budget是临时关系名。value是临时属性名
    (select max(budget)
    from department)
select budget
from department, max_budget
where department.budget = max_budget.value;

连接

自然连接

natural join只考虑在两个关系都出现的属性上取值相同的元组对。SQL提供了一种自然连接的构造形式，来指定需要哪些列相等tA join tB using (A, B, ...)

on

on在参与连接的关系上设置条件，写法与where类似。
下面的两种写法是等价的：

select *
from student join takes on student.ID = takes.ID;
//等价于
select *
from student, takes
where student.ID = takes.ID;

但on有他自己的两个优点：1.在外连接中on与where有不同的用法；2.在on中指定连接条件，在where指定其余条件，使得语句更加易懂。

外连接

在参与连接的任何关系中的某些元组可能会因为与另一个关系中的所有元组都不匹配而丢失，外连接通过在结果中创建包含空值元组的方式，保留那些丢失的元组。
外连接分为三种：

• 左外连接(left outer join)只保留其左侧关系的元组
• 右外连接(right outer join)只保留右侧
• 全外连接(full outer join)保留两个关系的元组

作为示例，左外连接是这样运算的：首先计算出内连接的结果，然后对于左侧关系中任意一个与右侧关系任何元组都不匹配的元组t，向连接结果中加入一个元组r，使得r在t中的属性被t赋值，其他属性被赋为null。

其他

视图

视图其实就是一种封装。格式为create view v as ...其中…是任何查询表达式。如：

create view faculty as
select ID, name, dept_name
from student
//视图的属性名可以按下述方式显式地指定
//create view faculty(stu.ID, stu.name, dept_name)

无论何时执行查询，视图都会被重新计算。

只有同时满足下列条件，才允许对视图进行修改

• from子句只有一个数据库关系
• select子句只包含属性名，不包含任何表达式、聚集、distinct
• 任何没有出现在select中的属性可以取null
• 查询没有group by或having

在视图定义的末尾加上with check option，使得对视图的修改（更新、插入）必须满足其定义中where子句的条件。

事务

有以下4个特征（ACID）：

• 原子性

• 持久性

• 隔离性

• 一致性

• start transaction事务开始

• commit work提交当前事务

• rollback work回滚当前事务，即撤销当前事务中所有SQL语句

一个DML语句会自动提交一次事务。

游标

用于滚动查看数据。

//创建游标
declare myCursor for
select ID from student;
//创建存储数据的变量
declare o int;
//打开游标
open myCursor;
//使用游标获取数据
fetch myCursor into o;
//关闭游标
close myCursor;

索引

在关系的属性上创建的索引是一种数据结构，用于高效查找。

创建索引的语法为：create index studentID_index on student(ID);

角色

用户可以理解为角色的子集，各个用户都属于一个角色。创建角色的语法为create role <角色名>

授权

授权包括select,insert,update,delete权限。用grant来授权，其格式为：

grant <权限列表> (限制的属性，可选，默认为所有属性)
on <关系/视图名>
to <用户/角色>

用revoke来收回权限，格式与grant相同。

用户名public指所有当前和未来的用户。

在默认情况下，被授权的角色/用户无法将此权限授权给其他人，但可以通过在grant后附加with grant option来允许该操：grant select on department to Arisa with grant option，一个对象（关系、视图、角色）的创建者拥有该对象上的所有权限。

从一个用户/角色处收回权限可能导致其他用户/角色也失去该权限，这一行为称为级联收回，并且级联是默认的行为，但revoke可以添加restrict来防止级联收回：revoke select on department from Arisa restrict，可以用cascade代替restrict来显式地声明需要级联收回。

触发器

先引入一段使用触发器的代码

//创建一个触发器在给section关系插入后触发
create trigger timeslot_check1 after insert on section
    referencing new row as nrow
    //对每个插入的行都执行
    for each row
    //when指定一个条件，仅对于满足条件的元组才会执行触发器剩余的部分
    when (nrow.time_slot_id not in (
        select time_slot_id
        from time_slot))
    begin
        rollback
    end;

referencing new row as创建一个变量称为过渡变量用于存储插入、更新后的新元组，相应的，referencing old row as创建一个过渡变量用于存储已经更新或删除的行的旧值。

触发器也可以在事件之前激发，只要把after改为before。触发器只能相应insert, delete和update三种语句。

在加载数据的时候触发器应当被显式地设置为无效。触发器默认情况下在创建时是有效的，但可以通过alter trigger trigger_name disable将其设为无效。

循环、条件语句

下面给出这些语句的使用方法。

while

while 布尔表达式 do
    语句序列;
end while

repeat

repeat
    语句序列;
until 布尔表达式
end repeat

for

declare n integer default 0;
for r as 
    select budget from department
    where dept_name = 'Music'
do
    set n = n - r.budget
end for

leave类似于C的break，而iterate类似于C的continue

if

if 布尔表达式
    then 语句
elseif 布尔表达式
    then 语句
else    语句
end if

用其他语言访问数据库

JDBC

Java必须首先引用java.sql.*，它包含了JDBC所提供的接口定义。

必须在连接数据库之前调用Class.forName()完成驱动程序加载，其参数指定一个java.sql.Driver接口的实现类（一个驱动程序），如MySQL的驱动名称为com.mysql.jdbc.Driver。

通过调用DriverManager类的getConnection方法打开一个数据库连接，该方法有三个参数：第一个参数是以String类型表示的URL，指明服务器所在的主机名称以及可能包含的其他如通信协议等信息，后两个则是用户和密码。

通过Statement类的一个实例来向数据库发送SQL语句。方法getString()可以返回所有基本SQL数据类型的属性（都被转化为String）。

也可以通过以?来代表以后再给出的实际值，来创建一个预备语句。使用Connection类的prepareStatement()方法来提交SQL语句用与编译。在使用PrepareStatement类的executeQuery()和executeUpdate()执行查询和更新前，必须先使用setString()或setInt()等方法为?设定具体的值。各个set方法的第一个参数用来确定为第几个?设定值（从1而不是0开始），第二个参数是要设定的值。

预备语句是执行SQL查询的首选方法。因为如果使用Java的字符串操作来构造SQL语句，那么在用户输入了诸如’等的特殊字符时生成的SQL会出现错误，而各个set方法会自动完成检查，并插入需要的转义字符。

嵌入式SQL

嵌入式SQL与JDBC的主要区别为：嵌入的SQL请求被宿主语言的声明 及 允许运行时刻执行数据库访问的过程调用 所替代。为使预处理器识别嵌入式SQL，需要使用EXEC SQL语句，格式为：EXEC SQL <嵌入式SQL语句>。

执行任何SQL语句前，程序必须先连接到数据库EXEC SQL connect to sever username using password，其中的server指将要建立连接的数据库。

在嵌入的SQL语句中使用宿主语言的变量时需要加上冒号以区别SQL变量，这样使用的变量必须生命在一个DECLARE区段里:

EXEC SQL BEGIN DECLARE SECTION;
int a;
EXEC SQL END DECLARE SECTION;

使用declare curse（声明游标）语句来表示关系查询。但这时并不计算查询结果，必须用open或fetch语句得到结果元组。

EXEC SQL
    declare c cursor for    //c被称为该查询的游标
    select ID, name
    from student
    where tot_cred > :credit_amount;    //假设声明了tot_cred，并且有一个宿主变量credit_amount
EXEC SQL open c；   //执行上述查询并将结果存于一个临时关系中
//用fetch语句把结果元组赋值给宿主变量
EXEC SQL fetch c into :si, :sn; //ID赋给si，name赋给sn

执行open后游标指向结果的第一个元组，执行fetch后游标指向结果的下一个元组。

必须使用close语句告诉数据库系统删除用于保存查询结果的临时关系EXEC SQL close c;

与其他嵌入式SQL的实现方法相比，SQLJ提供了相同的特性，但更接近Java。

SQL函数和过程

SQL可以声明函数和过程，也允许用宿主语言定义函数，并且允许函数、过程的重载。具体语法每个数据库不太相同，没有使用SQL标准，使用时再查看。