程序员之路：关系数据库的应用（3e）

database 数据库 程序员之路

绪论

我们生活在信息时代，一个由传统工业生产转向了基于信息和计算机的工业生产时代。电子技术、通信技术、计算机技术与网络技术的发展，使人类能够为利用信息而快速、及时、大量地收集、处理、存储和检索信息。对信息的利用，大大提高了信息时代的社会生产力和组织决策成功率。
什么是信息？直白地说，信息就是人们对事物的感知所进行的描述，这些描述可以以不同的形式进行呈现，如：文字、图像等。如果要用计算机对信息进行处理，信息在计算机中有一个具体的名称——数据（Data）。因此，计算机中的信息处理即数据处理。
这里需要注意的是，“数据”（data）不等同于“数字”（number），“数据”代表了具备多样性的信息，如：一个数字、一个电话号码、一首歌、一个日期、一张图片、一部电影等等……
在计算机中，负责组织、维护、检索信息的软件系统叫做数据库管理系统（Database Management System），数据库（Database）即存储数据的地方。

1、课程目的 

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

程序员为什么要学习数据库？

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

2、课程对象

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

• 具备初中及以上文化程度；
• 会使用计算机

3、课程内容

为便于读者学习，本课程将内容划分为三个部分：

（1）数据库基础

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

（2）数据库的使用

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

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

使用数据库的过程：

（3）数据库的设计

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

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

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

  规划和设计数据库过程：

如此安排三个部分的内容，是基于这样一个自然规律：当你熟悉了一种事物，你才能去创造和改进这种事物。比如，只有一个会开车的人，才会针对驾驶体验去重新设计和改进驾驶操作环境；让一个从来没有开过车的人针对驾驶体验去重新设计和改进驾驶操作环境是不可相像的。

4、学习方法

学习一项技能的根本方法是“做中学”（Learning by doing）, 因此在学习过程中应做到以下几点：

• 循序渐进，在完全掌握前面内容的情况下，再进行后面的内容的学习；
• 针对各章节内容的实例，请在计算机上进行实际操作；
• 认真完成各章节布置的作业；
• 如果课程中有不明白的地方，要会主动通过网络进行检索学习。
  
  

第一部分 数据库基础

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

1.1 手工管理数据的常识

1.2 数据库的基本概念

2.1 数据库（Database）

数据库的目的是存储并管理数据（data）。在数据库中，数据是以表（table）的形式存储的。与生活中见到的表格一样，数据库中的表也是由行（row）和列（column）构成的，一列表示某种信息，如“姓名”、“性别”等，而由多列构成的一行就构成了一条记录。
一个数据库里可以有多张表。

2.2 关系数据库（Relational Database）

如果数据库里的数据是以表（table）的形式存储，并且多张表和表之间可以建立相互参照（reference）的关系（relationship），这样的数据库就叫关系数据库。

2.3 关系数据库管理系统（RDBMS）

RDBMS: Relational Database Management System.

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

主要功能：

（1）安全管理

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

（2）事务管理

一个事务（Transaction）包含对数据库的多项操作，为保证数据得到正确处理，对数据的操作都应该得到一个稳定的最终状态——要么对数据的所有更改都成功使数据到达一个新的状态，要么撤消所有更改回到之前的状态。

（3）数据一致性管理

管理对数据库中数据的约束，保证数据的变更都是有效的。比如把不同的数据类型的数据放到了某一列（把“李明”放到了“出生日期”那一列），或者参照的数据不存在（“销售明细”表里有“产品编号”一列，参照了“产品表”里的“产品编号”列，而“销售明细”的“产品编号”列里填入了“0303”，而在“产品表”里并不存在“产品编号”为“0303”的记录），这些都是数据不一致的体现，是不被允许的。

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

多用户访问数据时的冲突控制，这种冲突控制叫做事务隔离，它可采取不同策略对两个事务之间的操作进行不同级别的隔离。例如当一个用户正在更改数据，更改的数据已经写入数据库，但本身的事务还没有完成，其它用户此时应该读到更改后的数据还是在这个用户的事务开始之前（更改前）的数据？

