Mysql(数据库专块学习：一)

数据库概念

• 数据库（DB，DataBase）
• 概念：数据仓库，软件，安装在操作系统（Window，linux，mac…）之上！SQL可以存储大量的数据。500万！
• 作用：存储数据，管理数据

数据库分类

关系型数据库（SQl）

• MySQL，Oracle，Sql Server,DB2,SQLlite

非关系型数据库(NoSQL) not Only

• Redis，MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性决定

DBMS(数据库管理系统)

• 数据库管理软件，科学有效的管理我们的数据。维护和获取数据。
• MySQL，数据库管理系统！

MySQL简介

MySQL是一个** 关系型数据库管理系统 **
前世：瑞典MySQL AB公司
今生：属于Oracle旗下产品
MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
开软的数据库软件
特点：体积小、速度快、总体拥有成本低
作用：中小型网站，或者大型网站，集群
官网：https://www.mysql.com/

安装建议：
1.尽量不要使用exe
2.尽量使用安装包安装
安装视频：https://www.bilibili.com/video/BV1NJ411J79W?p=3&spm_id_from=pageDriver

SQLyog：
https://blog.csdn.net/Huangyuhua068/article/details/82027442

连接数据库

命令行连接！（所有的语句用 ; 结尾）

mysql -uroot -p12345  --连接数据库

update mysql.user set authentication_string=password('12345') where user='root' and Host='localhost'; --更新密码

flush privileges; --刷新权限
-------------------------------------
 show databases; --查看所有数据库
 use XXX(数据库名); --使用数据库名
 show tables; --查看XXX(数据库)的所有表
 describe XXXX(表名); --查看XXXX(表名)的字段结构
--------------------------------------
create database XXX(数据库名); --创建一个数据库
exit; --退出连接
-- 单行注释
/* 多行注释 */

• 数据库XX语言 CRUD(增删改查)
  • DDL（数据定义库语言）
  • DML（数据库操作语言）
  • DQL（数据库查询语言）
  • DCL（数据库控制语言）

操作数据库

》 操作数据库(了解)

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] XXX(数据库);

2、删除数据库

DROP DATABASE [IF EXISTS] XXX(数据库);

3、使用数据库

--  ` 在tab键的上方，如果你的表名或者字段名是一个特殊字符，则需要带上
USE `XXX`(数据库);

4、查看数据库

--查看所有数据库
SHOW DATABASES;

》操作数据库中的表(了解)

1、创建数据表
-注意点

1. 使用英文的符号 （） , ` ’
2. 表的名称、字段等尽量使用 `` 括起来
3. 字符串用单引号 ‘’ 括起来
4. 所有语句结束加,（英文的）最后一个不用加
5. PRIMARY KEY主键，一般一个表只有唯一的主键

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '名称',
`pwd` VARCHAR(10) NOT NULL DEFAULT '12345' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`phone` VARCHAR(15) DEFAULT NULL COMMENT '电话号码',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
)[表类型][字符集设置][注释]

2、查看数据库内容

SHOW CREATE DATABASE 数据库名称 --查看数据库的语句
SHOW CREATE TABLE 表名称 --查看数据表的定义语句
DESC 表名称 --查看表的结构

3、修改表

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS teacher
-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher ADD age INT(11)

-- 修改表的字段 （重命名，修改约束） 
 -- 修改属性：ALTER TABLE 表名 MODIFY 字段名 列属性 
ALTER TABLE teacher MODIFY age VARCHAR(11)
 -- 字段重命名：ALTER TABLE 表名 CHANGE  旧字段名 新字段名 列属性 
ALTER TABLE teacher CHANGE age age1 INT(1)

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher DROP age1

格式：

 ALTER TABLE 旧表名 RENAME AS 新表名 -- 修改表名 
 ALTER TABLE 表名 ADD 字段名 列属性 -- 增加表的字段
 ALTER TABLE 表名 MODIFY 字段名 列属性  -- 修改属性
 ALTER TABLE 表名 CHANGE  旧字段名 新字段名 列属性  -- 字段重命名+修改属性
 ALTER TABLE 表名 DROP 字段名  -- 删除表的字段

最终结论：change用来字段重命名，不能修改字段类型和约束； modify不用来字段重命名，只能修改字段类型和约束；

