MySQL笔记系统总结

一、初识数据库

1.1 数据库分类

网络数据库：
层级数据库：
关系数据库：关系型数据库：存储在磁盘中。关系型数据库：存储在内存中

1.2 关系型数据库

概念： 关系数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
关系数据结构： 指的数据以什么方式来存储，是一种二维表的形式存储本质：二维表
关系操作集合： 如何来关联和管理对应的存储数据，SQL指令
关系完整性约束： 数据内部有对应的关联关系，以及数据与数据之间也有对应的关联关系
表内约束： 对应的具体列只能放对应的数据（不能乱放）
表间约束： 自然界各实体都是有着对应的关联关系（外键）
关系操作集合： 如何来关联和管理对应的存储数据，SQL指令
小型关系型数据库： Microsoft Access，SQLite
中型关系型数据库： SQL Server，Mysql
大型关系型数据库： Oracle，DB2

1.3 SQL介绍

SQL： 结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
SQL分类：
1、数据查询语言：专门用于查询数据：代表指令为select/show
2、数据操作语言：专门用于写数据：代表指令为insert，update和delete
3、事务处理语言：专门用于事务安全处理：transaction
4、数据控制语言：专门用于权限管理：代表指令为grant和revoke
5、数据定义语言：专门用于结构管理：代表指令create和drop（alter）

1.4 MySQL基本介绍

MySQL是一个关系型数据库管理系统

1.5MySQL的客服端的系统操作指令

1、启动和停止MySQL服务
Net start 服务（mysql）：开启服务
Net stop mysql：关闭服务

net start mysql   //启动MySQL
net start mysql   //关闭MySQL

2、登录MySQL
连接认证基本语法：
Mysql.exe/mysql -h主机地址 -P端口 -u用户名 -p密码
注意事项
1、 通常端口都可以默认：mysql坚挺的端口通常都是3306
2、 密码的输入可以先输入-p，直接换行，然后再以密文方式输入密码
3、退出
建议方式：使用SQL提供的指令
Exit; //exit带分号
\q; //quit缩写
Quit：
4、Mysql服务端架构
Mysql服务端架构有以下几层构成：
1、 数据库管理系统（最外层）：DBMS，专门管理服务器端的所有内容
2、 数据库（第二层）：DB，专门用于存储数据的仓库（可以有很多个）
3、 二维数据表（第三层）：Table，专门用于存储具体实体的数据
4、 字段（第四层）：Field，具体存储某种类型的数据（实际存储单元）
数据库中常用的几个关键字
Row：行
Column：列（field）

二、数据库基本操作

2.1数据库的基本操作

1.创建数据库：基本语法：create database 数据库名字 [库选项];

库选项：数据库的相关属性
字符集：charset 字符集，代表着当前数据库下的所有表存储的数据默认指定的字符集（如果当前不指定，那么采用DBMS默认的）
校对集：collate 校对集
Create database 数据库名字 charset 字符集名称;

2.显示数据库:
基本语法：show databases;
显示部分:基本语法：show databases like ‘匹配模式’;
_：匹配当前位置单个字符
%：匹配指定位置多个字符
显示数据库创建语句:基本语法：show create database 数据库名字;
3.选择数据库:
基本语法：use 数据库名字;
4.修改数据库
基本语法：alter database 数据库名字 charset = 字符集;
5.删除数据库
基本语法：drop database 数据库名字;
删除虽简单，但是切记要做好安全操作：确保里面数据没有问题。（重要）

2.2数据表的基本操作

1.创建表

基本语法：create table 表名(字段名 字段类型 [字段属性], 字段名 字段类型 [字段属性],…) [表选项]

复制已有表结构：从已经存在的表复制一份（只复制结构：如果表中有数据不复制）
基本语法：create table 新表名 like 表名; //只要使用数据库.表名，就可以在任何数据库下访问其他数据库的表名

2.显示数据表
基本语法：show tables;

匹配显示表
基本语法：show tables like ‘匹配模式’;

3.显示表结构
本质含义：显示表中所包含的字段信息（名字，类型，属性等）
Describe 表名
Desc 表名
show columns from 表名

4.显示表创建语句
查看数据表创建时的语句：此语句看到的结果已经不是用户之前自己输入的。
基本语法：show create table 表名;

Mysql中有多种语句结束符
;与\g所表示的效果是一样的，都是字段在上排横着，下面跟对应的数据
\G字段在左侧竖着，数据在右侧横着
5.设置表属性
表属性指的就是表选项：engine，charset和collate
基本语法：alter table 表名 表选项 [=] 值;

注意：如果数据库已经确定了，里面有很多数据了，不要轻易修改表选项（字符集影响不大）
6.修改表结构
修改表名：rename table 旧表名 to 新表名

修改表选项：alter table 表名 表选项 [=] 新值
新增字段：alter table 表名 add [column] 新字段名 列类型 [列属性] [位置first/after 字段名]

字段位置：字段想要存放的位置
First：在某某之前（最前面），第一个字段
After 字段名：放在某个具体的字段之后（默认的）

修改字段名：alter table 表名 change 旧字段名 新字段名 字段类型 [列属性] [新位置]