（5）备份/恢复数据

备份数据，避免数据遗失。当必要时（比如存储介质损坏、迁移系统到新服务器等）可在新系统中从备份中恢复数据。

1.4 SQL 语言

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

2、学习环境的建立

本教程将采用业界最受欢迎的开源数据库管理系统 MariaDB 作为讲解对象。

2.1 MraiaDB 数据库管理系统应用架构

2.2 MraiaDB 数据库管理系统管理界面

2.3 在 Windows 中安装 MariaDB

（1）下载安装

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、管理服务

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

2.1 服务的启动、停止

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

net start mariadb
net stop mariadb

2.2 用 mysqladmin 管理数据库系统

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

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

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

mysqladmin -uroot -p password

3、备份和恢复数据库

3.1 备份数据库

【命令形式】

mysqldump -u用户名 -p[密码] [选项] 数据库名1 [ 数据库名 [...]]` >  备份文件名

命令中的 > 符号叫重定向符号，表示将它之前的命令的输出结果定向到（保存到）后面文件名指定的文件。

【获取帮助】

可用命令 mysqldump --help 查看 mysqldump 的帮助文档。

【重要选项】

--databases 或 -B
后面可指定一到多个数据库名称（空格分隔），备份文件中会出现安全创建数据库的 CREATE DATABASE IF NOT EXISTS ... 语句。

--all-databases 或 -A
备份数据库管理系统中的所有数据库，相当于 --databases 选项后罗列出系统中的所有数据库。

--add-drop-database
备份中如果有创建数据库语句（用以上选项），则会在每个创建数据库语句前安全删除数据库DROP DATABASE IF EXISTS ...

--routines 或 -R
备份数据库中的存储过程和函数定义

--single-transaction

保证备份过程中的数据一致性（备份期间数据被“固化”），对备份多连接、数据变动频繁的数据库系统特别重要。只对采用了InnoDB引擎（默认引擎）的表有效。

【举例】

以下例子对数据库管理系统中的一个数据库进行备份：

mysqldump -uroot -p --databases --add-drop-database --routines --single-transaction mysite > mysite.sql

或：

mysqldump -uroot -p -B --add-drop-database --R --single-transaction mysite > mysite.sql

【练习】

1、请写出将数据库管理系统中的 mysite 和 shop 数据库同时备份出来的语句。
2、请写出将数据库管理系统中所有的数据库同时备份出来的语句。

3.2 恢复数据库

【命令形式】

mysql -u用户名 -p[密码] [选项] [数据库名] < 备份文件名

命令中的 < 符号叫做重定向符，它将后面文件名指定的内容输入到符号前的命令。
如果备份数据库时用了 --databases 选项，则命令中的 [数据库名] 不需要。

【获取帮助】

可用命令 mysql --help 查看 mysql 的帮助文档。

【重要选项】

--default-character-set=name
设定字符集为 name 表示的字符集。如果备份的数据库的字符集是 uft8mb4 , 就应该将默认字符集设定为 utf8mb4。

【举例】

1、将数据库 mysite 备份到 mysite.sql，然后再将其恢复到数据库管理系统中。
(1) 不用 --databases 选项进行备份的恢复（单个数据库可不用）

-- 备份
mysqldump -uroot -p -R --single-transaction mysite > mysite.sql
-- 恢复（先尝试建库，再恢复）
mysql -uroot -p -e "create database mysite if not exists default charset utf8mb4" 
mysql -uroot -p --default-character-set=utf8mb4 mysite < mysite.sql

（2）用 --databases 或 -B 选项（备份多个数据库必需）进行备份的恢复

-- 备份
mysqldump -uroot -p -B -R --single-transaction mysite > mysite.sql
-- 恢复
mysql -uroot -p --default-character-set=utf8mb4 < mysite.sql

【练习】

1、将所有数据库备份出来（含存储过程和函数定义），然后再将备份恢复到系统中。

第三章 操作 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: 使用
注意提示符的变化（MariaDB [(none)]> 到 MariaDB [mysql]>）。

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

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

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| ...... (略去一些)         |  
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)
MariaDB [mysql]>

show: 显示，展示
table: 表

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]>

select : 选择
from : 从
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)]>

variable : 变量
like ： 像

8、断开连接

MariaDB [(none)]>exit

第二部分 数据库的使用

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

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

第四章 预备知识：数据库设计文档解读

数据库设计文档用于描述数据库的设计，其中主要包含需求描述、实体及实体间关系的描述（实体关系图，Entity Relationship Diagram，ER图）、数据字典（表、视图、触发器、存储过程、函数等的定义）等。

4.1 数据库设计的相关概念

4.1.1 实体（Entity）

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

4.1.2 E-R 图（Entity Relationship Diagram ）

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

4.1.3 数据字典（Data Dictionary）

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

4.1.4 表（Table）

实体的信息以表的形式进行存储，表包含行和列，某一列就是实体的某个属性（Property），某一行就是实体的某个实例（Instance）。

4.1.5 视图（view）

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

4.1.6 触发器（trigger）

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

4.1.7 存储过程（Stored Procedure）

是一段被存储（stored）起来的 SQL 语言程序，用于完成特定的数据处理任务。可在其它 SQL 语言程序中根据需要调用它。

4.1.8 函数（Function）

它也是一段 SQL 语言程序，用于完成特定的数据处理任务。与存储过程不同的是，它不能返回结果集，只能返回单一值，同时，调用方法也不同。

4.2 需求描述

需求描述主要描述的是客户对数据处理（增、删、改、查）的基本需求，它是我们测试数据库设计的标尺。
需求描述通常按照一定条理（按实体罗列或按事务处理过程罗列等），梳理出对数据的处理需求，便于阅读理解和测试。

4.3 实体关系的图示表示

业界有多种实体关系图，本教程将采用使用较为普遍的“乌鸦脚”画法。
对实体关系的解读我们将放到后续章节中每个具体实例中去理解。

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

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

5.1 解读设计稿

5.1.1 需求描述

某公司需要用软件对员工（Employee）进行管理。主要需求描述如下：
1、能够列出员工所在的所有部门；
2、能够按照姓名查找员工；
3、能够查找某个年龄段的员工并按年龄进行升序、降序排序；
4、能够查找某月薪段的员工并按月薪进行升序、降序排序；
5、能够找出最近一年入职的所有员工；
6、能够按部门名称统计员工人数、月薪总额及平均月薪；
7、能够按部门统计员工的平均年龄。

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

图中只有一个实体。方框的顶行是实体名称“雇员信息”和对应的表名“employees”。从第二行开始，分两列。第一列为实体属性是何种关键列的标识，第二列为实体的属性名称。
从图中可以清晰地看出，这是一个“雇员”实体，它具有“雇员编号”、“所在部门”、“雇员姓名”、“性别”、“出生日期”、“雇用日期”、“月薪”等属性，且其中的“雇员编号”具有“PK”字样的关键列标识。
PK，Primary Key，即主键。主键列是这样一列：每一行记录的这一列的值都是不同的（否则不可能作为主键列），它可以保证表中绝对不会出现完全相同（各列均完全相同）的两行记录。
主键列可以由多列组合构成，多列构成的主键列，它们的组合不可能出现重复（否则不可能组合做主键）。

5.1.2 数据字典

（1）表定义

表名：雇员信息（ employees）

（2）触发器

表名：雇员信息（ employees）

（3）视图

（无）

（4）存储过程

（无）

（5）函数

（无）

（6）用户及权限

5.2 导入备份并进入数据库

5.2.1 导入备份数据

从随书光盘中拷贝（或从指定网址下载）c1_emp_db_with_users.zip 到硬盘，解压得到 c1_emp_db_with_users.sql 文件，以下假定文件被解压到了 D 盘根文件夹下。
打开 Windows 的命令窗口，在命令行打入以下命令并回车：

C:\Users\Administrator>mysql -uroot -p --default-character-set=utf8mb4 < d:\c1_emp_db_with_users.sql
Enter password: ******
C:\Users\Administrator>

输入您的root用户的密码回车后，如果没有任何提示信息，说明导入备份数据成功！
注意：由于此备份导入了系统数据库 mysql，您原有的 root 用户的密码被设为了 000000 (六个0)！！！

5.2.2 进入数据库

您可用 c1_emp_db_admin 或者 root 用户名进入数据库。

C:\Users\Administrator> mysql -uc1_emp_db_admin -p
Enter password: ******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.4.17-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use c1_emp_db;
Database changed
MariaDB [c1_emp_db]>

看到 MariaDB [c1_emp_db]>提示符后，您就成功进行了数据库，可以边学习边做后面的练习了。

5.3 查看数据

5.3.1 查看表的信息

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

MariaDB [c1_emp_db]> show tables;
+-----------------------+
| Tables_in_c1_emp_db   |
+-----------------------+
| employees             |
+-----------------------+
1 rows in set (0.000 sec)
MariaDB [c1_emp_db]> show full tables; -- 可区分普通表 (BASE TABLE) 和视图 (VIEW)
+-----------------------+------------+
| Tables_in_c1_emp_db   | Table_type |
+-----------------------+------------+
| employees             | BASE TABLE |
+-----------------------+------------+
1 rows in set (0.000 sec)

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

MariaDB [c1_emp_db]> describe employees; 
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned   | NO   | PRI | NULL    | auto_increment |
| sid        | char(18)              | NO   | UNI | NULL    |                |
| department | varchar(50)           | YES  |     | NULL    |                |
| name       | varchar(50)           | NO   |     | NULL    |                |
| gender     | char(1)               | YES  |     | 男      |                |
| birth_date | date                  | YES  |     | NULL    |                |
| emp_date   | date                  | YES  |     | NULL    |                |
| salary     | decimal(8,2) unsigned | YES  |     | 0.00    |                |
+------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.005 sec)
MariaDB [c1_emp_db]>

describe, 描述，在此可简写为 desc。

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

MariaDB [c1_emp_db]> show create table `employees`\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sid` char(18) COLLATE utf8mb4_unicode_ci NOT NULL,
  `department` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` char(1) COLLATE utf8mb4_unicode_ci DEFAULT '男',
  `birth_date` date DEFAULT NULL,
  `emp_date` date DEFAULT NULL,
  `salary` decimal(8,2) unsigned DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employees_sid_unique` (`sid`),
  CONSTRAINT `chk_gender` CHECK (`gender` in ('男','女'))
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.000 sec)
MariaDB [c1_emp_db]>

