1. 创建数据库,名称为qfdb;(如果已有,则省略)
create database if not exist qfdb; use qfdb
2. 创建数据表customer(客户)、deposite(存款)、bank(银行),表结构如下:
customer的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
c_id | char(6) | 客户标识 | 主键,非空 |
name | varchar(30) | 客户姓名 | 非空 |
location | Varchar(30) | 工作地点 | |
salary | decimal(8,2) | 工资 |
bank的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
b_id | char(5) | 银行标识 | 主键,非空 |
bank_name | char(30) | 银行名次 | 非空 |
deposite的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
d_id | int | 存款流水号 | 主键,非空,自增 |
c_id | char(6) | 客户标识 | 外键,关联customer表的c_id |
b_id | char(5) | 银行标识 | 外键,关联bank表的b_id |
dep _date | date | 存入日期 | |
dep_type | char(1) | 存款期限 | 1,3,5分别代表1年期、3年期和5年期 |
amount | decimal(8,2) | 存款金额 |
create table customer (c_id char(6) not null primary key comment '客户标识', name varchar(30) not null comment '客户姓名', location varchar(30) comment '工作地点', salary decimal(8, 2) comment '工资')default charset utf8mb4; create table bank (b_id char(5) not null primary key comment '银行标识', bank_name char(30) not null comment '银行名词')default charset utf8mb4; create table deposite (d_id int not null primary key auto_increment comment '存款流水号', c_id char(6) comment '客户标识', b_id char(5) comment '银行标识', dep_date date comment '存入日期', dep_type char(1) comment '存款期限:1,3,5分别代表1年期、3年期和5年期', amount decimal(8, 2) comment '存款金额', foreign key (c_id) references customer(c_id), foreign key (b_id) references bank(b_id)) default charset utf8mb4;
3. 录入数据如下:
customer的数据如下,注意最后一条记录用你的学号和你的姓名代替。
c_id | name | location | salary |
---|---|---|---|
101001 | 孙杨 | 广州 | 1234 |
101002 | 郭海 | 南京 | 3526 |
101003 | 卢江 | 苏州 | 6892 |
101004 | 郭惠 | 济南 | 3492 |
你的学号 | 你的姓名 | 北京 | 6324 |
insert into customer values ('101001', '孙杨', '广州', 1234), ('101002', '郭海', '南京', 3526), ('101003', '卢江', '苏州', 6892), ('101004', '郭惠', '济南', 3492), ('50274', '彭宇航', '北京', 6324) ;
bank的数据如下:
b_id | bank_name |
---|---|
B0001 | 工商银行 |
B0002 | 建设银行 |
B0003 | 中国银行 |
B0004 | 农业银行 |
insert into bank values ('B0001', '工商银行'), ('B0002', '建设银行'), ('B0003', '中国银行'), ('B0004', '农业银行');
deposite的数据如下:
d_id | c_id | b_id | dep_date | dep_type | amount |
---|---|---|---|---|---|
1 | 101001 | B0001 | 2011-04-05 | 3 | 42526 |
2 | 101002 | B0003 | 2012-07-15 | 5 | 66500 |
3 | 101003 | B0002 | 2010-11-24 | 1 | 42366 |
4 | 101004 | B0004 | 2008-03-31 | 1 | 62362 |
5 | 101001 | B0003 | 2002-02-07 | 3 | 56346 |
6 | 101002 | B0001 | 2004-09-23 | 3 | 353626 |
7 | 101003 | B0004 | 2003-12-14 | 5 | 36236 |
8 | 101004 | B0002 | 2007-04-21 | 5 | 26267 |
9 | 101001 | B0002 | 2011-02-11 | 1 | 435456 |
10 | 101002 | B0004 | 2012-05-13 | 1 | 234626 |
11 | 101003 | B0003 | 2001-01-24 | 5 | 26243 |
12 | 101004 | B0001 | 2009-08-23 | 3 | 45671 |
insert into deposite(c_id, b_id, dep_date, dep_type, amount) values ('101001','B0001','2011-04-05', '3', 42526), ('101002','B0003','2012-07-15', '5',66500), ('101003','B0002','2010-11-24', '1',42366), ('101004','B0004','2008-03-31', '1',62362), ('101001','B0003','2002-02-07', '3',56346), ('101002','B0001','2004-09-23', '3',353626), ('101003','B0004','2003-12-14', '5',36236), ('101004','B0002','2007-04-21', '5',26267), ('101001','B0002','2011-02-11', '1',435456), ('101002','B0004','2012-05-13', '1',234626), ('101003','B0003','2001-01-24', '5',26243), ('101004','B0001','2009-08-23', '3',45671);
4. 更新customer表的salary属性,将salary低于5000的客户的salary变为原来的2倍.
update customer set salary=salary+salary where salary<5000;
5. 对deposite表进行统计,按银行统计存款总数,显示为b_id,total.
select b_id, sum(amount) as total from deposite group by b_id;
6. 对deposite、customer、bank进行查询,查询条件为location在广州、苏州、济南的客户,存款在300000至500000之间的存款记录,显示客户姓名name、银行名称bank_name、存款金额amount.
select customer.name, bank.bank_name, deposite.amount from deposite inner join bank on deposite.b_id = bank.b_id inner join customer on deposite.c_id = customer.c_id where customer.location in ('广州', '苏州', '济南') and deposite.amount between 300000 and 500000;