MySQL 入门学习

1. 初识MySQL

1.1 数据库基本知识

数据库（DB: DataBase)分为，结构化数据库和非结构化数据库。

结构化数据库：
即通过行和列的方式储存数据。
非结构化数据库：
通过对象属性的方式储存数据。
DBMS : Data Base Management Systems
即数据库管理系统，用这个系统管理数据库。

1.2 MySQL 基本指令

1.2.1 命令行指令

net start mysql但此时并未进入mysql模式，需要下一步指令。
mysql -u root -p123456进入mysql模式，root为账户名，p后面为密码123456。
\q和exit退出mysql。

1.3. sqlyog

sqlyog是一款SQL可视化管理软件。
具体来说：sql是文本交互的，即通过代码行进行编译，而sqlyog是可视化交互的，即有各种按钮。
sqlyog的每一次操作都对应 sql的一行代码操作。这在 **“历史记录” **界面中可以查询。

2. 操作数据库

2.1 mysql基本语法

" -- " 为sql语句的注释行
sql是可以换行的，每一个语句以";"结尾。
sql是不区分大小写的，sqlyog对于关键词会自动大写，但是实际上无所谓的。
尽量避免命名与保留词重合，若查询重名单位，则将其封装在一对漂符` 内。

2.2 基本指令

show databasees; --查看所有的数据库
use xxx; --切换到 xxx数据库
show tables; --查看数据库中的所有表
creat database xxxx; --创建一个新的数据库xxx
drop database xxx; --删除数据库
drop database IF EXISTS xxx; --IF语句，当存在时执行，不存在时也不报错。

2.3 数据类型与字段属性

2.3.1 数据类型

此处不介绍数值类型变量。
字符串类型：

char 字符串：固定字节大小的 0~255
varchar 可变字符串：字节大小 0-65535 较为常用。
tinytext微型文本：字节大小2^8-1
text文本串： 2^16-1 保存大文本

时间日期类型：

datatime： YYYY-MM-DD HH: mm: ss 此为最常用的时间格式。
timestamp 时间戳：1970.1.1至今的毫秒数，较为常用。

2.3.2 字段属性

列的字段属性如下所示：
Unsigned:

无符号的整数
声明了该列不能声明为负数

zerofill：

0填充的
不足设定位数的，自动用0填充。

自增：

通常理解为自增：即在上一条记录的基础上+1 （默认）
通常用来设计唯一的主键 ~index，必须是整数类型。
可以自定义设计主键自增的起始值和步长。

默认：

设置默认值。

2.3.3 字段规范

根据阿里巴巴的项目数据表规范，每一个数据表必须存在如下字段：
id -- 主键
version -- 乐观锁
is_delete -- 伪删除
gmt_create -- 创建时间
gmt_updata -- 修改时间

2.4 创建数据库表

-- 目标：创建一个school数据库
-- 创建学生表（列，字段）使用sql语言创建
-- 学生表包含如下列字段：学号int 登录密码varchar（20）姓名性别varchar（2），出生日期（datatime）家庭住址 email

-- 表名称和字段名称，尽量使用飘符 `` 括起来
-- 字符串使用 '' 括起来。
-- 字段定义语句之间用 "," 进行分隔。
-- PRIMARY KEY 主键，每一个表只有唯一一个的主键。

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

注意：创建完成之后，需要刷新才能进行查询

2.4.1 数据库表创建的格式

CREATE TABLE [IF NOT EXISTS] `student` (
  `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  PRIMARY KEY(`id`)  -- 指定主键
)

[] 内为选填,实际输入时不可有[]，其余部分为必填。

2.4.2 查看语句（重要）

此为逆向操作。较常使用，必须记住。

SHOW CREATE DATABASE school;	-- 查看 schoolers 数据库的创建语句
SHOW CREATE TABLE student;	-- 查看 student 数据表的定义语句
DESC student;  			-- 查看 student 表结构

2.4.3 数据表的属性

（一）engine的选择
传统上engine分为：MYISAM 和 INNODB，前者较老，后者为目前的默认选择。

二者的功能性对比如下：
| | MYISAM | INNODB |
| ---- | ---- | ---- |
| 事务支持 | 不支持 | 支持 |
| 外部行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为前者的2倍 |
常规的使用操作：

MYISAM：节约空间，速度较快。
INNODB：安全性较高，事务的处理，多表多用户操作。

二者物理空间上的差别

innoDB在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
MYISAM 对应文件：

*.frm 表结构的定义文件
*.MYD 数据文件（data)
*.MYI 索引文件（index）

（二）字符集编码方式
在创建表的伊始，需要指定字符集编码，通常设置默认的 UTF8：character-set-server=utf8

