关系型数据库

建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据
关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成

关系数据结构

数据以什么方式来存储，如：二维表的形式

关系操作集合

如何来关联和管理对应的存储结构，如：SQL指令

关系完整性约束

数据内部有对应的关联关系，以及数据与数据之间也有对应的关联关系

表内约束
对应的具体列只能放对应的数据（不能乱放）
表间约束
自然界各实体都是有着对应的关联关系（外链）

SQL

结构化查询语言，是一种数据库查询和程序设计为目的的编程语言，用于存储数据以及查询、更新和管理关系数据库系统

SQL分类

数据查询语言（DQL）
专门用于查询数据，代表指令为：select，show
数据操作语言（DML）
专门用于写数据，代表指令：insert，update,delete
事务处理语言（TPL）
专门用于事务安全处理（不是所有的关系型数据库都提供事务安全处理），代表指令：transaction,commit,rollback
数据控制语言（DCL）
专门用于权限管理，代表指令：grant,revoke
数据定义语言（DDL）
专门用于结构管理，代表指令：create，drop

MySQL

MySQL是一种关系型数据库管理系统，使用SQL作为操作指令，C/S结构

mysql服务端架构有以下几层构成：

数据库管理系统（最外层）：DBMS，专门管理服务器端的所有内容
数据库（第二层）： DB，朱门用于存储数据的仓库（可以有很多个）
二维数据表（第三层）：Table，专门用于存储具体实体的数据

1. 字段（第四层）： Field，具体存储某种类型的数据（实际存储单元

库操作

创建数据库

基本语法： create database DB_NAME [DB_OPTIONS];

DB_OPTIONS:
- 字符集：charset字符集，代表当前数据库下所有表存储数据的默认字符集
- 校对集：collate校对集

显示数据库

全部
语法： show databases;
部分
语法： show databases like 'PATTERN';
_ 匹配单个字符
% 匹配多个字符
查看数据库创建语句
语法：show create database DB_NAME

字符集

查看系统保存的三种关系处理字符集
show variables like 'character_set%'
打通客户端、连接层、服务端三者之间字符集关系的方法

set names 字符集
等价于：
- set character_set_client = 字符集
- set character_set_connection = 字符集
- set character_results = 字符集

选择数据库

use DB_NAME;

修改数据库

修改数据库的字符集和校对集（库选项）
alter database DB_NAME charset=字符集

删除数据库

drop database DB_NAME;

表操作

创建数据表

create table TABLE_NAME(字段名 字段类型 [字段属性] [,字段名 字段类型 [字段属性]]..  ) 表选项;

表选项：引擎类型，charset字符集，collate校对集

字段类型：
1. 整型
tinyint:迷你整型，一个字节，范围：0-255
smallint：小整型，两个字节，范围：0-65535
mediuminto：中整型，三个字节
int: 整型，四个字节
bigint:大整型，八个字节

    mysql默认为整型增加负数，所以各种整型能表示的范围减半，
    无符号标识：unsigned
    用零补空位：zerofill,从左侧开始填充0，负数不能使用zerofill，zerofill默认会为字段类型添加unsigned

2. 浮点型（精度类型）：是一种有可能丢失精度的数据类型，尤其是在超出范围的时候
float(总位数，小数位数)：单精度，系统提供4个字节用来存储数据，只能保证7个左右的精度
double(总位数，小数位数):双精度，系统提供8个字节用来存储数据，只能保证15个左右的精度

    浮点数通常用来保存一些数量特别大，大到可以不用那么精确的数据

3. 定点数decimal
能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入，动态分配，超过9个数会扩展4个字节）

    decimal(M,D): M表示总长度，最大值不能超过65，D表示小数位，最大不能超过30

