一、什么是数据库

内存条 临时记忆 速度比较快 不能永久保存数据
硬盘 文件夹 持久化记忆 可以实现数据持久化 增删改查（大批量的数据信息）
数据库 1.数据持久化2.查询速度快更方便进行大批量数据的管理 需要单独购买
本质上是一个文件系统 还是以文件的方式存在服务器的电脑上
数据库分类 主要类别：关系型数据库（）

Oracle 收费型数据库
MySql暂时性免费 6.0开始收费
SQL Srever 微软提供的数据库 收费的
DB2 IBM公司提供的数据库 收费的 银行系统
SQLite 嵌入式的小型数据库 移动端
Database（DB）
作用：存储数据 大批量数据的操作 使用同一套方式操作（SQL）
安装数据库
1.解压
2.找到my-defult.ini
basedir = E:\MYSQL\mysql-5.6.44-winx64
datadir = E:\MYSQL\mysql-5.6.44-winx64\date
3.配置环境变量
\bin
4.使用管理员身份打开控制台
mysqld -install MySQL —defalute-file=”E:\MYSQL\mysql-5.6.44-winx64\my-default.ini”
安装成功会显示successfully；
net start mysql;
具体化几个抽象的概念
数据库—>文件夹
表—>文件
数据—>文件中记录的数据
MySQL数据库 - 图1

2、合理使用范式和反范式

在企业中很好能做到严格意义上的范式或者反范式，一般需要混合使用。

缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算显示它；也可以在user表中建一个num_messages列，每当用户发现新消息时更新这个值。

二、SQL

什么是SQL Structured Query Language 结构化查询语句
定义了所有关系型数据库的规则

SQL通用语法：
可以单行或多行书写 但是要以分号结尾
可以使用空格和缩进增强可读性
MySQL中的语句不区分大小写 建议关键字用大写
单行注释 —【空格】 需要注释的内容
MySql的方言：
单行注释 #【空格】需要注释的内容

三、分类：

DDL（Data Definition Language）数据定义语言
用来定义数据库对象：数据库 表 列 例如：Create
DML（Data Manipulation Language）数据的操作语言
用来对数据库中表中的数据进行增、删、改 insert
DQL（Date Query Language）数据查询语言
用来查询数据表中的数据 select
DCL（Date Control Language）数据控制语言
用来定义数据库的访问权限及安全级别 GRANT

  1. 登录数据库 mysql -uroot  -p
  2. 退出数据库 exit、quit

3.1.DDL：

1.创建数据库
create database 数据库名称；
create database if not exists 数据库名称；//判断是否存在这个数据库，没有则新增
create database 数据库名称 character set gbk；
2.查询
展示所有数据库
show database；
展示某个数据库的字符编码
show create database 数据库名称；
3.修改字符编码
alter database 数据库名称 character set utf8

  1. 4.删除
  2.    drop database 数据库名称；
  3.    drop database if exists 数据库名称；

操作表
创建表（创建表 创建列）
1.create table 表名（列名1 数据类型1，列名2 数据类型2）；
int double date(yyyy-MM-dd) dateTime(yyyy-MM-dd HH:mm:ss)
varchar（长度）字符串
复制一张表
create table 新表名 like 旧表名；
2.查询表
show tables;
查询表的结构
desc 表名；
3.修改
表名
alter table 表名 rename to 新表名；
添加一列
alter table 表名 add 列名 类型；
修改列名
alter table 表名 change 列名 新列名 新类型；
修改类型
alter table 表名 modify 列名 新类型；
删除列
alter table 表名 drop 列名；
4.删除表
drop table 表名；
drop table if exists 表名；

3.2DML：数据的增删改操作

1.添加数据
insert into 表名（列名1，列名2） values（值1，值2，值3）；

  1. 注意：
  2.        列名和值要一一对应
  3.        除了形式为数字的值 其他的值都应该加上''或者""
  4.        如果整行添加信息 可采用以下语法
  5.        某列为自增列 自增列无需赋值，但是要给null占位
  6. insert into 表名 values（值1，值2，值3）；
  7.        批量增加数据
  8. insert into  表名 values（值1，值2，值3），（值1，值2，值3）；
  9.    3.删除操作
  10. delete from 表名 [where 列名 =值]；
  11. 注意：
  12.        删除一般要增加删除条件 否则会删除整个表的数据
  13.        如果要删除整表信息 应该使用 TRUNCATE TABLE 表名；效率高 先删除整个表 再创建一张没有数据的表
  14. 多行删除
  15.       delete from 表名 [where 列名 =值1 or 列名 =值2];
  16.     4.修改数据
  17. update 表名 set 列名 = 值1 [where id=值]；
  18. 注意：
  19.        不加where条件是改变所有行当前列的值

