/*
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)
*/