4. 时间日期类型
date：日期类型，系统使用3个字节来存储数据，对应格式：YYYY-mm-dd,能表示的范围从1000-01-01 到 9999-12-31，初始值为0000-00-00
time: 时间类型，系统使用3个字节来存储数据，对应格式：HH：ii：ss,MySQL中的time类型能表示的时间范围大的多，能表示从：-838:59:59-838:59:59
datetime：日期时间类型，就是将date和time合并起来，使用8个字节存储数据，格式：YYYY-mm-dd HH：ii:ss 范围是从1000-01-01 00:00:00到9999-12-31 23:59:59
timestamp: 时间戳类型，系统使用4个字节来存储，MySQL中的时间戳表示从格林威治时间开始，格式依然是YYYY-mm-dd HH：ii:ss，该字段不为空，不输入内容|输入null|CURRENT_TIMESTAMP 会更新为现在的时间
year: 年类型，占用一个字节来保存，能表示1900-2155年，但是year有两种数据插入方式：0-99和四位数的具体年
5. 字符串型
char(L): 定长字符，指定长度L后，系统会根据L来计算所需要的空间，一次性分配到位，L的长度理论值：0-255
varchar(L):边长字符，指定长度L后，系统会根据实际存储的数据来计算分配所需要的空间，L的长度理论值：0-65535

    varchar要记录数据的长度（系统根据数据的长度自动分配空间），所以每一个varchar数据产生后，系统会在数据后面增加1-2个字节的额外开销，用来保存数据所占用的空间长度，如果数据长度小于127个字符，额外开销一个字节，如果大于127个，就开销两个字节

    MySQL的行记录总长度不能超过65535个字节，varchar的理论长度是65535个字节，所以MySQL的存储长度远远达不到varchar的理论值，所以当超过65535个字节的时候就该改用Text或者longtext类型

6. Text文本类型
tinytext:系统使用一个字节来保存，
text:系统使用两个字节
mediumtext：系统使用三个字节
longtext: 使用四个字节

    1.在选择对应的存储文本的时候，不用刻意选择Text类型，系统会自动根据存储的数据长度来选择合适的文本类型
    2.在选择字符存储类型的时候，如果数据超过255个字符，一定选择Text存储

7.enum枚举类型
enum: 在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果,系统使用1到2个字节来存储枚举数据，最多支持65535个选项
语法： enum(value1,value2...)

  枚举字段其实存储的是数组的中各个选项的下标，从1开始，为数值

  枚举的意义：
  1.规范数据本身，限定只能插入规定的数据项
  2.节省存储空间

8.set集合
set:是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制：1表示该选项被选中，0表示该选项没有被选中,最多只能放64个选项
语法：set(value1,value2,value3....)
意义：同enum一样，规范数据，节省空间

字段属性:

字段属性又称列属性，在MySQL中共有6个：
- null:
- default:
- comment:列描述，专门给开发人员的注释，只能在查看创建数据库命令里才能看到
- primary key(主键)：
创建主键的三种方法：
1. 字段名字段类型 primary key notnull....
2. 字段名字段类型字段属性，primary key（字段名[,字段2]）
3. alter table 表名 add primary key(字段名[,字段2])
如果使用多个键作为主键，就是作为复合主键使用

  删除主键：
  alter table 表明 drop primary key

  主键约束：
  1. 当前字段对应的数据不能为空
  2. 当前字段对应的数据不能重复

auto_increment：
自增长,当给定某个字段该属性之后，该列的数据没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据，通常自动增长用于逻辑主键

查看自增长：自增长一旦触发使用之后，会自动的在表选项中增加一个表选项（一个表只能有一个自增长），查看表创建命令可以看到
修改自增长： alter table 表名 atuto_increment=值
删除自增长：就是在该字段属性之后不再保留auto_increment,当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长，语法：alter table 表名 modify 字段名字段类型字段属性（不含auto_increment和primary key),删除自增长后不会取消primary key，所以不用再加一个primary key属性
初始设置：在MySQL中有一组变量用来维护自增长的初始值和步长，show variables like 'auto_increment%'
unique[ key]: 唯一键，用来保证对应的字段中的数据唯一的，唯一键允许字段数据为null，null可以有多个
创建方法：类似primary key,也有三个，也支持复合唯一
删除唯一键：唯一键是索引（index)的一种，所以删除唯一键的方法就是 alter table 表名 index 唯一键名字
修改唯一键：先删除后增加

复制已有表结构

  create table TABLE_NAME like OLD_TABLE_NAME;

显示表结构

  describe TABLE_NAME
  desc TABLE_NAME
  show  columns from TABLE_NAME

显示创建表的指令

show create table TABLE_NAME;
ps: ;,\g和\G分别以两种方式显示数据

修改表结构

修改表选项

alter table TABLE_NAME 表选项[=]值

修改表名

alter table OLD_TABLE_NAME to NEW_TABLE_NAME

新增字段

alter tables add [column] (字段名 字段类型 [字段属性] first|after ，[字段名 字段类型 [字段属性]]..)  

特点：默认加到表的最后面

修改字段名

alter table TABLE_NAME change 字段名 新字段名 新字段类型 【新字段属性】【新位置】

修改字段类型

alter table TABLE_NAME modify 字段名 新字段类型 【新字段属性】 【新位置】

删除字段

alter table TABLE_NAME drop 字段名

删除表

