一、 Mysql

1. Install

(1) 安装

(2) 卸载

1. 去mysql的安装目录找到my.ini文件复制 datadir = "文件路径"
2. 卸载mysql
3. 删除路径下的MySQL文件夹

(3) 配置

• mysql服务启动

  1. 手动

  2. cmd --> services.msc 打开服务的窗口

  3. 使用管理员打开cmd

     • net start mysql : 启动mysql服务
     • net stop mysql : 关闭mysql服务

• mysql登录

  1. mysql -uroot -proot

• mysql退出

  1. exit
  2. quit

2. SQL

(1) 概念

Structured Query Language: 结构化查询语言

(2) SQL通用语法

1. SQL语句不区分大小写,关键字建议使用大写

2. 注释方式

   • 单行注释: -- 注释内容 || # 注释内容(mysql特有)
   • 多行注释: /* 注释 */

(3) SQL分类

• DDL(Data Definition Language，数据定义语言): 定义数据库对象: 数据库,表,列 create,drop,alter
• DML(Data Manipulation Language，数据操作语言): 增删改表中数据 insert,delete,update
• DQL(Data Query Language，数据查询语言): 查询表中数据 select
• DCL(Data Control Language，数据控制语言): 定义数据库访问权限 grant,revoke
• TCL:(Transaction Control Language，事务控制语言)

3. DDL

(1) 操作数据库

1. C(Create) - 创建

• 创建数据库 - create database if not exists X character set gbk;

2. R(Retrieve) - 查询

• 查询所有数据库名称 - show databases;
• 查询数据库创建信息(字符集) - show create database X;

3. U(Update) - 更新

• 修改数据库字符集 - alter database 数据库名称 character set 字符集名称;

4. D(Delete) - 删除

• 删除数据库 - drop database X;

(2) 操作表

1. C(Create) - 创建

create table 表名(
    列名 数据类型
    ...
);

• 复制表 - create table 新表 like 被复制表;

2. R(Retrieve) - 查询

• 查询所有表名称 - show tables;
• 查询表结构 - desc 表名;

3. U(Update) - 更新

• 修改表名 - alter table 表名 rename to 新表名;
• 修改表字符集 - alter table character set 字符集;
• 添加一列 - alter table 表名 add 列名 数据类型;
• 修改列名称/类型 - alter table 表名 change 旧列名 新列名 新数据类型;
• 删除一列 - alter table 表名 drop 列名;

4. D(Delete) - 删除

• drop table if exists 表名;

4. DML

(1) 添加数据

• insert into 表名(列名1,列名2...列名n) values (值1,值2,...值n);

除了数字类型,其他类型需要添加引号

(2) 删除数据

• delete from 表名 [where 条件]
• TRUNCATE TABLE 表名 删除表,再创建一张一模一样的空表

(3) 修改数据

• update 表名 set 列名1 = 值1, 列名2 = 值2,...[where 条件]

5. DQL

-- 完整语法
select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后条件
order by
    排序
limit
    分页限定

(1) 基础查询

• 去重复结果集: select distinct 列名 from 表名;
• 起别名: select math + englist as total from 表名;

IFNULL(exper1 , exper2) - 不为null,返回exper1,否则返回exper2

(2) 运算符

• <> 不等号相当于 !=
• between 左范围 and 右范围;(左右都为闭区间)
• in(数据1,数据2)
• is null / is not null

(3) 模糊查询

• 占位符: _: 单个任意字符; %: 多个任意字符
• SELECT * FROM student WHERE NAME LIKE '小_';

(4) 排序查询

• order by
• 排序方式
  • ASC -> 升序
  • DESC -> 降序
• 多个排序条件
  • select * from student order by math DESC , english DESC;

(5) 聚合函数

• 将一列数据作为一个整体,进行纵向的计算

1. count - 计数
2. max - 最大值
3. min - 最小值
4. sum - 和
5. avg - 平均值

注意: 聚合函数的计算,排除null值

1. 选择不包含非空的列进行计算: 主键
2. IFNULL函数

(6) 分组查询

• 语法: group by 分组字段;

  • 分组之后查询的字段: 分组字段/聚合函数