修改字段类型（属性）：alter table 表名 modify 字段名 新类型 [新属性] [新位置]

删除字段：alter table 表名 drop 字段名

7.删除表结构
基础语法：drop table 表名[,表名2…]，可以同时删除多个数据表

批量删除表

2.3数据的基本操作

1.插入操作
基本语法：向表中指定字段插入数据
Insert into 表名[(字段列表)] values(对应字段列表)

注意：后面（values中）对应的值列表只需要与前面的字段列表相对应即可（不一定与表结构完全一致）
基本语法：向表中所有字段插入数据
Insert into 表名 values（对应表结构） //值列表必须与字段列表一致

2.查询操作
查询表中全部数据：select * from 表名; //表示匹配所有的字段

简单条件查询数据：select 字段列表/ from 表名 where 字段名 = 值; //mysql中没有==符号

3.删除操作删除操作
基本语法：delete from 表名 [where 条件]; //如果没有where条件：意味着系统会自动删除该表所有数据（慎用）

4.更新操作
基本语法：update 表名 set 字段名 = 新值 [where 条件];//如果没有where条件，那么所有的表中对应的那个字段都会被修改成统一值。

三、MySQL的数据类型

3.1列类型（字段类型）

1.整数类型
1.Tinyint：迷你整形，系统采用一个字节来保存的整形：一个字节 = 8位，最大能表示的数值是0-255
2.Smallint：小整形，系统采用两个字节来保存的整形：能表示0-65535之间
3.Mediumint：中整形，采用三个字节来保存数据。
4.Int：整形（标准整形），采用四个字节来保存数据。
5.Bigint：大整形，采用八个字节来保存数据

错误原因：并不是说tinyint没有这么大的空间，而是因为mysql默认的为整形增加负数。
实际表示的区间为-128,127
无符号：表示存储的数据在当前字段中，没有负数（只有正数，区间为0-255）
基本语法：在类型之后加上一个 unsigned
显示长度：Tinyint(3)： 表示最长可以显示3位，unsigned说明只能是正数，0-255永远不会超过三个长度
Tinyint(4)：表示最长可以显示4位，-128~127
2.小数类型
1.浮点型：
Float:又称之为单精度类型：系统提供4个字节用来存储数据，但是能表示的数据范围比整型大的多，大概是10^38；只能保证大概7个左右的精度（如果数据在7位数以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）

基本语法
Float：表示不指定小数位的浮点数
Float(M,D)：表示一共存储M个有效数字，其中小数部分占D位
Float(10,2)：整数部分为8位，小数部分为2位

Double:Double又称之为双精度：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右。

2.定点数

3.时间日期类型
Date:日期类型：系统使用三个字节来存储数据，对应的格式为：YYYY-mm-dd，能表示的范围是从1000-01-01 到9999-12-12，初始值为0000-00-00
1.Time:时间类型：能够表示某个指定的时间，但是系统同样是提供3个字节来存储，对应的格式为：HH:ii:ss，但是mysql中的time类型能够表示时间范围要大的多，能表示从-838:59:59~838:59:59，在mysql中具体的用处是用来描述时间段。

2.Datetime:日期时间类型：就是将前面的date和time合并起来，表示的时间，使用8个字节存储数据，格式为YYYY-mm-dd HH:ii:ss，能表示的区间1000-01-01 00:00:00 到9999-12-12 23:59:59，其可以为0值：0000-00-00 00:00:00

3.Timestamp:时间戳类型：mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是：YYYY-mm-dd HH:ii:ss

4.Year:年类型：占用一个字节来保存，能表示1900_{2155年，但是year有两种数据插入方式：0}99和四位数的具体年

3、 year的特殊性：可以采用两位数的数据插入，也可以采用四位数的年份插入
4、 year进行两位数插入的时候，有一个区间划分，零界点为69和70：当输入69以下，那么系统时间为20+数字，如果是70以上，那配系统时间为19+数字
5、 timestamp当对应的数据被修改的时候，会自动更新（这个被修改的数据不是自己）
6、 time类型特殊性：本质是用来表示时间区间（当前时间之后的多少个小时），能表示的范围比较大
7、 在进行时间类型录入的时候（time）还可以使用一个简单的日期代替时间，在时间格式之前加一个空格，然后指定一个数字（可以是负数）：系统会自动将该数字转换成天数 * 24小时，再加上后面的时间。

在mysql中，有一项规定：mysql的记录长度（record == 行row）总长度不能超过65535个字节。
Varchar能够存储的理论值为65535个字符：字符在不同的字符集下可能占用多个字节。
2、 计算在utf8和GBK下对应的varchar能够存储的长度
Utf8 最多只能存储21844个字符
GBK最多只能存储32766个字符

4.字符串型
**1.Char:定长字符：**指定长度之后，系统一定会分配指定的空间用于存储数据
基本语法：char(L)，L代表字符数（中文与英文字母一样），L长度为0到255

**2.Varchar:变长字符：**指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度）
基本语法：Varchar(L)，L代表字符数，L的长度理论值位0到65535
如果确定数据一定是占指定长度，那么使用char类型；
如果不确定数据到底有多少，那么使用varchar类型；
如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar

3.Text：文本类型：本质上mysql提供了两种文本类型
Text：存储普通的字符文本
Blob：存储二进制文本（图片，文件），一般都不会使用blob来存储文件本身，通常是使用一个链接来指向对应的文件本身。
Text：系统中提供的四种text
Tinytext：系统使用一个字节来保存，实际能够存储的数据为：2 ^ 8 + 1
Text：使用两个字节保存，实际存储为：2 ^ 16 + 2
Mediumtext：使用三个字节保存，实际存储为：2 ^ 24 + 3
Longtext：使用四个字节保存，实际存储为：2 ^ 32 + 4

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

**4.Enum：**枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果
如果确定某个字段的数据只有那么几个值：如性别，男、女、保密，系统就可以在设定字段的时候规定当前字段只能存放固定的几个值：使用枚举

基本语法：enum(数据值1,数据值2…)
系统提供了1到2个字节来存储枚举数据：通过计算enum列举的具体值来选择实际的存储空间：如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。
基本语法：enum(数据值1,数据值2…)
系统提供了1到2个字节来存储枚举数据：通过计算enum列举的具体值来选择实际的存储空间：如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。

将数据转换成数值：而普通字符串转换成数值为0
Select 字段名 + 0 from 表名;

5.Set
集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制：1表示该选项被选中，0表示该选项没有被选中
基本语法：set(‘值1’,’值2’,’值3’…)
系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元
1个字节：set只能有8个选项
2个字节：set只能有16个选项
3个字节：set只能表示24个选项
8个字节：set可以表示64个选项
2、 插入数据：可以插入多个数据，就是在数据插入的字符串中，使用对应的逗号“,”将选项进行隔开

3、 数据选项所在的数据与数据插入的顺序无关：最终都会变成选项对应的顺序

Set集合的意义：
1、 规范数据
2、 节省存储空间

Enum：单选框
Set：复选框

3.2 列属性

属性又称之为字段属性，在mysql中一共有6个属性：null，默认值，列描述，主键，唯一键和自动增长
1.Null属性:
NULL属性：代表字段为空
注意：
1、 在设计表的时候，尽量不要让数据为空
2、 Mysql的记录长度为65535个字节，如果一个表中有字段允许为NULL，那么系统就会设计保留一个字节来存储NULL，最终有效存储长度为65534个字节。
2.默认值
Default：默认值，当字段被设计的时候，如果允许默认条件下，用户不进行数据的插入，那么就可以使用事先准备好的数据来填充：通常填充的是NULL

3.3 列描述

列描述：comment，是专门用于给开发人员进行维护的一个注释说明

基本语法：comment ‘字段描述’;

查看Comment：必须通过查看表创建语句

3.4 主键

顾名思义：主要的键，primary key，在一张表中，有且只有一个字段，里面的值具有唯一性

1.创建主键
1、 方案1：直接在需要当做主键的字段之后，增加primary key属性来确定主键
2、 方案2：在所有字段之后增加primary key选项：primary key(字段信息)

基本语法：alter table 表名 add primary key(字段);

2.查看主键
方案1：查看表结构

方案2：查看表的创建语句方案2：查看表的创建语句

3.删除主键
基本语法：alter table 表名 drop primary key;

4.复合主键
案例：有一张学生选修课表：一个学生可以选修多个选修课，一个选修课也可以由多个学生来选：但是一个学生在一个选修课中只有一个成绩。

5.主键约束
主键一旦增加，那么对对应的字段有数据要求

1、 当前字段对应的数据不能为空；
2、 当前字段对应的数据不能有任何重复

6.主键分类
主键分类采用的是主键所对应的字段的业务意义分类
业务主键：主键所在的字段，具有业务意义（学生ID，课程ID）
逻辑主键：自然增长的整型（应用广泛）

3.5自动增长

自动增长：auto_increment，当给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据。
原理：
1、 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记住当前对应的数据值，再给定一个指定的步长
2、 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据
3、 自动增长的触发：给定属性的字段没有提供值
4、 自动增长只适用于数值
1.使用自增长
基本语法：在字段之后增加一个属性auto_increment

插入数据：触发自动增长，不能给定具体值
2.删除自动增长
删除自增长：就是在字段属性之后不再保留auto_increment，当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长

3.初始设置
在系统中，有一组变量用来维护自增长的初始值和步长
Show variables like ‘auto_increment%’;

4.细节问题
1、 一张表只有一个自增长：自增长会上升到表选项中
2、 如果数据插入中没有触发自增长（给定了数据），那么自增长不会表现

3、 自增长修改的时候，值可以较大，但是不能比当前已有的自增长字段的值小

3.6唯一键

唯一键：unique key，用来保证对应的字段中的数据唯一的。

主键也可以用来保证字段数据唯一性，但是一张表只有一个主键。
1、 唯一键在一张表中可以有多个。
2、 唯一键允许字段数据为NULL，NULL可以有多个（NULL不参与比较）
1.创建唯一键
创建唯一键与创建主键非常类似
1、 直接在表字段之后增加唯一键标识符：unique[ key]1、 直接在表字段之后增加唯一键标识符：unique[ key]

