MySQL上机操作
1. 创建数据库,名称为 MyBank
MariaDB [(none)]> create database MyBank;
Query OK, 1 row affected (0.006 sec)
2. 创建数据表 customer (客户)、deposite(存款)、bank(银行),表结构如下:
customer 的表结构
MariaDB [MyBank]> create table customer (
-> c_id char(6) not null primary key comment ‘客户标识’,
-> name varchar(30) not null comment ‘客户姓名’,
-> location varchar(30) comment ‘工作地点’,
-> salary double(9,2) comment ‘工资’
-> );
Query OK, 0 rows affected (0.021 sec)
MariaDB [MyBank]> desc customer;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| c_id | char(6) | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| location | varchar(30) | YES | | NULL | |
| salary | double(9,2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.011 sec)
属性名称
类型与长度
中文含义
备注
c_id
char(6)
客户标识
主键,非空
name
varchar(30)
客户姓名
非空
location
varchar(30)
工作地点
salary
double(9,2)
工资
bank 的表结构
属性名称
类型与长度
中文含义
备注
b_id
char(5)
银行标识
主键,非空
bank_name
char(30)
银行名称
非空
MariaDB [MyBank]> create table bank (
-> b_id char(5) primary key not null comment ‘银行标识’,
-> bank_name char(30) not null comment ‘银行名称’
-> );
Query OK, 0 rows affected (0.021 sec)
MariaDB [MyBank]> desc bank;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| b_id | char(5) | NO | PRI | NULL | |
| bank_name | char(30) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.013 sec)
deposite 的表结构 (存款)
属性名称
类型与长度
中文含义
备注
d_id
int
存款流水号
主键,非空,自增
c_id
char(6)
客户标识
外键,关联customer表的c_id
b_id
char(5)
银行标识
外键,关联bank表的b_id
dep_date
date
存入日期
dep_type
int
存款期限
amount
double(9,3)
存款金额
MariaDB [MyBank]> 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 int comment ‘存款期限’,
-> amount double(9,3) comment ‘存款金额’,
-> constraint deposite_customer_fk foreign key(c_id) references customer(c_id),
-> constraint deposite_bank_fk foreign key(b_id) references bank(b_id)
-> );
Query OK, 0 rows affected (0.025 sec)
MariaDB [MyBank]> desc deposite;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| d_id | int(11) | NO | PRI | NULL | auto_increment |
| c_id | char(6) | YES | MUL | NULL | |
| b_id | char(5) | YES | MUL | NULL | |
| dep_date | date | YES | | NULL | |
| dep_type | int(11) | YES | | NULL | |
| amount | double(9,3) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3. 录入数据如下:
customer 的数据如下,注意最后一条记录用你的学号和你的姓名代替
c_id
name
location
salary
101001
孙杨
广州
1234
101002
郭海
南京
3526
101003
卢江
苏州
6892
101004
郭惠
济南
3492
你的学号(后六位)
你的姓名
北京
6324
MariaDB [MyBank]> insert into customer values(101001,"孙杨",'广州',1234);
Query OK, 1 row affected (0.012 sec)
MariaDB [MyBank]> insert into customer values(101002,"郭海",'南京',3526);
Query OK, 1 row affected (0.002 sec)
MariaDB [MyBank]> insert into customer values(101003,"卢江",'苏州',6892);
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> insert into customer values(101004,"郭惠",'济南',3492);
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> insert into customer values(092519,"李春来",'北京',6324);
Query OK, 1 row affected (0.006 sec)
MariaDB [MyBank]> select *from customer;
+--------+--------+----------+---------+
| c_id | name | location | salary |
+--------+--------+----------+---------+
| 101001 | 孙杨 | 广州 | 1234.00 |
| 101002 | 郭海 | 南京 | 3526.00 |
| 101003 | 卢江 | 苏州 | 6892.00 |
| 101004 | 郭惠 | 济南 | 3492.00 |
| 092519 | 李春来 | 北京 | 6324.00 |
+--------+--------+----------+---------+
bank 的数据如下:
b_id
bank_name
B0001
工商银行
B0002
建设银行
B0003
中国银行
B0004
农业银行
MariaDB [MyBank]> insert into bank values('B0001','工商银行');
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> insert into bank values('B0002','建设银行');
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> insert into bank values('B0003','中国银行');
Query OK, 1 row affected (0.006 sec)
MariaDB [MyBank]> insert into bank values('B0004','农业银行');
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> select *from bank;
+-------+-----------+
| b_id | bank_name |
+-------+-----------+
| B0001 | 工商银行 |
| B0002 | 建设银行 |
| B0003 | 中国银行 |
| B0004 | 农业银行 |
+-------+-----------+
4 rows in set (0.000 sec)
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
2018-03-31
1
62362
5
101001
B0003
2012-02-07
3
56346
6
101002
B0001
2014-09-23
3
353626
7
101003
B0004
2015-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
2020-01-24
5
26243
12
101004
B0001
2009-08-23
3
45671
MariaDB [MyBank]> insert into deposite values(1,'101001','B0001','2011-04-05',3,42526);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(2,'101002','B0003','2012-07-15',5,66500);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(3,'101003','B0002','2010-11-24',1,42366);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(4,'101004','B0004','2018-03-31',1,62362);
Query OK, 1 row affected (0.010 sec)
MariaDB [Mybank]> insert into deposite values(5,'101001','B0003','2012-02-07',3,56346);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(6,'101002','B0001','2014-09-23',3,353626);
Query OK, 1 row affected (0.010 sec)
MariaDB [Mybank]> insert into deposite values(7,'101003','B0004','2015-12-14',5,36236);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(8,'101004','B0002','2007-04-21',5,26267);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(9,'101001','B0002','2011-02-11',1,435456);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(10,'101002','B0004','2012-05-13',1,234626);
Query OK, 1 row affected (0.009 sec)
MariaDB [Mybank]> insert into deposite values(11,'101003','B0003','2020-01-24',5,26243);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> insert into deposite values(12,'101004','B0001','2009-08-23',3,45671);
Query OK, 1 row affected (0.005 sec)
MariaDB [Mybank]> select *from deposite;
+------+--------+-------+------------+----------+------------+
| d_id | c_id | b_id | dep_date | dep_type | amount |
+------+--------+-------+------------+----------+------------+
| 1 | 101001 | B0001 | 2011-04-05 | 3 | 42526.000 |
| 2 | 101002 | B0003 | 2012-07-15 | 5 | 66500.000 |
| 3 | 101003 | B0002 | 2010-11-24 | 1 | 42366.000 |
| 4 | 101004 | B0004 | 2018-03-31 | 1 | 62362.000 |
| 5 | 101001 | B0003 | 2012-02-07 | 3 | 56346.000 |
| 6 | 101002 | B0001 | 2014-09-23 | 3 | 353626.000 |
| 7 | 101003 | B0004 | 2015-12-14 | 5 | 36236.000 |
| 8 | 101004 | B0002 | 2007-04-21 | 5 | 26267.000 |
| 9 | 101001 | B0002 | 2011-02-11 | 1 | 435456.000 |
| 10 | 101002 | B0004 | 2012-05-13 | 1 | 234626.000 |
| 11 | 101003 | B0003 | 2020-01-24 | 5 | 26243.000 |
| 12 | 101004 | B0001 | 2009-08-23 | 3 | 45671.000 |
+------+--------+-------+------------+----------+------------+
12 rows in set (0.000 sec)
4. 更新 customer 表的 salary 属性,将 salary 低于 5000 的客户的 salary 变为原来的 2 倍.
MariaDB [Mybank]> select *from customer;
+--------+--------+----------+---------+
| c_id | name | location | salary |
+--------+--------+----------+---------+
| 101001 | 孙杨 | 广州 | 1234.00 |
| 101002 | 郭海 | 南京 | 3526.00 |
| 101003 | 卢江 | 苏州 | 6892.00 |
| 101004 | 郭惠 | 济南 | 3492.00 |
| 92519 | 李春来 | 北京 | 6324.00 |
+--------+--------+----------+---------+
5 rows in set (0.000 sec)
MariaDB [Mybank]> update customer set salary=salary*2 where salary<5000;
Query OK, 3 rows affected (0.010 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [Mybank]> select *from customer;
+--------+--------+----------+---------+
| c_id | name | location | salary |
+--------+--------+----------+---------+
| 101001 | 孙杨 | 广州 | 2468.00 |
| 101002 | 郭海 | 南京 | 7052.00 |
| 101003 | 卢江 | 苏州 | 6892.00 |
| 101004 | 郭惠 | 济南 | 6984.00 |
| 92519 | 李春来 | 北京 | 6324.00 |
+--------+--------+----------+---------+
5. 对 deposite 表进行统计,按银行统计存款总数,显示为 b_id,total.
MariaDB [Mybank]> select b_id,sum(amount) from deposite group by b_id;
+-------+-------------+
| b_id | sum(amount) |
+-------+-------------+
| B0001 | 441823.000 |
| B0002 | 504089.000 |
| B0003 | 149089.000 |
| B0004 | 333224.000 |
+-------+-------------+
4 rows in set (0.000 sec)
6. 对 deposite 、customer 、bank 进行查询,查询条件为 location 在广州、苏州、济南的客户,存款在 300000 至 500000 之间的存款记录, 显示客户姓名 name、银行名称 bank_name 、存款金额 amount.
MariaDB [mybank]> select name,bank_name,amount from bank,customer,deposite where deposite.c_id = customer.c_id and deposite.b_id = bank.b_id and amount > 30000 and amount < 50000 and location in (select location from customer where location="广州" or location ="济南" or location = "苏州");
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 孙杨 | 工商银行 | 42526.000 |
| 郭惠 | 工商银行 | 45671.000 |
| 卢江 | 建设银行 | 42366.000 |
| 卢江 | 农业银行 | 36236.000 |
+------+-----------+-----------+
4 rows in set (0.001 sec)
7. 在 deposite 中插入一条记录, 客户标识 c_id 为你的学号,银行标识 b_id 为 B0003,存款日期 dep_date 为当前系统日期,存款期限 type 为 3,存款金额 amount 为 50000
MariaDB [MyBank]> insert into deposite values(13,'092519','B0003','2020-11-28',3,50000);
Query OK, 1 row affected (0.004 sec)
MariaDB [MyBank]> select *from deposite;
+------+--------+-------+------------+----------+------------+
| d_id | c_id | b_id | dep_date | dep_type | amount |
+------+--------+-------+------------+----------+------------+
| 1 | 101001 | B0001 | 2011-04-05 | 3 | 42526.000 |
| 2 | 101002 | B0003 | 2012-07-15 | 5 | 66500.000 |
| 3 | 101003 | B0002 | 2010-11-24 | 1 | 42366.000 |
| 4 | 101004 | B0004 | 2018-03-31 | 1 | 62362.000 |
| 5 | 101001 | B0003 | 2012-02-07 | 3 | 56346.000 |
| 6 | 101002 | B0001 | 2014-09-23 | 3 | 353626.000 |
| 7 | 101003 | B0004 | 2015-12-14 | 5 | 36236.000 |
| 8 | 101004 | B0002 | 2007-04-21 | 5 | 26267.000 |
| 9 | 101001 | B0002 | 2011-02-11 | 1 | 435456.000 |
| 10 | 101002 | B0004 | 2012-05-13 | 1 | 234626.000 |
| 11 | 101003 | B0003 | 2020-01-24 | 5 | 26243.000 |
| 12 | 101004 | B0001 | 2009-08-23 | 3 | 45671.000 |
| 13 | 092519 | B0003 | 2020-11-28 | 3 | 50000.000 |
+------+--------+-------+------------+----------+------------+
13 rows in set (0.000 sec)
8. 完成以下题目:
(1) 在 bank 中插入一条新记录 B0005,交通银行;
MariaDB [MyBank]> insert into bank values ('B0005','交通银行');
Query OK, 1 row affected (0.005 sec)
MariaDB [MyBank]> select * from bank;
+-------+-----------+
| b_id | bank_name |
+-------+-----------+
| B0001 | 工商银行 |
| B0002 | 建设银行 |
| B0003 | 中国银行 |
| B0004 | 农业银行 |
| B0005 | 交通银行 |
+-------+-----------+
5 rows in set (0.000 sec)
(2) 查询今天到期的存款信息
MariaDB [MyBank]> select *from deposite where year(dep_date)+dep_type < year(now()) or if(year(dep_date)+dep_type=2020,( month(dep_date) <= month(now()) and dayofmonth(dep_date)
+------+--------+-------+------------+----------+------------+
| d_id | c_id | b_id | dep_date | dep_type | amount |
+------+--------+-------+------------+----------+------------+
| 1 | 101001 | B0001 | 2011-04-05 | 3 | 42526.000 |
| 2 | 101002 | B0003 | 2012-07-15 | 5 | 66500.000 |
| 3 | 101003 | B0002 | 2010-11-24 | 1 | 42366.000 |
| 4 | 101004 | B0004 | 2018-03-31 | 1 | 62362.000 |
| 5 | 101001 | B0003 | 2012-02-07 | 3 | 56346.000 |
| 6 | 101002 | B0001 | 2014-09-23 | 3 | 353626.000 |
| 8 | 101004 | B0002 | 2007-04-21 | 5 | 26267.000 |
| 9 | 101001 | B0002 | 2011-02-11 | 1 | 435456.000 |
| 10 | 101002 | B0004 | 2012-05-13 | 1 | 234626.000 |
| 12 | 101004 | B0001 | 2009-08-23 | 3 | 45671.000 |
+------+--------+-------+------------+----------+------------+
10 rows in set (0.000 sec)
(3) 查询存款金额超过 50000 且存款期限为 3 年的存款信息
MariaDB [MyBank]> select *from deposite where amount > 50000 and dep_type=3;
+------+--------+-------+------------+----------+------------+
| d_id | c_id | b_id | dep_date | dep_type | amount |
+------+--------+-------+------------+----------+------------+
| 5 | 101001 | B0003 | 2012-02-07 | 3 | 56346.000 |
| 6 | 101002 | B0001 | 2014-09-23 | 3 | 353626.000 |
+------+--------+-------+------------+----------+------------+
2 rows in set (0.000 sec)
(4) 查询前 3 名的存款信息
MariaDB [MyBank]> select *from deposite where d_id<4;
+------+--------+-------+------------+----------+-----------+
| d_id | c_id | b_id | dep_date | dep_type | amount |
+------+--------+-------+------------+----------+-----------+
| 1 | 101001 | B0001 | 2011-04-05 | 3 | 42526.000 |
| 2 | 101002 | B0003 | 2012-07-15 | 5 | 66500.000 |
| 3 | 101003 | B0002 | 2010-11-24 | 1 | 42366.000 |
+------+--------+-------+------------+----------+-----------+
3 rows in set (0.000 sec)
(5) 查询孙杨在中国银行的存款信息
MariaDB [MyBank]> select name,bank_name,dep_date,dep_type,amount from bank join deposite on deposite.b_id = bank.b_id join customer on customer.c_id = deposite.c_id where name="孙杨" and bank_name="中国银行";
+------+-----------+------------+----------+-----------+
| name | bank_name | dep_date | dep_type | amount |
+------+-----------+------------+----------+-----------+
| 孙杨 | 中国银行 | 2012-02-07 | 3 | 56346.000 |
+------+-----------+------------+----------+-----------+
1 row in set (0.000 sec)
(6) 查询存款日期在 2012 年之后的农业银行的存款信息,并按降序排列
MariaDB [MyBank]> select bank_name,d_id,c_id,deposite.b_id,dep_date,dep_type,amount from bank join deposite on bank.b_id = deposite.b_id where bank_name = '农业银行' and year(dep_date) > 2012 order by dep_date desc;
+-----------+------+--------+-------+------------+----------+-----------+
| bank_name | d_id | c_id | b_id | dep_date | dep_type | amount |
+-----------+------+--------+-------+------------+----------+-----------+
| 农业银行 | 4 | 101004 | B0004 | 2018-03-31 | 1 | 62362.000 |
| 农业银行 | 7 | 101003 | B0004 | 2015-12-14 | 5 | 36236.000 |
+-----------+------+--------+-------+------------+----------+-----------+
2 rows in set (0.000 sec)
源文件 链接:https://share.weiyun.com/2MlbdJ0l 密码:fx2rak