相关文章：Issue 18
Crontab执行shell脚本报错
Dmesg时间显示问题
Idea2023更改maven默认仓库源
Idea如何回退到之前的工程
Windows下mysql和python3安装过程
Windows结束进程
传统运维技术篇01
传统运维技术篇02
传统运维技术篇03
传统运维技术篇04
传统运维技术篇05
传统运维技术篇06
传统运维技术篇07
常见问题总结
快速删除大量小文件多种方式速度对比
记一次由于kswapd0进程导致系统异常
面试题整理
黑群晖外部访问frp搭建过程
2025-03-19    2025-03-19    3396 字  7 分钟
Issue

数据库篇

一、数据库基本知识

实体：客观存在的真实事物，人、物品都是。

属性：实体的某个特征，比如一个人的年龄是18

关系：实体与实体之间的联系，分为一对一、一对多、多对多

记录：表格中的一行

字段：表格中的一列

主键：能唯一标识表中的一行的列

外键：表中存放了其他表的主键

举例：

mariadb01-min.webp

分析表中主键和外键。

1. 班级表

  • 主键：班级号（每个班级号是唯一的，可以作为主键）

  • 外键：无外键，班级表独立。

2. 学生表

  • 主键：学号（每个学号是唯一的，适合用作主键）

  • 外键：班级号（外键，引用班级表中的班级号）

3. 成绩表

  • 主键：可以是组合主键，包括成绩号（唯一标识成绩记录）或者由学号和考试号组成的组合主键。

  • 外键：

    • 学号（外键，引用学生表中的学号）

    • 考试号（外键，引用考试表中的考试号）

    • 科目号（外键，引用科目表中的科目号）

4. 科目表

  • 主键：科目号（每个科目号是唯一的，适合用作主键）

  • 外键：无外键，科目表独立。

5. 考试表

  • 主键：考试号（每个考试号是唯一的，适合用作主键）

  • 外键：无外键，考试表独立。

总结：

  • 班级表：有主键班级号，无外键。

  • 学生表：有主键学号，外键班级号。

  • 成绩表：主键成绩号（或组合主键），外键学号、考试号、科目号。

  • 科目表：有主键科目号，无外键。

  • 考试表：有主键考试号，无外键。

事务：保证两个数据操作同时成功。

mariadb02-min.webp

访问锁：

mariadb03-min.webp

数据库原理：

mariadb04-min.webp

数据类型：

存储引擎中myisam不支持事务，innoDB支持事务。开启事务会消耗大量资源，和钱相关的项目会开启，其他不用开启。

mariadb05-min.webp

二、数据库操作

基本命令如下：

mariadb07-min.webp

按照上图创建数据库jxcdm，并设计表，表中插入数据。并且使用联合查询方式使结果按下图方式显示。

mariadb06-min.webp

安装数据库：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

yum install -y mariadb-server
systemctl restart mariadb 
#更改mysql密码
mysqladmin -uroot password 123456
#进入数据库
mysql -uroot -p123456
#创建库
MariaDB [(none)]> create database jxcms;
Query OK, 1 row affected (0.000 sec)
# ctrl + d 退出数据库

构建sql语句：

vim jx_class.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