3.3DQL：数据的查询操作

select from 表名； 不推荐
select from 表名[where 列名=值]；

select 列名1，列名2，列名3 from 表名
where 条件列表
group by 分组条件
having 分组之后的条件查询
order by 结果排序
limit（mysql方言）分页限定

select 列名列表 from 表名；

  1. 去除重复值 distinct
  2. select distinct 列名  from 表名；
  3. 注意：不应该和其他列同时查询

3.4基本的运算

注意：
只能用于数字型列
如果运算中出现了NULL那么结果就是NULL

ifnull（列名，0）；可以把某一列中出现的null替换为0
SELECT gname,math,IFNULL(sorce,0) sorce FROM mygoods;
可以使用关键字重新编写一个列名
select name，math+ifnull（english，0） as 总成绩 from 表名；

where条件查询 <>不等于
between …and… 区间查找
in（值1，值2） 等值查找
like 模糊查询 匹配占位符使用
name like ‘张‘
代表一个字符
%代表多个字符
查找空
不能使用=null 要用is null
and && or || not ！

3.5排序

order by 列名
SELECT gname,math-10 as 数学 FROM mygoods ORDER BY 数学 DESC;
多列排序
select *from mygoods order by math asc,english desc;

聚合函数
注意：使用了聚合函数后 行数会变为一行 不能和普通列一起展示
count(列名)记录总行数—>总人数
SELECT COUNT(*) AS 总人数 FROM mygoods;
max（列名）19:11 2020/7/29
min （列名）
sum（列名）
avg （列名）

分组查询
注意：分组是根据当前列的相同项进行合并 行数会变化 也不能和其他普通列一起展示
group by 列名

分组后筛选
使用having
SELECT sex AS 性别,AVG(english) AS 平均分 FROM mygoods GROUP BY sex HAVING 平均分>60;

where和having
1.where再分组之前进行筛选 如果不满足条件 则不参与分组，having是在分组之后的虚拟结果中再次筛选
2.where后不可以跟聚合函数 having可以进行聚合函数的判断 having操作的所有数据（列）都是经过前期代码
筛选后的结果
3.where操作真实表，having操作虚拟表

执行顺序 先执行where 对于真实表进行筛选 再执行group by对于筛选后的虚表进行分组之后执行聚合函数，
聚合函数是对分组之后的数据 每个组都要分别去执行聚合函数
最后使用having 对于所有以上操作最终结果进行最后筛选
索引行数=（当前页面页码-1）每页行数
分页查询
SELECT FROM mygoods LIMIT 0（起始行下标）,3（每页展示的行数）;

3.6约束

概念：对表中的数据进行限定 保证数据的正确性、有效性、完整性
分类：
主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

  1.   添加主键：
  2. alter table score modify id int primry key;
  3.   create table score(
  4. id int primary key,
  5. name varchar(255))

效果：不允许重复 不能为空
删除主键：
alter table score drop primry key;

修改主键
alter table score modify id int primary key；
自增列 只能用于int类型列 auto_increment;

非空约束：
新增列时操作
删除自增
ALTER TABLE mygoods MODIFY id int;
表已创建 添加非空约束
alter table 表名 modify 列名 类型 not null
添加自增
ALTER TABLE mygoods MODIFY id int auto_increment;
当前列必须是主键的时候 才可以设置为自增

主键必然非空 其他列可随意设置
唯一约束：
create table score(
id int primary key auto_increment,
name varchar(255) unique)；
唯一约束的列 可以为空
设置已有列的唯一约束：
ALTER TABLE mygoods MODIFY gname VARCHAR(25) UNIQUE;
删除唯一约束
ALTER TABLE 表名 DROP INDEX 列名;