2.5 数据表的增删查改

-- 修改表名称
ALTER TABLE teacher RENAME AS teacher1;

-- 增加表的字段 
ALTER TABLE teacher1 ADD numbers INT(11)

-- 修改表的字段（重命名 ； 修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(100)  -- 修改约束,即字段类型
ALTER TABLE teacher1 CHANGE age age1 INT(1)   -- 字段重命名

-- 删除表字段
ALTER TABLE teacher1 DROP age1

-- 删除表
DROP TABLE IF EXISTS teacher1

所有的修改和删除操作，尽量加上判断，即 IF EXISTS 语句。避免报错。

3. MySQL数据管理

3.1 外键（了解）

外键用于在多张表之间建立联系（表层面）。
当某张表被另一张表的外键所关联时，该表不能删除。
可见外键的存在会在表层面导致复杂的相互联系，因此禁止在表层面使用外键连接。
项目中，在应用层联系表，即用java语言实现。

3.2 DML语言（要求全部记住）

DML语言，即database management language，数据库管理语言。
其用于数据库的一般性管理，不含查询操作。

3.2.1 增

使用结构化语句：

INSERT INTO `表名`(`字段名1`, `字段名2`, `字段名n`) VALUES('值1'), ('值2'), ('值n')

注意1：表名和字段名用飘符 ` 括起来，插入值用单引号 ''括起来。
注意2：字段名可以省略不写，那则是默认插入所有字段，即插入值的数量与字段数量匹配。
例如：

INSERT INTO `student`( `name` ) VALUES('zfy')  -- 插入单一字段
INSERT INTO `student`( `name`, `id`, `address` ) VALUES('zfy'), ('M202171599'), ('hust')  -- 插入多个字段

3.2.2 删

使用结构化语句：

DELETE FROM `表格` WHERE 索引条件;  -- 删除指定内容

DELETE FROM `表格`  -- 未指定索引条件，则删除该表所有内容。

TRUNCATE `表格`  -- 专业的清空表格内容指令

例如：

DELETE FROM `student` WHERE id = 1;  -- 删除id为1的学生

DELETE 和 TRUNCATE 清空表单的区别：

TRUNCATE 会归零自增量，是专业的方式。
DELETE FROM 不会归零子增量。

3.2.3 改

使用结构化语句：

UPDATE `表名` SET `（列）字段名1`, `（列）字段名2` = '值1', '值2' WHERE 指定的判别条件

若不指定后面的判别条件，则将该列所有值均修改为输入值。
WHERE后的判别条件可以是多个，用 and/or/not 等连接词连接。
修改值与字段名一一对应。
表名和字段名用飘符 ` 括起来，插入值用单引号 ''括起来。
例如：

UPDATE `student` SET `name` = '苍翠双子' WHERE `name` = 'zfy'  -- 单一条件定位
UPDATE `student` SET `name` = 'zfy', `pwd` = 'qq199911.24' WHERE `name` = '苍翠双子' AND `gender` = '女'  -- 多条件定位

4. DQL查询语句（最重点）

4.1 DQL概念简介

Data Query Language：数据查询语言

所有的查询操作都用它： Select
数据库中最核心的语言，是最重要的语句。

4.2 指定查询字段

4.2.1 SELECT 查询

使用结构化语句：

-- 查询该表的全部字段：
SELECT * FROM `表名`  

-- 查询指定字段：
SELECT `字段1`,`字段2` FROM `表名`  

-- 查询结果赋予别名：
SELECT `字段1` AS '别名1',`字段2` AS '别名2' FROM `表名` AS '表别名'

-- 函数  CONCAT(a, b)：
SELECT CONCAT('追加字段',`列（字段）名`) AS '列的新名字' FROM `表名`

-- 查询系统版本（函数）：
SELECT VERSION()

-- SELECT 用于简单计算
SELECT 计算式 AS 输出结果名称
SELECT 100*3-1 AS 计算结果

-- 查询自增的步长（变量）
SELECT @@auto_increment_increment

-- 列表值查询修改
SELECT `字段名`+1 AS '新名字' FROM `表名`
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM `result`

例：

SELECT CONCAT('姓名：',`studentname`) AS '新名字' FROM `student`

结果如下：

（二）DISTINCT去重查询
使用结构化关键词 DISTINCT 对查询去重。

SELECT DISTINCT `字段名` FROM `表名` -- 去除所有重复值

4.3 模糊查询

4.3.1 where 语句

作用：检索数据中符合条件的值。
其搜索条件由一个或者多个表达式组成，表达式的结果返回一个布尔值。

逻辑运算符，即与或非，对应符号为：&&与，｜｜或，！非。但一般使用英文表示，即 and，or，not。

