程序员之路：关系数据库的应用

database 数据库 程序员之路

绪论

1、课程目的

掌握作为程序员所需要规划、设计和使用管理数据库的基本技能。

程序员为什么要学习数据库？
所有计算机程序都是用来处理信息数据（data）的，绝大多数程序的数据都用数据库来进行存储。不懂数据库，也就没有办法很好地开发计算机程序。可以说，数据库在信息管理类的应用程序中，是举足轻重的角色。因此可以说，很好地使用数据库来组织和管理信息，是程序员的必备技能之一。

2、课程对象

有志于软件开发或数据库管理的任何人，只要满足以下条件：

• 具备初中及以上文化程度；
• 会使用电脑……

3、课程内容

课程分为三个部分：

（1）数据库基础

了解数据库基本概念及搭建学习环境。

（2）数据库的使用

通过由简到繁的多个实际案例，达到以下目标：

• 掌握数据库相关的基本概念
• 读懂设计稿
• 掌握数据的增删改查
• 了解视图、存储过程、函数及触发器

（3）数据库的设计

通过由简到繁的多个实际案例，达到以下目标：

• 掌握相关的基本概念
• 根据业务需求和样例数据规划数据表
• 建库、建表、插入样例数据

• 根据业务需求测试处理和访问数据

  规划和设计数据库过程

  

4、学习方法

• 认真观看视频，并跟随视频亲自动手实践
• 可根据学习效果调整自己的学习速度（视频教学的好处！）
• 认真思考，完成视频中的作业
• 借助互联网去学习同行们提供的知识和经验
• 主要学会“怎么做”，以后再去琢磨“为什么”
  
  

第一部分 数据库基础

第一章 数据库的基本概念与学习环境的建立

1、什么是数据库（Database）？

数据库的目的是存储并管理数据（data）。

2、什么是关系数据库（Relational Database）？

关系数据库里的数据以表（table）的形式存储，表和表之间可以建立相互参照（reference）的联系（relation）。

3、什么是关系数据库管理系统（RDBMS）？

RDBMS: Relational Database Management System.

关系数据库系统是一套计算机软件，用于管理数据库。

主要功能：

（1）安全管理

根据用户身份和权限限制用户对数据的访问。

（2）事务管理

对数据的操作都会得到一个稳定的最终状态——要么数据更改成功到达一个新的状态，要么撤消所有更改回到之前的状态。

（3）数据一致性管理

同样的数据如果出现在不同的地方，必须保持一致。

（4）多用户并发访问管理

多用户访问数据时的冲突控制。

（5）备份/恢复数据

备份数据，避免数据遗失。从备份中恢复数据。

4、什么是 SQL 语言？

SQL 语言，即结构化查询语言，是一种接近于人类自然语言的数据库专用语言。我们用它来定义数据库的各种元素，如库、表、关系、约束、触发器、存储过程、函数等等，用于这方面的语句又称作数据定义语言 DDL。我们也用它来完成数据的操纵，如对数据进行增加、删除、修改、查询等操作，用于这方面的语句又叫数据操纵语言 DML。当然，还有一类语句是用于管理数据库权限分配的，叫数据控制语言DCL，以及管理数据库事务的事务的控制语句，叫TCL。我们不用费心记住这些，这里只是做概念性的介绍，所有这些语句我们以后都会用到。

5、MySQL/MraiaDB 数据库管理系统应用架构

6、MySQL/MraiaDB 数据库管理系统管理界面

7、在 Windows 中安装 MySQL/MariaDB

（1）下载安装

MySQL社区版本下载
MariaDB 下载

（2）测试安装

如有需要，配置环境变量PATH。

第二章 管理 MySQL 数据库

1、配置文件

为服务器和客户端提供各类配置信息，以规定服务器和客户端的默认行为。
查看配置文件名及其读取顺序：

mysqladmin --help
...
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MariaDB 10.4\my.ini C:\Program Files\MariaDB 10.4\my.cnf C:\Program Files\MariaDB 10.4\data\my.ini C:\Program Files\MariaDB 10.4\data\my.cnf
...

2、管理服务

服务是一种特殊程序，它一般不提供与用户直接交互的界面，而是在后台运行，通过某种请求和响应协议来向客户端程序提供服务。

（1）服务的启动、停止

a) 通过 services.msc 服务管理程序来完成
b) 通过命令行来完成

net start mariadb
net stop mariadb

（2）用 mysqladmin 管理数据库系统

mysqladmin -h 服务器地址 -P 服务器端口 -u 用户名 -p 密码 管理命令 

-h：忽略时使用本机地址；
-P: 忽略时使用配置文件里的端口（通常为3306）；
-u: 忽略时使用当前操作系统的用户；
-p: 忽略时不使用密码，密码忽略时会提示输入。

例1：改变本机上的数据库管理系统（默认端口：3306）的 root 密码。

mysqladmin -uroot -p password

3、备份和恢复数据库

（1）备份

备份单个数据库：
mysqldump -u用户名 -p 数据库 > 备份文件名
举例：

C:\>mysql -uroot -p shop > shop.sql

备份多个数据库:
mysqldump -u 用户名 -p --databases 数据库1 数据库2 ... > 备份文件名
举例：

C:\>mysqldump -u root -p --databases test  mysql  > test_mysql_backup.sql

备份全部数据库:
mysqldump --all-databases --routines --single-transaction --quick --lock-tables=false > 备份文件名 -u root -p
举例：