2、 在所有的字段之后使用unique key(字段列表);

3、 在创建完表之后也可以增加唯一键
alter table 表名 add unique key(字段列表);

2.查看唯一键
唯一键是属性，可以通过查看表结构来实现
唯一键效果：在不为空的情况下，不允许重复
在查看表创建语句的时候，会看到与主键不同的一点：多出一个“名字”

3.删除唯一键

一个表中允许存在多个唯一键：假设命令为主键一样：alter table 表名 drop unique key;//错误的

Index关键字：索引，唯一键是索引一种（提升查询效率）

删除的基本语法：alter table 表名 drop index 唯一键名字;

修改唯一键：先删除后增加

4.复合唯一键
唯一键与主键一样可以使用多个字段来共同保证唯一性；

一般主键都是单一字段（逻辑主键），而其他需要唯一性的内容都是由唯一键来处理。

3.7表关系

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

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

母亲表
母亲ID 姓名 年龄 身高
M1
M2

孩子表
孩子ID 姓名 年龄 身高
母亲ID
K1 M1
K2 M1

3.多对多
多对多：一张表中的一条记录在另外一张表中可以匹配到多条记录，反过来也一样。

多对多的关系如果按照多对一的关系维护：就会出现一个字段中有多个其他表的主键，在访问的时候就会带来不便。

既然通过两张表自己增加字段解决不了问题，那么就通过第三张表来解决。
师生关系
1、 一个老师教过多个班级的学生；
2、 一个学生听过多个老师讲的课；

多对多解决方案；增加一个中间表，让中间表与对应的其他表形成两个多对一的关系：多对一的解决方案是在“多”表中增加“一”表对应的主键字段。

四、高级数据库操作

4.1新增数据

1.多数据插入
基本语法：insert into 表名 [(字段列表)] values(值列表), (值列表)…;

2.主键冲突
主键冲突：在有的表中，使用的是业务主键（字段有业务含义），但是往往在进行数据插入的时候，又不确定数据表中是否已经存在对应的主键。

主键冲突的解决方案：
1、 主键冲突更新：
类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法。
Insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值;

2、 主键冲突替换：
当主键冲突之后，干掉原来的数据，重新插入进去。
Replace into [(字段列表)] values(值列表);

3.蠕虫复制
蠕虫复制：一分为二，成倍的增加。从已有的数据中获取数据，并且将获取到的数据插入到数据表中。

基本语法：

Insert into 表名 [(字段列表)] select */字段列表 from 表;

注意：
1、 蠕虫复制的确通常是重复数据，没有太大业务意义：可以在短期内快速增加表的数据量，从而可以测试表的压力，还可以通过大量数据来测试表的效率（索引）
2、 蠕虫复制虽好，但是要注意主键冲突。

4.2更新数据

1、 在更新数据的时候，特别要注意：通常一定是跟随条件更新
Update 表名 set 字段名 = 新值 where 判断条件;

2、 如果没有条件，是全表更新数据。但是可以使用limit 来限制更新的数量；
Update 表名 set 字段名 = 新值 [where 判断条件] limit 数量;

改变4个a变成e
Update my_simple set name = ‘e’ where name = ‘a’ limit 4;

4.3 删除数据

1、 删除数据的时候尽量不要全部删除，应该使用where进行 判定；
2、 删除数据的时候可以使用limit来限制要删除的具体数量

Delete删除数据的时候无法重置auto_increment

Mysql有一个能够重置表选项中的自增长的语法;
Truncate 表名; == drop - create

4.4 查询数据

完整的查询指令：

Select select选项 字段列表 from 数据源 where条件 group by分组 having条件 order by排序 limit限制;

Select选项：系统该如何对待查询得到的结果
All：默认的，表示保存所有的记录

Distinct：去重，去除重复的记录，只保留一条（所有的字段都相同）

字段列表：有的时候需要从多张表获取数据，在获取数据的时候，可能存在不同表中有同名的字段，需要将同名的字段命名成不同名的：别名 alias

基本语法：字段名 [as] 别名

4.5 Where子句

Where字句：用来从数据表获取数据的时候，然后进行条件筛选。

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

Where是通过运算符进行结果比较来判断数据。
Where字句：用来从数据表获取数据的时候，然后进行条件筛选。

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

Where是通过运算符进行结果比较来判断数据。

4.6 Group by子句

Group by表示分组的含义：根据指定的字段，将数据进行分组：分组的目标是为了统计
分组统计
Group by表示分组的含义：根据指定的字段，将数据进行分组：分组的目标是为了统计
分组统计
基本语法： group by 字段名;

Group by是为了分组后进行数据统计的，如果只是想看数据显示，那么group by没什么含义：group by将数据按照指定的字段分组之后，只会保留每组的第一条记录。
利用一些统计函数：（聚合函数）
count()：统计每组中的数量，如果统计目标是字段，那么不统计为空NULL字段，如果为*那么代表统计记录
avg()：求平均值
sum()：求和
max()：求最大值
min()：求最小值