drop table TABLE_NAME1【，TABLE_NAME2]

数据操作

插入数据

insert into TBALE_NAME [字段1，字段2] values （值1，值2）;

查询数据

select * or 字段列表 from TABLE_NAME  [where 条件]；

删除数据

delete from TABLE_NAME [where 条件]；

修改数据

update TABLE_NAME set 字段1=value【，字段2=value】 where 条件；

表关系

表与表之间有什么样的关系，每种关系应该如何设计表结构
1. 一对一

一张表中的一条记录与另外一张表中最多有一条明确的关系，通常，此设计方案保证两张表中使用同样的主键即可

一对多
一对多，通常也叫作多对一的关系，通常在一对多的关系设计的方案中，在‘多’关系的表中去维护一个字段，这个字段是’一‘关系表的主键
多对多

多对多解决方案，增加一个中间表，让中间表与对应的其他表形成两个多对一的关系

MySQL高级数据操作

插入数据

多数据插入

  只要写一次insert指令，可以插入多条数据
  语法：insert into 表名 [字段列表] values (值列表1),(值列表2),...

主键冲突

  在有的表中，使用的是业务主键（字段有业务含义），插入数据的时候不知道主键数据是否重复
  1. 主键冲突更新
    类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法
    insert into 表名 [字段列表] values (值列表) on duplicate key update 字段名=新值
  2. 主键冲突替换
    如果插入过程主键冲突，干掉原来的数据，重新插入进去，没有冲突更新效率高
    replace into 表名 [字段列表] values （值列表）；

蠕虫复制

  蠕虫复制: 一分为二，成倍增加，从已有的数据中获取数据，并且将获取到的数据插入到数据表中
  语法： insert into 表名 [字段列表] select [字段列表] from 表名 [where 条件]

更新数据

  1. 更新数据时候通常是跟随条件更新，不跟条件就是全表更新，比较危险
  2. 全表更新时，可以使用limit来限制更新数量

删除数据

  1. 删除数据时候通常是跟随条件更新，不跟条件就是全表删除
  2. 全表删除时，可以使用limit来限制更新数量
  3. delete删除数据无法重置auto_increment
  4. truncate 删除可以达到重置auto_increment的效果，因为这个命令相当于drop+create

查询数据

完整的查询语句：
select [select选项] 字段列表 from 数据源 where 过滤条件 group by 分组字段 having 过滤条件 sort by 排序字段 limit 限制

select选项：

all 默认选项，显示全部
distinct 去重

数据源

单表
多表： from 表1,表2,表3....
动态数据：从表中查询出来得到的二维结果表（子查询）
语法： from (select 字段列表 from 数据源) as 别名

从第一张表取出一条记录，拼凑第二张表的所有记录，保留所有结果，这在数学上有一个专业的说法：笛卡尔积

where子句

用来从数据表获取数据的时候，进行条件筛选

数据获取原理：针对表去对应的磁盘出获取所有的记录（一条条），where的作用就是在拿到一条结果就开始判断，判断是否复合条件，如果符合条件，就保存下来，不符合就舍弃

group by

group by是为了分组后进行数据统计的，如果只是想看数据显示，那么group by没什么意义，group by将数据按照指定的字段分组之后，只会保留每组的第一条记录
ps:group_concat(字段名)，分组后将改组的该字段数据连接起来

统计函数（聚合函数）
- count
- avg
- max
- min

多分组：
将数据按照某个字段分组后，对已经分组的数据进行再次分组
语法：group by 字段1，字段2

分组排序：
分组默认有排序的功能，按照分组字段进行排序，默认是升序
语法：group by 字段1[asc|desc],字段2[asc|desc]

回溯统计
当分组进行多分组之后，往上统计的过程中，需要进行层层上报，将这种层层上报统计的过程称之为回溯统计，每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为NULL
语法：group by 字段[asc|desc] with rollup;

having子句

having的本质和where一样，是用来进行数据条件筛选

where在group by之前，不能使用聚合函数

having在group by之后，可以使用聚合函数或者字段别名

ps:where是从表中取数据，是将数据从磁盘拿到内存，where之后的所有操作都是内存操作，别名也是在数据进入到内存之后才有的

order by字句

根据校对规则对数据进行排序

语法： order by 字段名 [asc|desc]

limit字句

主要用来限制获取到的数据记录的数量

记录数限制
纯粹的限制获取的数量，从第一条到指定的数量
语法： limit 数量
分页
利用limit来限制获取指定区间的数据
语法： limit offset(偏移量)，length(数量) //offset是从0开始的