4、删除表

-- 删除表（如果存在）
DROP TABLE [IF EXISTS] 表名

所有的创建和删除操作尽量加上判断，以免报错
注意点：

• 字段名，使用``包裹
• 注释 – /**/
• sql关键字大小写不敏感，建议大家写小写
• 所有符号用英文！

》 操作数据库表中的数据(了解)

数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 最常用
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节（精度问题！）
• decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

• char 字符串固定大小的 0~255
• varChar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期
java.util.Date

• date YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 1970.1.1到至今的毫秒数 时间戳 也较为常用
• yaer 年份

null

• 没有值，未知
• 注意，不要使用NULL进行运算，结果为NULL

数据库的字段属性（重点）

Unsigned:

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

zerofill

• 0填充的
• 不足的位数，使用0来填充，int(3),1 ---- 001

自增

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

为空 null 和 not null

• 假设设置为 not null ，如果不给它赋值，就会报错！
• null，如果不填写值，默认为null

默认：

• 设置默认的值

• sex，默认值为男，如果不指定，则会有默认的值。

• 每个表都必须存在以下五个字段，表示一个记录存在的意义：
  id 主键
  version 乐观锁
  is_delect 伪删除
  gmt_create 创建时间
  gmt_update 修改时间

• MySQL中数据类型的长度问题总结：
  https://blog.csdn.net/YAruli/article/details/79187814

数据表的类型

• INNODB：默认使用
• MYISAM：早些年使用的

类名	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MYISAM的2倍

常规使用操作：

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

在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件存储

MySQL引擎在物理文件上的区别

• INNODB 在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（idata）

设置数据表的字符集编码

CHAESET=utf8

• 不设置的话，会是mysql的默认字符集编码Latin1（不支持中文）
• 可以在my.ini中配置默认编码
  打开my.ini以后，在[client]和[mysqld]下面均加上default-character-set=utf8，保存并关闭（mysqld中增加如果出错，可以试character-set-server=utf8）
  https://www.jianshu.com/p/ec0c86ee3e04

MYSQL数据管理

外键（了解即可）

方式一：

