实验四：多表查询

（一）实验目的

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

2、掌握外键建立。

3、掌握操作关联表。

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

（二）实验内容

表customer

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

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

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

表bank

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

 CREATE TABLE bank (
         b_id char(6) PRIMARY KEY  COMMENT '银行标识',
         bank_name varchar(30)  NOT NULL  COMMENT '银行名称'
);

INSERT INTO bank (b_id,bank_name) VALUES
        ('B0001','工商银行'), ('B0002','建设银行'), ('B0003','中国银行'),('B0004','农业银行');

表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)	存款金额

CREATE TABLE deposite (
         d_id int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '存款流水号',
         c_id char(6) NOT NULL DEFAULT 0 COMMENT '客户标识',
         b_id char(6) NOT NULL DEFAULT '' COMMENT '银行标识',
         dep_date     date COMMENT '存入日期',
         amount decimal(8,2) COMMENT '存款金额',
        constraint cs_id foreign key (c_id) references customer(c_id),
        constraint bk_id foreign key (b_id) references bank(b_id)
);

INSERT INTO deposite (d_id,c_id,b_id,dep_date,amount) VALUES
        (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）将数据表deposite中孙萌的存款金额加10000

 update  deposite set amount =amount+10000
 where c_id in(select c_id from customer where name='孙萌');

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

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

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

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

select bank_name from bank b
join  deposite d on d.b_id =b.b_id
join customer c on c.c_id =d.c_id 
where c.name ='王琦';

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

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

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

 方法一：内连接查询

select customer.c_id,customer.name,bank.bank_name,deposite.amount
from deposite
join  customer on customer.c_id= deposite.c_id
join  bank on bank.b_id=deposite.b_id
and deposite.dep_date='2011-04-05';

方法(2):where查询：
SELECT customer.name,customer.c_id,bank.bank_name,deposite.amount
        from deposite,customer,bank,
WHERE deposite.dep_date='2011-04-05'
and customer.c_id= deposite.c_id
and bank.b_id= deposite.b_id ;

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

方法(1)：内连接查询：
select customer.c_id,customer.name,bank.b_id ,bank.bank_name,deposite.amount,deposite.dep_date
from deposite
join customer on customer.c_id= deposite.c_id
join bank on bank.b_id=deposite.b_id
and customer.name='赵越' and bank.bank_name='建设银行';

方法(2):where查询：
SELECT customer.c_id,customer.name,bank.b_id ,bank.bank_name,deposite.amount,deposite.dep_date
FROM deposite,bank,customer
WHERE  customer.c_id= deposite.c_id
and bank.b_id=deposite.b_id
and customer.name='赵越' and bank.bank_name='建设银行';

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

方法(1)：内连接查询：
select customer.name,bank.bank_name,deposite.amount
from deposite
join customer on customer.c_id= deposite.c_id
join bank on bank.b_id=deposite.b_id
where bank_name='农业银行' order by amount desc limit 5;

方法(2)：where查询：
select customer.name,bank.bank_name,deposite.amount
from deposite,bank,customer
where customer.c_id= deposite.c_id
and bank.b_id=deposite.b_id
and bank_name='农业银行' order by amount desc limit 5;

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

方法(1)：内连接查询：
select customer.name,bank.bank_name,deposite.amount
from deposite
join customer on customer.c_id= deposite.c_id
join bank on bank.b_id=deposite.b_id  where name like '童%';

方法(2)：where查询：
SELECT customer.name,bank.bank_name,deposite.amount
FROM deposite,bank,customer
WHERE customer.c_id= deposite.c_id
AND bank.b_id=deposite.b_id  AND name LIKE '童%';

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

方法(1):内连接查询
select customer.c_id,customer.name,bank.bank_name,deposite.amount
from deposite
join customer on customer.c_id= deposite.c_id
join bank on bank.b_id=deposite.b_id
and customer.name='孙萌';

方法(2)：子查询
select c_id, name, bank_name, amount
from (select c.c_id,c.name,b.bank_name,d.amount
from deposite d
join customer c on c.c_id= d.c_id
join bank b on b.b_id=d.b_id) as w
where w.name='孙萌';

方法(3):where查询
select customer.name,customer.c_id,bank.bank_name,deposite.amount
from customer,bank,deposite
where  customer.c_id= deposite.c_id
and bank.b_id= deposite.b_id and customer.name='孙萌';

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

方法(1)：子查询：
select c_id, name, bank_name, amount
from (select c.c_id,c.name,b.bank_name,d.amount
from deposite d
join customer c on c.c_id= d.c_id
join bank b on b.b_id=d.b_id) as w
where w.amount>=10000 and bank_name='工商银行';

方法(2)：where查询
select customer.name,customer.c_id,bank.bank_name,deposite.amount
from deposite,bank,customer
where customer.c_id= deposite.c_id
and bank.b_id=deposite.b_id
and amount>=10000 and bank_name='工商银行';

方法(3)：内连接查询
select customer.name,customer.c_id,bank.bank_name,deposite.amount
from deposite
join customer on customer.c_id= deposite.c_id
join bank on bank.b_id=deposite.b_id
where amount>=10000 and bank_name='工商银行';