查询中的运算符

算术运算符

    '+，-，×，/，%'

    基本算术运算符：通常不在条件中使用，而是用于结果运算（select 字段中）

比较运算符

    '>,>=,<,<=,=,<>，between 条件1 and 条件2'
    通常用在条件中进行限定结果
    ps：
      1. 在MySQL中，数据会先自动转换成同类型，再比较
      2. between中条件1必须小于条件2，反过来不行

逻辑运算符

    and,or,not

in运算符

    在什么里面，用来替代'等于=',结果不止一个，是一个结果集的时候用in
    语法： in (值1，值2，值3....)

is运算符

    is是专门用来判断字段是否为NULL的运算符

like运算符

    like运算符，是用来对字符串进行模糊匹配的
    基本语法：like '匹配模式'
        1. _ ：匹配对应的单个字符
        2. % ： 匹配多个字符

联合查询

将多个查询到的结果合并到一起（纵向合并）: 字段数不变，多个查询的记录数合并

应用场景

将同一张表中不同的结果（需要对应多条查询语句来实现），合并到一起进行展示
在数据量大的情况下，会对表进行分表操作，需要对每张表进行部分数据统计，合并到一起进行展示

语法

select语句 union [union选项] select语句

union选项（与select选项基本一样）
- distinct
- all

ps:union理论上只要保证字段数一样，不需要每次拿到数据对应的字段类型一致，而且只会保留第一个select语句对应的字段名

order by在union查询中的使用

只要使用order by,union前后的select语句必须全部用小括号括起来
order by需要配合limit使用才能起到排序的效果，否则排序无效

连接查询

将多张表连接到一起进行查询，会导致记录数行和字段数列都会发生改变

连接查询的意义

在关系型数据设计过程中，实体（表）与实体之间存在很多联系（一对一，一对多，多对多），通常在实际操作的过程中，需要利用这层关系来保证数据的完整性

连接查询分类

交叉连接
内连接
外连接：左连接和右连接
自然连接

交叉连接 cross join

将一张表的数据与另外一张表彼此交叉

原理

从第一张表依次取出每一条记录
取出每一条记录之后，与另外一张表的全部记录挨个匹配
没有任何匹配条件的话，所有的结果都会进行保留
记录数=第一张表记录数*第二章表的记录数
字段数=两张表的字段数之和

语法

表1 cross join 表2

应用

交叉连接产生的结果是笛卡尔积，没有实际意义

内连接

inner join，从一张表中取出所有的记录去另外一张表中匹配，利用匹配条件进行匹配，成功了则保留，失败了放弃

原理

从第一张表中取出一条记录，然后去另外一张表中进行匹配
利用匹配条件进行匹配,匹配到保留，匹配失败向下继续，直至全表匹配完成

语法

表1 [inner] join 表2 on 匹配条件；

如果内连接没有条件（允许），其实就是交叉连接
因为表的设计通常容易产生同名字段，尤其是id，所以建议使用表.字段名来避免重名出错
如果条件总使用到对应的表名，通常可以使用别名来代替书写较长的表名
内连接匹配的时候，必须保证匹配到才会保存
内连接因为不强制必须使用匹配条件（on),因此可以在数据匹配完成之后，使用where来限制，效果与on一样，建议使用on

应用场景

通常在对数据有精确要求的地方使用，必须保证两种表中都能进行的数据匹配

外连接

outer join,按照某一张表作为主表（表中所有记录在最后都保留），根据条件去连接另外一张表，从而得到目标数据

外连接分为：左连接（left join)和右连接（right join）

左连接：左表是主表
右连接：右表是主表

语法

表1 [left|right] join 表2 on 连接条件

左连接对应的主表字段在左边，右连接主表对应的字段在右边

主表未匹配到的数据记录会保留，在从表对应的字段，数据为NULL

特点

外连接中主表数据记录一定会保存，连接之后不会出现记录数少于主表的情况（内连接会）
左连接和右连接可以互相转换，只要把表的顺序替换即可，但是数据对应的字段顺序会发生变化

using关键字

是在连接查询中用来代替对应的on关键字的，进行条件匹配

特点

在连接查询时，使用on的地方用using代替
使用using的前提是对应的两张表连接的字段同名（类似自然连接）
如果使用using关键字，那么对应的同名字段，最终在结果中只会保留一个

语法

表1 [inner|left|right] join 表2 using（同名字段）

从功能上'using(同名字段)'相当与'on 表1.同名字段=表2.同名字段'

子查询

在一条select语句中，嵌入了另外一条select语句，那么被嵌入的select语句称之为子查询语句（sub query)