• where与having的区别?

  • where在分组之前进行限定,如果不满足条件,则不参加分组.
  • having在分组之后进行限定,如果不满足条件,则不会被查询出来.
  • where后不可跟聚合函数,having可以进行聚合函数判断

(7) 分页查询

• 语法: limit开始的索引,每页查询的条数;
• 公式: 开始的索引 = (当前页码 - 1) * 条数
• limit只在mysql中适用

6. 约束

对表中的数据进行限定,保证数据的正确性,有效性,完整性

(1) 主键约束(primary key)

• primary key --> 自动增长(auto_increment) modify
• 删除: alter table 表名 drop primary key;
• 添加: alter table 表名 modify 列名 数据类型 primary key

(2) 非空约束(not null)

• 值不能为空
  增删: ALTER TABLE 表名 MODIFY 列名 数据类型;

(3) 唯一约束(unique)

• 值不能重复, null 可以重复

• 删除: alter table 表名 drop index 列名
• 添加: alter table 表名 modify 列名 数据类型 unique

(4) 外键约束(foreign key)

• 创建表时: constraint 外键名称 foreign key (外键字段名称) reference 主表名称(主表列名称)
• 删除: alter table 主表名称 drop foreign key 外键名称
• 添加: alter table 主表名称 add constraint 外键名称 foreign key (外键字段名称) reference 外键表名称(外键表列名称)

(5) 级联

谨慎使用

• 删除: 外键约束添加 --> on delete cascad
• 添加: 外键约束添加 --> on update cascad

7. 多表查询

• 笛卡尔积 --> 消除无用数据

(1) 内连接

1. 隐式内连接
2. 显示内连接
   • select 字段列表 from table1 [inner] join table2 on 条件

(2) 外连接

1. 左外连接/右外连接

   • select 字段列表 from table1 left/right [outer] join table2 on 条件

(3) 子查询

• 查询中嵌套查询,嵌套查询为子查询

8. 多表间的关系

(1) 分类

1. 一对一(唯一外键)

2. 一对多(在多的表中,添加外键)

3. 多对多(采用中间表记录)

(2) 数据库范式

1. 分类

• 第一范式(1NF): 每一个列都是不可拆分的原子项
• 第二范式(2NF): 建立在第一范式基础上，消除部分依赖
• 第三范式(3NF): 建立在第二范式基础上，消除传递依赖。

• BCNF:

  1.所有非主属性对每一个码都是完全函数依赖。

  2.所有的主属性对每一个不包含它的码,也是完全函数依赖。

  3.没有任何属性完全函数依赖于非码的任何一组属性。

2. 概念

• 完全函数依赖: X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X
• 部分函数依赖：存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X
• 传递函数依赖: 设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X
• 码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码
• 全码：如果一个码包含了所有的属性，这个码就是全码。
• 主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
• 非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。
• 外码：一个属性（或属性组），它不是码，但是它别的表的码，它就是外码。

(3) 数据库的备份与还原

1. 命令行:

• 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存路径.sql

• 还原: sourse 文件路径

9. 事务

Transaction

(1) 基本介绍

如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败

1. 开启事务: start transaction
2. 回滚: rollback
3. 提交: commit
   • 查看事务的默认提交方式: select @@autocomimt;(1为自动,0为手动)
   • 修改事务的默认提交方式: set @@autocomimt = x;

(2) 四大特征

1. 原子性(Atomicity): 事务是不可分割的最小操作单位,要么同时成功,要么同时失败
2. 持久性(Durability): 当事务提交或回滚后,数据库会持久化的保存数据
3. 隔离性(Isolation): 多个事务之间,互相独立
4. 一致性(Consistency): 事务操作前后,数据总量不变

(3) 隔离级别

多个事务之间是隔离的,互相独立的.如果多个事务操作同一批数据,会引发一些问题,设置不同的隔离级别可以解决这些问题

• 存在问题
  1. 脏读: 一个事务,读取到另一个事务中没有提交的数据
  2. 不可重复读(虚读): 在同一个事务中,两次读取到的数据不同
  3. 幻读: 一个事务(DML)数据库中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
