- 基本域类型: char(n),varchar(n),int,smallint,numeric(p,d),real,doubleprecision,float(n)
创建表:
cr(eatetablecustomercustomer_namecustomer_streetcustomer_cityprimarykeychar(20),char(30),char(30),(customer_name))cr(eatetablebranchbranch_namebranch_cityassets_cityprimarykeychar(15),char(30),numeric(16,2),(branch_name))cr(eatetableaccountaccount_numberbranch_namebalance_cityprimarykeychar(10),char(15),numeric(12,2),(account_number))cr(eatetabledepositorcustomer_nameaccount_numberprimarykeychar(20),char(10),(customer_name,accountnumber))添加元祖: insertintoaccountvalues(′A−9732′,′Perrvridge′,1200)
- 给每个在Perryridge支行贷款的客户赠送一个200美元的新存款账户
insertintoaccountselectloan_number,branch_name,200fromloanwherebranch_name=′Perryridge′
- 给每个在Perryridge支行贷款的客户赠送一个200美元的新存款账户
- 删除元祖:
deletefromaccount
- 删除余额低于银行平均余额的账户记录
deletefromaccountwherebalance<(selectavg(balance)fromaccount)
- 删除余额低于银行平均余额的账户记录
- 删除表: droptabler
- 修改表: altertableraddAD 或 altertablerdropA
数据库查询:
with...asselect...(selectdistinct...,...,avg/min/max/sum/count(...)as...,...from...as...,...where...and...orlike...ornot...orisnull..and(not)in(select...)and...>some/any/all(select...)andexists(select...)orderbygroupby(...)havingavg(...))union(all),intersect(all),except(select...)一些例子:
- 找出每个支行的储户数
selectbranch_name,count(distinct(customer_name))fromdepositor,accountwheredeposition.account_number=account.account_numbergroupbybranch_name - 找出平均账户余额大于1200的支行
selectbranch_name,avg(balance)fromaccountgroupbybranchnamehavingavg(balance)>1200 - 找出住在Harrison且在银行中至少有三个账户的客户的平均余额
selectdepositor.customer_name,avg(balance)fromdepositor,account,customerwheredepositor.account_number=account.account_numberanddepositor.customer_name=customer.customer_nameandcustomer_city=′Harrison′groupbydepositor.customer_namehavingcount(distinct(depositor.accountnumber)>=3) - 找出在Perryridge支行同时有账户和贷款的客户
selectdistinctcustomer_namefromborrower,loadwhereborrower.load_number=load.load_numberandbranch_name=′Perryridge′and(branch_name,customer_name)in(selectbranch_name,customer_namefromdepositor,accountwheredepositor.account_number=account.account_number) - 找出平均余额大于等于所有平均余额的支行
selectbranch_namefromaccountgroupbybranch_namehavingavg(balance)>=all(selectavg(balance)fromaccountgroupnybranch_name) - 对每位客户,判断他拥有账户的所有支行的集合中是否包含坐落在Brooklyn的所有支行的集合。这里“关系A包含B”写成
notexists(BexceptA)
。
selectdistinctS.customer_namefromdepositorasSwherenotexists((selectbranch_namefrombranchwherebranch_city=′Brooklyn′)except(selectR.branch_namefromdepositorasT,accountasRwhereT.account_number=R.account_numberandS.customer_name=T.customer_name - 找出所有在Perryridge支行中只有一个账户的客户
selectT.customer_namefromdepositorasTwhereunique(selectR.customer_namefromaccount,depositorasRwhereT.customer_name=R.customer_nameandR.account_number=account.account_numberandaccount.branch_name=′Perryridge′) - 查询在所有支行中总余额最多的支行
selectmax(tot_balance)from(selectbranch_name,sum(balance)fromaccountgroupbybranch_name)asbranch_total(branch_name,tot_balance) - 查询所有存款总额大于全部支行平均存款总额的支行
withbranch_total(branch_name,value)asselectbranch_name,sum(balance)fromaccountgroupbybranch_namewithbranch_total_avg(value)asselectavg(value)frombranch_totalselectbranch_namefrombranch_total,branch_total_avgwherebranch_total.value>=branch_total_avg.value
- 找出每个支行的储户数
视图: createviewvas<查询表达式>
- 更新:对余额超过10000美元的账户付6%的利息,其余账户付5%。
updateaccountsetbalance=casewhenbalance<=10000thenbalance∗1.05elsebalance∗1.06end - 事务:commit work, rollback work
- 完整性约束:not null, unique, check(<谓词>)
- 参照完整性:
createtableaccount(...foreignkey(branch_name)referencesbranchondeletecascadeonupdatecascade,...) - 断言:
createassertion<断言名>check<谓词>
- 每个支行的贷款金额总和必须少于该支行账户余额总和
- 每笔贷款的客户中至少有一人的账户余额不小于1000.00美元
createassertionsum_constraintcheck(notexists(select∗frombranchwhere(selectsum(amount)fromloanwhereloan.branch_name=branch.branch_name)>=(selectsum(balance)fromaccountwhereaccount.branch_name=branch.branch_name)))
createassertionbalance_constraintcheck(notexists(select∗fromloanwherenotexists(select∗fromborrower,depositor,accountwhereloan.loan_number=borrower.loan_numberandborrower.customer_name=depositor.customer_nameanddepositor.account_number=account.account_numberandaccount.balance>=1000)))
15. 授权: grant<授权列表>on<关系名或视图名>to<用户/角色列表>
16. SQL过程