5.SQL2changjing

予早 2025-09-01 00:51:32

Categories： Tags：

按业务场景组织SQL

查看MySQL信息

-- 查看系统版本
select VERSION();
-- 查看系统时间
select now();
-- 查看当前用户
select user();
-- 查看当前数据库
select database();
-- 显示表描述信息
desc 表名;


-- 退出数据库，关闭连接，这是唯一一个可以不写";"的
exit
exit;

truncate department;



 

数据库导入

source sql文件路径（可以将文件拖入cmd直接显示路径）

 

退出数据库

认证

-- 查看用户信息
use mysql;
select host, user, password from user;

-- 查看权限
show grants for 'root'@'%';
show grants for 'root'@'localhost';
show grants for 'root'@'192.168.1.1';

-- 创建用户
use mysql;
create user 'sunshine'@'localhost' identified by 'mysqL123456_';
flush privileges;

-- 修改用户密码
use mysql;
update user set password = password('mysqL123456_') where user = 'sunshine';
flush privileges;
-- 或者
set password for 'sunshine'@'localhost'= password('mysqL123456_');
flush privileges;

-- 删除用户
use mysql;
delete from user where user='superboy' and host='localhost' ;
flush privileges;

-- 赋予权限、撤销权限

-- 赋予部分权限，其中的shopping.*表示对以shopping所有文件操作。
grant select,delete,update,insert on simpleshop.* to superboy@'localhost';
flush privileges;
-- 赋予所有权限
grant all privileges on simpleshop.* to superboy@localhost;
flush privileges

-- 撤销update权限
revoke update on simpleshop.* from superboy@localhost;
-- 撤销所有权限
revoke all on simpleshop.* from superboy@localhost;

视图（逻辑表）

物理表、逻辑表（有表结构，无表数据）

逻辑表有利于多表查询，不利于插入、更新、删除

# 创建
create [or replace] view 视图名称 [列名列表] as select语句 [with [cascaded | local] check option]
# 修改，使用create or replace也可以
alter view [or replace] view 视图名称 [列名列表] as select语句 [with [cascaded | local] check option]

show create view 视图名称; # 查看创建视图语句
drop view if exists stu_v_1;

基于视图对数据进行操作

# 查询
select * from 视图名称 ...;

对视图的修改：（1）单表视图操作: 可以进行增删改, 但是要实现新增: 前提是视图必须包含基表的所有不能为空的字段。（2）多表视图(基表来源两个以上)不能插入数据, 也不能删除数据，但是可以修改数据。

使用视图还是有很多局限性的，并没有像直接使用表那么方便。如果视图定义中包含了group by、union、聚合函数以及其他一些特殊情况，就不能被更新了；更新视图的查询也可以是一个关联语句，但是被更新的列必须来自同一张表；而且所有使用临时表算法实现的视图都无法被更新。

存储过程

-- 创建
create procedure 存储过程 ([参数列表])
begin
  -- sql语句
end;

-- 查看
show create procedure 存储过程名称;

-- 查询指定的存储过程的状态信息
select * from information_schema.routines where routine_schema = 'xxx'

-- 调用
call 存储过程名称([参数列表])

-- 删除
drop procedure [id exists] 存储过程名称;

在SQL中，DELIMITER是一个命令行工具，用于改变SQL语句的结束符。在大多数SQL环境中，默认的语句结束符是分号（;）。但是，当你定义存储过程、函数或触发器时，这些对象内部可能包含多个SQL语句，并且每个内部语句也会以分号结束。因此，你需要一种方式来告诉SQL解析器，一个存储过程或函数的结束，而不是仅仅一个单独的SQL语句的结束。

DELIMITER命令的作用就是改变这个结束符。当你定义存储过程时，你可以设置一个新的结束符，比如$$，这样你就可以在存储过程内部自由地使用分号，而不会意外地结束存储过程的定义。一旦存储过程定义完成，你可以通过再次设置DELIMITER为分号来恢复默认的行为。

以下是使用DELIMITER的一个例子：

sql

DELIMITER $$