-- 注释
-- 创建班级表
DROP TABLE IF EXISTS `jx_class`;
CREATE TABLE `jx_class` (
 `id` INT AUTO_INCREMENT,          -- 班级号（自增长）
 `name` VARCHAR(20) NOT NULL,      -- 班级名
 PRIMARY KEY(`id`)		  -- id字段为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 写入班级表数据
INSERT INTO `jx_class` VALUES(1, '2211');
INSERT INTO `jx_class` VALUES(NULL, '2210');
-- 创建学生表
DROP TABLE IF EXISTS `jx_stu`;
CREATE TABLE `jx_stu` (
 `id` INT AUTO_INCREMENT,          -- 学生号（自增长）
 `classId` INT NOT NULL,          -- 班级号（自增长）
 `name` VARCHAR(20) NOT NULL,       -- 学生名
 `age` INT NOT NULL,          -- 年龄
 PRIMARY KEY(`id`)                -- id字段为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 写入学生表数据
INSERT INTO `jx_stu` VALUES(1, 1, '张三', 18);
INSERT INTO `jx_stu` VALUES(2, 1, '李四', 19);
INSERT INTO `jx_stu` VALUES(3, 2, '王五', 20);
INSERT INTO `jx_stu` VALUES(4, 2, '马六', 21);
-- 创建考试表
DROP TABLE IF EXISTS `jx_test`;
CREATE TABLE `jx_test` (
 `id` INT AUTO_INCREMENT,          -- 考试号（自增长）
 `name` VARCHAR(20) NOT NULL,      -- 考试名
 PRIMARY KEY(`id`)                -- id字段为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 写入考试表数据
INSERT INTO `jx_test` VALUES(1, '期中考试');
INSERT INTO `jx_test` VALUES(NULL, '期末考试');

-- 创建科目表
DROP TABLE IF EXISTS `jx_subject`;
CREATE TABLE `jx_subject` (
 `id` INT AUTO_INCREMENT,          -- 科目号（自增长）
 `name` VARCHAR(20) NOT NULL,      -- 科目名
 PRIMARY KEY(`id`)                -- id字段为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 写入科目表数据
INSERT INTO `jx_subject` VALUES(1, '网工');
INSERT INTO `jx_subject` VALUES(NULL, '运维');

-- 创建成绩表
DROP TABLE IF EXISTS `jx_score`;
CREATE TABLE `jx_score` (
 `id` INT AUTO_INCREMENT,          -- 成绩号（自增长）
 `stuId` INT NOT NULL,          -- 学生号
 `testId` INT NOT NULL,          -- 考试号
 `subjectId` INT NOT NULL,          -- 科目号
 `score` FLOAT NOT NULL,          -- 分数
 PRIMARY KEY(`id`)                -- id字段为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 写入成绩表数据
INSERT INTO `jx_score` VALUES(1, 1, 1, 1, 90);
INSERT INTO `jx_score` VALUES(2, 1, 2, 1, 80);
INSERT INTO `jx_score` VALUES(3, 1, 1, 2, 85);
INSERT INTO `jx_score` VALUES(4, 1, 2, 2, 84);
INSERT INTO `jx_score` VALUES(5, 2, 1, 1, 78);
INSERT INTO `jx_score` VALUES(6, 2, 2, 1, 88);
INSERT INTO `jx_score` VALUES(7, 2, 1, 2, 89);
INSERT INTO `jx_score` VALUES(8, 2, 2, 2, 94);
INSERT INTO `jx_score` VALUES(9, 3, 1, 1, 68);
INSERT INTO `jx_score` VALUES(10, 3, 2, 1, 78);
INSERT INTO `jx_score` VALUES(11, 3, 1, 2, 69);
INSERT INTO `jx_score` VALUES(12, 3, 2, 2, 64);
INSERT INTO `jx_score` VALUES(13, 4, 1, 1, 81);
INSERT INTO `jx_score` VALUES(14, 4, 2, 1, 77);
INSERT INTO `jx_score` VALUES(15, 4, 1, 2, 59);
INSERT INTO `jx_score` VALUES(16, 4, 2, 2, 48);

将sql导入数据库，如果出现以下报错说明jx_class已存在。所以在创建表之前先判断表是否存在，如果存在则丢弃，再创建表(DROP TABLE IF EXISTS `jx_class`;)。

1
2

[root@jx-ops-81 jx1206]# mysql -uroot -p123456 jxcms < jx_class.sql 
ERROR 1050 (42S01) at line 3: Table 'jx_class' already exists

然后导入表，所有数据都准备好了。

1

[root@jx-ops-81 jx1206]# mysql -uroot -p123456 jxcms < jx_class.sql

进入到数据库中使用联合查询语句来将结果和图上匹配。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

MariaDB [(none)]> use jxcms;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [jxcms]> select jx_score.id, jx_class.name, jx_stu.name, jx_subject.name, jx_test.name, jx_score.score  from jx_score  join jx_stu on jx_score.stuId=jx_stu.id  join jx_subject on jx_score.subjectID=jx_subject.id  join jx_test on jx_score.testId=jx_test.id join jx_class on jx_stu.classId=jx_class.id;
+----+------+--------+--------+--------------+-------+
| id | name | name   | name   | name         | score |
+----+------+--------+--------+--------------+-------+
|  1 | 2211 | 张三   | 网工   | 期中考试     |    90 |
|  2 | 2211 | 张三   | 网工   | 期末考试     |    80 |
|  3 | 2211 | 张三   | 运维   | 期中考试     |    85 |
|  4 | 2211 | 张三   | 运维   | 期末考试     |    84 |
|  5 | 2211 | 李四   | 网工   | 期中考试     |    78 |
|  6 | 2211 | 李四   | 网工   | 期末考试     |    88 |
|  7 | 2211 | 李四   | 运维   | 期中考试     |    89 |
|  8 | 2211 | 李四   | 运维   | 期末考试     |    94 |
|  9 | 2210 | 王五   | 网工   | 期中考试     |    68 |
| 10 | 2210 | 王五   | 网工   | 期末考试     |    78 |
| 11 | 2210 | 王五   | 运维   | 期中考试     |    69 |
| 12 | 2210 | 王五   | 运维   | 期末考试     |    64 |
| 13 | 2210 | 马六   | 网工   | 期中考试     |    81 |
| 14 | 2210 | 马六   | 网工   | 期末考试     |    77 |
| 15 | 2210 | 马六   | 运维   | 期中考试     |    59 |
| 16 | 2210 | 马六   | 运维   | 期末考试     |    48 |
+----+------+--------+--------+--------------+-------+
16 rows in set (0.001 sec)

三、奇怪问题

我们还使用jx1206项目内的war包作为应用。现在将代码上传到远程仓库（gitee上），然后在开发服务器上拉取仓库中最新代码，更改源码中a.sh文件，添加数据库查询相关命令。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 开发服务器
cd jx1206
git pull --all
vi /opt/jx1206/src/main/webapp/WEB-INF/cgi/a.sh

#!/bin/bash
# 网站脚本前面必须这样写
echo "Content-Type:text/html"
echo ""

#echo "aaaaaaaaaaaaaaa"
mysql -h10.10.10.81 -ujxadmin -p123456 jxcms -e "select * from jx_stu" | grep -v id

运维服务器数据库需要创建一个用户具有管理员功能，否则在开发服务器上访问时会报错：

1

grant all on *.* to "jxadmin"@"%" identified by "123456";

进入到开发服务器上将源代码打包成war包上传到tomat的webapps中，重启java。先在开发服务器上测试：

1
2
3
4
5
6

[root@jx-dev-71 cgi]# curl 127.0.0.1/cgi-bin/a.sh
aaaaaaaaaaaaaaaaaaaaa
1	1	??	18
2	1	??	19
3	2	??	20
4	2	??	21

发现中文字符是问号。

去运维服务器上测试也是问号。

1
2
3
4
5
6

[root@jx-ops-81 cgi]# curl 127.0.0.1/cgi-bin/a.sh
aaaaaaaaaaaaaaa
1	1	??	18
2	1	??	19
3	2	??	20
4	2	??	21

单独执行sql语句显示正常。

1
2
3
4
5

[root@jx-ops-81 cgi]# mysql -h10.10.10.81 -ujxadmin -p123456 jxcms -e "select * from jx_stu" | grep -v id
1	1	张三	18
2	1	李四	19
3	2	王五	20
4	2	马六	21

在shell脚本里显示问号，在终端里显示正常。查看终端字符编码环境：

1
2

[root@jx-ops-81 cgi]# echo $LANG
en_US.UTF-8

脚本中并没用使用编码环境，修改a.sh，添加编码环境。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#!/bin/bash

# 网站脚本前面必须这样写
echo "Content-Type:text/html"
echo ""

export LANG=en_US.UTF-8

#echo "aaaaaaaaaaaaaaa"
mysql -h10.10.10.81 -ujxadmin -p123456 jxcms -e "select * from jx_stu" | grep -v id

现在在开发机上测试，显示正常。

1
2
3
4
5
6

[root@jx-dev-71 cgi]# curl 127.0.0.1/cgi-bin/a.sh
aaaaaaaaaaaaaaaaaaaaa
1	1	张三	18
2	1	李四	19
3	2	王五	20
4	2	马六	21

应用需要从外部浏览器访问，我们之前做过映射，从笔记本访问127.0.0.1的7180端口流量会转到开发机的80端口。

我们从浏览器访问，又出现新问题，之前的问号变成了乱码。

1
2

http://127.0.0.1:7180/cgi-bin/a.sh
aaaaaaaaaaaaaaaaaaaaa 1 1 寮犱笁 18 2 1 鏉庡洓 19 3 2 鐜嬩簲 20 4 2 椹叚 21

我们想一下整个业务访问过程。从浏览器访问127.0.0.1:7180/cgi-bin/a.sh首先需要经过虚拟机网关服务器nat网卡映射，然后到网关服务器的nat规则，然后走内核转发，最后到开发机的80业务口上。

最开始shell中没有添加export LANG=en_US.UTF-8 时，在开发服务器上抓到的包如下：

mysql抓包01-min.webp

分段排查，这里在开发服务器上抓业务口的3306。分析MySQL包的响应包。

1

# tcpdump -i enp0s3 -p port 3306   -vv -nn -w mysql.pcap

抓到之后我们分析一下：

mysql抓包02-min.webp

乱码那里对应的16进制编码转换成utf8后正确显示了中文。证明了运维服务器上mysql服务器发送给开发机mysql客户端的数据是正确的。

然后我们在排查下一段：开发服务器上返回给浏览器的包是否正确？

在开发服务器上抓80端口，由于是tcp协议，可以追踪TCP流，使用16进制显示，找到中文那里对应16进制编码，将该编码转换成utf8，结果确定是我们想要的中文。

1

tcpdump -i enp0s3 -p port 80   -vv -nn -w mysql04.pcap

mysql抓包03-min.webp

现在看来开发服务器上返回给浏览器的包也是正确的。那么问题到底出现在哪里了呢？我们在仔细查看shell脚本中的代码发现：我们只告诉了浏览器我们传送的是文本或者html，并没有说是中文。导致浏览器访问时都按照文本或者html方式处理所以中文显示乱码。

下面我们修改shell脚本，添加中文编码格式，使用浏览器再次访问测试，终于显示正常了。

1

echo "Content-Type:text/html;charset=utf8"

1
2

http://127.0.0.1:7180/cgi-bin/a.sh
aaaaaaaaaaaaaaaaaaaaa 1 1 张三 18 2 1 李四 19 3 2 王五 20 4 2 马六 21

最终完整脚本如下：

1
2
3
4
5
6
7
8
9

#!/bin/bash

# 网站脚本前面必须这样写
echo "Content-Type:text/html;charset=utf8"
echo ""

export LANG=en_US.UTF-8
echo "aaaaaaaaaaaaaaaaaaaaa"
mysql -h10.10.10.81 -ujxadmin -p123456 jxcms -e "select * from jx_stu" | grep -v id

mysql数据库可以使用一条命令将16进制编码转换成汉字，先登录mysql，然后输入以下命令：

1
2
3
4
5
6
7

MariaDB [(none)]> SELECT CONVERT(UNHEX('e5bca0e4b889') USING utf8);
+-------------------------------------------+
| CONVERT(UNHEX('e5bca0e4b889') USING utf8) |
+-------------------------------------------+
| 张三                                      |
+-------------------------------------------+
1 row in set (0.001 sec)

这里将`e5bca0e4b889`更改成你想要查询的16进制编码。