SQL语句复习

来源：互联网发布：电子采购软件编辑：程序博客网时间：2024/06/07 02:26

基本域类型：char(n),varchar(n),int,smallint,numeric(p,d),real,doubleprecision,float(n)
创建表：
$c r (e a t e t a b l e c u s t o m e r c u s t o m e r_n a m e c u s t o m e r_s t r e e t c u s t o m e r_c i t y p r i m a r y k e y c h a r (20), c h a r (30), c h a r (30), (c u s t o m e r_n a m e))$
$c r (e a t e t a b l e b r a n c h b r a n c h_n a m e b r a n c h_c i t y a s s e t s_c i t y p r i m a r y k e y c h a r (15), c h a r (30), n u m e r i c (16, 2), (b r a n c h_n a m e))$
$c r (e a t e t a b l e a c c o u n t a c c o u n t_n u m b e r b r a n c h_n a m e b a l a n c e_c i t y p r i m a r y k e y c h a r (10), c h a r (15), n u m e r i c (12, 2), (a c c o u n t_n u m b e r))$
$c r (e a t e t a b l e d e p o s i t o r c u s t o m e r_n a m e a c c o u n t_n u m b e r p r i m a r y k e y c h a r (20), c h a r (10), (c u s t o m e r_n a m e, a c c o u n t n u m b e r))$
添加元祖：insertintoaccountvalues(′A−9732′,′Perrvridge′,1200)
- 给每个在Perryridge支行贷款的客户赠送一个200美元的新存款账户
  $i n s e r t i n t o a c c o u n t s e l e c t l o a n_n u m b e r, b r a n c h_n a m e, 200 f r o m l o a n w h e r e b r a n c h_n a m e =' P e r r y r i d g e'$
删除元祖：deletefromaccount
- 删除余额低于银行平均余额的账户记录
  $d e l e t e f r o m a c c o u n t w h e r e b a l a n c e < (s e l e c t a v g (b a l a n c e) f r o m a c c o u n t)$
删除表：droptabler
修改表：altertableraddAD 或 altertablerdropA
数据库查询：
$w i t h . . . a s s e l e c t . . . (s e l e c t d i s t i n c t . . ., . . ., a v g / m i n / m a x / s u m / c o u n t (. . .) a s . . ., . . . f r o m . . . a s . . ., . . . w h e r e . . . a n d . . . o r l i k e . . . o r n o t . . . o r i s n u l l . . a n d (n o t) i n (s e l e c t . . .) a n d . . . > s o m e / a n y / a l l (s e l e c t . . .) a n d e x i s t s (s e l e c t . . .) o r d e r b y g r o u p b y (. . .) h a v i n g a v g (. . .)) u n i o n (a l l), i n t e r s e c t (a l l), e x c e p t (s e l e c t . . .)$
一些例子：
- 找出每个支行的储户数
  $s e l e c t b r a n c h_n a m e, c o u n t (d i s t i n c t (c u s t o m e r_n a m e)) f r o m d e p o s i t o r, a c c o u n t w h e r e d e p o s i t i o n . a c c o u n t_n u m b e r = a c c o u n t . a c c o u n t_n u m b e r g r o u p b y b r a n c h_n a m e$
- 找出平均账户余额大于1200的支行
  $s e l e c t b r a n c h_n a m e, a v g (b a l a n c e) f r o m a c c o u n t g r o u p b y b r a n c h n a m e h a v i n g a v g (b a l a n c e) > 1200$
- 找出住在Harrison且在银行中至少有三个账户的客户的平均余额
  $s e l e c t d e p o s i t o r . c u s t o m e r_n a m e, a v g (b a l a n c e) f r o m d e p o s i t o r, a c c o u n t, c u s t o m e r w h e r e d e p o s i t o r . a c c o u n t_n u m b e r = a c c o u n t . a c c o u n t_n u m b e r a n d d e p o s i t o r . c u s t o m e r_n a m e = c u s t o m e r . c u s t o m e r_n a m e a n d c u s t o m e r_c i t y =' H a r r i s o n' g r o u p b y d e p o s i t o r . c u s t o m e r_n a m e h a v i n g c o u n t (d i s t i n c t (d e p o s i t o r . a c c o u n t n u m b e r) > = 3)$
- 找出在Perryridge支行同时有账户和贷款的客户
  $s e l e c t d i s t i n c t c u s t o m e r_n a m e f r o m b o r r o w e r, l o a d w h e r e b o r r o w e r . l o a d_n u m b e r = l o a d . l o a d_n u m b e r a n d b r a n c h_n a m e =' P e r r y r i d g e' a n d (b r a n c h_n a m e, c u s t o m e r_n a m e) i n (s e l e c t b r a n c h_n a m e, c u s t o m e r_n a m e f r o m d e p o s i t o r, a c c o u n t w h e r e d e p o s i t o r . a c c o u n t_n u m b e r = a c c o u n t . a c c o u n t_n u m b e r)$