Group_concat()：为了将分组中指定的字段进行合并（字符串拼接）

多分组
将数据按照某个字段进行分组之后，对已经分组的数据进行再次分组

基本语法：group by 字段1,字段2; //先按照字段1进行排序，之后将结果再按照字段2进行排序，以此类推。

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

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

多分组回溯统计

4.7 Having子句

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

1、 Having是在group by子句之后：可以针对分组数据进行统计筛选，但是where不行
查询班级人数大于等于4个以上的班级
Where不能使用聚合函数：聚合函数是用在group by分组的时候，where已经运行完毕

Having在group by分组之后，可以使用聚合函数或者字段别名（where是从表中取出数据，别名是在数据进入到内存之后才有的）

强调：having是在group by之后，group by是在where之后：where的时候表示将数据从磁盘拿到内存，where之后的所有操作都是内存操作。

4.8 Order by子句

Order by排序：根据校对规则对数据进行排序
基本语法：order by 字段 [asc|desc]; //asc升序，默认的

Order by也可以像group by一样进行多字段排序：先按照第一个字段进行排序，然后再按照第二个字段进行排序。
Order by 字段1 规则,字段2 规则;

4.9 Limit子句

Limit限制子句：主要是用来限制记录数量获取
记录数限制
纯粹的限制获取的数量：从第一条到指定的数量
基本语法： limt 数量;

Limit通常在查询的时候如果限定为一条记录的时候，使用的比较多：有时候获取多条记录并不能解决业务问题，但是会增加服务器的压力。

##4.10 分页
利用limit来限制获取指定区间的数据。
基本语法：limit offset,length; //offset偏移量：从哪开始，length就是具体的获取多少条记录
Mysql中记录的数量从0开始
Limit 0,2; 表示获取前两条记录

注意：limit后面的length表示最多获取对应数量，但是如果数量不够，系统不会强求

5.查询中的运算符

5.1算术运算符

+、-、*、/、%

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

5.2比较运算符

>、>=、<、<=、=、<>

通常是用来在条件中进行限定结果
=：在mysql中，没有对应的 ==比较符号，就是使用=来进行相等判断
<=>：相等比较

特殊应用：就是在字段结果中进行比较运算

在条件判断的时候，还有有对应的比较运算符：计算区间
Between 条件1 and 条件2;

Between中条件1必须小于条件2，反过来不可以

5.3 逻辑运算符

and、or、not
and：逻辑与

or：逻辑或

not：逻辑非

5.4 In运算符

In：在什么里面，是用来替代=，当结果不是一个值，而是一个结果集的时候
基本语法： in (结果1,结果2,结果3…)，只要当前条件在结果集中出现过，那么就成立

5.5 Is运算符

Is是专门用来判断字段是否为NULL的运算符
基本语法：is null / is not null

5.6 Like运算符

Like运算符：是用来进行模糊匹配（匹配字符串）
基本语法：like ‘匹配模式’;

匹配模式中，有两种占位符：
_：匹配对应的单个字符
%：匹配多个字符

6.联合查询

基本概念：联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，使用谓词为UNION或UNION ALL。

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

6.1 基本语法

基本语法：
Select 语句
Union [union 选项]
Select 语句;

Union选项：与select选项基本一样
Distinct：去重，去掉完全重复的数据（默认的）

All：保存所有的结果

注意细节：union理论上只要保证字段数一样，不需要每次拿到的数据对应的字段类型一致。永远只保留第一个select语句对应的字段名字。

6.2 Order by的使用

1、 在联合查询中，如果要使用order by，那么对应的select语句必须使用括号括起来

正确的语法：加上括号

2、 orderby在联合查询中若要生效，必须配合使用limit：而limit后面必须跟对应的限制数量（通常可以使用一个较大的值：大于对应表的记录数）

7.连接查询

连接查询：将多张表连到一起进行查询（会导致记录数行和字段数列发生改变）
连接查询的意义
在关系型数据库设计过程中，实体（表）与实体之间是存在很多联系的。在关系型数据库表的设计过程中，遵循着关系来设计：一对一，一对多和多对多，通常在实际操作的过程中，需要利用这层关系来保证数据的完整性。

连接查询分类

连接查询一共有以下几类：

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

7.1 交叉连接

交叉连接：将两张表的数据与另外一张表彼此交叉交叉连接：将两张表的数据与另外一张表彼此交叉
原理
1、 从第一张表依次取出每一条记录
2、 取出每一条记录之后，与另外一张表的全部记录挨个匹配
3、 没有任何匹配条件，所有的结果都会进行保留
4、 记录数 = 第一张表记录数 * 第二张表记录数；字段数 = 第一张表字段数 + 第二张表字段数（笛卡尔积）

语法
基本语法：表1 cross join 表2;

应用
交叉连接产生的结果是笛卡尔积，没有实际应用。
本质：from 表1,表2;

7.2 内连接

内连接：inner join，从一张表中取出所有的记录去另外一张表中匹配：利用匹配条件进行匹配，成功了则保留，失败了放弃。
原理
1、 从第一张表中取出一条记录，然后去另外一张表中进行匹配
2、 利用匹配条件进行匹配：
2.1 匹配到：保留，继续向下匹配
2.2 匹配失败：向下继续，如果全表匹配失败，结束
语法
基本语法：表1 [inner] join 表2 on 匹配条件;

