数据库与ORM开发手册

前言

在数字化时代，数据库技术已成为我们日常生活和工作中不可或缺的一部分。无论是处理个人信息、管理企业数据，还是构建大型应用程序，数据库都发挥着核心作用。作为一个数据库开发者，我们不仅要掌握基本的数据库知识，还要了解如何高效地设计和维护数据库系统。这篇数据库开发手册正是为了帮助我更好地掌握数据库开发的各个方面而编写的。

---

提示：数据库规约内容引自阿里《java开发手册》

一、MySQL 数据库 规约

建表规约

 说明：此处只列出了一些常见的强制规约,详细请参考阿里开发手册原文

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间

只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重

考虑。

说明： MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

正例： aliyun_admin，rdc_config，level3_name

反例： AliyunAdmin，rdcConfig，level_3_name

【强制】表名不使用复数名词。

说明： 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。

【强制】小数类型为 decimal，禁止使用 float 和 double。

说明： 在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。

【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长

度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索

引效率。

【强制】表必备三字段：id, create_time, update_time。

说明： 其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time 的类型均为 datetime 类型。

【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned

tinyint（1 表示是，0 表示否）。

说明： 任何字段如果为非负数，必须是 unsigned 。

注意： POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在< resultMap >设置从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。

正例： 表达逻辑删除的字段名 is_deleted ，1 表示删除，0 表示未删除。

【推荐】表的命名最好是遵循“业务名称_表的作用”。

正例： alipay_task / force_project / trade_config

SQL语句规约

【强制】不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的

标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明： count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果

为 NULL，因此使用 sum()时需注意 NPE 问题。

正例： 使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;

【强制】使用 ISNULL()来判断是否为 NULL 值。

说明： NULL 与任何值的直接比较都为 NULL 。

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明： 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

【强制】数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。

ORM规约

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

说明： 1）增加查询分析器解析成本。

2）增减字段容易与 resultMap 配置不一致。

3）无用字段增加网络消耗，尤其是 text 类型的字段。

【强制】POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行

字段与属性之间的映射。

说明： 参见定义 POJO 类以及数据库字段定义规定，在<resultMap>中增加映射，是必须的。在MyBatis Generator 生成的代码中，需要进行对应的修改。

二、查询语句

1.单表查询

查询语法格式:[ ]中内容表示可写可不写根据情况决定

SELECT [DISTINCT] | 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(S)]
[group by 字段列表]
[having 函数方法判断]
[ORDER BY 字段 [ASC|DESC][ ,字段 [ASC|DESC], …]]
[LIMIT 开始索引,长度];

查询使用规则及注意事项:

• NULL值的处理:包含空值的任何计算结果都为null,如果要计算可以采用IFNULL()进行处理

例如:(其中若月薪和奖金为null则用0替换参与计算)

-- - 查询所有员工的年薪((月薪 + 奖金) * 12) 。
SELECT ENAME,(IFNULL(MGR,0)+IFNULL(SAL,0))*12 as "年薪" FROM emp;

2.连表查询

隐式内连接查询

语法:

SELECT [DISTINCT] * | 字段 [别名] [, 字段 [别名], …]
FROM 表名称 [别名], [表名称 [别名], …]
[WHERE 条件(S)/消除笛卡尔积连接]
[ORDER BY 排序字段 [ASC|DESC] [, 排序字段 [ASC|DESC], …]];

显式内连接查询

语法:

SELECT 别名1.column,..., 别名2.column,...
FROM table1 别名1 [INNER] JOIN table2 别名2 ON 别名1.column1 = 别名2.column2
WHERE 查询判断条件

外连接查询:

左外连接查询:

语法:

SELECT 别名1.column,..., 别名2.column,...
FROM table1 别名1 LEFT [OUTER] JOIN table2 别名2 ON 别名1.column1 = 别名2.column2
WHERE 查询判断条件

右外连接查询

语法:

