数据库知识总结(一)
SQL
select customer-name,T.load-numer,S.amount
from borrower as T, loan as S
where T.loan-number = S.loan-number
%匹配任意子串，如'Perry%'匹配以Perry开头的字符串，'%idge%'匹配idge为子串的字符串
_匹配任意一个字符。
like
order by 元组显示顺序，order by amount desc降序
集合运算
union并运算，自动去除重复
union all保留所以重复
intersect交运算
except差运算
聚集函数
avg平均值
min最小值
max最大值
sum总和
count计数 count(*)计算元组个数，使用count(*)不允许使用distinct
group by分组
having
找出住在Harrison且在银行中至少有3个账户的客户的平均余额
select depositor.customer-name,avg(balance)
from depositor,account,customer
where depositor.account-number = account.account-number and
depositor.customer-name = customer.customer-name and
customer-city = 'Harrison'
group by depositor.customer-name
having count(distinct depositor.account-number)>=3
空值
null , not null -> unknown既不是null也不是not null
除count(*)外所有聚集函数都忽略null空值， 布尔类型true,false,unknown->对应的聚集函数some,every
嵌套子查询
连接词in
select distinct customer-name
from borrower
where customer-name in (select customer-name
from depositor)
集合的比较
找出那些资产至少比位于Brooklyn某一家支行要多的支行的名称
select branch-name
from branch
where assets > some(select assets
from branch
where branch-city = 'Brooklyn')
>all 比所有都大
测试是否为空关系
exists测试子查询是否有元组
找出在银行既有账户又有贷款的客户
select customer-name
from borrow
where exists(select *
from depositor
where depositor.customer-name = borrower.customer-name)
测试是否存在重复元组
unique测试子查询的结果中是否有重复的元组
找出所有在Perryridge支行只有一个账户的客户
select T.customer-name
from depositor as T
where unique(select R.customer-name
from account,depositor as R
where T.customer-name = R.customer-name and
R.account-number = account.account-number and
account.branch-name = 'Perryidge')
视图
create view branch-total-loan(branch-name, total-loan) as
select branch-name,sum(amount)
from loan
group by branch-name
复杂查询
（1）派生关系
as result(branch-name, avg-balance) as重命名
（2）with子句
with子句提供定义临时视图的方法
with max-balance(value) as
select max(balance)
from account
select account-number
from account, max-balance
where account.balance = max-balance.value
数据库的修改
（1）插入
insert into account values('A','P',1200)
（2）删除
delete from acounnt
where branch-name='Perridge'
（3）更新
update account
set balance = balance*0.5
where balance>=1000
关系的连接
（1）左连接 :左边的表不管on后面的连接条件是否成立都是not null
select * from loan left join borrower on loan.loan-number = borrow.loan-number
（2)右连接:右边的表不管on后面的连接条件是否成立都是not null
select * from loan right join borrower on loan.loan-number = borrow.loan-number
（3）内连接：条件满足的才会显示
select * from loan inner join borrower on loan.loan-number = borrow.loan-number
（4）自然连接：与内连接类似，区别在于loan-number只有1列，即不会重复
select * from loan natural inner join borrower on loan.loan-number = borrow.loan-number
DDL
建表
create table customer
(customer-name char(20),
customer-street char(20),
customer-city char(30),
primary key(customer-name))
删除关系
drop table r
trancate table r 比drop更高效，不可以回滚，而drop可以
更改关系
alter table r add A,D
索引
create index idx_balance on loan(balance)
drop index idx_balance on loan