4.3.2 like, in, % 和 _

这四个关键字符用于模糊查询。
其中 % 代表 任意数量的字符
_ 代表 一个字符位
like 是模糊查询 **包含 **被查询字段的元素，可以与 % 搭配。
in 是查询 具体的 一个或者多个值，通常不与 % 搭配。
举例：

-- 姓李，名字为两个字。
SELECT `studentno`, `studentname` FROM `student`
WHERE studentname LIKE '李_';  

 -- 名字中间带“嘉”的人，不一定3个字。
SELECT `studentno`, `studentname` FROM `student`
WHERE studentname LIKE '%嘉%'; 

-- 被查询字段名必须严格为'北京'， '河南洛阳'
SELECT `studentno`, `studentname` FROM `student`
WHERE `Address` IN '('北京', '河南洛阳')';  

-- 查询有出生日期的同学    即字段不为空
SELECT `studentno`, `studentname` FROM `student`
WHERE `borndate` IS NOT NULL;

4.4 联表查询

4.4.1 join on 联表查询

联表查询：顾名思义，从多张表中联合查询信息。

需求：确定需要查询的信息来自哪些表和哪些字段。
条件：确定被连接的两张表之间存在的 交叉点，即共有的字段名（列）
手段：共有 7种连接查询的方式，常用的有，左，中，右，查询。
由易到难原则：多张表的连接，先从两两开始，再逐步增加。

三种join on 的区别如下表所示：

操作	描述
inner join	如果表中至少有一个匹配，就返回查询行
left join	会返回左表中存在的所有查询值，即使右表并无匹配项
right join	会返回右表中存在的所有查询值，即使右表并无匹配项

代码示例：

