MySQL 的一些基础知识记录，包括一些配置文件，cmd命令等
交叉连接查询 [产生笛卡尔积]
内连接查询(使用的关键字 inner join -- inner可以省略)
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
外连接查询(使用的关键字 outer join -- outer可以省略)
左外连接：left outer join select * from A left outer join B on 条件;
右外连接：right outer join select * from A right outer join B on 条件;
满外连接: full outer join select * from A full outer join B on 条件;
子查询 select的嵌套 表自关联： 将一张表当成多张表来用
前言
这里使用的MySQL版本是8.0.25
MySQL安装，包括相关配置文件文本内容，相关cmd命令
通过安装包配置环境变量使用cmd管理员权限通过命令安装MySQL 8.0.25
一、安装配置
在解压缩目录下新建一个配置文件my,ini
内容如下：
[mysqld]
# 设置mysql的端口为3306
port=3306
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端默认使用的端口
port=3306
default-character-set=utf8
在系统变量中新增MYSQL_HOME与在Path中新增%MYSQL_HOME%\bin 路径（参考JDK配置）使用cmd管理员权限进入MySQL的bin路径下，运行相关命令即可
①对mysql进行初始化，请注意，这里会生产一个临时密码，后边要使用这个临时密码
mysqld --initialize --user=mysql --console
②安装mysql服务
mysqld --install
③启动mysql服务
net start mysql
④登录mysql，这里需要使用之前生产的临时密码
mysql -uroot –p
⑤修改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
⑤修改root用户权限
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
图形化界面使用：我使用的是 Navicat Premium 15 。
二、其他补充内容
0.部分基础知识
约束 （constraint） 约束实际上就是表中数据的限制条件，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。常见有 主键约束(primary key) PK； 自增长约束(auto_increment) 和 非空约束(not null)。
MySQL查询 支持4种运算符 算术运算符 ；比较运算符； 逻辑运算符； 位运算符。
DQL支持 正则表达式
MySQL的多表操作-多表联合查询 就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。
MySQL-函数非常多，主要可以分为以下几类: 聚合函数 数学函数 字符串函数 日期函数 控制流函数 窗口函数
MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点. 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。
1.游标使用
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.
-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name
2.存储过程与触发器
MySQL 5.0 版本开始支持存储过程。 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法； 存储过就是数据库 SQL 语言层面的代码封装与重用。
格式如下（示例）：
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
sql语句
end 自定义的结束符合
delimiter ;
MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。 存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。
--新建表单
create table emp(
emp_id int primary key auto_increment comment '编号',
emp_name char(20) not null default '' comment '姓名',
salary decimal(10,2) not null default 0 comment '工资',
department char(20) not null default '' comment '部门'
);
--插入数据
insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');
-- 创建存储过程
delimiter $$
create procedure proc01()
begin
select emp_name,department from emp;
end $$
delimiter ;
--调用 存储过程
call proc01();
与存储过程的区别：
1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
5.存储过程可以调用存储函数。但函数不能调用存储过程。
6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.
触发器（trigger）：
触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。 在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。要尽量减少使用触发器
1、创建只有一个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;
2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
执行语句列表
end;
3、查看触发器
show triggers;
4、删除触发器
drop trigger if exists trigger_name ;
-- 用户表
create table user(
uid int primary key ,
username varchar(50) not null,
password varchar(50) not null
);
-- 用户信息操作日志表
create table user_logs(
id int primary key auto_increment,
time timestamp,
log_text varchar(255)
);
-- 如果触发器存在，则先删除
drop trigger if exists trigger_test1;
-- 创建触发器trigger_test1
create trigger trigger_test1
after insert on user -- 触发时机：当添加user表数据时触发
for each row
insert into user_logs values(NULL,now(), '有新用户注册');
--在用户表中新增一条数据
insert into user values(1,'张三','123456');
--查询对应日志表
select * from user_logs ;
+----+---------------------+--------------+
| id | time | log_text |
+----+---------------------+--------------+
| 1 | 2025-02-25 13:39:31 | 有新用户注册 |
+----+---------------------+--------------+
3.事务（Transaction）
MySQL默认存储引擎innoDB 支持事务，行级锁定和外键。
-- 创建账户表 create table account( id int primary key, -- 账户id name varchar(20), -- 账户名 money double -- 金额 ); -- 插入数据 insert into account values(1,'zhangsan',1000); insert into account values(2,'lisi',1000); -- 模拟账户转账 -- 开启事务 begin; update account set money = money - 200 where name = 'zhangsan'; update account set money = money + 200 where name = 'lisi'; -- 提交事务 commit;
事务的隔离级别（Isolate） 
• 读未提交 (Read uncommitted)一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读。
• 读已提交 (Read committed)一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
• 可重复读 (Repeatable read)就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
• 串行 (Serializable)是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
Mysql的默认隔离级别是Repeatable read。
-- 查看隔离级别
show variables like '%isolation%’;
-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
-- 设置read committed
set session transaction isolation level read committed;
-- 设置repeatable read
set session transaction isolation level repeatable read;
-- 设置serializable
set session transaction isolation level serializable;4.锁与优化
锁是计算机协调多个进程或线程并发访问某一资源（数据）的机制（避免争抢）。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。
1、 从对数据操作的粒度分 ：1） 表锁：操作时，会锁定整个表。
2） 行锁：操作时，会锁定当前操作行。
2、从对数据操作的类型分：
1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
MySQL中除InnoDB支持行锁，其他存储引擎仅支持表锁
InnoDB 实现了以下两种类型的行锁。
共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
日志：
日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。
错误日志查看指令：show variables like 'log_error%';
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。 MySQl8.0默认已经开启。
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000001' from 9215;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000001' from 9215 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000001' from 9215 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
查询日志
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
-- 开启查询日志
set global general_log=1;
慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒
-- 查看慢日志配置信息
SHOW VARIABLES LIKE '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log=1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%’;
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time=4;
三、MySQL的优化
--下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______'; -- 查看当前会话统计结果
show global status like 'Com_______'; -- 查看自数据库上次启动至今统计结果
SHOW STATUS LIKE 'Innodb_rows_%’; -- 查看针对Innodb引擎的统计结果
其他查询参数：
1. 定位低效率执行SQL-show processlist
show processlist;
1） id列，用户登录mysql时系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据
2.explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序
explain select * from user where uid = 1;
type 显示的是访问类型，是较为重要的一个指标，可取值为(最好到最坏)： system > const > eq_ref > ref > range > index > ALL 
3.show profile分析SQL
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
set profiling=1; -- 开启profiling 开关；
SHOW PROFILES ; 查看SQL语句执行的耗时：
在上述查询中编号为2的查询分析
show profile for query 2; 
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ： 
各个列名解释：
4.使用索引优化
索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
避免索引失效应用-全值匹配，该情况下，索引生效，执行效率高，尽量使用索引，精确查找，不变更索引
扫一扫
253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
