数据库实验代码备份

/*
use TestData
create table customer
(customer_name char(20),
 customer_street char(30),
 customer_city char(30),
 primary key(customer_name))
 
 create table branch
 (branch_name char(15),
  branch_city char(30),
  assets numeric(16,2),
  primary key(branch_name))
 
  create table account
  (account_number char(10),
   branch_name char(15),
   balance numeric(12,2),
   primary key(account_number))
  
   create table depositor
   (customer_name char(20),
    account_number char(10),
    primary key(customer_name,account_number))
  */ 
 
insert into account
 values('B-9732','Perrvridge',1200)

select * from account

5.20

/*
创建数据集
create database TestData
*/
use TestData
/*
创建表
create table customer
(customer_name char(20),
 customer_street char(30),
 customer_city char(30),
 primary key(customer_name))
 
 create table branch
 (branch_name char(15),
  branch_city char(30),
  assets numeric(16,2),
  primary key(branch_name))
  
  create table account
  (account_number char(10),
   branch_name char(15),
   balance numeric(12,2),
   primary key(account_number))
   
   create table depositor
   (customer_name char(20),
    account_number char(10),
    primary key(customer_name,account_number))
    
*/


/*表的插入元素*/
/*
insert into account
 /*values('B-9732','Perrvridge',1200)*/
 /*values('A-3456','Pein',2304);*/
 /*values('B-3234','Martin',3423);*/
 /*values('D-2334','Luxi',343);*/
 /*values('V-3444','Didala',3421);*/
 /*values('K-4225','Kakaxi',342);*/
 
select * from account
*/


/*
表的删除元素
delete from account
where branch_name = 'Perryridge'
delete from loan
where amount between 1300 and 1500
delete from account
where branch_name in (select branch_name
                      from branch_city  = 'Brooklyn')
delete from account
where balance < (select avg(balance) from account)


*/




/*
在不修改整个元组的情况下改变其部分属性的值
update account
set balance = balance*1.05
/*
这里可以加上一句(按条件更改)
where balance >= 1000
*/
where balance > (select avg(balance) from account)
*/


/*
根据条件选择不同的更改策略
update account
set balance = case
when balance <= 10000 then balance * 1.05
else balance * 1.06
 end
*/






/*
视图的更新
比如一个职员需要查看loan关系中除loan_amount以外的所有贷款数据。假设loan_branch是给
该职员的视图，我们这样定义这个视图、
create view loan_branch as
select loan_number,branch_name
from loan


以下的元祖插入不好
insert into loan_branch
values('L-37','Perryridge')
解决方案
1.拒绝插入
2.向loan关系插入元祖('L-37','Perryridge',null)
*/




/*
集合的Union运算
(select customer_name
from depositor)
union
(select customer_name
from borrower)
/*如果要保留所有重复 */
(select customer_name
from depositor)
union all
(select customer_name
from borrower)
*/


/*
集合的intersect运算
(select customer_name
from depositor)
intersect
(select customer_name
from borrower)
/*如果想保留所有重复，我们必须用intersect all*/
(select customer_name
from depositor)
intersect all
(select customer_name
from borrower)
*/


/*
集合的except运算
(select distinct customer_name
from depositor)
except
(select customer_name 
from borrower)
/*如果想保留所有重复，我们必须用except all*/
(select distinct customer_name
from depositor)
except all
(select customer_name 
from borrower)
*/


/*
聚集函数
select avg(balance)
from account
where branch_name = 'Perryridge'
select branch_name,avg(balance)
from account
group by branch_name




select branch_name,count(distinct,customeer_name)
from depositor,account
where depositor,account,number = account.account.number
group by branch_name
*/


/*
空值
select loan_number
from loan
where amount is null


select sum(amount)
from loan
*/


/*
嵌套子查询
(select customer_name
from depositor)
select distinct customer_name
from borrower
where customer_name in(select customer_name
from depositor)
*/