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