1、 如果内连接没有条件（允许），那么其实就是交叉连接（避免）

2、 使用匹配条件进行匹配

3、 因为表的设计通常容易产生同名字段，尤其是ID，所以为了避免重名出现错误，通常使用表名.字段名，来确保唯一性3、 因为表的设计通常容易产生同名字段，尤其是ID，所以为了避免重名出现错误，通常使用表名.字段名，来确保唯一性

4、 通常，如果条件中使用到对应的表名，而表名通常比较长，所以可以通过表别名来简化

5、 内连接匹配的时候，必须保证匹配到才会保存

6、 内连接因为不强制必须使用匹配条件（on）因此可以在数据匹配完成之后，使用where条件来限制，效果与on一样（建议使用on）

应用
内连接通常是在对数据有精确要求的地方使用：必须保证两种表中都能进行数据匹配。

7.3 外连接

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

外连接分为两种：左外连接（left join），右外连接（right join）
左连接：左表是主表
右连接：右表是主表
原理

1、 确定连接主表：左连接就是left join左边的表为主表；right join就是右边为主表
2、 拿主表的每一条记录，去匹配另外一张表（从表）的每一条记录
3、 如果满足匹配条件：保留；不满足即不保留
4、 如果主表记录在从表中一条都没有匹配成功，那么也要保留该记录：从表对应的字段值都未NULL
语法
基本语法：
左连接：主表 left join 从表 on 连接条件;
右连接：从表 right join 主表 on连接条件;

左连接对应的主表数据在左边；右连接对应的主表数据在右边：

右连接查看数据

特点
1、 外连接中主表数据记录一定会保存：连接之后不会出现记录数少于主表（内连接可能）
2、 左连接和右连接其实可以互相转换，但是数据对应的位置（表顺序）会改变
应用
非常常用的一种获取的数据方式：作为数据获取对应主表以及其他数据（关联）

7.4 Using关键字

是在连接查询中用来代替对应的on关键字的，进行条件匹配。
原理
1、 在连接查询时，使用on的地方用using代替
2、 使用using的前提是对应的两张表连接的字段是同名（类似自然连接自动匹配）
3、 如果使用using关键字，那么对应的同名字段，最终在结果中只会保留一个。
语法
基本语法：表1 [inner,left,right] join 表2 using(同名字段列表); //连接字段

8.子查询

8.1 子查询概述

子查询：sub query

子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。

子查询：指在一条select语句中，嵌入了另外一条select语句，那么被嵌入的select语句称之为子查询语句。
主查询概念：主查询：主要的查询对象，第一条select语句，确定的用户所有获取的数据目标（数据源），以及要具体得到的字段信息。
子查询和主查询的关系
1、 子查询是嵌入到主查询中的；
2、 子查询的辅助主查询的：要么作为条件，要么作为数据源
3、 子查询其实可以独立存在：是一条完整的select语句

按功能分
标量子查询：子查询返回的结果是一个数据（一行一列）
列子查询：返回的结果是一列（一列多行）
行子查询：返回的结果是一行（一行多列）
表子查询：返回的结果是多行多列（多行多列）
Exists子查询：返回的结果1或者0（类似布尔操作）

按位置分
Where子查询：子查询出现的位置在where条件中
From子查询：子查询出现的位置在from数据源中（做数据源）

8.2 标量子查询

概念
标量子查询：子查询得到结果是一个数据（一行一列）
语法
基本语法：select * from 数据源 where 条件判断 =/<> (select 字段名 from 数据源 where 条件判断); //子查询得到的结果只有一个值

8.3 列子查询

概念
列子查询：子查询得到的结果是一列数据（一列多行）
语法
基本语法：
主查询 where 条件 in (列子查询);

8.4 行子查询

概念
行子查询：子查询返回的结果是一行多列
行元素
行元素：字段元素是指一个字段对应的值，行元素对应的就是多个字段：多个字段合起来作为一个元素参与运算，把这种情况称之为行元素。
语法
基本语法：
主查询 where 条件[（构造一个行元素）] = (行子查询);

总结
已经学过三个子查询：常见的三个子查询
标量子查询、列子查询和行子查询：都属于where子查询

8.5 表子查询

概念
表子查询：子查询返回的结果是多行多列，表子查询与行子查询非常相似，只是行子查询需要产生行元素，而表子查询没有。

行子查询是用于where条件判断：where子查询
表子查询是用于from数据源：from子查询
语法
基本语法：

Select 字段表 from (表子查询) as 别名 [where] [group by] [having] [order by] [limit];

8.6 Exists子查询

概念
Exists子查询：查询返回的结果只有0或者1，1代表成立，0代表不成立
语法
基本语法：where exists(查询语句); //exists就是根据查询得到的结果进行判断：如果结果存在，那么返回1，否则返回0

Where 1：永远为真

8.7 子查询中特定关键字的使用