子查询与主查询的关系

子查询是嵌入到主查询中的
子查询是辅助主查询的，要么作为条件，要么作为数据源
子查询是可以独立存在的，是一条完整的select语句

分类

标量子查询（一行一列形式的一个数据）
语法：select * from 数据源 where 条件判断 =/<> (select 字段名 from 数据源 where 条件判断)
列子查询（子查询得到的结果是一列数据）
语法: select * from 数据源 where 条件判断 in （select 字段名 from 数据源 where 条件判断）
行子查询（子查询返回的结果是一行多列数据，一条记录）
行元素：字段元素是指一个字段对应的值，行元素对应的就是多个字段，多个字段合起来作为一个元素参与运算
语法： select * from 数据源 where （构造行元素）= （select 字段名 from 数据源 where 条件判断）
表子查询
表子查询用于from数据源：from 子查询
语法： select * from (select * from 数据源 where 条件) as 别名 [where 条件] [group by] [having] [order by] [limit]
exists子查询
exists子查询：返回的结果只有1和0,1代表真，0代表假
语法： where exists(子查询)

总结：标量子查询、列子查询、行子查询、exists子查询都属于where子查询,表子查询属于from子查询

子查询中特定关键字的使用
- in
主查询 where 条件 in（列子查询)
- any
主查询 where 条件 = any(列子查询)：条件在查询结果中有任意一个匹配即可，等价与in
主查询 where 条件 <> any(列子查询)：条件在查询结果中不等于任意一个
- some
同any完全一样
- all
=all(列子查询)：等于里面所有
<>all(列子查询)：不等于其中所有

数据库的备份与还原

sql备份

整库数据备份也叫sql数据备份，备份的结果都是sql指令，sql备份不只是备份数据，还备份对应的sql指令（表结构），即便是数据库遭到毁灭性的破坏，也能利用sql备份实现数据还原。sql备份因为需要备份数据结构，因此产生的备份文件特别大，不适合特大型数据备份，也不适合数据变换频繁型数据库

sql备份用到的是专门的备份客户端，因此还没与数据库服务器进行连接
语法：mysqldump -hPup 数据库名字[表1 表2 ...] > 备份文件地址

数据还原

利用MySQL客户端，在没有登录数据库之前，可以直接利用该客户端进行数据还原
mysql -hPup 数据库 < 备份地址
连接进入数据库之后，sql指令提供了一种导入sql指令的方式
source sql文件位置

用户权限管理

在不同的项目中，给不同的角色（开发者）不同的权限，为了保证数据库数据的安全

用户管理

MySQL需要客户端进行连接认证才能进行服务器操作，需要用户信息

在MySQL中，对应的用户管理中，是由对应的host和user共同组成主键来区分用户
- user 用户名
- host 代表本质是允许访问的客户端（ip或者主机地址），如果host使用*代表所有的用户（客户端）都可以访问

创建用户

直接使用root用户在mysql.user表中插入记录（不推荐）
专门创建用户的sql指令
create user '用户名'@'HOST' identify by '明文密码'

删除用户

    mysql中user是带着host本身的（具有唯一性）
    语法：drop user 'user'@'host'

修改用户密码

    mysql中提供了多种修改的方式，基本上都必须使用对应提供的一个系统函数：password(),需要靠该函数对密码进行加密处理
    1. 使用专门的修改密码的指令
      语法： set password for 用户= password('新明文密码')
    2. 使用更新语句update更新mysql.user表中对应用户的password字段
      语法： update mysql.user set password= password('新的明文密码') where user='用户名' and host='HOST';

权限管理

数据权限：增删改查（insert，delete，update，select)
结构权限：结构操作（create，drop，alter）
管理权限：权限管理（create user，grant，revoke），通常只给管理员此权限

1. 授予权限：grant

      语法： grant 权限列表 on 数据库名.表名 to 用户

2. 取消权限： revoke

      将权限从用户手中收回,权限回收不需要刷新权限，用户马上就会感受到
      语法 revoke 权限列表 on 数据库名.表名 from 用户

3. 刷新权限： flush

      将当前对用户权限的操作，进行一个刷新，将操作的具体内容同步到对应的表中
      语法：flush privileges;

root密码重置

停止MySQL服务
重新启动服务：mysqld --skip-grant-tables, 启动服务器但是跳过权限
此时启动的MySQL服务没有权限概念，非常危险，任何客户端，不需要任何用户信息都可以直接登录，而且是root权限
修改root用户密码
关闭mysqld进程，重新正常启动MySQL服务

