Mysql - 第二次上机

Mysql第二次上机

题目

1、 基于第一次上机创建的银行数据库，创建一个视图branch_detail，能够显示所有支行的存款客户数量、存款总额、贷款客户数量、贷款总额。
2、 在account的account_number属性上建立索引，并在account表里插入大量元组，比较有无索引在查询速度上的区别。
3、 创建角色银行经理branch_manager，银行经理具有插入、删除和更新branch表的权限。
4、 自由练习第四章中级SQL的其他特性。

…

1. 视图

create view branch_detail as
select * from
	(select branch_name as deposite_branch,
		count(distinct(customer_name)) as depositor_amount,
		sum(balance) as deposite_balance
		from account337 natural join depositor337 natural join branch337
	group by branch_name
	) as depositor
	left join
	(select branch_name as borrow_branch,
		count(distinct(customer_name)) as borrower_amount,
		sum(amount) as borrow_balance
	from borrower337 natural join loan337 natural join branch337
	group by branch_name
	) as borrower
	on depositor.deposite_branch = borrower.borrow_branch
	union
	select * from
	(select branch_name as deposite_branch,
		count(distinct(customer_name)) as depositor_amount,
		sum(balance) as deposite_balance
	from account337 natural join depositor337 natural join branch337
	group by branch_name
	) as depositor
	right join
	(select branch_name as borrow_branch,
		count(distinct(customer_name)) as borrower_amount,
		sum(amount) as borrow_balance
	from borrower337 natural join loan337 natural join branch337
	group by branch_name
	) as borrower
	on depositor.deposite_branch = borrower.borrow_branch;

2. 效果
   

3. 索引

   1. 生成100W条数据

   #include <iostream>
   #include <ctime>
   #include <fstream>
   using namespace std;
   
   string bank[] = {"Brighton","Downtown","Mianus","North Town","Perryridge","Pownal","Redwood","Round Hill"};
   
   int main()
   {
   	std::srand(std::time(0));
   	ofstream f_out("data.txt");
   
   	for(int i = 0;i < 1000000;i ++) {
   		f_out << "A-" << i+400 << ",";
   		int variable = std::rand()/((RAND_MAX + 1u)/7);
   		f_out << bank[variable] << ",";
   		variable = (std::rand()/((RAND_MAX + 1u)/20000))+100;
   		f_out << variable << endl;
   	}
   	
   	return 0;
   }
   

   2. 插入数据，建立索引进行比较
      
      
      我建立在了balance上。尝试建立在account_number上，但查询下来并无差别( primary key(account_number) )

4. 下面的权限懒得写了 …

view部分应该还能再简单一点，目前还没有想到简写方法。
因为mysql中没有full join，所以使用left join + union + right join实现full join。