C:\>mysqldump --all-databases --routines --single-transaction --quick --lock-tables=false > full-backup-2020-02-08.sql -u root -p

（2）恢复

恢复单个数据库：
mysql -u 用户名 -p 数据库名 < 备份文件
举例：

C:\>mysql -uroot -p shop < shop.sql

恢复多个数据库：
mysql -u 用户名 -p --default-character-set=数据库字符集编码 < 备份文件
举例：

C:\>mysql -uroot -p --default-character-set=utf8 < test_mysql_backup.sql

恢复全部数据库：
mysql -u 用户名 -p --default-character-set=数据库字符集编码 < 备份文件
举例：

C:\>mysql -uroot -p --default-character-set=utf8 < full-backup-2020-02-08.sql

第三章 了解 MariaDB 数据库系统

1、登录到数据库服务器

mysql [-h 服务器地址] [-P 服务器端口] [-u 用户名] [-p [密码]]

-h：忽略时使用本机地址；
-P: 忽略时使用配置文件里的端口（通常为3306）；
-u: 忽略时使用当前操作系统的用户；
-p: 忽略时不使用密码，密码忽略时会提示输入。

例如：登录到运行于本机的数据库服务器，端口为配置文件指定的窗口，用户名为 root，需要登录时提示输入密码。

mysql -u root -p

2、列出系统中管理的数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>

SQL语言用分号（;）表示一条语句的结束，换行并不会打断一条语句的完整性。在 MySQL/MariaDB 系统中，用 \g 来结束一条语句与分号作用一样，而用 \G 来结束一条语句，会把输出结果按竖式排列，便于显示查看过宽的输出结果。

information_schema, mysql, performance_schema 是三个数据库管理系统自用的数据库。
test 是一个用于测试的数据库，任何人都有足够权限对它操作，为安全起见，通常会被删除。

3、进入某个数据库

如：进入 mysql 数据库。

MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]>

use: 使用
注意提示符的变化。

4、列出数据库中的数据表

如：列出 mysql 数据库中的数据表。

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)
MariaDB [mysql]>

show: 显示，展示

5、查看数据库系统的用户

MariaDB [mysql]> select Host, User, Password from user;
+-----------------+------+-------------------------------------------+
| Host            | User | Password                                  |
+-----------------+------+-------------------------------------------+
| localhost       | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| desktop-f6ge8f3 | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| 127.0.0.1       | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| ::1             | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| %               | root | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+-----------------+------+-------------------------------------------+
5 rows in set (0.001 sec)
MariaDB [mysql]>

Host（主机）: 用户可登录的地点（计算机网络地址）。
User（用户）: 用户名。
Passowrd（密码）: 密码（已加密）。

6、查看系统中的字符集编码的排序规则

show charset;
show character set;
show chaset like '%utf8%';
show collation;
show collation like '%utf8%';

character set: 字符集（在计算机内部把文字编码成数字的方案，同一个方案编码的字符，只能用同一种方案解码）
collation: 排序规则（如汉字在字典中就有按笔划排序、按拼音排序等规则）

7、查看系统使用的字符集

MariaDB [(none)]> show variables like 'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | gbk                                           |
| character_set_connection | gbk                                           |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | gbk                                           |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.4\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]>

8、断开连接

MariaDB [(none)]>exit

第二部分 数据库的使用

在这一部分，我们将读懂别人的数据库设计，了解数据库内的各种元素：表及表间关系、视图、触发器、函数、存储过程等。更重要的是，我们将利用 SQL 语言去操纵数据库里的数据，实现数据库设计需求文档中所需要实现的各种业务操作。

充分了解特定场景下别人根据需求对数据库所做出的设计，熟练地操作别人数据库里的数据，对我们将来在类似场景下根据需求自行设计数据库具有重要的启发作用。

第四章 预备知识：对数据库表及其关系的认识

4.1 对数据库表的大致要求

为了正确地处理信息，数据库对表是有一定要求的。

4.1.1 每一列数据拥有自己的数据类型

在计算机内部，不同的数据类型表示的事物不同，运算方法也不同。为了正确处理信息，每一列都需要指明其数据类型。数据类型主要分四大类：表示数量多少的数值类型、表示文字信息的字符串类型、表示日期和时间的日期时间型以及表示二进制字节串的大二进制对象类型（图像、音频、视频等二进制字节串）。

参见：附录一 MySQL/MariaDB 常用数据类型速查表

4.1.2 每行记录都能和其它行区分开来

看看以下表格：

请问，第一行和第三行是怎么回事儿？
可能的情况有：
（1）填写重复了，那么月薪必然有一处是错误的，需要重新确定，并删除一行；
（2）填写不重复，公司就有两个同名同姓同性别同出生年月的两个员工！
到底是哪种情况？
因此，数据库表需要杜绝这种情况。
造成这种情况的是员工的属性中没有可以用来唯一区别彼此的属性，甚至多个属性组合起来也不能区别彼此。
所以，为了解决此问题，就要找到一个能够区别彼此的员工属性，比如“身份证号”就是一个很好的属性，因为每个员工的身份证号是绝对不可能相同的。

这样的一列（或者几列的组合）叫做主键（primary key）。

4.2 表的参照关系

我们把数据库表描述的事物叫实体（entity），在关系数据库中，实体之间具有参照关系。描述这种关系的图叫实体关系图（ER 图）。
本教程全程采用乌鸦脚画法绘制 ER 图。