5.3.2 查看所有数据

语法：
select 逗号分隔的列名列表 from 表名;
其中，“逗号分隔的列名列表”可以是全部列也可以是部分列，全部列也可用 * 号代替。
如果 SQL 语句过长，可在必要时回车换行，控制台会另起一行，并出现 -> 提示符，您可继续输出余下的语句内容并多次换行，只有按下 ；（英文分号） 并回车后，语句才会被执行。

MariaDB [c1_emp_db]> select `id`,`sid`,`name`,`gender`,`birth_date`,`emp_date`,`salary`
-> from `employees`;

88 rows in set (0.001 sec)

select:选择，from: 从

或：

MariaDB [c1_emp_db]> select * from `employees`;

88 rows in set (0.001 sec)

5.3.3 记录排序

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

MariaDB [c1_emp_db]> select * from `employees` order by `salary` desc;

88 rows in set (0.001 sec)

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

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

MariaDB [c1_emp_db]> select * from `employees` order by `salary` asc;

88 rows in set (0.001 sec)

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

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

MariaDB [c1_emp_db]> select * 
from `employees` 
order by convert(`name` using gbk); 

88 rows in set (0.001 sec)

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

5.3.4 修改返回的列名

以下例子分别将原字段名改成对应的中文含义。

MariaDB [c1_emp_db]> select `id` as `编号`, `name` as `姓名`, `department` as `部门`, `salary` as `月薪` 
-> from `employees`;

