目录
SQL数据定义
基本类型
SQL支持多种类型：
int 4个字节，sallint两个字节
基本模式定义
完整性约束
例子：创建一个department关系
创建一个支行的关系，他拥有3个属性：支行名、所在城市以及资产，对于支行名这个属性，他要求是由非空的长度为15的字符串构成，所在地要求是长度为30的字符串，资产是整数型。语句最后分号结束
完整性约束
-
not null 非空，在该属性上不允许有空值
-
primary key（A1,A2,…,An）
- 这些属性构成关系的主码，主码属性必须非空且唯一（没有一个元组的主码属性是空值，也没有两个元组在所有主码属性上取值相同。）
- 主码是可选的，但为每个关系指定一个主码更好一些。
-
foreign key（A1,A2,…,An）reference s
- 关系中任意元组在属性（A1,A2,…,An）上的取值必须对应于关系s中某元组在主码属性上的取值
create table instructor
(ID varchar(5),
namee varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2)
primary key(ID),
foreign key(dept_name) references department);
drop table命令
要从SQL数据库中去掉一个关系，我们使用drop table命令。drop table命令删除数据库中所有关于被去掉的关系的信息。
drop table r;
delete from r;
delete table r保留关系r，但删除r中所有的元组；drop table r不仅删除r的所有元组，还删除r的模式
不同之处，delete from r仅仅是删除了所有元组，数据库里还会有一个r的空表，而drop命令除了删除元组之外，还会把r的模式也删除了
alter table命令
alter table 命令为已有的关系增加属性。
alter table r add A D;
r是现有关系的名字，A是待添加属性的名字，D是待添加属性的域,关系中的所有元组在该属性的取值将被设置为null。
同样的，从关系中去掉属性
alter table r drop A;
很多数据库系统并不支持去掉属性，尽管允许去掉整个表
SQL查询的基本结构
SQL表达式基本结构包括三个子句：select，from和where。
一个典型的SQL查询具有如下形式：
select A1, A2, ..., An
from r1, r2, ..., rm
where P
每个Ai表示一个属性，每个ri表示一个关系，P是一个谓词。
等价的关系代数表达式：
Π
A
1
,
A
2
,
.
.
.
,
A
n
(
σ
P
(
r
1
×
r
2
×
.
.
.
×
r
m
)
)
\Pi_{A_1,A_2,...,A_n}(\sigma _{P}(r_1\times r_2\times ... \times r_m))
ΠA1,A2,...,An(σP(r1×r2×...×rm))
- 对r1到rn的进行笛卡尔积的运算上按照表达式p选取特定的元组，再在上面选择A1到An列作为最终的结果。
- 如果省略where子句，谓词P为true。
- 与关系代数表达式的结果不同，在SQL的查询结果中可以包含很多重复的元组。
select子句
- select子句对应关系代数中的投影运算，用来列出查询结果中所要的属性
- 在sql语句中大小写不敏感，用户编写sql语句系统不会区分大小写
- 允许在查询结果的关系中存在冗余的情况。
- 例子：从贷款关系中找到所有支行的名字
如果用关系代数表达式来描述，就是对贷款关系在支行名字属性列上做投影 - SQL允许结果中出现重复，要强行删除重复，在select后加入关键词distinct
- 使用关键词all来显示指明不去除重复，all保留重复元组是默认的
- *星号表示“所有的属性”
select * from loan，表示查询贷款关系中的所有元组
- select子句还可以带+ - * /的算术表达式，（关系代数里的投影运算下标也可以是算数表达式），运算对象可以是常数或元组的属性。
where子句
- where子句对应关系代数中的选择谓词，包括一个作用在from子句中关系属性上的谓词
- SQL在where子句中使用逻辑连词and、or和not
- 逻辑连词的运算对象可以是包含比较运算符＜, ＞, ＜=, ＞=, ==, ＜＞的表达式
- SQL允许我们使用比较运算符来比较字符串、算术表达式以及特殊类型，如日期类型。
- SQL提供between比较运算符来说明一个值是小于或者等于某个值，同时大于或等于另一个值。between所指代的范围是包括上下界的，是一个闭区间。
from子句
- from子句对应关系代数中的笛卡尔积，它列出表达式求值中需要扫描的关系。
在select子句中列出的是我们最终想要查询的对象，from子句中是我们的查询所涉及到的所有关系，而查询条件则在where子句中进行了描述
附加的基本运算
更名运算
- 使用as子句
- 在两个不同关系可能拥有相同的属性名，或者在select子句中有算数表达式，那么结果属性就没有名字
- SQL提供了为关系和属性重新命名的机制，使用as子句来进行
- as子句可以出现在select子句中，也可以出现在from子句中，即as子句既可以对属性更名，也可以对关系更名。
select name as instructor_name,course_id
from instructor,teaches
where instructor.ID=teaches.ID
- 重命名可以把一个长的关系名替换成短的，方便
select T.name,S.course_id
from instructor as T,teaches as S
where T.ID=S.ID
可以悄悄看出，先执行from，后执行where，再select
- 重命名可以用在需要比较同一个关系中的元组的情况，把一个关系跟它自身进行笛卡尔积运算，为了适用于需要比较同一个关系内部的元组这一情况。
例子：找出资产至少比位于Brooklyn的某一家支行高的支行名
字符串运算
SQL支持字符串的模式匹配运算符。
- like运算符：
- %：匹配任意子串
- _：匹配任一个字符
模式是大小写敏感的，大写字符和小写字符不匹配
例子： - 'intro%'匹配任何一个intro打头的字符串
- ‘ %comp’匹配任何包含‘comp’子串的字符串
- ‘___’匹配只含三个字符的字符串
- ‘___%’匹配至少含三个字符的字符串
- 使用escape关键字定义转义字符，使用反斜线\作为转义字符
like 'ab\%cd%' escape '\' --匹配所有以“ab%cd开头的字符串”
SQL还支持在字符串上有多种函数：
- 使用“||”串联
- 大小写转换
- 提取子串、计算字符串长度
排列元组的显示次序
order by子句可以让查询结果中元组按排列顺序显示
例子：按照字母顺序列出在Perryridge支行中有贷款的客户
- order by子句默认使用升序，用desc表示降序，asc表示升序
- 排序可在多个属性上进行。
重复元祖
多重集：（multiset）：一个集合中一个值可以出现多次，值出现的顺序是无关紧要的
在某些情况下，包含重复元组的关系是有用的。SQL不仅明确定义查询结果中有哪些元组，而且还定义结果中的每个元组各有几个副本。
定义关系运算符的多重集版本（已知关系r1和r2）：
集合运算
SQL作用在关系上的union、intersect、except运算对应数学集合论中的
∪
\cup
∪、
∩
\cap
∩、
−
-
−
保留所有副本，不去除重复，则使用union all, intersect all, except all
- sql作用在关系上的union、intersect和except运算对应于数学集合论中的并、交和差运算。这三个运算都是自动消除冗余的。
- 为了保留多重集中的所有的冗余结果，我们可以在原有的关键词后面加上all，也就是使用union all、intersect all和except all来替换之前的三个关键词来保留这些多重集中的冗余结果。
- 于这些重复的元组到底在结果中出现了多少次，假设一个元组在关系r中出现了m次，并且在关系s中出现了n次，那么它在
r union all s中出现了m+n次，在r intersect all s中出现了m和n中最小的次数，在r except all s中刚出现了，0和m-n中最大的次数。- m + n times in r union all s
- min(m,n) times in r intersect all s
- max(0, m – n) times in r except all s
mysql里只支持union，不支持intersect和except
聚集函数
基本聚集
聚集函数是以值的一个集合为输入，返回单个值的函数，这个集合可以是普通的集合或者是多重集。在sql中提供了五个固有聚集函数，包括：
平均值、最小值、最大值、总和以及计数函数。
- avg 平均值
- min 最小值
- max 最大值
- sum 总和
- count 计数
其中sum和avg的输入必须是数字集，但其他运算符还可以作用于非数字数据类型的集合上，比如对于字符串，我们就可以对他进行计数以及取最大或者最小值。
使用count计算一个关系中元组的个数：count(*),sql不允许在用count(*)时使用关键字distinct， distinct可用在删除重复元组的情况下，放在聚集函数的表达式中。
分组聚集
将聚集函数作用到一组元组集上，也就是说按照某一条件把关系中的元组进行分组，之后在在各个分组中使用聚集函数。 通过在group by子句中给出的一个或多个属性来构成分组的条件。在group by子句中的所有属性上取值相同的元组将被分到一个组中
- group by后边的属性必须在select中出现
- 保证出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性。任何没有出现在group by子句中的属性如果出现在select子句中，它必须在聚集函数内部，否则是错误的查询，错误实例：（ID没在groupby中但是出现在select中）
select dept_name,ID,avg(salary)--错误！！！
from instructor
group by dept_name;
having子句
- 对分组条件进行限定，having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数，having对聚集函数的值加以判断，不能单独使用。
- 聚集函数应用在having中，如果用在where中，只能用子查询来实现，在子查询中进行聚合函数计算，在主查询中进行where判断
- 任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中，否则是错误的
顺序from -> where -> group by -> having -> select
例子：
空值
- 对于空值参与的运算，如果算数运算的输入有一个是空，则该算数表达式的结果为空。例如5+null的结果还是空值null。
- 如果有空值参与比较运算，sql将比较运算的结果看成是unknown，也就是说结果既不是is null，也不是 is not null，这就创建了除了true和false以外的第三种逻辑值。
- 如果where子句谓词对一个元组计算出unknown的话，那么这个结果是错误的，该元组不能被加入到结果集中。
- 聚集运算中除了count（*）以外的所有聚集函数都忽略输入集合中的空值。
嵌套子查询
- 子查询是嵌套在另一个查询中的select-from-where表达式，子查询嵌套在where子句中，同层对于集合的成员、集合的比较、集合的基数进行检查
集合成员资格
- in检测元组是否是集合中的成员
- not in检测是够不是集合中的成员
- in、not in也适用于枚举集合
select distinct name
from instructor
where name not in ('M','E');
集合的比较
- “至少比某一个要大”用 结构** >some **来表示
- some表达式：
- 例子：
- 只要有一个满足，表达式为true
- =some 等价于 in，但是 ≠ \neq =some 不等价于 not in，因为in的反面是对任意t，不属于r
- not in等价于 ≠ \neq =all，=all不等价于in
- all：“比所有的都”
- some，all都允许<,<=,>,>=,<>
some详解
其他运算符和some组合的情况
- 至少小于其中一个(<some),
- 至少小于等于其中一个(<=some),
- 至少大于等于其中一个(>=some),
- 至少等于其中一个(=some)
- 至少不等于其中一个(<>some),SQL中<>符号为不等于
=some等价于in
- 因为=some的意思是至少等于其中一个,而in关键字,也是至少等于其中一个的意思,所以=some等价于in。
<>some不等价与not in
- <>some的意思是至少不等于其中任意一个,
- not in的意思是都不等于其中任意一个.
可见not in要求比<>some要严格,所以<>some不等价于not in。
all详解
- 大于其中所有的>all,
- 小于等于其中所有的<=all,
- 大于等于其中所有的>=all,
- 等于其中所有的=all,
- 不等于其中所有的<>al,
- <>all等价于not in
- <>all的意思是不等于其中所有的,和not in的意思是一样的,两者等价。
=all不等价与in
- =all的意思是等于其中所有的,
- in的意思是等于其中一个
- =all比in要求要严格,=all不等价于in
例子：找出满足下面条件的所有教师的姓名，他们的工资至少比biology系某一个教师的工资要高
法一：
select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
法二：
select name
from instructor
where salary > some(select salary
from instructor
where dept_name = 'Biology');
例子：找出平均工资最高的系
select dept_name
from instructor
group by dept_name
having avg( salary) >= all (select avg(salary)
from instructor
group by dept_name);
空关系测试
测试一个子查询的结果中是否存在元组,exist结构在作为参数的子查询非空时返回true
例子：找出在2009秋季学期和2010春季学期同时开课的课程
select course_id
from section as S
where semester = 'Fall' and year = 2009 and
exists (select *
from section as T
where semester = 'Sping' and year = 2010 and
S.course_id = T.course_id);
来自外层查询的一个相关名称（S）可以用在where子句的子查询中
双重否定表示肯定
关系A包含关系B可以写成not exists(B except A)
例子：找出选修了biology系开设的所有课程的学生
select S.ID,S.name
from student as S
where not exists((select course_id
from course
where dept_name = 'biology'
except
(select T.course_id
from takes as T
where S.ID=T,ID));
重复元组存在性测试
sql提供一个布尔函数，用于测试在一个子查询的结果中是否存在重复性元组，如果作为参数的子查询结果中没有重复的元组，unique结构将返回true值。
例子：找出所有在2009年 最多 开设一次 的课程
select R.course_if
from course as T
where unique(select R.course_id
from section as R
where T.course_id = R.course_id
and R.year = 2009)
- unique:只有一个
- not unique：至少两个
from子句中的子查询
- 由于任何s-f-w表达式返回的结果都是关系，所以s-f-w式子也可以被插入到from中
- 增加一个新的福安息进行查询
例子：找出系平均工资大于4200的那些系中教师的平均工资
法一：having子句
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 4200;
法二：from嵌套子句
select dept_name,avg_salary
from(select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name)
where avg_salary>4200
from子句中的子查询计算平均工资，在having子句中的谓词放在外层where中
法二改进：
select dept_name,avg_salary
from(select dept_name,avg(salary)
from instructor
group by dept_name)
as dept_avg(dept_name,avg_salary)
where avg_salary>4200
改进后给from子查询额结果关系起名字，同时重命名属性
- 有时having做不到的，from嵌套可以做到
- from子句中的子查询不能使用来自from子句其他关系的相关变量，可以在from子句子查询前缀一个lateral（老师也没讲吧）
with子句
- 提供定义临时关系的方法，仅一次查询有效，也只对包含with子句的查询有效。
- with子句使查询逻辑更清晰，并允许在一个查询内的多个地方使用视图定义
例子：找出具有最大预算值的系
with max_budget(value)as
(select max(budget)
from department)
select budget
from department,max_budget
where department.budget = max_budget.value;
定义了临时关系max_budget，并马上使用
视图
数据库的修改
删除
delete from r
where P;
- P：谓词，r：关系
- 从r中找出所有使P（t）为T的元组，从r中删除，没有where则删除所有元组（关系仍然存在只是变成空的了）
- delete只能作用于一个关系，多个关系删除使用多个delete
- 通过where子句嵌套sfw可以引用任意数目的关系
- 删除时注意顺序
delete from instructor
where salary < (select avg(salary)
from instructor);
插入
insert into course
values('c','d','cc',4);
- 元组属性值的排列顺序和关系模式中属性排列顺序一致
- 允许在inset中指定属性，这样顺序就可以乱了
insert into course(c_id,title,dept_name,cre)
values('c','d','cc',4);
- 在查询结果的基础上插入元组
- 插入之前先执行完select语句，否则重复插入
更新
- 不改变元组的情况下改变部分属性的值
update instructor
set salary = salary*1.05
where salary < 70000;
- update语句的顺序十分重要
使用case语句,避免次序引发的问题
update instructor
set salary = case
when salary <= 10000 then salary*1.05
when pred2 then result2
when pred3 then result3
else salsry*1.03
end
连接表达式
- 内连接（自然连接）
- 左外连接
- 右外连接
- 全外连接
高级SQL
日期和时间类型
- date：yyyy-mm-dd
- time：hh:mm:ss
- timestamp：yyyy-mm-dd hh:mm:ss:ii…i
数据转换：
cast e as t
将一个字符串e转换成类型t，t可以是以上三种
提取：
extract (field from d)
从date或time值d中提取出单独的域
用户定义的类型
create type定义新的类型
create type dollars as numeric (12,2) final;
--把用户定义为总共12位数字的十进制数，final不用管
create domain定义新的域
create domain ddollars as numeric (12,2) not null;
--在域上声明约束，例如not null
大对象类型
large-object types
查询返回一个指针blob
一致性约束
(integrity constrain)
完整性约束通过确保对数据库的授权更改不会导致数据一致性的丢失，从而防止意外损坏数据库
- not null 属性值不可为空
- primary key
- unique 候选码，主码属性唯一
- check§,where p is a predicate（p断言）
not null
限定属性branch name的需来排除空值
unique
A1,A2,…,Am形成了一个候选码，及在关系中没有两个元组能在所有列出的属性上取值相同
候选码属性可以为null
check子句
输入check不会报错，但是没有实现check功能的触发器
- check( P )子句指定一个谓词P，关系中的每个元组都必须满足谓词P
引用约束（参照完整性）
referential integrity
- primary key
- unique
- foreign key
断言（assertion）
- 断言是一个谓词，希望数据库总能满足的一个条件
例子：对于student关系中的没个元组，它在属性tot_cred上的取值必须等于该生所成功修完课程的学分总和
授权（authorization）
grant语句来授予权限
grant <权限列表>
on <关系名或视图名>
to <用户\角色列表>
- read（select）：用于读取关系中的元组
- update：允许用户修改关系中的任意元组
- inset：允许用户往关系中插入元组
- delete：允许用户从关系中删除元组
revoke语句收回权限, 用户被授予这个权限才能被撤销
revoke <权限列表>
on <关系名或视图名>
from <用户\角色列表>
扫一扫
613
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
