python 数据库项目实战

============4.数据库项目实战==================
1.昨日巩固。


2.项目介绍：
	-- 创建银行数据库
	-- 创建数据表customer（客户）、bank（银行）、deposit（存款）
	-- 向表中插入测试数据
	-- 对数据进行增删改等操作



3.创建库：
	
	1.查看数据库是否启动。
	2.mysql -uroot -p  # 连接本地数据库
	3.\s # 查看数据服务的状态
	4.创建数据库并设置字符集:
		create database bank charset=utf8;
	5.查看创建的数据库：
		show databases;


4.创建客户表：
	create table customer(
		c_id char(6) primary key not null,
		name varchar(30) not null,
		location varchar(30),
		salary decimal(8,2)
		);

   创建银行表：
	create table banks(
			b_id char(5) primary key not null,
			bank_name varchar(30) not null
			);

   创建存款表：
    create table deposite(
        d_id int(10) auto_increment primary key not null,
        c_id char(6),
        b_id char(5),
        dep_date date,
        dep_type enum('1','3','5'),
        amount decimal(8,2),
        foreign key(c_id) references customer(c_id),
        foreign key(b_id) references banks(b_id)
    );



5.插入数据：
    
	insert customer values
		('101001','孙杨', '广州', '1234'),
		('101002','郭海', '南京', '3526'),
		('101003','卢江', '苏州', '6892'),
		('101004','郭慧', '济南', '3492'),
		('101006','同学1', '北京', '12000'),
		('101007','同学2', '北京', '13000'),
		('101008','同学3', '北京', '14000'),
		('101009','同学4', '北京', '13500'),
		('101010','同学5', '北京', '13000'),
		('101005','徐老师', '北京', '85000');

	insert banks values
		('B0001','工商银行'),
		('B0002','建设银行'),
		('B0003','中国银行'),
		('B0004','农业银行');

	insert deposite values
			(0,'101001','B0001','2011-04-05','3','42526'),
			(0,'101002','B0003','2012-07-15','5','66500'),
			(0,'101003','B0002','2010-11-24','1','42366'),
			(0,'101004','B0004','2008-03-31','1','62362'),
			(0,'101005','B0003','2002-02-07','3','56346'),
			(0,'101006','B0001','2004-09-23','3','353626'),
			(0,'101007','B0004','2003-12-14','5','36236'),
			(0,'101008','B0002','2007-04-21','5','26267'),
			(0,'101005','B0002','2011-02-11','1','435456'),
			(0,'101006','B0004','2012-05-13','1','234626'),
			(0,'101003','B0003','2001-01-24','5','26243'),
			(0,'101004','B0001','2009-08-23','3','45671');



6.完成一下功能：
	1）在bank中插入一条新记录B0005,交通银行
		insert into banks(b_id,bank_name) values('B0005','交通银行');


	2）查询出每名帐户的银行存款金额（排序）
		select * from deposite order by amount;


	3）给自己的帐户多存入10000元
		-- update deposite set amount=amount+10000 where c_id='101005';
        update deposite set amount=amount+10000
        where c_id in(select c_id from customer where name='徐老师');


	4）查看'徐老师'帐户的金额：
		select c.c_id,c.name, b.b_id,b.bank_name,d.amount, d.dep_type,d.dep_date 
        from deposite as d
        inner join customer as c on c.c_id =d.c_id
		inner join banks as b on b.b_id = d.b_id
        and c.name='徐老师';


    5）删除'同学1'的存款记录，并查看结果。

        delete from deposite where deposite.c_id 
        in(select customer.c_id from customer where customer.name='同学1');

        select c.c_id,c.name, b.b_id,b.bank_name,d.amount, d.dep_type,d.dep_date 
        from deposite as d
        inner join customer as c on c.c_id =d.c_id
        inner join banks as b on b.b_id = d.b_id
        and c.name='同学1';


	6) 查询郭海在建设银行的存款信息（显示信息：客户ID，客户姓名，银行标识，银行名称，存款日期，存款金额）

		方法(1)：内连接查询：
    		SELECT customer.c_id,customer.name,banks.b_id ,banks.bank_name,
    		       deposite.amount,deposite.dep_date
    		FROM deposite
    		INNER JOIN  customer on customer.c_id= deposite.c_id
    		INNER JOIN  banks on banks.b_id=deposite.b_id
    		and customer.name='郭海' and banks.bank_name='中国银行';
		 

		方法(2):表连接查询：
    		SELECT customer.c_id,customer.name,banks.b_id ,banks.bank_name,
    		       deposite.amount,deposite.dep_date
    		FROM deposite,banks,customer
    		WHERE  customer.c_id= deposite.c_id
    		and banks.b_id=deposite.b_id
    		and customer.name='郭海' and banks.bank_name='中国银行';