JAVA入门基础_从零开始的培训_MYSQL基础
2022/9/2 2:24:04
本文主要是介绍JAVA入门基础_从零开始的培训_MYSQL基础，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录- 1、数据库概述 与 MYSQL5.7、MYSQL8.0 安装篇（基于Windows）
- MYSQL是什么，为什么要使用MYSQL
- MYSQL的四个版本
- MYSQL环境搭建
- MYSQL的安装与卸载
- Windows10 下安装MYSQL 8.26版本
- 配置环境变量
- Windows10 下安装MYSQL 5.7版本（属于安装2个MYSQL版本）
- MYSQL的卸载步骤
- Windows10 下安装MYSQL 8.26版本
- MYSQL的环境问题
- 登录到MYSQL
- 解决MYSQL5.7中的中文乱码问题 | 8.0的数据库不用修改
- 解决使用Navicat图形化界面工具无法连接到MYSQL 8的问题
- MYSQL忘记密码的还原办法
- MYSQL的安装与卸载
- SQL的基本介绍
- SQL是干什么的
- SQL的分类
- DDL 数据定义语言
- DML 数据操纵语言
- DCL 数据控制语言
- 写SQL的一些提议
- MYSQL中的注释
- MYSQL是什么，为什么要使用MYSQL
- 2、SQL之SELECT使用篇、包含运算符
- SELECT的查询结构（2种）
- 对查询结构中参数的解释
- SELECT查询语句的执行顺序
- MYSQL中的运算符
- 算数运算符（7个）
- 比较运算符（19个）
- 逻辑运算符（7个）
- 位运算符（6个）
- 使用运算符时的小问题记录
- MYSQL中的数据类型
- 所有的数据类型
- 常见的数据类型可用属性
- 数据类型中的一些细节
- UNSIGNED对于整数型、浮点型的作用
- MYSQL5.7整数类型的宽度显示作用
- 浮点数类型(m,n)的作用
- DATETIME 和 TIMESTAMP的区别
- CHAR、VARCHAR、TEXT的使用建议
- 选择数据类型的建议
- 单行函数
- 数值型函数
- 字符串函数（SQL语言中字符串的索引从1开始）
- 常用的多行函数
- 时间日期函数（只列举常用）
- 获取日期、时间
- 日期与时间戳的转换
- 计算日期和时间的函数
- 日期的格式化与解析
- 时间日期函数（只列举常用）
- 多表查询JOIN必须要会的一张图
- 子查询的分类
- 按照查询的记录数量
- 单行比较操作符
- 多行比较操作符
- 按内查询是否被执行多次
- 按照查询的记录数量
- SELECT 查询中遇到的问题记录
- WHERE 与 HAVING的区别
- EXISTS 与 NOT EXISTS的作用
- 使用了GROUP BY 分组后，SELECT 的列表问题、WITH ROLLUP问题
- SELECT的查询结构（2种）
- 3、SQL 之DDL、DML、DCL使用篇
- 数据库的增删改查
- 对数据表的增删改查
- 创建表的3种方式（包括创建临时表）
- 查看建表语句以及表字段结构
- 修改表
- 对于数据的增删改
- 增加数据的5种方式
- 修改数据
- 删除数据的2种方式
- 删除表中重复数据
- MYSQL表中对于数据及类型的约束
- 约束需要考虑的4个方面
- 约束的分类
- 根据约束数据列的限制
- 根据约束的作用范围
- 常见的6个约束如下
- 查看某个表已有的表级约束
- 添加、修改、删除列级约束
- 添加、删除表级约束(check在MYSQL5.7不支持)
- 4、其他数据库对象篇
- 视图
- 视图的创建与使用
- 视图的原理
- 存储过程与存储函数
- 存储过程
- 存储过程的创建与调用（IN、OUT、INOUT）
- IN 、OUT的同时使用
- INOUT的使用
- 删除存储过程
- 查看存储过程的信息（3种方式）
- 存储过程的创建与调用（IN、OUT、INOUT）
- 存储函数
- 存储函数的创建与调用
- 不传入参数
- 传入单个参数
- 传入多个参数
- 删除存储函数
- 查看存储函数的信息（3种）
- 存储函数的创建与调用
- 存储过程与存储函数的参数作用
- 报错：you might want to use the less safe log_bin_trust_function_creators variable
- 错误处理
- 处理错误通常定义在存储过程和存储函数当中，使用如下：
- 错误的处理方式有3种
- 处理错误的6种定义方式
- 抛出一个自定义的错误
- 处理错误通常定义在存储过程和存储函数当中，使用如下：
- 存储过程、存储函数的区别
- 存储过程
- 系统变量与用户变量
- 系统变量
- 系统变量的分类
- 系统变量的查看、修改（全局、会话）
- 用户变量的创建、修改、使用（会话、局部）
- 会话用户变量
- 局部用户变量
- 系统变量
- 流程控制
- 条件判断语句 IF、CASE
- 循环语句LOOP、WHILE、REPEAT
- LOOP 循环（需要配合LEAVE）
- WHILE循环
- REPEAT循环（UNTIL后面不能加分号）
- 跳转语句 LEAVE、ITERATE
- 游标（相当于JAVA中的迭代器）
- 触发器
- 基本语法
- 使用示例
- 查询触发器、删除触发器
- 触发器对于外键约束时的处理情况
- 视图
- 常见问题
- 事物回滚的概念（MYSQL8.0保证了对DDL事物的原子性）
- TRUNCATE 与 DELETE 不添加 WHERE条件的区别
- 关于约束
- 为什么开发建表时，加 not null default '' 或 default 0
- 为什么不想要null值
- AUTU_INCREMENT约束的字段是从1开始的吗？
- 是不是每个表都可以选择存储引擎（用了外键约束后不可以）
1、数据库概述 与 MYSQL5.7、MYSQL8.0 安装篇（基于Windows）
MYSQL是什么，为什么要使用MYSQL
-
MYSQL是一个关系数据库管理系统（RDBMS），我们可以把MYSQL理解为一个管理程序，它管理着我们的一个个数据库。打比方：MYSQL就好比WORD程序，数据库就好比使用WORD程序打开的一个个.docx文档。
-
MYSQL主要是性能卓越、开放源代码、用户群体广泛、主要还有免费版
-
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持 4GB ，64位系统支持最大的表文件为 8TB 。
MYSQL的四个版本
-
社区版(GPL)，免费
- General Public License,GNU通用公共许可协议，软件可以自由使用，修改源代码都是允许的。
-
集群社区版（免费）
-
商业版（收费）
-
集群商业版（收费）
MYSQL环境搭建
MYSQL的安装与卸载
Windows10 下安装MYSQL 8.26版本
-
下载地址
-
双击下载好的安装包：
mysql-installer-community-8.0.26.0.msi -
选择自定义安装，Custom
-
将产品移动到需要安装的右侧
-
点一下移动到右侧的产品，点Advanced Options高级选项
-
设置MYSQL安装位置以及数据库存放的位置
-
一路next，最后点击Execute执行
-
继续点击next，进行MYSQL的配置
-
继续点击next，使用新的认证授权方式，再点确定
-
设置root的密码（我这里是abc123）后点击next
-
设置服务名称、开机是否自启动等，设置好后点击next
-
最后点击Execute执行，然后一路确认即可。
配置环境变量
-
找到并复制MYSQL8安装的路径中的bin路径，我这里是这个：
D:\a_dev_software\MYSQL\MySQL Server 8.0\bin -
将其添加到系统环境变量中的path当中
Windows10 下安装MYSQL 5.7版本（属于安装2个MYSQL版本）
-
下载地址
-
双击下载好的安装包：
mysql-installer-community-5.7.34.0.msi -
页面弹出后，直接点击右下角的cancel取消，确认
-
在页面中选择add
-
将产品移动到需要安装的右侧
-
点一下移动到右侧的产品，点Advanced Options高级选项
-
设置MYSQL安装位置以及数据库存放的位置
-
一路next，最后点击Execute执行
-
继续点击next，进行MYSQL的配置（端口记得换，不能2个MYSQL都用一个端口）
-
点击next，设置root的密码（我这里是abc123）后点击next
-
设置服务名称、开机是否自启动等，设置好后点击next
-
最后点击Execute执行，然后一路确认即可。安装完成最后可以看到如下页面
MYSQL的卸载步骤
-
在服务面板停止MYSQL服务
-
在控制面板找到MYSQL，将其卸载
-
找到数据库文件，可以自由选择是否删除
-
删除对应的环境变量
-
如果是MYSQL5.7，则需要删除如下注册表，win + r后输入regedit回车即可进入注册表管理界面
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL服务 目录删除 HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL服务 目录删除 HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL服务 目录删除 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务目录 删除 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务删除
MYSQL的环境问题
登录到MYSQL
mysql -uroot -pabc123 -P3306 -hlocalhost -u用户名 -p密码 -P端口 -h目标主机，localhost代表本机
解决MYSQL5.7中的中文乱码问题 | 8.0的数据库不用修改
-
原因：MYSQL5.7中的默认编码字符集为latin1，并不支持我们的中文，因此我们需要将其修改为UTF-8
-
使用命令查看系统编码（要登录到mysql才行）
# 查看系统的默认字符集 show variables like 'character_%'; # 查看系统的比较规则字符集（理解为数据的比较字符集） show variables like 'collation_%';
- 找到MYSQL 5.7数据目录中的my.ini文件
# 63行左右，在[mysql]下添加默认字符集 [mysql] default-character-set=utf8 #默认字符集 # 在76行左右，在[mysqld]下添加如下内容 character-set-server=utf8 # 设置服务器的字符集 collation-server=utf8_general_ci # 设置比较规则
-
重启MYSQL5.7的服务即可
-
小提示：已经创建好的数据库、数据表的字符集不会被更改，需要自行修改
alter table 表名 charset utf8; #修改表字符编码为UTF8 alter table 表名 modify 字段名 字段类型 charset utf8; #修改字段字符编码为UTF8 alter database 数据库名 charset utf8; #修改数据库的字符编码为utf8
解决使用Navicat图形化界面工具无法连接到MYSQL 8的问题
登录MYSQL8，执行如下语句
#使用mysql数据库 USE mysql; #修改'root'@'localhost'用户的密码规则和密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123'; #刷新权限 FLUSH PRIVILEGES;
MYSQL忘记密码的还原办法
1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)
2: 通过命令行+特殊参数开启（也可以直接在配置文件[mysqld]后加上skip-grant-tables）
mysqld --defaults-file="指定mysql的配置文件my.ini" --skip-grant-tables
3: 此时，mysqld服务进程已经打开。并且不需要权限检查
4: mysql -uroot 无密码登陆服务器。另启动一
个客户端进行
5: 修改权限表 （1） use mysql; （2）update user set authentication_string=password('新密
码') where user='root' and Host='localhost'; （3）flush privileges;
6: 通过任务管理器，关掉mysqld服务进
程。
7: 再次通过服务管理，打开mysql服务。 8: 即可用修改后的新密码登陆。
SQL的基本介绍
SQL是干什么的
- SQL（Structured Query Language，结构化查询语言），用于跟数据打交道。在不同的数据库管理系统中，SQL可能会出现一些细微的差距。
SQL的分类
DDL 数据定义语言
CARETE、DROP、ALTER等
DML 数据操纵语言
INSERT、DELETE、UPDATE、SELECT
DCL 数据控制语言
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等
写SQL的一些提议
-
SQL可以写单行或多行，注意可读性
-
字符串类型及日期类型，使用单引号
''表示 -
在书写别名时，使用
as并且别名使用双引号包裹"" -
数据库名、表名、表别名、字段名、字段别名都使用小写
-
SQL关键字、函数名、绑定变量都大写
MYSQL中的注释
# 单行注释 -- 单行注释 /* 多行注释 */
2、SQL之SELECT使用篇、包含运算符
SELECT的查询结构（2种）
- 第一种
SELECT ...,....,... FROM ...,...,.... WHERE 多表的连接条件 AND 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,...
- 第二种
SELECT ...,....,... FROM ... JOIN ... ON 多表的连接条件 JOIN ... ON ... WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,...
对查询结构中参数的解释
-
（1）from：从哪些表中筛选
-
（2）on：关联多表查询时，去除笛卡尔积
-
（3）where：从表中筛选的条件
-
（4）group by：分组依据
-
（5）having：在统计结果中再次筛选
-
（6）order by：排序
-
（7）limit：分页
SELECT查询语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
首先假设我们进行的查询是一个多表查询：
-
首先先通过** CROSS JOIN 求笛卡尔积**，相当于得到虚拟表 vt（virtual table）1-1；
-
通过** ON 进行筛选**，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
-
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
-
然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
-
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到** SELECT 和 DISTINCT**阶段 。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。
-
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 **ORDER BY **阶段 ，得到虚拟表 vt6 。
-
最后在 vt6 的基础上，取出指定行的记录，也就是 **LIMIT **阶段 ，得到最终的结果，对应的是虚拟表vt7 。
MYSQL中的运算符
算数运算符（7个）
+：加法-：减法*：乘法/或者DIV：除法%或者MOD：取模，取余时仅看被模数的正负号
比较运算符（19个）
-
=：等于 -
<=>：安全等于，为null而生，可以比较null -
<>或者!=： 不等于 -
<：小于 -
<=：小于等于 -
>：大于 -
>=：大于等于 -
IS NULL：判断是否为null -
IS NOT NULL：判断是否不为null -
ISNULL：判断是否为null的函数 -
LEAST：最小值运算符 -
GREATEST：最大值运算符 -
BETWEEN AND：判断一个值是否在两个值之间 -
IN：属于运算符，判断一个值是否为列表中任意一个值 -
NOT IN：不属于运算符，判断一个值是否不为列表中任意一个值 -
LIKE：模糊匹配，%代表多个任意字符，_一个任意字符 -
REGEXP：正则表达式运算符 -
RLIKE：正则表达式运算符
逻辑运算符（7个）
-
AND或者&&：逻辑与 -
OR或者||： 逻辑或 -
NOT或者!：逻辑非 -
XOR：异或
位运算符（6个）
-
&：与 -
|： 或 -
^：异或 -
<<：左移 -
>>：右移 -
>>>：无符号右移
使用运算符时的小问题记录
-
NULL值与任何值进行计算时，结果都为NULL，除非使用<=>、IS NULL、IS NOT NULL、IFNULL()等判断NULL的运算符或函数
-
字符串类型的数值在计算时，会隐式转换成与其进行计算的数据类型，若是转换失败则将会被转换为0，字符串与字符串进行比较时例外。
-
当一个数除0，那么结果为NULL
MYSQL中的数据类型
所有的数据类型
| 类型 | 类型举例 |
|---|---|
| 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT |
| 浮点类型 | FLOAT、DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
| 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
| 枚举类型 | ENUM |
| 集合类型 | SET |
| 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
| JSON类型 | JSON对象、JSON数组 |
| 空间数据类型 | 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |
常见的数据类型可用属性
| MySQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集 |
数据类型中的一些细节
UNSIGNED对于整数型、浮点型的作用
-
对于整数型： 将无法存储负数，且将数据存储范围扩大（扩大的大小就是负数的范围）。
-
对于浮点型： 仅仅是无法存储负数。存储空间一样占用，存储范围没有变化。
MYSQL5.7整数类型的宽度显示作用
-
设置了宽度仍然可以存储超越宽度的数值，所以需要配合上ZEROFILL使用才有意义。
-
ZEROFILL ： 整数位数不足宽度时，会往左侧补0。（添加此参数时，会同时默认添加UNSIGNED）
浮点数类型(m,n)的作用
-
用于限制浮点数的宽度m和小数位数n
-
宽度m包括小数位，例如：99.51的宽度m为4
DATETIME 和 TIMESTAMP的区别
-
TIMESTAMP存储空间比较小，表示的日期时间范围也比较小。
-
TIMESTAMP 底层存储的是毫秒值，计算速度更快。
-
TIMESTAMP 的日期时间会根据时区自动变换。
CHAR、VARCHAR、TEXT的使用建议
-
当字符串长度固定或基本不变时，使用CHAR
-
字符数不超过5000时，使用VARCHAR
-
字符数大于5000时，使用TEXT并新建一个表进行存储，避免影响索引查询的效率
-
InnoDB存储引擎内部行的存储格式并不区分固定长度与可变长度列，影响性能的因素是数据行使用的存储总量。
选择数据类型的建议
-
整数类型：一般采用INT
-
小数类型：使用DECIMAL(M,D)
-
日期时间：DATETIME
-
字符串类型：略（上面有写）
原因：这样做的好处是，确保你的系统不会因为数据类型定义出错。数据出错的代价远远高于占用的资源代价。
单行函数
数值型函数
| 函数名 | 函数作用 |
|---|---|
| ABS(x) | 求x的绝对值 |
| SIGN(x) | 判断x的值，大于0返回1，小于0返回-1，等于0返回0 |
| PI() | 圆周率 |
| CEIL(x) 或 CEILING(x) | 将x向上取整 |
| FLOOR(x) | 将x向下取整 |
| LEAST(e1,e2,e3) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3) | 返回列表中最大值 |
| MOD(x,y) | x除y之后的余数 |
| RAND() | 返回0到1之间的伪随机数 |
| RAND(x) | 输入一个种子，依然返回0到1之间的伪随机数 |
| ROUND(x) | 返回x四舍五入后的值 |
| ROUND(x,y) | 返回x四舍五入后的值，小数保留y位(可以为负数) |
| TRANCATE(x,y) | 保留x的y位小数，多出的位数直接去除 |
| SQRT(x) | 求x的平方根，x为负数时返回NULL |
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数，例如CONV(12, 8, 2)代表12是一个8进制，需要转换为2进制 |
字符串函数（SQL语言中字符串的索引从1开始）
| 函数名 | 函数作用 |
|---|---|
| ASCII(s) | 返回字符串s中第一个元素的ASCII码所代表的数字 |
| CHAR_LENGTH(s) 或 CHARACTER_LENGTH(s) | 返回x的字符数量 |
| LENGTH(s) | 返回s的字节数量 |
| CONCAT(s1,s2,s3) | 将列表中的数据拼接为一个字符串 |
| CONCAT_WS(x,s1,s2,s3) | 将列表中的数据拼接为一个字符串，其中x为连接符 |
| insert(str,idx,len,replacestr) | 将字符串str索引 idx的位置开始后的len个字符，替换成replacestr |
| REPLACE(str,a,b) | 将str字符串中的子集a替换成b |
| UPPER(s) 或 UCASE(s) | 将s转换为大写 |
| LOWER(s) 或 LCASE(s) | 将s转换为小写 |
| LEFT(str,n) | 获取字符串str从左开始的n个元素的子集 |
| RIGHT(str,n) | 获取字符串str从右开始的n个元素的子集 |
| LPAD(str,n,pad) | str如果不满足字符长度为n，则往左侧补pad填充到字符长度为n（相当于右对齐） |
| RPAD(str,n,pad) | str如果不满足字符长度为n，则往右侧补pad填充到字符长度为n（相当于左对齐） |
| LTRIM(s) | 去除s左侧的空格 |
| RTRIM(s) | 去除s右侧的空格 |
| TRIM(s) | 去除s左右二侧的空格 |
| TRIM(s1 from s) | 去除s左右两侧的s1 |
| TRIM(LEADING s1 from s) | 去除s左侧的s1 |
| TRIM(LEADING s1 from s) | 去除s左侧的s1 |
| REPEAT(str,n) | 返回str重复n次的结果 |
| SPACE(n) | 返回n个空格 |
| STRCMP(str1,str2) | 比较2个字符串之间的大小 |
| SUBSTR(s,index,len) 或者 SUBSTRING(s,n,len) 或 MID(s,n,len) | 返回s字符串中从index开始的len个元素 |
| LOCATE(substr,str) 或者 POSITION(substr IN str) 或者 INSTR(str,substr) | 获取str中substr第一次出现的索引位置 |
| ELT(m,s1,s2,s3,s4) | 返回指定位置的字符串，如果m为1，返回s1，m为2，返回s2 |
| FIELD(s,s1,s2,s3,s4) | 返回s在字符串列表中出现的位置 |
| FIND_IN_SET(s,'s1,s2,s3,s4') | 返回s在字符串列表中出现的位置 |
| REVERSE(s) | 将字符串s反转 |
| NULLIF(value1,value2) | 如果2个值相等则返回null，否则返回value1 |
常用的多行函数
| 函数名 | 作用 |
|---|---|
| AVG() | 平均值，只适用于数值类型 |
| SUM() | 求和，只适用于数值类型 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| COUNT(字段名)、COUNT(1)、COUNT(*) | 统计数量，注意COUNT(字段名)可能会统计不正确，因为不统计NULL值 |
时间日期函数（只列举常用）
获取日期、时间
# 1.获取当前的日期、时间、日期时间 SELECT CURDATE(),CURTIME(),NOW() FROM DUAL
日期与时间戳的转换
# 2. 日期与时间戳的转换
# 2.1 获取当前时间戳
SELECT UNIX_TIMESTAMP() FROM DUAL;
# 2.2 获取指定时间的时间戳
SELECT UNIX_TIMESTAMP('2022-08-13 16:33:06') FROM DUAL;
# 2.3 将时间戳转换为日期
SELECT FROM_UNIXTIME(1660379586) FROM DUAL;
计算日期和时间的函数
# 3. 计算日期和时间的函数 DATEDIFF、TO_DAYS、DATE_ADD、DATE_SUB
# 3.1 计算2个日期相差的天数，左边减右边，结果：3
SELECT DATEDIFF('2022-08-13', '2022-08-10') FROM DUAL;
# 3.2 计算2个时间相差的时间，左减右，结果：06:05:28
SELECT TIMEDIFF('16:35:48', '10:30:20') FROM DUAL;
# 3.3 返回一个日期距离0000年1月1日的天数(注意是天数)
SELECT TO_DAYS('2000,01,01') FROM DUAL;
# 3.4 为一个日期时间 增加日期时间，结果: 2022-08-15 16:33:06
SELECT DATE_ADD('2022-08-13 16:33:06',INTERVAL 2 DAY) FROM DUAL;
# 3.5 为一个日期时间 减少日期时间，结果: 2022-08-11 16:33:06
SELECT DATE_SUB('2022-08-13 16:33:06',INTERVAL 2 DAY) FROM DUAL;
日期的格式化与解析
# 4. 日期的格式化与解析
# 4.1 将一个日期时间进行格式化, 2022/08/13 16:33:06
SELECT DATE_FORMAT('2022-08-13 16:33:06','%Y/%m/%d %H-%i-%S');
# 4.2 获得一个日期时间的格式，注意，日期和时间的也可以获取
SELECT GET_FORMAT(TIME, 'ISO') FROM DUAL;
# 4.3 将一个字符串转换为日期格式
SELECT STR_TO_DATE('16:33:06',GET_FORMAT(TIME,'ISO')) FROM DUAL;
多表查询JOIN必须要会的一张图
-
（1）左上图：相当于左外连接
-
（2）右上图：相当于右外连接
-
（3）中图：相当于内连接
-
（4）左中图：相当于左外连接不要中间的部分。
-
（5）右中图：相当于右外连接不要中间部分
-
（6）左下图：可以用左上图 UNION ALL 右中图 。 也可以用右上图 UNION ALL 左中图
-
（7）右下图：左中图 UNION ALL 右中图即可。
子查询的分类
按照查询的记录数量
-
单行子查询（子查询返回的只有一条记录）
-
多行子查询（子查询返回的有多条记录）
单行比较操作符
| 运算符 | 作用 |
|---|---|
| = | 相等 |
| >= | 大于等于 |
| > | 大于 |
| <= | 小于等于 |
| < | 小于 |
| <> | 不等于 |
多行比较操作符
| 运算符 | 作用 |
|---|---|
| IN | 等于列表中任意一个 |
| ANY | 匹配列表中任意一个，需要配合单行比较操作符使用 |
| SOME | 等同于ANY |
| ALL | 匹配列表中所有的值，需要配合单行比较操作符使用 |
按内查询是否被执行多次
-
不相关子查询（每次都是固定的返回结果）
-
相关子查询（每一次的查询都需要依靠外查询来提供查询条件）
SELECT 查询中遇到的问题记录
WHERE 与 HAVING的区别
WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
EXISTS 与 NOT EXISTS的作用
EXISTS 通常用于相关子查询，判断子查询中的数据是否符合条件，符合时返回1（true），不符合时返回0（false）。
NOT EXISTS与其恰恰相反。
使用IN 和 NOT IN的相关子查询通常可以改用 EXISTS 和 NOT EXISTS实现。
使用了GROUP BY 分组后，SELECT 的列表问题、WITH ROLLUP问题
-
使用了GROUP BY 分组后，SELECT 列表只能放聚合函数或进行了分组的字段。
-
当使用了ORDER BY 后，无法使用WITH ROLLUP，
-
WITH ROLLUP：在GROUP BY分组字段的基础上再进行统计数据，对数据进行求和。
3、SQL 之DDL、DML、DCL使用篇
数据库的增删改查
1. 创建数据库 CREATE DATABASE IF NOT EXISTS my_db CHARACTER SET = 'UTF8'; 2. 查询所有的数据库 SHOW DATABASES; 3. 查看正在使用的数据库 SELECT DATABASE(); 4. 查看指定数据库下所有的表 SHOW TABLES FROM 数据表名; 5. 查看数据库的创建信息 SHOW CREATE DATABASE 数据库名 6. 使用/切换数据库 USE 数据库名; 7. 修改数据库字符集 ALTER DATABASE 数据库名 CHARACTER SET = 'GBK'; 8. 删库跑路 DROP DATABASE IF EXISTS 数据库名。
对数据表的增删改查
创建表的3种方式（包括创建临时表）
1. 白手起家创建 CREATE TABLE IF NOT EXISTS( 字段名 字段类型 [约束条件] [默认值], 字段名 字段类型 [约束条件] [默认值], 字段名 字段类型 [约束条件] [默认值] ); 2. 通过查询的表数据创建（注意查询语句如果有别名会使用别名作为新表的字段名） CREATE TABLE 需要创建的表名 AS SELECT * FROM 表名; 3. 创建临时表 # 创建一个临时表 CREATE TEMPORARY TABLE 表名 SELECT * FROM 表名; # 删除一个临时表 DROP TABLE 临时表名;
查看建表语句以及表字段结构
# 查看建表语句 SHOW CREATE TABLE 表名; # 查看表字段结构 DESC 表名字;
修改表
1. 追加列 ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST]; 2. 修改列 ALTER TABLE 表名 MODIFY 修改的字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST]; 3. 重命名列 ALTER TABLE 表名 CHANGE 修改的字段名 新字段名 字段类型 [约束] [默认值] [AFTER|LAST|FIRST]; 4. 删除列 ALTER TABLE 表名 DROP COLUMN 字段名; 5. 修改表名 RENAME TABLE 原表名 TO 修改后的表名; 6. 删除表 DROP TABLE IF EXISTS 表名; 7. 清空表（会把表中数据清空并释放存储空间） TRUNCATE TABLE 表名;
对于数据的增删改
增加数据的5种方式
# 方式1 INSERT INTO 表名 values(各字段的值); # 方式2（推荐） INSERT INTO 表名(指定字段) VALUES (根据指定的字段填写值); # 方式3、添加多行数据 INSERT INTO 表名(指定字段) VALUES (根据指定的字段填写值), (根据指定的字段填写值), (根据指定的字段填写值); # 方式4、利用SELECT实现多行数据的添加，（注意SELECT查询出来的数据类型与表名可以匹配） INSERT INTO 表名(指定字段) SELECT 填写字段 FROM 表名; 例如： INSERT INTO t_test1 SELECT last_name, salary FROM employees; # 方式5、依然是利用SELECT实现数据的添加 INSERT INTO 表名 SELECT 字段列表，逗号间隔 UNIOIN ALL SELECT 字段列表，逗号间隔 UNIOIN ALL SELECT 字段列表，逗号间隔; 例如： INSERT INTO t_teset1 SELECT '张三',2000 union all SELECT '李四',2500 union all SELECT '王五',3000 ;
修改数据
UPDATE 表名 SET 需要修改的字段=需要修改后的值 WHERE ..
删除数据的2种方式
# 方式1 DELETE FROM 表名 WHERE ... # 方式2 配合SELECT，例如多个表同时删除某条记录 语法示例如下： DELETE e, d FROM employee e JOIN departments d ON e.department_id = d.department_id WHERE d.department_id = 10;
删除表中重复数据
DELETE FROM class_info WHERE id IN ( SELECT id FROM ( SELECT id FROM class_info WHERE id NOT IN ( SELECT MIN(id) FROM class_info GROUP BY class_name, class_leader ) ) c );
MYSQL表中对于数据及类型的约束
约束需要考虑的4个方面
约束就是为了保证数据的完整性，完整性指的是精确性和可靠类。
-
实体完整性。同一个表中，不能出现2条无法区分的记录
-
域完整性。指的是表中的字段能够存储的数据规范。提现在check 检查约束。
-
引用完整性。例如：员工所在部门，那么必须存在该部门。提现在foreign key 外键。
-
用户自定义完整性。例如：想要让用户名唯一，或者年龄不能为空等.
约束的分类
根据约束数据列的限制
-
单列约束，每个约束只能约束一列。
-
多列约束，每个约束可以约束多列。
根据约束的作用范围
-
列级约束：约束作用于列上
-
表级约束：约束作用于表上
常见的6个约束如下
-
NOT NULL 该列不能为空，列级约束
-
UNIQUE 唯一性约束，表级约束
-
PRIMARY KEY 主键约束（非空、唯一），无法更改约束名，表级约束
-
FOREIGN KEY 外键约束，表级约束。必须引用/参考主表的主键或唯一约束的列
- 设置外键时可以指定 ON UPDATE/DELETE CASCADE/SET NULL ，分别代表更新或删除时，另一张关联表CASCADE共同删除或修改，SET NULL 设置为空
-
CHECK 检查约束，表级约束
-
DEFAULT 默认值，列级约束
查看某个表已有的表级约束
SELECT * FROM information_schema.table_constraints WHERE TABLE_NAME = 'employees';
添加、修改、删除列级约束
- 添加列级约束
1. 通过创建表的方式 CREATE TABLE t_test1 ( name VARCHAR(12) NOT NULL, age INT ) 2. 通过ALTER MODIFY的方式 ALTER TABLE t_test1 MODIFY age INT NOT NULL;
- 修改列级约束
ALTER TABLE t_test1 MODIFY age INT DEFAULT 10;
- 删除列级约束（其实就是ALTER MODIFY 该字段不写约束即可）
ALTER TABLE t_test1 MODIFY age INT;
添加、删除表级约束(check在MYSQL5.7不支持)
- 添加表级约束
# 1. 通过创建表时指定 # 提示：constriant就是为了给表约束取名。对主键无效。不取名则默认为字段名(check的默认格式为 表名_chk_1，表名_chk_2，外键的为：表名_ibfk_1)。 CREATE TABLE test_2( id INT, name VARCHAR(12), t_test1_id INT, age INT, birthday DATE, CONSTRAINT fk_id FOREIGN KEY (t_test1_id) REFERENCES t_test1(id), PRIMARY KEY(id), CHECK (age > 10), UNIQUE KEY(name) ) 2. 通过ALTER ADD 添加 ALTER TABLE test_2 ADD CONSTRAINT chk_age CHECK(age < 20);
- 删除表级约束（根据约束名）
# 1、 删除主键约束 和 唯一约束 ALTER TABLE test_2 DROP INDEX `PRIMARY`; ALTER TABLE test_2 DROP INDEX name; # 2、删除检查约束 ALTER TABLE test_2 DROP CHECK test_2_chk_1; # 3、删除外键约束 ALTER TABLE test_2 DROP FOREIGN KEY fk_id;
4、其他数据库对象篇
视图
视图的创建与使用
# 创建视图 CREATE VIEW vu_sal AS SELECT * FROM employees; # 使用视图 SELECT * FROM vu_sal;
视图的原理
-
视图其实就是一张虚拟表，视图中不存储数据
-
可以把视图理解为一个存储起来的SELECT查询
-
对视图的修改就相当于对于基表的修改
-
对视图进行一条数据数据修改时，该条数据必须与基表中的数据一对一对应
存储过程与存储函数
存储过程
存储过程的创建与调用（IN、OUT、INOUT）
IN 、OUT的同时使用
# 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回 delimiter $ CREATE PROCEDURE format_date(IN my_date DATE, OUT res VARCHAR(32)) BEGIN SELECT DATE_FORMAT(my_date, GET_FORMAT(DATE,'ISO')) INTO res FROM DUAL; END $ delimiter ; set @my_date = '2020/10/20'; CALL format_date(@my_date, @res); SELECT(@res) FROM DUAL;
INOUT的使用
# 传入a和b两个值，最终a和b都翻倍并返回、创建带inout模式参数的存储过程 DELIMITER $ CREATE PROCEDURE calculate_num(INOUT a INT, INOUT b INT) BEGIN SELECT a * 2 INTO a; SELECT b * 2 INTO b; END $ DELIMITER ; # 调用 set @a = 2; set @b = 6; CALL calculate_num(@a, @b); SELECT @a FROM DUAL; SELECT @b FROM DUAL;
删除存储过程
DROP PROCEDURE 存储过程名;
查看存储过程的信息（3种方式）
SHOW CREATE PROCEDURE 存储过程名;
SHOW PROCEDURE STATUS LIKE '存储过程名';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
存储函数
存储函数的创建与调用
不传入参数
#无参有返回 #1. 创建函数get_count(),返回公司的员工个数 DELIMITER // CREATE FUNCTION get_count() RETURNS INT DETERMINISTIC CONTAINS SQL BEGIN RETURN (SELECT COUNT(1) FROM employees); END // DELIMITER ; # 调用存储函数 SELECT get_count() FROM DUAL;
传入单个参数
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资 DELIMITER $ CREATE FUNCTION ename_salary(input_name VARCHAR(25)) RETURNS DOUBLE(8,2) DETERMINISTIC CONTAINS SQL BEGIN RETURN (SELECT salary FROM employees WHERE last_name = input_name); END $ DELIMITER ; drop FUNCTION ename_salary; # 调用 set @input_name = 'Austin'; SELECT ename_salary(@input_name); #3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资 DELIMITER $ CREATE FUNCTION dept_sal(dept_id INT) RETURNS INT DETERMINISTIC CONTAINS SQL BEGIN RETURN (SELECT AVG(salary) FROM employees WHERE department_id = dept_id); END $ DELIMITER ; # 调用 SELECT dept_sal(60);
传入多个参数
#4. 创建函数add_float()，实现传入两个float，返回二者之和 DELIMITER $ CREATE FUNCTION add_float(f1 FLOAT, f2 FLOAT) RETURNS FLOAT DETERMINISTIC CONTAINS SQL BEGIN RETURN (SELECT f1 + f2 FROM DUAL); END $ DELIMITER ; # 调用 SELECT add_float(60, 60);
删除存储函数
DROP FUNCTION 存储函数名;
查看存储函数的信息（3种）
SHOW CREATE FUNCTION 函数过程名;
SHOW FUNCTION STATUS LIKE '函数过程名';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
存储过程与存储函数的参数作用
| 参数名 | 参数作用 |
|---|---|
| LANGUAGE SQL | 说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。 |
| [NOT] DETERMINISTIC | 指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。 |
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | 指明子程序使用SQL语句的限制。（下面有具体参数说明） |
| SQL SECURITY { DEFINER | INVOKER } | 执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。DEFINER：表示只有当前存储过程的创建者或者定义者才能执行当前存储过程。INVOKER ：表示拥有当前存储过程的访问权限的用户能够执行当前存储过程 |
-
CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
-
NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
-
READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
-
MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
-
默认情况下，系统会指定为CONTAINS SQL。
报错：you might want to use the less safe log_bin_trust_function_creators variable
1. 加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
2、 SET GLOBAL log_bin_trust_function_creators = 1;
错误处理
- 咱们MYSQL执行的时候，如果出现报错，往往不仅有错误码CODE，还有个错误代号SQLSTATE，例如：其中的1136就是错误码，21S01就是错误代号SQLSTATE
ERROR 1136 (21S01): Column count doesn't match value count at row 1
处理错误通常定义在存储过程和存储函数当中，使用如下：
错误的处理方式有3种
-
EXIT 遇到错误时就停止
-
CONTINUE 继续执行
-
UNDO 遇到错误时撤回之前的操作（MYSQL不支持）
处理错误的6种定义方式
# 定义错误名称 DECLARE column_not_match CONDITION FOR 1130; # 通过CODE 定义 DECLARE column_not_match_2 CONDITION FOR SQLSTATE '21S01'; # 通过SQLSTATE定义 # 1. 使用错误码 DECLARE EXIT HANDLER FOR 1130 SET @info = '输入的字段与列字段不匹配,CODE 1130'; # 2. 使用字符提示SQLSTATE DECLARE CONTINUE HANDLER FOR SQLSTATE '21S01' SET @info = '输入的字段与列字段不匹配, SQLSTATE 21S01'; # 3. 使用定义好的错误名称 DECLARE EXIT HANDLER FOR column_not_match SET @info = '输入的字段与列字段不匹配,column_not_match'; # 4. 匹配所有以01开头的SQLSTATE错误代码； DECLARE EXIT HANDLER FOR SQLWARNING SET @info = '输入的字段与列字段不匹配,SQLWARNING'; # 5. 匹配所有以02开头的SQLSTATE错误代码； DECLARE EXIT HANDLER FOR NOT FOUND SET @info = '输入的字段与列字段不匹配,NOT FOUND'; # 6. 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码； DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = '输入的字段与列字段不匹配,SQLEXCEPTION';
抛出一个自定义的错误
示例： SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '这是一个自定义的错误';
存储过程、存储函数的区别
| 关键字 | 调用语法 | 返回值 | 应用场景 | |
|---|---|---|---|---|
| 存储过程 | PROCEDURE | CALL 存储过程() | 有0个或多个 | 一般用于更新 |
| 存储函数 | FUNCTION | SELECT 存储函数() | 有且只能有一个 | 一般用于查询结果为一个值并返回时 |
注意：存储函数可以放在查询语句中使用，存储过程不行
系统变量与用户变量
系统变量
系统变量的分类
-
全局系统变量（被所有会话所共享）
-
会话系统变量（一个MYSQL的连接就相当于一个会话）
-
注意：有一些系统变量，既是全局系统变量、也是会话系统变量
系统变量的查看、修改（全局、会话）
# 全局系统变量的查看 SHOW GLOBAL VARIABLES; SELECT @@global.character_set_database; # 查看指定全局系统变量 # 会话系统变量的查看 SHOW SESSION VARIABLES; SHOW VARIABLES; # 默认查看会话系统变量 SELECT @@session.character_set_database; # 查看指定会话系统变量 # 修改全局系统变量 SET @@global.MAX_CONNECTIONS = 150; SET global max_connections = 1000; # 修改会话系统变量 SET @@session.character_set_database = 'gbk'; SET session character_set_database = 'utf8mb3';
- 注意：要想让系统变量永久生效，只能通过更改配置文件的方式。否则重启MYSQL服务都会充值系统变量。MYSQL8修改全局系统变量时添加关键字PERSIST可以让全局系统变量持久化。例如：
SET PERSIST max_connections = 1000;
- MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。
用户变量的创建、修改、使用（会话、局部）
会话用户变量
# 会话用户变量的定义 SET @A = 1; SET @B = 2; SET @SUM = @A + @B; # 会话系统变量的使用 SELECT @SUM FROM DUAL;
局部用户变量
- 提示： 只能定义在存储过程或存储函数当中，准确来说是必须定义在 BEGIN END语句块当中，并且变量的声明必须放在BEGIN的首行。
DELIMITER $ CREATE PROCEDURE test_var() BEGIN # 1. 声明变量 DECLARE var1 INT DEFAULT 10; DECLARE var2 VARCHAR(12); DECLARE var3 DATE; # 2. 为变量赋值 # 2.1 第一种赋值方式，SET SET var2 = '张三'; # 2.2 第二种赋值方式，SELECT .. INTO .. SELECT STR_TO_DATE(NOW(),GET_FORMAT(DATETIME,'ISO')) INTO var3 FROM DUAL; # 3. 使用变量 SELECT var1,var2,var3; END $ DELIMITER ;
流程控制
条件判断语句 IF、CASE
# 注意ELSE后面不需要接THEN DELIMITER $ CREATE PROCEDURE if_test(IN num INT) BEGIN IF num = 0 THEN SELECT 'the num is 0'; ELSEIF num = 1 THEN SELECT 'the num is 1'; ELSE SELECT 'num不是0也不是1呗'; END IF; END $ DELIMITER ; # CASE的第一种用法，相当于JAVA中的IF ELSE DELIMITER $ CREATE PROCEDURE case_test(IN num INT) BEGIN CASE WHEN num = 0 THEN SELECT 'the num is 0'; WHEN num = 1 THEN SELECT 'the num is 1'; ELSE SELECT 'num不是0也不是1呗'; END CASE; END $ DELIMITER ; # CASE的第二种用法，相当于JAVA中的SWITCH DELIMITER $ CREATE PROCEDURE case_test2(IN num INT) BEGIN CASE num WHEN 0 THEN SELECT 'the num is 0'; WHEN 1 THEN SELECT 'the num is 1'; ELSE SELECT 'num不是0也不是1呗'; END CASE; END $ DELIMITER ;
循环语句LOOP、WHILE、REPEAT
LOOP 循环（需要配合LEAVE）
DELIMITER $ CREATE PROCEDURE loop_test(OUT result INT) BEGIN # 初始化 DECLARE num INT DEFAULT 0; # 循环条件 loop_lable:LOOP # 迭代条件 IF num = 10 THEN LEAVE loop_lable; END IF; # 循环体、迭代条件 SET num = num + 1; END LOOP loop_lable; SET result = num; END $ DELIMITER ; # 调用 CALL loop_test(@result); SELECT @result;
WHILE循环
DELIMITER $ CREATE PROCEDURE while_test(OUT result INT) BEGIN # 初始化 DECLARE num INT DEFAULT 0; # 循环条件 WHILE num < 10 DO # 循环体、迭代条件 SET num = num + 1; END WHILE; SET result = num; END $ DELIMITER ;
REPEAT循环（UNTIL后面不能加分号）
DELIMITER $ CREATE PROCEDURE repeat_test(OUT result INT) BEGIN # 初始化 DECLARE num INT DEFAULT 0; # 循环条件 REPEAT # 循环体、迭代条件 SET num = num + 1; UNTIL num > 10 END REPEAT; SET result = num; END $ DELIMITER ; # 调用 CALL repeat_test(@result); SELECT @result;
跳转语句 LEAVE、ITERATE
-
设置标签不仅仅可以作用在LOOP上，还可以作用到其他的循环结构、甚至是BEGIN上。一般都是配合LEAVE使用
-
ITERATE理解一下，与JAVA中的continue作用一样。
游标（相当于JAVA中的迭代器）
注意： 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致
DELIMITER $ CREATE PROCEDURE cursor_test() BEGIN # 定义局部变量 DECLARE avg_sal DOUBLE DEFAULT 0.0; DECLARE avg_sum_sal DOUBLE DEFAULT 0.0; DECLARE count INT DEFAULT 1; # 1. 定义游标 DECLARE avg_sal_cursor CURSOR FOR SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal; # 2. 打开游标 OPEN avg_sal_cursor; # 3. 使用游标 WHILE count <= 3 DO # 每次都取出游标中的一条数据赋值给 avg_sal FETCH avg_sal_cursor INTO avg_sal; # 累计求和 SET avg_sum_sal = avg_sum_sal + avg_sal; # 迭代条件 SET count = count + 1; END WHILE; # 4. 关闭游标 CLOSE avg_sal_cursor; SELECT avg_sum_sal; END $ DELIMITER ; # 调用 CALL cursor_test();
触发器
基本语法
CREATE TRIGGER 触发器名 (BEFORE|AFTER) (INSERT|DELETE|UPDATE) 表名 FOR EACH ROW # 表示表中的每一行进行操作时都会触发该触发器 BEGIN // 这里写方法体 // 注意： 使用NEW可以获取到当前操作的数据 END
使用示例
DELIMITER // CREATE TRIGGER after_insert_emp AFTER INSERT ON test_tri FOR EACH ROW BEGIN INSERT INTO test_tri_log VALUES(NEW.last_name, NEW.salary); END // DELIMITER ;
查询触发器、删除触发器
# 方式1 SHOW TRIGGERS; # 方式2 SHOW CREATE TRIGGER 触发器名; # 方式3 SELECT * FROM information_schema.TRIGGERS; # 删除触发器 DROP TRIGGER IF EXISTS 触发器名称;
触发器对于外键约束时的处理情况
-
前提：触发器是对于从表进行定义的
-
如果修改从表，那么触发器会正常执行
-
如果修改主表导致的从表内容修改，触发器不会执行。
常见问题
事物回滚的概念（MYSQL8.0保证了对DDL事物的原子性）
-
原子性： 理解一次访问数据库的请求所执行的SQL，要么全都执行成功，要么全都别执行。
-
MYSQL中，DDL数据定义语言的操作都是不可回滚的，执行时需要务必注意
DDL操作回滚日志 写入到data dictionary数据字典表mysql.innodb_ddl_log （该表是隐藏的表，通过show tables无法看到）
- DML操作如果想要回滚数据，则需要先将自动回滚关闭（建议关闭前先提交一次），而后手动提交事物
# 提交事物 commit; # 将自动回滚设置为false set autocommit = false; # 执行DML操作 --- # 提交事物 commit;
TRUNCATE 与 DELETE 不添加 WHERE条件的区别
在效率上来说，TRUNCATE可以更快，但是TRUNCATE不支持事物的回滚，并且无法触发TRIGGER，建议是尽量不实用TRUNCATE
关于约束
为什么开发建表时，加 not null default '' 或 default 0
因为不想要null值
为什么不想要null值
-
null值除了is not null 、is null 等特殊的比较运算符，一般计算结果都为null，会影响我们查询的结果
-
null值会影响索引的效果。
AUTU_INCREMENT约束的字段是从1开始的吗？
默认是从1开始的，如果添加第一条字段时手动指定AUTO_INCREMENT字段的值，那么会按照指定字段的值往后排。
是不是每个表都可以选择存储引擎（用了外键约束后不可以）
MYSQL支持多种存储引擎，默认都使用INNODB，当然也可以在创建表的时候指定存储引擎。不过外键关联的表无法指定不同的存储引擎。（想想明明2张表需要关联，还指定不同的存储引擎，这不就影响参照完整性了么）。
这篇关于JAVA入门基础_从零开始的培训_MYSQL基础的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-23Springboot应用的多环境打包入门
- 2024-11-23Springboot应用的生产发布入门教程
- 2024-11-23Python编程入门指南
- 2024-11-23Java创业入门：从零开始的编程之旅
- 2024-11-23Java创业入门：新手必读的Java编程与创业指南
- 2024-11-23Java对接阿里云智能语音服务入门详解
- 2024-11-23Java对接阿里云智能语音服务入门教程
- 2024-11-23JAVA对接阿里云智能语音服务入门教程
- 2024-11-23Java副业入门：初学者的简单教程
- 2024-11-23JAVA副业入门：初学者的实战指南