4.2.1 一对一关系

原表（每位员工记录其基本信息和一个银行帐号用于发放工资）：

按关注拆分表（因查看需求后，发现工资银行帐户只能由部分用户如财务处使用，而员工基本信息供所有用户使用）

雇员基本信息

工资卡帐户

这时，工资卡帐户中的“雇员编号”指向了雇员基本信息表中的“雇员编号”。我们说工资卡帐户的“雇员编号”是外键（foreign key）,它参照（references）了雇员基本信息表中的“雇员编号”。表中的“雇员编号”要求必须有值且不能出现重复值。

在做各种查询和统计的时候，如果涉及到两张表上的数据，先用两张表结合起来得到“原表”那样的“结果集”，再在此“结果集”上做查询和统计。

ER 图：

4.2.2 一对多关系

原表，借用以上联系人例子（需要记录一个联系人的所有手机号，即一个联系人拥有多个手机号，而一个手机号只属于一个联系人）：

拆分为两张表：

联系人基本信息

联系人手机号

此表中的“联系人编号”是外键，参照表“联系人”中的“编号（主键）”。此关系下的外键的值可以重复出现，是否可以为“空”要根据具体情况来决定。

在做各种查询和统计的时候，如果涉及到两张表上的数据，先用两张表结合起来得到“原表”那样的“结果集”，再在此“结果集”上做查询和统计。

ER 图：

4.2.3 多对多关系

一个选手可以参加多个赛项，一个赛项可有多个选手参加。
原表（大量重复数据，占用存储空间，且有数据不一致的可能）：

拆分成三张表：

选手信息

赛项信息

选手成绩

上表中“选手编号”和“赛项编号”的组合值应该唯一（不能出现重复），且二者都是外键，都不应该出现“空值”。

在做各种查询和统计的时候，如果涉及到多张表上的数据，先用三张表结合起来得到“原表”那样的“结果集”，再在此“结果集”上做查询和统计。

ER图：

第五章 案例一 单表：雇员信息

数据库名称： c1_emp_db（ 备份下载页面）

5.1 解读设计稿

5.1.1 E-R图（实体关系图）

实体（Entity）
数据库里的每一张表都存储了描述某个事物的信息，我们把这被描述的事物叫“实体”。

E-R 图
实体-关系图，用于描述各个实体之间的依赖关系。

5.1.2 数据字典

数据字典
用于详细数据库中的各个元素，包括对实体、触发器、视图、函数与存储过程等的详细定义。

（1）表定义

表名：雇员信息（ employees）

（2）触发器

触发器（trigger）

是一段 SQL 语言程序，在表的数据变动时会被自动触发执行。常常用来保证数据的一致性。

表名：雇员信息（ employees）

（3）视图

视图（view）
相当于一个查看数据的窗口，其中的列可以来自多个表。视图中的数据是只读的。视图一般用于组织来自多个表的数据的结果集供用户读取。

视图名称：

（4）存储过程与函数

存储过程（stored procedure）
被存储的一段 SQL 程序，在需要时可被执行完成特定任务。存储过程可返回结果集。

函数（function）
被存储的一段 SQL 程序，在需要时可被执行完成特定任务。函数只能返回单个结果数据。

（5）用户及权限

5.1.3 需求分析中的数据查询需求

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

5.2 查看数据

5.2.1 查看表的信息

列出当前数据库的所有表：

show tables;
show full tables; -- 可区分普通表和视图

查看表（比如表： employees）的结构信息：

describe `employees`; -- 可用简短方式 desc

查看表（比如表：employees）的建立脚本：

show create table `employees`;

5.2.2 查看所有数据

语法：
select 逗号分隔的列名列表 from 表名;
其中，逗号“分隔的列名列表”可以是全部列也可以是部分列，全部列也可用 * 号代替。

select `id`,`sid`,`name`,`gender`,`birth_date`,`emp_date`,`salary`
from `employees`;

select:选择，from: 从

或：

select * from `employees`;

5.2.3 记录排序

查看所有记录并按月薪从高到低排序。

select * from `employees` order by `salary` desc;

order: 排序
by: 依据
desc: descend 下降

查看所有记录并按月薪从低到高排序。

select * from `employees` order by `salary` asc;

asc: ascend, 上升。默认，可省略

查看所有记录并按姓名拼音升序（a～z）排序。

select * 
from `employees` 
order by convert(`name` using gbk); 

convert: 转换
using: 使用
gbk: 汉字内码扩展规范名称

5.2.4 修改返回的列名

select `id` as `编号`, `name` as `姓名`, `department` as `部门`, `salary` as `月薪` 
from `employees`;

5.2.5 限定返回的记录数

select * from `employees` limit 10;

limit: 限定，极限

5.2.6 指定跨过的记录数

select * from `employees` limit 30, 10;

或：

select * from `employees` limit 10 offset 30;

5.2.7返回不重复的数据

查看表中的所有部门（不重复）：

select distinct `departmen`t from `employees`;

distinct: 截然不同的

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

5.2.8 筛选符合条件的记录

参阅“MySQL/MariaDB 比较运算与逻辑运算”

找出姓名包含“志”字的员工。

select * from `employees` where `name` like '%志%';

找出姓名开始于“李”的员工。

select * from `employees` where `name` like '田%';

找出姓名结尾是“明”的员工。

select * from `employees` where `name` like '%明';

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