88 rows in set (0.000 sec)

5.3.5 限定返回的记录数

以下例子限定返回的记录数为10条。

MariaDB [c1_emp_db]> select * from `employees` limit 10;

10 rows in set (0.000 sec)

limit: 限定，极限

5.3.6 指定跨过的记录数

以下例子将跨过30条记录后获取10条记录。

MariaDB [c1_emp_db]> select * from `employees` limit 30, 10;

10 rows in set (0.000 sec)

或：

MariaDB [c1_emp_db]> select * from `employees` limit 10 offset 30;

10 rows in set (0.000 sec)

offset: 偏移量

5.3.7返回不重复的数据

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

MariaDB [c1_emp_db]> select distinct `department` from `employees`;

6 rows in set (0.001 sec)

distinct: 截然不同的
如果 distinct 后面跟着多个列，则返回多个不重复的组合值。

5.3.8 筛选符合条件的记录

条件是由逻辑运算“串”起来的比较表达式构成的。
比较表达式：两个量之间的比较运算（运算：>, <, =, ...）。
如：月薪大于等于3000（salary>=3000），年龄介于30到50岁之间(age between 30 and 50)等。
逻辑表达式：两个比较表达式之间的关系运算（运算：AND， OR，NOT）。
如：
月薪大于等于3000 并且 年龄介于30到50岁之间（salary>=3000 AND age between 30 and 50）;
AND: 与，并且。运算规则：两者都满足结果才能满足。
月薪大于等于3000 或者 年龄介于30到50岁之间（salary>=3000 OR age between 30 and 50）;
OR: 或者。运算规则：只要一个满足结果就能满足。
月薪不大于3000 或者 年龄在30到50岁之外（salary<=3000 OR age not between 30 and 50）;
请参阅：附录二：“MySQL/MariaDB 比较运算与逻辑运算”