外键

一张表A中有一个字段，保存的值指向另外一张表B的主键，B表称为主表，A表称为从表，主表被指向的主键被称为从表的外键
，外键又称为外关键字，外键表示了两张表之间的相互关系

增加外键

创建表时候增加外键（类似主键）: [constraint 外键名] foreign key(外键字段) references 主表（主键）
表完成后增加外键： alter table 表格 add constraint 外键名 foreign key(外键字段 ) references 主表（主键）

删除外键

语法： alter table 从表 drop foreign key 外键名字(就是constraint 外键名这个名字)

ps:删除外键只会把外键索引删除，不会删除增加外键时自动增加的普通索引，这个普通索引需要再次手动删除，方法：alter table drop index 索引名字

外键的基本要求

外键字段需要保证与关联的主表的关键字段类型完全一致，基本属性也要相同
如果是在表后增加外键，对数据还有一定的要求（从表数据与主表的关联关系）
外键只能使用innodb存储引擎，myisam不支持

外键约束

通过建立外键关系之后，对主表和从表都会有一定的数据约束效果

约束的基本概念

当一个外键产生时：外键所在的表（从表）会受制于主表数据的存在从而导致数据不能随意插入（不能插入主表关键字段不存在的数据）
当主表的关键字段被从表的外键引用时，对主表关键字段操作的时候就不会那么随意（比如不能随便删除一个被从表引入的记录，因为需要保证从表数据的有效性）

外键约束的基本概念

创建外键的时候，可以对外键约束进行选择性的操作

语法：add foreign key(外键字段) references 主表（主键） on 约束条件

约束条件有三种：
1. district: 严格模式，默认，不允许操作
1. casecade: 级联模式，一起操作，主表变化，从表数据跟着变化
1. set null: 置空模式，主表变化（删除),从表对应记录设置为空，前提是表中对应的外键字段允许为空

外键约束主要约束的对象是主表操作，从表就是不能插入主表不存在的数据

通常在进行约束的时候，需要指定操作：update和delete

常用的约束模式： on update casecade ,on delete set null , 更新级联，删除置空

外键约束的作用

保证数据的完整性：主表与从表的数据要一致

正是因为外键有非常强大的数据约束作用，而且可能导致数据在后台变化的不可控，导致程序在进行设计开发逻辑的时候，没有办法去很好的把握数据（业务），所以外键比较少使用，这也是很多存储引擎不支持外键约束的原因

视图

视图本质上是一张虚拟的表，所以它的增删改查操作都跟Table的操作类似

增加视图

语法：create view 视图名字 as select语句

删除视图

语法：drop view 视图名字

修改视图

语法： alter view 视图名字 as select语句

查看视图

语法： select 字段名字 from 视图名字

事务

事务是访问并可能更新数据库中各种数据项的一个程序执行单元，由事务开始和事务结束之间执行的全体操作组成

事务的基本原理

将用户所做的所有操作暂时保存在一个事务操作日志里，等到用户确认结果之后再保存到存储引擎中

自动事务

系统利用变量‘autocommit’做了额外的步骤来帮助用户确认操作结果，操作结束之后根据该变量的值来实现是否自动提交到存储引擎

关闭自动事务

set autocommit = 0|off

关闭自动事务之后，需要用户提供是否同步的命令：

commit：提交，同步到数据表，事务日志也会被清空
rollback：回滚，清空之前的操作，不要了

通常，我们不会关闭自动事务，这样操作太麻烦，因此只会在需要使用事务处理的时候，才会进行手动事务

手动事务

不管是开始、过程还是结束都需要用户，手动的发送事务操作指令来实现

手动事务对应的命令：
1. start transaction ：开始事务，从这条语句开始，后面的所有语句都不会直接写入到数据表，而是保存在事务日志中
1. 事务处理：多条连续SQL指令
1. 事务结束：commit|rollback：提交|回滚

回滚点

当有一系列事务操作时，其中的部分步骤如果成功了，回滚操作的时候没有必要将所有的操作从头再来，可以在某个之前操作都成功的点，设置一个记号（回滚点），如果后面的操作有失败，那么可以回滚到这个记号的位置

增加回滚点： savepoint 回滚点名字
回到回滚点： rollback 回滚点名字

ps:设置了多个回滚点，如果回滚到某个回滚点，那么这个回滚点之后的所有回滚点都会失效

特性

原子性: 一个事务是一个不可分割的工作单位，事务中包括的所有操作要么都做，要么都不做
一致性：事务必须使数据库从一个一致性状态变到另一个一致性状态（数据表中的数据修改，要么是所有操作一次性修改，要么是根本不动），一致性与原子性是密切相关的