选出月薪不低于 3000 且不高于 5000 的雇员并按月薪降序排序。

select * from `employees` 
where `salary`>=3000 and `salary`<=5000 
order by `salary` desc;
-- 或者
select * from `employees` 
where `salary` between 3000 and 5000 
order by `salary` desc;

where: 哪里；在那里（在表那里）

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

选出月薪不低于 3000 的男性雇员。

select * from `employees` 
where `salary`>=3000 and `gender`='男';

5.3 统计与计算数据

5.3.1 统计

（1）统计雇员总数

select count(*) from `employees`;

count：计数

（2）统计月薪总额

select sum(`salary`) from `employees`;

sum：总计，总和

（3）计算月薪平均值

select avg(`salary`) from `employees`;

avg: average, 平均值

同时统计以上三项数据

select count(*) as `雇员数`, sum(`salary`) as `月薪总额`, avg(`salary`) as `平均月薪` from `employees`;

（4）按部门分组统计

select department as 部门, count(*) as 雇员数, sum(salary) as 月薪总额, avg(salary) as 平均月薪 from employees group by department;

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

5.3.2 计算

（1）在查询输出中显示员工的年龄

select id,name,department,birth_date,TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age from employees;

当前日期：CURDATE()，Current Date（当前日期）。
计算两个日期之间的距离：

TIMESTAMPDIFF(时间单位, 日期时间 1, 日期时间 2)

时间单位：FRAC_SECOND（毫秒），SECOND（秒），MINUTE（分），HOUR（小时） ，DAY（天），WEEK（周），MONTH（月），QUARTER（季度）， YEAR（）。

（2）查找某年龄段的员工并按年龄进行降序排序

查找年龄介于 30 到 40 岁之间的员工并按年龄进行降序排序

select id,name,department,birth_date,TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age 
from employees
where TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) between 30 and 40
order by age desc;

注意： where 子句中不能用列的别名。

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

（3）找出最近一年入职的员工

select * from employees
where timestampdiff(DAY,emp_date,curdate())<366;
-- 或者
select * from employees
where emp_date>=date_sub(now(),interval 1 year);

