MySQL

前端（页面：站视，数据）

后台（连接点：连接数据库JDBC，连接前端（控制，控制试图跳转，给前端传递数据））

数据库

DDL : 定义

DML :操作

DQL :查询

DCL : 控制

1. 操作数据库

1.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] westos

1.2 删除数据库

DROP DATABASE [IF EXISTS] westos

1.3 使用数据库

-- 表或字段名是一个特殊字符，需要带``
use 'student'

-------------------------
SHOW CREATE DATABASE [表名]   -- 查看创建数据库的语句
SHOW CREATE TABLE [表名]        -- 查看数据表的 定义语句
DESC [表名]                    -- 显示表结构

-------------------------

1.4 数据库列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节（常用）
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 双精度浮点数 8个字节
decimal 字符串形式的浮点数金融计算

字符串

char 字符串（0--255）
varchar 可变字符串（0-65535）常用
tinytext 微型文本 2^8 -1
text 文本串 2^16 -1 保存大文本

时间日期

java.util.Date

date YYYY-MM-DD 日期
time HH: mm : ss 时间格式
datatime YYYY-MM-DD mm : ss 最常用的时间格式
timestamp 时间戳, 1970.1.1到现在的毫秒数（常用）

null

没有值，位置
~~不能使用NULL进行计算，结果为NULL~~

1.5 数据库的字段类型

Unsigned :

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

zerofill:

0填充
不足的位数，使用0来填充 int(3) , 5 --> 005

自增 AUTO_INCREMENT

上一条记录基础上+1
通常用来设置唯一主键 index 整数类型
可以自定义设计逐渐起始值根步长

非空 NOT NULL

必须有值，无则报错

默认 DEFAULT

设置默认值

主键 PRIMARY KEY

/*
‘version’     乐观锁

is_delete    伪删除

gmt_create    创建时间

gmt_update    修改时间
*/

1.6 数据表的类型

/* 
数据库引擎：
INNODB    默认使用~
MYISAM    早些年使用
*/

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

常规使用操作

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

物理空间位置

数据库文件都存在data目录下
本质上还是文件的存储

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

InnoDB在数据库表中止呕一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM对应文件
- *.frm 表结构定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

设置数据库表的字符集编码

-- 1. 数据表中设置字符集编码
CHARSET = UTF8

-- 2. my.ini中配置默认的编码
character-set-server=utf8

mysql默认字符集编码不支持中文

1.7 数据表的操作

-- 1. 表的创建：
-- `字段名` : 表明并非关键字
CREATE Table [IF NOT EXISTS] '表名'(
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
------------------------------------

-- 2. 修改表名
ALTER TABLE [OldName] RENAME AS [NewName]

-- 3. 增加表的字段
ALTER TABLE [TableName] ADD [字段名] [列属性]

-- 4. 修改表的字段
-- change --> 字段重命名，不能修改字段类型和约束
-- modify --> 只能修改字段类型和约束，不用来字段重命名
ALTER TABLE [TableName] MODIFY [字段名] [新列属性]
ALTER TABLE [TableName] CHANGE [OldName] [NewName] [列属性]

-- 5. 删除表的字段
ALTER TABLE [TableName] DROP [字段名]

-- 6. 删除表
DROP TABLE IF EXISTS [TableName]

== 所有创建和删除尽量加上判断，以免报错 ==

2. MySQL数据表操作

2.1 外键

方法一：在创建表的时候增加外键

-- `字段名` : 表明并非关键字
CREATE Table [IF NOT EXISTS] '表名'(
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释],
    PRIMARY KEY ('字段名')        -- 主键
    Key 'FK_...'('字段名')
    CONSTRAINT 'FK_...' FOREIGN KEY ('字段名') REFERENCES '外键表名' ('字段名') -- 外键
)[表类型][字符集设置][注释]
------------------------------------

方法二：更改表字段增加外键

ALTER TABLE [TableName] 
ADD CONSTRAINT 'FK_...' FOREIGN KEY ('字段名') REFERENCES '外键表名'('字段名')

删除有外键关系的表的时候，必须要先删除从表，在删除主表
物理外键，数据库级别的外键不建议使用（避免数据库过多造成困扰）！！！

== 最佳实践 ==

数据库就是单纯的表，只用来存储数据
用程序实现外键的使用！！！

