数据库项目实战

63 阅读 0 评论 42 点赞

我是靠谱客的博主幽默小海豚，最近开发中收集的这篇文章主要介绍数据库项目实战，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

2.创建库：

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

3.创建表：


创建客户表：
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)
);

4.插入数据：


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');

5.实战练习


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='中国银行';