CREATE TABLE IF NOT EXISTS `grade`(
`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`grade_name` VARCHAR(30) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表中的grade_id 字段，要去引用年级表中的grade_id
-- 定义外键 key
-- 给这个外键添加约束（执行引用）  references(引用)
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '名称',
`pwd` VARCHAR(10) NOT NULL DEFAULT '12345' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`phone` VARCHAR(15) DEFAULT NULL COMMENT '电话号码',
`grade_id` INT(11) NOT NULL COMMENT '编辑编号',
PRIMARY KEY(`id`),
KEY `FK_grade_id` (`grade_id`),
CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：

CREATE TABLE IF NOT EXISTS `grade`(
`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`grade_name` VARCHAR(30) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '名称',
`pwd` VARCHAR(10) NOT NULL DEFAULT '12345' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`phone` VARCHAR(15) DEFAULT NULL COMMENT '电话号码',
`grade_id` INT(11) NOT NULL COMMENT '编辑编号',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表时，没有外键关系，额外添加
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 被引用的表 (被引用的字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`)

• 删除有外键关系的表的时候，必须要先删除引用别人的表（从表），在删除被引用的表（主表）
• 以上的操作都是物理外键，数据库级别的外键，不建议使用！（避免数据过多造成困扰，了解即可）
  最佳实践
• 数据库只是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键，使用程序来实现

DML语言（重要，全部记住）

**数据库意义：**数据存储，数据管理
DML语言：数据操作语言

• insert
• uodate
• delete

添加

-- 插入语句（添加）
-- INSERT INTO `表名` ([`字段名1`,`字段名2`,...]) VALUES('值1','值2','值3',...),('值X1','值X2','值X3',...),...
INSERT INTO `grade` (`grade_name`) VALUES('二班'),('三班')

INSERT INTO `student` (`name`,`pwd`,`sex`,`grade_id`) VALUES('张三','123','男',1),('李四','123','男',1)

-- 由于主键自增，可以省略（如果不写表的字段，它会依次一一匹配）
INSERT INTO `grade` VALUES(4,'四班')
INSERT INTO `student` VALUES(7,'张三','123','男','2015-02-01','西安','122134252',2)

• 语法：INSERT INTO 表名 ([字段名1,字段名2,…]) VALUES(值1,值2,值3,…),(值X1,值X2,值X3,…),…
• 注意事项：

1. 字段和字段之间使用，英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可values(),()

修改

update 修改谁 （条件） set 原来的值 = 新值

-- 修改学员名字
UPDATE `student` SET `name`='糖糖' WHERE id=5;

-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name`='lili'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='娜娜',sex='女' WHERE id=7

-- 语法：
-- UPDATE `表名` SET `字段1`='value',[`字段`='值',...] WHERE 条件

条件：where 子句 运算符 id 等于某个值，大于某个值，在某个区间内修改…

删除

delete命令

-- 删除数据（避免这样写，会删除所有数据）
-- DELETE FROM `表名`
DELETE FROM `student`

-- 删除指定数据
--  DELETE FROM `表名` WHERE 条件
DELETE FROM `student` WHERE id=1

truncate命令
作用：安全清楚一个数据库表，表的结构和索引约束不会变！

-- 清空数据表
TRUNCATE `表名`
TRUNCATE `student`

delete和truncate的区别
- 相同点：都能删除数据，都不会删除表的结构
- 不同点：
- truncate 重新设置，自增列的计数器会归零
- - truncate 不会影响事务

DELETE FROM `student` -- 不会影响自增，自增从删除的数据中延续
TRUNCATE `student`  -- 会影响自增，自增重新开始排序

了解即可：delete删除的问题，重启数据库，表现现象

• INNODEB：自增列会重新从1开始（存在内存当中的，断电即失）
• MYISAM：继续从上一个则增量开始（存在文件中，不会丢失）

DQL语言（查询数据–最重点）

（Date Query Language：数据库查询语言）

• 所有的查询操作都用它， select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语言
• 使用频率最高的语句

sql完整语法：

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM table_name[as table_alias]
[left|right|inner join table_name2] -- 联合查询
[WHERE…]  -- 指定结果需要满足的条件
[GROUP BY…]  -- 指定结果按照哪几个字段来分组
[HAVING…]  -- 过滤分组的记录仪必须满足的次要条件
[ORDER BY…]  -- 指定查询记录按一个或者多个条件排序
[LIMIT {[offset,]row_count|row)countOFFSET offset}];
-- 指定查询的记录从哪条至哪条

指定查询字段

-- 查询全部学生 
SELECT * FROM student

-- 查询指定字段
-- 别名，给结果起一个名字 As 可以给字段起别名，也可以给表起别名
SELECT `studentNo` AS '学号',`studentname` AS `学生姓名` FROM student AS a
 
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',studentname) AS '姓名' FROM student

语法：select 字段,… from 表

有的时候，列名字不是那边的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名
去重：distinct

-- 查询参加了考试的学生
SELECT * FROM result -- 查询全部考试成绩
SELECT 	`studentno` FROM result -- 查询 参加考试的学生
SELECT DISTINCT `studentno` FROM result -- 去重

数据库的列（表达式）

SELECT VERSION() -- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩+1 查看
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量…
select 表示式 from 表名

where条件子句

• 作用：检索数据中符合条件的值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个值都为真，结果真
or II	a or b aIIb	逻辑或，其中一个为真，则结果为真
Not !	not a !a	逻辑非，真为假，假为真

模糊查询：比较运算符

运算符	语法	描述
is Null	a is null	如果a是空(null)，结果为真
is not Null	a is not null	如果a不为空(not null)，结果为真
between … and …	x between a and b	x包含在a和b区间之间（包含，a和b）[a,b]
like	a like b	SQL匹配，如果a匹配b,则结果为真
in	a in (a1,a2,a3…)	假设a在a1,或者a2…其中的某一个值中，结果为真

-- 查询张同学
-- like 结合 %（代表0到任意个字符）  _（一个字符）
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%'

-- 查询姓刘的同学，名字后面只有一个字
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张_'

-- 查询姓刘的同学，名字后面只有两个字
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张__'

-- ==============in===========
-- 查询1000,1001号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001)

联表查询

JOIN 对比