1、找出姓名包含“玉”字的员工。
一个 % 号代表零到任意多个字符组合，'%玉%' 表示 “玉” 可以出现在姓名中的任意位置。

MariaDB [c1_emp_db]> select * from `employees` where `name` like '%玉%';

3 rows in set (0.000 sec)

2、找出姓名开始于“李”的员工
一个 % 号代表零到任意多个字符组合，'李%' 表示 “李” 必须出现在姓名的开头位置。

MariaDB [c1_emp_db]> select * from `employees` where `name` like '李%';

1 row in set (0.000 sec)

3、找出姓名结尾是“明”的员工
一个 % 号代表零到任意多个字符组合，'%明' 表示 “明” 必须出现在姓名的最后位置。

MariaDB [c1_emp_db]> select * from `employees` where `name` like '%明';

1 row in set (0.000 sec)

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

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

15 rows in set (0.000 sec)

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

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

MariaDB [c1_emp_db]> select * from `employees` 
where `salary`>=3000 and `gender`='男';

45 rows in set (0.001 sec)

5.4 统计与计算数据

5.4.1 统计

（1）统计雇员总数

count(*) 中的 * 代表所有列的组合。

MariaDB [c1_emp_db]> select count(*) from `employees`;
+----------+
| count(*) |
+----------+
|       88 |
+----------+
1 row in set (0.000 sec)

count：计数

（2）统计月薪总额

MariaDB [c1_emp_db]> select sum(`salary`) from `employees`;
+---------------+
| sum(`salary`) |
+---------------+
|     577822.00 |
+---------------+
1 row in set (0.000 sec)

sum：总计，总和

（3）计算月薪平均值

MariaDB [c1_emp_db]> select avg(`salary`) from `employees`;
+---------------+
| avg(`salary`) |
+---------------+
|   6566.159091 |
+---------------+
1 row in set (0.000 sec)

avg: average, 平均值

同时统计以上三项数据并重新命名

MariaDB [c1_emp_db]> select count(*) as `雇员数`, sum(`salary`) as `月薪总额`, avg(`salary`) as `平均月薪` 
-> from `employees`;