外键约束
注意点：至少涉及两张表 是表与表之间的联系，主表的主键限制从表的某个列取值
创建从表时设置外键关系
create table 表名（
id int primary key auto_increment,
name varchar(255) not null,
sex int not null,
—设置主外键关联
constraint 外键名称 foreign key（sex）references 主表名称（主键列）
）；
作用：从表中的外键列 只能去主表中主键的值

创建表之后 添加外键
alter table 从表名 add constraint 外键名称 foreign key（sex）references 主表名称（主键列）
删除外键
alter table 从表名 drop foreign key 外键名称；

注意点
删除 先删除从表 再删除主表

级联操作：
alter table 从表名 add constraint 外键名称 foreign key（sex）references 主表名称（主键列）
on update cascade;
新增从表自动新增主表
删除主表自动删除从表
alter table 从表名 add constraint 外键名称 foreign key（sex）references 主表名称（主键列）
on delete cascade;
修改 先删除从表 修改主表 新增从表
alter table 从表名 add constraint 外键名称 foreign key（sex）references 主表名称（主键列）
on update cascade on delete cascade;

添加级联更新后可以直接通过修改主表 自动修改从表
update 主表名 set id=3 where id=1;
删除主表后自动删除从表
添加级联删除后 可以直接通过删除主表 自动删除从表
delete from 主表名 where id=3；

3.7多表联合查询

笛卡尔积
有多个集合 当共同被查询没有添加任何条件时 所得到虚拟表为每张表各行互相匹配的结果
虚拟表的行数=多个集合的行数相乘
联合查询 消除无用的数据
select 列的列表 from 表1，表2；
多表查询分类
内连接查询
隐式内连接
SELECT main.sex,sex.sex FROM main,sex WHERE sex.sex=main.id;
显式内连接
SELECT d1.sname,d2.sex FROM sex as d1 [INNER] JOIN main as d2 ON d1.sex=d2.id;
外连接查询
左外连接（行数以左边的表格为主）
SELECT FROM 从表 LEFT JOIN 主表 ON emp.dept_id=dept.id;
查询的是左表全部的信息及其交集的部分
右外连接（行数以右边的表格为主）
SELECT FROM 主表 RIGHT JOIN 从表 ON emp.dept_id=dept.id;
查询的是 右表全部的信息及其交集的部分
当条件给出正确的外键匹配关系时 也是可以展示正确的查询结果
子查询
概念：查询中再嵌套查询 我们称被嵌套的查询为子查询
子查询的结果作为父级查询的条件组成
— 查询财务部和市场部所有员工信息
SELECT emp.name,emp.salary,dept.name FROM emp,dept
WHERE emp.dept_id=dept.id AND emp.dept_id in(
(SELECT id FROM dept WHERE name=’市场部’),
(SELECT id FROM dept WHERE name=’财务部’));

子查询的结果（虚拟表）

四、数据库的设计

表就是现实事物的抽象化

4.1表与表之间的结构关系

保存的数据分类：
1.一对一表结构
列如：人和身份证
实现：通过任意一方添加外键约束 指向另一方的主键 自动唯一（比如唯一id约束唯一id）
2.一对多
列如：学生（多）和班级（一）
实现：通过多的一方添加外键 指向一的一方的主键
3.多对多表结构
需要借助第三张表来实现，这张表至少得包含两个列 保证两张表的主键信息 保证正确
的对应关系
通过三表联合查询 获取数据

查询没有报4号科目的学生人数：
SELECT COUNT() AS 没有报4号科目的同学人数 FROM student
WHERE NOT EXISTS(SELECT FROM temp WHERE les_id=4 AND student.id=temp.stu_id);

2.数据库的设计范式
概念：设计数据库时 需要遵循的一些规范。要遵循后边的范式要求，就必须先遵循前边的所有范式要求
这些不同的规范要求被称为不同的范式，各种范式呈递次规范，满足越高的范式数据库，沉余越小。
目前，数据库总共有六种范式：第一范式（1NF）第二范式（2NF）第三范式（3NF）
巴斯-科德范式（BCNF）第四范式（4NF）第五范式（5NF 完美范式）

4.2事务