CREATE PROCEDURE MyProcedure()
BEGIN
    -- 一些SQL语句
    SELECT 'Hello, World!';
    -- 这里的分号不会结束存储过程的定义，因为我们已经改变了结束符
END $$

DELIMITER ;

在这个例子中：

DELIMITER $$ 将结束符从分号改为$$。
CREATE PROCEDURE MyProcedure() 定义了一个存储过程。
在存储过程内部，每个SQL语句后面都可以跟一个分号，因为现在的结束符是$$。
END $$ 表示存储过程的定义结束，$$是新的结束符。
DELIMITER ; 将结束符改回默认的分号。

使用DELIMITER可以让你在定义复杂的存储过程时，更清晰地组织和结束你的SQL语句，避免因为意外的分号而导致的语法错误。

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

CREATE FUNCTION 存储函数名称 ([ 参数列表 ]) 
RETURNS type [characteristic ...] 
BEGIN
    -- SQL语句 
RETURN ...; 
END ;

触发器

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作，分为表级触发器（语句级触发器）、行级触发器

OLD和NEW来引用触发器中发生变化的记录内容

mysql中触发器是行级触发

触发器类型	NEW、OLD
insert	new表示将要或者已经新增的数据
update	old表示修改之前的数据，new表示将要或已经修改后的数据
delete	old表示将要或者已经删除的数据

CREATE TRIGGER trigger_name 
BEFORE/AFTER INSERT/UPDATE/DELETE 
ON tbl_name FOR EACH ROW -- 行级触发器 
BEGIN
    trigger_stmt; 
END;

show triggers; -- 查看触发器
drop trigger [schema.]trigger_name;

SQL批量后台执行

使用nohup执行脚本文件

#!/bin/bash
mysql -u root -p[password] --database=db < test.sql
# 注意-p一定要和密码连着，密码中有shell特殊字符时加上dan'y

chmod u+x import.sh

nohup ./import.sh &

将一张表的部分数据更新到另一张表，该如何操作呢？

参考答案

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。参考如下代码：

update b set b.col=a.col from a,b where a.id=b.id;
update b set col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

### 补数据

# 查询去重后跟进的建议数量
SELECT
  count(DISTINCT `guidance_id`)
FROM
  `gatherone_oms`.`tb_alm_pitch_record`;

# 非去重 accound_id join 建议id，与上述数量保持一致说明无二义性数据
SELECT
  t1.`guidance_id`,
  t2.`account_id`
FROM
  (
    SELECT DISTINCT
      `guidance_id` COLLATE utf8mb4_0900_ai_ci AS `guidance_id`
    FROM
      `gatherone_oms`.`tb_alm_pitch_record`
  ) t1
  LEFT JOIN (
    select
      `account_id`,
      `guidance_id`
    from
      `gatherone_plateform_data`.`tb_meta_guidance`
    GROUP BY
      `account_id`,
      `guidance_id`
  ) t2 ON t1.`guidance_id` = t2.`guidance_id`;

# 更新数据
UPDATE `gatherone_oms`.`tb_alm_pitch_record` AS t0
JOIN (
  SELECT
    t1.`guidance_id`,
    t2.`account_id`
  FROM
    (
      SELECT DISTINCT
        `guidance_id` COLLATE utf8mb4_0900_ai_ci AS `guidance_id`
      FROM
        `gatherone_oms`.`tb_alm_pitch_record`
    ) t1
    LEFT JOIN (
      select
        `account_id`,
        `guidance_id`
      from
        `gatherone_plateform_data`.`tb_meta_guidance`
      GROUP BY
        `account_id`,
        `guidance_id`
    ) t2 ON t1.`guidance_id` = t2.`guidance_id`
) AS t3 ON t3.`guidance_id` = t0.`guidance_id`
SET
  t0.`account_id` = t3.`account_id`;

行列转换

行转列

以mysql为例，首先，假设我们有一张分数表（tb_score），表中的数据如下图：

可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。通常，我们有两种方式来实现这种转换。