1 row in set (0.000 sec)

（4）按部门分组统计

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

6 rows in set (0.001 sec)

group: 小组；分组。
group by: 依据 ... 分组。

5.4.2 计算

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

注意，以下例子输出的年龄与当前日期有关，您的输出结果有可能不同。

MariaDB [c1_emp_db]> select id,name,department,birth_date,TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age 
-> from employees;

88 rows in set (0.001 sec)

当前日期：CURDATE()，current cate（当前日期）的单词组合。

其中，TIMESTAMPDIFF（）为计算两个日期之间的距离函数。

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

TIMESTAMPDIFF, timestamp difference （时间戳差异）的单词组合。

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

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

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

MariaDB [c1_emp_db]> 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;

27 rows in set (0.001 sec)

注意： where 子句中不能用列的别名（如：age）。

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

注意：输出结果与当前日期有关，您的输出结果可能不同。

MariaDB [c1_emp_db]> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-03-18 |
+------------+
1 row in set (0.000 sec)
MariaDB [c1_emp_db]> select * from employees where emp_date>=date_sub(curdate(),interval 1 year);

14 rows in set (0.000 sec)

日期减法运算：

date_sub(日期时间，interval 数量 单位）

参见日期减法运算说明

（4）统计员工平均年龄

注意：平均年龄与当前日期有关，你的结果可能不同。

MariaDB [c1_emp_db]> select avg(timestampdiff(YEAR,birth_date,now())) as avg_age from employees;
+---------+
| avg_age |
+---------+
| 41.1591 |
+---------+
1 row in set (0.000 sec)

5.5 增、删、改数据

5.5.1 增加记录

语法：

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

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

插入时可根据需要只罗列出部分列，同时满足以下条件的必须罗列出来：

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

以下例子向雇员表中插入两条记录。注意：“id（员工编号）”、“birth_date（出生日期）” 和 “gender（性别）” 并没有提供。

MariaDB [c1_emp_db]> insert into
    -> `employees`(`sid`,`name`,`department`,`emp_date`,`salary`)
    -> values
    -> ('530103198803221221','李倩倩','研发部','2020-02-08',3500),
    -> ('530101199511232312','赵全','研发部','2020-02-08',3000);
Query OK, 2 rows affected (0.007 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK : 查询（语句执行）成功；
2 rows affected (0.007 sec) : 2 行（记录）受到影响（耗时 0.007秒）
Recordes: 记录数；
Duplicates: 重复的记录数；
Warnings: 警告数。

下面来确认一下：

MariaDB [c1_emp_db]> select * from employees order by id desc limit 2;
+----+--------------------+------------+--------+--------+------------+------------+---------+
| id | sid                | department | name   | gender | birth_date | emp_date   | salary  |
+----+--------------------+------------+--------+--------+------------+------------+---------+
| 90 | 530101199511232312 | 研发部     | 赵全   | 男     | 1995-11-23 | 2020-02-08 | 3000.00 |
| 89 | 530103198803221221 | 研发部     | 李倩倩 | 女     | 1988-03-22 | 2020-02-08 | 3500.00 |
+----+--------------------+------------+--------+--------+------------+------------+---------+
2 rows in set (0.000 sec)

发现数据库中的性别和出生日期被正确填入了！
这是触发器的作用（触发器根据身份证号码计算出生日和性别后，一并填入数据表）。

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

查看触发器：

-- 列出数据库中的所有触发器
MariaDB [c1_emp_db]> show triggers \G
*************************** 1. row ***************************
             Trigger: trigger_for_ins -- 触发器名: trigger_for_ins
               Event: INSERT -- 事件：插入
               Table: employees -- 表：employees
           Statement: 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                 -- 语句：结束
              Timing: BEFORE    -- 执行时机：之前
             Created: 2021-03-18 14:27:20.90
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
             Trigger: trigger_for_upd
               Event: UPDATE
               Table: employees
           Statement: 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
              Timing: BEFORE
             Created: 2021-03-18 14:27:20.92
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
  Database Collation: utf8mb4_general_ci
2 rows in set (0.004 sec)
-- 查看某个触发器的详细 SQL 定义
-- show create trigger 触发器名\G
MariaDB [c1_emp_db]> show create trigger trigger_for_ins\G
...（略去一些内容）
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_for_ins  -- 创建触发器 trigger_for_ins
BEFORE INSERT   -- 在插入前（被触发执行）
ON `employees`  -- 针对表 employees
FOR EACH ROW    -- 针对表的每一行
BEGIN           -- 开始 
    -- 设置新插入记录的 birth_date 为：新插入记录身份证号的第7位开始后的4位（年）- 新插入记录身份证号的第11位开始后的2位（月）-新插入记录身份证号的第13位开始后的2位（日），如："1987-12-20".
    SET NEW.birth_date=CONCAT(SUBSTRING(NEW.sid,7,4),'-',SUBSTRING(NEW.sid,11,2),'-',SUBSTRING(NEW.sid,13,2));
    -- 开始判断：如果新插入记录身份证号的第17位开始后的1位是偶数：
    IF CAST(SUBSTRING(NEW.sid,17,1) AS INT) % 2 = 0 THEN
        -- 设置新插入记录的 gender 为 '女'
        SET NEW.gender = '女';
    -- 否则
    ELSE
        -- 设置新插入记录的 gender 为 '男'
        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.5.2 修改记录

语法：

UPDATE 表名 SET 列名1=值1, 列名2=值2, ... where 条件;

以下例子将编号为 18 的员工信息进行了更改。

MariaDB [c1_emp_db]> select * from employees where id=18;
+----+--------------------+------------+------+--------+------------+------------+---------+
| id | sid                | department | name | gender | birth_date | emp_date   | salary  |
+----+--------------------+------------+------+--------+------------+------------+---------+
| 18 | 522702196607274182 | 研发部     | 车文 | 女     | 1966-07-27 | 1998-03-27 | 9094.00 |
+----+--------------------+------------+------+--------+------------+------------+---------+
1 row in set (0.000 sec)
MariaDB [c1_emp_db]> update employees set name='车小文', gender='男' where id=18;
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [c1_emp_db]> select * from employees where id=18;                     18;
+----+--------------------+------------+--------+--------+------------+------------+---------+
| id | sid                | department | name   | gender | birth_date | emp_date   | salary  |
+----+--------------------+------------+--------+--------+------------+------------+---------+
| 18 | 522702196607274182 | 研发部     | 车小文 | 女     | 1966-07-27 | 1998-03-27 | 9094.00 |
+----+--------------------+------------+--------+--------+------------+------------+---------+
1 row in set (0.000 sec)

update: 更新
Rows matched: 1 Changed: 1 Warnings: 0
匹配的行数:1 已被更改的记录数：1 警告

问题：性别更改为什么不成功？

5.5.3 删除记录

语法：

DELETE FROM 表名 WHERE 条件;

（1）按条件删除记录

以下例子删除身份证号为 “522702196607274182” 的员工

MariaDB [c1_emp_db]> delete from employees where sid='522702196607274182';
Query OK, 1 row affected (0.004 sec)
MariaDB [c1_emp_db]>

（2）删除全部记录

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

MariaDB [c1_emp_db]> delete from employees;
Query OK, 89 rows affected (0.007 sec)
MariaDB [c1_emp_db]>

delete: 删除

5.6 验证需求描述中需要实现的功能

5.6.1 能够列出员工所在的所有部门；

select distinct `department` from  `employees`;

5.6.2 能够按照姓名查找员工；

select * from employees where name='伍宁';
select * from `employees` where `name` like '%娜%';

5.6.3 能够查找某个年龄段的员工并按年龄进行升序、降序排序；

5.6.4 能够查找某月薪段的员工并按月薪进行升序、降序排序；

5.6.5 能够找出最近一年入职的所有员工；

5.6.6 能够按部门名称统计员工人数、月薪总额及平均月薪；

5.6.7 能够按部门统计员工的平均年龄。

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

数据库： 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;