In
主查询 where 条件 in (列子查询);
Any
任意一个
= any(列子查询)：条件在查询结果中有任意一个匹配即可，等价于in

<>any(列子查询)：条件在查询结果中不等于任意一个

1 =any(1,2,3) ===== true
1 <>any(1,2,3) ===== true

Some
与any完全一样：在国外，some与any的正面含义一致，但是否定就大不相同：not any与not some

开发者为了让对应的使用者不要在语法上纠结：重新设计了some
All
= all(列子查询)：等于里面所有
<>all(列子查询)：不等于其中所有

All数据展示

如果对应的匹配字段有NULL，那么不参与匹配

9. 数据库的备份与还原

9.1 数据库的备份

整库数据备份也叫SQL数据备份：备份的结果都是SQL指令

在Mysql中提供了一个专门用于备份SQL的客户端：mysqldump.exe

应用场景
SQL备份是一种mysql非常常见的备份与还原方式，SQL备份不只是备份数据，还备份对应的SQL指令（表结构）：即便是数据库遭到毁灭性的破坏（数据库被删），那么利用SQL备份依然可以实现数据还原。

SQL备份因为需要备份结构，因此产生的备份文件特别大，因此不适合特大型数据备份，也不适合数据变换频繁型数据库备份。
SQL备份
SQL备份用到的是专门的备份客户端，因此还没与数据库服务器进行连接。

基本语法：mysqldump/mysqldump.exe -hPup 数据库名字 [表1 [表2…]] > 备份文件地址

备份可以有三种形式：
1、 整库备份（只需要提供数据库名字）

2、 单表备份：数据库后面跟一张表
3、 多表备份：数据库后跟多张表

查看备份的成果

9.2 数据还原数据还原

Mysql提供了多种方式来实现：两种
Mysqldump备份的数据中没有关于数据库本身的操作，都是针对表级别的操作：当进行数据（SQL还原），必须指定数据库

1、 利用mysql.exe客户端：没有登录之前，可以直接用该客户端进行数据还原
Mysql.exe –hPup 数据库 < 文件位置

2、 在SQL指令，提供了一种导入SQL指令的方式
Source SQL文件位置; //必须先进入到对应的数据库

3、 人为操作：打开备份文件，复制所有SQL指令，然后到mysql.exe客户端中去粘贴执行。（不推荐）

10 用户权限管理

用户权限管理：在不同的项目中给不同的角色（开发者）不同的操作权限，为了保证数据库数据的安全。

通常，一个用户的密码不会长期不变，所以需要经常性的变更数据库用户密码来确保用户本身安全（mysql客户端用户）

10.1 用户管理

Mysql需要客户端进行连接认证才能进行服务器操作：需要用户信息。Mysql中所有的用户信息都是保存在mysql数据库下的user表中。

默认的，在安装Mysql的时候，如果不选择创建匿名用户，那么意味着所有的用户只有一个：root超级用户

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

10.2创建用户

理论上讲可以采用两种方式创建用户：
1、 直接使用root用户在mysql.user表中插入记录（不推荐）
2、 专门创建用户的SQL指令
基本语法：create user 用户名 identified by ‘明文密码’;
用户：用户名@主机地址
主机地址：’’ / ‘%’

简化版创建用户（谁都可以访问，不需要密码）简化版创建用户（谁都可以访问，不需要密码）

当用户创建完成之后，用户是否可以使用?

10.3 删除用户

注意：mysql中user是带着host本身的（具有唯一性）
基本语法：drop user 用户名@host;

10.4 修改用户密码

Mysql中提供了多种修改的方式：基本上都必须使用对应提供的一个系统函数：password()，需要靠该函数对密码进行加密处理。

1、 使用专门的修改密码的指令
基本语法：set password for 用户 = password(‘新的明文密码’);

2、 使用更新语句update来修改表
基本语法：update mysql.user set password = password(‘新的明文密码’) where user = ‘’ and host= ‘’;

10.5 权限管理

在mysql中将权限管理分为三类：
1、 数据权限：增删改查（select\update\delete\insert）
2、 结构权限：结构操作（create\drop）
3、 管理权限：权限管理（create user\grant\revoke）：通常只给管理员如此权限

授予权限：grant授予权限：grant
将权限分配给指定的用户
基本语法：grant 权限列表 on 数据库/.表名/ to 用户;
权限列表：使用逗号分隔，但是可以使用all privileges代表全部权限
数据库.表名：可以是单表（数据库名字.表名），可以是具体某个数据库（数据库.），也可以整库（.）

用户被分配权限以后不需要退出就可以看到效果
具体权限查看：单表权限只能看到数据库中的一张表

