Welcome to aparke’s blog!
1． 创建数据库，名称为CustomDB
2． 创建表（存储引擎使用InnoDB）customer（客户）、deposite（存款）、bank（银行），表结构如下：
(1)customer的表结构属性名称 类型与长度 中文含义 备注
c_id char(6) 客户标识 主键
name varchar(30) 客户姓名 非空
location varchar(30) 工作地点
salary decimal(8,2) 工资
CREATE TABLE customer ( |
属性名称 类型与长度 中文含义 备注
b_id char(5) 银行标识 主键
bank_name char(30) 银行名次 非空```sql
CREATE TABLE bank (
b_id CHAR(5) PRIMARY KEY
,bank_name VARCHAR(30) NOT NULL
) engine = innodb DEFAULT charset = utf8;
(3)deposite的表结构属性名称 类型与长度 中文含义 备注
d_id int 存款流水号 主键，自增（提示：使用关键字AUTO_INCREMENT）
c_id char(6) 客户标识
b_id char(5) 银行标识
dep _date date 存入日期
amount decimal(8,2) 存款金额
CREATE TABLE deposite ( |
3、录入数据：
customer的数据如下:c_id name location salary
101001 孙杨 广州 1234
101002 郭海 南京 3526
101003 卢江 苏州 6892
101004 郭惠 济南 3492
insert into customer values(101001,"孙杨","广州",1234); |
b_id bank_name
B0001 工商银行
B0002 建设银行
B0003 中国银行
B0004 农业银行```sql
insert into bank values("B0001","工商银行");
insert into bank values("B0002","建设银行");
insert into bank values("B0003","中国银行");
insert into bank values("B0004","农业银行");
deposite的数据如下：d_id c_id b_id dep_date amount
1 101001 B0001 2011-04-05 42526
2 101002 B0003 2012-07-15 66500
3 101003 B0002 2010-11-24 42366
4 101004 B0004 2008-03-31 62362
5 101001 B0003 2002-02-07 56346
6 101002 B0001 2004-09-23 353626
7 101003 B0004 2003-12-14 36236
8 101004 B0002 2007-04-21 26267
9 101001 B0002 2011-02-11 435456
10 101002 B0004 2012-05-13 234626
11 101003 B0003 2001-01-24 26243
12 101004 B0001 2009-08-23 45671
insert into deposite values (1,101001,"B0001","2011-04-05", 42526 ); |
4、修改数据：
(1).将数据表deposite中孙杨的存款金额加10000UPDATE deposite
SET amount = amount + 10000
WHERE c_id = (
SELECT c_id
FROM customer
WHERE name = "孙杨"
);
(2).将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加1000UPDATE deposite
SET amount = amount + 1000
WHERE b_id = (
SELECT b_id
FROM bank
WHERE bank_name = "工商银行"
)
AND dep_date = "2011-04-05"
(3).将数据表deposite中郭海的银行标识改为建设银行UPDATE deposite
SET b_id = (
SELECT b_id
FROM bank
WHERE bank_name = "建设银行"
)
WHERE c_id = (
SELECT c_id
FROM customer
WHERE name = "郭海"
);
5、查询数据：
(1).查询孙杨的存款信息（显示字段：客户ID，客户姓名，银行名称，存款金额）SELECT customer.c_id
,name
,bank_name
,amount
FROM customer
,bank
,deposite
WHERE deposite.c_id = customer.c_id
AND bank.b_id = deposite.b_id
AND customer.name = "孙杨";
SELECT c.c_id
,name
,bank_name
,amount
FROM customer c
INNER JOIN deposite d ON c.c_id = d.c_id
INNER JOIN bank b ON d.b_id = b.b_id
WHERE c.name = "孙杨";
(2).查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额SELECT c.c_id
,name
,bank_name
,amount
FROM customer c
INNER JOIN deposite d ON c.c_id = d.c_id
INNER JOIN bank b ON d.b_id = b.b_id
WHERE d.dep_date = "2011-04-05";
#表连接也可以实现
(3).查询郭海在建设银行的存款信息（显示字段：客户ID，客户姓名，银行标识，银行名称，存款日期，存款金额）SELECT c.c_id
,name
,b.b_id
,bank_name
,dep_date
,amount
FROM customer c
INNER JOIN deposite d ON c.c_id = d.c_id
INNER JOIN bank b ON d.b_id = b.b_id
WHERE c.name = "郭海"
AND bank_name = "建设银行";
(4).查询工商银行存款大于等于一万的客户姓名（使用表连接和子查询两种方式实现）#表连接
SELECT name
FROM customer
,bank
,deposite
WHERE deposite.c_id = customer.c_id
AND bank.b_id = deposite.b_id
AND amount > 10000
AND bank_name = "工商银行";
#内连接查询：
SELECT customer.name
FROM deposite
INNER JOIN customer ON customer.c_id = deposite.c_id
INNER JOIN bank ON bank.b_id = deposite.b_id
WHERE amount > 10000
AND bank_name = '工商银行';
#子查询
SELECT name
FROM customer
WHERE 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 = '工商银行'
)
)
);
(5).查询在农业银行存款前五名的客户存款信息（显示字段：客户姓名，银行名称，存款金额）SELECT name
,bank_name
,amount
FROM customer
,bank
,deposite
WHERE deposite.c_id = customer.c_id
AND bank.b_id = deposite.b_id
AND bank_name = "农业银行"
ORDER BY amount DESC limit 5;
SELECT name
,bank_name
,amount
FROM deposite
INNER JOIN customer ON customer.c_id = deposite.c_id
INNER JOIN bank ON bank.b_id = deposite.b_id
WHERE bank_name = "农业银行"
ORDER BY amount DESC limit 5;
(6).查询姓“卢”的客户的存款信息（显示字段：客户姓名，银行名称，存款金额）（提示where name like ‘卢%’）SELECT name
,bank_name
,amount
FROM deposite
INNER JOIN customer ON customer.c_id = deposite.c_id
INNER JOIN bank ON bank.b_id = deposite.b_id
WHERE name LIKE '卢%';