-- =============联表查询 join===============
SELECT s.studentno ,s.studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno

-- right join
SELECT s.studentno ,s.studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno

-- left join
SELECT s.studentno ,s.studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接：
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

分页和排序

排序 ORDER BY

-- 排序： 升序ASC , 降序 DESC
-- order by 通过哪个字段排序
-- 查询的结果根据 结果降序，排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY studentresult DESC

排序 LIMIT

-- 缓解数据压力
-- 语法： limit 当前页，页面的大小
-- 网页的应用： 当前，总的页数，页面的大小
-- 第一页 LIMIT 0,5 1~5       （1-1）*5
-- 第二页 LIMIT 5,5 6~10      （2-1）*5
-- 第三页 LIMIT 10,5 11~15    （3-1）*5
-- 第pageNum页  
-- LIMIT (pageNum-1)*pageSize,pageSize
-- [pageSize:页面条数]
-- [(pageNum-1)*pageSize：起始值]
-- [pageNum：当前页]
-- [数据总数/pageSize=总页数]
SELECT s.`studentno`,` studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY studentresult DESC
LIMIT 0,2

语法：limit 查询起始下标，pageSize

子查询

-- 查询课程是“高等数学-2”分数不小于80分的学生姓名和学号
SELECT studentno,studentname FROM student WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>50 AND `subjectno`=(
SELECT subjectno FROM `subject` WHERE `subjectname`='高等数学-2')
)

分组和过滤

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`studentno` -- 通过什么字段来分组
HAVING 平均分>60

MySQL的常用函数

官网参考：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('你是小可爱') -- 字符串长度  5
SELECT CONCAT('你','是','小可爱') -- 拼接字符串  你是小可爱
SELECT INSERT('你是小可爱',1,2,'') -- 查询 替换  小可爱
SELECT LOWER('HelloWorld')  -- 全转小写字母 helloworld
SELECT UPPER('helloworld')  -- 全转大写字母 HELLOWORLD
SELECT INSTR('HelloWorld','o') -- 返回第一次出现的字串的索引 5
SELECT REPLACE('HelloWorld','Hello','Hi ')  -- 替换出现的字符串
SELECT SUBSTR('你是小可爱',2,3) -- 返回指定的子字符串 （源字符串，截取位置，截取长度）  是小可
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()

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	求平均值
max()	最大值
min()	最小值
…	…

-- ========== 聚合函数 ============
SELECT COUNT(studentname) FROM student -- COUNT(指定列),忽略所有的null值
SELECT COUNT(*) FROM student -- COUNT(*),不会忽略null值,本质计算行数
SELECT COUNT(1) FROM student -- COUNT(1),不会忽略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 

数据库级别的MD5加密（扩展）

-- 明文密码
INSERT INTO testMD5 VALUES(1,'aa','12345'),(2,'lili','12345'),(3,'bibi','12345')
-- 加密
UPDATE testMD5 SET pwd=MD5(pwd)
-- 插入加密
INSERT INTO testMD5 VALUES(4,'cici',MD5('12345')),(5,'qoqo',MD5('12345'))
-- 对比加密后的密码
SELECT * FROM testMd5 WHERE NAME='aa' AND pwd=MD5('12345')

事务

事务原则 ：ACID原则（原子性，一致性，隔离性，持久性）
参考连接：https://blog.csdn.net/dengjili/article/details/82468576/

1. 原子性（Atomicity）
   要么都成功，要么都失败
2. 一致性（Consistency）
   事务前后的数据完整性要保持一致
3. 持久化（Durability） --事务提交
   事务一旦提交则不可逆，被持久化到数据库中。
4. 隔离性（Isolation）
   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的。不能被其他事务的操作数据所干扰，事务之间相互隔离。

   隔离产生的问题
   脏读：指一个事务读取了另外一个事务未提交的数据。
   不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
   虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

-- ===========事务=================
-- Mysql 是默认开启事务自动提交的
SET autocommit = 0;
SET autocommit = 1;

-- 手动处理事务
SET auto = 0; -- 关闭自动提交

-- 事务处理
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT XX
UPDATE XX

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK

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

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL ,
`money` DECIMAL(9,2) NOT NULL ,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 模拟转账：事务
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;

索引

MySQL官方对索引的定义为：索引（Index）是帮助mySQl高效获取数据的数据结构。0.5s 0.00001s
提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

主键索引（PRIMARY KEY）

• 唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引(UNIQUE KEY)

• 避免重复的列出现，唯一索引可以重复，多个列都可以是标识位，唯一索引

常规索引(KEY/INDEX)

• 默认的，index/key关键字类设置

全文索引（FULLTEXT）

• 在特定的数据库引擎下才有，MYISAM
• 快速定位数据

【MySQL优化】——看懂explain
https://blog.csdn.net/jiadajing267/article/details/81269067

-- =========================索引的使用==========================
-- 1.创建表的时候添加索引
-- 2.创建完毕后，添加索引

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

-- 增加一个全文索引（索引名） 列名
ALTER TABLE student ADD FULLTEXT INDEX `studentName`(`studentname`)

-- Explain 分析sql执行
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

测试索引

CREATE TABLE `app_user`(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`phone` VARCHAR(20) DEFAULT '' COMMENT '电话号码',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男，1:女',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE =INNODB DEFAULT CHARSET =utf8 COMMENT='app用户表'

DELIMITER $$ -- 写函数之前必须写，标志
CREATE FUNCTION ()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000; 
   DECLARE i INT DEFAULT 0;
WHILE i<num DO
	-- 查询
	INSERT INTO `app_user`(`name`,`phone`,`gender`,`password`)
	VALUES(CONCAT('用户',i),CONCAT('18',FLOOR(RAND()*((999999999-10000000)+100000000))),
	FLOOR(RAND()*2),
	UUID());
	  SET i = i+1;
   END WHILE;
   RETURN i;
END

SELECT mock_data();
SELECT * FROM `app_user` WHERE `name`='用户9999'  -- 执行耗时   : 1.016 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'  

-- id_表名_字段名
-- create Index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM `app_user` WHERE `name`='用户9999' -- 执行耗时   : 0.016 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'  

索引在小数据量的时候，影响不大，但是在大数据的时候，区别十份明显

索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构
hash类型的索引
btree:Innodb的默认数据结构
阅读:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

数据库备份

• 作用：

1. 确保重要的数据不丢失
2. 数据转移

• mysql数据库备份的方式：

1. 直接拷贝物理文件

2. 在SQLyog这种可视化工具中手动导出
   在想要导出的表或者库中，右击，选择备份或者导出
   

3. 使用命令行导出mysql dump命令行使用

# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置:/文件名
mysqldump -hlocalhost -uroot -p12345 school student >D:/a.sql

# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置:/文件名
mysqldump -hlocalhost -uroot -p12345 school student result >D:/a.sql

权限管理

• 用户管理：

SQLyog:可视化管理

用户表：mysql.user

-- 创建用户
CREATE USER tt IDENTIFIED BY '12345'
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('12345')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR tt= PASSWORD('12345')
-- 重命名
RENAME USER tt TO tangtang

-- 用户授权 ALL PRIVILEGES 全部权限， 库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO tangtang
 
 -- 查询权限
 SHOW GRANTS FOR tangtang -- 查看指定用户权限
 SHOW GRANTS FOR root@localhost 

-- 插销权限 
REVOKE ALL PRIVILEGES ON *.* FROM tangtang
 
 -- 删除用户
 DROP USER tangtang
 
# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql
mysql -u用户名 -p密码 库名<备份文件

假设你要备份数据库，防止数据丢失。
把数据库的sql文件互发

数据库的规范设计

为什么需要设计

糟糕的数据库设计

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
• 程序性能差
  良好的数据库设计
• 节省内存空间
• 保证数据库的完整性
• 方便开发系统
  软件开发中，关于数据库的设计
• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图E-R图
  设计步骤（如：个人博客）
• 收集信息，分析需求
  1. 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  2. 分类表（文章分类，创建人）
  3. 文章表（文章信息）
  4. 友链表（友链信息）
  5. 自定义表（系统信息，某个关键字，或者一些主字段 key:value）
• 标识实体（把需求）

三大范式

• 数据规范化作用：
  避免数据重复、更新异常、插入异常(无法正常显示信息)、删除异常（丢失有效信息）

三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式
每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键相关，而不能直接相关

规范化和性能问题
关联查询的表不超过三张
- 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑规范性
- 故意给某些表增加一些冗余的字段（多表查询变成单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

jdbc（重要）

• 驱动：应用程序和数据库直接依靠驱动来连接
• sun公司为了简化开发人员的(对数据库的统一)操作，提供了一个（java操作数据库的）规范，俗称JDBC，这些规范的实现由具体的厂商来做。
  对应开发人员来说，只需要掌握JDBC接口的操作即可
  
  java.sql
  javax.sql
  还需要导入一个数据库驱动包
  https://downloads.mysql.com/archives/c-j/

示例

• 创建测试数据库

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `password` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1. 创建一个普通项目
2. 导入jar包

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

        //2.用户信息和url  useUnicode:是否使用中文&characterEncoding字符集使用&useSSL是否安全连接
        String url = "jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String userName="root";
        String password="12345";

        //3.连接成功，数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url, userName, password);
        //4.执行sql的对象 statement执行sql的对象
        Statement statement = connection.createStatement();
        // 5.执行sql的对象，以及执行sql
        String sql="select * from users";
        ResultSet resultSet = statement.executeQuery(sql); //结果集中存放了查询的结果
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("===========================================");
        }
        // 6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

步骤总结：

• 加载驱动
• 连接数据库DriverManager
• 获得执行sql的对象Statement
• 获得返回的结果集
• 释放连接

DriverManager

    //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //不推荐使用，会初始化两遍
    Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动，推荐使用
    
	 //connection 代表数据库
	 //数据库设置自动提交
	 connection.setAutoCommit();
	 //事务提交
	 connection.commit();
	 //事务回滚
	 connection.rollback();

URL

 String url = "jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf8&useSSL=true";
//jdbc:mysql://主机:端口号/数据库名?参数1&参数2&参数3

//oralce --1521 
//jdbc:oralce:thin:@localhost:1521:sid

Statement/PreparedStatement 执行SQL的对象

   String sql="select * from users";
   statement.executeQuery(); //查询操作返回 resultSet
   statement.execute();//执行任何sql语句
   statement.executeLargeUpdate();//更新、插入、删除。返回受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

       //不知道具体类型的情况下
       resultSet.getObject();
       //如果知道列的类型就使用指定的类型
       resultSet.getString();
       resultSet.getInt();
       resultSet.getFloat();
       resultSet.getDate();

遍历、指针

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

释放资源

	//释放资源
       resultSet.close();
       statement.close();
       connection.close();  //消耗资源用完关掉

Statement对象

JDBC中的statement对于向数据库发送sql语句，想要完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增，删，改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行发生了变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作 - create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

     Statement statement = connection.createStatement();
     String sql = "insert into user(...) values(...)";
     int num=statement.executeUpdate(sql);
     if(num>0){
         System.out.println("插入成功");
     }

CRUD操作 - delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

     Statement statement = connection.createStatement();
     String sql = "delete from user where id=1";
     int num=statement.executeUpdate(sql);
     if(num>0){
         System.out.println("删除成功");
     }

CRUD操作 - update

使用executeUpdate(String sql)方法完成数据跟新操作，示例操作：

     Statement statement = connection.createStatement();
     String sql = "update users set name = 'aa'  where id=1";
     int num=statement.executeUpdate(sql);
     if(num>0){
         System.out.println("更新成功");
     }

代码实现
1.提供工具类

package com.JDBC.lesson02.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 (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, userName, password);
    }

    //释放资源
    public static void release(Connection connection, Statement statement,ResultSet resultSet) throws SQLException {
        if(resultSet!=null){
            resultSet.close();
        }
        if(statement!=null){
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }
}

2.编写调用代码

public class TestUtils {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(4,'tangtang','123456','12345@qq.com','2021-01-01')";
            int i =statement.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入

• 在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息
  示例

public class TestUtils02 {
    public static void main(String[] args) throws SQLException {
        login(" 'or' 1=1","123456");
    }
    public static void login(String username,String password) throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where `name`='"+username+"' and `password` ='"+password+"'";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("password"));
                System.out.println("===========================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

PreparedStatement对象

PreparedStatement