RDBMS之综合实践
本章学习目标
熟练掌握综合练习
通过前面章节的学习,大家已经掌握了MySQL的基本操作和高级应用,本章将通过一个综合案例将前面所学知识进行综合练习,以提高大家在实际开发中应用MySQL数据库的能力。
13.1 数据准备
在学习综合案例前,首先创建五张数据表并插入数据,用于后面的例题演示,分别为银行表bank、管理员信息表admin、客户表customer、客户备注信息表cus_remarks和存款流水信息表deposite,首先来了解一下表结构,银行表bank如表13.1所示。
表13.1 bank表
字段 |
字段类型 |
说明 |
b_id |
char(5) |
银行编号 |
b_name |
varchar(30) |
银行名称 |
表13.1中列出了银行表的字段、字段类型和说明,接着创建银行表,其中b_id为主键,b_name不为NULL,在创建表之前还要创建库,这里库的名称为qfexample,SQL语句如下所示。
mysql> CREATE DATABASE qfexample;
Query OK, 1 row affected (0.00 sec)
mysql> USE qfexample;
Database changed
mysql> CREATE TABLE bank(
-> b_id CHAR(5) PRIMARY KEY,
-> b_name VARCHAR(30) NOT NULL
-> );
Query OK, 0 rows affected (0.08 sec)
银行表创建完成后,向表中插入数据,SQL语句如下所示。
mysql> INSERT INTO bank VALUES
-> ('B0001','中国工商银行'),
-> ('B0002','中国农业银行'),
-> ('B0003','中国银行'),
-> ('B0004','中国建设银行'),
-> ('B0005','中国交通银行'),
-> ('B0006','招商银行'),
-> ('B0007','浦发银行'),
-> ('B0008','兴业银行'),
-> ('B0009','中国农业发展银行'),
-> ('B0010','中国民生银行');
Query OK, 10 rows affected (0.07 sec)
Records: 10 Duplicates: 0 Warnings: 0
接着创建管理员信息表admin,表结构如表13.2所示。
表13.2 admin表
字段 |
字段类型 |
说明 |
a_id |
varchar(30) |
管理员编号 |
a_name |
varchar(50) |
管理员姓名 |
a_sex |
varchar(10) |
管理员性别 |
a_phone |
varchar(30) |
管理员电话 |
a_date |
date |
管理员入行日期 |
b_id |
char(5) |
所属银行编号 |
表13.2中列出了管理员表的字段、字段类型和说明,接着创建管理员表,其中a_id为主键,b_id为外键,关联表bank中的b_id字段,SQL语句如下所示。
mysql> CREATE TABLE admin(
-> a_id VARCHAR(30) PRIMARY KEY,
-> a_name VARCHAR(50),
-> a_sex VARCHAR(10),
-> a_phone VARCHAR(30),
-> a_date DATE,
-> b_id CHAR(5),
-> FOREIGN KEY (`b_id`) REFERENCES `bank` (`b_id`)
-> );
Query OK, 0 rows affected (0.08 sec)
管理员信息表创建完成后,向表中插入数据,SQL语句如下所示。
mysql> INSERT INTO admin VALUES
-> ('BANK100001','浩然','男','13816668888','2012-05-02','B0002'),
-> ('BANK100002','智宇','男','13816661188','2014-07-22','B0006'),
-> ('BANK100003','永昌','男','13816662288','2016-04-08','B0008'),
-> ('BANK100004','映冬','男','13816663388','2011-11-09','B0001'),
-> ('BANK100005','思萱','女','13816664488','2011-12-04','B0003'),
-> ('BANK100006','香彤','女','13816665588','2012-07-11','B0004'),
-> ('BANK100007','振宇','男','13816666688','2016-07-13','B0007'),
-> ('BANK100008','元冬','男','13816667788','2014-08-14','B0010'),
-> ('BANK100009','梦蕊','女','13816669988','2017-09-18','B0005'),
-> ('BANK100010','罗文','男','13816668811','2013-02-20','B0006'),
-> ('BANK100011','昌茂','男','13816668822','2014-01-22','B0007'),
-> ('BANK100012','曦哲','男','13816668833','2015-03-21','B0008'),
-> ('BANK100013','智晖','男','13816668844','2016-04-24','B0001'),
-> ('BANK100014','谷芹','女','13816668855','2017-05-31','B0003'),
-> ('BANK100015','元瑶','女','13816668866','2014-06-03','B0002'),
-> ('BANK100016','觅云','女','13816668877','2013-07-06','B0004'),
-> ('BANK100017','映雁','女','13816668899','2012-08-01','B0005'),
-> ('BANK100018','恨山','男','15816168888','2011-09-15','B0007'),
-> ('BANK100019','辰阳','男','15816768888','2010-10-22','B0010'),
-> ('BANK100020','运珧','女','13811168888','2017-10-17','B0002');
接着创建客户表customer,表结构如表13.3所示。
表13.3 customer表
字段 |
字段类型 |
说明 |
c_id |
char(6) |
客户编号 |
c_name |
varchar(30) |
客户姓名 |
c_sex |
varchar(10) |
客户性别 |
c_card |
varchar(50) |
客户身份证号 |
c_province |
varchar(50) |
客户开户省份 |
c_create |
timestamp |
客户创建时间 |
表13.3中列出了客户表的字段、字段类型和说明,接着创建客户表,其中c_id为主键,c_name不为NULL,c_create默认值为系统当前时间,SQL语句如下所示。
mysql> CREATE TABLE customer(
-> c_id CHAR(6) PRIMARY KEY,
-> c_name VARCHAR(30)NOT NULL,
-> c_sex VARCHAR(10),
-> c_card VARCHAR(50),
-> c_province VARCHAR(50),
-> c_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.09 sec)
客户表创建完成后,向表中插入数据,SQL语句如下所示。
mysql> INSERT INTO customer
-> (c_id,c_name,c_sex,c_card,c_province)
-> VALUES
-> ('C10001','彦哲','男','110229198201062223','北京'),
-> ('C10002','松宁','女','220229198201062223','上海'),
-> ('C10003','芝赋','女','330229198201062223','广州'),
-> ('C10004','帅铭','男','440229198201062223','安徽'),
-> ('C10005','再军','男','550229198201062223','辽宁'),
-> ('C10006','玉博','男','660229198201062223','天津'),
-> ('C10007','晨朗','女','770229198201062223','河北'),
-> ('C10008','熙珑','女','880229198201062223','海南'),
-> ('C10009','乐隽','女','990229198201062223','河南'),
-> ('C10010','君贤','男','120229198201062223','湖北'),
-> ('C10011','蓉阳','女','130229198201062223','山西'),
-> ('C10012','文昌','男','140229198201062223','重庆'),
-> ('C10013','鹏瑞','男','150229198201062223','陕西'),
-> ('C10014','健钊','男','160229198201062223','内蒙古'),
-> ('C10015','建瑜','男','170229198201062223','青海'),
-> ('C10016','飞龙','男','180229198201062223','黑龙江'),
-> ('C10017','然宁','女','190229198201062223','山东'),
-> ('C10018','芝家','女','210229198201062223','湖南'),
-> ('C10019','正尧','女','310229198201062223','广东'),
-> ('C10020','晨启','男','410229198201062223','吉林');
接着创建客户备注信息表cus_remarks,表结构如表13.4所示。
表13.4 cus_remarks表
字段 |
字段类型 |
说明 |
c_id |
char(6) |
客户编号 |
c_remarks |
text |
客户备注信息 |
表13.4中列出了客户备注信息表的字段、字段类型和说明,接着创建客户备注信息表,其中c_id为主键且与customer表的c_id字段外键关联,是一个基于主键的一对一关联关系,一个客户只对应一个客户备注信息,SQL语句如下所示。
mysql> CREATE TABLE cus_remarks(
-> c_id CHAR(6) PRIMARY KEY,
-> c_remarks TEXT,
-> FOREIGN KEY(c_id) REFERENCES customer(c_id)
-> );
Query OK, 0 rows affected (0.09 sec)
客户备注信息表创建完成后,向表中插入数据,SQL语句如下所示。
mysql> INSERT INTO cus_remarks
-> VALUES
-> ('C10023','客户23的备注信息'),
-> ('C10007','客户07的备注信息'),
-> ('C10026','客户26的备注信息'),
-> ('C10015','客户15的备注信息'),
-> ('C10009','客户09的备注信息'),
-> ('C10029','客户29的备注信息'),
-> ('C10012','客户12的备注信息'),
-> ('C10036','客户36的备注信息'),
-> ('C10038','客户38的备注信息'),
-> ('C10042','客户42的备注信息'),
-> ('C10011','客户11的备注信息'),
-> ('C10020','客户20的备注信息'),
-> ('C10010','客户10的备注信息'),
-> ('C10002','客户02的备注信息'),
-> ('C10013','客户13的备注信息');
Query OK, 15 rows affected (0.03 sec)
Records: 15 Duplicates: 0 Warnings: 0
最后创建存款流水信息表deposite,表结构如表13.5所示。
表13.5 deposite表
字段 |
字段类型 |
说明 |
d_id |
INT(10) |
存款流水编号 |
c_id |
char(6) |
客户编号 |
b_id |
char(5) |
银行编号 |
d_amount |
decimal(8,2) |
存款金额 |
d_date |
timestamp |
存款日期 |
表13.5中列出了存款流水信息表的字段、字段类型和说明,接着创建存款流水信息表,其中d_id为主键且自增,d_date默认值为系统当前时间,c_id为外键,与customer表中的c_id字段关联,是一对多的关联关系,一个客户可以有多个存款流水信息,SQL语句如下所示。
mysql> CREATE TABLE deposite(
-> d_id INT(10) AUTO_INCREMENT PRIMARY KEY,
-> c_id CHAR(6),
-> b_id CHAR(5),
-> d_amount DECIMAL(8,2),
-> d_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> FOREIGN KEY(c_id) REFERENCES customer(c_id)
-> );
Query OK, 0 rows affected (0.08 sec)
存款流水信息表创建完成后,向表中插入数据,SQL语句如下所示。
mysql> INSERT INTO deposite
-> (c_id,b_id,d_amount)
-> VALUES
-> ('C10003','B0001',1000),
-> ('C10026','B0007',1000),
-> ('C10043','B0003',1000),
-> ('C10011','B0004',600),
-> ('C10009','B0001',1500),
-> ('C10029','B0010',3000),
-> ('C10049','B0009',8000),
-> ('C10032','B0002',10000),
-> ('C10027','B0003',50000),
-> ('C10019','B0007',100),
-> ('C10041','B0008',2500),
-> ('C10015','B0002',3600),
-> ('C10022','B0009',7200),
-> ('C10006','B0010',8800),
-> ('C10039','B0003',15000),
-> ('C10017','B0005',3200),
-> ('C10021','B0007',6400),
-> ('C10001','B0002',9800),
-> ('C10010','B0006',12300),
-> ('C10045','B0007',3500);
至此,五张表创建完成,后面会用这些表做演示例题。
13.2 综合练习
数据准备完成后,接下来利用这些表进行综合练习,便于大家快速理解和掌握前面章节的知识。
例13-1 将表admin中a_id为BANK100017的员工手机号修改为13661122333,SQL语句如下所示。
mysql> UPDATE admin
-> SET a_phone='13661122333'
-> WHERE a_id='BANK100017';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
例13-2 将表admin中a_id为BANK100050的员工姓名修改为齐山,手机号修改为15916614321,SQL语句如下所示。
mysql> UPDATE admin
-> SET a_name='齐山',a_phone='15916614321'
-> WHERE a_id='BANK100050';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
例13-3 将表admin中a_id为BANK100050的员工信息删除,SQL语句如下所示。
mysql> DELETE FROM admin
-> WHERE a_id='BANK100050';
Query OK, 1 row affected (0.06 sec)
例13-4 查询表deposite中的所有数据,SQL语句如下所示。
mysql> SELECT * FROM deposite;
+------+--------+-------+----------+---------------------+
| d_id | c_id | b_id | d_amount | d_date |
+------+--------+-------+----------+---------------------+
| 1 | C10003 | B0001 | 7000.00 | 2018-01-02 17:30:33 |
| 2 | C10026 | B0007 | 1000.00 | 2018-01-02 17:30:33 |
| 3 | C10043 | B0003 | 30000.00 | 2018-01-02 17:30:33 |
| 4 | C10011 | B0004 | 600.00 | 2018-01-02 17:30:33 |
| 5 | C10009 | B0001 | 1500.00 | 2018-01-02 17:30:33 |
| 6 | C10029 | B0010 | 3000.00 | 2018-01-02 17:30:33 |
| 7 | C10049 | B0009 | 8000.00 | 2018-01-02 17:30:33 |
| 8 | C10032 | B0002 | 10000.00 | 2018-01-02 17:30:33 |
| 9 | C10027 | B0003 | 50000.00 | 2018-01-02 17:30:33 |
| 10 | C10019 | B0001 | 100.00 | 2018-01-02 17:30:33 |
| 11 | C10041 | B0008 | 2500.00 | 2018-01-02 17:30:33 |
| 12 | C10015 | B0002 | 3600.00 | 2018-01-02 17:30:33 |
| 13 | C10022 | B0009 | 7200.00 | 2018-01-02 17:30:33 |
| 14 | C10006 | B0010 | 8800.00 | 2018-01-02 17:30:33 |
| 15 | C10039 | B0003 | 15000.00 | 2018-01-02 17:30:33 |
| 16 | C10017 | B0005 | 3200.00 | 2018-01-02 17:30:33 |
| 17 | C10021 | B0007 | 6400.00 | 2018-01-02 17:30:33 |
| 18 | C10001 | B0002 | 9800.00 | 2018-01-02 17:30:33 |
| 19 | C10010 | B0006 | 12300.00 | 2018-01-02 17:30:33 |
| 20 | C10045 | B0007 | 3500.00 | 2018-01-02 17:30:33 |
+------+--------+-------+----------+---------------------+
例13-5 20 rows in set (0.00 sec)
例13-6 查询表deposite中的所有c_id和d_amount,SQL语句如下所示。
mysql> SELECT c_id,d_amount FROM deposite;
+--------+----------+
| c_id | d_amount |
+--------+----------+
| C10003 | 7000.00 |
| C10026 | 1000.00 |
| C10043 | 30000.00 |
| C10011 | 600.00 |
| C10009 | 1500.00 |
| C10029 | 3000.00 |
| C10049 | 8000.00 |
| C10032 | 10000.00 |
| C10027 | 50000.00 |
| C10019 | 100.00 |
| C10041 | 2500.00 |
| C10015 | 3600.00 |
| C10022 | 7200.00 |
| C10006 | 8800.00 |
| C10039 | 15000.00 |
| C10017 | 3200.00 |
| C10021 | 6400.00 |
| C10001 | 9800.00 |
| C10010 | 12300.00 |
| C10045 | 3500.00 |
+--------+----------+
例13-7 20 rows in set (0.00 sec)
例13-8 查询表admin中所有性别为女的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_sex='女';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100005 | 思萱 | 女 | 13816664488 | 2011-12-04 | B0003 |
| BANK100006 | 香彤 | 女 | 13816665588 | 2012-07-11 | B0004 |
| BANK100009 | 梦蕊 | 女 | 13816669988 | 2017-09-18 | B0005 |
| BANK100014 | 谷芹 | 女 | 13816668855 | 2017-05-31 | B0003 |
| BANK100015 | 元瑶 | 女 | 13816668866 | 2014-06-03 | B0002 |
| BANK100016 | 觅云 | 女 | 13816668877 | 2013-07-06 | B0004 |
| BANK100017 | 映雁 | 女 | 13661122333 | 2012-08-01 | B0005 |
| BANK100020 | 运珧 | 女 | 13811168888 | 2017-10-17 | B0002 |
| BANK100023 | 新曦 | 女 | 13814468888 | 2015-01-27 | B0008 |
| BANK100024 | 寻巧 | 女 | 13815568888 | 2015-03-07 | B0001 |
| BANK100025 | 碧萱 | 女 | 13817768888 | 2013-04-08 | B0003 |
| BANK100031 | 涵蕾 | 女 | 13816633888 | 2016-01-21 | B0003 |
| BANK100032 | 寄琴 | 女 | 13816644888 | 2016-11-25 | B0005 |
| BANK100034 | 巧蕊 | 女 | 13816666888 | 2011-10-28 | B0004 |
| BANK100048 | 飞莲 | 女 | 13006668888 | 2015-08-27 | B0001 |
| BANK100049 | 青寒 | 女 | 15816768888 | 2013-08-26 | B0008 |
+------------+--------+-------+-------------+------------+-------+
16 rows in set (0.00 sec)
例13-9 查询表admin中a_id为BANK100019的管理员姓名和电话,SQL语句如下所示。
mysql> SELECT a_name,a_phone
-> FROM admin
-> WHERE a_id='BANK100019';
+--------+-------------+
| a_name | a_phone |
+--------+-------------+
| 辰阳 | 15816768888 |
+--------+-------------+
1 row in set (0.00 sec)
例13-10 查询表admin中所有入行时间在2016年1月1日之后的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_date>'2016-01-01';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100003 | 永昌 | 男 | 13816662288 | 2016-04-08 | B0008 |
| BANK100007 | 振宇 | 男 | 13816666688 | 2016-07-13 | B0007 |
| BANK100009 | 梦蕊 | 女 | 13816669988 | 2017-09-18 | B0005 |
| BANK100013 | 智晖 | 男 | 13816668844 | 2016-04-24 | B0001 |
| BANK100014 | 谷芹 | 女 | 13816668855 | 2017-05-31 | B0003 |
| BANK100020 | 运珧 | 女 | 13811168888 | 2017-10-17 | B0002 |
| BANK100021 | 澄邈 | 男 | 13812268888 | 2016-11-19 | B0003 |
| BANK100022 | 辰光 | 男 | 13813368888 | 2016-02-25 | B0006 |
| BANK100031 | 涵蕾 | 女 | 13816633888 | 2016-01-21 | B0003 |
| BANK100032 | 寄琴 | 女 | 13816644888 | 2016-11-25 | B0005 |
| BANK100044 | 佑运 | 男 | 13666668888 | 2016-04-13 | B0007 |
| BANK100045 | 昆皓 | 男 | 13776668888 | 2017-04-12 | B0006 |
+------------+--------+-------+-------------+------------+-------+
12 rows in set (0.00 sec)
例13-11 查询表admin中所有入行时间在2016年1月1日之后的女管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_date>'2016-01-01'
-> AND a_sex<>'男';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100009 | 梦蕊 | 女 | 13816669988 | 2017-09-18 | B0005 |
| BANK100014 | 谷芹 | 女 | 13816668855 | 2017-05-31 | B0003 |
| BANK100020 | 运珧 | 女 | 13811168888 | 2017-10-17 | B0002 |
| BANK100031 | 涵蕾 | 女 | 13816633888 | 2016-01-21 | B0003 |
| BANK100032 | 寄琴 | 女 | 13816644888 | 2016-11-25 | B0005 |
+------------+--------+-------+-------------+------------+-------+
5 rows in set (0.00 sec)
例13-12 查询表admin中a_id为BANK100015或者姓名为泽光的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_id='BANK100015'
-> OR a_name='泽光';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100015 | 元瑶 | 女 | 13816668866 | 2014-06-03 | B0002 |
| BANK100027 | 泽光 | 男 | 13819968888 | 2014-08-01 | B0010 |
+------------+--------+-------+-------------+------------+-------+
2 rows in set (0.00 sec)
例13-13 查询表admin中a_id为BANK100011、BANK100023和BANK100030的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_id IN('BANK100011','BANK100023','BANK100030');
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100011 | 昌茂 | 男 | 13816668822 | 2014-01-22 | B0007 |
| BANK100023 | 新曦 | 女 | 13814468888 | 2015-01-27 | B0008 |
| BANK100030 | 怀寒 | 男 | 13816622888 | 2015-01-18 | B0002 |
+------------+--------+-------+-------------+------------+-------+
3 rows in set (0.01 sec)
例13-14 查询表admin中所有入行时间在2016年1月1和2017年1月1日之间的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_date BETWEEN '2016-01-01' AND '2017-01-01';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100003 | 永昌 | 男 | 13816662288 | 2016-04-08 | B0008 |
| BANK100007 | 振宇 | 男 | 13816666688 | 2016-07-13 | B0007 |
| BANK100013 | 智晖 | 男 | 13816668844 | 2016-04-24 | B0001 |
| BANK100021 | 澄邈 | 男 | 13812268888 | 2016-11-19 | B0003 |
| BANK100022 | 辰光 | 男 | 13813368888 | 2016-02-25 | B0006 |
| BANK100031 | 涵蕾 | 女 | 13816633888 | 2016-01-21 | B0003 |
| BANK100032 | 寄琴 | 女 | 13816644888 | 2016-11-25 | B0005 |
| BANK100044 | 佑运 | 男 | 13666668888 | 2016-04-13 | B0007 |
+------------+--------+-------+-------------+------------+-------+
8 rows in set (0.00 sec)
例13-15 查询表admin中姓名最后一个字是光的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_name LIKE'%光';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100022 | 辰光 | 男 | 13813368888 | 2016-02-25 | B0006 |
| BANK100027 | 泽光 | 男 | 13819968888 | 2014-08-01 | B0010 |
+------------+--------+-------+-------------+------------+-------+
2 rows in set (0.00 sec)
例13-16 查询表admin中姓名中含有云的管理员信息,SQL语句如下所示。
mysql> SELECT * FROM admin
-> WHERE a_name LIKE'%云%';
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100016 | 觅云 | 女 | 13816668877 | 2013-07-06 | B0004 |
| BANK100028 | 云天 | 男 | 13810068888 | 2014-02-17 | B0008 |
+------------+--------+-------+-------------+------------+-------+
2 rows in set (0.00 sec)
例13-17 查询表deposite中的所有存款金额,并去除重复数据,SQL语句如下所示。
mysql> SELECT DISTINCT d_amount FROM deposite;
+----------+
| d_amount |
+----------+
| 1000.00 |
| 600.00 |
| 1500.00 |
| 3000.00 |
| 8000.00 |
| 10000.00 |
| 50000.00 |
| 100.00 |
| 2500.00 |
| 3600.00 |
| 7200.00 |
| 8800.00 |
| 15000.00 |
| 3200.00 |
| 6400.00 |
| 9800.00 |
| 12300.00 |
| 3500.00 |
+----------+
例13-18 查询表deposite中的所有数据,按存款余额升序排序,SQL语句如下所示。
mysql> SELECT * FROM deposite
-> ORDER BY d_amount ASC;
+------+--------+-------+----------+---------------------+
| d_id | c_id | b_id | d_amount | d_date |
+------+--------+-------+----------+---------------------+
| 10 | C10019 | B0001 | 100.00 | 2018-01-02 17:30:33 |
| 4 | C10011 | B0004 | 600.00 | 2018-01-02 17:30:33 |
| 1 | C10003 | B0001 | 1000.00 | 2018-01-02 17:30:33 |
| 2 | C10026 | B0007 | 1000.00 | 2018-01-02 17:30:33 |
| 3 | C10043 | B0003 | 1000.00 | 2018-01-02 17:30:33 |
| 5 | C10009 | B0001 | 1500.00 | 2018-01-02 17:30:33 |
| 11 | C10041 | B0008 | 2500.00 | 2018-01-02 17:30:33 |
| 6 | C10029 | B0010 | 3000.00 | 2018-01-02 17:30:33 |
| 16 | C10017 | B0005 | 3200.00 | 2018-01-02 17:30:33 |
| 20 | C10045 | B0007 | 3500.00 | 2018-01-02 17:30:33 |
| 12 | C10015 | B0002 | 3600.00 | 2018-01-02 17:30:33 |
| 17 | C10021 | B0007 | 6400.00 | 2018-01-02 17:30:33 |
| 13 | C10022 | B0009 | 7200.00 | 2018-01-02 17:30:33 |
| 7 | C10049 | B0009 | 8000.00 | 2018-01-02 17:30:33 |
| 14 | C10006 | B0010 | 8800.00 | 2018-01-02 17:30:33 |
| 18 | C10001 | B0002 | 9800.00 | 2018-01-02 17:30:33 |
| 8 | C10032 | B0002 | 10000.00 | 2018-01-02 17:30:33 |
| 19 | C10010 | B0006 | 12300.00 | 2018-01-02 17:30:33 |
| 15 | C10039 | B0003 | 15000.00 | 2018-01-02 17:30:33 |
| 9 | C10027 | B0003 | 50000.00 | 2018-01-02 17:30:33 |
+------+--------+-------+----------+---------------------+
例13-19 查询表deposite中的所有数据,按存款余额降序排序,若存款余额相同,按c_id升序排序,SQL语句如下所示。
mysql> SELECT * FROM deposite
-> ORDER BY d_amount DESC,c_id ASC;
+------+--------+-------+----------+---------------------+
| d_id | c_id | b_id | d_amount | d_date |
+------+--------+-------+----------+---------------------+
| 9 | C10027 | B0003 | 50000.00 | 2018-01-02 17:30:33 |
| 15 | C10039 | B0003 | 15000.00 | 2018-01-02 17:30:33 |
| 19 | C10010 | B0006 | 12300.00 | 2018-01-02 17:30:33 |
| 8 | C10032 | B0002 | 10000.00 | 2018-01-02 17:30:33 |
| 18 | C10001 | B0002 | 9800.00 | 2018-01-02 17:30:33 |
| 14 | C10006 | B0010 | 8800.00 | 2018-01-02 17:30:33 |
| 7 | C10049 | B0009 | 8000.00 | 2018-01-02 17:30:33 |
| 13 | C10022 | B0009 | 7200.00 | 2018-01-02 17:30:33 |
| 17 | C10021 | B0007 | 6400.00 | 2018-01-02 17:30:33 |
| 12 | C10015 | B0002 | 3600.00 | 2018-01-02 17:30:33 |
| 20 | C10045 | B0007 | 3500.00 | 2018-01-02 17:30:33 |
| 16 | C10017 | B0005 | 3200.00 | 2018-01-02 17:30:33 |
| 6 | C10029 | B0010 | 3000.00 | 2018-01-02 17:30:33 |
| 11 | C10041 | B0008 | 2500.00 | 2018-01-02 17:30:33 |
| 5 | C10009 | B0001 | 1500.00 | 2018-01-02 17:30:33 |
| 1 | C10003 | B0001 | 1000.00 | 2018-01-02 17:30:33 |
| 2 | C10026 | B0007 | 1000.00 | 2018-01-02 17:30:33 |
| 3 | C10043 | B0003 | 1000.00 | 2018-01-02 17:30:33 |
| 4 | C10011 | B0004 | 600.00 | 2018-01-02 17:30:33 |
| 10 | C10019 | B0001 | 100.00 | 2018-01-02 17:30:33 |
+------+--------+-------+----------+---------------------+
例13-20 查询表admin中的总记录数,SQL语句如下所示。
mysql> SELECT COUNT(*) FROM admin;
+----------+
| COUNT(*) |
+----------+
| 49 |
+----------+
1 row in set (0.00 sec)
例13-21 查询表deposite中存款余额大于5000的人数,查询结果的列名指定为total,SQL语句如下所示。
mysql> SELECT COUNT(*) AS total FROM deposite
-> WHERE d_amount>5000;
+-------+
| total |
+-------+
| 9 |
+-------+
1 row in set (0.00 sec)
例13-22 查询表deposite中存款余额的总和,SQL语句如下所示。
mysql> SELECT SUM(d_amount) FROM deposite;
+---------------+
| SUM(d_amount) |
+---------------+
| 148500.00 |
+---------------+
1 row in set (0.00 sec)
例13-23 查询表deposite中的平均存款余额,SQL语句如下所示。
mysql> SELECT AVG(d_amount) FROM deposite;
+---------------+
| AVG(d_amount) |
+---------------+
| 7425.000000 |
+---------------+
1 row in set (0.01 sec)
例13-24 查询表deposite中最多的存款余额,SQL语句如下所示。
mysql> SELECT MAX(d_amount) FROM deposite;
+---------------+
| MAX(d_amount) |
+---------------+
| 50000.00 |
+---------------+
1 row in set (0.00 sec)
例13-25 查询表deposite中最少的存款余额,SQL语句如下所示。
mysql> SELECT MIN(d_amount) FROM deposite;
+---------------+
| MIN(d_amount) |
+---------------+
| 100.00 |
+---------------+
1 row in set (0.00 sec)
例13-26 查询银行编号以及每个银行的管理员人数,SQL语句如下所示。
mysql> SELECT b_id,COUNT(*)
-> FROM admin
-> GROUP BY b_id;
+-------+----------+
| b_id | COUNT(*) |
+-------+----------+
| B0001 | 6 |
| B0002 | 7 |
| B0003 | 6 |
| B0004 | 3 |
| B0005 | 4 |
| B0006 | 5 |
| B0007 | 6 |
| B0008 | 6 |
| B0009 | 2 |
| B0010 | 4 |
+-------+----------+
10 rows in set (0.00 sec)
例13-27 查询表deposite中的b_id以及每个银行存款金额大于5000的人数,SQL语句如下所示。
mysql> SELECT b_id,COUNT(*)
-> FROM deposite
-> WHERE d_amount>5000
-> GROUP BY b_id;
+-------+----------+
| b_id | COUNT(*) |
+-------+----------+
| B0002 | 2 |
| B0003 | 2 |
| B0006 | 1 |
| B0007 | 1 |
| B0009 | 2 |
| B0010 | 1 |
+-------+----------+
6 rows in set (0.00 sec)
例13-28 查询表deposite中存款金额总和大于15000元的银行编号以及存款金额总和,SQL语句如下所示。
mysql> SELECT b_id,SUM(d_amount)
-> FROM deposite
-> GROUP BY b_id
-> HAVING SUM(d_amount)>15000;
+-------+---------------+
| b_id | SUM(d_amount) |
+-------+---------------+
| B0002 | 23400.00 |
| B0003 | 66000.00 |
| B0009 | 15200.00 |
+-------+---------------+
3 rows in set (0.00 sec)
例13-29 查询表admin中前10个管理员的信息,SQL语句如下所示。
mysql> SELECT * FROM admin LIMIT 0,10;
+------------+--------+-------+-------------+------------+-------+
| a_id | a_name | a_sex | a_phone | a_date | b_id |
+------------+--------+-------+-------------+------------+-------+
| BANK100001 | 浩然 | 男 | 13816668888 | 2012-05-02 | B0002 |
| BANK100002 | 智宇 | 男 | 13816661188 | 2014-07-22 | B0006 |
| BANK100003 | 永昌 | 男 | 13816662288 | 2016-04-08 | B0008 |
| BANK100004 | 映冬 | 男 | 13816663388 | 2011-11-09 | B0001 |
| BANK100005 | 思萱 | 女 | 13816664488 | 2011-12-04 | B0003 |
| BANK100006 | 香彤 | 女 | 13816665588 | 2012-07-11 | B0004 |
| BANK100007 | 振宇 | 男 | 13816666688 | 2016-07-13 | B0007 |
| BANK100008 | 元冬 | 男 | 13816667788 | 2014-08-14 | B0010 |
| BANK100009 | 梦蕊 | 女 | 13816669988 | 2017-09-18 | B0005 |
| BANK100010 | 罗文 | 男 | 13816668811 | 2013-02-20 | B0006 |
+------------+--------+-------+-------------+------------+-------+
10 rows in set (0.00 sec)
13.3 本章小结
本章通过一个综合案例,将前面章节所学内容进行了综合练习,有利于大家巩固所学知识,如果想熟练掌握MySQL操作,还需要大家多练习,不需要死记硬背。
- 点赞
- 收藏
- 关注作者
评论(0)