数据库篇
一、数据库基本知识
实体：客观存在的真实事物，人、物品都是。
属性：实体的某个特征，比如一个人的年龄是18
关系：实体与实体之间的联系，分为一对一、一对多、多对多
记录：表格中的一行
字段：表格中的一列
主键：能唯一标识表中的一行的列
外键：表中存放了其他表的主键
举例：
分析表中主键和外键。
1. 班级表
主键：
班级号（每个班级号是唯一的，可以作为主键）外键：无外键，班级表独立。
2. 学生表
主键：
学号（每个学号是唯一的，适合用作主键）外键：
班级号（外键，引用班级表中的班级号）
3. 成绩表
主键：可以是组合主键，包括
成绩号（唯一标识成绩记录）或者由学号和考试号组成的组合主键。外键：
学号（外键，引用学生表中的学号）考试号（外键，引用考试表中的考试号）科目号（外键，引用科目表中的科目号）
4. 科目表
主键：
科目号（每个科目号是唯一的，适合用作主键）外键：无外键，科目表独立。
5. 考试表
主键：
考试号（每个考试号是唯一的，适合用作主键）外键：无外键，考试表独立。
总结：
班级表：有主键
班级号，无外键。学生表：有主键
学号，外键班级号。成绩表：主键
成绩号（或组合主键），外键学号、考试号、科目号。科目表：有主键
科目号，无外键。考试表：有主键
考试号，无外键。
事务：保证两个数据操作同时成功。
访问锁：
数据库原理：
数据类型：
存储引擎中myisam不支持事务，innoDB支持事务。开启事务会消耗大量资源，和钱相关的项目会开启，其他不用开启。
二、数据库操作
基本命令如下：
按照上图创建数据库jxcdm，并设计表，表中插入数据。并且使用联合查询方式使结果按下图方式显示。
安装数据库：
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
-- 注释
-- 创建班级表
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`;)。
[root@jx-ops-81 jx1206]# mysql -uroot -p123456 jxcms < jx_class.sql
ERROR 1050 (42S01) at line 3: Table 'jx_class' already exists然后导入表，所有数据都准备好了。
[root@jx-ops-81 jx1206]# mysql -uroot -p123456 jxcms < jx_class.sql进入到数据库中使用联合查询语句来将结果和图上匹配。
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文件，添加数据库查询相关命令。
# 开发服务器
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
运维服务器数据库需要创建一个用户具有管理员功能，否则在开发服务器上访问时会报错：
grant all on *.* to "jxadmin"@"%" identified by "123456";进入到开发服务器上将源代码打包成war包上传到tomat的webapps中，重启java。先在开发服务器上测试：
[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发现中文字符是问号。
去运维服务器上测试也是问号。
[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语句显示正常。
[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脚本里显示问号，在终端里显示正常。查看终端字符编码环境：
[root@jx-ops-81 cgi]# echo $LANG
en_US.UTF-8脚本中并没用使用编码环境，修改a.sh，添加编码环境。
#!/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
现在在开发机上测试，显示正常。
[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端口。
我们从浏览器访问，又出现新问题，之前的问号变成了乱码。
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 时，在开发服务器上抓到的包如下：
分段排查，这里在开发服务器上抓业务口的3306。分析MySQL包的响应包。
# tcpdump -i enp0s3 -p port 3306 -vv -nn -w mysql.pcap抓到之后我们分析一下：
乱码那里对应的16进制编码转换成utf8后正确显示了中文。证明了运维服务器上mysql服务器发送给开发机mysql客户端的数据是正确的。
然后我们在排查下一段：开发服务器上返回给浏览器的包是否正确？
在开发服务器上抓80端口，由于是tcp协议，可以追踪TCP流，使用16进制显示，找到中文那里对应16进制编码，将该编码转换成utf8，结果确定是我们想要的中文。
tcpdump -i enp0s3 -p port 80 -vv -nn -w mysql04.pcap
现在看来开发服务器上返回给浏览器的包也是正确的。那么问题到底出现在哪里了呢？我们在仔细查看shell脚本中的代码发现：我们只告诉了浏览器我们传送的是文本或者html，并没有说是中文。导致浏览器访问时都按照文本或者html方式处理所以中文显示乱码。
下面我们修改shell脚本，添加中文编码格式，使用浏览器再次访问测试，终于显示正常了。
echo "Content-Type:text/html;charset=utf8"http://127.0.0.1:7180/cgi-bin/a.sh
aaaaaaaaaaaaaaaaaaaaa 1 1 张三 18 2 1 李四 19 3 2 王五 20 4 2 马六 21最终完整脚本如下：
#!/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，然后输入以下命令：
MariaDB [(none)]> SELECT CONVERT(UNHEX('e5bca0e4b889') USING utf8);
+-------------------------------------------+
| CONVERT(UNHEX('e5bca0e4b889') USING utf8) |
+-------------------------------------------+
| 张三 |
+-------------------------------------------+
1 row in set (0.001 sec)这里将`e5bca0e4b889`更改成你想要查询的16进制编码。
评论区