隔离性：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
如果一个客户端在使用事务操作一个数据（可能是一行、也可能是整表）的时候，另外一个客户端不能对该数据进行操作

  什么时候是行被隔离？什么时候是整表被隔离？
  如果条件中使用了索引（主键），系统是根据主键直接找到某条记录，这个时候与其他记录无关，那么只隔离一条记录
  如果说系统是通过全表检索（每一条记录都去检查，没有索引），被检索的所有数据都会被锁定（整表隔离）

持久性：指一个事务一旦提交，它对数据库中数据的改变是永久性的

SQL编程

变量

MySQL本质是一种编程语言，需要很多变量来保存数据，MySQL中很多属性控制都是通过MySQL中固有的变量来实现

系统变量
系统内部定义的变量，针对所有用户（MySQL客户端）有效
查看系统变量
查看系统所有变量： show variables；
select查询变量的数据值： select @@变量名
修改系统变量：
1.局部修改：会话级别，只针对当前自己客户端的当次连接
set 变量名= value
2.全局修改：针对所有的客户端，全时刻有效，不过修改只针对新连接客户端有效，不影响现已连接的客户端
set global 变量名= value
set @@global.变量名 = value

会话变量
会话变量也称之为用户变量，会话变量跟MySQL客户端绑定，设置的变量只对当前用户使用的当前客户端有效

  1. 定义变量（MySQL中因为没有比较符号‘==’，所以使用赋值符号‘=’代替比较符号，有时在赋值的时候会报错，为了解决这个问题增加了一个变量的赋值符号':='
      set @变量名:=value
  2. 通过查询数据为变量赋值
      1. 赋值且查看过程：
        select @变量1 ：= 字段1，@变量2 ：= 字段2 from 数据源 where 条件
      2. 赋值不查看数据：
        select 字段1，字段2 from 数据源 where 条件 into @变量1，@变量2

  3. 查看变量： select @变量名

局部变量：
作用范围在begin和end语句块之间，declare专门用于定义局部变量

流程结构

if分支

基本语法

在select查询当中，当做一种条件来判断
语法： if（条件，为真结果，为假结果）

用在复杂的语句块中（函数/存储过程/触发器）
基础语法

if 条件表达式 then
    满足条件要执行的语句；
else
    if 条件表达式 then
        满足条件要执行的语句；
    else
        不满足条件要执行的语句；
    end if;
end if;

while循环

循环体都是需要在大型代码块中使用

基本语法
while 条件 do
要循环的代码；
end while;

结构标识符

为默写特定的结构进行命名，然后为的是在某些地方使用名字

基本语法：
标识名字： while 条件 do
循环体
end while【标识名字];

标识符的存在主要是为了循环体中使用循环控制，在MySQL中没有continue和break，但是有自己的关键字替代
- interate 标识名字：迭代，就是以下的代码不执行，重新开始循环，相当于continue
- leave 标识名字：离开，整个循环终止，相当于break

函数

在MySQL中，也分为内置函数（系统函数）和自定义函数，使用 select 函数名（参数列表）调用

字符串函数

char_length(): 判断字符串的字符数
length(): 判断字符串的字节数
concat(): 连接两个字符串
instr(): 判断指定字符是否在目标字符串中存在，存在则返回其位置，不存在则返回0
lcase(): 将字符串小写
ltrim(): 消除左边对应的空格
left(): 从左侧开始截取字符串到指定位置
mid(): 从中间指定位置开始截取，如果不指定截取长度，直接到最后

时间函数

now(): 返回当前时间（日期时间）
curdate(): 返回当前日期
curtime(): 返回当前时间
datediff(): 判断两个日期之间间隔的天数
date_add(日期，interval 数值 day|hour|minute|second): 时间相加
unix_timestamp(): 时间戳
from_unixtime(): 将时间戳转换为时间日期格式

数学函数

abs(): 绝对值
ceiling(): 向上取整
floor()：向下取整
pow(): 求指数，谁的多少次方
rand() : 获取一个随机数（0-1之间）
round(): 四舍五入

其他函数

md5()：对数据进行md5加密
version(): 获取版本号
datebase(): 显示当前所在数据库
uuid(): 生成一个唯一标识符

自定义函数

基础语法

delimiter $$ //修改语句结束符为‘$$’
create function 函数名(形参) returns 返回值类型
begin
    函数体
    return 返回值数据
end
$$
delimiter ;  //将语句结束符修改回'；'

