實(shí)驗(yàn)五 多表查詢

（一）實(shí)驗(yàn)?zāi)康?/span>

1、掌握數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)、數(shù)據(jù)庫(kù)和表的基本操作

2、掌握外鍵建立。

3、掌握操作關(guān)聯(lián)表。

4、掌握多表查詢方法，包括子查詢，連接查詢，條件查詢。

（二）實(shí)驗(yàn)要求

請(qǐng)大家將執(zhí)行結(jié)果以圖片形式完成實(shí)驗(yàn)報(bào)告，并在最后給出自己經(jīng)過(guò)實(shí)驗(yàn)得到的總結(jié)。

（三）實(shí)驗(yàn)學(xué)時(shí)

2學(xué)時(shí)

（四）實(shí)驗(yàn)內(nèi)容

已知，有一個(gè)客戶表customer,一個(gè)銀行表bank和一個(gè)存款信息表deposite，請(qǐng)按要求對(duì)這三個(gè)表進(jìn)行操作。表結(jié)構(gòu)及表中內(nèi)容如下所示。

表customer

屬性名稱	類型與長(zhǎng)度	中文含義	備注
c_id	char(6)	客戶標(biāo)識(shí)	主鍵
name	varchar(30)	客戶姓名	非空
location	varchar(30)	工作地點(diǎn)
salary	decimal(8,2)	工資

表bank

屬性名稱	類型與長(zhǎng)度	中文含義	備注
b_id	char(6)	銀行標(biāo)識(shí)	主鍵
bank_name	varchar(30)	銀行名稱	非空

表deposite

屬性名稱	類型與長(zhǎng)度	中文含義	備注
d_id	int(11)	存款流水號(hào)	主鍵，自增
c_id	char(6)	客戶標(biāo)識(shí)	外鍵，關(guān)聯(lián)customer的c_id
b_id	char(6)	銀行標(biāo)識(shí)	外鍵，關(guān)聯(lián)bank的b_id
dep_date	date	存入日期
amount	decimal(8,2)	存款金額

customer的數(shù)據(jù)如下：

c_id	name	location	salary
101001	孫萌	廣州	1234
101002	王琦	南京	3526
101003	趙越	北京	6892
101004	童彤	海南	3492

bank的數(shù)據(jù)如下

b_id	bank_name
B0001	工商銀行
B0002	建設(shè)銀行
B0003	中國(guó)銀行
B0004	農(nóng)業(yè)銀行

deposite表數(shù)據(jù)如下

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）啟動(dòng)MySQL服務(wù)器。

mysql -uroot -proot

（2）登錄MySQL服務(wù)器。

mysql -uroot -proot

（3）創(chuàng)建數(shù)據(jù)庫(kù)exp07。

create database exp07

（4）切換當(dāng)前數(shù)據(jù)庫(kù)。

use exp07

（5）按要求創(chuàng)建以上三張表格，插入相應(yīng)數(shù)據(jù)。

create table customer( c_id char(6) primary key comment '客戶標(biāo)識(shí)', name varchar(30) not null comment '客戶姓名', location varchar(30) comment '工作地點(diǎn)', salary decimal(8,2) comment '工資' ) create table bank( b_id char(6) primary key comment '銀行標(biāo)識(shí)', bank_name varchar(30) not null comment '銀行名稱' )

create table deposite( d_id int(11) primary key auto_increment comment '存款流水號(hào)', c_id char(6) comment '客戶標(biāo)識(shí)', b_id char(6) comment '銀行標(biāo)識(shí)', dep_date date comment '存入日期', amount decimal(8,2) comment '存款金額' ) #關(guān)聯(lián)customer的c_id alter table deposite add constraint waic_id foreign key(c_id) references customer(c_id) on delete restrict on update cascade #關(guān)聯(lián)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','建設(shè)銀行'), ('B0003','中國(guó)銀行'), ('B0004','農(nóng)業(yè)銀行') 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）將數(shù)據(jù)表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）將數(shù)據(jù)表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）將數(shù)據(jù)表deposite中王琦的銀行標(biāo)識(shí)改為建設(shè)銀行

update deposite set b_id=(select b_id from bank where bank_name='建設(shè)銀行') 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變?yōu)樵瓉?lái)的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 ='建設(shè)銀行')) #查詢更改情況 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）查詢?nèi)掌跒?011-04-05這一天進(jìn)行過(guò)存款的客戶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）查詢趙越在建設(shè)銀行的存款信息（顯示信息：客戶ID，客戶姓名，銀行標(biāo)識(shí)，銀行名稱，存款日期，存款金額）

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='建設(shè)銀行'

（12）查詢?cè)谵r(nóng)業(yè)銀行存款前五名的客戶存款信息（顯示信息：客戶姓名，銀行名稱，存款金額）

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='農(nóng)業(yè)銀行' 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條件查詢?nèi)N方式實(shí)現(xiàn)）

#表連接 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） 查詢工商銀行存款大于等于一萬(wàn)的客戶ID,姓名，銀行名稱，存款金額

（使用表連接、子查詢及where條件查詢?nèi)N方式實(shí)現(xiàn)）

#表連接 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

總結(jié)

以上是生活随笔為你收集整理的mysql实验5的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。