SELECT 别名1.column,..., 别名2.column,...
FROM table1 别名1 
RIGHT [OUTER] JOIN table2 别名2 ON 别名1.column = 别名2.column
RIGHT [OUTER] JOIN table3 别名3 ON 别名3.column = 别名?.column 
.......
WHERE 查询判断条件

连表查询注意事项:

• 当多个表中有重名列时，必须在列的名字前加上表名作为前缀或者表的别名（使用别名更简单，性能更高）；
• 左(右)外连接查询出 JOIN 左(右)边表的全部数据查询出来，JOIN 右(左)边的表不匹配的数据使用 NULL 来填充数据。
• 任意多表查询时，每个表都可以定义别名，返回的字段名前面都建议添加别名前缀
• 多表查询时，一定保证每张表的主键字段必须返回

三、常用函数

日期函数:

• NOW()：获取当前时间；
• DAY(date)：获取日期中的天数，范围是从 1 到 31；
• MONTH(date)：返回 date 对应的月份，范围时从 1 到 12；
• YEAR(date)：返回 date 对应的年份，范围是从 1000 到 9999；
• LAST_DAY(date)：获取一个日期或日期时间值，返回该月最后一天对应的值。
• HOUR(datetime)：返回 time 对应的小时数，范围是从 0 到 23；
• MINUTE(datetime)：返回 time 对应的分钟数，范围是从 0 到 59；

日期转换函数:

DATE_FORMAT(date字段/字符串值, format)

-- 将字符串类型日期转为date类型日期
DATE_FORMAT('2023-11-06','%Y-%m-%d');
-- 将date类型日期转为字符串日期
DATE_FORMAT(NOW(),'%Y-%m-%d');

数学函数:

• abs(x) 返回x的绝对值
• rand() 返回0到1的随机数
• mod(x, y) 返回x除以y以后的余数
• power(x, y) 返回x的y次方
• round(x) 返回离x最近的整数
• round(x, y) 保留x的y位小数四舍五入后的值
• sqrt(x) 返回x的平方根
• truncate(x, y) 返回数字x截断为y位小数的值 #不四舍五入
• ceil(x) 返回大于或等于x的最小整数floor(x) 返回小于或等于x的最大整数
• greatest(x1,x2,...) 返回集合中最大的值
• least(x1,x2,...) 返回集合中最小的值

字符串函数:

concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串

模糊查询名字中有SMI的职员名字

SELECT emp.ENAME FROM emp WHERE ENAME LIKE CONCAT('%',"SMI",'%');

• substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
• substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
• length(x) 返回字符串 x 的长度replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
• upper(x) 将字符串 x 的所有字母变成大写字母
• lower(x) 将字符串 x 的所有字母变成小写字母
• left(x,y) 返回字符串 x 的前 y 个字符
• right(x,y) 返回字符串 x 的后 y 个字符
• repeat(x,y) 将字符串 x 重复 y 次
• space(x) 返回 x 个空格
• strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
• reverse(x) 将字符串 x 反转

四、正则表达式的使用

常用正则表达式

模式	表示
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
*	匹配前面的子表达式一次或多次
+	匹配前面的子表达式零次或多次
[0-9]	匹配数字0-9
[...]	匹配所包含的任意一个字符
[^...]	匹配未包含的任意字符

SQL正则运用语法:

SELECT 字段 FORM  表名 WHERE 字段 regexp 正则表达式

正则表达式使用例子:

emp表:

查询职业以S开头的员工姓名与工资信息:

SELECT emp.ENAME,emp.JOB,emp.MGR FROM emp WHERE JOB REGEXP "^S";

显示结果

查询名字是以N结尾的职员名称

SELECT emp.ENAME FROM emp WHERE ENAME REGEXP "N$";

查询雇员编号中0出现一次或一次以上的编号

SELECT emp.EMPNO FROM emp WHERE EMPNO REGEXP "0+";

查询结果:

查询职业中包含MAN的职业

SELECT emp.JOB FROM emp WHERE JOB REGEXP "MAN";

查询结果:

参考文章:

阿里巴巴集团技术团队《java开发手册》

http://t.csdnimg.cn/S9eOD

---