创建数据库
create database accountInfo/*创建账户信息数据库*/
创建数据表
1 /*定义主码、外码、和人数、余额的取值范围。*/ 2 /*创建储蓄所表*/ 3 create table bank( 4 bank_ID int primary key identity(10001,1),/*从10001开始，每次增加1*/ 5 bank_name nvarchar(20), /*储蓄所名称，*/ 6 bank_address nvarchar(40), 7 bank_peopleNum int check(bank_peopleNum between 1 and 30), 8 bank_city nvarchar(10) 9 )10 11 /*创建账户表*/12 /*帐户（编号，姓名，余额，建立日期，储蓄所编号）*/13 create table account(14 account_id int primary key identity(1,1),15 account_name nvarchar(15),16 account_balance int check(account_balance>= 0 ),17 account_found_date char(8),18 bank_ID int foreign key references bank(bank_id)19 )20 21 /*创建借贷表*/22 /*借贷（帐户，借贷类型，金额，日期）*/23 create table borrow(24 borrow_id int primary key identity(1,1),25 account_ID int foreign key references account(account_ID),26 type_borrow nvarchar(10),27 money_sum int check(money_sum>=0),28 date_borrow char(8)29 )
插入数据
1 --插入bank数据 2 insert into bank values('长春南湖路','朝阳区',10,'长春市') 3 insert into bank values('西安大路','朝阳区',13,'长春市') 4 insert into bank values('幸福二路','南关区',11,'长春市') 5 insert into bank values('幸福三路','南关区',12,'长春市') 6 insert into bank values('桃林街','城关区',18,'灵宝市') 7 8 --插入account数据 9 insert into account values('赵大',10000,20170510,10001)10 insert into account values('钱二',5000,20160120,10001)11 insert into account values('张三',50000,20161120,10001)12 insert into account values('李四',50000,20161120,10005)13 insert into account values('孙五',500000,20161110,10005)14 15 --插入borrow数据16 insert into borrow values(1,'短期',1000,20170516)17 insert into borrow values(2,'长期',3000,20170416)18 insert into borrow values(3,'长期',200000,20170510)
连接查询
--内连接select account_name,account_balance,bank_namefrom account , bankwhere account.bank_ID=bank.bank_id and bank_name='长春南湖路'--左外连接select bank.bank_ID,count(account_id)'账户个数',sum(account_balance)'余额总数'from bank left outer join account on(bank.bank_ID=account.bank_ID)group by bank.bank_ID
嵌套查询
select account_name,account_balancefrom accountwhere bank_ID in(select bank_ID from bank where bank_name='长春南湖路')
select account_namefrom accountwhere account_balance>(select MAX(account_balance)from account where account_name='长春南湖路')
select*from account as A,Bank as Bwhere A.bank_ID=B.bank_ID and B.bank_city in(select bank_cityfrom bankgroup by bank_cityhaving count(bank_ID)=(select min(Nm)from(select bank_city,count(bank_ID)from bankgroup by bank_city)as _bank(Cy,Nm)));
select account_id from accountwhere account_id not in (select id from( select account_ID from borrowgroup by account_ID ) as v(id)--,Nm))
组合查询
select account_idfrom account except (select account_ID from borrow )
创建及使用视图
1 create view view1 as 2 select account.bank_ID, 3 sum(account_balance)'balanceSum' 4 from bank,account 5 where bank.bank_ID=account.bank_ID 6 group by account.bank_ID 7 8 select v1.bank_ID vid 9 from(select max(v.balanceSum)as balanceSum from view1 v)10 su,view1 v111 where v1.balanceSum=su.balanceSum
存储过程
--根据上面基本表的信息定义一个存储过程，完成下面功能：--入口参数：储蓄所编号--1 显示储蓄所信息--2 如果没有帐户，删除该储蓄所记录--3 如果帐户余额总额低于100万元，开一个新帐户alter PROCEDURE pr_bank(@bank_id int)asBEGIN select *from bank where bank_ID = @bank_id if (select count(*) from account where bank_id=@bank_id)=0 delete from bank where bank_ID=@bank_id if (select sum(account_balance) from account where bank_id=@bank_id group by bank_id)<1000000 insert into account(bank_id) values(@bank_id)ENDexec pr_bank 10005
作者：耑新新，发布于
转载请注明出处，欢迎邮件交流：