概念：一个包含多个步骤的业务操作 使用事务来管理 如果其中一个操作出现了异常当前事务中
其他步骤的操作将被还原
列如：张三给李四转账
张三查询+转帐+查询 全部的过程都正常 才算是完整的结束事务
语法流程：
开启事务
start transaction；
各个表的操作步骤
if事务回滚
rollback；
else关闭事务
commit；
事务提交的两种方式
自动提交
Mysql是默认自动提交事务
一条DML语句会自动提交一次事务
手动提交
Oracle数据库
需要手动开启和关闭
修改事务的提交
select @@autocommit； 1：自动提交； 0：手动提交
set @@autocommit=0； 设置为手动提交方式
特性：
1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2.持久性：当事务提交（commit）和回滚（rollback），数据库才会持久化地保存数据。
3.隔离性：多个事务之间，相互独立。
4.一致性：事务操作前后，数据总量不变。

  1. 读取异常：select 当一方开启事务操作数据后 在使用提交事务或者回滚事务前，查询到的数据

都只是领时数据信息，此时读取的数据并不是最终结果
事务的隔离级别：
1.脏读：一个事物读取到了另一个事务还没有持久化保存的数据
2.不可重复读：同一个事务 两次读取到的数据信息不一致
3.幻读：一个事物在操作（DML）数据表中的所有记录，另一个事务添加了一条数据，
则第一个事务查询不到自己的修改。
提升事务隔离级别：
1.read uncommited：读未提交
产生：脏读 不可重复读 幻读
2.read commited：读已提交 （Oracle默认隔离级别）
产生：不可重复读 幻读
3.repeatbale read：可重复读（mysql默认隔离级别）
产生：幻读
4.serializable：串行化
可以解决所有问题
查询和修改当前隔离级别（设置后 连接断开重连）
select @@tx_isolation；
set global transaction isolation level 隔离级别；
案例一：
设置为serializable
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
不提交也不回滚 让另一个事物去访问 另一个事物会处于等待状态 超时会报错结束等待

  1. 注意：隔离级别重小到大 安全级别越来越高 效率越来越高
  2. 悲观锁：事务中的业务大概率会出现并发异常时 我们处于一种悲观状态 可以使用悲观锁
  3. 先关闭默认提交模式
  4. 是在修改前加锁
  5. START TRANSACTION; -- 加锁
  6. SELECT money from bank where id=2 for update
  7. 再关闭这个事务前 其他的事务还是查询到数据库中的真实数据 如果其他事物使用DML
  8. COMMIT;
  9.  -- 乐观锁
  10. 在修改之前先去获取待修改的值 在修改的同时，判断这个值跟刚才获取到的值是否相同，相同

则执行修改
DCL：对于数据库用户权限的相关设置
DBA：DataBaseAdministrator 数据库管理员

4.3管理用户 授权操作

1.管理用户：
添加用户：
CREATE USER ‘zhangsan’@’localhost’ IDENTIFIED BY ‘666’;
修改用户信息：
SET PASSWORD FOR ‘zhangsan’@’localhost’=PASSWORD(‘000’);
UPDATE USER SET PASSWORD=PASSWORD(000) WHERE USER=’zhangsan’;
删除用户：
DROP USER ‘zhangsan’@’localhost’;
查询用户：
SELECT *FROM USER;
注释：
通过注解来判定一个接口是否是函数式接口
1.函数式接口作为方法的参数使用
2.函数式接口作为方法的返回值使用

  1. //Lambda表达式写法
  2. show(()->{
  3. System.out.println("我就是匿名内部类");
  4. });
  5. show(()->System.out.println("我就是匿名内部类"));

3.函数式接口作为方法的返回值使用 返回Lambda表达式
简化方法
return (String o1,String o2)->{
return o;
}

4.4完整查询语句

select 字段 from 表名 where 条件 group by 字段 having 聚合函数筛选 order by 字段 ASC(DESC) limit 起始位置，条数；
1、执行from将数据库中的表加载到内存中
2、执行where从内存表中筛选出符合条件的语句
3、如果有group by，则分组，如果没有group by默认分为一个组
4、如果有having，则先使用聚合函数，查询出聚合函数的值，再记性筛选
5、如果有order by，则根据order by的规则进行排序。order by后面可以跟多个字段
6、如果有limit，则根据limit显示最终数据

4.5会话变量

