实验五 多表查询
(一)实验目的
1、掌握数据库基础知识、数据库和表的基本操作
2、掌握外键建立。
3、掌握操作关联表。
4、掌握多表查询方法,包括子查询,连接查询,条件查询。
(二)实验要求
请大家将执行结果以图片形式完成实验报告,并在最后给出自己经过实验得到的总结。
(三)实验学时
2学时
(四)实验内容
(1)启动MySQL服务器。
(2)登录MySQL服务器。
(3)创建数据库exp07。
(4)切换当前数据库。
mysql> create database exp07;
Query OK, 1 row affected (0.00 sec)
mysql> use exp07;
Database changed
- 按要求创建以上三张表格,插入相应数据。
①创建customer表,并插入数据
mysql> 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'工资'
-> )character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into customer(c_id,name,location,salary)
-> values('101001','孙萌','广州',1234);
Query OK, 1 row affected (0.01 sec)
mysql> insert into customer(c_id,name,location,salary)
-> values('101002','王琦','南京',3526);
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer(c_id,name,location,salary)
-> values('101003','赵越','北京',6892);
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer(c_id,name,location,salary)
-> values('101004','童彤','海南',3492);
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;
+--------+------+----------+---------+
| c_id | name | location | salary |
+--------+------+----------+---------+
| 101001 | 孙萌 | 广州 | 1234.00 |
| 101002 | 王琦 | 南京 | 3526.00 |
| 101003 | 赵越 | 北京 | 6892.00 |
| 101004 | 童彤 | 海南 | 3492.00 |
+--------+------+----------+---------+
4 rows in set (0.00 sec)
②创建bank表,并插入数据
mysql> #创建bank表,并插入数据
mysql> create table bank(
-> b_id char(6) primary key comment'银行标识',
-> bank_name varchar(30) not null comment'银行名称'
-> )character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into bank(b_id,bank_name)
-> values('B0001','工商银行');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bank(b_id,bank_name)
-> values('B0002','建设银行');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bank(b_id,bank_name)
-> values('B0003','中国银行');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bank(b_id,bank_name)
-> values('B0004','农业银行');
Query OK, 1 row affected (0.00 sec)
mysql> select * from bank;
+-------+-----------+
| b_id | bank_name |
+-------+-----------+
| B0001 | 工商银行 |
| B0002 | 建设银行 |
| B0003 | 中国银行 |
| B0004 | 农业银行 |
+-------+-----------+
4 rows in set (0.00 sec)
③创建deposite表,并插入数据
mysql> #创建deposide表
mysql> create table deposite(
-> d_id int unsigned primary key auto_increment comment'存款流水账',
-> c_id char(6) comment'客户标识',
-> b_id char(6) comment'银行标识',
-> dep_date date comment'存入日期',
-> amount decimal(8,2) comment'存款金额'
-> )character set utf8 collate utf8_bin;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (1,'101001','B0001','2011-04-05',42526);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (2,'101002','B0003','2012-12-02',66500);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (3,'101003','B0002','2018-11-21',500000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (4,'101004','B0004','2020-03-02',12987);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (5,'101001','B0002','2015-09-02',456);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (6,'101002','B0004','2018-09-02',99000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (7,'101003','B0003','2016-06-22',35000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (8,'101004','B0001','2016-03-12',67890);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (9,'101001','B0004','2019-11-02',400000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (10,'101002','B0001','2017-08-22',12000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (11,'101003','B0002','2015-09-02',12345.98);
Query OK, 1 row affected (0.00 sec)
mysql> insert into deposite (d_id,c_id,b_id,dep_date,amount)
-> values (12,'101004','B0003','2014-10-13',5678.88);
Query OK, 1 row affected (0.00 sec)
mysql> #查看创建情况
mysql> select * from deposite;
+------+--------+-------+------------+-----------+
| d_id | c_id | b_id | dep_date | amount |
+------+--------+-------+------------+-----------+
| 1 | 101001 | B0001 | 2011-04-05 | 42526.00 |
| 2 | 101002 | B0003 | 2012-12-02 | 66500.00 |
| 3 | 101003 | B0002 | 2018-11-21 | 500000.00 |
| 4 | 101004 | B0004 | 2020-03-02 | 12987.00 |
| 5 | 101001 | B0002 | 2015-09-02 | 456.00 |
| 6 | 101002 | B0004 | 2018-09-02 | 99000.00 |
| 7 | 101003 | B0003 | 2016-06-22 | 35000.00 |
| 8 | 101004 | B0001 | 2016-03-12 | 67890.00 |
| 9 | 101001 | B0004 | 2019-11-02 | 400000.00 |
| 10 | 101002 | B0001 | 2017-08-22 | 12000.00 |
| 11 | 101003 | B0002 | 2015-09-02 | 12345.98 |
| 12 | 101004 | B0003 | 2014-10-13 | 5678.88 |
+------+--------+-------+------------+-----------+
12 rows in set (0.00 sec)
- 将数据表deposite中孙萌的存款金额加10000
①方法一:内联结
mysql> update deposite as d inner join
-> (select c_id from customer where name='孙萌') as c
-> on c.c_id=d.c_id
-> set d.amount =d.amount+10000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
建议执行完代码后,讲+10000改为-10000,把数据恢复原样。下面也是一样
②方法二:左联结
mysql> update deposite as d left join
-> (select c_id from customer where name='孙萌') as c
-> on c.c_id=d.c_id
-> set d.amount =d.amount+10000;
Query OK, 12 rows affected (0.00 sec)
Rows matched: 12 Changed: 12 Warnings: 0
③方法三:右联结
mysql> update deposite as d right join
-> (select c_id from customer where name='孙萌') as c
-> on c.c_id=d.c_id
-> set d.amount =d.amount+10000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
- 将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加100000
①方法一:内联结
mysql> update deposite as d inner join
-> (select b_id from bank where bank_name='工商银行') as b
-> on b.b_id=d.b_id
-> set d.amount =d.amount+100000
-> where d.dep_date='2011-04-05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
②方法二:左联结
mysql> update deposite as d left join
-> (select b_id from bank where bank_name='工商银行') as b
-> on b.b_id=d.b_id
-> set d.amount =d.amount+100000
-> where d.dep_date='2011-04-05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
③方法三:右联结
mysql> update deposite as d right join
-> (select b_id from bank where bank_name='工商银行') as b
-> on b.b_id=d.b_id
-> set d.amount =d.amount+100000
-> where d.dep_date='2011-04-05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 将数据表deposite中王琦的银行标识改为建设银行
mysql> update deposite set b_id=(select b_id from bank where bank_name='建设银行')
-> where c_id in (select c_id from customer where name='王琦');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
- 将salary低于5000的建行客户的salary变为原来的2倍.
①方法一
mysql> #方法一:内连接
mysql> update customer as c inner join
-> (select c_id from deposite where b_id='B0002') as d
-> on c.c_id=d.c_id
-> set c.salary=c.salary*2
-> where c.salary<5000;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
②方法二
mysql> #方法二:左连接
mysql> update customer as c left join
-> (select c_id from deposite where b_id='B0002') as d
-> on c.c_id=d.c_id
-> set c.salary=c.salary*2
-> where c.salary<5000;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
③方法三
mysql> #方法三:右连接
mysql> update customer as c right join
-> (select c_id from deposite where b_id='B0002') as d
-> on c.c_id=d.c_id
-> set c.salary=c.salary*2
-> where c.salary<5000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 查询日期为2011-04-05这一天进行过存款的客户ID,客户姓名,银行名称,存款金额
①方法一
mysql> #方法一:内联结
mysql> select c.c_id,c.name,b.bank_name,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b on b.b_id=d.b_id
-> and d.dep_date='2011-04-05';
+--------+------+-----------+-----------+
| c_id | name | bank_name | amount |
+--------+------+-----------+-----------+
| 101001 | 孙萌 | 工商银行 | 372526.00 |
+--------+------+-----------+-----------+
1 row in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.name,c.c_id,b.bank_name,d.amount
-> from deposite as d,customer as c,bank as b
-> where d.dep_date='2011-04-05'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id;
+------+--------+-----------+-----------+
| name | c_id | bank_name | amount |
+------+--------+-----------+-----------+
| 孙萌 | 101001 | 工商银行 | 372526.00 |
+------+--------+-----------+-----------+
1 row in set (0.00 sec)
- 查询赵越在建设银行的存款信息(显示信息:客户ID,客户姓名,银行标识,银行名称,存款日期,存款金额)
①方法一
mysql> #方法一:内联结
mysql> select c.c_id,c.name,b.b_id,b.bank_name,d.dep_date,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b
-> on b.b_id=d.b_id
-> and c.name='赵越' and b.bank_name='建设银行';
+--------+------+-------+-----------+------------+-----------+
| c_id | name | b_id | bank_name | dep_date | amount |
+--------+------+-------+-----------+------------+-----------+
| 101003 | 赵越 | B0002 | 建设银行 | 2018-11-21 | 480000.00 |
| 101003 | 赵越 | B0002 | 建设银行 | 2015-09-02 | -7654.02 |
+--------+------+-------+-----------+------------+-----------+
2 rows in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.c_id,c.name,b.b_id,b.bank_name,d.dep_date,d.amount
-> from deposite as d,customer as c,bank as b
-> where c.name='赵越' and b.bank_name='建设银行'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id;
+--------+------+-------+-----------+------------+-----------+
| c_id | name | b_id | bank_name | dep_date | amount |
+--------+------+-------+-----------+------------+-----------+
| 101003 | 赵越 | B0002 | 建设银行 | 2018-11-21 | 480000.00 |
| 101003 | 赵越 | B0002 | 建设银行 | 2015-09-02 | -7654.02 |
+--------+------+-------+-----------+------------+-----------+
2 rows in set (0.00 sec)
- 查询在农业银行存款前五名的客户存款信息(显示信息:客户姓名,银行名称,存款金额
①方法一
mysql> #方法一:内联结
mysql> select c.name,b.bank_name,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b on b.b_id=d.b_id
-> and b.bank_name='农业银行'
-> limit 5;
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 童彤 | 农业银行 | -7013.00 |
| 孙萌 | 农业银行 | 400000.00 |
+------+-----------+-----------+
2 rows in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.name,b.bank_name,d.amount
-> from deposite as d,customer as c,bank as b
-> where b.bank_name='农业银行'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id
-> limit 5;
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 童彤 | 农业银行 | -7013.00 |
| 孙萌 | 农业银行 | 400000.00 |
+------+-----------+-----------+
2 rows in set (0.00 sec)
(13)查询姓“童”的客户的存款信息(显示信息:客户姓名,银行名称,存款金额)
①方法一
mysql> #方法一:内联结
mysql> select c.name,b.bank_name,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b on b.b_id=d.b_id
-> where name like '童%';
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 童彤 | 农业银行 | -7013.00 |
| 童彤 | 工商银行 | 47890.00 |
| 童彤 | 中国银行 | -14321.12 |
+------+-----------+-----------+
3 rows in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.name,b.bank_name,d.amount
-> from deposite as d,customer as c,bank as b
-> where c.name like '童%'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id;
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 童彤 | 农业银行 | -7013.00 |
| 童彤 | 工商银行 | 47890.00 |
| 童彤 | 中国银行 | -14321.12 |
+------+-----------+-----------+
3 rows in set (0.00 sec)
(14)查询孙萌的存款信息(显示信息:客户ID,客户姓名,银行名称,存款金额)
(使用表连接、子查询及where条件查询三种方式实现)
①方法一
mysql> #方法一:内联结
mysql> select c.c_id,c.name,b.bank_name,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b on b.b_id=d.b_id
-> where name='孙萌';
+--------+------+-----------+-----------+
| c_id | name | bank_name | amount |
+--------+------+-----------+-----------+
| 101001 | 孙萌 | 工商银行 | 42526.00 |
| 101001 | 孙萌 | 建设银行 | 456.00 |
| 101001 | 孙萌 | 农业银行 | 400000.00 |
+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.c_id,c.name,b.bank_name,d.amount
-> from deposite as d,customer as c,bank as b
-> where c.name='孙萌'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id;
+--------+------+-----------+-----------+
| c_id | name | bank_name | amount |
+--------+------+-----------+-----------+
| 101001 | 孙萌 | 工商银行 | 42526.00 |
| 101001 | 孙萌 | 建设银行 | 456.00 |
| 101001 | 孙萌 | 农业银行 | 400000.00 |
+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
③方法三
mysql> #方法三:子查询
mysql> select c.c_id,c.name,d.amount,b.bank_name
-> from deposite as d,customer as c,bank as b
-> where b.b_id in
-> (select b_id from deposite where c_id in
-> (select c_id from customer where name='孙萌'))
-> and c.name='孙萌'
-> and c.c_id=d.c_id
-> and b.b_id=d.b_id;
+--------+------+-----------+-----------+
| c_id | name | amount | bank_name |
+--------+------+-----------+-----------+
| 101001 | 孙萌 | 42526.00 | 工商银行 |
| 101001 | 孙萌 | 456.00 | 建设银行 |
| 101001 | 孙萌 | 400000.00 | 农业银行 |
+--------+------+-----------+-----------+
3 rows in set (0.00 sec)
(15) 查询工商银行存款大于等于一万的客户ID,姓名,银行名称,存款金额
(使用表连接、子查询及where条件查询三种方式实现)
①方法一
mysql> #方法一:内联结
mysql> select c.c_id,c.name,b.bank_name,d.amount
-> from deposite as d
-> inner join customer as c
-> on c.c_id=d.c_id
-> inner join bank as b on b.b_id=d.b_id
-> where d.amount>=10000
-> and b.bank_name = '工商银行';
+--------+------+-----------+----------+
| c_id | name | bank_name | amount |
+--------+------+-----------+----------+
| 101001 | 孙萌 | 工商银行 | 42526.00 |
| 101004 | 童彤 | 工商银行 | 47890.00 |
+--------+------+-----------+----------+
2 rows in set (0.00 sec)
②方法二
mysql> #方法二:表联结
mysql> select c.c_id,c.name,b.bank_name,d.amount
-> from deposite as d,customer as c,bank as b
-> where d.amount>=10000
-> and b.bank_name = '工商银行'
-> and c.c_id= d.c_id
-> and b.b_id= d.b_id;
+--------+------+-----------+----------+
| c_id | name | bank_name | amount |
+--------+------+-----------+----------+
| 101001 | 孙萌 | 工商银行 | 42526.00 |
| 101004 | 童彤 | 工商银行 | 47890.00 |
+--------+------+-----------+----------+
2 rows in set (0.00 sec)
③方法三
mysql> #方法三:子查询
mysql> select c.c_id,c.name,d.amount,b.bank_name
-> from deposite as d,customer as c,bank as b
-> where c.c_id in
-> (select c_id from deposite where amount>=10000 and b_id in
-> (select b_id from bank where bank_name='工商银行'))
-> and d. amount>=10000
-> and b.bank_name='工商银行'
-> and c.c_id=d.c_id
-> and b.b_id=d.b_id;
+--------+------+----------+-----------+
| c_id | name | amount | bank_name |
+--------+------+----------+-----------+
| 101001 | 孙萌 | 42526.00 | 工商银行 |
| 101004 | 童彤 | 47890.00 | 工商银行 |
+--------+------+----------+-----------+
2 rows in set (0.00 sec)
子查询的方法还有很多,上述方法还是比较笨的
建议不是必要不要去搞子查询,内查询已经很好用了
说明:上述查询结果中出现负数是正常情况,因为数据做了修改,所以出现了负债
源码:
#建立数据库exp07
create database exp07;
#切换当前数据库
use exp07;
#建立customer表,并插入数据
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'工资'
)character set utf8 collate utf8_bin;
insert into customer(c_id,name,location,salary)
values('101001','孙萌','广州',1234);
insert into customer(c_id,name,location,salary)
values('101002','王琦','南京',3526);
insert into customer(c_id,name,location,salary)
values('101003','赵越','北京',6892);
insert into customer(c_id,name,location,salary)
values('101004','童彤','海南',3492);
#查看创建情况
select * from customer;
#创建bank表,并插入数据
create table bank(
b_id char(6) primary key comment'银行标识',
bank_name varchar(30) not null comment'银行名称'
)character set utf8 collate utf8_bin;
insert into bank(b_id,bank_name)
values('B0001','工商银行');
insert into bank(b_id,bank_name)
values('B0002','建设银行');
insert into bank(b_id,bank_name)
values('B0003','中国银行');
insert into bank(b_id,bank_name)
values('B0004','农业银行');
#查看创建情况
select * from bank;
#创建deposide表
create table deposite(
d_id int unsigned primary key auto_increment comment'存款流水账',
c_id char(6) comment'客户标识',
b_id char(6) comment'银行标识',
dep_date date comment'存入日期',
amount decimal(8,2) comment'存款金额'
)character set utf8 collate utf8_bin;
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (1,'101001','B0001','2011-04-05',42526);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (2,'101002','B0003','2012-12-02',66500);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (3,'101003','B0002','2018-11-21',500000);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (4,'101004','B0004','2020-03-02',12987);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (5,'101001','B0002','2015-09-02',456);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (6,'101002','B0004','2018-09-02',99000);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (7,'101003','B0003','2016-06-22',35000);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (8,'101004','B0001','2016-03-12',67890);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (9,'101001','B0004','2019-11-02',400000);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (10,'101002','B0001','2017-08-22',12000);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (11,'101003','B0002','2015-09-02',12345.98);
insert into deposite (d_id,c_id,b_id,dep_date,amount)
values (12,'101004','B0003','2014-10-13',5678.88);
#查看创建情况
select * from deposite;
#将数据表deposite中孙萌的存款金额加10000
#方法一:内查询
update deposite as d inner join
(select c_id from customer where name='孙萌') as c
on c.c_id=d.c_id
set d.amount =d.amount+10000;
#方法二:左查询
update deposite as d left join
(select c_id from customer where name='孙萌') as c
on c.c_id=d.c_id
set d.amount =d.amount+10000;
#方法三:右查询
update deposite as d right join
(select c_id from customer where name='孙萌') as c
on c.c_id=d.c_id
set d.amount =d.amount+10000;
#将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加100000
#方法一:内查询
update deposite as d inner join
(select b_id from bank where bank_name='工商银行') as b
on b.b_id=d.b_id
set d.amount =d.amount+100000
where d.dep_date='2011-04-05';
#方法二:左查询
update deposite as d left join
(select b_id from bank where bank_name='工商银行') as b
on b.b_id=d.b_id
set d.amount =d.amount+100000
where d.dep_date='2011-04-05';
#方法三:右查询
update deposite as d right join
(select b_id from bank where bank_name='工商银行') as b
on b.b_id=d.b_id
set d.amount =d.amount+100000
where d.dep_date='2011-04-05';
#将数据表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='王琦');
#将salary低于5000的建行客户的salary变为原来的2倍.
#方法一:内连接
update customer as c inner join
(select c_id from deposite where b_id='B0002') as d
on c.c_id=d.c_id
set c.salary=c.salary*2
where c.salary<5000;
#方法二:左连接
update customer as c left join
(select c_id from deposite where b_id='B0002') as d
on c.c_id=d.c_id
set c.salary=c.salary*2
where c.salary<5000;
#方法三:右连接
update customer as c right join
(select c_id from deposite where b_id='B0002') as d
on c.c_id=d.c_id
set c.salary=c.salary*2
where c.salary<5000;
#查询日期为2011-04-05这一天进行过存款的客户ID,客户姓名,银行名称,存款金额
#方法一:内联结
select c.c_id,c.name,b.bank_name,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b on b.b_id=d.b_id
and d.dep_date='2011-04-05';
#方法二:表联结
select c.name,c.c_id,b.bank_name,d.amount
from deposite as d,customer as c,bank as b
where d.dep_date='2011-04-05'
and c.c_id= d.c_id
and b.b_id= d.b_id;
#查询赵越在建设银行的存款信息(显示信息:客户ID,客户姓名,银行标识,银行名称,存款日期,存款金额)
#方法一:内联结
select c.c_id,c.name,b.b_id,b.bank_name,d.dep_date,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b
on b.b_id=d.b_id
and c.name='赵越' and b.bank_name='建设银行';
#方法二:表联结
select c.c_id,c.name,b.b_id,b.bank_name,d.dep_date,d.amount
from deposite as d,customer as c,bank as b
where c.name='赵越' and b.bank_name='建设银行'
and c.c_id= d.c_id
and b.b_id= d.b_id;
#查询在农业银行存款前五名的客户存款信息(显示信息:客户姓名,银行名称,存款金额)
#方法一:内联结
select c.name,b.bank_name,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b on b.b_id=d.b_id
and b.bank_name='农业银行'
limit 5;
#方法二:表联结
select c.name,b.bank_name,d.amount
from deposite as d,customer as c,bank as b
where b.bank_name='农业银行'
and c.c_id= d.c_id
and b.b_id= d.b_id
limit 5;
#查询姓“童”的客户的存款信息(显示信息:客户姓名,银行名称,存款金额)
#方法一:内联结
select c.name,b.bank_name,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b on b.b_id=d.b_id
where name like '童%';
#方法二:表联结
select c.name,b.bank_name,d.amount
from deposite as d,customer as c,bank as b
where c.name like '童%'
and c.c_id= d.c_id
and b.b_id= d.b_id;
#查询孙萌的存款信息(显示信息:客户ID,客户姓名,银行名称,存款金额)
(使用表连接、子查询及where条件查询三种方式实现)
#方法一:内联结
select c.c_id,c.name,b.bank_name,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b on b.b_id=d.b_id
where name='孙萌';
#方法二:表联结
select c.c_id,c.name,b.bank_name,d.amount
from deposite as d,customer as c,bank as b
where c.name='孙萌'
and c.c_id= d.c_id
and b.b_id= d.b_id;
#方法三:子查询
select c.c_id,c.name,d.amount,b.bank_name
from deposite as d,customer as c,bank as b
where b.b_id in
(select b_id from deposite where c_id in
(select c_id from customer where name='孙萌'))
and c.name='孙萌'
and c.c_id=d.c_id
and b.b_id=d.b_id;
#查询工商银行存款大于等于一万的客户ID,姓名,银行名称,存款金额
(使用表连接、子查询及where条件查询三种方式实现)
#方法一:内联结
select c.c_id,c.name,b.bank_name,d.amount
from deposite as d
inner join customer as c
on c.c_id=d.c_id
inner join bank as b on b.b_id=d.b_id
where d.amount>=10000
and b.bank_name = '工商银行';
#方法二:表联结
select c.c_id,c.name,b.bank_name,d.amount
from deposite as d,customer as c,bank as b
where d.amount>=10000
and b.bank_name = '工商银行'
and c.c_id= d.c_id
and b.b_id= d.b_id;
#方法三:子查询
select c.c_id,c.name,d.amount,b.bank_name
from deposite as d,customer as c,bank as b
where c.c_id in
(select c_id from deposite where amount>=10000 and b_id in
(select b_id from bank where bank_name='工商银行'))
and d. amount>=10000
and b.bank_name='工商银行'
and c.c_id=d.c_id
and b.b_id=d.b_id;
最后一题子查询的代码补充:
select c.c_id,c.name,b.bank_name,d.amount
from bank b,deposite d,customer as c
WHERE c.c_id in ( select c_id from deposite
WHERE amount in(select amount from deposite
WHERE amount>=10000 and b_id IN (select b_id from bank
where bank_name='工商银行')))
an b.b_id in select b_id from bank
where bank_name='工商银行')
and d. amount>=10000
and b.bank_name='工商银行'
and c.c_id=d.c_id
and b.b_id=d.b_id;
Select customer.c_id,name,bank_name,amount\
from deposite , customer,bank
where (deposite.d_id,customer.c_id,bank.b_id)
in ( select deposite.d_id, deposite.c_id, deposite.b_id
from deposite
where deposite.b_id
in (select bank.b_id from bank where bank_name='工商银行')
and amount>=10000);
Select customer.c_id,name,bank_name,amount\
from deposite , customer,bank
where deposite.b_id in(select bank.b_id from bank where bank_name='工商银行')
and customer.c_id=deposite.c_id
and bank.b_id=deposite.b_id
and amount>=10000;
最后一种方法是最简单的,其他方法都显得有些冗余,最后一种就是在deposite表中找到对应的信息,跟其他两个表做链接,非常好用。子查询就是讲究逻辑和思维。