• 隔离级别

  1. read unccommitted: 读未提交
     问题: 脏读/虚读/幻读

  2. read committed: 读已提交(Oracle默认)
     问题: 虚读/幻读

  3. repeatable read: 可重复读(MySQL默认)
     问题: 幻读

  4. serializable: 串行化

• 注意: 隔离级别从小到大安全性越来越高,效率越来越低

(4) 具体操作

• 查询隔离级别

SELECT @@tx_isolation;

• 修改隔离级别

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

10. 数据类型

(1) 数据对应

类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)	描述
VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
BLOB	L+N	BLOB	java.lang.byte[]	-4
TEXT	65535	VARCHAR	java.lang.String	-1
INTEGER	4	INTEGER UNSIGNED	java.lang.Long	4
TINYINT	3	TINYINT UNSIGNED	java.lang.Integer	-6
SMALLINT	5	SMALLINT UNSIGNED	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT UNSIGNED	java.lang.Integer	4
BIT	1	BIT	java.lang.Boolean	-7
BIGINT	20	BIGINT UNSIGNED	java.math.BigInteger	-5
FLOAT	4+8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
BOOLEAN	1	同TINYINT
ID	11	PK (INTEGER UNSIGNED)	java.lang.Long	4
DATE	10	DATE	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93
YEAR	4	YEAR	java.sql.Date	91

二、Mysql高级

(1) 索引

1. 概述

帮助MySQL高效获取数据的数据结构(有序)

2. 优势劣势

• 优势

1. 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
2. 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

• 劣势

1. 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
2. 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行NERT、 URDATE、 DELETE.因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

3. 结构

1. BTree

   BTree又叫多路平衡搜索树,一颗m叉的 BTree特性如下

   • 树中每个节点最多包含m个孩子
   • 除根节点与叶子节点外,每个节点至少有[ceil(m / 2)]个孩子
   • 若根节点不是叶子节点,则至少有两个孩子
   • 所有的叶子节点都在同一层
   • 每个非叶子节点由n个key与n + 1个指针组成,其中[ceil(m / 2) - 1] < n < m - 1

4. 分类

1. 单值索引：
2. 唯一索引：
3. 复合索引：

5. 语法

1. 创建索引：CREATE INDEX (index_name) ON (table_name(column_name))

2. 删除索引：DROP INDEX (index_name) ON (table_name)

3. 查看索引：SHOW INDEX FROM (table_name) [\G]

4. ALTER：

   • alter table tb_name add primary key (column_list):
     该语句添加一个主键,这意味着索引值必须是一的,且不能为NULL
   • alter table tb_name add unique index_name(column_list)
     这条语句创建索引的值必须是唯一的(除了NUL外,NULL可能会出现多次)
   • alter table tb_name add index index_name(column_list):
     添加通索引,索引值可以出现多次
   • alter table tb_name add fulltext index_name (column_list):
     该语句指定了索引为 FULL TEXT,用于全文索引

6. 设计原则

(2) 视图

1. 创建/修改

CREATE [OR REPLACE] [ALGORITHM [UNDEFINED I MERGE I TEMPTABLE]
VIEW view_name [(column_list)]
AS select statement
[WITH [CASCADED I LOCAL] CHECK OPTION]

2. 查看

show tables;

3. 删除

drop view if exists (view_name);

(3) 存储过程/函数

• 存储过程

1. 创建

delimiter $

create procedure procedure_name(参数列表)

begin

        存储过程体（一组合法的SQL语句）

end $

delimiter ;

2. 查看

show procedure status[\G];

3. 删除

drop procedure [if exists] procedure_name;

• 函数

(4) 触发器

1. 创建

create trigger trigger-name
before/after insert/update/delete
on table_name
[for each row]
trigger_stmt;

(5) 存储引擎

1. Innodb

2. MyISAM

3. MEMORY

4. MERGE

(6) 优化SQL步骤

一般步骤概要：

1. 通过 show status 命令了解各种sql的执行频率

show [session | global] status like 'Com_______;

2. 定位执行效率较低的sql语句
3. 通过explain分析低效sql的执行计划

• id - 执行优先级 数字越大，优先级越高
• select_type

2. 通过 show profile 分析sql
3. 通过trace分析 优化器 如何选择执行计划
4. 确定问题并采取相应的优化措施