文章目录
数据库概念
数据库（database）是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库
数据库分类
- 网络数据库
网络数据库是指把数据库技术引入到计算机网络系统中，借助于网络技术将存储于数据库中的大量信息及时发布出去；而计算机网络借助于成熟的数据库技术对网络中的各种数据进行有效管理，并实现用户与网络中的数据库进行实时动态的数据交互 - 层级数据库
层次结构模型实质上是一种有根节点的定向有序树 - 关系数据库
关系数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据
数据库的另一种区分方式：基于存储介质
存储介质分为两种：磁盘和内存
- 关系型数据库：存储在磁盘中
- 非关系型数据库：存储在内存中
关系型数据库
关系型数据库，是建立在关系模型基础上的数据库，借助于数学模型来处理数据库中的数据
关系模型由三部分组成：
- 关系数据结构：指的数据以什么方式来存储，是一种二维表的形式存储，如
| 姓名 | 年龄 | 身高 | 体重 |
|---|---|---|---|
| 张三 | 20 | 178 | 70 |
| 李四 | 21 | 180 | 75 |
- 关系操作集合：如何来关联和管理对应的存储数据，即SQL指令
获取张三的年纪，已知条件为姓名：
select 年龄 from 二维表 where 姓名 = 张三; - 关系完整性约束：数据内部有对应的关联关系，以及数据与数据之间也有对应的关联关系
- 表内约束：对应的具体列只能放对应的数据
- 表间约束：自然界各实体都是有着对应的关联关系（外键）
SQL介绍
结构化查询语言（Structured Query Language），简称SQL，是一种特殊目的的编程语言，也是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，SQL就是专门为关系型数据库而设计出来的
SQL分类
- 数据查询语言（DQL:Data Query Language）
也叫数据检索语言，专门用于查询数据，代表指令为select/show - 数据操作语言（DML:Data Manipulation Language）
也称为动作查询语言，专门用于写数据。其语句包括动词INSERT，UPDATE和DELETE，它们分别用于添加、修改和删除表中的行 - 事务处理语言（TPL）
专门用于事务安全处理，它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN，TRANSACTION，COMMIT和ROLLBACK。（不是所有的关系型数据库都提供事务安全处理） - 数据控制语言（DCL）
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组队数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单各列的访问 - 数据定义语言（DDL）
专门用于结构管理，其语句包括动词create、alter和drop，在数据库中创建新表或删除表、为表加入索引等。
MySQL
MySQL介绍
MySQL是一个关系型数据库管理系统，是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的应用RDBMS（Relational Database Management System，关系型数据库管理系统）应用软件
- MySQL是一种开源免费的数据库产品
- MySQL对PHP的支持最好
MySQL中用到的操作指令就是SQL指令
启动和停止MySQL服务
MySQL是一种C/S结构，即客户端和服务端
服务端对应的软件：MySQL.exe
- 命令行方式
net start mysql：开启mysql服务
net stop mysql：关闭mysql服务 - 系统服务方式
前提：在安装mysql的时候已经将mysql添加到windows服务中了
- 第一种：右击我的电脑-管理-服务
- 第二种：命令行输入services.msc进入服务
查看当前运行的mysql服务：
tasklist| findstr "mysql"
登陆和退出MySQL系统
通过客户端（mysql.exe）与服务器进行连接认证，就可以进行操作
登陆：
- 找到mysql.exe，（通过cmd控制台：如果在安装的时候指定了mysql.exe所在路径为环境变量，就可以直接访问；如果没有则必须进入到mysql.exe所在路径）
- 输入对应的服务器地址
-h 域名/IP地址，中间没有空格 - 输入服务器中mysql监听的端口：
-P3306（注意大写） - 输入用户名：
-u:root - 输入密码：
-p:password，输入完-p直接换行可以密文输入密码
连接认证基本语法：mysql -h主机地址 -p端口 -u用户名 -p密码
退出：
断开与服务器的连接：通常mysql提供的服务器数量有限，一旦客户端用完，建议断开该连接
断开连接命令：exit;、\q、quit
MySQL服务端架构
- 数据库管理系统（最外层）：DBMS，专门管理服务器端的所有内容
- 数据库（第二层）：DM，专门用于存储数据的仓库（可以有很多个）
- 二位数据表（第三层）：table，专门用于存储具体实体的数据
- 字段（第四层）：field，具体存储某种类型的数据（实际存储单元）
数据库中常用的关键字：row-行，column-列
数据库基本操作
数据库是数据存储的最外层（最大单元）
创建数据库
语法：create database 数据库名字 [库选项]
库选项：数据库的相关属性：
- 字符集：charset字符集，如果不指定则采用DBMS默认字符集
如create database test charset gbk，创建了一个名为test的数据库，采用gbk字符集 - 校对集：collate校对集
显示数据库
每当用户创建一个数据库，那么在mysql的data文件夹里就会有一个对应的存储数据的文件夹
- 显示所有数据库：
语法：show databases;
该语句会显示所有的数据库，包含我们创建的和系统自带的数据库 - 显示部分数据库
语法：show databases link '匹配模式';
_:匹配当前位置单个字符
%：匹配指定位置多个字符
匹配模式举例：
- 获取以my开头的全部数据库：
my% - 获取m开头，后面第一个字母不确定，最后为database的数据库：
m_database - 获取以database结尾的数据库：
%database
- 查看数据库创建语句
语法：show create database mydatabase;
系统数据库解释：
- information_schema：保存数据库所有的接口信息（表，库）
- mysql：核心数据库：权限关系
- performance_schema：保存了数据库的运行效率相关信息
语句结束符
mysl中有多种语句结束符，不一定非要用分号结束
;和\g效果是一样的，二维表输出时会横着显示，如show databases;\G结尾则会竖排输出二维表，如show databases\G
选择数据库
因为数据是存储到数据表，而数据表又存储在数据库下，因此要操作数据则必须进入到对应的数据库才可以
基本语法：use 数据库名字；
修改数据库
修改数据库字符集（库选项）：字符集和校对集
基本语法：alter database 数据库名字 charset = 字符集;
删除数据库
基本语法： drop database 数据库名字;
数据表操作
创建数据表
基本语法：create table 表名(字段名 字段类型 [字段属性],字段名 字段类型 [字段属性],...) [表选项]
在创建的时候，新表必须挂靠到指定的数据库下，可以用两种方式实现
- 在表前面加上数据库名字，用
.连接：数据库名.表名 - 在创建数据表之前先进入到对应的数据库：
use 数据库名
表名一般以所在数据库名的前两个字母加下划线开头
表选项：
- engine：存储引擎：mysql提供的具体存储数据的方式，默认为innodb
- charset：字符集，只对当前自己表有效（权重比数据库高）
- collate：校对集
复制已有表结构
从已经存在的表中复制一份（只复制结构，表中的数据不复制）
基本语法：create table 新表名 like 表名;
使用数据库.表名就可以在任何数据库下访问其他数据库的表名
create table students like classes.students;
显示数据表
每当一张数据表创建，那么就会在对应的数据库下创建一些文件（与存储引擎有关）
基本语法：
- 显示所有表：
show tables; - 匹配显示表：
show tables like '匹配模式';
显示表结构
本质含义：显示表中所包含的字段信息（名字，类型，属性等）
describe 表名;desc 表名;show columns from 表名;
这三个命令结果都是相同的，将输出6个属性：
- Field：字段名
- Type：字段类型
- Null：值是否允许为空
- Key：索引
- Default：如果不给值，会引用的默认值
- extra：额外的属性
显示表创建语句
查看数据表创建时的语句，不过已经被系统加工过了
基本语法：show create table 表名;
设置表属性
表属性指的就是表选项：engine,charset和collate
基本语法：alter table 表名 表选项 = 值;
如果表里已经有很多数据了，修改表选项需谨慎
修改表结构
| 操作 | 语法 |
|---|---|
| 修改表名 | rename table 旧表名 to 新表名 |
| 修改表选项 | alter table 表名 表选项 = 新值 |
| 新增字段 | alter table 表名 add [column] 新字段名 列类型 [列属性] [位置：first/after字段名] |
| 修改字段名 | alter table 表名 change 旧字段名 新字段名 字段类型 [列属性] [新位置] |
| 修改字段类型（属性） | alter table 表名 modify 字段名 新类型 [新属性] [新位置] |
| 删除字段 | alter table 表名 drop 字段名 |
##### 删除表结构
语法：drop table 表名[,表名2...];
数据操作
插入数据
- 向表中指定字段插入数据
基本语法：insert into 表名[(字段列表)] values(对应字段列表) - 向表中所有字段插入数据
基本语法：insert into 表名 values(对应字段列表，必须与表名的字段列表结构一致)
查询操作
查询表中全部数据：select * from 表名;
查询表中部分字段：select 字段列表1[,字段列表2] from 表名;
简单条件查询数据：select 字段列表/* from 表名 where 字段名 = 值;
mysql中没有
==符号，=就代表等于
删除操作
基本语法：delete from 表名 where 条件;
更新操作
更新：将数据进行修改（通常是修改部分字段数据）
基本语法：update 表名 set 字段名 = 新值 where 条件;
修改服务器端变量
语法：set 变量名 = 值;
修改mysql字符集
语法：set names = gbk;
为什么要修改mysql字符集：
mysql有几个变量影响着数据的编码方式
- character_set_client：客户端传输数据时的编码
- character_set_connection
- character_set_server：服务端接受数据时的编码
- character_set_result：呈现结果时的编码
如果这些编码有问题则影响数据的正常显示，我们可以用set names = gbk;直接统一修改这些变量值，也可以单独设置;
列类型（字段类型）
整数类型
| 字段类型 | 保存字节数 | 数据范围 |
|---|---|---|
| tinyint | 1 | 255 |
| smallint | 2 | 65535 |
| mediuint | 3 | 16777215 |
| int | 4 | 4294967295 |
| bigint | 8 | 18446744073709551615 |
注意：这里的数据范围并不是最大能保存的数据，因为mysql默认为整型增加了负数，如tinyint实际能保存的值的范围为（-128，127）
无符号标识设定：
无符号：表示存储的数据在当前字段中没有负数，此时的tinyint就可以取到最大值255
基本语法：在字段类型后加上unsighed
显示长度
当我们用desc查看数据表时，会发现字段类型后面都有一个数字用被括号包着，像int(10)，后面的这个括号就是显示长度。
显示长度和数据位数不一样，它只负责数据显示时的长度，不影响数据本身
如果给字段类型添加了unsigned，则会比正常显示长度少一位，即符号位
为字段添加zerofill属性可以让数据输出时保持最大显示长度，从左侧开始补0；负数不可以使用zerofill，因为只要添加了zerofill，则默认也会添加unsigned
我们也可以手动设置显示长度，如果数据输出长度小于我们设置的显示长度，则向左补零（前提添加了zerofill）；如果大于显示长度则不受影响
小数类型
浮点型
浮点型又称为精度类型，是一种有可能丢失精度的数据类型
浮点型用整型同样的位数却可以保存更大的数据范围，这是因为浮点型有部分位用来保存指数，另一部分用来保存数值
浮点型float的精度只有7位，第8位以后的精度可能会丢失，此时会进行四舍五入，double同理
我们不可以手动插入超出指定整数位数的浮点数，但是如果是超出精度进行四舍五入时系统自动进位导致整数位数增加，则系统自己可以接受
浮点数也可以使用科学记数法来存储数据，输出时会转换成正常数字
| 字段类型 | 保存字节数 | 精度 | 数据范围 | 基本语法 |
|---|---|---|---|---|
| float/单精度类型 | 4 | 7 | 10^38 | float(M,D)：表示一共存储M个有效数字，小数部分占D位，也可以不指定括号内容 |
| double/双精度类型 | 8 | 15 | 10^308 | double(M,D) |
浮点数应用：
- 数量很大并且不是很要求精度的时候，如天上星星的数量
- 在时间或月份前面补0以求美观
定点型
定点数：能够保证数据精确的小数（小数部分可能不精确，超出部分会四舍五入），整数部分一定精确
decimal：定点数，系统自动根据存储的数据来分配内存空间，每大概9个数就会分配4个字节来存储，同时小数和整数部分是分开的
decimal(M,D)：M表示总长度，最大值不能超过65；D代表小数部分长度，最长不能超过30
- 定点数如果小数部分丢失精度进行四舍五入进位导致整数部分也要进位时，系统会报错
定点数应用：数量很大且要求精度（涉及到钱的时候）
时间日期类型
PHP中有着非常强大的时间日期转换函数，date()可以将时间戳转换成想要的格式，strtotime()又可以将很多格式转换成对应的时间戳。PHP通常不需要数据库来帮忙处理这么复杂的时间如期，所以通常配合PHP的时候，时间的保存通常使用时间戳（真正的时间戳，即格林威治时间到现在的总秒数，不是mysql的时间戳），从而用整型来保存
date
日期类型：系统使用三个字节来存储数据，对应的格式为：yyyy-mm-dd，能表示的范围是从1000-01-01到9999-12-12，初始值为0000-00-00
time
时间类型：能够表示某个指定的时间，但是系统同样是提供3个字节来存储，对应的格式为：hh:ii:ss，但是mysql中的time类型能够表示时间范围要大的多，能表示从-838:59:~838:59:59,在mysql中具体的用处是用来描述时间段
因为time类型存储的时间段，当输入时间的时候在前面输入一个数字+一个空格，系统会自动转换成该数字*24个小时，然后再与后面的时间相加，如：
输入5 12:59:59
输出132:59:59
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
timestamp
时间戳类型：mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是yyyy-mm-dd hh:ii:ss
与timestamp同一行的时间被修改的时候，timestamp会自动更新自己的时间
year
年类型：占用一个字节来保存，能表示1990~2155年，但是year有两种视距插入方式：0~99和四位数的具体年
year进行两位数插入时，会判断插入的数字与70的大小关系，如果插入的数字大于等于70，则系统默认插入‘19+输入的数字’；如果插入的数字小于70，则系统默认插入‘20+输入的数字’
char
定长字符：指定长度之后，系统一定会分配指定的空间用于存储数据
基本语法：char(L)，L代表字符数（每个中文和英文都占一个字符），L长度为0到255
varchar
变长字符：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（前提数据没有超出长度）
基本语法：varchar(L)，L代表字符数，L的长度理论值为0-65535
因为varchar要记录数据长度，所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销用来保存数据所占用的空间长度。如果数据本身小于127个字符，就额外开销一个字节；如果大于127个字符，则开销两个字节
char和varchar数据存储对比（UTF8，一个字符都会占用3个字节）
| 存储数据 | char(2) | varchar(2) | char所占字节 | varchar所占字节 |
|---|---|---|---|---|
| a | a | a | 2*3=6 | 1*3+1=4 |
| ab | ab | ab | 2*3=6 | 2*3+1=7 |
char和varchar区别
- char一定会使用指定的空间，varchar是根据数据来定空间
- char的数据查询效率比varchar高：char可以直接找，而varchar需要根据后面的记录数来计算
- 如果确定数据一定是占指定长度，那么使用char类型
- 如果不确定数据到底有多少，那么使用varchar类型
- 如果数据长度超过255个字符，不论是否固定长度，都会用text，不再使用char和vcarchar
text类型
文本类型：本质上mysql提供了两种文本类型
text：存储普通的字符文本
blob：存储二进制文本（图片，文件），一般都不会用blob来存储文件本身，通常是使用一个链接来指向对应的文件
系统提供四种text类型：
| 类型 | 保存字节数 | 实际存储数 |
|---|---|---|
| tinytext | 1 | 2^8+1 |
| text | 2 | 2^16+2 |
| mediumtext | 3 | 2^24+3 |
| longtext | 4 | 2^32+4 |
在选择对应的存储文本的时候，不用可以去选择text类型，系统会自动根据存储的数据长度来选择合适的文本类型
enum
枚举类型：在数据插入之前，先设定几个项，这几个项就是最终可能出现的数据结果
如果确定某个字段的数据只有固定几个值，如性别：男、女，系统就可以在设定字段的时候规定当前字段只能存放固定的几个值，使用枚举
基本语法：enum(数据值1，数据值2...)
系统提供了1-2个字节来存储枚举数据，通过计算enum列举的具体值来选择实际的存储空间，如果数据值列表在255个以内，那么一个字节就够，如果超过255小于65535，那么系统采用两个字节保存
**枚举enum的存储原理：**实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标。当系统设定枚举类型的时候，会给枚举中每个元素定义一个下标，这个下标规则从1开始：enum(1=>'男',2=>'女')
- enum有规范数据的功能，能够保证插入的数据必须是设定的范围，其它类型都不可以
- 在mysql中系统会自动进行数据类型转换，如果碰到±*/符号，系统会自动将数据转换成数值，普通字符串会转换成数值0。通过这个原理我们用enum类型的数据进行+0处理，会发现得到的都是他们的下标值，因此我们在插入数据的时候也可以通过下标值来插入
枚举的意义：
- 规范数据本身，只能插入规定的字符串
- 节省存储空间
Set
集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制：1表示该选项被选中，0表示未被选中
基本语法：set('值1','值2','值3')
系统为set提供了多个字节来进行保存，但是系统会自动计算来选择具体的存储单元：1个字节-set只能有8个选项，2个-16个，3个-24个，4个字节-64个选项
set和enum一样，最终存储到数据字段中的依然是数字而不是真实的字符串
例：
//创建表
create table my_set(
hobby set('篮球','乒乓球','足球')
)charset utf8;
篮球 乒乓球 足球
1 1 1
//插入数据，插入顺序与创建时的顺序无关，系统自动排列成创建时的顺序
insert into my_set values('篮球,足球');
篮球 乒乓球 足球
1 0 1
//所以最终插入的数据是101
- 系统将set列里的数据进行编排，从第一个开始占位，每一个对应一个二进制位
- 插入数据的时候，如果有set列里的数据，则对应的位为1，否则为0
- 最终插入的数据其实就是一个0和1的二进制数，系统在保存的时候会将这个二进制数颠倒过来再转换成10进制保存
set集合的意义：规范数据、节省存储空间
我们可以将enum看成一个单选框，将set看成一个多选框
MySQL记录长度
在mysql中，有一项规定：mysql的记录长度总长度不能超过65535个字节
varchar能够存储的理论值为65535个字符：字符在不同的字符集下可能占用多个字节，在utf8下需要占65535*3个字节，在gbk下需要65535*2个字节，所以varchar并不能存储到理论的最大字符数
varchar除了存储数据本身需要占用空间，还需要额外的空间保存记录长度
- 在utf8下，varchar最多存储21844个字符
- 在gbk下，varchar最多存储32766个字符
因此，如果存储的字符数较多，则需要用text来存储
字段属性
字段属性，又叫列属性，在mysql中一共有6个属性：null，默认值，列描述，主键，唯一键和自动增长
Null属性
Null属性：代表字段为空
如果对应的字段为yes则代表该字段可以为Null
注意：
- 在设计表的时候，尽量不要让数据为空
- mysql的记录长度为65535个字节，如果一个表中有字段允许为NULL，那么系统就会涉及保留一个字节来存储null，最终有效存储长度为65534个字节
默认值
default：当字段被设计的时候，如果允许默认条件下用户可以不进行数据的插入，那么就可以使用事先准备好的数据来填充，通常是Null
//创建数据表
create table my_default(
name varchar(10) NOT NULL, --不能为空
age int default 18 --如果字段在进行数据插入的时候没有提供，则默认插入18
)charset utf8;
//插入数据
insert into my_default(name) values('tom'); --插入name值但不插入age，则系统默认插入18
insert into my_default valuer('jack',default); --在插入数据时直接插入default则插入默认值18
列描述
comment：专门用户给开发人员进行维护的一个注释说明
基本语法：comment '注释内容'
和注释不同，comment需要通过表创建语句来查看
create table my_comment(
name varchar(10) not null comment '用户名不能为空'
)charset utf8;
show create table my_comment; --可以查看comment内容
主键
主键，即主要的键:primary key
在一张表中，有且只有一个字段，里面的值具有唯一性
创建主键
一、随表创建：
- 直接在需要做主键的字段后，增加
primary key属性来确定主键
create table my_pkey(
username varchar(10) primary key
)charset utf8;
- 在所有字段之后增加
primary key选项，primary key(字段名)
create table my_pkey(
username varchar(10),
age int (10),
primary key(username)
)charset utf8;
二、表后增加：
基本语法：
alter table my_pkey add primary key(username);
查看主键
- 查看表结构
通过查看表结构，Key值为PRI的字段即为主键，且主键不允许为空 - 查看表创建语句
删除主键
基本语法：alter table 表名 drop primary key
虽然删除了主键，但创建主键时默认添加的
not null依然保留
复合主键
案例：有一张学生选修课表，一个学生可以选修多个选修课，一个选修课也可以由多个学生来选，但是一个学生在一个选修课中只有一个成绩
基本语法：在添加主键的时候多写一个主键即可，中间用逗号隔开
主键约束
主键一旦增加，那么对应的字段有数据要求
- 当前字段对应的数据不能为空
- 当前字段不能有重复
主键分类
主键根据主键对应字段的业务意义分为两类：
- 业务主键：主键所在的字段具有业务意义（学生ID，课程ID）
- 逻辑主键：自动增长的整型（保证数据唯一性）
自动增长
自动增长：auto increment，当给定某个数值字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增长后，填充数据
通常自动增长用于逻辑主键
原理
- 在系统中有维护一组数据，用来保存当前使用了自动增长的字段，记住当前对应的数据值，再给定一个指定的步长
- 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据
使用自动增长
基本语法：在字段后添加auto increment
修改自动增长
- 查看自增长：自增长一旦触发后，会自动的在表选项中增加一个选项（一张表最多只能拥有一个自增长）
- 表选项可以通过修改表结构来实现：
alter table 表名 auto_increment = 值
删除自动增长
删除自增长，就是在字段属性之后不再保留auto_increment，因为系统在执行语句时如果没有发现auto_increment属性就不会再自动增长
alter my_auto modify id int;
-- id以前有int和auto属性，此时我们只设置一个int属性，也就删除了auto属性。
-- 因为主键会存放到表选项中，所以主键不需要再次设置，否则会二次设置主键，就会报错
初始设置
之所以自增长第一次增加1，第二次增加2，是因为系统中有一组变量用来维护自增长的初始值和步长
我们可以通过show variables like 'auto_increment%';来查看
- auto_increment_offset：初始值
- auto_increment_increment：步长
注意：
- 一张表只有一个自增长，自增长会上升到表选项中
- 如果数据插入中没有触发自增长，那么该字段不会自动增长，但是增长值会初始化到下一个值
- 手动修改自增长值的时候，不能比当前的自增长值小
唯一键
唯一键：unique key，用来保证对应的字段中的数据唯一
主键也可以保证字段唯一性，但一张表只能有一个主键，却可以有多个唯一键，并且字段数据允许为NULL
创建唯一键
创建唯一键与创建主键语法一致：
- 直接在表字段之后增加唯一键标识符：
unique key - 在所有的字段之后使用
unique key(字段列表) - 在创建完表之后也可以增加唯一键：
alter table 表名 add unique key(字段列表)
查看唯一键
可以通过表创建语句查看，又因为唯一键是一个属性，所以也可以通过表结构来查看：
desc my_unique;
Key值为UNI的字段即是唯一键字段，且允许为NULL
唯一键效果：不允许重复插入不为空的数据
在查看表创建语句的时候，会发现唯一键与主键不同的一点：唯一键多出了一个名字（默认为字段名），作用是为了能够删除指定的唯一键
PRIMARY KEY('id'),
UNIQUE KEY 'username' ('username')
删除唯一键
一个表中允许存在多个唯一键，所以我们在删除的时候就不能像删除主键一样，那样会不知道删除哪个唯一键
删除的基本语法：alter table 表名 drop index 唯一键名字;
index关键字：索引，唯一键也是索引的一种（提升查询效率）
修改唯一键
mysql不能直接修改唯一键，必须以先删除，再添加的形式来修改
复合唯一键
唯一键和主键一样可以使用多个字段来共同保证唯一性
一般主键都是单一字段（逻辑主键），而其他需要唯一性的内容都是由唯一键来处理
表关系
表关系：表与表之间（实体）有什么样的关系，每种关系应该如何设计表结构
一对一关系
一对一：一张表中的一条记录与另外一张表中最多有一条明确的关系。
通常此设计方案保证两张表中使用同样的主键即可
案例：
学生表：
| 学生ID（PRI） | 姓名 | 年龄 | 性别 | 住址 | 身高 | 体重 |
|---|---|---|---|---|---|---|
表的使用过程中，常用的信息如学生ID，姓名需要经常查询，而其他不常用的信息偶尔才会用到，如果我们每次都要查询整张表会增加系统负担
解决方案：将表拆分为两张表：常用表和不常用表
常用表：
| 学生ID（PRI） | 姓名 | 年龄 | 性别 |
|---|---|---|---|
不常用表：
| 学生ID（PRI） | 住址 | 身高 | 体重 |
|---|---|---|---|
如果不常用表没有学生ID，那么即使知道了常用表的学生ID也无法查询不常用表的信息，所以要以同样的学生ID来指向数据。同时两张表都只有一个学生ID，并且两张表合起来就是一张完整的表，这样的两张表就是一对一关系。
一对多关系
一对多，通常也叫做多对一的关系。通常一对多的关系设计方案，在多关系的表中去维护一个字段，这个字段是一关系的主键
father表：
| father_id | name | age |
|---|---|---|
| f1 |
son表：
| son_id | name | age | father |
|---|---|---|---|
| s1 | f1 | ||
| s2 | f1 |
我们可以通过son表中的father字段查找到每个son的父亲；也可以通过son表中father找到父亲f1的所有son
我们一般不向father表中添加son字段来表示f1的儿子，因为一个f1可能有多个儿子，在进行查询的时候还需要将这些儿子字段分开，比较麻烦。所以我们一般只在多表中去维护一表的关系
多对多关系
多对多：一张表中的一条记录在另外一张表中可以匹配到多条记录，反过来也一样
多对多的关系如果按照多对一的关系维护，就会出现一个字段中有多个其他表的主键，在访问的时候就会带来不便
既然通过两张表自己增加字段解决不了问题，那么就通过第三张表来解决
师生关系案例：一个老师要教多个学生/一个学生要上多个老师的课
教师表：
| t_id | name | age | gender |
|---|---|---|---|
| t1 | 王老师 | 30 | 女 |
| t2 | 张老师 | 35 | 男 |
学生表：
| s_id | name | age | gender |
|---|---|---|---|
| s1 | 小宋 | 21 | 男 |
| s2 | 小杨 | 21 | 女 |
| s3 | 小新 | 20 | 女 |
此时我们需要设计一张中间表来维护两张表对应的联系：每一种联系都要包含
中间表：
| ts_id | t_id | s_id |
|---|---|---|
| 1 | t1 | s1 |
| 2 | t1 | s2 |
| 3 | t1 | s3 |
| 4 | t2 | s1 |
| 5 | t2 | s2 |
多对多的解决方案：增加一个中间表，让中间表与另外两个表形成两个多对一的关系：多对一的解决方案就是在多表中增加一表对应的主键字段
高级数据操作
新增数据
多数据插入
只需要写一次insert指令，但是可以直接插入多条记录
基本语法：insert into 表名 [(字段列表)] values(值列表),(值列表)...;
主键冲突
主键冲突，在有的表中，使用的是业务主键（字段有业务含义），但是往往在进行数据插入的时候，又不确定数据表中是否已经存在对应的主键，此时如果重复插入主键字段则会产生冲突并报错
解决方案：
- 主键冲突更新
类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法：
insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值; - 主键冲突替换
如果需要更新的数据过多，则不适合用冲突更新的方法，需要用冲突替换：当主键冲突之后，直接用新的数据替换掉原来的数据，语法：
replace into 表名 [(字段列表)] values(值列表),(值列表)...;
虽然用replace要比insert方便，但是效率要低并且资源占用大
蠕虫复制
蠕虫复制：一分为二，二分为四，成倍增加。从已有的数据中获取数据，并且将获取到的数据插入到数据表中
基本语法：insert into 表名 [(字段列表)] select */字段列表 from 表;
注意：
- 蠕虫复制的确是重复数据，没有太大业务意义，但可以在短期内快速增加表的数据量从而测试表的压力和效率（索引）
- 我们也可以通过蠕虫复制来复制一张表的数据到另一种表中
- 要注意主键冲突
更新数据
- 在更新数据的时候，特别要注意：一定是跟随条件更新：
update 表名 set 字段名 = 新值 where 判断条件; - 如果没有条件，则是更新全部表的数据，但可以使用
limit来设置需要更新的数量：update 表名 set 字段名 = 新值 [where 判断条件] limit 数量;
删除数据
语法：delete from 表名 [where 条件];
- 删除数据的时候尽量不要全部删除，应该使用where进行判定
- 删除数据的时候可以使用limit来限制要删除的具体数量
delete删除数据的时候无法重置auto_increment,但可以使用truncate 表名;语句来重置表内的自增长。该语句的本质是先drop掉该表，再create一个表
查询数据
完整的查询指令：
select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制
解释：
1. select选项：
| key | value1 | value2 |
|---|---|---|
| select选项 | all | distinct |
| 系统该如何对待查询得到的记录 | 默认，保存所有记录 | 去除重复记录，仅保存一条 |
2. 字段列表：
有时需要从多张表获取数据，在获取数据的时候，可能存在不同表中有同名的字段，需要将同名的字段命名成不同名的：别名alias
基本语法：字段名 [as] 别名
3. from数据源：
from是为前面的查询提供数据：数据源只要是一个符合二维表结构的数据即可
| 类型 | 语法 | 解释 |
|---|---|---|
| 单表数据 | from 表名 | 将一张表作为数据源来查询数据 |
| 多表数据 | from 表1,表2… | 两张表的记录数相乘，字段数拼接：从第一张表取出一条记录，去拼凑第二张表的所有记录，保留所有结果。得到的结果在数学上有一个专业的说法-笛卡尔积，这个结果除了给数据库造成压力，没有其他意义，应该尽量避免出现笛卡尔积 |
| 动态数据 | from (select 字段列表 from 表) [as] 别名 | from后面跟的不是一个实体表，而是一个从表中查询出来得到的二维结果表 |
4. where子句：
where子句：用来从数据表获取数据的时候，进行条件筛选
数据获取原理：针对表去对应的磁盘处获取所有的记录（一条条），where的作用就是在拿到一条结果就开始进行判断，判断时候符合条件：如果符合就保存下来，如果不符合就直接舍弃（不放到内存中）
where是通过运算符进行结果比较来判断数据的
5. group by子句：
group by表示分组的含义：根据指定的字段，将数据进行分组，分组的目标是为了统计
分组统计：
基本语法：group by 字段名;
group by是为了分组后进行数据统计的，如果只是想看数据显示，那么group by没有什么含义。group by将数据按照指定的字段分组之后，只会保留每组的第一条记录
想要查看统计数据需要用到一些统计函数（聚合函数）：
- count():统计每组中的数量，如果统计目标是字段，那么不统计空NULL字段；如果为*，那么代表统计记录
- avg():求平均值
- sum():求和
- max():求最大值
- min():求最小值
- group_concat():将分组中指定的字段进行合并（字符串拼接）
-- 使用聚合函数：按照班级统计每班人数，最大年龄，最矮身高和平均年龄
select class_id,count(*),max(stu_age),min(stu_height),avg(stu_age) from my_stydent group by class_id;
多分组：
将数据按照某个字段进行分组之后，对已经分组的数据进行再次分组
基本语法：group by 字段1,字段2
先按照字段1进行排序，之后将结果再按照字段2进行排序，以此类推…
分组排序：
mysql中，分组默认有排序的功能：按照分组字段进行排序，默认是升序
基本语法：group by 字段 [asc|desc]
回溯统计：
当分组进行多分组之后，往上统计的过程中需要进行层层上报，将这种层层上报统计的过程称之为回溯统计：每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为NULL
基本语法：group by 字段 with rollup
6. having子句：
having的本质和where一样，是用来进行数据条件筛选
having是在group by子句之后，可以针对分组数据进行统计筛选，但是where不行
where不能使用聚合函数：聚合函数是用在group by分组的时候，此时where已经运行完毕
having在group分组之后，可以使用聚合函数或者字段别名。而where是从磁盘中取出数据，别名是在数据进入到内存之后才有的
having是在group by之后，group by是在where之后：where是将数据从磁盘拿到内存，where之后的所有操作都是内存操作
7. order by子句：
order by排序：根据校对规则对数据进行排序
基本语法：order by 字段 [asc|desc]
order by也可以像group by一样进行多字段排序：先按照第一个字段进行排序，然后再按照第二个字段进行排序：order by 字段1 [asc|desc],字段2 [asc|desc]
8. limit子句：
limit限制子句：主要是用来限制记录数量的获取，获取的记录数越小，服务器的压力也就越小。也可以对数据的操作进行限制
记录数限制：
纯粹的限制获取的数量：从第一条到指定的数量
基本语法：limit 数量
分页：
利用limit来限制获取指定区间的数据
基本语法：limit offset,length
- offset:从哪开始
- length：获取多少条
mysql中记录的数量从0开始，如limit 0,2表示获取前两条记录
如果剩余的数据不够获取了，则只获取到最后一条数据
查询中的运算符
算数运算符
+、-、*、/、%
基本算数运算，通常不在条件中使用，而是用于结果运算(select字段中)
注意：
- 在mysql中除法的运算结果是以浮点数来表示
- 除法运算或模运算中如果除数为0，那么结果返回NULL
- 对NULL进行任何算数运算，结果都为NULL
比较运算符
>、>=、<、<=、=、<>
-
通常是用来在条件中进行限定结果
=：在mysql中，没有对应的==比较符号，就是使用=来进行相等判断
<=>：相等比较 -
特殊应用：就是在字段结果中进行比较运算
-- mysql中没有规定select必须有数据表
-- 如果这里直接用=来比较，会被判定为赋值符号，所以要加尖括号
select '1' <=> 1,0.02 <=> 0;
输出1和0
在mysql中没有bool值，1即代表true，0代表false
在mysql中，数据会先自动转换成同类型再比较，所以'1' = 1;
- 在条件判断的时候，还有对应的比较运算符，计算区间
语法：between 条件1 and 条件2;
between中的条件1必须小于条件2，并且是闭区间查找-查找范围包括条件1和条件2
逻辑运算符
and：逻辑与
or：逻辑或
not：逻辑非
举例：
select * from my_student where stu_age >=20 and sty_age <=30;
in运算符
in：是用来替代=，当结果不是一个值，而是一个结果集的时候
基本语法：in(结果1,结果2,结果3...)
只要当前条件在结果集里出现过，那么就成立
is运算符
is是专门用来判断字符是否为NULL的运算符
基本语法：is null/is not null
like运算符
like运算符：是用来进行模糊匹配（字符串）
基本语法：like '匹配模式';
匹配模式中，有两种占位符：
_：匹配对应的单个字符
%：匹配多个字符
-- 获取所有姓张的同学
select * from my_stydent where stu_name like '张%';
联合查询
基本概念
联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，使用谓语为UNION或UNION ALL
联合查询：将多个查询的结果合并到一起（纵向合并）：字段数不变，多个查询的记录数合并
应用场景
- 将同一张表中不同的结果（需要对应多条查询语句来实现），合并到一起展示数据，如：男生身高升序排序，女生身高降序排序
- 最常见：在数据量大的情况下，会对表进行分表操作，需要对每张表进行数据统计，使用联合查询来将数据存放到一起显示，如：
QQ1表获取在线数据，QQ2表也获取在线数据：将所有的在线数据显示出来
基本语法
select 语句
union [union选项]
select 语句;
union选项：
distinct：去重，去掉完全重复的数据（默认）
all：保存所有的结果
注意：union理论上只要保证字段数一样，不需要每次拿到的数据对应的字段类型一致，只保留第一个select选择的字段名
order by的使用
在联合查询中，如果要使用order by，那么对应的select语句必须用括号扩起来，同时必须配合limit来使用（且limit后要跟限制数量，通常可以使用一个较大的值，大于对应表的记录数），不然order by不会生效！
举例：
(select * from my_student where gender = '男' order by stu_height asc limit 10)
union
(select * from my_student where gender = '女' order by stu_height desc limit 10);
连接查询
连接查询：将多张表连到一起进行查询（会导致记录数行和字段数列发生改变）
##### 连接查询的意义
在关系型数据库设计过程中，实体（表）与实体之间是存在很多联系的。在关系型的数据表的设计过程中，遵循着关系来设计：一对一，一对多和多对多，通常在实际操作的过程中，需要利用这层关系来保证数据的完整性
连接查询分类
连接查询一共有以下几类：
- 交叉连接
交叉连接：将两张表的数据与另外一张表彼此交叉
原理：
① 从第一张表依次取出每一条记录
② 取出每一条记录之后，与另外一张表的全部记录挨个匹配
③ 没有任何匹配条件，所有的结果都会进行保留
④ 记录数 = 第一张表记录数 * 第二张表记录数；字段数 = 第一张表字段数 + 第二张表字段数（笛卡尔积）
语法：
表1 cross join 表2
应用：
交叉连接产生的结果是笛卡尔积，没有实际应用
本质：from 表1,表2; - 内连接
内连接：inner join，从一张表中取出所有的记录去另外一张表中匹配，利用匹配条件进行匹配，成功了则保留，失败了则放弃
原理：
① 从第一张表中取出一条揭露，然后去另外一张表中进行匹配
② 利用匹配条件进行匹配：匹配到则保留，并继续向下匹配；匹配失败则向下继续匹配，如果全表匹配失败则结束
语法：
表1 [inner] join 表2 on 匹配条件;
内连接匹配的时候必须数据保证匹配到才会保存
应用：
内连接通常是在对数据有精确要求的地方使用：必须保证两张表中都能进行数据匹配 - 外连接
外连接：outer join，按照某一张表作为主表（表中所有记录在最后都会保留），根据条件去链接另外一张表，从而得到目标数据
外连接分为两种：左外连接（left join），右外连接（right join）
左连接：左表为主表
右链接：右表为主表
原理：
① 确定连接主表：左连接就是left join左边的表为主表，right join就是右边为主表
② 拿主表的每一条记录，去匹配另外一张表（从表）的每一条记录
③ 如果满足匹配条件则保留，不满足则不保留
④ 如果主表记录在从表中一条都没有匹配成功，那么也要保留该记录：从表对应的字段值都为NULL
语法：
左连接：主表 left join 从表 on 连接条件
右连接：主表 right join 从表 on 连接条件
左连接对应的主表数据在左边，右连接对应的主表数据在右边
特性：
① 外连接中，主表数据记录一定会保存：如果没有匹配到从表数据则为NULL，连接之后不会出现记录数少于主表（而内连接有可能）
②左连接和右连接可以互相转换，但是数据对应的位置（表顺序）会改变
应用：
外连接是非常常用的一种获取数据的方式：作为获取对应主表数据以及其他关联数据 - using关键字
是在连接查询中用来代替对应的on关键字的，用来进行条件匹配
原理：
① 在连接查询时，使用on的地方用using代替
② 使用using的前提是对应的两张表连接的字段是同名（类似自然连接会自动匹配）
③ 如果使用using关键字，那么对应的同名字段，最终在结果中只会保留一个
语法:
表1 [inner,left,right] join 表2 using(同名字段列表);
举例：
select * from my_student left join my_class on my_student.class_id = my_class.class_id;
-- 将on替换为using：
select * from my_student left join my_class using(class_id);
子查询
子查询（sub query）是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块，当一个查询是另一个查询的条件时，称之为子查询
子查询：指在一条select语句中，嵌入了另外一条select语句，那么被嵌入的select语句称之为子查询语句
主查询
主查询，主要的查询对象，第一条select语句，确定用户所有获取数据的目标（数据源），已经要具体得到的字段信息
##### 子查询和主查询的关系
- 子查询是嵌入到主查询中的
- 子查询是辅助主查询的，要么作为条件，要么作为数据源
- 子查询其实可以独立存在，它是一条完整的select语句
子查询分类
##### 按功能分
- 标量子查询：子查询返回的结果是一个数据（一行一列）
- 列子查询：返回的结果是一列（一列多行）
- 行子查询：返回的结果是一行（一行多列）
- 表子查询：返回的结果是多行多列
- Exists子查询：返回的结果是0或1（类似于布尔操作）
按位置分
- where子查询：子查询出现的位置在where条件中
- from子查询：子查询出现的位置在from的数据源中（作数据源）
标量子查询
概念
标量子查询：子查询得到的结果是一个数据（一行一列）
语法
基本语法：select * from 数据源 where 条件判断 =/<> (select 字段名 from 数据源 where 条件判断);
列子查询
概念
列子查询：子查询得到的结果是一列数据（一列多行）
语法
基本语法：主查询 where 条件 in (列子查询);
行子查询
概念
行子查询：子查询返回的结果是一行多列
行元素
行元素：字段元素是指一个字段对应的值，行元素对应的就是多个字段，多个字段合起来作为一个元素参与运算，把这种情况称为行元素
语法
基本语法：主查询 where 条件[(构造一个行元素)] = (行子查询);
表子查询
概念
表子查询：子查询返回的结果是多行多列，表子查询与行子查询非常相似，只是行子查询需要产生行元素，而表子查询没有
行子查询是用于wjere子查询，而表子查询用于from子查询
语法
基本语法：
select 字段表 from (表子查询) as 别名 [其他条件或操作];
Exists子查询
概念
Exists子查询：查询返回的结果只有0或1，1代表成立，0代表不成立
语法
基本语法：where exists(查询语句);
exists就是根据查询得到的结果进行判断，若结果存在那么返回1，否则返回0
子查询中的关键字
1. in
主查询 where 条件 in (列子查询);
2. any
任意一个
= any(列子查询)：条件在查询结果中有任意一个匹配即可，等价于in
<>any(列子查询)：不等于any，条件在查询结果中不等于任意一个
1 = any(1,2,3) --true
1 <> any(1,2,3) --true
3. some
与any完全一样，只是not any 和not some在英文含义上不一样，为了区分开来而增加了some，用哪个都一样
4. all
= all(列子查询)：等于里面所有
<> all(列子查询)：不等于里面所有
如果对应的匹配字段有NULL，那么不参与匹配
整库数据备份与还原
整库数据备份也叫SQL数据备份，备份的结果都是SQL指令
在MySQL中提供了一个专门用于备份SQL的客户端：mysqldump.exe
数据备份
应用场景
SQL备份是一种mysql非常常见的备份与还原方式，SQL备份不只是备份数据，还备份对应的SQL指令（表结构），即使是数据库遭到攻击或毁灭性的破坏（数据库被删），那么利用SQL备份依然可以实现数据还原
SQL备份因为需要备份结构，因此产生的备份文件特别大，不适合特大型数据备份，也不适合数据变化频繁型数据库备份
应用方案
SQL备份用到的是专门的备份客户端，因此还没雨数据库服务器进行连接
基本语法：mysqldump/mysqldump.exe -hPup 数据库名字 [表1 [表2...]] > 备份文件地址
-hPup：-hlocalhost -P3306 -uroot -proot
备份可以有三种形式：
- 整库备份（只需要提供数据库名字）
- 单表备份，数据库后面跟一张表
- 多表备份，数据库后面跟多张表
数据还原
mysqldump备份的数据中没有关于数据库本身的操作，都是针对表级别的操作：当进行数据SQL还原时，必须指定数据库
数据还原的三种方式
- 利用mysql.exe客户端：没有登陆之前，可以直接用该客户端进行数据还原
mysql.exe -hPup 数据库 < 文件位置 - 在SQL指令中提供了一种导入SQL指令的语句
source SQL 文件位置;//必须先进入到对应的数据库 - 人为操作：打开备份文件，复制所有SQL指令，然后到mysql.exe客户端中去粘贴执行
用户权限管理
用户权限管理：在不同的项目中给不同的角色（开发者）不同的操作权限，为了保证数据库的安全
用户管理
mysql需要客户端进行连接认证才能进行服务器操作，因为需要用户信息，mysql中所有的用户信息都是保存在mysql数据库下的user表中
默认的，在安装mysql的时候，如果不选择创建匿名用户，那么意味着所有的用户只有一个root超级用户
在mysql中，对用户的管理是由对应的Host和User共同组成主键来区分用户
- User：道标用户的用户名
- Host：代表本质是允许访问的客户端（IP或者主机地址），如果host使用
%则代表所有的用户（客户端）都可以访问
创建用户
理论上讲可以采用两种方式创建用户：
- 直接使用root用户在mysql.user表中插入记录（不推荐）
- 专门创建用户的SQL指令：
基本语法：create user 用户 identified by '明文密码';
用户：用户名@主机地址
主机地址：''或'%'
--创建用户
create user 'user1'@'%' identified by '123456';
--简化创建用户，没有密码，谁都可以登录
create user user2;
删除用户
基本语法：drop user 用户名@host;
修改用户密码
mysql中提供了多种修改密码的方式，基本上都必须使用对应提供的一个系统函数：password()，需要靠该函数对密码进行加密处理
- 使用专门的修改密码的指令：
set password for 用户 = password('新密码'); - 使用更新语句update来修改表：
update mysql.user set password = password('新密码') where user = '' and host = '';
权限管理
在mysql中将权限管理分为三类
- 数据权限：增删改查(select/update/delete/insert)
- 结构权限：结构操作（creat/drop）
- 管理权限，权限管理（create user/grant/revoke）
授予权限：grant
将权限分配给指定的用户
基本语法：grant 权限列表 on 数据库/* . 表名/* to 用户;
权限列表：使用逗号分隔，但是可以使用all privileges代表全部权限
数据库.表名：可以是单表（数据库名字.表名），可以是具体某个数据库（数据库.*），也可以整库（*.*）
权限回收：revoke
权限回收：将权限从用户手中收回
基本语法：revoke 权限列表/all privileges on 数据库/* . 表/* from 用户;
权限刷新：flush
刷新权限：将当前对用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中
基本语法：flush privileges;
密码丢失的解决方案
如果忘记了root用户密码，就需要去找回或者重置root用户密码
① 停止mysql服务
② 重新启动服务 mysqld.exe --skip-grant-tables
③ 当前启动的服务器没有权限概念，非常危险，任何客户端，不需要任何用户信息都可以直接登陆，而且是root权限：新开客户端，使用mysql.exe就能登陆
④ 修改root用户的密码，指定用户名@host
⑤ 赶紧关闭服务器，重启服务
外键
概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另外一个关系的外键。由此可见，外键表示了两个关系之间的相互信任，以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表，外键又称作外关键字
外键：foreign key
假设一张表（A）中有一个字段，保存的值指向另外一张表（B）的主键，
那么B为主表，A为从表
外键的操作
增加外键
注意外键名是用反引号包括：ESC下面的键
- 在创建表的时候增加外键（类似主键）
基本语法：在字段之后增加一条语句：[constraint `外键名`] foreign key(外键字段) references 主表(主键);
创建后的外键字段的key属性为mul，即多索引。外键本身是一个索引，外键要求外键字段本身也是一种普通索引
- 在创建表后增加外键
alter table 从表 add [constraint `外键名`]foreign key(外键字段) references 主表(主键);
修改外键
外键不允许修改，只能先删除后增加
基本语法：alter table 从表 drop foreign key 外键名字;
外键的基本要求
- 外键字段需要保证与关联的主表的关键字段类型完全一致
- 基本属性也要相同
- 如果是在表后增加外键，对数据还有一定的要求（从表数据和主表的关联关系）
- 外键只能使用innodb存储引擎，不支持myisam
外键约束
外键约束：通过建立外键关系之后，对主表和从表都会有一定的数据约束
约束的基本概念
- 当一个外键产生时，外键所在的表（从表）会受制于主表数据的存在从而导致数据不能进行某些不符合规范的操作（不能插入主表不存在的数据）
- 如果一张表被其他表外键引入，那么该表的数据操作就不能随意：必须保证从表数据的有效性（不能随便删除一个被从表引入的记录）
约束模式
可以在创建外键的时候，对外键约束进行选择性的操作
基本语法：add foreign key(外键字段) references 主表(主键) on 约束模式;
约束模式有三种：
- district：严格模式，默认的，不允许操作
- cascade：级联模式，一起操作，主表变化，从表数据跟着变化
- set nyll：置空模式，主表变化（删除），从表对应记录设置为空，但前提是从表中对应的外键字段允许为空
外键约束主要约束的对象还是主表操作，从表只是不能插入主表不存在的数据
通常在进行约束的时候，需要指定操作：update和delete
常用的约束模式：on update cascade / on delete set null，更新级联 / 删除置空
约束的作用
保证数据的完整性：主表与从表的数据要一致
正是因为外键有很强大的数据约束作用，而且可能导致数据在后台变化的不可控，导致程序在进行设计开发逻辑的时候，没有办法去很好的把握数据（业务），所以外键比较少使用
视图
创建视图
视图的本质是SQL指令（select 语句）
基本语法：create view 视图名字 as select 指令;
可以是单表数据，也可以是连接查询，联合查询或子查询
查看视图
查看视图结构：视图本身是虚拟表，所以关于表的一些操作都使用与视图
基本语法：show tables/show create table[view]/desc 视图名字;
使用视图
视图是一张虚拟表，可以直接把数图当做“表”操作，但是视图本身没有数据，是临时执行select语句得到对应的结果。视图主要用于查询操作
基本语法：select 字段列表 from 视图名字 [子句];
修改视图
修改视图：本质是修改视图对应的查询语句
基本语法：alter view 视图名字 as 新select指令;
删除视图
基本语法：drop view 视图名字;
事务安全
事务概念
事务（transaction）是访问并可能更新数据库中各种数据项的一个程序执行单元（unit）。事务通常由高级数据库操纵语言或编程语言书写的用户程序的执行所引起。事务由事务开始（begin transaction）和事务解释（end transaction）之间执行的全体操作组成
事务基本原理
基本原理：mysql允许将事物统一进行管理（前提存储引擎innode），将用户所做的操作，暂时保存起来，不直接放到数据表里（更新），等到用于确认结果之后再进行操作
自动事务
自动事务：autocommit，当客户端发送一条SQL指令（写操作：增删改）给服务器的时候，服务器在执行之后，不用等待用户反馈结果，会自动将结果同步到数据表
证明： 我们打开两个客户端，一个用来执行SQL命令，一个用来查看结果
当执行SQL命令的时候，另一个客户端也更新了数据，这就属于自动事务，这是因为系统做了额外的步骤来帮助用户操作，这是通过autocommit变量来控制的，我们可以通过show variables like 'autocommit%';来查看.
我们可以关闭自动事务，这样系统就不再帮助用户提交数据了：set autocommit = off
一旦自动事务关闭，那么需要用户提供是否同步的命令：
commit：提交（同步到数据表，同时清空事务）
rollback：回滚（清空之前的操作）
在没有提交的时候，我们同时查看两个客户端数据，发现执行SQL指令的客户端依然可以看到数据，而另一个则不能，这是因为SQL执行的客户端在查看数据时系统会自动根据日志中的操作进行数据加工
通常，我们不会关闭自动事务，这样操作太麻烦。因此只会在需要使用事务处理的时候，才会进行操作（手动事务）
手动事务
不管是开始还是过程还是结束都需要用户手动的发送事务操作指令来实现
手动事务对应的命令：
start transaction;开启事务，从这条语句开始，后面的所有语句都不会直接写入到数据表（保存在事务日志中）- 事务处理：多个写指令构成
- 事务提交：commit/rollback，到这个时候所有的事务才算结束
开启事务
语法：start transaction;
执行事务
将多个连续的但是是一个整体的SQL指令逐一执行
① 事务操作：新增数据
② 事务操作：更新数据
此时客户端无法查看到数据
提交事务
确认提交：commit，提交后可以在客户端查看到数据
回滚操作：rollback
回滚点
回滚点：savepoint：当有一系列的事务操作时，而其中的步骤如果成功了，没有必要重新来过，可以在某个成功点设置一个记号（回滚点），然后如果后面有失败，那么可以回到这个记号位置
增加回滚点：savepoint 回滚点名字;
回到回滚点：rollback to 回滚点名字;//回滚点之后的操作全部清除
事务特性
事务具有4个特性：①原子性②一致性③隔离性④持久性，这四个特性称为ACID特性
- 原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸多操作要么都做，要么都不做。
从开始事务到提交事务，要么所有操作都成功，要么所有操作都失败 - 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态，一致性与原子性是密切相关的。
数据表中的数据修改，要么是所有操作一次性修改，要么是根本不改 - 隔离性（isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
如果一个客户端在使用事务操作某些数据的时候，另外一个客户端不能对该数据进行操作 - 持久性（durability）：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响
变量
mysql本质是一种编程语言，需要很多变量来保存数据。mysql中很多的属性控制都是通过mysql中固有的变量来实现的
系统变量
系统内部定义的变量，系统变量针对所有的用户（mysql客户端）有效
查看系统所有变量：show variables [like 'pattern'];
mysql允许用户使用select查询系统变量的数据值
基本语法：select @@系统变量名;
修改系统变量：
- 局部修改（会话级别）
只针对当前自己客户端的当次连接有效
基本语法：set 变量名 = 新值; - 全局修改
针对所有的客户端，“所有时刻”都有效
基本语法：set global 变量名 = 值;||set @@global.变量名 = 值;
全局修改之后，所有正在连接的客户端并没有发生改变，这是因为全局修改只针对之后新开的客户端有效，如果想要正在连接的客户端生效，只能使用局部修改
会话变量
会话变量也称之为用户变量，会话变量跟mysql客户端是绑定的，设置的变量只对当前用户使用的客户端生效
定义用户变量：set @变量名 = 值;
在mysql中没有比较符号==，是用=符号代替的。有时候在赋值的时候会报错，mysql为了避免系统分不清赋值和比较符号，特增加了一个变量的赋值符号：:=
mysql是专门存储数据的，允许将数据从表中取出存储到变量中。查询得到的数据必须只能是一行数据（一个变量对应一个字段），因为mysql没有数组
1.赋值且查看赋值过程：select @变量1 := 字段1,@变量2 := 字段2 from 数据表 where 条件;
2.只复制，不看过程：select 字段1,字段2... from 数据源 where 条件 into @变量1,@变量2...;
查看自定义变量：select @变量1,@变量2...;
局部变量
作用范围在begin到end语句块之间，在该语句块里设置的变量，declare语句专门用于定义局部变量
- 局部变量是用
declare关键字来声明 - 局部变量declare语句出现的位置一定是在begin和end之间（begin end是在大型语句块中使用：函数/存储过程/触发器）
声明语法：declare 变量名 数据类型 [属性];
流程结构
流程结构：代码的执行顺序
if分支
基本语法：
- 用在select查询中，当做条件来进行判断：
if(条件，为真的结果，为假的结果) - 用在复杂的语句块中（函数/存储过程/触发器）
if 条件表达式 then
满足条件要执行的语句;
end if;
复合语法：
if 条件表达式 then
满足条件要执行的语句;
else
不满足条件要执行的语句;
end if;
while循环
循环体都是需要在大型代码块中使用
基本语法：
while 条件 do
循环执行的代码;
end while;
结构标识符
结构标识符：为某些特定的结构进行命名，然后为的是在某些地方使用名字
基本语法：
标识名字:while 条件 do
循环体
end while[标识名字];
标识符的存在只要是为了循环体中使用循环控制。在mysql中没有continue和break，由自己的关键字替代：
lterate：迭代，就是以下的代码不执行，重新开始循环（continue）
leave：离开，整个循环终止（break）
举例：
标识名字:while 条件 do
if 条件判断 then
循环控制;
lterate/leave 标识名字;
end if;
循环体
end while[标识名字];
函数
在mysql中，函数分为两类：系统函数（内置函数）和自定义函数
不管是内置函数还是用户自定义函数，都是使用select 函数名(参数列表);
内置函数
字符串函数
char_length()：判断字符串的字符数
length()：判断字符串的字节数（与字符集）
concat()：连接字符串
instr()：判断字符在目标字符串中是否存在，存在则返回其位置，不存在返回0
lcase()：全部小写
left()：从左侧指定位置开始截取字符串
ltrim()：消除左边对应的空格
mid()：从中间指定位置开始截取，如果不指定截取长度，则直接截到最后
注意：在mysql中字符串的下标是从1开始
时间函数
now()：返回当前时间（日期+时间）
curdate()：返回当前日期
curtime()：返回当前时间
unix_timestamp()：获取时间戳，从格林威治时间算起
from_unixtime()：将指定时间戳转换为对应的时间日期格式
datediff()：判断两个日期之间的天数差距
date_add(日期，interval 时间数字 day/hour/minute/second)：进行时间的增加
日期做参数必须使用字符串形式：'2020-02-02’
数学函数
abs()：绝对值
ceiling()：向上取整
floor()：向下取整
pow()：求指数，谁的多少次方
rand()：获取一个随机数（0-1之间）
round()：四舍五入函数
其他函数
md5():对数据进行md5加密（mysql中的md5与其他任何地方的md5加密出来的内容是完全相同的）
version()：获取版本号
database()：显示当前所在数据库
uuid()：生成一个唯一标识符（自增长），自增长是单表唯一，UUID是整库（数据唯一的同时空间也唯一）
自定义函数
自定义函数：用户自己定义的函数
函数：实现某种功能的语句块（由多条语句组成）
- 函数内部的每条指令都是一个独立的个体，需要符合语句定义规范，即需要使用语句结束符的分号
- 函数是一个整体，而且函数在调用的时候才会执行，那么当设计函数的时候，意味着整体不能被中断
- mysql一旦见到语句结束符分号，就会自动开始执行
解决方案：在定义函数之前，尝试修改临时的语句结束符
基本语法：delimiter 新符号/$$
中间为正常SQL指令：使用分号结束（系统不会执行，因为不认识分号），使用新符号结束，修改回语句结束符：delimiter ;
创建函数
delimiter $$
create function 函数名(形参 形参数据类型) returns 返回值类型
begin
函数体
return 返回值数据; --需要与结构中定义的返回值类型一致
end
$$
delimiter ;
如果函数体只有一行，那么可以省略begin和end，也不需要修改结束符
查看函数
- 查看function状态，查看所有函数
show function status [like 'pattern']; - 查看函数创建语句
show create function 函数名字;
调用函数
自定义函数调用与内置函数调用一样：select 函数名(实参列表);
删除函数
删除函数：drop function 函数名;
注意事项
- 自定义函数是属于用户界别的，只有当前客户端对应的数据库中可以使用
- 可以在不同的数据库下看到对应的函数，但是不可以调用
- 函数必须规范返回值，那么在函数内部不能使用select指令，因为select一旦执行就会得到一个结果集（result set），但是
select 字段 into@变量;是唯一可用的select语句
变量作用域
变量作用域，变量能够使用的区域范围
局部作用域
使用declare关键字声明（在结构体内：函数/存储过程/触发器），而且只能在结构体内部使用
declare关键字声明的变量没有任何符号修饰，就是普通字符串，如果在外部访问该变量，系统会认为是字段
会话作用域
用户定义的，使用@符号定义的变量，使用set关键字
会话作用域：在当前用户当次连接有效，只要在本连接之中，任何地方都可以使用（可以在结构内部，也可以跨库）
全局作用域
所有的客户端所有的连接都有效，需要使用全局符号来定义
set global 变量名 = 值;
set @@global.变量名 = 值;
通常，在SQL编程的时候，不会使用自定义变量来控制全局，一般都是定义会话变量或者在结构中使用局部变量来解决问题
存储过程
概念
存储过程（stored procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译（效率较高），用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行他。存储过程是SQL数据库中的一个重要对象
与函数的区别
相同点
- 存储过程和函数目的都是为了可重复地执行操作数据库的SQL语句的集合
- 存储过程函数都是一次编译，后续执行
不同点
- 标识符不同，函数的标识符为FUNCTION，过程为PROCEDURE
- 函数中有返回值且必须返回，而过程没有返回值
- 过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在select中，必须将返回值赋给变量
- 函数可以在select语句中直接使用，而过程不能：因为函数是使用select调用，而过程不是
存储过程操作
创建过程
create procedure 过程名字([参数列表])
begin
过程体
end
结束符
-- 如果过程体只有一条语句，那么可以省略begin和end
-- 过程基本可以实现所有函数能够实现的功能
查看过程
查看过程与查看函数完全一样，除了关键字
查看全部存储过程：show procedure status [like 'pattern'];
查看过程创建语句：show create procedure 过程名字;
调用过程
过程：没有返回值，select不能调用
调用过程有专门的语法：call 过程名([实参列表]);
删除过程
语法：drop procedure 过程名字;
存储过程的形参类型
存储过程也允许提供参数（形参和实参），存储的参数也和函数一样，需要指定其类型
但是存储过程对参数还有额外的要求：自己的参数分类
IN
表示参数从外部传入到里面使用（过程内部使用），可以是直接的数据也可以是保存数据的变量
OUT
表示参数是从过程里面把数据保存到变量中，交给外部使用，传入的必须是变量
如果说传入的out变量本身在外部有数据，那么进入过程之后，第一件事就是被清空，设为NULL
INOUT
数据可以从外部传入到过程内部使用，同时内部操作之后，又会将数据返还给外部
触发器
概念
触发器是一种特殊类型的存储过程，它不同于我们全面介绍过的存储过程，触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用
触发器：trigger，是一种非常接近于js中的事件的知识。提前给某张表的所有记录（行）绑定一段代码，如果该行的操作满足触发条件，这段提前准备好的代码就会自动执行
作用
- 可在写入数据表前，强制检验或转换数据（保证数据安全）
- 触发器发生错误时，异动的结果会被撤销。（如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销）
优缺点
优点
- 触发器可通过数据库中的相关表实现级联更改。（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作（用户不知道））
- 保证数据安全，进行安全校验
缺点
- 对触发器过分的依赖，势必会影响数据库的结构，同时增加了维护的复杂度
- 造成数据在程序层面不可控（PHP层）
基本操作
创建触发器
基本语法：
create trigger 触发器名字 触发时机 触发事件 on 表 for each row
begin
end
触发对象：on 表 for each row，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器
触发时机
触发时机：每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后
before：在表中数据发生改变前的状态
after：在表中数据已经发生改变后的状态
触发事件
触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有增删改
insert：插入操作
update：更新操作
delete：删除操作
注意事项
一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个，一张表中只能有一个对应after insert触发器
因此，一张表中最多的触发器只能由6个：before insert，before update，before delete，after insert，after update，after delete
案例
需求：有一张商品表，一张订单表（保留商品ID），每次订单生成，商品表中对应的库存就应该发生变化
1.创建两张表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)charset utf8;
create table my_orders(
id int primary key auto_increment,
goods_id int not null.
goods_num int not null
)charset utf8;
insert into my_goods values(null,'手机',1000),(null,'电脑',500),(null,'游戏机',100);
- 创建触发器，如果订单表发生数据插入，对应的商品就减少库存
delimiter $$
create trigger after_insert_order_t after insert on my_orders for each row
begin
update my_goods set inv = inv - 1 where id = 1;
end
$$
delimiter ;
查看触发器
- show triggers;
- show create trigger 触发器名字;
触发触发器
想让触发器执行，需要让触发器在指定的表中，在对应的时机进行对应的操作：
- 表为my_orders
- 在插入之后
- 插入操作
删除触发器
语法：drop trigger 触发器名字;
触发器应用
记录关键字：new、old
触发器针对的是数据表中的每条记录（每行），每行在数据操作前后都有一个对应的状态，触发器在执行之前就将对应的状态获取到了，将没有操作之前的状态（数据）都放到new中
在触发器中，可以通过old和new来获取绑定表中的对应的数据记录
基本语法：关键字.字段名
old和new并不是所有触发器都有
insert：插入前全为空，所以没有old
delete：清空了所有数据，所以没有new
商品自动扣除库存的实现
-- 创建一个自动扣除商品库存的触发器
delimiter $$
create trigger a_i_o_t after insert on my_orders for each row
begin
--更新商品库存：new代表着新增的订单
update my_goods set inv = inv - new.goods_num where id = new.goods_id;
end
$$
delimiter ;
--判断库存功能，如果减去的数量大于剩余数量，则主动报错
delimiter $$
create trigger b_i_o_t before insert on my_orders for each row
begin
--取出库存数据进行判断
select inv from my_goods where id = new.goods_id into @inv;
--判断
if @inv < new.goods_num then
--中断操作：暴力解决，主动出错
insert into XXX values('XXX');
end if;
end
$$
delimiter ;
7294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
