mysql实验5

实验五 多表查询

（一）实验目的

1、掌握数据库基础知识、数据库和表的基本操作

2、掌握外键建立。

3、掌握操作关联表。

4、掌握多表查询方法，包括子查询，连接查询，条件查询。

（二）实验要求

请大家将执行结果以图片形式完成实验报告，并在最后给出自己经过实验得到的总结。

（三）实验学时

2学时

（四）实验内容

已知，有一个客户表customer,一个银行表bank和一个存款信息表deposite，请按要求对这三个表进行操作。表结构及表中内容如下所示。

表customer

属性名称	类型与长度	中文含义	备注
c_id	char(6)	客户标识	主键
name	varchar(30)	客户姓名	非空
location	varchar(30)	工作地点
salary	decimal(8,2)	工资

表bank

属性名称	类型与长度	中文含义	备注
b_id	char(6)	银行标识	主键
bank_name	varchar(30)	银行名称	非空

表deposite

属性名称	类型与长度	中文含义	备注
d_id	int(11)	存款流水号	主键，自增
c_id	char(6)	客户标识	外键，关联customer的c_id
b_id	char(6)	银行标识	外键，关联bank的b_id
dep_date	date	存入日期
amount	decimal(8,2)	存款金额

customer的数据如下：

c_id	name	location	salary
101001	孙萌	广州	1234
101002	王琦	南京	3526
101003	赵越	北京	6892
101004	童彤	海南	3492

bank的数据如下

b_id	bank_name
B0001	工商银行
B0002	建设银行
B0003	中国银行
B0004	农业银行

deposite表数据如下

d_id	c_id	b_id	dep_date	amount
1	101001	B0001	2011-04-05	42526
2	101002	B0003	2012-12-02	66500
3	101003	B0002	2018-11-21	500000
4	101004	B0004	2020-03-02	12987
5	101001	B0002	2015-09-02	456
6	101002	B0004	2018-09-02	99000
7	101003	B0003	2016-06-22	35000
8	101004	B0001	2016-03-12	67890
9	101001	B0004	2019-11-02	400000
10	101002	B0001	2017-08-22	12000
11	101003	B0002	2015-09-02	12345.98
12	101004	B0003	2014-10-13	5678.88

（1）启动MySQL服务器。

mysql -uroot -proot

（2）登录MySQL服务器。

mysql -uroot -proot

（3）创建数据库exp07。

create database exp07

（4）切换当前数据库。

use exp07

（5）按要求创建以上三张表格，插入相应数据。

create table customer(
c_id char(6) primary key comment '客户标识',
name varchar(30) not null comment '客户姓名',
location varchar(30) comment '工作地点',
salary decimal(8,2) comment '工资'
)

create table bank(
b_id char(6) primary key comment '银行标识',
bank_name varchar(30) not null comment '银行名称'
)

create table deposite(
d_id int(11) primary key auto_increment comment '存款流水号',
c_id char(6) comment '客户标识',
b_id char(6) comment '银行标识',
dep_date date comment '存入日期',
amount decimal(8,2) comment '存款金额'
)

#关联customer的c_id
alter table deposite
add constraint waic_id foreign key(c_id) references customer(c_id)
on delete restrict on update cascade

#关联bank的b_id
alter table deposite
add constraint waib_id foreign key(b_id) references bank(b_id)
on delete restrict on update cascade

insert into customer (c_id,name,location,salary) values
(101001,'孙萌','广州',1234),
(101002,'王琦','南京',3526),
(101003,'赵越','北京',6892),
(101004,'童彤','海南',3492)

insert into bank (b_id,bank_name) values
('B0001','工商银行'),
('B0002','建设银行'),
('B0003','中国银行'),
('B0004','农业银行')

insert into deposite (c_id,b_id,dep_date,amount) values
(101001,'B0001','2011-04-05',42526),
(101002,'B0003','2012-12-02',66500),
(101003,'B0002','2018-11-21',500000),
(101004,'B0004','2020-03-02',12987),
(101001,'B0002','2015-09-02',456),
(101002,'B0004','2018-09-02',99000),
(101003,'B0003','2016-06-22',35000),
(101004,'B0001','2016-03-12',67890),
(101001,'B0004','2019-11-02',400000),
(101002,'B0001','2017-08-22',12000),
(101003,'B0002','2015-09-02',12345.98),
(101004,'B0003','2014-10-13',5678.88)

 （6）将数据表deposite中孙萌的存款金额加10000