2.2 DML语言(数据操作)

2.2.1 插入

insert 插入

-- 插入单条记录：
insert into [表名] ([字段名1,字段名2,...,]) values ('值1','值2',...)

-- 插入多条记录：
insert into [表名] (字段) values (值1),(值2),(...)

--- 字段名根值--> 一一对应  ---

2.2.2 更新

update 修改

-- 带条件修改
update '表名' set 'column_name = value,[column_name = value,...]' where [条件]

-- 不指定条件，会修改所有数据
update '表名' set 'column_name' = value

条件：

操作符	含义
BETWEEN ... AND ...	[A,B]
AND	&&
OR	\|\|

2.2.3 删除

delete 删除

delete FROM 'TableName' where [条件]

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和约束不变

delete | TRUNCATE 的区别

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

** delete删除的问题，重启数据库 -->

InnoDB 自增列会从1开始（存在内存当中，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

2.3 DQL查询数据

select 查询

SELECT [ALL | DISTINCT]
{* | table.* | [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 [TableName]

-- 查询指定字段
select '字段1','字段2' from [TableName]

-- 别名，给结果起一个名字
select '字段1' as 别名1,'字段2' as 别名2 from [TableName]

-- Concat函数
select CONCAT('字符串',字段) as 别名 from [TableName]

去重 distinct

作用：去除select查出来的结果中重复的数据（重复数据只显示一条）

select distinct '字段' from [TableName]

数据库的列（表达式）

select VERSON()     -- 查询系统版本 (函数)
select 100*3+1        -- 用于计算 (表达式)
select @@auto_increment_increment    --查询自增的步长 (变量)

------------------------------------

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量...

select 表达式 from 表

2.3.1 where条件子句

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

逻辑运算符

与： AND &&

或 : OR ||

非： NOT !

模糊查询：比较运算符

运算符	语法	描述
IS NULL	A is null	操作符为null，结果为真
IS NOT NULL	A is not null	操作符不为null，结果为真
BETWEEN	A between B and C	a在b、c之间，结果为真
LIKE	A like B	SQL匹配，a匹配b，结果为真
IN	a in (a1,a2,a3,...)	a在a1,a2,a3... 某个值中，结果为真

-- 查询关键字A前后只有一个字 : _
select ... from [TableName] where [] LIKE '_A_'

-- 查询关键字A前后有多个字 : %
select ... from [TableName] where [] LIKE '%A%'

-- 查询多个记录
select ... from [TableName] where [] in (a,b,c)

2.3.2 Join联表查询

Join 对比

INNER JOIN
LEFT JOIN
RIGHT JOIN

select [col1,col2,...] from T1 as s[INNER JOIN | LEFT JOIN | RIGHT JOIN] T2 as r ON [conditions(r.a = s.b)]

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

2.3.3 联表查询

自连接

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

select A.'cataName',B.'cataName'
from [Table] as A,[Table] as B
where A.Aid = B.Bid

2.3.4 分页和排序

LIMIT 分页

作用：缓解数据库压力（图片 : 瀑布流）
语法： limit 起始位置 , 页面的大小

ORDER BY 排序

ASC : 升序
DESC : 降序

-- 分页 

-- limit  起始下标页 , 页面的大小 如: 0-5
-- n : 当前页
-- pageSize : 页面大小
-- 起始值 : (n-1)*pageSize
LIMIT [currentPage] [PageSize]

2.3.5 子查询

本质 : where语句中嵌套一个子查询

2.3.6 分组和过滤

GROUP BY
HAVING

-- 顺序
select 去重 需要查询的字段 from 表 (表、字段可取别名)
xxx join 要链接的表 on 等职判断
where (具体的值，子查询语句)
GROUP BY  (通过哪个字段分组)
HAVING (过滤分组后的信息，条件)
ORDER BY (通过哪个字段排序) [ASC | DESC]
LIMIT startIndex pagesize

3. MYSQL函数

3.1 常用函数

常用函数

-- 数字运算
SELECT ABS() : 绝对值
SELECT CELLING()  : 向上取整
SELECT FLOOR()      : 向下取整
SELECT RAND()      : 返回0-1随机数
SELECT SING()      : 判断一个数的符号  负数: -1 0 : 0 正数 : 1

-- 字符串函数
SELECT CHAR_LENGTH('STRING') : 返回字符串的长度
SELECT CONCAT('A','B')    ：字符串拼接
SELECT INSERE('oldString',l,r,'insertString')    : 从某个位置开始替换某个长度
SELECT LOWER()     : 转小写字母
SELECT UPPER()     : 转大写字母
SELECT INSTR()     : 返回第一次出现子串的索引
SELECT REPLACE('String','A','B') : 替换出现的指定字符串 将A替换为B
SELECT SUBSTR('String',l,r)    : 返回指定字符串l-r的子串

-- 时间和日期函数 ！！！
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()

3.2 聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
....	....

COUNT

COUNT(field) : 会忽略所有的null
COUNT(*) ：不会忽略null值本质计算行
COUNT(1) ：不会忽略null值本质计算行

3.3 数据库级别的MD5加密

增强算法复杂性和不可逆性
不可逆

-- 函数 ：MD5()
insert into [Table] values(MD5(pwd))

4. 事务

6.1 什么是事务

要么都成功，要么都失败

一一一一一一一一一

将一组SQL放在一个批次中执行

一一一一一一一一一

事务原则：ACID

A : 原子性（要么都完成，要么都不完成）

C : 一致性 (数据的变化是一致的 )

I : 隔离性 (多用户同时操作，各个事务不相互影响)

D : 持久性
- 事务没有提交，恢复到原装
- 事务已经提交，持久化到数据库

原子性（Atomicity）

要么都完成，要么都不完成

一致性（Consistency）

事务前后的数据完整性要保证一致

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据干扰，事务之间要相互隔离

隔离导致的问题

事务的隔离级别

脏读

一个事务读取了另一个事务未提交的数据
不可重读

在一个事务内读取表中某一行数据，多次读取结果不同
虚度（幻读）

在一个事务内读取了别的事务插入的数据，导致前后数据不一致

手动处理事务

-- mySql 默认开启事务自动提交
SET autocommit = 0    /* 关闭 */
SET autocommit = 1  /* 开启 */

-- 手动处理事务

-- 事务开启
START TRANSACTION    -- 标记一个事务的开始

-- 提交 ： 持久化（成功）
COMMIT
-- 回滚 ： 回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1  /* 开启自动提交 */

SAVAPOINT    保存点名    -- 设置事务的保存点
ROLLBACK TO SAVEPOINT 保存点名    -- 回滚事务的保存点
RELEASE SAVEPOINT 保存点名        -- 撤销保存点

------------------------
-- 字符集、校对
CHARACTER SET UTF8 COLLATE utf8_general_ci

------------------------

5. 索引

索引是帮助MYSQL高效获取数据的数据结果

5.1 索引的分类

在一个表中，主键索只有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）
- 默认的，index、key关键字设置
全文索引（FullText）
- 特定数据库引擎支持
- 快速定位数据

-- 增加全文索引 (索引名)列名
ALTER TABLE school.student ADD FULLTEXT INDEX 'studentName' ('studentName')
CREATE INDEX <indexName> on <table('field')>
-- EXPLAIN 分析sql执行的状况

-- 非全文索引
EXPLAIN SELECT * FROM student;

-- 全文索引
SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURN INF
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i < NUM DO
        INSERT INTO app_user('name','email','')
        SET i = i+1;
    END WHILE
    RETURN i;
END;

MySQL

MySQL

1. 操作数据库

1.1 创建数据库

1.2 删除数据库

1.3 使用数据库

1.4 数据库列类型

1.5 数据库的字段类型

1.6 数据表的类型

1.7 数据表的操作

2. MySQL数据表操作

2.1 外键

2.2 DML语言(数据操作)

2.2.1 插入

2.2.2 更新

2.2.3 删除

2.3 DQL查询数据

2.3.1 where条件子句

2.3.2 Join联表查询

2.3.3 联表查询

2.3.4 分页和排序

2.3.5 子查询

2.3.6 分组和过滤

3. MYSQL函数

3.1 常用函数

3.2 聚合函数

3.3 数据库级别的MD5加密

4. 事务

6.1 什么是事务

原子性（Atomicity）

一致性（Consistency）

持久性（Durability）

隔离性（Isolation）

手动处理事务

5. 索引

5.1 索引的分类

5.2 索引原则

6. 权限管理和备份

6.1 用户管理