日期减法运算：
date_sub(日期时间，interval 数量 单位）
参见日期减法运算说明

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

（4）统计员工平均年龄

select avg(timestampdiff(YEAR,birth_date,now())) 
from employees;

• 能够按以下指定条件查询筛选记录；
  
  • 列出公司的所有部门
  • 按姓名查找员工
  • 查找某年龄段的员工并按年龄进行升序、降序排序
  • 查找某月薪段的员工并按月薪进行升序、降序排序
  • 找出最近一年入职的所有员工
• 能够按以下指定条件统计信息
  
  • 按部门统计员工人数、月薪总额及平均月薪；
  • 按部门统计员工的平均年龄

5.4 增、删、改数据

5.4.1 增加记录

语法：
insert into 表名 [(列名 1，列名 2，...)] values (值 1，值 2，...)[,(值 1，值 2，...), ...];

• 表名后的列名可全部省略，省略时相当于按列自然顺序全部罗列出来了；
• 表名后的列名不用和表中列的自然顺序一致，也不需要全部罗列出来（那些必填的且没有默认值的列一般都要列出来）；
• 自动增长的列不需要列出来，当然也可列出来并指定具体值（但值重复会出错），也可指定空值（NULL），这样它会自己填入增长的数值；
• 表名后的列名的顺序必须和 values 后的值的顺序一致，并且一一对应。

插入时可根据需要只罗列出部分列，必须罗列出来的列是：

• 不可为空（NULL）
• 且默认值是 NULL
• 且不是自动增长的列

insert into `employees` (sid, name, department,emp_date,salary) values ('530103198803221221','李倩倩','研发部','2020-02-08',3500), ('530101199511232312','赵全','研发部','2020-02-08',3000);

发现数据库中的性别和出生日期被正确填入了！
这是触发器的作用。

初识触发器：触发器是一段 SQL 程序，它会被设定在某种情况下自动执行，如插入记录前、插入记录后、更新记录前、更新记录后、删除记录前、删除记录后等，用于在这些时间点做一些特定的工作来保证数据的完整性、联动性等。

查看触发器：

-- 列出数据库中的所有触发器
show triggers \G
-- 查看某个触发器的详细 SQL 定义
-- show create trigger 触发器名\G
MariaDB [c1_emp_db]> show create trigger trigger_for_ins\G
...
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_for_ins 
BEFORE INSERT 
ON `employees`
FOR EACH ROW
BEGIN
    SET NEW.birth_date=CONCAT(SUBSTRING(NEW.sid,7,4),'-',SUBSTRING(NEW.sid,11,2),'-',SUBSTRING(NEW.sid,13,2));
    IF CAST(SUBSTRING(NEW.sid,17,1) AS INT) % 2 = 0 THEN
        SET NEW.gender = '女';
    ELSE
        SET NEW.gender = '男';
    END IF;
END
...

concat(字符串1, 字符串2, ...) 函数：将逗号分隔的字符串串接起来，构成更长的字符串。如：concat('I ','am ','so ','happy','!') 将构成新的长字符串：'I am so happy！'。
sunstring(字符串, 起始位置, 字符个数) 函数，从字符串中取子串，第一个参数是字符串，第二个参数是起始位置（从1开始），第三个参数是要几个字符，省略则取到末尾。如：substring('I am so happy!', 3, 2) 会取得 'am', substring('I am so happy!', 3) 会取得 'am so happy!'
cast(值 as 类型) 函数，将其中的值转换成其它数据类型，如：cast('123' as integer), 会把字符串 '123' 变成整数数值的 123。
% 运算符，取余数。如 81%2 得到 1，8%3 得到 2。

5.4.2 修改记录

update `employees` set name='杨兰', gender='女' where id=...;

update: 更新

5.4.3 删除记录

（1）删除全部记录

没有确认机会，慎重！慎重！！慎重！！！

delete from employees;

delete: 删除

（2）按条件删除记录

delete from employees where ...;

第六章 案例二 一对多关系：部门与员工管理

数据库： c2_dept_db (备份下载)

6.1 基础知识：表的连接查询

如何解读 E-R图中的连接。

涉及数据库： misc (请用 root 登录使用)

use misc;

城市列表：

MariaDB [misc]> select * from cities;
+----+--------+
| id | name   |
+----+--------+
|  1 | 昆明   |
|  2 | 上海   |
|  3 | 北京   |
+----+--------+
3 rows in set (0.000 sec)
MariaDB [misc]>

与会者列表：

MariaDB [misc]> select * from attendees;
+----+-----------+--------------+
| id | name      | from_city_id |
+----+-----------+--------------+
|  1 | 李强      |            1 |
|  2 | 周小妮    |            1 |
|  3 | 吴汉明    |            2 |
|  4 | 郑治国    |         NULL |
+----+-----------+--------------+
4 rows in set (0.000 sec)
MariaDB [misc]>

与会者的 from_city_id 填的是某个城市的编号（id）。
所以，与会者表参照了城市表。
从数据可以看出，一名与会者可对应一个城市（来自一个城市），而一个城市可有多名与会者。城市与与会者二者是一对多的关系。同时我们也看到，有的与会者尚不知来自何处，有的城市根本没有与会者。

一名与会者可有零到一个城市与之对应，而一个城市可有零到多个与会者。我们把城市和与会者的关系叫做：一对多的关系。

现在有四种方法把两个表连接起来显示信息。

注意，以下将用到 left join（左连接） 和 right join（右连接） 来连接两张表。
对应左右连接，谁是左边的表或右边的表？

谁是左边或右边的表？
出现在查询语句中的 from 后第一张表就是左表，后一张表就是右表（因为语句是从左向右写的！！！）。
因此，如果在 from 后把表的位置变动一下，结果就不一样了。

（1）显示所有城市及与会者

注意： 只有昆明、上海有与会者，北京没有。

MariaDB [misc]> select cities.*, attendees.*
    -> from cities left join attendees
    -> on cities.id=attendees.from_city_id;
+----+--------+------+-----------+--------------+
| id | name   | id   | name      | from_city_id |
+----+--------+------+-----------+--------------+
|  1 | 昆明   |    1 | 李强      |            1 |
|  1 | 昆明   |    2 | 周小妮    |            1 |
|  2 | 上海   |    3 | 吴汉明    |            2 |
|  3 | 北京   | NULL | NULL      |         NULL |
+----+--------+------+-----------+--------------+
4 rows in set (0.000 sec)
MariaDB [misc]>

from 后有两张表，cities left join attendees。left join，左边连接，意思是列出所有左边的表（cities）的记录，和对应右边的表（attendees）的记录，没有对应的就填空值（NULL）。

注意结果：
1、城市全部罗列出来了，尽管有的城市（北京）尚没有与会者；
2、与会者并没有全部罗列出来，因为有的与会者尚不知来自何方（郑治国）。

（2）显示城市及所有与会者

注意： 与会者“郑治国”尚不知来自哪个城市。
以下查询使用了表的别名简化输入。

MariaDB [misc]> select c.*, a.*
    -> from cities c right join attendees a
    -> on c.id=a.from_city_id;
+------+--------+----+-----------+--------------+
| id   | name   | id | name      | from_city_id |
+------+--------+----+-----------+--------------+
|    1 | 昆明   |  1 | 李强      |            1 |
|    1 | 昆明   |  2 | 周小妮    |            1 |
|    2 | 上海   |  3 | 吴汉明    |            2 |
| NULL | NULL   |  4 | 郑治国    |         NULL |
+------+--------+----+-----------+--------------+
4 rows in set (0.000 sec)
MariaDB [misc]>

from 后有两张表，cities rigth join attendees。rigth join，右边连接，意思是列出所有右边的表（cities）的记录，和对应左边的表（attendees）的记录，没有对应的就填空值（NULL）。

注意结果：
1、与会者全部罗列出来了，尽管有的与会者（郑治国）尚不知来自何处；
2、城市并没有全部罗列出来，因为有的城市尚没有与会者（北京）。

（3）只显示来自已知城市的与会者

MariaDB [misc]> select c.*, a.*
    -> from cities c inner join attendees a
    -> on c.id=a.from_city_id;
+----+--------+----+-----------+--------------+
| id | name   | id | name      | from_city_id |
+----+--------+----+-----------+--------------+
|  1 | 昆明   |  1 | 李强      |            1 |
|  1 | 昆明   |  2 | 周小妮    |            1 |
|  2 | 上海   |  3 | 吴汉明    |            2 |
+----+--------+----+-----------+--------------+
3 rows in set (0.000 sec)
MariaDB [misc]>

from 后有两张表，cities inner join attendees。inner join， 内部连接，意思是列出左右边能够连接起来的记录。

注意结果：
1、未知来自何处的与会者（郑治国 ）没有被列出来；
2、没有与会者的城市（北京）没有被列出来。

（4）全部城市和与会者都需要显示

MariaDB [misc]> select c.*,a.* 
    ->from cities c left join attendees a on c.id=a.from_city_id 
    ->union 
    ->select c.*,a.* 
    ->from cities c right join attendees a on c.id=a.from_city_id;
+------+--------+------+-----------+--------------+
| id   | name   | id   | name      | from_city_id |
+------+--------+------+-----------+--------------+
|    1 | 昆明   |    1 | 李强      |            1 |
|    1 | 昆明   |    2 | 周小妮    |            1 |
|    2 | 上海   |    3 | 吴汉明    |            2 |
|    3 | 北京   | NULL | NULL      |         NULL |
| NULL | NULL   |    4 | 郑治国    |         NULL |
+------+--------+------+-----------+--------------+
5 rows in set (0.001 sec)

6.2 解读设计稿

6.2.1 E-R 图

6.2.2 数据字典

（1）表定义

表名：部门（ departments）

表名：员工（employees）

（2）触发器

表名：

（3）视图

视图名称：

（4）存储过程与函数

（5）用户及权限

6.2.3 从需求文档整理的数据查询需求

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

6.3 数据查询、统计与维护

6.3.1 查询

（1）列出所有部门及其员工，全部部门都要列出

全部部门都要列出，如果部门在 from 子句中位于左边，则是左连接：

select d.*, e.* 
from departments d left join employees e
on d.id=e.department_id;

如果部门在 from 子句中位于右边，则是右连接：

select d.*, e.* 
from employees e right join departments d
on d.id=e.department_id;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（2）列出所有员工及其所在部门，全部员工都要列出

全部员工都要列出，如果员工在 from 子句中位于左边，则是左连接：

select d.*, e.* 
from employees e left join departments d
on d.id=e.department_id;

如果员工在 from 子句中位于右边，则是右连接：

select d.*, e.* 
from departments d right join employees e
on d.id=e.department_id;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（3）列出所有部门的所有员工，忽略无员工部门及无部门员工

因为只列出有员工的部门和有部门的员工，采用内连接，表的出现顺序无关。

select d.*, e.* 
from departments d inner join employees e
on d.id=e.department_id;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（4）合并列出所有部门和所有员工，包括无员工部门及无部门员工

合并列出，双方内容都需要完全列出，要用完整外连接。在有的数据库系统里，有完整外连接的语句，如：

select d.*, e.* 
from departments d full outer join employees e
on d.id=e.department_id;

在 MariaDB/MySQL 中我们可以用以下语句来实现：

select d.*, e.* 
from departments d left join employees e
on d.id=e.department_id
union
select d.*, e.* 
from departments d right join employees e
on d.id=e.department_id;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（5）列出没尚不属于任何部门的员工

select * from employees where department_id is null;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（6）列出尚无员工的部门

select * from departments
where (select count(*) from employees where department_id=departments.id)=0;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（7）找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序

因为查询涉及两张表的内容，查询中需要将两张表连接得到一个结果集，再从结果集中筛选记录。两步是并到同一语句中的。

select e.id, e.name, e.birth_date, 
       timestampdiff(YEAR, e.birth_date, curdate()) as age, d.name
from departments d inner join employees e
on e.department_id=d.id -- 到此为止为连接两表得到一个结果集
where  d.name='公关部' and -- 过滤结果集
       timestampdiff(YEAR, e.birth_date, curdate()) between 25 and 35
order by age asc  -- 排序
;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（8）列出员工人数在 3 人以上的部门及其员工

因为查询涉及两张表的内容，查询方法同上。

 select e.*, d.name 
 from departments d inner join employees e 
 on e.department_id=d.id -- 到此为止为连接两表得到一个结果集
 where (select count(*) from employees e1 where e1.department_id=d.id) > 3
 ;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

6.3.2 统计

（1）按性别分组统计员工人数

select gender, count(*) 
from employees
group by gender;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

（2）按出生年月分组统计员工人数

select concat(year(birth_date),'-',month(birth_date)) as 出生年月, count(*) as 人数
from employees
group by 出生年月;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

(3) 按部门统计员工人数（需要显示部门名称）

因为统计涉及两张表的内容，需要将两张表连接得到一个结果集，再从结果集中进行统计。两步是并到同一语句中的。

select d.name, count(*)
from departments d left join employees e
on e.department_id=d.id -- 连接两表得到一个结果集
group by d.id  -- 分组统计
;

• 能够按以下指定条件查询筛选记录；
  
  • 列出所有部门及其员工，全部部门都要列出
  • 列出所有员工及其所在部门，全部员工都要列出
  • 列出所有部门的所有员工，忽略无员工部门及无部门员工
  • 合并列出所有部门和所有员工，包括无员工部门及无部门员工
  • 列出没尚不属于任何部门的员工
  • 列出尚无员工的部门
  • 找出“公关部”的所有年龄在 25 到 35 岁的员工，结果中显示年龄并按年龄升序排序
  • 列出员工人数在 3 人以上的部门及其员工
• 能够按以下指定条件统计信息
  
  • 按性别分组统计员工人数
  • 按出生年月分组统计员工人数
  • 按部门统计员工人数（需要显示部门名称）

6.3.3 数据维护

添加一名员工到“市场部”。

insert into employees(name,gender,birth_date,phone,department_id)
values ('王小小','女','1998-12-20','13087676754',(select id from department where name='市场部'));

练习：

（1）列出员工人数在 5 人以下的部门。
（2）更新一条部门的 id 号，观察所属员工的部门 id 是否一起发生了变化。
（3）用一条语句新增两个部门，"信息技术部"和"采购部"。
（4）各添加一名员工到“信息技术部”和“采购部”。
（5）把第 10 号员工的姓名改成'顾晓'，出生年月改成'1988-10-10'。

第七章 案例三 一对多关系特例：一对一 会员管理

数据库： c3_member_db (备份下载)

一对一关系是一对多关系的一个特例，它是通过限定外键为唯一值来达到的。一对一关系的两个实体通常用于描述同一事物的不同侧面，便于分离业务关注点及针对用户分配不同的处理和访问权限。

7.1 解读设计稿

7.1.1 E-R 图

连接示意图

标准 E-R 图

7.1.2 数据字典

（1）表定义

表名：会员信息（ members）

表名：会员详情（ member_details）

（2）触发器

表名：雇员信息（member_details）

（3）视图

视图名称：member_infos

（4）存储过程与函数

（5）用户及权限

7.1.3 整理的数据查询需求

• 能够按以下指定条件查询筛选记录；
  
  • 根据性别列出所有会员，并列出其详情
  • 按姓名查找会员，并列出其详情
  • 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
  • 找出入会一年以上的会员（含详情）
  • 找出尚没有“会员详情”的会员
• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.2 查询记录

7.2.1 根据性别列出所有会员，并列出其详情

select m.*, d.*
from members m left join member_details d
on m.id=d.member_id
where m.gender='男';

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.2.2 按姓名查找会员，并列出其详情

select m.*, d.*
from members m left join member_details d
on m.id=d.member_id
where m.name like '%志%';

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.2.3 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序

查找 30到 40 岁之间的会员并按年龄升序排序。

使用函数 get_member_age()

select m.*, d.*, get_member_age(d.sid) as age
from members m left join member_details d
on m.id=d.member_id
where get_member_age(d.sid) between 30 and 40;

使用视图 member_infos

select * from member_infos where age between 30 and 40;

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.2.4 找出入会一年以上的会员（含详情）

利用视力 member_infos

select * from member_infos
where join_date < date_sub(now(), interval 1 year);

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄段（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.2.5 找出尚没有“会员详情”的会员

子查询
出现在查询语句里作为查询语句的一部分的一条完整的查询语句。

select * from members
where (select count(*) from member_details where member_id=members.id)=0;

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄段（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

练习

1、找出入会一个月内的会员（含详情）
2、找出年龄在 30 岁以上的女性会员（含详情）

7.3 统计与计算

7.3.1 按性别分组统计会员人数

select gender, count(*) from members
group by gender;

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.3.2 按年龄段（10+，20+，30+，40+ ...）分组统计会员人数

利用视图 member_infos：

select floor(age/10)*10 as age_range, count(*) from member_infos
group by age_range;

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

7.3.3 按入会年份统计会员人数

select year(join_date) as year, count(*)
from members
group by year;

• 能够按以下指定条件查询筛选记录；
• 根据性别列出所有会员，并列出其详情
• 按姓名查找会员，并列出其详情
• 查找某年龄段的会员（含详情）并按年龄进行升序、降序排序
• 找出入会一年以上的会员（含详情）
• 找出尚没有“会员详情”的会员

• 能够按以下指定条件统计信息
  
  • 按性别分组统计会员人数
  • 按年龄（10+，20+，30+，40+ ...）分组统计会员人数
  • 按入会年份统计会员人数

练习

1、列出所有会员编号、身份证号、姓名、性别、年龄（用函数及相关表、用视图各实现一次）。

7.4 增、改、删

7.4.1 增加：确认一对一关系

给已经有会员详情的会员记录再添加一条会员详情。

MariaDB [db_2_2]> select * from member_details where member_id=10\G
*************************** 1. row ***************************
           id: 7
          sid: 44132319940218504X
      address: 贵阳秀英区
      contact: 何辉
contact_phone: 13165155723
    member_id: 10
1 row in set (0.000 sec)
MariaDB [db_2_2]> insert into member_details(sid,address,member_id)
    -> values('520101198012212451','昆明五华区',10);
ERROR 1062 (23000): Duplicate entry '10' for key 'member_details_member_id_unique'
MariaDB [db_2_2]>

ERROR: 错误
Duplicate entry: 重复的条目
unique: 独一的，唯一的

7.4.2 增加：确认增加一条会员详情给一位不存在的会员失败

MariaDB [db_2_2]> insert into member_details(sid,address,member_id)
    -> values('520101198012212451','昆明五华区',1000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_2_2`.`member_details`, CONSTRAINT `member_details_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
MariaDB [db_2_2]>

7.4.3 修改身份证号：确认更新时触发器产生了作用

select * from members m inner join member_details d
on m.id = d.member_id;

选其中一条记录进行修改。

update member_details set sid='(身份证号)' where id=编号;

7.4.4 删除会员信息：确认会员详情会被级联删除

select * from members m inner join member_details d
on m.id = d.member_id;

选其中一条记录进行删除。

delete from members where id=编号;

练习

1、自行增加一条会员记录，并为其添加对应的详情记录。
2、修改一条会员基本信息记录的 ID 号，观察其详情记录的外键变化。

第八章 案例四 一对多自关联：行政区划管理

数据库： c4_division_db 数据库备份

8.1 解读设计稿

8.1.1 E-R 图

8.1.2 数据字典

（1）表定义

表名：区划行政（ divisions）

（2）触发器

表名：区划行政（ divisions）

（3）视图

视图名称：

（4）存储过程与函数

（5）用户及权限

8.1.3 从需求文档整理的数据查询需求

• 能够按以下指定条件查询筛选记录；

  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称

• 能够按以下指定条件统计信息

  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

​

8.2 数据查询、统计与维护

8.2.1 数据查询

（1）获取所有顶级行政区划

select * from divisions where parent_id is null;

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

（2）获取所有最末节行政区划

select * from divisions d1
where (select count(*) from divisions d2 where d2.parent_id=d1.id)=0;

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

（3）根据分类 ID 获取完整的地名级联名称

使用函数：get_addr_by_id()

MariaDB [db_2_4]> select get_addr_by_id(30001);
+-----------------------------------------+
| get_addr_by_id(30001)                   |
+-----------------------------------------+
| 广东省>韶关市>曲江区>乌石镇             |
+-----------------------------------------+
1 row in set (0.000 sec)
MariaDB [db_2_4]>

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

（4）根据类别关键字获取所有相关的地名级联名称

使用存储过程：get_addr_by_name()

MariaDB [db_2_4]> call get_addr_by_name('盘龙');
+-------+-----------------------------------------------------------------+
| id    | address                                                         |
+-------+-----------------------------------------------------------------+
| 42509 | 河南省>驻马店市>确山县>盘龙街道办事处                           |
| 42509 | 湖北省>武汉市>黄陂区>盘龙城经济开发区                           |
| 42509 | 四川省>广元市>利州区>盘龙镇                                     |
| 42509 | 四川省>南充市>南部县>盘龙镇                                     |
| 42509 | 云南省>昆明市>盘龙区                                            |
| 42509 | 云南省>昆明市>盘龙区>盘龙区阿子营街道办事处                     |
| 42509 | 云南省>昆明市>盘龙区>盘龙区滇源街道办事处                       |
| 42509 | 云南省>文山壮族苗族自治州>砚山县>盘龙彝族乡                     |
| 42509 | 重庆市>荣昌区>盘龙镇                                            |
| 42509 | 重庆市>云阳县>盘龙街道办事处                                    |
+-------+-----------------------------------------------------------------+
10 rows in set (0.011 sec)
Query OK, 47 rows affected (0.011 sec)
MariaDB [db_2_4]>

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

8.2.2 统计

（1）统计共有几个顶级行政

select count(*) from divisions where parent_id is null;

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

（2）统计共有几个末级行政区划

select count(*) from divisions d1
where (select count(*) from divisions d2 where d2.parent_id=d1.id)=0;

• 能够按以下指定条件查询筛选记录；
  
  • 获取所有顶级行政区划
  • 获取所有最末节行政区划
  • 根据区划 ID 获取完整的地名级联名称
  • 根据区划关键字获取所有相关的地名级联名称
• 能够按以下指定条件统计信息
  
  • 统计共有几个顶级行政
  • 统计共有几个末级行政区划

第九章 案例五 多对多关系：通讯录管理

数据库： c5_contact_db 下载数据库备份

9.1 解读设计稿

9.1.1 E-R 图

9.1.2 数据字典

（1）表定义

表名：联系人（contacts）

表名：分组（groups）

表名：联系人-分组（contact_group）

（2）触发器

表名：

（3）视图

视图名称：

（4）存储过程与函数

（5）用户及权限

9.1.3 从需求文档整理的数据查询需求

• 能够按以下指定条件查询筛选记录；

  • 列出某个分组的所有联系人
  • 列出某个联系人所在的分组
  • 按“联系人编号、姓名、手机号、所在分组”列出所有联系人（一列显示多个分组，逗号分隔）
  • 列出尚不属于任何分组的联系人
  • 列出尚无联系人的分组
• 能够按以下指定条件统计信息
  
  • 按分组统计联系人人数
  • 列出每个联系人的分组数

9.2 数据查询

9.2.1 列出某个分组的所有联系人

select g.name,c.name,c.mobile,c.email
from contacts c inner join contact_group cg 
on c.id=cg.contact_id
inner join groups g
on cg.group_id=g.id
where g.name='业务';

简洁写法：

select g.name,c.name,c.mobile,c.email
from contacts c, contact_group cg, groups g
where 
c.id=cg.contact_id and 
cg.group_id=g.id and 
g.name='业务';

• 能够按以下指定条件查询筛选记录；
  
  • 列出某个分组的所有联系人
  • 列出某个联系人所在的分组
  • 按“联系人编号、姓名、手机号、所在分组”列出所有联系人（一列显示多个分组，逗号分隔）
  • 列出尚不属于任何分组的联系人
  • 列出尚无联系人的分组
• 能够按以下指定条件统计信息
  
  • 按分组统计联系人人数
  • 列出每个联系人的分组数

9.2.2 列出某个联系人所在的分组

select g.name,c.name,c.mobile,c.email
from groups g inner join contact_group cg 
on g.id=cg.group_id
inner join contacts c
on cg.contact_id=c.id
where c.name='李明';