MySQL数据库基础
2022-05-08 14:05:46 0 举报AI智能生成
数据的基础知识、DDL、DML、DCL、视图、触发器、存储过程、存储函数、附带链接、无痛学习MySQL
作者其他创作
大纲/内容
一、MySQL的概述和安装
MySQL的概述
数据库的种类
关系型数据库(RDBMS)
Oracle、MySQL 和 SQL Server
非关系型数据库
键值型数据库
Redis
文档型数据库
MongoDB
搜索引擎数据库
Elasticsearch
列式数据库
HBase
图形数据库
Neo4J
为什么要选择MySqL
RDBMS设计模式
ER模型
表的关联关系
一对一（one - to - one）
一对多（one - to - many）
多对多（many - to- many）
自我引用(Self - reference)
MySQL的环境搭建
MySQL的4大版本
社区版
企业版
集群版
高级群版
MySQL的下载
经典版本：MySQL5.7
最新版本：MySQL8.0.27
MySQL的安装
建议软件目录和数据目录分开放
推荐教程链接
https://blog.csdn.net/gh_xiaohe/article/details/124037312
MySQL的配置
用户、密码、端口号
服务名称
环境变量
MySQL5.7的字符集修改
MySQL的基本使用
服务的启动与关闭
启动net start 服务名
关闭 net stop 服务名
命令行访问MySQL
mysql -uroot -p 密码 -P端口号
图形化管理工具
MySQL的卸载
推荐教程链接
https://blog.csdn.net/gh_xiaohe/article/details/124035535
二、SQL之Select使用
Select的5个子句
分支主题
分支主题
select 字段列表/函数/计算表达式 from 表名
where子句
从数据库表中直接筛选出满足条件的那些行
group by
分组
如果没有where那么就是对整张表进行分组
如果有where就是对满足where条件的行进行分组
having
（1）和where的不同
（1）where是在表中直接筛选,having它是在查询的初步结果中再次筛选
（2）where后面条件中不能出现 聚合函数
（3）having和group by一起使用
order by
排序
默认是升序
明确指明是升序还是降序
order by xxx desc
降序
order by xxx asc
升序
select * from employee order by did 【asc】,salary desc;
按部门编号升序，同一个部门的按照薪资的降序排列
limit
参数
第几页
page
每页显示几行
n
limit (page-1)*n , n
01SQL语言的使用规则
SQL大小规则
命名规则
分支主题
注释
分支主题
规范
分支主题
SQL的分类
DML(数据操作系统）：INSERT/ DELETE/ UPDATE / SELECT
DDL(数据定义语言) ： CREATE/ALTER/DROP/TRUNCATE/RENAME
DCL（数据控制语言） ：ROLLBACK/COMMIT/GRANT/REVOKE
数据库对象
表(table)、视图(view)、索引(index)、序列(sequence)、存储函数(function)
存储过程(procedure)、触发器(trigger)
02基本的select语句
select 字段列表/函数/计算表达式 from 表名
列的别名
AS 或者空格 建议使用 双引号引用起来
去除重复行
DISTINCT
空值参与运算
空值参与运算后所得结果都是null
着重号
` `定义的名字和关键字重复还需要使用时
查询常数
select ‘张三’ .......所查的表结构后加上张三常数列
显示表结构
DESC
过滤数据
where
03运算符
算数运算符
+ ， - ， * ， / （div），%（mod）
比较运算符
符号类型
> ,< ,>=,<=,=, !=（<>）
NULL判断
is null
is not null
千万不要使用=判断
非符号类型
分支主题
逻辑运算符
与(&&, and)，或(|| , or)，非（not）
分支主题
范围
between ... and ...
集合范围
in (x1,x2,x3...)
not in (x1,x2,x3)
模糊查询
like
结合通配符
%
表示任意个字符
_
一个_表示一个字符
位运算符
分支主题
运算符的优先级
分支主题
04排序与分页
排序ORDER BY
单列排序
多列排序
ASC升序 、DESC 降序
分页LIMITER
公式
（当前页数 - 1）* 每页条数 ，每页条数
位置
必须放在整个select的最后
新特性
LIMIT ... OFFSET ...
05多表查询
阿里规范：多表链接必须使用带表的别名
分支主题
笛卡尔积（或交叉链接）
SQL92语法
(+)创建连接
(+) 表示哪个是从表。
等值链接、非等值链接
自连接、非自连接
内连接、外连接
没有满外连接
多个链接条件使用and操作符
SQL99语法
JOIN ON
等值链接、非等值链接
自连接、非自连接
内连接、外连接
内连接 INNER JOIN ON
外连接 OUTER JOIN
左外连接 LEFT OUTER JOIN
右外连接 RIGHT OUTER JOIN
满外链接 FULL OUTER JOIN
MySQL不支持但是可以实现
使用LEFT JOIN UNION RIGHT JOIN 代替
满外链接
MySQL不支持但是可以实现
使用LEFT JOIN UNION RIGHT JOIN 代替
多个链接条件使用and操作符
合并查询 UNION 和 UNION ALL
UNION 查询结构去重
UNION ALL 查询结果不去重
如何选择： 建议使用 UNION ALL 执行效率高
7种 SQL JOINS的实现
分支主题
总结：连接 n个表,至少需要n-1个连接条件。
06单行函数
分支主题
数值函数
基本函数（重点）其他了解
角度与弧度互换函数
三角函数
指数与对数
进制间的转换
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124142056
字符串函数
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124144680
日期和时间函数
获取日期、时间（重）
日期与时间戳的转换（重）
获取月份、星期、星期数、天数等函数
日期的操作函数
时间和秒钟转换的函数
计算日期和时间的函数（重）
日期的格式化与解析（重）
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124161233
流程控制函数
加密与解密函数
MySQL信息函数
其他函数
07聚合函数
聚合函数不能嵌套使用
聚合函数类型
AVG()
SUM()
MAX()
MIN()
COUNT()
GROUP BY
HAVING
SELECT的执行过程
分支主题
WHERE 为什么不包含聚合函数的过滤条件
从执行过程来看 可以先前引用，但是没有办法向下引用
为什么where 的效率要比 having的效率高？
由SQL的执行顺序可知，where的执行在 having的前面，现在where 中过滤一些条件，分组时效率高很多，提升很大，如果此时 过滤条件写在 having 中 先分组后进行条件筛选，很多数据都是不需要的数据，无用功
08 子查询
单行子查询、多长子查询
相关子查询、不相关子查询
子查询可以应用的位置
分支主题
问题：自连接和子查询有好坏之分吗？
（子查询和自连接都可以实现如何选择）
一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
经典题型
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124185427
三、SQL之DDL、DML、DCL使用篇
SQL的分类
DML(数据操作系统）：INSERT/ DELETE/ UPDATE / SELECT
DDL(数据定义语言) ： CREATE/ALTER/DROP/TRUNCATE/RENAME
DCL（数据控制语言） ：ROLLBACK/COMMIT/GRANT/REVOKE
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124314094
创建和管理数据库
使用数据库（SHOW/SELECT）
使用/切换数据库
USE 数据库名
查看当前所有的数据库
SHOW DATABASES # s 代表多个数据库
查看当前正在使用的数据库
SELECT DATABASE();;
查看指定库下的所有表
SHOW TABLES FROM 数据库名
查看数据的创建信息
SHOW CREATE DATABASE数据库名
或
SHOW CREATE DATABASE\G 数据库名\G
注意:
要操作表格和数据库之前必须先声明是对那个数据库进行操作,否则就需要对所有对象加上 “数据库名”。
创建数据库（CREATE）
方式一：创建数据库
CREATE DATABASE 数据库名;# 创建的此数据库使用的是默认的字符集
方式二：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
方式三：方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
CREATE DATABASE IF NOT EXISTS 数据库名;
注意：
DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删
旧库完成的。
修改数据库（ALTER）
更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
注意：（一般不用）修改数据库，不修改数据库名
删除数据库（DROP）
方式一：删除指定数据库
DROP DATABASE 数据库名
方式二：删除指定数据库（推荐）
DROP DATABASE IF NOT EXISTS 数据库名
创建和管理表
查询表结构（DESC/SHOW）
DESCRIBE/DESC 表名
SHOW CREATE TABLE 表名
使用 SHOW CREATE TABLE 不仅仅可以查看创建时的详细语句，还可以查看存储引擎和字符编码
创建表(CREATE TABLE)
注意：MySQL 8.x 版本中，不在推荐 INT 类型 指定显示长度 ，并且在未来可能会去掉这样的语法
方式一：正常创建（白手起家）
CREATE TABLES 【 IF NOT EXISTS】 表名 （
字段1 ，数据类型 [ 约束条件 ] [ 默认值 ] ，
字段2 ，数据类型 [ 约束条件 ] [ 默认值 ] ，
字段3 ，数据类型 [ 约束条件 ] [ 默认值 ] ，
.........
[ 表约束条件 ]
）
方式二：基于现有表进行创建
CREATE TABLES 【IF NOT EXISTS】 表名
AS
SELECT * FROM 另一个表名
衍生出两种现象
现象一：创建表 时 有另个表中的结构 和 表中的数据
正常创建
现象二：创建表时 仅仅 只创建表 结构 不 加入表数据
增加 where 条件 让其横不成立
如 where 1 = 2；
修改表中的列(ALTER TABLE)
追加一个列(ALTER TABLE ... ADD )
ALTER TABLE 表名 ADD 【COLUMN】 字段名
字段类型 【FIRST | AFTER 字段名】;
修改一个列(ALTER TABLE ... MODIFY )
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名 1 字段类型 【 DEFAULT 默认值】 【FIRST | AFTER 字段名 2】
重命名一个列(ALTER TABLE ... CHANGE)
ALTER TABLE 表名 CHANGE 【COLUMN】
列名 新列名 新数据类型；
删除一个列(ALTER TABLE ... DROP )
ALTER TABLE 表名 DROP 【COLUMN 】 字段名
重命名表（ALTER TABLE 表名 RENAME TO 表名）
方式一：RENAME (建议)
RENAME TABLE 需要修改表 TO 修改后的表名
方式二:
ALTER TABLE 需要修改的表名 RENAME TO 修改后的表名
删除表（DROP TABLE ）
DROP TABLE 【IF NOT EXISTS】数据表1，【数据表2，数据表3】
可以回滚
清空表（TRUNCATE TABLE）
TRUNCATE TABLE 数据表
不能回滚
经典面试题：删除表使用 DROP 还是 使用 TRUNCATE
理性：
阿里巴巴规范：TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
原理：不需要考虑、回滚等操作、避免了浪费资源
实际角度：
使用 TRUNCATE 可能会造成事故 ，从此角度出发来看，浪费的资源就显得不值得一提，故而选择了 DROP
复制表数据
创建表方式二
数据处之增删改
增加
insert into 表名称(字段列表) values(值列表);
值列表要与字段列表一一对应
分支主题
insert into 表名称 values(值列表);
默认所有列都要插入数据，值列表的数量与顺序要与表结构一一对应
分支主题
说明：没有进行赋值的hire_date 的值为 null，初始设置的默认值，没有这是默认nul
同时插入多条数据
分支主题
将查询节后插入到表中
分支主题
注意：添加数据的字段的长度不能低于查询的字段的长度 ，不然有插入失败的风险
修改
update 表名称 set 字段名 = 字段值，字段名 = 字段值。。。 where 条件;
案例 1
分支主题
案例 2
分支主题
总结：
可以一次更新多条数据。
如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
使用WHERE子句指定需要更新的数据。
删除
删除全表的数据
delete from 表名称;
truncate 表名称;
效率高，但不能回滚
删除部分行
delete from 表名称 where 条件;
总结
使用 WHERE 子句删除指定的记录。
如果省略 WHERE 子句，则表中的全部数据将被删除
删除中的数据完整性错误
在删除数据时，也有可能因为约束的影响，导致删除失败
新特性计算列
MySQL数据类型
数据类型精讲
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124519784?spm=1001.2014.3001.5501
整型
xxxint
给int指定宽度必须结合zerofill 和unsigned才有意义
浮点型
float和double
可以指定宽度（M,D）
M是指总宽度
D是指小数点后几位
定点型
decimal和numeric
字符型
char，varchar，xxxtext
char类型
适合于字符串较短，长度固定，修改频繁
char如果不指定宽度，默认是1
varchar类型
必须指定宽度，最多不能超过几个字符
实际占的宽度 N个字符的宽度 + 1或2个字节
因为每次要计算实际占的字节数，因此varchar效率较低，但是对于不定长的字符串，节省空间
xxxtext类型
适合于小摘要
日期时间类型
date, time, datetime, year, timestamp
二进制数据
xxxblob
四、约束
数据的完整性
实体完整性
域完整性
引用完整性
用户自定义完整性
非空约束 NOT NULL
唯一性约束 UNIQUE
主键PRIMARY KEY
自增长 AUTO_INCREMENT
淘宝数据库的主键是如何设计的
推荐博客链接
https://blog.csdn.net/AlbenXie/article/details/122890302
订单ID = 时间 + 去重字段 + 用户ID后6位尾号
大部分人的回答如此自信：用8字节的 BIGINT 做主键，而不要用INT。
UUID啊，虽然全局唯一，但是占用36字节，数据无序，插入性能差。
总结
分支主题
外键约束FOREIGN KEY
开发中，为什么外键被禁止使用
推荐博客：
https://blog.csdn.net/qq_37171353/article/details/123422801
CHEK检查约束
在MySQL8.0中有效
DEFAULT默认值约束
为什么创建表时，加 not null default “” 或 default 0
不想让表中出现null值。
为什么不想要 null 的值？
不好比较。null是一种特，碰到运算符，通常返回null殊值
效率不高。影响提高索引效果。
五、其他数据库对象
01 视图
为什么使用视图？优点？缺点？
为什么使用视图？
可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。
比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。
优点
操作简单
减少数据冗余
数据安全
适应灵活多变的需求
能够分解复杂的查询逻辑
缺点
实际项目中，如果视图过多，会导致数据库维护成本的问题
创建、修改、删除视图
创建视图
视图的格式
分支主题
创建单表视图
情况1：视图中的字段与基表的字段有对应关系
分支主题
情况2：视图中的字段在基表中可能没有对应的字段
分支主题
创建多表联合视图
分支主题
基于视图创建视图
分支主题
查看视图
语法1：查看数据库的表对象、视图对象
SHOW TABLES;
语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
语法3：查看视图的属性信息
SHOW TABLE STATUS LIKE '视图名称'\G
语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
修改视图
方式一：CREATE OR REPLACE VIEW 子句修改视图
分支主题
说明：CREATE VIEW 子句中各列的别名应和子查询中各列相对应。
方式2：ALTER VIEW
分支主题
删除视图
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
修改视图数据
一般情况下
MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。
UPDATE操作
分支主题
DELETE操作
分支主题
02 存储过程和函数
推荐博客链接：
https://blog.csdn.net/gh_xiaohe/article/details/124527097
存储过程
理解
是一组经过预先编译的 SQL 语句的封装。
好处
分支主题
分类
分支主题
创建存储过程
语法
分支主题
调用存储过程
调用in模式的参数：
CALL sp1('值');
调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;
调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;
存储函数
语法格式：
分支主题
注意创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：
方式一：
加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
分支主题
方式二：
执行：（重启服务后失效）
SET GLOBAL log_bin_trust_function_creators = 1;
举例
分支主题
存储函数可以调用存储函数
存储函数和存储过程的对比
分支主题
存储过程和函数的查看、修改、删除
查看存储过程和函数的创建信息
使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
从information_schema.Routines表中查看存储过程和函数的信息
分支主题
修改存储过程或函数
修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。
分支主题
删除存储过程和函数
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
关于存储过程使用的争议
优点
存储过程可以一次编译多次使用。
可以减少开发工作量。
存储过程的安全性强。
存储过程的安全性强。
良好的封装性。
缺点
可移植性差。
调试困难。
存储过程的版本管理很困难。
它不适合高并发的场景。
03 变量、流程控制与游标
查看MySQL文档的系统变量
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
分类
系统变量、用户变量（会话用户变量、局部变量）
系统变量
查看所有或部分系统变量
分支主题
查看指定系统变量
分支主题
修改系统变量的值
方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
分支主题
局部变量
位置：只能放在 BEGIN … END 中，而且只能放在第一句
定义变量
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL
举例：DECLARE myparam INT DEFAULT 100;
变量赋值
方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
使用变量（查看、比较、运算等）
SELECT 局部变量名;
对比会话用户变量与局部变量
分支主题
定义条件与处理程序
分支主题
定义条件
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
举例
分支主题
定义处理程序
定义处理程序的几种方式 ：
分支主题
流程控制
分支结构：IF、CASE WHEN
IF
IF 语句的语法结构：
分支主题
特点：
① 不同的表达式对应不同的操作
② 使用在begin end中
CASE
CASE 语句的语法结构1：
分支主题
CASE 语句的语法结构2：
分支主题
循环结构: LOOP、WHILE、REPEAT
凡是循环结构，一定具备4个要素：
1、初始化条件
2、循环条件
3、循环体
4、迭代条件
LOOP
LOOP语句的基本格式如下：
分支主题
WHILE
WHILE语句的基本格式如下：
分支主题
循环结构之 REPEAT
REPEAT语句的基本格式如下
分支主题
注意 UNTIL 后不要加 ；号
对比三种循环结构：
分支主题
三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
跳转结构：LEAVE、ITERATE
leave
LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。
可以把 LEAVE 理解为 break。
基本格式如下：
LEAVE 标签名
ITERATE
TERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。
ITERATE 理解为 continue，意思为“再次循环”
语句基本格式如下：
ITERATE label
举例
分支主题
小结
都可以使用在循环中
ITERATE 只能使用在循环当中
LEAVE 可以结束存储过程、存储函数体
都是使用带标签的场景
游标
推荐个人博客链接
https://blog.csdn.net/gh_xiaohe/article/details/124566390
什么是游标
分支主题
使用游标步骤
第一步，声明游标
DECLARE cursor_name CURSOR FOR select_statement;
第二步，打开游标
OPEN cursor_name
第三步，使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...
注意：var_name必须在声明游标之前就定义好。
注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。
第四步，关闭游标
CLOSE cursor_name
MySQL中游标可以在存储过程和函数中使用。
04 触发器
触发器的概述
分支主题
触发器的创建
创建触发器的语法结构是：
分支主题
触发器使用到新加入的数据
添加 new 不区分大小写
删除 old
查看触发器
方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
删除触发器
DROP TRIGGER IF EXISTS 触发器名称;
注意
触发器不使用了，需要删除，它不像存储过程和存储函数需要调用才会助兴， 触发器的执行是基于事件发生以后自动的一个执行（很隐蔽的）
优点
触发器可以确保数据的完整性。
触发器可以帮助我们记录操作日志。
触发器还可以用在操作数据前，对数据进行合法性检查。
缺点
触发器最大的一个问题就是可读性差
相关数据的变更，可能会导致触发器出错。
注意点
注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
新知识 自定义错误 （固定格式）
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
六、MySQL8.0其他新特性
01 窗口函数
窗口函数可以进行排序、生成序列号等一般聚合函数无法实现的高级操作。
解决什么痛点？
需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好**。
语法格式
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
分类
序号函数
分布函数
前后函数
首尾函数
其他函数
MySQL窗口函数网址
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。
分支主题
02 公用表达式
解决什么痛点？
分类
普通公用表达式
分支主题
递归公用表达式
分支主题
小结
分支主题
收藏
0 条评论
下一页