定义：在一次会话中生效的变量
语法：set @变量名=值
查询：select @变量名
局部变量
在代码块中的变量为局部变量，代码块begin……end
定义局部变量并给默认值：
delete 变量名 数据类型 default 值；
修改局部变量的值：
set 变量=新的值

4.6结构语句

分支语句语法：
IF 判断语句 THEN
执行的语句
ELSEIF 判断语句 THEN
执行语句
ELSE
执行语句
ENDIF；

循环语句语法：
WHILE 判断语句 DO
循环的语句
END WHILE；
跳出循环：
iterate：结束本次循环，继续下次循环对比continue；
leave：结束所有循环，对比break；

4.7函数编程

语法
CREATE FUNCTION 函数名(参数列表) RETURNS 数据类型
BEGIN
代码块；
END
示例：
delimiter & 定义结束符号
CREATE FUNCTION add() RETURNS int
BEGIN
DECLARE res int DEFAULT 0;
END &
查询函数
—查询所有函数
show function status；
—查询函数：模糊查询
show function status like %add%；
使用函数
select 函数名()；
删除函数
DROP FUNCTION add；

4.8数据库存储过程

概念：
和函数的概念一致，但是没有返回值，应用：向数据中持续添加数据
存储过程语法：
CREATE PROCEDURE 名称(参数列表)
BEGIN
代码块
END &
in：存储过程的外面变量传递到外面
out：存储过程的里面传递到外面
inout：即可以从外面传里面，也可以里面传外面
示例：
创建会话：
set @num1=1；
set @num2=2；
CREATE PROCEDURE pro(in a int,out b int）
BEGIN
代码块
END &
调用存储过程：
call pro(@num1,@num2)
查询存储过程
show procedure status like ‘%名称%’
触发器
create trigger 名称(触发器) 触发时机 触发事件 on 触发表格 for each row
begin
代码块
end
示例代码：
delimiter &
create trigger tril after insert on t_order for each row
begin
update course set course=’3-105’ where id=’825’;
end &
触发事件：insert delete update
触发时机：after before
查询触发器
show triggers 显示所有触发器
show create trigger tri 显示指定触发器

五、索引各个单词的含义：

一、索引的基本知识

索引的好处：
1、快速查找匹配where子句的行；
2、从considerration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引；
3、如果表具有多行索引，则优化器可以使用索引的任何最左边前缀来查找行；
4、当表连接的时候，从其他表检索行数据；
5、查找特定索引列的min或max值；
6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组；
7、在某些情况下，可以优化查询以检索值而无需查询数据行；+

谓词下推：是SQL语句中的部分语句( predicates 谓词部分) 可以被 “pushed” 下推到数据源或者靠近数据源的部分.通过尽早过滤掉数据，这种优化可以大大减少查询/处理时间。

  1. 举例：
  2. inner join的结果集是左表和有表都要满足条件，所以inner join condtion中的条件都是可以下推的，比如下面的查询
  3. select e.* from emp e inner join dept d on e.deptno = d.deptno and emp.deptno=5;
  4. 优化后的查询
  5. select e. from (select * from emp where emp.deptno =5) e 
  6. inner join (select * from deptno where deptno =5 ) on 
  7. e.deptno = d.deptno;

二、索引方法

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

  1. FULLTEXT
    即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。

  1. HASH
    由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

  1. BTREE
    BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
  2. RTREE
    RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

ps. 此段详细内容见此片博文：Mysql几种索引类型的区别及适用情况

三、索引类型

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

ps.索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

Normal 普通索引
表示普通索引，大多数情况下都可以使用

Unique 唯一索引
表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

约束唯一标识数据库表中的每一条记录，即在单表中不能用每条记录是唯一的（例如身份证就是唯一的），Unique(要求列唯一)和Primary Key(primary key = unique + not null 列唯一)约束均为列或列集合中提供了唯一性的保证，Primary Key是拥有自动定义的Unique约束，但是每个表中可以有多个Unique约束，但是只能有一个Primary Key约束。
mysql中创建Unique约束

Full Text 全文索引
表示全文收索，在检索长文本的时候，效果最好，短文本建议使用Index,但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多

FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

SPATIAL 空间索引
空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

  1. truncate table name;
  2. 删除表中的所有行，而不记录单个行删除操作。