update deposite as d join
(select c_id from customer where name='孙萌') as c
on c.c_id = d.c_id
set d.amount = d.amount+10000

#查询更改情况
select c.name,d.amount
from customer c join deposite d
on d.c_id = (select c_id from customer where name='孙萌')&&c.c_id=d.c_id

（7）将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加100000

update deposite
set amount=amount+100000
where b_id=(select b_id from bank where bank_name='工商银行')&&dep_date='2011-04-05'

#查询更改情况
select dep_date,amount from deposite
where b_id =(select b_id from bank where bank_name='工商银行')&&dep_date='2011-04-05'

（8）将数据表deposite中王琦的银行标识改为建设银行

update deposite
set b_id=(select b_id from bank where bank_name='建设银行')
where c_id=(select c_id from customer where name='王琦')

#查询更改情况
select c.name,b.bank_name,d.b_id,d.c_id
from customer c
join bank b
join deposite d
on c.name='王琦'&&b.b_id=d.b_id&&d.c_id=c.c_id

（9）将salary低于5000的建行客户的salary变为原来的2倍.

update customer
set salary=salary*2
where salary<5000
&& c_id in (select c_id from deposite where b_id = (select b_id from bank where bank_name ='建设银行'))

#查询更改情况
select c.name,c.salary,b.bank_name
from customer c
join bank b
join deposite d
where c.salary<5000 && b.b_id=d.b_id && c.c_id=d.c_id

（10）查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额

select c.c_id,c.name,b.bank_name,d.amount
from customer c
join bank b
join deposite d
where d.dep_date='2011-04-05'&&b.b_id=d.b_id&&c.c_id=d.c_id

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

select c.c_id,c.name,b.b_id,b.bank_name,d.dep_date,d.amount
from customer c
join bank b
join deposite d
where c.name='赵越'&&d.c_id=c.c_id&&b.b_id=d.b_id&&b.bank_name='建设银行'

（12）查询在农业银行存款前五名的客户存款信息（显示信息：客户姓名，银行名称，存款金额）

from customer c
join bank b
join deposite d
on c.c_id=d.c_id&&b.b_id=d.b_id&&b.bank_name='农业银行' order by d.amount desc limit 5

（13）查询姓“童”的客户的存款信息（显示信息：客户姓名，银行名称，存款金额）

select c.name,b.bank_name,d.amount
from customer c
join bank b
join deposite d
on c.c_id=d.c_id&&b.b_id=d.b_id&&c.name like '童%'

（14）查询孙萌的存款信息（显示信息：客户ID，客户姓名，银行名称，存款金额）

（使用表连接、子查询及where条件查询三种方式实现）

#表连接
select c.c_id,c.name,b.bank_name,d.amount
from customer c
join bank b
join deposite d
on c.c_id=d.c_id&&b.b_id=d.b_id&&c.name='孙萌'

#子查询
select c.c_id,c.name,b.bank_name,d.amount
from 
(select c_id,name from customer where name='孙萌') as c,
(select b_id,amount from deposite where c_id=(select c_id from customer where name='孙萌')) as d,
(select bank_name,b_id from bank where b_id in (select b_id from deposite where c_id=(select c_id from customer where name='孙萌'))) as b
where b.b_id=d.b_id

#where条件查询
select c.c_id,c.name,b.bank_name,d.amount from 
customer as c,
bank as b,
deposite as d
where c.name='孙萌'&&d.c_id=c.c_id&&b.b_id=d.b_id;

（15） 查询工商银行存款大于等于一万的客户ID,姓名，银行名称，存款金额

（使用表连接、子查询及where条件查询三种方式实现）

#表连接
select c.c_id,c.name,b.bank_name,d.amount
from customer c
join bank b
join deposite d
on c.c_id=d.c_id && b.b_id=d.b_id && b.bank_name='工商银行' && d.amount>10000

#子查询
select c.c_id,c.name,b.bank_name,d.amount
from(select c_id,name from customer where c_id in (select c_id from deposite where b_id =(select b_id from bank where bank_name='工商银行'))) as c,
(select b_id,c_id,amount from deposite where b_id=(select b_id from bank where bank_name='工商银行')) as d,
(select b_id,bank_name from bank where bank_name='工商银行') as b
where c.c_id=d.c_id

#where条件查询
select c.c_id,c.name,d.amount,b.bank_name
from customer c,bank b,deposite d
where d.amount>=10000 && b.bank_name='工商银行' && d.c_id=c.c_id && b.b_id=d.b_id