查看函数

show function status 函数名;
show create function 函数名;

调用函数

select 函数名();

删除函数

drop function 函数名;

注意事项

自定义函数是属于用户级别的，只有当前客户端对应的数据库中可以调用
可以在不通的数据库下看到对应的函数，但是不可以调用
自定义函数通常是为了将多行代码集合到一起解决一个重复性的问题，非复用性的原因没必要创建自定义函数
函数因为必须规范返回值，那么在函数内部不能使用select指令，因为select一旦执行就会得到一个结果

存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译（效率比较高），用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它

与函数的区别

相同点

存储过程和函数的目的都是为了可重复的执行操作数据库的SQL语句的集合
存储过程函数都是一次编译，后续执行

不同点

标识符不同，函数的标识符为function，过程为：procedure
函数中有返回值，且必须返回，而过程没有返回值
过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除了在select中，必须将返回值赋给变量
函数是select调用，而过程不是

存储过程操作

创建过程
delimiter $$
create procedure 过程名字([参数列表])
begin
过程体
end
$$
delimiter ;

查看过程

查看存储过程跟查看函数是完全一样的

过程调用

过程没有返回值，所以select不可能调用procedure，调用过程有一个专用的语法：call procedure名字；

删除过程

  drop procedure 过程名字

存储过程的形参类型

存储过程允许提供参数（形参和实参），procedure的参数也和函数一样，需要指定其类型，但是procedure对参数还有额外的要求，自己的参数分类

in：表示参数从外部传入到里面使用（过程内部使用），可以是直接数据也可以是保存数据的变量
out: 表示参数是从过程里面把数据保存到变量中，交给外部使用，传入的必须是变量；如果传入的out变量在外部本身有数据，那么在进入procedure之后，第一件事就是被清空，设为null
inout：数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返还给外部

触发器

触发器是一种特殊类型的存储过程，通过事件进行触发而被执行，不同于存储过程通过存储过程名字被调用

作用

可以在写入数据表前，强制检验或转换数据（保证数据安全）
触发器发生错误时，异动的结果会被撤销（如果触发器执行错误，前面用户执行成功的操作也会被撤销，事务安全）
部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器
可依照特定的情况，替换异动的指令（MySQL不支持）

触发器优缺点

优点

触发器可通过数据库中的相关表实现级联更改（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作）
保证数据安全，进行安全校验

缺点

对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度
造成数据在程序层面不可控

触发器基本语法

创建

  create trigger 触发器名字 触发时机 触发事件 on 表名 for each row
  begin
      sql操作
  end

触发时机

每张表中对应的行都会有不同的状态，当sql指令发生时，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后（before和after）

触发事件

mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

查看触发器
show triggers;
show create trigger 触发器名字；

删除触发器
drop trigger 触发器名字；

触发器实际应用

触发器针对的是数据表中的每条记录（每行），每行在数据操作前后都有一个对应的状态，触发器在执行之前就将对应的状态获取到了，将没有操作之前的状态（数据）都保存到old关键字中，而操作后的状态都放到new中

在触发器中，可以通过old.字段名和new.字段名来获取绑定表中对应的记录数据

old和new并不是所有触发器都有，因为insert插入前全为空，所以没有old，delete清空数据了，所以没有new

mysql

once

这个人太懒什么东西都没留下

MySQL笔记

关系型数据库

关系数据结构

关系操作集合

关系完整性约束

SQL

SQL分类

MySQL

mysql服务端架构有以下几层构成：

1. 字段（第四层）： Field，具体存储某种类型的数据（实际存储单元

库操作

创建数据库

显示数据库

字符集

选择数据库

修改数据库

删除数据库

表操作

创建数据表

复制已有表结构

显示表结构

显示创建表的指令

修改表结构

数据操作

插入数据

查询数据

删除数据

修改数据

表关系

MySQL高级数据操作

插入数据

多数据插入

主键冲突

蠕虫复制

更新数据

删除数据

查询数据

查询中的运算符

算术运算符

比较运算符

逻辑运算符

in运算符

is运算符

like运算符

联合查询

连接查询

交叉连接 cross join

内连接

外连接

using关键字

子查询

数据库的备份与还原

sql备份

用户权限管理

用户管理

权限管理

root密码重置

外键

增加外键

删除外键

外键的基本要求

外键约束

外键约束的作用

视图

增加视图

删除视图

修改视图

查看视图

事务

事务的基本原理

自动事务

手动事务

特性

SQL编程

变量

流程结构

if分支

while循环

函数

字符串函数