取消权限：revoke
权限回收：将权限从用户手中收回
基本语法：revoke 权限列表/all privileges on 数据库/.表/* from 用户;

权限回收，同样不需要刷新，用户马上就会感受到

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

10.6 密码丢失的解决方案

如果忘记了root用户密码，就需要去找回或者重置root用户密码
1、 停止服务
2、 重新启动服务：mysqld.exe –skip-grant-tables //启动服务器但是跳过权限

3、 当前启动的服务器没有权限概念：非常危险，任何客户端，不需要任何用户信息都可以直接登录，而且是root权限：新开客户端，使用mysql.exe登录即可

4、 修改root用户的密码：指定 用户名@host

5、 赶紧关闭服务器，重启服务

11. 外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

外键：foreign key
一张表（A）中有一个字段，保存的值指向另外一张表（B）的主键
B：主表
A：从表

11.1 增加外键

Mysql中提供了两种方式增加外键
1、 方案1：在创建表的时候增加外键（类似主键）
基本语法：在字段之后增加一条语句
[constraint 外键名] foreign key(外键字段) references 主表(主键);

MUL：多索引，外键本身是一个索引，外键要求外键字段本身也是一种普通索引

2、 方案2：在创建表后增加外键
Alter table 从表 add [constraint 外键名] foreign key(外键字段) references 主表(主键);

外键名字可以指定

11.2 修改&删除外键

外键不允许修改，只能先删除后增加
基本语法：alter table 从表 drop foreign key 外键名字;

外键不能删除产生的普通索引，只会删除外键自己

如果想删除对应的索引：alter table 表名 drop index 索引名字;

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

11.3 外键约束

外键约束：通过建立外键关系之后，对主表和从表都会有一定的数据约束效率。
约束的基本概念
1、 当一个外键产生时：外键所在的表（从表）会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作（不能插入主表不存在的数据）；

2、 如果一张表被其他表外键引入，那么该表的数据操作就不能随意：必须保证从表数据的有效性（不能随便删除一个被从表引入的记录）

11.4 外键约束的概念

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

基本语法： add foreign key(外键字段) references 主表(主键) on 约束模式;

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

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

通常在进行约束时候的时候，需要指定操作：update和delete
常用的约束模式： on update cascade, on delete set null，更新级联，删除置空

更新模式

删除模式

11.5 约束作用

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

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

十二、视图基本操作

12.1 创建视图

视图的本质是SQL指令（select语句）
基本语法：create view 视图名字 as select指令; //可以是单表数据，也可以是连接查询，联合查询或者子查询

Show tables/show create table[view]/desc 视图名字；

12.2 使用视图

视图是一张虚拟表：可以直接把视图当做“表”操作，但是视图本身没有数据，是临时执行select语句得到对应的结果。视图主要用户查询操作。

基本语法：select 字段列表 from 视图名字 [子句];

12.3 修改视图

修改视图：本质是修改视图对应的查询语句
基本语法：alter view 视图名字 as 新select指令;

12.4删除视图

基本语法：drop view 视图名字;

十三、事务安全

13.1 事务概念

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

13.2 事务基本原理

基本原理：Mysql允许将事务统一进行管理（存储引擎INNODB），将用户所做的操作，暂时保存起来，不直接放到数据表（更新），等到用于确认结果之后再进行操作。

13.3 自动事务

自动事务：autocommit，当客户端发送一条SQL指令（写操作：增删改）给服务器的时候，服务器在执行之后，不用等待用户反馈结果，会自动将结果同步到数据表。

证明：利用两个客户端，一个客户端执行SQL指令，另外一个客户端查看执行结果

自动事务：系统做了额外的步骤来帮助用户操作，系统是通过变量来控制的。Autocommit
Show variables like ‘autocommit%’;

关闭自动事务：关闭之后系统就不在帮助用户提交结果了
Set autocommit = Off;

查看执行结果

一旦自动事务关闭，那么需要用户提供是否同步的命令
Commit：提交（同步到数据表：事务也会被清空）
Rollback：回滚（清空之前的操作，不要了）

事务没有提交的对比查看：在执行事务端的客户端中，系统在进行数据查看的时候会利用事务日志中保存的结果对数据进行加工

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

13.4 手动事务

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

手动事务对应的命令：
1、 start transaction; //开启事务：从这条语句开始，后面的所有语句都不会直接写入到数据表（保存在事务日志中）
2、 事务处理：多个写指令构成
3、 事务提交：commit/rollback，到这个时候所有的事务才算结束

开启事务

执行事务
将多个连续的但是是一个整体的SQL指令，逐一执行
1、 事务操作1：新增数据

2、 事务操作2：更新数据

提交事务
确认提交：commit，数据写到数据表（清空）

回滚操作：rollback，所有数据无效并清空
回滚点
回滚点：savepoint，当有一系列事务操作时，而其中的步骤如果成功了，没有必要重新来过，可以在某个点（成功），设置一个记号（回滚点），然后如果后面有失败，那么可以回到这个记号位置。

增加回滚点：savepoint 回滚点名字; //字母数字和下划线构成
回到回滚点：rollback to 回滚点名字; //那个记号（回滚点）之后的所有操作没有了

注意：在一个事务处理中，如果有很多个步骤，那么可以设置多个回滚点。但是如果回到了前面的回滚点，后面的回滚点就失效；

1、 增加回滚点操作

2、 出现错误步骤

3、 回到正确点：回滚

事务特点
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
事务从start transaction起到提交事务（commit或者rollback），要么所有的操作都成功，要么就是所有的操作都失败；

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
数据表中的数据修改，要么是所有操作一次性修改，要么是根本不懂

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

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

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。