使用 CASE…WHEN…THEN 语句实现行转列，参考如下代码：
```
SELECT userid, 
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文', 
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学', 
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语', 
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治'  
FROM tb_score  GROUP BY userid
```
注意，SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject=”语文”的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。假如userid =’001’ and subject=’语文’ 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。
使用 IF() 函数实现行转列，参考如下代码：
```
SELECT userid, 
SUM(IF(`subject`='语文',score,0)) as '语文', 
SUM(IF(`subject`='数学',score,0)) as '数学', 
SUM(IF(`subject`='英语',score,0)) as '英语', 
SUM(IF(`subject`='政治',score,0)) as '政治'  
FROM tb_score  GROUP BY userid
```
注意，IF(subject=’语文’,score,0) 作为条件，即对所有subject=’语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

DCL

用户管理

-- 查询用户
use mysql;
select * from user;
-- 创建用户 想要在任意主机运行@%通配符
create user '用户名'@'主机名' identified by ’密码‘;
-- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-- 删除用户
drop user '用户名'@'主机名';

权限控制

-- 查询权限
show grants for '用户名'@'主机名';
-- 授予权限
grant all on sqlwxercise.* to '用户名'@'主机名';
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-- 多个权限之间使用逗号分隔

联合查询，union

select 字段列表 from 表A union select 字段列表 from 表B;

事务

-- 显式开启事务
start transaction; -- begin; 等价
-- 提交
commit;
-- 回滚
rollback;

-- 查看事务隔离级别
select @@transaction_isolation;

日志

https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

Mysql七大日志

一般查询日志，general query log
错误日志，error log
慢查询日志，slow query log
回滚日志，undo log
重做日志，redo log
二进制日志，binlog
中继日志，relay log

一般日志，general log

该日志记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等。

错误日志，error log

该日志文件会记录 MySQL 服务器的启动、关闭和运行错误等信息。

慢查询日志，slow query log

记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位 MySQL 服务器性能瓶颈所在。

重做日志，redo log

作用：
重做日志（redo log）的作用是确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘。在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

内容：
物理格式的日志，记录的是物理数据页面修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。

什么时候产生：
事务开始之后就产生 redo log，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。

什么时候释放：
当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：
默认情况下，对应的物理文件位于数据库的 data 目录下的 ib_logfile1&ib_logfile2。

innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下，innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认为 2。

回滚日志，undo log

作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

内容

逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

什么时候产生

事务开始之前，将当前时的版本生成 undo log，undo 也会产生 redo 来保证 undo log 的可靠性。

什么时候释放

当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否由其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理 undo log 的日志空间。

对应的物理文件

MySQL 5.6 之前，undo 表空间位于共享表空间的回滚段中，共享表空间的默认名称是 ibdata，位于数据文件目录中

MySQL 5.6 之后，undo 表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变 undo log 文件的个数。

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

二进制日志，binlog

二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，不记录查询语句，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。默认情况下，二进制日志功能是开启的，启动时可以重新配置–log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。

作用

用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步；用于数据库基于时间点的还原。

内容

逻辑格式的日志，可以简单认为就是执行过的事务中的 SQL 语句，但又不完全是 SQL 语句这么简单。

它包括了执行的 SQL 语句（增删改）反向的信息，也就意味着 delete 对应着 delete 本身和其反向的 insert；update 对应着 update 执行前后的版本的信息；insert 对应着 delete 和 insert 本身的信息。

在使用 MySQLbinlog 解析 binlog 之后一些都会真相大白。因此可以基于 binlog 做到类似于 Oracle 的闪回功能，其实都是依赖于 binlog 中的日志记录。

什么时候产生

事务提交的时候，一次性将事务中的 SQL 语句（一个事物可能对应多个 SQL 语句）按照一定的格式记录到 binlog 中。

这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候刷新到磁盘，redo log 是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了 bin_log 的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为 binlog 是在事务提交的时候一次性写入造成的，这些可以通过测试验证。

什么时候释放

binlog 默认是保持时间由参数 expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过 expire_logs_days 配置的天数之后，会被自动删除。

中继日志，relay log

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

总结

一般日志、错误日志、慢查询日志
回滚日志、重做日志、二进制日志
中继日志

redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），undo段位于共享表空间内。

666.11.调优

k8s