-- 从3张表中，查询4个字段信息。
-- 视情况分别使用右连接和中连接链接两张表
SELECT s.studentno, studentname, subjectname, StudentResult 
FROM student s  -- 表student 简写为s
RIGHT JOIN result r  -- 表result 简写为r
ON r.`StudentNo` = s.`StudentNo`  -- 构建连接点
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`;

4.4.2 自联结

自联结，即将自己的表和自己的表联结，一张表拆成 两张一样 的即可。
需要先理清需要查询信息之间的 逻辑关系，再通过 **查询别名 **的方式，拆成两张表。
举例说明：
现有表如下：

categoryid	pid	categoryName
7	5	PS技术
6	3	web开发
2	1	信息技术
8	2	办公信息
4	3	数据库
5	1	美术设计
3	1	软件开发

需求：查询类别和技术名的对应，如：软件开发-数据库；软件开发-web开发。
问题分析：查询的两个对应关系现在存在一个字段内，通过前面俩字段实现索引。
现需要将其拆开对应，使用自连接查询。
父表：

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子表：

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

注意，子表的"pid"和父表的"categoryid"是一样的，这也是从一张表拆出来的根源。
使用自联结查询：

-- 自连接查询代码：
SELECT a.`categotyName` AS '父栏目', b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b  -- 一张表读/拆成两张表
WHERE a.`categoryid` = b.`pid`;        -- 自联结的节点

结果如下：

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

4.5 limit and order by 分页和排序

排序（order by）

根据某字段进行排序，升序（ASC）或者降序（DESC）。
语法：ORDER BY 字段名 ASC/DESC

分页（limit）

分页的意义：于数据库级别实现数据的隔离，减少数据库的压力。
语法：limit(查询起始下标, pageSize)即起始页和每页显示的数量。
limit语句必须处于sql语句的最后一行。

4.6 子查询与嵌套查询

子查询/嵌套查询即将查询条件 分步进行。
查询语句中的 条件语句为子查询语句，即嵌套新的查询。
层层查询，由里及外，即从最内部开始查询。
适用于条件嵌套复杂的查询，层层解构倒推。

-- 查询 高等数学-4 的所有考试结果（学号，科目编号，成绩），降序排列。
-- 使用子查询将条件分成三步进行：①查询考试结果 ②结果中科目为 高等数学-4 ③降序排列
SELECT `StudentNo`, `SubjectNo`, `StudentResult`
FROM `result`
WHERE subjectno = (
      SELECT subjectno FROM `subject`
      WHERE subjectname = '高等数学-4'
)
ORDER BY studentresult DESC;  -- 降序排列

4.7 分组和过滤

分组（GROUP BY）

根据指定字段进行分组。
通常要搭配having条件判别（过滤）语句使用，不能用where语句。

过滤（having）

判别（过滤）分组需要满足的次要条件。
该语句可以叠加使用，where语句则不行。

例如：

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：按照平均分大于80分组。
SELECT subjectname, AVG(studentresult) AS 平均分, MAX(studentresult), MIN(studentresult)
FROM result AS r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分 > 80  -- 分组的判别（过滤）条件

5. MySQL 函数

5.1 常用函数

①数学运算：

SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.3)  -- 向下取整
SELECT RAND()  -- 返回一个 0-1 之间的随机数
SELECT SIGN(10)  -- 判断一个数的符号， 0返回0，正数返回1，负数返回-1

②字符串函数

SELECT CHAR_LENGTH('我命由我不由天')  -- 返回字符串的长度
SELECT CONCAT('我', '爱', '编程')  -- 拼接字符串
SELECT INSERT('我爱编程helloworld', 2,1, '超级热爱')  -- 字符串替换，其形参有4。数字2意为：从2号位置开始替换；数字1意为：被替换的字符串长度。
SELECT LOWER('ZFY')  -- 小写字母
SELECT UPPER('zfy')  -- 大写字母
SELECT INSTR('zfy','y')  -- 返回第一次出现子串的索引值，形参有2.第一个是父串（被查询对象）。第二个是子串（查询对象）
SELECT REPLACE('会当水击三千里，自信人生二百年', '二百', '五百')  -- 替换指定字段
SELECT SUBSTR('会当水击三千里，自信人生二百年', 4, 6)  -- 截取指定长度的字符串，第一个数字是截取起点，第二个是截取长度。
SELECT REVERSE('名字倒着写')  -- 反转字符串

③时间和日期函数(需要记住)

SELECT CURRENT_DATE()  -- 获取当前日期
SELECT CURDATE()  -- 获取当前日期
SELECT NOW()  -- 获取当前的时间
SELECT LOCALTIME()  -- 本地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW())  -- 查询当前年份
SELECT MONTH(NOW())  -- 查询当前月份
SELECT DAY(NOW())  -- 查询当前天
SELECT HOUR(NOW())  -- 查询当前时
SELECT MINUTE(NOW())  -- 查询当前分钟（省略其他）
SELECT SECOND(NOW())  -- 查询当前秒钟（省略其他）

④系统相关函数

SELECT SYSTEM_USER()  -- 查询系统用户
SELECT USER()  -- 同上
SELECT VERSION()  -- 查询版本

5.2 聚合函数

常用数据处理/查询函数。

-- 统计表中的数据量 COUNT（），其有三种形式：
SELECT COUNT(`borndate`) FROM student;  -- 会忽略所有的null值。
SELECT COUNT(*) FROM student;  -- 不会忽略null值，本质是计算行数。
SELECT COUNT(1) FROM result;  -- 不会忽略null值，本质是计算行数。

-- 一般的统计指标函数
SELECT SUM(`studentresult`) AS 总和 FROM result  -- 计算总和
SELECT AVG(`studentresult`) AS 平均分 FROM result  -- 计算平均值
SELECT MAX(`studentresult`) AS 最大值 FROM result  -- 计算最大值
SELECT MIN(`studentresult`) AS 最小值 FROM result  -- 最小值

6. 事务

6.1 什么是事务

事务的定义：事务是一组操作的合称。事务内的操作，要么同时成功，要么同时失败。

事务的原则（ACID），事务具有的4个特性称为事务的原则：

原子性（Atomicity）：原子意为不可分割的最小个体。即事务内操作要么同时成功，要么同时失败。
一致性（Consistency）：针对一个事务操作，前后的状态一致。例如：A给B转钱，A少的一定是要B多的，这就是一致性。
持久性（Durability）：事务一旦提交成功，结果就会固化，具有不可逆的持久性。
隔离性（Isolation）：不同事务之间不能相互影响。这通常适用于多个用户同时操作。

违反隔离性的情况：

脏读：一个事务读取了另一个事务的数据。
不可重复读：在一个事务内读取表格中的某一行数据，多次读取结果不同。
幻读（虚读）：是指在一个事务内读到了别的事务插入的数据，导致前后读取不一致。

6.2 事务代码mysql

事务的基础流程代码：

事务开启的前置步骤为：SET autocommit = 0 即关闭自动提交。同理，事务关闭的后续步骤为：SET autocommit = 1
事务开启的代码：START TRANSACTION ：标志着事务的开启，直到结束代码出现，均划分为事务内。
事务提交的代码：COMMIT;：事务成功提交之后，所执行改变为不可逆的。
事务的撤回操作：ROLLBACK：仅在提交之前有用，用于撤回之前事务的操作。

-- mysql 是默认开启事务自动提交的
SET autocommit = 0  -- 关闭自动提交
SET autocommit = 1  -- 开启自动提交

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交
-- 事务开启
START TRANSACTION  -- 事务开启的标志，往后的sql语句均属于此事务内。

INSERT xx  -- 事务内操作1
INSERT xx  -- 事务内操作2

ROLLBACK;  -- 回滚：回到原来的样子，即事务失败
COMMIT;  -- 事务提交：即持久化，事务成功/结束

-- 事务结束：
SET autocommit = 1  -- 开启自动提交

事务的拓展代码：

SAVEPOINT 保存点1  -- 设置一个事务的存档点
ROLLBACK TO SAVEPOINT 保存点1  -- 回滚到保存点
RELEASE SAVEPOINT 保存点1  -- 销毁保存点

事务案例如下：

-- 事务 模拟转账
-- 创建数据库：
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf_general_ci
USE shop

-- 创建表单
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO account(`name`, `money`)
VALUE ('A', 2000.00), ('B', 1000.00)


-- 模拟转账：事务
SET autocommit = 0;  -- 关闭自动提交，即事务的前置步骤
START TRANSACTION  -- 开启事务

UPDATE account SET money = money-500 WHERE `name` = 'A';  -- A减500
UPDATE account SET money = money+500 WHERE `name` = 'B';  -- B加500

COMMIT;  -- 提交事务，事务即被持久化。
ROLLBACK;  -- 回滚，回到执行之前（该操作必须在提交之前，一旦提交即不可逆）。

SET autocommit = 1;  -- 自动提交恢复默认值，事务关闭的后续步骤

7. 索引

索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构。
即，索引的本事是一种数据结构。
索引是通过 树搜索结构 实现，其本质是通过更少的路径找到目标。

7.1 索引的分类

sql中索引的默认类型为 Btree。Btree一颗中序遍历结果有序的多路平衡树。
在一个表中，主键索引只能有一个，但唯一索引可以有多个。

主键索引（PRIMARY KEY）
由于主键的不可重复性，主键索引也是唯一的表示，仅能有一列标为主键索引。
唯一索引（UNIQUE KEY)
标明列的唯一性，即意为该列（名）是唯一的，避免重复列的出现。显然，多个列可以标识此索引。
常规索引（KEY/INDEX)
默认的索引，通过KEY/INDEX字符来设置。
全文索引（FullText)

用于在大容量数据库中快速定位。（相反，小容量无用）
不是所有的数据库引擎都有。

7.2 索引的基础语法

索引创建有两种途径：

在创建表的时候给字段添加索引（字段）。
创建完毕的表，追加（插入）索引。

-- 索引的使用

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引（索引名称）列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`StudentName`);

-- explain 分析sql的执行状况。
EXPLAIN SELECT * FROM student;  -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');  -- MATCH-AGAINST 匹配机制

8. 权限管理和备份

8.1 数据库权限管理

8.2 MySQL备份

数据库备份的方式：

直接物理拷贝文件。
使用sqlyog这种可视化工具手动导出。此处略。
在命令行导出 mysqldump 。命令行使用如下代码：
mysqldump -主机地址(hlocalhost) -权限(uroot) -p密码数据库名称表名1 表名2 表名3 >导出位置/文件名
注意，可以将多张表导入至一个文件。
命令行导入sql文件:cmd登录sql，use 数据库转至目标数据库。
source 路径/sql文件名即可以导入sql表文件。
如果是sql数据库文件，则无需跳转至目标数据库，直接source即可。

9. 数据库设计规范

9.1 数据库设计目标

糟糕的数据库设计：

数据冗余，浪费空间。
数据库插入和删除都会麻烦，异常。（因此要避免使用物理外键）
程序性能差

良好的数据库设计：

节省内存空间。
保证数据库的完整性。
方便我们开发系统。

9.2 数据库设计流程

数据库设计流程如下：

需求分析：分析业务和需要处理的数据库的需求，将需求一一分解至表单级别。
概要设计：根据分析的需求设计 E-R图即关系图。

以个人博客为例，实践数据库设计流程如下。
博客网页后台数据库设计：

收集信息，分析需求（落实到每张表）：

用户表（user）：用户登录注销，用户的个人信息，写博客，创建分类。
分类表（category）：文章分类，谁创建的。
文章表（blog）：存放文章信息。
友链表（links）：存放友链信息。
自定义表：系统信息，某个关键的字，或者一些主字段。

标识实体（将需求落实到每个字段）
标识实体之间的关系

写博客：user --> blog
创建分类：user --> category
关注：user --> user
友链：links
评论：user --> user --> blog

9.3 数据库设计的三大范式（面试考题）

9.3.1 为什么需要数据规范化？

不规范的数据库存在如下问题：

信息重复
更新异常
插入异常：即无法正常显示信息。
删除异常：即丢失有效信息。

9.3.2 三大范式

数据库设计的三大范式：
第一范式：满足原子性原则，即要求数据库的每一列都是不可再分的原子数据项。
第二范式：确保数据库表中的每一列都和主键相关，而不是只和主键的某一部分相关，即每张表只描述一件事情。这主要针对联合主键情况。
第三范式：确保数据表中每一列数据都和主键直接相关，而不是间接相关。
注：每一范式的前提都是满足前置范式。

举例理解范式具体内容：
（1）第一范式：
错误设计：

地址
青青草原，🐏村，肥羊小学

分析：该设计不满足第一范式原子性要求，因为内容可以被继续拆分为三块。
修改之后：

镇	村	详细地址
青青草原	🐏村	肥羊小学

（2）第二范式：
错误设计：

学号	姓名	系名	系主任	课程名	分数
001	喜羊羊	信息工程系	羊村村长	《如何打败灰太狼》	100
001	喜羊羊	信息工程系	羊村村长	《如何打败灰太狼它老婆》	100
001	喜羊羊	信息工程系	羊村村长	《如何收养小灰灰》	100
002	美羊羊	信息工程系	羊村村长	《如何收养小灰灰》	80
002	美羊羊	信息工程系	羊村村长	《如何打败灰太狼》	80
002	美羊羊	信息工程系	羊村村长	《如何打败灰太狼它老婆》	80
003	懒羊羊	没关系	黑大帅	《青草100种烹饪方法》	80
003	懒羊羊	没关系	黑大帅	《毛选》	0
003	懒羊羊	没关系	黑大帅	《思政》	0

分析：该表信息混杂，主键信息即对应课程信息也对应人员信息，应该拆分成两张表。
即将一个表中最有关联性的信息放在一张表中。
修改如下：

①课程安排表：

学号	姓名	课程名称	课程得分
001	喜羊羊	《如何打败灰太狼》	100
001	喜羊羊	《如何打败灰太狼它老婆》	100
001	喜羊羊	《如何收养小灰灰》	100
002	美羊羊	《如何打败灰太狼》	80
002	美羊羊	《如何打败灰太狼它老婆》	80
002	美羊羊	《如何收养小灰灰》	80
003	懒🐏	《青草100种烹饪方法》	80
003	懒🐏	《毛选》	0
003	懒🐏	《思政》	0

②人员安排表：

学号	姓名	系名	系主任
001	喜羊羊	信息工程系	羊村村长
001	喜羊羊	信息工程系	羊村村长
001	喜羊羊	信息工程系	羊村村长
002	美羊羊	信息工程系	羊村村长
002	美羊羊	信息工程系	羊村村长
002	美羊羊	信息工程系	羊村村长
003	懒🐏	没关系	黑大帅
003	懒🐏	没关系	黑大帅
003	懒🐏	没关系	黑大帅

（3）第三范式：

第三范式指：列属性不能依赖于任何非主属性，即列直接不能存在依赖关系。
第三范式确保非主键列之间没有 传递函数依赖关系，也就是要 消除传递依赖。
根据此理论分析，在《人员安排表》中：
系主任依赖系名系名又依赖学号，即列中存在依赖传递关系。故将其继续拆为两张表。

③学生表：

学号	姓名	系
001	喜羊羊	信息工程系
002	美羊羊	信息工程系
003	懒羊羊	没关系

④系表：

系	系主任
信息工程系	羊村村长
没关系	黑大帅

9.3.3 规范性和性能的问题（面试重点）

规范性是指，严格遵守三大范式的设计要求，而性能则是数据库运行的具体性能。说到底，规范性也是为性能服务的。
规范性和性能不能兼得，二者之间需要寻找一个均衡。

项目要求如下：

关联查询的表不得超过** 三张表**。
考虑商业化的需求和目标（成本和用户体验等），数据库的性能更加重要（而不是规范性）。
从性能的角度出发，往往会给某些表特意增加一些冗余的字段（从多表查询变为单表查询）。

10. JDBC（重点）

10.1 数据库驱动

程序通过数据库驱动，跟数据库打交道。就像任何其他驱动做的一样。
而JDBC框架管理众多数据库驱动，是一个集成的环境。
除了默认的 java.sql，javax.sql包，还需要导入** 数据库驱动包：**mysql-connector-java

10.2 JDBC测试代码

JDBC进行sql操作步骤如下：

加载数据库驱动
提交用户信息和url
连接成功数据库对象
指定sql的对象
以 sql 对象去执行 sql语句，若存在结果则返回结果
释放连接

public class jdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
        // 1. 固定写法，加载驱动。
        Class.forName("com.mysql.jdbc.Driver");  // 存在异常则抛出

        // 2. 提交用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?  // 连接的基本信息
        useUnicode=true&characterEncoding=utf8&useSSL=true";  // 调用的参数行，用 & 连接
        String username = "root";
        String password = "123456";

        // 3. 连接成功，获得数据库对象connection：
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4. 获得SQL执行对象 Statement：
        Statement statement = connection.createStatement();

        // 5. St对象执行sql语句，并返回结果：
        String sql1 = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql1);  // 返回结果集，结果集中封装了全部的查询结果
        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("pwd=" + resultSet.getObject("password"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
        }


        // 6. 释放连接，关闭遵循砌墙原则，后来的先关。
        resultSet.close();
        statement.close();
        connection.close();
    }
}

10.3 JDBC对象解释

DriverManager
此为数据库驱动对象，用于加载数据库驱动。

Class.forName("com.mysql.jdbc.Driver");  // 加载驱动，存在异常则抛出

URL
提交数据库连接的具体信息和要求。

String url = "jdbc:mysql://localhost:3306/jdbcstudy?
useUnicode=true&characterEncoding=utf8&useSSL=true";

正则表达式如下：jdbc:mysql://主机地址:数据库对应端口号/导入数据库名称?参数1&参数2&参数3
// useUnicode=true 启动中文字符编码集
// characterEncoding=utf8 字符集为utf8
// useSSL=true 启动安全连接
// mysql端口号 -- 3306

Connection
此为数据库连接对象，即代表数据库本身,可以进行数据库级别的操作。

connection.rollback();  // 回滚
connection.commit();  // 提交
connection.setAutoCommit();  // 设置自动提交

Statement & PrepareStatement

此为数据库执行对象，通过此对象进行sql语句操作。
前者存在sql注入问题，后者是改进版本，不存在此问题。必须使用后者。

preparestatement.execute();  // 执行任何SQL语句，但效率不是最高。
preparestatement.executeQuery();  // sql查询操作，**返回结果对象 ResultSet。**
preparestatement.executeUpdate();  // 执行所有的更新sql语句，包括插入，删除，更新，**返回一个受影响的行数。**

ResultSet
sql执行结果对象，从该对象中读取结果。

获取指定的数据类型：

//  不知道具体的数据类型
resultSet.getobject();

//  知道具体的数据类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

对结果进行遍历，指针操作：

resultSet.beforeFirst();  // 移动到最前面
resultSet.afterLastt();  // 移动到最后面
resultSet.next();  // 移动到下一个数据
resultSet.Previous();  // 移动前一行
resultSet.absolute(row);  // 移动到指定行

资源释放

这是 JDBC 操作sql的最后一步，必须记得，很费资源的。
资源释放代码遵循砌墙原则，即后开的对象先释放。

//  关闭由内向外
resultSet.close();
statement.close();
connection.close();

10.4 封装sql连接工具类

（一）将连接sql的必要步骤写成class进行封装，后续仅需要调用。

package com.zfy.lesson01.Utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

//  创建连接工具类
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    //  连接信息获取和数据库驱动载入
    static{
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");  // 读取文件流
            Properties properties = new Properties();  // 新建属性对象
            properties.load(in);  // 获取属性对象

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //  驱动仅需要加载一次，也放在静态内

            Class.forName(driver);  //  获取驱动

        } catch (Exception e ){
            e.printStackTrace();
        }
    }

    //  获取连接函数
    public static Connection getConnection() throws SQLException{
        Connection connection =  DriverManager.getConnection(url, username, password);
        return connection;
    }

    //  释放资源函数
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

（二）使用封装的类进行sql调用

package com.zfy.lesson01.Utils;
import com.zfy.lesson01.Utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;  // 注意，try-catch内对象无法被获取,因此先在此创建对象。
        Statement st = null;
        ResultSet rs = null;

        try {
           conn = JdbcUtils.getConnection();  // 创建连接对象
           st = conn.createStatement();  // 创建执行对象
           String sql = "INSERT INTO users(id, `NAME`, `password`, `email`, `birthday`)" +
                   "VALUES(4, 'ZFY', '123456', '3178674428@qq.com', '2000-4-20')";
           int i = st.executeUpdate(sql);
           if (i>0){
               System.out.println("插入成功!");
           }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

10.5 sql注入问题

sql注入问题的产生是因为sql没有严格的输入审查机制。
可以利用这个漏洞，盗取sql数据库信息，甚至进行修改删除等恶意操作。
实例:

// 定义sql登录语句：用户名 + 密码 登录
String sql = "select * from users where `NAME` = '"+username+"' AND `password` = '"+password+"'";

sql注入漏洞，代码如下：

Login("'or' '1 = 1", "123456");

其原理为：非法输入与原生代码混合，改变了代码意思。
'or' '1=1'恒成立，所以查询出所有结果。

10.6 PreparedStatement 解决sql注入问题

prepareStatement对象解决了sql注入问题，一定使用该方式执行sql。
preparedstatement使用占位符代替参数，经过预编译之后，再手动将参数一一传入。
PStatement执行sql语句的四步操作：①写sql语句，并使用占位符代替参数。②预编译sql语句，③依次手动给参数赋值，④执行操作
PStatement将传入参数再次进行 "" 包裹，避免了恶意篡改问题。
以sql插入为例：

package com.zfy.lesson01.Utils;
import com.zfy.lesson01.Utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class preparedStatement {
    public static void main(String[] args) {

        // 创建对象
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 1. Prepardstatement对象使用 占位符代替参数，使得安全性得到保证。
            String sql = "insert into users(id, `NAME`, `PASSWORD`, `email`, `birthday`)value(?,?,?,?,?)";

            // 2. 预编译SQL，先写sql语句，然后不执行。
            st = conn.prepareStatement(sql);

            // 3. 手动给参数 依次赋值
            st.setInt(1,4);  // id值
            st.setString(2,"ZFY");
            st.setString(3,"123456");
            st.setString(4,"3178674429@11.com");

            // 创建一个Date对象，注意是java.util.Date,而不是sql的java.sql.Date对象。
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            // 4. 执行操作
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

总结增删改查操作：

// 增
String sql01 = "insert into users(id, `NAME`, `PASSWORD`, `email`, `birthday`)value(?,?,?,?,?)";
st = conn.prepareStatement(sql01);
st.setInt(1,4); 
st.setString(2,"ZFY");
st.setString(3,"123456");
st.setString(4,"3178674429@11.com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
rt = st.executeUpdate();
//删
String sql02 = "delete from users where id = ?";
st = conn.prepareStatement(sql02);
st.setInt(1,4);
rt = st.executeUpdate();

//改
String sql03 = "updata users set `NAME`=? where id=?;"l
st = conn.prepareStatement(sql03);
st.setString(1,"狂神");
st.setInt(2,1);
rt = st.executeUpdate();

//查
String sql04 = "select * from users where id=?;"l
st = conn.prepareStatement(sql04);
st.setInt(1,1);
rt = st.executeQuery();  // 查询函数与 增删改不同，为st.executeQuery()

posted @ 2022-05-12 16:42 青梧成林2022 阅读(199) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· JavaWeb02

· Springboot项目-学生管理系统

· Mysql数据库

· MySQL

· MySQL简单了解

阅读排行：
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能

公告

昵称：青梧成林2022
园龄： 3年2个月
粉丝： 3
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

文章分类

企业级Web应用开发(7)

青梧成林2022

MySQL 入门学习

1. 初识MySQL

1.1 数据库基本知识

1.2 MySQL 基本指令

1.2.1 命令行指令

1.3. sqlyog

2. 操作数据库

2.1 mysql基本语法

2.2 基本指令

2.3 数据类型与字段属性

2.3.1 数据类型

2.3.2 字段属性

2.3.3 字段规范

2.4 创建数据库表

2.4.1 数据库表创建的格式

2.4.2 查看语句 （重要）

2.4.3 数据表的属性

2.5 数据表的增删查改

3. MySQL数据管理

3.1 外键（了解）

3.2 DML语言 （要求全部记住）

3.2.1 增

3.2.2 删

3.2.3 改

4. DQL查询语句（最重点）

4.1 DQL概念简介

4.2 指定查询字段

4.2.1 SELECT 查询

4.3 模糊查询

4.3.1 where 语句

4.3.2 like, in, % 和 _

4.4 联表查询

4.4.1 join on 联表查询

4.4.2 自联结

4.5 limit and order by 分页和排序

4.6 子查询与嵌套查询

4.7 分组和过滤

5. MySQL 函数

5.1 常用函数

5.2 聚合函数

6. 事务

6.1 什么是事务

6.2 事务代码mysql

7. 索引

7.1 索引的分类

7.2 索引的基础语法

8. 权限管理和备份

8.1 数据库权限管理

8.2 MySQL备份

9. 数据库设计规范

9.1 数据库设计目标

9.2 数据库设计流程

9.3 数据库设计的三大范式（面试考题）

9.3.1 为什么需要数据规范化？

9.3.2 三大范式

9.3.3 规范性和性能的问题（面试重点）

10. JDBC（重点）

10.1 数据库驱动

10.2 JDBC测试代码

10.3 JDBC对象解释

10.4 封装sql连接工具类

10.5 sql注入问题

10.6 PreparedStatement 解决sql注入问题

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

文章分类

阅读排行榜

推荐排行榜

2.4.2 查看语句（重要）

3.2 DML语言（要求全部记住）