- 找出平均余额大于等于所有平均余额的支行
  $s e l e c t b r a n c h_n a m e f r o m a c c o u n t g r o u p b y b r a n c h_n a m e h a v i n g a v g (b a l a n c e) > = a l l (s e l e c t a v g (b a l a n c e) f r o m a c c o u n t g r o u p n y b r a n c h_n a m e)$
- 对每位客户，判断他拥有账户的所有支行的集合中是否包含坐落在Brooklyn的所有支行的集合。这里“关系A包含B”写成 notexists(BexceptA)。
  $s e l e c t d i s t i n c t S . c u s t o m e r_n a m e f r o m d e p o s i t o r a s S w h e r e n o t e x i s t s ((s e l e c t b r a n c h_n a m e f r o m b r a n c h w h e r e b r a n c h_c i t y =' B r o o k l y n') e x c e p t (s e l e c t R . b r a n c h_n a m e f r o m d e p o s i t o r a s T, a c c o u n t a s R w h e r e T . a c c o u n t_n u m b e r = R . a c c o u n t_n u m b e r a n d S . c u s t o m e r_n a m e = T . c u s t o m e r_n a m e$
- 找出所有在Perryridge支行中只有一个账户的客户
  $s e l e c t T . c u s t o m e r_n a m e f r o m d e p o s i t o r a s T w h e r e u n i q u e (s e l e c t R . c u s t o m e r_n a m e f r o m a c c o u n t, d e p o s i t o r a s R w h e r e T . c u s t o m e r_n a m e = R . c u s t o m e r_n a m e a n d R . a c c o u n t_n u m b e r = a c c o u n t . a c c o u n t_n u m b e r a n d a c c o u n t . b r a n c h_n a m e =' P e r r y r i d g e')$
- 查询在所有支行中总余额最多的支行
  $s e l e c t m a x (t o t_b a l a n c e) f r o m (s e l e c t b r a n c h_n a m e, s u m (b a l a n c e) f r o m a c c o u n t g r o u p b y b r a n c h_n a m e) a s b r a n c h_t o t a l (b r a n c h_n a m e, t o t_b a l a n c e)$
- 查询所有存款总额大于全部支行平均存款总额的支行
  $w i t h b r a n c h_t o t a l (b r a n c h_n a m e, v a l u e) a s s e l e c t b r a n c h_n a m e, s u m (b a l a n c e) f r o m a c c o u n t g r o u p b y b r a n c h_n a m e w i t h b r a n c h_t o t a l_a v g (v a l u e) a s s e l e c t a v g (v a l u e) f r o m b r a n c h_t o t a l s e l e c t b r a n c h_n a m e f r o m b r a n c h_t o t a l, b r a n c h_t o t a l_a v g w h e r e b r a n c h_t o t a l . v a l u e > = b r a n c h_t o t a l_a v g . v a l u e$
视图：createviewvas<查询表达式>
更新：对余额超过10000美元的账户付6%的利息，其余账户付5%。
$u p d a t e a c c o u n t s e t b a l a n c e = c a s e w h e n b a l a n c e < = 10000 t h e n b a l a n c e * 1.05 e l s e b a l a n c e * 1.06 e n d$
事务：commit work, rollback work
完整性约束：not null, unique, check(<谓词>)
参照完整性：
$c r e a t e t a b l e a c c o u n t (. . . f o r e i g n k e y (b r a n c h_n a m e) r e f e r e n c e s b r a n c h o n d e l e t e c a s c a d e o n u p d a t e c a s c a d e, . . .)$
断言：createassertion<断言名>check<谓词>
- 每个支行的贷款金额总和必须少于该支行账户余额总和
- 每笔贷款的客户中至少有一人的账户余额不小于1000.00美元
  $c r e a t e a s s e r t i o n s u m_c o n s t r a i n t c h e c k (n o t e x i s t s (s e l e c t * f r o m b r a n c h w h e r e (s e l e c t s u m (a m o u n t) f r o m l o a n w h e r e l o a n . b r a n c h_n a m e = b r a n c h . b r a n c h_n a m e) > = (s e l e c t s u m (b a l a n c e) f r o m a c c o u n t w h e r e a c c o u n t . b r a n c h_n a m e = b r a n c h . b r a n c h_n a m e)))$

c r e a t e a s s e r t i o n b a l a n c e_c o n s t r a i n t c h e c k (n o t e x i s t s (s e l e c t * f r o m l o a n w h e r e n o t e x i s t s (s e l e c t * f r o m b o r r o w e r, d e p o s i t o r, a c c o u n t w h e r e l o a n . l o a n_n u m b e r = b o r r o w e r . l o a n_n u m b e r a n d b o r r o w e r . c u s t o m e r_n a m e = d e p o s i t o r . c u s t o m e r_n a m e a n d d e p o s i t o r . a c c o u n t_n u m b e r = a c c o u n t . a c c o u n t_n u m b e r a n d a c c o u n t . b a l a n c e > = 1000)))

15. 授权：

grant<授权列表>on<关系名或视图名>to<用户/角色列表>
16. SQL过程

0 0